2

I have a question in regards of what potential issues that might happen with a MySQL 5.1 DB that replicates from a MySQL 5.6 DB.

I know the risks involved based on resources such as http://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html and also https://serverfault.com/questions/262936/is-replication-from-mysql-5-5-master-to-a-5-1-slave-possible

However, this is a very special case because I am trying to replace the MySQL 5.1 DB with a 5.6 version, and the only way to have minimum downtime is to have a 5.6 DB set up first and then let it replicate from the 5.1 DB, which will in turn also replicate from the 5.6 DB to form a cyclic replication. The purpose of this is to ensure that during the switchover (when the 5.6 DB will be the active one receiving the live writes instead of the 5.1 ), no write data would be lost.

I was curious if anyone has ever had this kind of setup before with the 5.1 DB replicating from the 5.6 DB, and if there were any issues you encountered. I am not sure what SQL commands in the 5.6 DB could be problematic for the 5.1 DB during replication.

To be exact, the 5.6 version is 5.6.21 and the 5.1 version is 5.1.73

Thanks

  • My setup is actually one 5.1 master and a few 5.1 slaves. And there are almost 100 app servers pointing at this cluster( using the master for writes and slaves for reading ). The end result is to upgrade the master and all its slaves to 5.6 and have all the app servers point to the upgraded instances properly with minimal downtime. – user1805458 Nov 19 '14 at 22:24
  • I would love to avoid cyclic replication if possible, but this seems to be unavoidable. This is because once we have a 5.6 master that is already replicating from the old 5.1 master, if you start pointing the app servers to the new 5.6 cluster, there is always a possibility that something might fail and I might have to point all the app servers back to the 5.1 cluster. However, if there is already some new data in the 5.6 cluster, and then the app server is pointed back to 5.1, the new data might be lost on the 5.1 cluster. – user1805458 Nov 19 '14 at 22:26
  • This is why I think cyclic replication would be necessary for the transitory period to make sure that all the app servers can point successfully to the 5.6 cluster and are then verified to work properly with it. Then, the cyclic replication can be stopped and the 5.1 cluster could be retired. – user1805458 Nov 19 '14 at 22:27
  • I have tried some tests, and it looks like 5.1 can replicate properly without errors from a 5.6 master if that 5.6 master does not have GTID enabled and also has these settings in its my.cnf : – user1805458 Nov 25 '14 at 1:24
  • binlog_checksum=NONE binlog_row_image=FULL binlog_rows_query_log_events=OFF log_bin_use_v1_row_events=1 – user1805458 Nov 25 '14 at 1:24
1

I have successfully done 5.1 to 5.5 and 5.5 to 5.6 for short periods of time, as it looks like you plan to do. Could you do a stepped upgrade? That is, 5.1 to 5.5 to 5.6?

Some of the issues you could run into can be caused by things particular to your workload. I've used the pt-upgrade tool (http://www.percona.com/doc/percona-toolkit/2.2/pt-upgrade.html) to run a batch of queries against an older version replicating to a newer version, for testing (on a separate pair of test servers with production-sized data).

  • Hi Valerie, thanks for the reply. In my case, I am not doing an in-place upgrade of the 5.1 boxes. We first create a new 5.6 instance as a master that will replicate from the 5.1 master, and then we add more new 5.6 slave instances that will replicate from the new 5.6 master. I have also added more comments to my original post on the setup's details – user1805458 Nov 19 '14 at 22:31
  • +1 for "short periods" and step up approach. – RolandoMySQLDBA May 22 at 15:38
1

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.

GIVE IT A TRY AT YOUR OWN RISK !!!

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.

  • Hi Rolando, thanks for the detailed post. I have added some comments to my original post explaining why I think I might need cyclic replication – user1805458 Nov 19 '14 at 22:28
0

The general rule is you do not replicate from a newer version into an older versions.

Replicating into a newer version is the exact upgrade path you should do. If you only have one slave then you would just promote that to master. You'll want to be sure to retain the output of show master status before promotion so you can reconnect the old master as a slave.

If you have multiple slaves have only the new master replicating from the old master and then have the other slaves replicating from the to be new mater prior to promotion.

  • Hi atxdba, yes, I agree that it is not a good idea to replicate from newer versions to older versions, it says so in the documentation links I posted originally. However, I seem to have encountered a special temporary case that would need cyclic replication. I have added comments to my original post about it – user1805458 Nov 19 '14 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.