MySQL 5.6 Slaves Fail to Reconnect to older MySQL server when it goes down for backups

Question

I have an existing replication setup with Masters and Slaves running MySQL 5.0. A long existing script runs at night which restarts MySQL on the master with skip-networking, runs a backup, and then restarts again without skip-networking. The slaves lose connection but then retry to connect every 60 seconds until they successfully connect a couple of minutes later. Here's what the log looks like on an existing slave:

150520  4:12:01 [Note] Slave: received end packet from server, apparent master shutdown:
150520  4:12:01 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.<binlognumber>' position <binlogpos>
150520  4:12:01 [ERROR] Slave I/O thread: error reconnecting to master '<user>@<host>:<port>': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 111'  errno: 2013  retry-time: 60  retries: 86400
150520  4:14:01 [Note] Slave: connected to master '<user>@<host>:<port>',replication resumed in log 'mysql-bin.<binlognumber>' at position <binlogpos>

I introduced a few new slaves that are running MySQL 5.6. They are able to replicate the existing databases without any problems or errors and data is consistent. Everything seems to be working except at night when the original Master goes offline for backups the new servers fail to reconnect on their own.

This is what the log looks like on the new servers in the morning:

2015-05-20 04:12:01 32401 [Note] Slave: received end packet from server due to dump thread being killed on master. Dump threads are killed for example during master shutdown, explicitly by a user, or when the master receives a binlog send request from a duplicate server UUID <newslaveUUID> : Error
2015-05-20 04:12:01 32401 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.<binlognumber>' at position <binlogpos>
2015-05-20 04:12:01 32401 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2015-05-20 04:12:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 1, Error_code: 2003
2015-05-20 04:13:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 2, Error_code: 2003
2015-05-20 04:14:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 3, Error_code: 2003
2015-05-20 04:15:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 4, Error_code: 0
2015-05-20 04:16:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 5, Error_code: 0
2015-05-20 04:17:01 32401 [ERROR] Slave I/O: error reconnecting to master '<user>@<host>:<port>' - retry-time: 60  retries: 6, Error_code: 0

All I have to do is go into the servers and issue a STOP SLAVE; , START SLAVE; and everything starts right back up again without trouble. It just won't do it on its own.

If I replicate between 5.6 servers and one goes offline the 5.6 slaves can reconnect to the 5.6 master just fine.

The replication user has only replication client and replication slave privileges on everything. The Connect_Retry value is 60 on both new and old slaves.

Answer 1

While replicating from a MySQL 5.0 Master to MySQL 5.6 Slave is allowed, it is not good to keep that scenario indefinitely. Why ? The way MySQL 5.6 reconnects to an older Master may be more difficult with older Masters.

To illustrate problems of this nature, consider how MySQL Replication can break between versions because of Binary Logs changing between versions of MySQL. I have discussed this before in my answer to the Jan 02, 2015 question mysql replication master 5.5 slave 5.1 error on create database.

In that answer, I mentioned how binary log events have been added with each new major release of MySQL. This may call for MySQL 5.6 to ignore or bypass events from the binary logs it does not understand. In some rare cases, the SQL will break while trying to unravel and binary log event that may have changed between versions. In that instance, the SQL thread for MySQL Replication WILL NEVER START BACK UP.

While I am talking about the SQL thread in my scenario, your situation involves the I/O thread. There may some issues (bug or untested feature) causing reconnects not work. In light of this, a Slave reconnecting to a Master of a previous version of MySQL is no longer a trivial matter.

There is a current bug report for MySQL 5.6.20 that has been triaged since August 2014 (Bug #73494 : Slave can't reconnect after 2003 error when sha256_password is used). This is not directly related to connecting to a older Master but the problem exists with MySQL 5.6 itself.

You should either consider one of the following

• Increase max_allowed_packet to 1G on the Master and all Slaves
• refrain from using MySQL 5.6 as a Slave to an older Master
• upgrade to MySQL 5.6 across the board

UPDATE 2015-05-21 18:06 EDT

If Master and Slave have max_allowed_packet set at 16M, then there is no need to change it.

If you can live without changing skip-networking, try the following

• Step 01 : STOP SLAVE IO_THREAD;
• Step 02 : Run mysqldump --single-transaction ...
• Step 03 : START SLAVE IO_THREAD;

This way, the I/O Thread stays connected 24 / 7 and you never have to restart mysql.

GIVE IT A TRY !!!