Correct way to put a MySQL replicated environment safely into maintenance mode?
I need to perform some maintenance on our master/slave/slave MySQL cluster. The maintenance involves optimising three tables, at least one of which is being locked for a duration of approximately 2 hours. The optimise operation for each table runs to completion on the master and then propagates to the slaves in turn via the binary log when complete.
The way I see it I currently have 3 options:
Restart master with skip-networking, run the optimise and then bring it back online afterwards and allow the slaves to catch up.
Bring all 3 nodes offline concurrently, run the optimise operations in parallel in parallel, and reset replication afterwards.
Cleanly stop MySQL on the nodes, shut off networking to the outside world, bring them back up, run the optimise on master and allow it to propagate to the slaves before restoring network access.
I’m struggling to find any documentation on best practices for this, or the exact behaviour of the shutdown, is anybody able to confirm the best way to safely bring the boxes offline to perform the necessary work?
Performing the operation cleanly is of bigger concern than the time taken, as the maintenance will be performed overnight.
The MySQL version is 5.6, it unfortunately is an older version, plans are in place to upgrade in the near future but I’m stuck with it for the time being. The replication method is GTID.
Many thanks.
Subject
Views
Written By
Posted
Correct way to put a MySQL replicated environment safely into maintenance mode?
618
June 08, 2022 07:20AM
347
June 14, 2022 02:19PM
364
June 16, 2022 06:49AM
318
June 16, 2022 11:31AM
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.