5

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?

  • well the steps you are following seem to be fine as if any issue being encountered at slave would not affect the operational master and moreover mysql_upgrade needs to be executed in isolated mode as you are doing it. – Nawaz Sohail Oct 3 '16 at 9:01
  • So these changes made by the upgrade process can be safely dropped? Are the mysql.CREATE TABLE IF NOT EXISTSs and the performance_schema changes not necessary? As I do it they are discarded at the point the slave becomes the new master. – scream314 Oct 3 '16 at 9:22
  • which version you are using? moreover why you need to drop those changes as this might not be the good idea to play with internal structured so would recommend not to explicitly drop those. – Nawaz Sohail Oct 3 '16 at 10:02
  • I updated my question with details. I do not explicitly drop the changes, they are "lost". I agree this is not a good idea. Should I maybe patch both nodes, then apply the (automatic) changes manually? – scream314 Oct 3 '16 at 11:36
  • My understanding how the upgrade process is working was completely wrong. – scream314 Oct 3 '16 at 12:12
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.

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.