mysql主从复制Slave_IO_Running:No问题处理

测试环境中mysql主从复制出现问题


解决方法：
master主库操作
显然是binlog的位置不对，记录master的binlog位置，重置slave

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000088 |   531506 |              |                  | f2b2ca82-f751-11ec-84de-0050568f6ec0:1-81927 |
+------------------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)

slave从库操作

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000088', MASTER_LOG_POS=0;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

会报错，解决的办法：
由于之前用的master_auto_position=1，现在改回0，重新用pos方法change就可以了。

mysql> change master to master_auto_position=0;
Query OK, 0 rows affected (0.08 sec)

然后重启master节点在查一下binlog的日志

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000089 |      194 |              |                  | f2b2ca82-f751-11ec-84de-0050568f6ec0:1-81927 |
+------------------+----------+--------------+------------------+----------------------------------------------+
1 row in set (0.00 sec)

slave节点操作

mysql>  change master to Master_Log_File='mysql-bin.000089',Master_Log_Pos=194;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave  status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.62
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000089
          Read_Master_Log_Pos: 11094
               Relay_Log_File: test-63-relay-bin.000002
                Relay_Log_Pos: 11220
        Relay_Master_Log_File: mysql-bin.000089
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

已经是成功的了。

参考：https://cloud.tencent.com/developer/article/1401408
https://blog.csdn.net/zhengwei125/article/details/79412857