Moretv-Computing3-1数据库主从状态不稳定解决办法

zhoulei

1、杭州机房Moretv-Computing3-1数据库主从状态频繁出现Slave_IO_Running: Connecting.查看error日志有如下大量报错：151027 11:31:29 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000381' at position 381145669
151027 11:31:29 [Note] Slave: received end packet from server, apparent master shutdown:

2、经过排查发现Moretv-Computing3-1（101.59.54.222）和BJUC-java-tomcat-vod-12（123.59.60.108）从库的server_id相同，并且BJUC-java-tomcat-vod-12上的error日志同样出现大量报错：
151027 11:31:23 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000381' at position 381145669
151027 11:31:24 [Note] Slave: received end packet from server, apparent master shutdown:

3、修改Moretv-Computing3-1的server_id:
mysql> set global server_id=212;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 212   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

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


4、show slave status\G不再出现Slave_IO_Running: Connecting。error日志正常。BJUC-java-tomcat-vod-12上日志也正常。
5、至此数据库故障解决，建议今后做数据库主从时确认好server_id。