当前位置：首页 > 文章列表 > 数据库 > MySQL > 分享下MySQL MHA集群怎么无故障迁移

分享下MySQL MHA集群怎么无故障迁移

来源：SegmentFault 2023-01-19 16:28:58 0浏览收藏

本篇文章向大家介绍《分享下MySQL MHA集群怎么无故障迁移》，主要包括MySQL、MHA，具有一定的参考价值，需要的朋友可以参考一下。

MySQL的主从复制功能(replication)可以配置一台机器为master，另外一台或者多台机器作为Slave，从master同步数据。整个过程主要包括以下几点
1. Master将update event写入bin-log
2. Master通过Binlog Dump线程将update event发给Slave
3. Slave通过IO Thread读取Master发送过来的update event并写入relay-log
4. Slave通过SQL Thread读取relay-log将update event应用到Slave

可以看到MySQL的replication是异步的，Master只负责将update event写入bin-log，至于Binlog Dump线程什么时候把event发送给Slave以及Slave什么时候把event应用在自己端，它其实是不会管的。所以极有可能update event已经在Master上生效，但是Slave上却没有的情况发生。

MySQL自5.5开始提供了半同步的复制(semisynchronous replication)，客户端在发送update event给Master后，Master必须保证至少一个Slave已经接收到了update event才能将update commit，但也有一个例外，如果所有的Slave都已经死了或者网络不通，那么Master在经过一定的时间(

在第一台Master上

update tb1 set col = col + 1;

在第二台Master上

在hh1, hh2, hh3, hh4上分别执行

ssh-keygen -t rsa ssh-copy-id -i .ssh/id_rsa.pub hh1 ssh-copy-id -i .ssh/id_rsa.pub hh2 ssh-copy-id -i .ssh/id_rsa.pub hh3 ssh-copy-id -i .ssh/id_rsa.pub hh4

下载mha manager和node 在Google Driver

在hh1，hh2， hh3上安装node

Configuration check: 重新检查一遍配置文件，因为master死机可能是在整个系统上线后很长时间才发生的，比如说在两个月后，那么在这段时间内很有可能MySQL集群的配置已经改变，所以需要重新检查一遍配置文件

Dead master shutdown: 强制关闭Manager认为已经死机的master

master_ip_failover_script 在关闭已经死机的master的时候会调用这个用户可自定义的脚本，可以用来做IP转移

Master Recovery: 新的master恢复阶段

Apply log(node): 由node来应用diff log，以使新的master的数据跟死掉的master的数据是一致的
master_ip_failover_script: 然后再调用一次用户自定义的脚本，同样可以用来做IP转移
Getting Latest (and oldest) Slaves: 检查所有的slave的状态，得到同步最新和最旧的slave
Saving Dead Master's Binlog(node): 如果死机的master还能通过SSH访问，那么，Manager会通过node取得它的bin-log
Determining New Master(latest? candidate master?): 选取新的master，有两种策略，第一种是最简单的，如果用户在配置文件里配置了哪台slave作为candidate master，那么直接用那台slave做新的master，第二种是在用户没有配置的情况下用的，选择同步到最新的slave作为master
New Master Diff Log Generation: 因为node已经取得了死掉的master的bin-log，所以可以跟新选择的master的log进行比较，生成diff log
Master Log Apply: 将diff log应用在新的master上

Slaves Recovery: 恢复所有的slave阶段

Apply log(node): 由node来应用diff log
Change master to…: 将slave的master指向新的master
Starting Parallel Slave Diff Log Generation: 同样的生成diff log，因为可能有多个slave，所以是parallel(并行)的
Starting Parallel Slave Log Apply: 将diff log应用在slave上

New master cleanup: 新的master原来是slave，所以要清掉他的slave信息

整个过程就是这样的，不是很复杂，也不是很简单，这个过程基本上可以保证数据不会丢失，同时所有的slave都能同步到一样的状态。可以查看log日志验证以上步骤，log比较长，已经做了删减，不想看的可以忽略：

Sat Jun 28 01:57:07 2014 - [info] Starting master failover. Sat Jun 28 01:57:07 2014 - [info] Sat Jun 28 01:57:07 2014 - [info] * Phase 1: Configuration Check Phase.. Sat Jun 28 01:57:07 2014 - [info] Sat Jun 28 01:57:07 2014 - [info] Dead Servers: Sat Jun 28 01:57:07 2014 - [info] hh1(10.3.40.102:3306) Sat Jun 28 01:57:07 2014 - [info] Checking master reachability via mysql(double check).. Sat Jun 28 01:57:07 2014 - [info] ok. Sat Jun 28 01:57:07 2014 - [info] Alive Servers: Sat Jun 28 01:57:07 2014 - [info] hh2(10.3.40.37:3306) Sat Jun 28 01:57:07 2014 - [info] hh3(10.3.40.71:3306) Sat Jun 28 01:57:07 2014 - [info] ** Phase 1: Configuration Check Phase completed. Sat Jun 28 01:57:07 2014 - [info] Sat Jun 28 01:57:07 2014 - [info] * Phase 2: Dead Master Shutdown Phase.. Sat Jun 28 01:57:07 2014 - [info] Sat Jun 28 01:57:07 2014 - [info] Forcing shutdown so that applications never connect to the current master.. Sat Jun 28 01:57:07 2014 - [info] Executing master IP deactivatation script: Sat Jun 28 01:57:07 2014 - [info] /usr/share/bi/master_ip_failover.sh --orig_master_host=hh1 --orig_master_ip=10.3.40.102 --orig_master_port=3306 --command=stopssh --ssh_user=root Sat Jun 28 01:57:08 2014 - [info] done. Sat Jun 28 01:57:08 2014 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Sat Jun 28 01:57:08 2014 - [info] * Phase 2: Dead Master Shutdown Phase completed. Sat Jun 28 01:57:08 2014 - [info] Sat Jun 28 01:57:08 2014 - [info] * Phase 3: Master Recovery Phase.. Sat Jun 28 01:57:08 2014 - [info] Sat Jun 28 01:57:08 2014 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Sat Jun 28 01:57:08 2014 - [info] Sat Jun 28 01:57:08 2014 - [info] The latest binary log file/position on all slaves is binlog.000002:120 ... Sat Jun 28 01:57:08 2014 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Sat Jun 28 01:57:08 2014 - [info] Sat Jun 28 01:57:09 2014 - [info] Fetching dead master's binary logs.. Sat Jun 28 01:57:13 2014 - [info] scp from root@10.3.40.102:/usr/share/bi/app1/saved_master_binlog_from_hh1_3306_20140628015707.binlog to local:/usr/share/bi/app1/saved_master_binlog_from_hh1_3306_20140628015707.binlog succeeded. Sat Jun 28 01:57:17 2014 - [info] * Phase 3.3: Determining New Master Phase.. Sat Jun 28 01:57:17 2014 - [info] Searching new master from slaves.. Sat Jun 28 01:57:17 2014 - [info] Candidate masters from the configuration file: Sat Jun 28 01:57:17 2014 - [info] Non-candidate masters: Sat Jun 28 01:57:17 2014 - [info] New master is hh2(10.3.40.37:3306) Sat Jun 28 01:57:17 2014 - [info] * Phase 3.3: New Master Diff Log Generation Phase.. Sat Jun 28 01:57:19 2014 - [info] * Phase 3.4: Master Log Apply Phase.. Sat Jun 28 01:57:19 2014 - [info] Starting recovery on hh2(10.3.40.37:3306).. Sat Jun 28 01:57:19 2014 - [info] Generating diffs succeeded. Sat Jun 28 01:57:19 2014 - [info] Waiting until all relay logs are applied. Sat Jun 28 01:57:21 2014 - [info] ** Finished master recovery successfully. Sat Jun 28 01:57:21 2014 - [info] * Phase 3: Master Recovery Phase completed. Sat Jun 28 01:57:21 2014 - [info] * Phase 4: Slaves Recovery Phase.. Sat Jun 28 01:57:21 2014 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Sat Jun 28 01:57:21 2014 - [info] -- Slave diff file generation on host hh3(10.3.40.71:3306) started, pid: 29373. Check tmp log /usr/share/bi/app1/hh3_3306_20140628015707.log if it takes time.. Sat Jun 28 01:57:21 2014 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Sat Jun 28 01:57:21 2014 - [info] -- Slave recovery on host hh3(10.3.40.71:3306) started, pid: 29375. Check tmp log /usr/share/bi/app1/hh3_3306_20140628015707.log if it takes time.. Sat Jun 28 01:57:24 2014 - [info] scp from local:/usr/share/bi/app1/saved_master_binlog_from_hh1_3306_20140628015707.binlog to root@hh3:/usr/share/bi/app1/saved_master_binlog_from_hh1_3306_20140628015707.binlog succeeded. Sat Jun 28 01:57:24 2014 - [info] Starting recovery on hh3(10.3.40.71:3306).. Sat Jun 28 01:57:24 2014 - [info] Generating diffs succeeded. ... Sat Jun 28 01:57:26 2014 - [info] -- Slave recovery on host hh3(10.3.40.71:3306) succeeded. Sat Jun 28 01:57:26 2014 - [info] All new slave servers recovered successfully. Sat Jun 28 01:57:26 2014 - [info] Sat Jun 28 01:57:26 2014 - [info] * Phase 5: New master cleanup phase.. Sat Jun 28 01:57:26 2014 - [info] Sat Jun 28 01:57:26 2014 - [info] Resetting slave info on the new master.. Sat Jun 28 01:57:26 2014 - [info] hh2: Resetting slave info succeeded. Sat Jun 28 01:57:26 2014 - [info] Master failover to hh2(10.3.40.37:3306) completed successfully.

MHA的优点：