MySQL Forums
Forum List  »  Replication

Re: upgrading mysql 5.0 to 5.5 on master-master setup
Posted by: Justin Ryan
Date: August 01, 2012 01:16PM

funny, I was just going to post the exact same question, except we're only going 5.1->5.5.

We used the additional servers method Aftab suggested to go 5.0->5.1. While it worked and was very safe, it involves a ton of copying, which takes a long time and plays havoc on our backups. We have dozens of pairs with hundreds of GB each.

Here is the method I am considering, with no downtime. NODE1 accepts application traffic, NODE2 is only a standby. (EDIT: Since you use NODE1 as a master for one schema and slave for another, and opposite for NODE2, I'm assuming you could temporarily use one node as master for both schemas during the upgrade process.)

1. Break master-master NODE2->NODE1 replication: `change master to master_host = ''` on NODE1

2. stop slave, stop mysql, upgrade RPMs on NODE2.

3. start mysql (--skip-slave-start) and run mysql_upgrade (we are actually upgrading to MariaDB 5.5, whose mysql_upgrade script runs the `ALTER TABLE ENGINE=innodb` upgrades for you, otherwise run that too for innodb tables). Restart mysql, check errorlog.

4. start slave on NODE2, catch up. Capture NODE2 log position.

5. point application traffic at NODE2, which is replicating from NODE1.

6. Break replication NODE1->NODE2: `change master to master_host = ''` on NODE2. No replication set up at all now.

7. upgrade NODE1 as above.

8. change master on NODE1 to the position captured in step 4. catch up. Capture NODE1 log position.

9. change master on NODE2 to log position captured in step 8, re-establishing master-master.

10. point application traffic back to NODE1

Breaking replication ensures that whatever mysql_upgrade or you do to upgrade tables does not get logged and therefore replicated. Though, looking through the mysql_upgrade manpage, it looks like you can use --skip-write-binlog to prevent this as well ( --write-binlog is the default).

What do you think?



Edited 2 time(s). Last edit at 08/01/2012 01:22PM by Justin Ryan.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: upgrading mysql 5.0 to 5.5 on master-master setup
2154
August 01, 2012 01:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.