mysql5.7配置主从同步（master-slave）

mysql5.7配置主从同步（master-slave）

准备工作

主服务器：192.168.5.130

从服务器：192.168.5.140

步骤

1. 主数据库配置修改

​ 在标签[mysqld]下配置

log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
binlog-do-db = test #需要同步的数据库
binlog-ignore-db = mysql #忽略同步的数据库

2. 主数据库新建同步账号

mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.5.140' identified by 'yourPassword';
mysql> FLUSH PRIVILEGES;

3. 重启mysql服务，查看master status

service mysqld restart
mysql -uroot -p

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000018 |      154 | test		 | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4. 配置从数据库

mysql> CHANGE MASTER TO MASTER_HOST='192.168.5.130',MASTER_PORT=3306,MASTER_USER='rep1',MASTER_PASSWORD='yourPassword',MASTER_LOG_FILE='mysql-bin.000018',MASTER_LOG_POS=154;

CHANGE MASTER TO
MASTER_HOST=‘192.168.5.130’, //主库IP
MASTER_PORT=3306, //主库端口
MASTER_USER=‘rep1’, //访问主库且有同步复制权限的用户
MASTER_PASSWORD=‘yourPassword’, //登录密码
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE=‘mysql-bin.000018’,
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=154;

最后的两个值需要跟主服务器一致

最后的两个值需要跟主服务器一致

5. 启动slave模式并查看状态

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.5.130
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: No
            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: 154
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 130
                  Master_UUID:
             Master_Info_File: /usr/local/data/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: 210209 01:47:26
     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和Slave_SQL_Running应该都是Yes，如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。这里报错是说主从数据库的UUID是一样的，因为这两个是同一个虚拟机克隆出来的，这里需要修改一下从数据库的UUID，解决办法是进入数据库的datadir数据目录，里面会有一个auto.cnf文件，先将这个文件改名备份一个mv auto.cnf auto.cnf.bak，然后重启mysqld服务就会生成新的UUID

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.130
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 601
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 767
        Relay_Master_Log_File: mysql-bin.000018
             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: 601
              Relay_Log_Space: 978
              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: 130
                  Master_UUID: e2b43918-1fd7-11eb-9a1e-000c2960bd29
             Master_Info_File: /usr/local/data/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)

这里就表明主从同步配置成功了

6. 测试主从同步是否生效

从服务器：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test           		 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> select * form test;
Empty set (0.00 sec)

主服务器：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test           		 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> select * form test;
Empty set (0.00 sec)

mysql> insert into test (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+
| id |
+----+
| 1  |
+----+
1 row in set (0.00 sec)

从服务器：

mysql> select * from test;
+----+
| id |
+----+
| 1  |
+----+
1 row in set (0.00 sec)

以上便是mysql5.7搭建主从配置的全过程

补充

• 如果【主服务器】重启mysql服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。
• 如果某段时间内，【从数据库】服务器异常导致同步中断（可能是同步点位置不匹配），可以尝试以下恢复方法：进入【主数据库】服务器（正常），在bin-log中找到【从数据库】出错前的position，然后在【从数据库】上执行change master，将master_log_file和master_log_pos重新指定后，开始同步。