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 !!!
What we have done in a similar scenario (using Aurora RDS for master and slaves) is to use an intermediate small EC2 instance with a standard MySQL acting as a filter. This instance is a relay-slave (secondary master) between our main master and final slaves with a configuration similar to this.
We came to this solution dued to the lack of SUPER privilege and replication configuration options in Aurora RDS: You can not set sql_log_bin or define replication filters in RDS.
The problem here is with DDL statements, they are replicated to slaves although you have replication filters by db or tables in the slave. So a possible workaround is:
- Create an auxiliary schema in master.
- Set a db filter in mysql.cnf of secondary master, so it only adds to its binlog changes in desired schema:
binlog-do-db=<myschema>
- If you need to add or modify columns of one of your tables in main master, first set the auxiliary schema as default:
use auxiliary-schema;
- Modify the table indirectly, like
alter table <myschema>.<table> ...
This way, DDL statements are applied in secondary master, but not stored in its binlog and therefore not send to the slaves.
Best Answer
There is one thing that cannot work with MySQL Replication from 5.5 to 5.6 :
DATETIME. Why ?MySQL 5.6 DATETIME has a very different byte format because it accommodates microseconds. When the binlog_format is
ROW, or whenMIXEDswitches fromSTATEMENTtoROW, only a 5.6 Slave can unpack the row change from the relay logs. A slave running MySQL 5.5 cannot handle that.I have two recent posts where I mentioned this
Jan 02, 2015: mysql replication master 5.5 slave 5.1 error on create databaseJun 18, 2015: mysql master slave replication scenarioThe workaround I learned from my boss back in January was to switch binlog_format to
STATEMENTand restart mysqld.If you enable log_bin_use_v1_row_events on a MySQL 5.6 Master to let a MySQL 5.5 slave read it, that sounds like it could work. I still worry about the
DATETIMErepresentation. If you set the option log_bin_use_v1_row_events to true and it stops MySQL 5.6 from writing microseconds with binlog_formatROWorMIXED, then you I think have something. Based on my two posts, I cannot guarantee other statements would be immune.I would normally say "GIVE IT A TRY !!!" but I have never tried that log_bin_use_v1_row_events. So, go ahead and try and let us know.