时间：2021-07-01 10:21:17 帮助过：24人阅读
角色 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) mysql> insert into sbtest.b values(1,‘a‘); Query OK, 1 row affected (0.00 sec) mysql> insert into sbtest.b values(2,‘b‘); Query OK, 1 row affected (0.01 sec) mysql> insert into sbtest.b values(3,‘c‘); Query OK, 1 row affected (0.00 sec) mysql> insert into sbtest.b values(4,‘d‘); Query OK, 1 row affected (0.00 sec) mysql> insert into sbtest.b values(5,‘e‘); Query OK, 1 row affected (0.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 | | mysql-bin.000001 | 120 | Query | 1 | 238 | create table sbtest.b(id int,name varchar(10)) | | mysql-bin.000001 | 238 | Query | 1 | 315 | BEGIN | | mysql-bin.000001 | 315 | Query | 1 | 421 | insert into sbtest.b values(1,‘a‘) | | mysql-bin.000001 | 421 | Xid | 1 | 452 | COMMIT /* xid=102 */ | | mysql-bin.000001 | 452 | Query | 1 | 529 | BEGIN | | mysql-bin.000001 | 529 | Query | 1 | 635 | insert into sbtest.b values(2,‘b‘) | | mysql-bin.000001 | 635 | Xid | 1 | 666 | COMMIT /* xid=103 */ | | mysql-bin.000001 | 666 | Query | 1 | 743 | BEGIN | | mysql-bin.000001 | 743 | Query | 1 | 849 | insert into sbtest.b values(3,‘c‘) | | mysql-bin.000001 | 849 | Xid | 1 | 880 | COMMIT /* xid=104 */ | | mysql-bin.000001 | 880 | Query | 1 | 957 | BEGIN | | mysql-bin.000001 | 957 | Query | 1 | 1063 | insert into sbtest.b values(4,‘d‘) | | mysql-bin.000001 | 1063 | Xid | 1 | 1094 | COMMIT /* xid=105 */ | | mysql-bin.000001 | 1094 | Query | 1 | 1171 | BEGIN | | mysql-bin.000001 | 1171 | Query | 1 | 1277 | insert into sbtest.b values(5,‘e‘) | | mysql-bin.000001 | 1277 | Xid | 1 | 1308 | COMMIT /* xid=106 */ | +------------------+------+-------------+-----------+-------------+------------------------------------------------+ 17 rows in set (0.01 sec)
Slave
mysql> show relaylog events in ‘mysqld-relay-bin.000002‘; +-------------------------+------+-------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------------+------+-------------+-----------+-------------+------------------------------------------------+ | mysqld-relay-bin.000002 | 4 | Format_desc | 2 | 120 | Server ver: 5.6.31-log, Binlog ver: 4 | | mysqld-relay-bin.000002 | 120 | Rotate | 1 | 0 | mysql-bin.000001;pos=120 | | mysqld-relay-bin.000002 | 167 | Format_desc | 1 | 0 | Server ver: 5.6.31-log, Binlog ver: 4 | | mysqld-relay-bin.000002 | 283 | Query | 1 | 238 | create table sbtest.b(id int,name varchar(10)) | | mysqld-relay-bin.000002 | 401 | Query | 1 | 315 | BEGIN | | mysqld-relay-bin.000002 | 478 | Query | 1 | 421 | insert into sbtest.b values(1,‘a‘) | | mysqld-relay-bin.000002 | 584 | Xid | 1 | 452 | COMMIT /* xid=102 */ | | mysqld-relay-bin.000002 | 615 | Query | 1 | 529 | BEGIN | | mysqld-relay-bin.000002 | 692 | Query | 1 | 635 | insert into sbtest.b values(2,‘b‘) | | mysqld-relay-bin.000002 | 798 | Xid | 1 | 666 | COMMIT /* xid=103 */ | | mysqld-relay-bin.000002 | 829 | Query | 1 | 743 | BEGIN | | mysqld-relay-bin.000002 | 906 | Query | 1 | 849 | insert into sbtest.b values(3,‘c‘) | | mysqld-relay-bin.000002 | 1012 | Xid | 1 | 880 | COMMIT /* xid=104 */ | | mysqld-relay-bin.000002 | 1043 | Query | 1 | 957 | BEGIN | | mysqld-relay-bin.000002 | 1120 | Query | 1 | 1063 | insert into sbtest.b values(4,‘d‘) | | mysqld-relay-bin.000002 | 1226 | Xid | 1 | 1094 | COMMIT /* xid=105 */ | | mysqld-relay-bin.000002 | 1257 | Query | 1 | 1171 | BEGIN | | mysqld-relay-bin.000002 | 1334 | Query | 1 | 1277 | insert into sbtest.b values(5,‘e‘) | | mysqld-relay-bin.000002 | 1440 | Xid | 1 | 1308 | COMMIT /* xid=106 */ | +-------------------------+------+-------------+-----------+-------------+------------------------------------------------+ 19 rows in set (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 170524 17:16:37 BINLOG ‘ dU8lWQ8CAAAAdAAAAHgAAABAAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAa6p q08= ‘/*!*/; # at 120 #700101 8:00:00 server id 1 end_log_pos 0 CRC32 0x74c6d70c Rotate to mysql-bin.000001 pos: 120 # at 167 #170524 17:15:49 server id 1 end_log_pos 0 CRC32 0xed2672eb Start: binlog v 4, server v 5.6.31-log created 170524 17:15:49 BINLOG ‘ RU8lWQ8BAAAAdAAAAAAAAAAAAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAety Ju0= ‘/*!*/; # at 283 #170524 17:17:20 server id 1 end_log_pos 238 CRC32 0xdd48c118 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617440/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table sbtest.b(id int,name varchar(10)) /*!*/; # at 401 #170524 17:17:27 server id 1 end_log_pos 315 CRC32 0xae393750 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617447/*!*/; BEGIN /*!*/; # at 478 #170524 17:17:27 server id 1 end_log_pos 421 CRC32 0x28a781ae Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617447/*!*/; insert into sbtest.b values(1,‘a‘) /*!*/; # at 584 #170524 17:17:27 server id 1 end_log_pos 452 CRC32 0x680f1bfe Xid = 29 COMMIT/*!*/; # at 615 #170524 17:17:33 server id 1 end_log_pos 529 CRC32 0x6a1aae7e Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617453/*!*/; BEGIN /*!*/; # at 692 #170524 17:17:33 server id 1 end_log_pos 635 CRC32 0x117786ca Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617453/*!*/; insert into sbtest.b values(2,‘b‘) /*!*/; # at 798 #170524 17:17:33 server id 1 end_log_pos 666 CRC32 0xa8400ec6 Xid = 30 COMMIT/*!*/; # at 829 #170524 17:17:38 server id 1 end_log_pos 743 CRC32 0x24f9a1d2 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617458/*!*/; BEGIN /*!*/; # at 906 #170524 17:17:38 server id 1 end_log_pos 849 CRC32 0x56fa9e89 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617458/*!*/; insert into sbtest.b values(3,‘c‘) /*!*/; # at 1012 #170524 17:17:38 server id 1 end_log_pos 880 CRC32 0x2ac656d4 Xid = 31 COMMIT/*!*/; # at 1043 #170524 17:17:44 server id 1 end_log_pos 957 CRC32 0x73a903bf Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617464/*!*/; BEGIN /*!*/; # at 1120 #170524 17:17:44 server id 1 end_log_pos 1063 CRC32 0x171b9b27 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617464/*!*/; insert into sbtest.b values(4,‘d‘) /*!*/; # at 1226 #170524 17:17:44 server id 1 end_log_pos 1094 CRC32 0x47d6fe57 Xid = 32 COMMIT/*!*/; # at 1257 #170524 17:17:49 server id 1 end_log_pos 1171 CRC32 0x2d37da37 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617469/*!*/; BEGIN /*!*/; # at 1334 #170524 17:17:49 server id 1 end_log_pos 1277 CRC32 0xd2201fa2 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1495617469/*!*/; insert into sbtest.b values(5,‘e‘) /*!*/; # at 1440 #170524 17:17:49 server id 1 end_log_pos 1308 CRC32 0xac1b464e Xid = 33 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* 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: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: mysql-bin.000001 Read_Master_Log_Pos: 1308 Relay_Log_File: mysqld-relay-bin.000002
