Mysql – Data Consistency Between Two MySQL Databases Running Different Versions

migrationMySQL

I need to migrate an old web application using old PHP and MySQL versions.

I am intending to start by migrating the code that interacts with the database to a new server with the latest versions and allow the old server to use an API in the new server. Ideally I would like to have the data consistent between the database in the new server and the one in the old server.

It seems that replication only works only when the master is only one version behind the slave. In this case the difference is a way more than one version. I also want changes to the database on the new server to reflect on the database on the old server.

Are there any tools/techniques to help with this situation other than ensuring the data consistency programmatically with cron jobs?

Best Answer

Plan A: Do it one step at a time.

Plan B: Set up a long replication chain: 5.5 -> 5.6 -> 5.7

Plan C: Take some downtime to do it via a dump.

Plan D: "Update in place". That is, install the next version on the existing version, keeping the data intact. Be sure to run mysql_upgrade. Repeat.

Caution: The upgrade from 5.7 to 8.0 may be the trickiest.

Caution: If you are using PHP's mysql_* interface, it would probably be better to switch to mysqli_* first. mysql_* has been removed from php 7.

I also want changes to the database on the new server to reflect on the database on the old server

Abandon that goal. There are too many risks in using Dual Master with different versions -- primarily because the "Slave" needs to be no older than the "Master".

Get a separate machine to practice on.

Related Question