MySQL高可用方案MHA自动Failover与手动Failover的实践及原理

概述集群信息 角色 IP地址 ServerID 类型 Master 192.168.244.10 1 写入 Candicate master 192.168.244.20 2 读 Slave 192.16

集群信息

角色                             IP地址                 ServerID      类型

Master                         192.168.244.10   1                 写入

Candicate master          192.168.244.20   2                 读

Slave                           192.168.244.30   3                 读

Monitor host                 192.168.244.40                      监控集群组

 

MHA具体的搭建步骤和原理，可参考另外一篇博客：

MySQL高可用方案MHA的部署和原理

 

自动Failover

为了通过MHA的日志清晰判断MHA自动Failover的实现原理，需模拟如下场景：

当主库发生故障时，master中还有一部分binlog日志没有传输到Candicate master和Slave上，且Slave上的二进制日志多于Candicate master上。

尝试了几种方案，总算如愿以偿。

 

方案一：

     1. 关闭Candicate master和Slave的主从复制。

     2. 通过存储过程生成测试数据

     3. 开启Candicate master和Slave的主从复制并kill掉master的MysqLd进程，模拟主库发生故障，进行自动failover *** 作

     为此，还特意写了个脚本，可惜的是，效果并不理想，在自动Failover的过程中，显示MHA Manager到master的“SSH is NOT reachable”。

 

方案二：

     通过tc命令对Candicate master和Slave的网卡分别设置不同的传输速率，确保发送到Candicate master的日志量小于Slave上的。

     很可惜，效果同方案一一样。

 

方案三：

      在主从复制中，直接关闭master的MysqLd数据库。

      但是，通过这样方式，并不会实现Slave上的二进制日志多于Candicate master的效果。

 

方案四：

      通过截取relay log，在关掉slave的情况下，修改master.info和relay-log.info的位置点来人为制造Candicate master的日志量小于Slave的。

      事后想想，其实这样的方法就等同于先关闭Candicate master的主从复制，再在master上执行一段 *** 作，再关闭slave上的主从复制，再在master上执行一段 *** 作。

      这样不就实现了master的binlog > slave 的relay log > Candicate master的relay log。

      方案四总算如愿以偿

    

下面通过方案四看看MHA的实现原理

1. 创建测试表，并插入测试数据

MysqL> create table sbtest.b(ID int,name varchar(10));query OK,0 rows affected (0.12 sec)MysqLinsert into sbtest.b values(1,'a');query OK,1); Font-weight: bold">1 row affected (0.002,1)">b0.013,1)">c4,1)">d5,1)">e0.01 sec)

 

2. 分别查看master上binlog的内容，slave上relay log的内容

   Master

MysqL> show binlog events;+------------------+------+-------------+-----------+-------------+------------------------------------------------+| Log_name         | Pos  | Event_type  | Server_ID | End_log_pos | Info                                           || MysqL-bin.000001 |    4 | Format_desc |         1 120 | Server ver: 5.6.31-log,binlog ver: 4          |  | query       238 | 10)) 315 BEGIN                                          421 ')             | XID         452 COMMIT /* xID=102 */                           529 635 666  xID=103 743 849 880  xID=104 957 |        1063 | 1094  xID=105 1171 1277 1308  xID=106 17 rows in set (0.01 sec)

   Slave

MysqL> show relaylog events in MysqLd-relay-bin.000002;-----------------------+------+-------------+-----------+-------------+------------------------------------------------+| Log_name                | MysqLd-relay000002 2 | Rotate      |           0 000001;pos=120                       167 283 401 478 584 615 692 798 829 906 1012 1043 1120 1226 1257 1334 1440 19 rows 0.00 sec)

     通过对比master中的binlog event，可以看到show relaylog events中的End_log_pos实际上指的是对应的二进制事件在binlog的位置。

     再来查看Candicate master中对应的relay log的内容

     [root@node2 MysqL]# MysqLbinlog MysqLd-relay-bin.000002

[root@node2 MysqL]# MysqLbinlog MysqLd-relay-bin.000002!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;!40019 SET @@session.max_insert_delayed_threads=0!50003 SET @olD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0;DEliMITER !;# at 4#170524 17:16:37 server ID 2  end_log_pos 120 CRC32 0x4faba9ae     Start: binlog v 4,server v 5.6.31-log created 37binlog 'dU8lWQ8CAAAAdAAAAHgAAABAAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAa6pq08='/*!*/;# at 120700101  8:00:00 server 1  end_log_pos 0 CRC32 0x74c6d70c     Rotate to MysqL-bin.000001  pos: # at 16715:49 server 0xed2672eb     Start: binlog v 49RU8lWQ8BAAAAdAAAAAAAAAAAAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAetyJu0=28320 server 238 CRC32 0xdd48c118     query    thread_ID=2    exec_time=0    error_code=0SET TIMESTAMP=1495617440;SET @@session.pseudo_thread_ID=2;SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1;SET @@session.sql_mode=1075838976;SET @@session.auto_increment_increment=!\C utf8 *//*;SET @@session.character_set_clIEnt=33,@@session.collation_connection=33;SET @@session.lc_time_names=;SET @@session.collation_database=DEFAulT;create table sbtest.b(ID 10))40127 server 315 CRC32 0xae393750     query    thread_ID=1495617447;BEGIN478421 CRC32 0x28a781ae     query    thread_ID=;insert into sbtest.b values(a)584452 CRC32 0x680f1bfe     XID = 29COMMIT61533 server 529 CRC32 0x6a1aae7e     query    thread_ID=1495617453692635 CRC32 0x117786ca     query    thread_ID=2,1)">b798666 CRC32 0xa8400ec6     XID = 3082938 server 743 CRC32 0x24f9a1d2     query    thread_ID=1495617458906849 CRC32 0x56fa9e89     query    thread_ID=3,1)">c1012880 CRC32 0x2ac656d4     XID = 31104344 server 957 CRC32 0x73a903bf     query    thread_ID=149561746411201063 CRC32 0x171b9b27     query    thread_ID=d12261094 CRC32 0x47d6fe57     XID = 3212571171 CRC32 0x2d37da37     query    thread_ID=149561746913341277 CRC32 0xd2201fa2     query    thread_ID=5,1)">e14401308 CRC32 0xac1b464e     XID = ;DEliMITER ;# End of log fileRolLBACK  added by MysqLbinlog !50003 SET COMPLETION_TYPE=@olD_COMPLETION_TYPE!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

VIEw Code

     MysqL中binlog有个有意思的地方是，位置点其实是也是字节的大小。

     譬如，上面这个relay log中，最后一个位点是# at 1440，算上最后一个commit *** 作需占用31个字节，所以整个文件的大小是1471，与实际大小吻合。

[root@node2 MysqL]# ll MysqLd-relay-bin.000002-rw-rw---- 1 MysqL MysqL 1471 May 24 17 MysqLd-relay-bin.000002

 

3. 通过show slave status查看io thread和sql thread的位置信息

MysqL show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.244.10                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_file: MysqL000001          Read_Master_Log_Pos: 1308               Relay_Log_file: MysqLd000002                Relay_Log_Pos: 1471        Relay_Master_Log_file: MysqL             Slave_IO_Running: Yes            Slave_sql_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_table:        Replicate_Ignore_table:       Replicate_Wild_Do_table:   Replicate_Wild_Ignore_table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter:           Exec_Master_Log_Pos:               Relay_Log_Space: 1645              Until_Condition: None               Until_Log_file:                 Until_Log_Pos:            Master_SSL_Allowed: No           Master_SSL_CA_file:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: Master_SSL_Verify_Server_Cert: No                Last_IO_Errno:                 Last_IO_Error:                Last_sql_Errno:                Last_sql_Error:   Replicate_Ignore_Server_IDs:              Master_Server_ID: 1                  Master_UUID: 2a6365e0-1d05-11e7-956d-000c29c64704             Master_Info_file: /var/lib/MysqL/master.info                    sql_Delay:           sql_Remaining_Delay: NulL      Slave_sql_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_sql_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            RetrIEved_GtID_Set:             Executed_GtID_Set:                 auto_position: 01 row 0.00 sec)

待会儿需要修改上面Master_Log_file,Read_Master_Log_Pos,Relay_Log_file,Relay_Log_Pos,Relay_Master_Log_file,Exec_Master_Log_Pos的值。

虽然这几个参数的值与master.info和relay-log.info文件是相对应的，

但通过修改master.info和relay-log.info的值，并重启slave，并不会将上述几个参数值修改。

上述几个参数是保存到内存中的，唯一可行的方案是首先关闭slave实例，再修改master.info和relay-log.info文件，然后重新启动MysqL实例。

 

4. 关闭Candicate master实例，剪裁relay log，修改master.info和relay-log.info文件。

   关闭实例

   [root@node2 MysqL]# service MysqLd stop

   剪裁relay log

   这里，写了个python脚本实现该功能

#!/usr/bin/pythonf1 = open(MysqLd-relay-bin.000002',1)">r)f2 = open(tmp_relay_binw+)size1=f1.read(615f2.write(size1)f1.seek(1471)size2=f1.read()f2.write(size2)f1.close()f2.close()

   在上述脚本size1中，615对应的是insert into sbtest.b values(1,'a')这条记录

   [root@node2 MysqL]# python 1.py 

   [root@node2 MysqL]# mv tmp_relay_bin MysqLd-relay-bin.000002

   修改master.info的内容

   主要是修改第三行

23MysqL-bin.0000011308

    修改为

452

    修改relay-log.info的内容

    原文件如下：

7./MysqLd-relay-bin.0000021471130801

    修改为：

4521

   启动slave，注意，配置文件中必须设置skip-slave-start，不然它自动开启主从复制。

   [root@node2 MysqL]# service MysqLd start

 

5. 对于slave，同样如此处理，只不过relay log的位置点要靠后些

)size1=f1.read(1043)f2.write(size1)f1.seek(1471f1.read()f2.write(size2)f1.close()f2.close()

   1043对应的是insert into sbtest.b values(3,'c')这条记录

   修改master.info的内容

23MysqL-bin.000001880

   修改relay-log.info的内容 

7./MysqLd-relay-bin.0000021043MysqL-bin.000001880001

   

6. 开启MHA监控    

# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null >
/masterha/app1/manager.log 2>&1 &

# masterha_check_status --conf=/etc/masterha/app1.cnf 

app1 (pID:1615) is running(0:Ping_OK),master:192.168.244.10

 

7. 关闭master实例

# service MysqLd stop
 

8. 查看MHA切换日志，了解整个切换过程。

    该日志的时间点与上面relay log的时间点并不吻合，原因在于这个反复测试了很多次。

Sun May 21 20:50:46 2017 - [warning] Got error on MysqL connect: 2013 (Lost connection to MysqL server at reading initial communication packet111)Sun May 2017 - [warning] Connection Failed 1 time(s)..Sun May 2017 - [info] Executing secondary network check script: /usr/local/bin/masterha_secondary_check -s 192.168.244.20 -s 244.30 --user=root --master_host=244.10 --master_ip=244.10 --master_port=3306  --user=root  --master_host=244.10  --master_ip=244.10  --master_port=3306 --master_user=monitor --master_password=monitor123 --Ping_type=SELECTSun May info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/MysqL --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --binlog_prefix=MysqL-binMonitoring server 244.20 is reachable,Master is not reachable from 244.20. OK.Sun May info] HealthCheck: SSH to 244.10 is reachable.Monitoring server 244.30 is reachable,1)">244.30. OK.-- 当monitor检测到master MysqLd不可用的时候，即根据masterha_secondary_check脚本从Candicate master和Slave上判断master MysqLd的可用性，根据上面的显示信息，通过192.168.244.20和192.168.244.30也判断到master MysqLd不可用。如果任意一个slave判断到master MysqLd可用，则输出的信息如下：  # /usr/local/bin/masterha_secondary_check -s 3306  Master is reachable from 244.20!  Sun May info] Master is not reachable from all other monitoring servers. Failover should start.Sun May 47 2 48 3 49 4 2017 - [warning] Master is not reachable from health checker!Sun May 2017 - [warning] Master 244.10(244.10:3306) is not reachable!2017 - [warning] SSH is reachable.-- 一共判断了4次，均判断master MysqLd不可用，但是master主机通过ssh还是能登录上去。    如果这里显示的是SSH is NOT reachable，则代表master主机也已经宕机了，    刚开始还以为是通过ssh来判断主机是否宕机，但在之前的测试方案中（具体可见文末），master并没有宕机，这里却显示SSH is NOT reachable.    通过上面的输出才知道是通过save_binary_logs脚本来判断ssh可用性的。Sun May info] Connecting to a master server Failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again,and trying to connect to all servers to check server status..Sun May 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. SkipPing.Sun May info] Reading application default configuration from /etc/masterha/app1.cnf..Sun May info] Reading server configuration from /etc/masterha/50 2017 - [warning] sql Thread is stopped(no error) on 244.20(244.20:244.30(244.30:info] GTID failover mode = ] Dead Servers:Sun May info]   ] Alive Servers:Sun May ] Alive Slaves:Sun May 3306)  Version=31-log (oldest major version between slaves) log-bin:enabledSun May info]     Replicating from info]     Primary candIDate for the new Master (candIDate_master is set)Sun May ] Checking slave configurations..Sun May ] Checking replication filtering settings..Sun May ]  Replication filtering check ok.Sun May info] Master is down!] Terminating monitoring script.Sun May info] Got exit code 20 (Master dead).Sun May info] MHA::MasterFailover version 0.56.Sun May ] Starting master failover.-- 读取MHA的配置文件，检查slave的相关配置，比如read_only参数，是否设置了复制的过滤规则   从上面的输出中可以看出，sql Thread正常停止了并不影响MHA的切换。Sun May ] Sun May info] * Phase : Configuration Check Phase..Sun May 51 info] Checking master reachability via MysqL(double check)...Sun May ]  ok.Sun May info]  Starting sql thread on ) ..Sun May info]   doneinfo] Starting Non-GTID based failover.Sun May info] ** Phase : Configuration Check Phase completed.-- 第一阶段，检查了MHA的配置信息，并再次判断了master的可用性。   第二阶段，关闭dead master。   包括执行摘除master上的vip，同时执行shutdown_script脚本，因为该脚本在配置文件中没有定义，故跳过。   Sun May : Dead Master Shutdown Phase..Sun May ] Forcing shutdown so that applications never connect to the current master..Sun May ] Executing master IP deactivation script:Sun May info]   /usr/local/bin/master_ip_failover --orig_master_host=244.10 --orig_master_ip=244.10 --orig_master_port=3306 --command=stopssh --ssh_user=root  disabling the VIP an old master:  SIOCSIFFLAGS: Cannot assign requested addressSun May info]   [warning] shutdown_script is not set. SkipPing explicit shutting down of the dead master.Sun May : Dead Master Shutdown Phase completed.Sun May ] -- 第三阶段   3.1 判断哪个slave的二进制日志是最新的。       通过下面的输出可以看出，所有的slave中，最新的二进制日志位置是MysqL-bin.000001:880（通过show slave status中的Master_Log_file,Read_Master_Log_Pos得到）       最旧的二进制日志位置是MysqL-bin.4523: Master Recovery Phase..Sun May : Getting Latest Slaves Phase..Sun May info] The latest binary log file/position on all slaves is MysqL-bin.] Latest slaves (Slaves that received relay log files to the latest):Sun May info] The oldest binary log ] oldest slaves:Sun May ] -- 3.2 保存master的binlog       注意，上面已经判断到slave中最新二进制日志是MysqL-bin.880，所以它把该位置后所有二进制日志都拼接起来，并scp到monitor的/masterha/app1目录下。   Sun May 3.2: Saving Dead Masters binlog Phase..Sun May 52 info] Fetching dead masters binary logs..Sun May info] Executing command on the dead master 3306): save_binary_logs --command=save --start_file=MysqL-bin.000001  --start_pos=880 --binlog_dir=/var/lib/MysqL --output_file=/tmp/saved_master_binlog_from_192.168.244.10_3306_20170521205050.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=  Creating /tmp if not exists..    ok. Concat binary/relay logs from MysqL-bin.000001 pos 880 to MysqL-bin.000001 EOF into /tmp/saved_master_binlog_from_192..10_3306_20170521205050.binlog .. binlog Checksum enabled  DumPing binlog format description event,from position 0 to .. ok.  DumPing effective binlog data from /var/lib/MysqL/MysqL-bin.000001 position 880 to tail(1308).. ok. binlog Checksum enabled Concat succeeded.Sun May info] scp from root@244.10:/tmp/saved_master_binlog_from_192.168.244.10_3306_20170521205050.binlog to local:/masterha/app1/saved_master_binlog_from_192..10_3306_20170521205050.binlog succeeded.Sun May  is reachable.Sun May 53 3.3 选新主阶段       首先判断最新的slave中是否包括最旧的二进制日志（MysqL-bin.）以后的relay log。       接着选新主，       因为192.168.244.20中设置了candIDate_master设置了，所以192..20被指定为新主。  Sun May : Determining New Master Phase..Sun May info] Finding the latest slave that has all relay logs  recovering other slaves..Sun May info] Checking whether  has relay logs from the oldest position..Sun May info] Executing command: apply_diff_relay_logs --command=find --latest_mlf=MysqL-bin.000001 --latest_rmlp=880 --target_mlf=MysqL-bin.000001 --target_rmlp=452 --server_ID=3 --workdir=/tmp --timestamp=20170521205050 --manager_version=0.56 --relay_log_info=/var/lib/MysqL/relay-log.info  --relay_dir=/var/lib/MysqL/  :    opening /var/lib/MysqL/relay-log. ... ok.    Relay log found at /var/lib/MysqL,up to MysqLd-relay-bin.000003 Fast relay log position search Failed. Reading relay logs to find..Reading MysqLd-relay-bin. binlog Checksum enabledReading MysqLd-relay-bin.000002 binlog Checksum enabled Master Version is 31-log binlog Checksum enabled MysqLd-relay-bin.000002 contains master MysqL-bin.000001 from position Target relay log FOUND!info] OK.  has all relay logs.Sun May ] Searching new master from slaves..Sun May info]  CandIDate masters from the configuration :Sun May info]  Non-candIDate masters:Sun May info]  Searching from candIDate_master slaves which have received the latest relay log events..Sun May ]   Not found.Sun May ]  Searching from all candIDate_master slaves..Sun May info] New master is ] Starting master failover..Sun May ] From:) (current master) +--) +--)To:) (new master) +-- 获取新主所需的差异二进制日志，包括两部分       1> 新主和最新的slave之间差异的relay log       2> 保存在MHA Manager上的最新的slave和原master之前差异的binlog       其中，差异的relay log通过如下方式获取：       ssh到192.168.244.30上，执行apply_diff_relay_logs获取差异的relay log。将差异的relay log scp到192.168.244.。Sun May : New Master Diff Log Generation Phase..Sun May info] Server 244.20 received relay logs up to: MysqL-bin.info] Need to get diffs from the latest slave(244.30) up to: MysqL-bin.880 (using the latest slaves relay logs)Sun May info] Connecting to the latest slave host 244.30,generating diff relay log files..Sun May info] Executing command: apply_diff_relay_logs --command=generate_and_send --scp_user=root --scp_host=244.20 --latest_mlf=MysqL-bin.3 --diff_file_readtolatest=/tmp/relay_from_read_to_latest_192.168.244.20_3306_20170521205050.binlog --workdir=/tmp --timestamp=20170521205050 --handle_raw_binlog= Sun May 54 ]     opening /var/lib/MysqL/relay-log. Target relay log file/position found. start_file:MysqLd-relay-bin.000002,start_pos:. Concat binary/relay logs from MysqLd-relay-bin.000002 pos 615 to MysqLd-relay-bin.000003 EOF into /tmp/relay_from_read_to_latest_192..20_3306_20170521205050.binlog .. binlog Checksum enabled binlog Checksum enabled  DumPing binlog format description event,1)">.. ok.  DumPing effective binlog data from /var/lib/MysqL/MysqLd-relay-bin.000002 position 615 to 1066).. ok.  DumPing binlog head events (rotate events),skipPing format description events from /var/lib/MysqL/MysqLd-relay-bin...  binlog Checksum enableddumped up to pos . ok.  No need to dump effective binlog data from /var/lib/MysqL/MysqLd-relay-bin.000003 (pos starts 120,filesize ). SkipPing. binlog Checksum enabled binlog Checksum enabled Concat succeeded. Generating diff relay log succeeded. Saved at /tmp/relay_from_read_to_latest_192..20_3306_20170521205050.binlog . scp node3:/tmp/relay_from_read_to_latest_192.168.244.20_3306_20170521205050.binlog to root@22) succeeded.Sun May info]  Generating  files succeeded.Sun May ] Sending binlog..Sun May scp from local:/masterha/app1/saved_master_binlog_from_192.168.244.10_3306_20170521205050.binlog to root@244.20:/tmp/saved_master_binlog_from_192.3.4 应用从master保存的二进制日志事件       首先，等原来的所有的relay log都应用完。       其次，再通过apply_diff_relay_logs应用差异的relay log，及差异的binlog。            应用完毕后，得到新的master binlog的文件和位置，其它slave可根据该文件和位置来建立主从复制关系。       第三，执行master_ip_failover脚本，执行如下 *** 作             将新主的read_only设置为0             启动vip            Sun May : Master Log Apply Phase..Sun May info] *NOTICE: If any error happens from this phase,manual recovery is needed.Sun May info] Starting recovery on )..Sun May ]  Generating diffs succeeded.Sun May info] Waiting until all relay logs are applIEd.Sun May ] Getting slave status..Sun May info] This slave(244.20)s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(MysqL-bin.000001:452). No need to recover from Exec_Master_Log_Pos.Sun May info] Connecting to the target slave host ,running recover script..Sun May info] Executing command: apply_diff_relay_logs --command=apply --slave_user=monitor' --slave_host=244.20 --slave_ip=244.20  --slave_port=3306 --apply_files=/tmp/relay_from_read_to_latest_192.168.244.20_3306_20170521205050.binlog,/tmp/saved_master_binlog_from_192.168.244.10_3306_20170521205050.binlog --workdir=/tmp --target_version=31-log --timestamp=0.56 --slave_pass=xxxSun May 55 ]  Concat all apply files to /tmp/total_binlog_for_192.168.244.20_3306.20170521205050.binlog .. copying the first binlog file /tmp/relay_from_read_to_latest_192.168.244.20_3306_20170521205050.binlog to /tmp/total_binlog_for_192..binlog.. ok.  DumPing binlog .10_3306_20170521205050.binlog..  binlog Checksum enableddumped up to pos . ok. /tmp/saved_master_binlog_from_192.168.244.10_3306_20170521205050.binlog has effective binlog events from pos .  DumPing effective binlog data from /tmp/saved_master_binlog_from_192.168.244.10_3306_20170521205050.binlog position 120 to 548).. ok. Concat succeeded.All apply target binary logs are concatinated at /tmp/total_binlog_for_192..binlog .MysqL clIEnt version is 31. Using --binary-mode.Applying differential binary/relay log files /tmp/relay_from_read_to_latest_192.168.244.10_3306_20170521205050.binlog on 3306. This may take long ...Applying log files succeeded.Sun May ]  All relay logs were successfully applIEd.Sun May info] Getting new masters binlog name and position..Sun May info]  MysqL-bin.000002:976info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=192.168.244.203306,MASTER_LOG_file=MysqL-bin.000002976,MASTER_USER=replxxx;Sun May ] Executing master IP activate script:Sun May info]   /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=3306 --new_master_host=244.20 --new_master_ip=244.20 --new_master_port=3306 --new_master_user=' --new_master_password=monitor123  Set read_only= on the new master.Enabling the VIP 244.188 on the new master: ]  OK.Sun May info] ** Finished master recovery successfully.Sun May : Master Recovery Phase completed.-- 第四阶段 slave恢复阶段-- 4.1 因为192..30拥有最新的relay log，所以也没必要获取差异的relay log-- 4.2 开始slave的恢复阶段        将monitor上保存的master上的差异的二进制日志scp到slave上。        应用差异日志。       3> 清除原来的复制关系，并再次执行change master命令建立新的主从同步。    如果有多个slave，则该恢复过程是并行的。    Sun May : Slaves Recovery Phase..Sun May 4.1: Starting Parallel Slave Diff Log Generation Phase..Sun May info] -- Slave diff file generation on host 3306) started,pID: 4966. Check tmp log /masterha/app1/244.30_3306_20170521205050.log if it takes ..Sun May 56 info] Log messages from  ...Sun May info]  This server has all relay logs. No need to generate  files from the latest slave.Sun May info] End of log messages from info] -- ) has the latest relay log events.Sun May info] Generating relay  files from the latest slave succeeded.Sun May : Starting Parallel Slave Log Apply Phase..Sun May info] -- Slave recovery on host 4968. Check tmp log /masterha/app1/58 57 244.30:/tmp/saved_master_binlog_from_192.244.30)s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(MysqL-bin.000001:880). No need to recover from Exec_Master_Log_Pos.Sun May 244.30 --slave_ip=244.30  --slave_port=3306 --apply_files=/tmp/saved_master_binlog_from_192.] MysqL clIEnt version is mode.Applying differential binary/relay log files /tmp/saved_master_binlog_from_192.info]  resetting slave 3306) and starting replication from the new master ]  Executed CHANGE MASTER.Sun May ]  Slave started.Sun May ] All new slave servers recovered successfully.-- 第五阶段 清理阶段   从MHA的配置文件中剔除server1的配置信息   Sun May 5: New master cleanup phase..Sun May info] resetting slave  on the new master..Sun May info]  244.20: resetting slave  succeeded.Sun May info] Master failover to ) completed successfully.Sun May info] Deleted server1 entry from /etc/masterha/app1.cnf .Sun May  生成 Failover 报告，发送告警邮件----- Failover Report -----app1: MysqL Master failover 3306) to ) succeededMaster 3306) is down!Check MHA Manager logs at node4:/masterha/app1/manager.log  details.Started automated(non-interactive) failover.InvalIDated master IP address on )The latest slave 3306) has all relay logs  recovery.Selected ) as a new master.): OK: Applying all logs succeeded.): OK: Activated master IP address.): This host has the latest relay log events.Generating relay  files from the latest slave succeeded.3306): OK: Applying all logs succeeded. Slave started,replicating from )3306): resetting slave  succeeded.Master failover to ] Sending mail..UnkNown option: conf

 

从上面的日志输出可以看出整个MHA的切换过程

 

首先，MHA Manager检测到master不可用，则会通过另外两个slave检查master的可用性。一共检测4次。

     同时判断MHA Manager到master的ssh可用性。

     ssh可用性的判断结果影响后后续切换中的“Phase 3.2: Saving Dead Master's binlog Phase”

 

Phase 1: Configuration Check Phase..

     检查了MHA的配置信息，并再次判断了master的可用性。

 

Phase 2: Dead Master Shutdown Phase..

     宕机的master处理阶段，包括摘除VIP，执行shutdown_script中定义的脚本。

 

Phase 3: Master Recovery Phase..

   Phase 3.1: Getting Latest Slaves Phase..

   判断哪个slave拥有最新的relay log（通过比较show slave status中的Master_Log_file,Read_Master_Log_Pos位置），

   哪个slave拥有最旧的relay log

 

   Phase 3.2: Saving Dead Master's binlog Phase..

   根据上面得到的slave的最新位置信息，将差异的二进制日志保存到MHA Manager的指定目录下。

   如果在第一步骤中，判断了MHA Manager到master的ssh不可用，则会跳过这个阶段。

 

   Phase 3.3: Determining New Master Phase..

   选择新的master

     

   Phase 3.3: New Master Diff Log Generation Phase..

   将差异的relay log和master差异日志scp到新的master上。

   

   Phase 3.4: Master Log Apply Phase..

   首先，等待slave上已有的relay log都应用完。

   其次，通过apply_diff_relay_logs应用差异的relay log，及差异的binlog。

   应用完毕后，得到新的master binlog的文件和位置，其它slave可根据该文件和位置来建立主从复制关系。

   第三，执行master_ip_failover脚本，执行如下 *** 作

   1> 将新主的read_only设置为0

   2> 启动vip

   

Phase 4: Slaves Recovery Phase

   Phase 4.1: Starting Parallel Slave Diff Log Generation Phase

   为slave获取差异的relay log

   因为192.168.244.30拥有最新的relay log，所以也没必要获取差异的relay log

 

   Phase 4.2: Starting Parallel Slave Log Apply Phase

   开始slave的恢复阶段

   1> 将差异的relay log和master差异日志scp到slave上。

   2> 应用差异日志。

   3> 清除原来的复制关系，并再次执行change master命令建立新的主从同步。

   如果有多个slave，则该恢复过程是并行的。

     

Phase 5: New master cleanup phase

    从MHA的配置文件中剔除server1的配置信息

 

最后，生成Failover Report并发送告警邮件。

 

手动Failover

与自动Failover相对应的是手动Failover，即当master发生故障时，通过手动执行脚本来进行故障切换。

命令如下：

# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.244.10 --dead_master_port=3306 --new_master_host=192.168.244.30 --new_master_port=3306 --ignore_last_failover

输出日志信息如下：

--dead_master_ip=<dead_master_ip> is not set. Using .Wed May 19:44:20 masterha_default.cnf not found. SkipPing.Wed May app1.cnf..Wed May ] Starting master failover.Wed May ] Wed May : Configuration Check Phase..Wed May Wed May ] Dead Servers:Wed May )Wed May  check)...Wed May ]  ok.Wed May ] Alive Servers:Wed May ] Alive Slaves:Wed May log (oldest major version between slaves) log-bin:enableDWed May  the new Master (candIDate_master is set)Wed May )Master 3306) is dead. Proceed? (yes/NO): yesWed May 23 GTID based failover.Wed May : Configuration Check Phase completed.Wed May : Dead Master Shutdown Phase..Wed May  is reachable.Wed May ] Forcing shutdown so that applications never connect to the current master..Wed May ] Executing master IP deactivation script:Wed May 244.10 --orig_master_port=3306 --command=stopssh --ssh_user=root  disabling the VIP an old master:  SIOCSIFFLAGS: Cannot assign requested addressWed May  [warning] shutdown_script is not set. SkipPing explicit shutting down of the dead master.Wed May : Dead Master Shutdown Phase completed.Wed May : Master Recovery Phase..Wed May : Getting Latest Slaves Phase..Wed May ] Latest slaves (Slaves that received relay log files to the latest):Wed May ] oldest slaves:Wed May s binlog Phase..Wed May s binary logs..Wed May 3306): save_binary_logs --command=save --start_file=MysqL-bin.000002  --start_pos=120 --binlog_dir=/var/lib/MysqL --output_file=/tmp/saved_master_binlog_from_192.168.244.10_3306_20170524194420.binlog --handle_raw_binlog=0.56  Creating /tmp 120 to MysqL-bin.000002 EOF into /tmp/saved_master_binlog_from_192..10_3306_20170524194420.binlog .. binlog Checksum enabled  DumPing binlog format description event,1)">143).. ok. binlog Checksum enabled Concat succeeded.saved_master_binlog_from_192.168.244.10_3306_20170524194420.binlog                                 100%  143     0.1KB/s   00    Wed May .10_3306_20170524194420.binlog to local:/masterha/app1/saved_master_binlog_from_192.168.244.10_3306_20170524194420.binlog succeeded.Wed May 25 : Determining New Master Phase..Wed May  recovering other slaves..Wed May ] All slaves received relay logs to the same position. No need to resync each other.Wed May info]  can be new master.Wed May ] Starting master failover..Wed May )Starting master switch from 3306)? (yes/32 info] New master decIDed manually is : New Master Diff Log Generation Phase..Wed May  files from the latest slave.Wed May ] Sending binlog..saved_master_binlog_from_192.33 .10_3306_20170524194420.binlog to root@: Master Log Apply Phase..Wed May )..Wed May ]  Generating diffs succeeded.Wed May  all relay logs are applIEd.Wed May ] Getting slave status..Wed May s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(MysqL-bin.000002:120). No need to recover from Exec_Master_Log_Pos.Wed May 192.168.244.30 --slave_ip=168.244.10_3306_20170524194420.binlog --workdir=/tmp --target_version=20170524194420 --handle_raw_binlog=0.56 --slave_pass=xxxWed May 168.244.10_3306_20170524194420.binlog on 244.30:...Applying log files succeeded.Wed May ]  All relay logs were successfully applIEd.Wed May s binlog name and position..Wed May 1429]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=192.168.244.30MysqL-bin.0000011429,1)">';Wed May ] Executing master IP activate script:Wed May 10 --orig_master_ip=244.30 --new_master_ip=244.30 --new_master_port='  Set read_only= Wed May ]  OK.Wed May  Finished master recovery successfully.Wed May : Master Recovery Phase completed.Wed May : Slaves Recovery Phase..Wed May : Starting Parallel Slave Diff Log Generation Phase..Wed May 1598. Check tmp log /masterha/app1/244.20_3306_20170524194420.log time..Wed May 34  ...Wed May ) has the latest relay log events.Wed May  files from the latest slave succeeded.Wed May : Starting Parallel Slave Log Apply Phase..Wed May 1600. Check tmp log /masterha/app1/time..saved_master_binlog_from_192.35 ] Sending binlog..Wed May 20 --slave_ip=244.2192.168.3306)..Wed May ]  Executed CHANGE MASTER.Wed May ]  Slave started.Wed May ) succeeded.Wed May ] All new slave servers recovered successfully.Wed May : New master cleanup phase..Wed May  on the new master..Wed May 244.30: resetting slave  succeeded.Wed May ) completed successfully.Wed May ] ----- Failover Report -----Check MHA Manager logs at node4  details.Started manual(interactive) failover.InvalIDated master IP address on 3306)] Sending mail..UnkNown option: conf

 

在切换的过程中，会有两次确认 *** 作。

通过日志的输出，可以看出，手动Failover的切换逻辑和自动Failover的切换逻辑基本一致。

在上面的命令中，显示指定了--new_master_host参数，如果，没有显示指定该参数的话，则默认新主为192.168.244.20，因为在/etc/masterha/app1.cnf中将主机设置为candIDate_master=1。

事实上，如果master alive的话，是不允许切换的。

@H_628_2403@

Wed May 26:26 2017 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm,ln187] None of server is dead. Stop failover.Wed May 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm,ln177] Got ERROR:  at /usr/local/bin/masterha_master_switch line 53.

VIEw Code

 

在默认情况下，如果MHA检测到两次切换的时间小于8小时，则不允许进行Failover，这个时候，需指定--ignore_last_failover参数

# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=244.10 --dead_master_port=3306--dead_master_ip=<dead_master_ip> is not set. Using masterha_master_switch line 53.[root@node4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=244.10 --dead_master_port=3306--dead_master_ip=<dead_master_ip> is not set. Using 29:2017 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm,ln309] Last failover was done at 2017/05/04:54. Current time is too early to do failover again. If you want to do failover,manually remove /masterha/app1/app1.failover.complete and run this script again.Wed May 53.

VIEw Code

 

后面附上之前提到的测试结果不理想的两种方案

方案一： 

模拟步骤如下：

1. master上创建测试表

MysqLuse sbtest;Database changedMysqLtable sbtest.t1(ID int primary key,col1 10),col2 varchar(10));
query OK,1); Font-weight: bold">0.33 sec)

 

2. 关闭Candicate master和Slave的主从复制。

MysqL stop slave;query OK,1); Font-weight: bold">0.03 sec)

 

3. 在master上生成测试数据

创建存储过程

delimiter //procedure p1()begin  declare v1 int default ;  while v1 <=700000 do    into sbtest.t1 values(v1,1)">aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeffffffffffgggggggggghhhhhhhhhhiiiiiiiiiijjjjjjjjjj);    set v1=v1+if v1 %1000 0 then     commitend whileend//delimiter ;

执行存储过程

MysqL> call p1;

共执行了两次，生成了500M左右的binlog

[root@node1 ~]# ll -h 000046 -rw-rw-- 1 MysqL MysqL 502M Apr 24 15:18 /var/lib/MysqL/MysqL-bin.00004

 

5. 开启Candicate master和Slave的主从复制并kill掉master的MysqLd进程，模拟主库发生故障，进行自动failover *** 作

在这里，我写了个脚本来实现开启slave复制并kill掉master MysqLd进程的功能

# vim  monitor_slave.py

!/usr/bin/python# Coding=utf-8import MysqLdb subprocess timeclass CheckSlaveStatus:    def __init__(self,host):        self.conn = MysqLdb.connect(host,1)">"",1)">")        self.cursor = self.conn.cursor()        self.cursor.execute(start slave)    def get_slave_status(self):        self.cursor.execute(show slave status)        slave_status_result = self.cursor.fetchone()        slave_io_running,slave_sql_running = slave_status_result[10],slave_status_result[11]        return [slave_io_running,slave_sql_running]     quit(self):        try:            self.conn.commit()            self.conn.close()        except Exception as e:            print e main():    check_host = []    check_slave_status_30 = CheckSlaveStatus(check_host[0])    check_slave_status_20 = CheckSlaveStatus(check_host[1])    for check_slave_status in [check_slave_status_30,check_slave_status_20]:        print Begin to check slave status"         True:            slave_status = check_slave_status.get_slave_status()             slave_status            if slave_status == [Yes]:                           time.sleep(5)                break    time.sleep(10)
Begin to kill master MysqLd    command_line=ps -ef |grep MysqLd |grep -v grep |awk '{print }' |xargs kill -9    p = subprocess.Popen(command_line,stdout=subprocess.PIPE,shell=True)    p.communicate()    check_slave_status_30.quit()    check_slave_status_20.quit()    Finished"if __name__ == __main__:    main()

执行该脚本

6. 查看MHA的切换日志

Sun May 09:46:2017 - [warning] Got error on MysqL select Ping: 2006 (MysqL server has gone away)Sun May 3306 --master_user=monitor --masteConnection timed out during banner exchangeSun May 47:08 app1.cnf..to MysqL server at 59 (s)..Monitoring server 01 2017 - [warning] HealthCheck: SSH to  is NOT reachable.Monitoring server 07  [warning] SSH is NOT reachable.Sun May ] Starting master failover.Sun May 09 : Configuration Check Phase completed.Sun May 3306 --command=stop disabling the VIP an old master: 37651149350830932017 - [warning] Dead Master is not SSH reachable. Could not save its binlogs. Transactions that were not sent to the latest slave (Read_Master_Log_Pos to the tail of the dead masters binlog) were lost.Sun May 10 37651149 --target_mlf=MysqL-bin.35083093 --server_ID=2 --workdir=/tmp --timestamp=20170521094708 --manager_version=000004 Fast relay log position search succeeded. Target relay log 000004,1)">35083256.Target relay log FOUND!11 13 ]  This server has all relay logs. Waiting all logs to be applIEd.. Sun May 000010:166054508MysqL-bin.000010166054508,1)">: Master Recovery Phase completed.Sun May 3606. Check tmp log /masterha/app1/244.30_3306_20170521094708.log 48:04 244.30 received relay logs up to: MysqL-bin.244.20) up to: MysqL-bin.37651149 (using the latest slave244.20,1)">244.30 --latest_mlf=MysqL-bin.2 --diff_file_readtolatest=/tmp/relay_from_read_to_latest_192.168.244.30_3306_20170521094708.binlog --workdir=/tmp --timestamp=20170521094708 --handle_raw_binlog=000004 pos 35083256 to MysqLd-relay-bin.000004 EOF into /tmp/relay_from_read_to_latest_192..30_3306_20170521094708.binlog .. binlog Checksum enabled binlog Checksum enabled  DumPing binlog format description event,1)">000004 position 35083256 to 37651312).. ok. binlog Checksum enabled binlog Checksum enabled Concat succeeded. Generating .30_3306_20170521094708.binlog . scp node2:/tmp/relay_from_read_to_latest_192.168.244.30_3306_20170521094708.binlog to root@diff log generation on host 3613. Check tmp log /masterha/app1/12 s Exec_Master_Log_Pos(MysqL-bin.000001:35083010) does not equal to Read_Master_Log_Pos(MysqL-bin.000001:35083093). It is likely that relay log was cut during transaction. Need to recover from Exec_Master_Log_Pos.Sun May info] Saving local relay logs from exec pos to read pos on 244.30: from MysqLd-relay-bin.000004:35083173 to the end of the relay log..Sun May info] Executing command : save_binary_logs --command=save --start_file=MysqLd-relay-bin.000004  --start_pos=35083173 --output_file=/tmp/relay_from_exec_to_read_192.168.244.30_3306_20170521094708.binlog --handle_raw_binlog=info  --binlog_dir=/var/lib/MysqL/05 ]   Creating /tmp  not exists..    ok. Concat binary/relay logs from MysqLd-relay-bin.35083173 to MysqLd-relay-bin.000004 EOF into /tmp/relay_from_exec_to_read_192.35083173 to ).. ok. binlog Checksum enabled binlog Checksum enabled Concat succeeded.Sun May 3306 --apply_files=/tmp/relay_from_exec_to_read_192.168.244.30_3306_20170521094708.binlog,/tmp/relay_from_read_to_latest_192.168.244.30_3306_20170521094708.binlog --workdir=/tmp --target_version=168.244.30_3306.20170521094708file /tmp/relay_from_exec_to_read_192.168.244.30_3306_20170521094708.binlog to /tmp/total_binlog_for_192..30_3306_20170521094708.binlog..  binlog Checksum enabled binlog Checksum enableddumped up to pos . ok. /tmp/relay_from_read_to_latest_192.168.244.30_3306_20170521094708.binlog has effective binlog events from pos .  DumPing effective binlog data from /tmp/relay_from_read_to_latest_192.168.244.30_3306_20170521094708.binlog position 283 to 2568339mode.Applying differential binary/relay log files /tmp/relay_from_exec_to_read_192.168.244.30_3306_20170521094708.binlog on ] All new slave servers recovered successfully.Sun May 3306): Generating differential relay logs up to )succeeded.Generating relay ] Sending mail..UnkNown option: conf

VIEw Code

 

方案二

对Candicate master上和Slave上的网卡进行带宽限制

Candicate master

[root@node2 ~]# /sbin/tc qdisc add dev eth0 root tbf rate 50kbit latency 50ms burst 15kb

Slave

/sbin/tc qdisc add dev eth0 root tbf rate 80kbit latency 50ms burst 15kb

注意：Candicate master的网卡限制在50kbit，slave的网卡限制在80kbit，

要确保Candicate master的小于slave的，这样才能模拟出Candicate master上relay log的位置小于slave上relay log的位置。

 

关于如何确认网卡的速率控制到多大合适，

可通过压测，测试master的吞吐量，

以及结合如下方法，测试主机之间的传输速率来确定网卡合适的速率。

[root@node1 ~]# dd if=/dev/zero of=hello.txt bs=100M count=1

[root@node1 ~]# time scp hello.txt 192.168.244.20:/tmp/

hello.txt 100% 100MB 2.6MB/s 00:39

 

参考

《深入浅出MysqL》

 

总结

以上是内存溢出为你收集整理的MySQL高可用方案MHA自动Failover与手动Failover的实践及原理全部内容，希望文章能够帮你解决MySQL高可用方案MHA自动Failover与手动Failover的实践及原理所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。