mysql Slave_IO_Running:NO 问题解决

Master slave 复制错误

Description：

Slave_IO_Running:NO

Slave_SQL_Running:Yes

Seconds_Behind_Master: NULL

我遇到的Slave_IO_Running:NO的情况有下面两种

1. 克隆的机器 两个mysql的uuid很可能相同，报错信息为The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

2. master上的mysql-bin.xxxxxx文件全错误，报错信息为Error reading packet from server: Could not find first log file name in binary log

第一种解决

查看从数据库的日志

[root@slave mysql]# tail /var/log/mysqld.log

发现如下报错：

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决方案：找到对应的 uuid 发现该文件为/var/lib/mysql/auto.cnf

编辑从数据库的该文件

# vim /var/lib/mysql/auto.cnf

将里面uuid的值随便改一个即可

[auto]
server-uuid=ba32605a-fcf9-11ec-a2a5-000c2935b535

重启从数据库服务:

# service mysqld restart

再次查看 发现主从备份成功：

mysql> show slave status\G

********* 1. row *********

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.21

Master_User: slave

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

第二种解决

查看从数据库的日志

[root@slave mysql]# tail /var/log/mysqld.log

如果发现如下报错：

081223 15:51:51 [ERROR] Error reading packet from server: Could not find first log file name in binary log

index file ( server_errno=1236)

081223 15:51:51 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file'

from master when reading data from binary log

081223 15:51:51 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000016', position 173

081223 15:51:58 [Note] Error reading relay log event: slave SQL thread was killed

解决步骤：

重启master库：service mysqld restart

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 98 | | |

+------------------+----------+--------------+------------------+

进入从库：

mysql> stop slave;

根据主库信息修改Master_Log_File='mysql-bin.000001'和Master_Log_Pos=98；

mysql> change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;

mysql> start slave;

mysql> show slave status\G

再次查看 发现主从备份成功：

********* 1. row *********

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.3.21

Master_User: slave

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 98

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

总结：首先应该看日志或者报错信息，之后就面向搜索引擎编程了。