Mysql主从同步记录

操作系统:CentOS 6.5 Mysql版本:5.7.18
准备两台Mysql,当然可以配置多台，下面以两台为例

master:192.168.43.66
slave:192.168.43.67

一、首先配置主服务器master(192.168.43.66)：
创建同步用户账号

 $mysql -uroot -p
 mysql> use mysql
 mysql> grant replication slave on *.* to 'master'@'192.168.43.67' identified by '12345678';
 mysql>flush privileges;

修改/etc/my.cnf配置文件vim /etc/my.cnf
增加以下参数

server-id=1  #主服务器id
log-bin=mysql-bin  #启动MySQL二进制日志系统，
binlog-do-db=my_data #需要同步的数据库

重启mysql

$ service mysqld restart

查看master数据库状态

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      453 | my_data      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出数据库my_data

二、现在开始配置slave(192.168.43.67):

vi /etc/my.cnf

server-id=2  #从服务器id
log-bin=mysql-bin  #启动MySQ二进制日志系统
replicate-do-db=my_data #需要同步的数据库名

导入数据库my_data
配置主从同步:

$ mysql -uroot -p
mysql>use mysql 
mysql>stop slave;
mysql>change master to
      master_host='192.168.43.66',
      master_user='master',
      master_password='12345678',
      master_log_file='mysql-bin.000005',
      master_log_pos=154;   
mysql>start slave;

#log_file与log_pos是主服务器master状态下的File与Position
执行start slave;时可能会报错：
Slave failed to initialize relay log info structure from the repository

此时需要在/etc/my.cnf中加入配置

relay_log=/data/logs

查看从数据库状态

mysql>show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.43.66
                  Master_User: zhangrui
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: my_data
          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: 154
              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: 9602f224-90b9-11e7-861d-000c29fa24a3
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

注意查看Slave_IO_Running: Yes Slave_SQL_Running: Yes 这两项必须为Yes 以及Log_File、Log_Pos要于master状态下的File,Position相同

参考地址：https://www.linuxidc.com/Linux/2016-08/134531.htm