mysql 主从同步

注意每台服务器的ID要唯一，/etc/my.cnf  server_id = 2
1.主服务器上执行：
 grant replication slave on *.* to 'repl'@'192.168.67.38' IDENTIFIED BY 'repl123' ;
flush privileges
2.主服务器上需要开启binlog
log_bin=/usr/local/mysql/data/mysqlbinlog

3.在主服务器执行备份命令
mysqldump -uroot -p -A -B -F --master-data=1 --single-transaction  --events  >/root/mysqldump.sql

4.在从服务器恢复数据
mysqldum < /root/mysqldump.sql

5.在从服务器上执行连接主服务器配置信息
CHANGE MASTER TO   MASTER_HOST='192.168.174.54',
  MASTER_USER='repl',
  MASTER_PASSWORD='repl123',
  MASTER_PORT=3306;
下面括号内的如果在备份的时候执行了，--master-data=1 这个选项那么就不需要指下面的参数如果没有指定刚需要加下面的配置
(MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10; )

6.启动复制：start slave
查看状态：
show slave status \G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.174.54
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqlbinlog.000021
          Read_Master_Log_Pos: 191
               Relay_Log_File: 192_168_67_38-relay-bin.000002
                Relay_Log_Pos: 352
        Relay_Master_Log_File: mysqlbinlog.000021
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0