Mysql – Upgrading Master-Slave MySQL

MySQLreplicationupgrade

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?

Best Answer

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.

Related Solutions

Mysql – Master Slave Replication in Mysql

The simple answer is No.

In Mysql replication, Master copies the bin log files to slaves, and after that, it's work is over. Now the Slave will run the bin files and execute them, but there won't be any performance on Master.

There might be scenario where you are using full synchronous replication, in which master will wait for the slave to execute the query, but again it won't impact the performance in terms of memory or CPU, but the master will wait for the query to be executed.

Also, for your second question, Phil already answered it, that ssh sends data through encryption which uses a lot of CPU, hence if you want other ways, use the other methods which are described by Phil.

Mysql – replication breaks after upgrading master

The mysql_upgrade program tries to realign the mysql.user table with latest grants for that MySQL Major Release. Since you only did a minor upgrade (5.1.30 -> 5.1.47) the mysql_upgrade was needless. The grant tables for the Slave have no bearing on Replication since the Slave has to authenticate back at the Master.

Fixing MySQL Replication is just a case of repointing replication from the last know executable position and starting from there. Here is how you do this:

Run SHOW SLAVE STATUS\G and choose the following two fields

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000504
        Read_Master_Log_Pos: 326579313
             Relay_Log_File: relay-bin.081210
              Relay_Log_Pos: 326579450
      Relay_Master_Log_File: mysql-bin.000504 <--- Choose This
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 326579313 <--- Choose That
            Relay_Log_Space: 326579450
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

From the above display, your would choose mysql-bin.000504 and 326579450.

Simply do the following on the Slave

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000504',master_log_pos=326579450;
START SLAVE;

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Master Slave Replication in Mysql

Mysql – replication breaks after upgrading master

Related Question