Master Slave 主从同步错误 Slave_IO_Running:NO/Slave_SQL_Running: No

Master Slave 主从同步错误

Slave_IO_Running:NO
Slave_SQL_Running:Yes

#在Slave库上查看状态
mysql> show slave status\G
Slave_IO_Running: No
Slave_SQL_Running: Yes

#重启master库：service mysqld restart
mysql> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------------±-------------±-------------±-----------------+
| mysql-bin.000534 | 14670663 | | |
±-----------------------±-------------±-------------±-----------------+
mysql> stop slave;
mysql> change master to Master_Log_File=‘mysql-bin.000001’,Master_Log_Pos=98;
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Slave_IO_Running:NO

检查发现他们的auto.cnf中的server-uuid是否相同

[root@localhost ~]# vim /var/lib/mysql/auto.cnf[auto]
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f

停止从库的mysqld服务，删除他的auto.cnf文件，再启动数据库服务即可

[root@localhost mysql]# systemctl stop mysqld.service[root@localhost mysql]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak[root@localhost mysql]# systemctl start mysqld.service

此时再去查看从库auto.cnf，已自动生成新的server-uuid：

[root@localhost mysql]# vim /var/lib/mysql/auto.cnf[auto]
server-uuid=2682888d-994a-11e8-aaf0-000c298cdafc

解决Slave_SQL_Running: No

下面介绍两种解决方法：

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况
解决：
stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。

方法二重新配置从库

主库：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000029 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> change master to master_host='192.168.25.111',-> master_user='repl',-> master_port=3306,-> master_password='Cv.125166',-> master_log_file='binlog.000029',-> master_log_pos=157;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

之后查看从库状态：

mysql> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.25.111Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000029Read_Master_Log_Pos: 157Relay_Log_File: slavee-relay-bin.000002Relay_Log_Pos: 323Relay_Master_Log_File: binlog.000029Slave_IO_Running: YesSlave_SQL_Running: Yes