mysql5.6 2003,mysql 5.6 replication 2003 error

mysql 5.6 replication 因2003错误导致Slave_IO_Running状态处于连接状态

mysql> show slave status \G;

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

Slave_IO_State: Reconnecting after a failed master event read

Master_Host: 192.168.3.4

Master_User: replicator

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000824

Read_Master_Log_Pos: 1030462648

Relay_Log_File: mysql-slave-relay-bin.000866

Relay_Log_Pos: 1030462811

Relay_Master_Log_File: mysql-bin.000824

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

…..

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master ‘replicator@192.168.3.4:3306’ – retry-time: 60 retries: 304

Last_SQL_Errno: 0

从Last_IO_Errno，Last_IO_Error，Slave_IO_Running 三个指标发现slave端出现问题。

erro日志信息

2016-05-31 23:42:32 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 1, Error_code: 2003

2016-05-31 23:44:35 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 2, Error_code: 2003

2016-05-31 23:46:38 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 3, Error_code: 2003

2016-05-31 23:48:41 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 4, Error_code: 2003

2016-05-31 23:50:44 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 5, Error_code: 2003

2016-05-31 23:52:47 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 6, Error_code: 2003

2016-05-31 23:54:50 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 7, Error_code: 2003

2016-05-31 23:56:53 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306’ – retry-time: 60 retries: 8, Error_code: 2003

2016-05-31 23:58:56 50184 [ERROR] Slave I/O: error reconnecting to master ‘replication@192.168.3.4:3306′ – retry-time: 60 retries: 9, Error_code: 2003

2003错误，有如下几种场景

1、密码过期

2、网络问题

3、主库处于不良状态，例如:主库重启后做长时间恢复，那么slave端也会出现2003错误

4、binlog的pos不对

因为是5.6版本，所以密码过期没有这种说法，而且测试没有问题;

主库处于不良状态和binlog可以很快排除没有问题，

尝试ping时，发现ping不通，原来在23点这个点做了网络线路的切换

解决方案:调整网络后，尝试连接后没有问题

如果遇到上述其它原因，处理方式:

4、在做chang to 的时候注意log_pos 是否跟此时主机的一样。在主机上 show master status \G ;可以查看到

mysql> show master status \G;

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

File: mysql-bin.000020

Position: 483723389

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

slave端执行change to:

CHANGE MASTER TO

MASTER_HOST=’192.168.3.4′,

MASTER_USER=’replication’,

MASTER_PASSWORD=’replication’,

MASTER_LOG_FILE=’mysql-bin.000020′,

MASTER_LOG_POS= 483723389;