Upgrading Master-Slave MySQL

Question

I'm experimenting with MySQL replication with MySQL 5.5.52. My main problem is: how to do "apt-get upgrade" on a master-slave setup properly?

Until now I upgraded the slave then failed over and upgraded the other node as a slave.

So far It worked without problems, but now I found the following: As I upgraded the slave it created some binlogs:

mysql.CREATE TABLE IF NOT EXISTS db (...)
mysql.CREATE TABLE IF NOT EXISTS host (...)
mysql.CREATE TABLE IF NOT EXISTS func (...)
...

As this was the slave node these changes are not replicated to the master, they are lost after the failover. If I repeat this on the other node after its role is changed from master to slave, the same will happen.

So the changes made by the upgrade will never be committed to the replicated database.

Is this an issue?

How can I avoid this?

Can I upgrade the master-slave setup without (significant) downtime, at all?

Update: So what happens if I do it like I did it in the past?

Node A : master
Node B : slave

On node B (slave): I issue apt-get upgrade, MySQL gets patched. Some binlogs appear on Node B, containing the statements like above. As this is a slave these changes are not replicated to the master.

After Node B is patched I switch the roles (with Corosync-Pacemaker).

After the failover:

Node A : slave
Node B : master

Now the Master MySQL on Node B contains the data that was on Node A prior the failover. As the changes caused by the patching were on a slave, they are NOT in the DB.

If I now patch Node A as a slave the same happens.

So in the end I will have a patched MySQL containing the exact same data it contained prior the patch. All changes the apt-get upgrade process made are "discarded".

^this is wrong^

So it is not discarded, but only applied as soon as the Slave (B) becomes master.

Does this mean I should not wait after patching one node (so patch them both in one run) and always keep the MySQL versions the same?

Answer 1

You cannot switch roles in the middle of an update between major versions. MySQL-5.6 will generate binary log messages that MySQL-5.5 won't understand. So if 5.6 is every a master of a 5.5 slave you can expect replication to break.

At all stages slave version must be >= master version.

So update the slave by one major version 5.5 -> 5.6. Ensure everything is running correctly. Then update the master by one major version. The binary log messages generated with the update will have no effect on the slave already updated.

While it might be tempting to make a new node C and go through convoluted update process your are better of using a test environment, doing a slave update, a master upgrade, start slave (and repeat until at the latest major version you want). Practices a few times. Test your applications against the final version. Schedule a small outage and run through the steps prepared and tested.

Have a fallback plan, i.e package/config/datadir of original saved and ready to put into place). Test your fallback plan.