角色 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 sbt