MySQL Forums
Forum List  »  Replication

Correct way to put a MySQL replicated environment safely into maintenance mode?
Posted by: Alex Walker
Date: June 08, 2022 07:20AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Correct way to put a MySQL replicated environment safely into maintenance mode?
77
June 08, 2022 07:20AM


Sorry, only registered users may post in this forum.

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.