大网数据广告活动
服务器运维问答

Correct way to put a MySQL replicated environment safely into maintenance mode?

· 52CE 我爱测

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 f…

问题原文

原帖链接 · motorleague · CC BY-SA 4.0

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:

  1. Restart master with skip-networking, run the optimise and then bring it back online afterwards and allow the slaves to catch up.
  2. Bring all 3 nodes offline concurrently, run the optimise operations in parallel in parallel, and reset replication afterwards.
  3. 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.

已采纳解答

原帖链接 · Rick James · CC BY-SA 4.0

First of all, do you really need to run OPTIMIZE TABLE? In most situations, InnoDB keeps its tables clean enough to never need Optimize. If you are using MyISAM, you should convert to InnoDB for a variety of reasons.

If the Optimize is because you did a big DELETE, then [next time] do the Delete in a less intrusive way, which may eliminate the need to Optimize. See http://mysql.rjweb.org/doc.php/deletebig .

OK, back to your question. There is a replication setting that prevents the propagation of commands to the Replicas. https://dev.mysql.com/doc/refman/8.0/en/set-sql-log-bin.html So, do this on the Primary:

# when logged in with sufficient privileges:
SET sql_log_bin = OFF;
OPTIMIZE TABLE ...;
SET sql_log_bin = ;

Then go through the Replicas, one at a time: Take a Replica offline and manually run OPTIMIZE. Put back online.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2022-06-08)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。