Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.
I once wrote a post about the home position of any empty binary log:
Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.
I once put that in an answer (Mar 05, 2013
: MySQL Replication without stopping master). In Step 06 from my answer I wrote this:
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
I also used position 4 in these other posts
Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.
Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.
UPDATE 2014-11-18 22:32 EST
Just for official reference, this example CHANGE MASTER TO command
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.
Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.
UPDATE 2014-11-19 17:47 EST
Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.
Every replication event generated by the master includes the error code that resulted from execution of the query that generated the event. This is usually "success" (0, no error).
When the slave executes a query, it expects the error it encounters to be the same error as the master encountered (again, usually "no error"). When this doesn't happen, replication stops. Note that the error message from the master isn't preserved, just the code. That's why you see placeholders in the error message, because the slave displays the sprintf()
template for the error message.
But note, carefully, where the error is.
Check the master server's error log.
It looks as if the master was not correctly upgraded to MySQL 5.5; specifically, it looks like the mysql_upgrade
utility was not used to upgrade the system tables to be fully compatible with MySQL 5.5.
http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html
You are indeed running an unsupported configuration, with a newer master and an older slave... but in this case, it appears that your master has a problem that is actually unrelated to replication.
Error on master:
message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d. Created with MySQL %d, now running %d.
Please use mysql_upgrade to fix this error.'
error code=1558 ;
The master encountered an error; you may find similar errors in the master's log, although it's possible that they only occur at startup, if at all. In any event, your application should have seen this error also... perhaps it's ignoring it. :(
The slave didn't encounter any error executing the actual query.
Error on slave: actual message='no error', error code=0.
It occurred to me that it's very important that the actions performed by mysql_upgrade
can't be safely replicated, since the slave server is older. It turns out, this was anticipated in the design, so the mysql_upgrade
utility appears to be safe to run on a master, without disconnecting the slave, even if the slave is older (or was already upgraded). According to this comment in the source code of mysql_upgrade.c
:
Master and slave should be upgraded separately.
All statements executed by mysql_upgrade will not be binlogged.
Best Answer
I've confirmed this is a bug with MySQL/Oracle Support. They will update the public tickets when the internal fixes are ready.
To summarize: MySQL replication is only supported between one release level (the second digit) and the next; e.g. between MySQL 5.1 and 5.5; 5.5 and 5.6 but not between MySQL 5.1 and 5.6. The documentation is mistaken when it says "MySQL supports replication from one major version to the next higher major version."* They mean release level or release series number, not major version. That is, unless they change the terminology in the future.
Replication between 5.1 and 5.6 might work, but it's not supported and not well tested. It's best to for replication servers to stay within one release level of each other, and to use the latest version of your 5.x release.
The bug reports are: