使用mysql5.7 增强半同步，GTID复制，设置master wait永不超时，外加mysqlfailover自动切换。可实现新一代高可用架构，零事务丢失自动切换！
搭建步骤
- 搭建一主两从（至少两从）增强半同步架构（安装略）。
master 半同步配置用不超时：
salve 的my.cnf配置(主库也要配置，防止主库切换后，再次以从库的角色加入集群，无法被自动发现)：rpl_semi_sync_master_wait_for_slave_count=1 rpl_semi_sync_master_wait_no_slave=ON rpl_semi_sync_master_timeout=1000000000000000000 rpl_semi_sync_master_wait_point=AFTER_SYNC
注意从库不能配置super_read_only=ONreport_host='172.40.0.164' # the ip of the slave report_port=3306 master_info_repository=TABLE - 在一台单独的监控机上安装mysqlfailover.
wget -c https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5-1.el7.noarch.rpm #mysqlfailover脚本依赖依赖mysql python connector wget -c https://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-2.1.7-1.el7.x86_64.rpm #安装： rpm -ivh mysql-connector-python-2.1.7-1.el7.x86_64.rpm rpm -ivh mysql-utilities-1.6.5-1.el7.noarch.rpm - failover监控脚本账号创建。mysqlfailover脚本使用一个数据库账号去监控集群中各节点状态，如果主库故障，会启用自动切换，切换时脚本会执行change master 等操作,且所有节点需要此账号，因此需要在主库上创建。此账号权限较大，为安全起见，可使用login-path.
对于mysql5.7还需要授权：create user fo@'192.168.216.146' identified by 'fo'; grant SUPER, REPLICATION SLAVE, SELECT, RELOAD, DROP, CREATE, INSERT on *.* to fo@'192.168.216.146' with grant option;
因为mysql 5.7中对于已经存在的账号，再次使用grant ... identified by 需要修改mysql库中的相关表。grant update,delete on mysql.* to 'fo'@'192.168.216.146'; - 在监控节点配置login-path
mysql_config_editor set --login-path=fo --user=fo --host=192.168.216.144 --port=3306 --password
# test login
mysql --login-path=fo
- 启动mysqlfailover
mysqlfailover --master=fo --discover-slaves-login=fo --log=/tmp/fo.log --rpl-user=repl:repl --force
mysqlfailover在第一次连接的时候，会在mysql库下面创建failover_console表，里面记录了所监控master的ip和端口。正常退出，这个表会被drop掉。但对于非正常退出，这个表会一直存在。如果下次再尝试连接，则会警告：
Multiple instances of failover console found for master 192.168.216.144:3306.
If this is an error, restart the console with --force.
Failover mode changed to 'FAIL' for this instance.
Console will start in 10 seconds..........starting Console.
上述信息是说mysqlfailover检查到已经有一个进程在运行了，因此这个进程的mode被修改为FAIL, FAIL mode的意思是该进程只监控，不会执行切换。如果确认只有一个mysqlfailover在运行，可以使用--force参数。这里需要说一下failover的三种模式：
- auto: (默认)。 自动切换模式。该模式在切换时，首先从candicate列表中选择，如果candicate列表的主机不可用，则会从其它的从节点选择一个作为新的master. 然后把新master配置为其它从节点的从库，复制缺少的事务，直到该master包含所有的事务（most update slave）
- elect: 该模式和auto模式类似，只是它只从candidate列表中选择。如果列表不可用则报错退出。使用该模式必须用--candidates选项。
- fail:该模式只监控，不执行切换。
测试
- 停止主库，等待约10s左右，可以看到切换动作
Failed to reconnect to the master after 3 attemps.
Failover starting in 'auto' mode...
# Candidate slave 192.168.216.145:3306 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Disconnecting new master as slave.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
# Discovering slaves for master at 192.168.216.145:3306
Failover console will restart in 5 seconds.
- 重新启动原来的主节点，然后将其作为从库加入集群：
mysqlreplicate --master=fo:fo@192.168.216.145:3306 --slave=fo:fo@192.168.216.144:3306 --rpl-user=repl:repl
加入后，failover脚本很快就发现新节点，显示一主两从的架构。
- 重新启用原来的主节点为master
mysqlrpladmin --master=fo:fo@192.168.216.145:3306 \
> --slaves=fo:fo@192.168.216.144:3306,fo:fo@192.168.216.147:3306 \
> --rpl-user=repl:repl --new-master=fo:fo@192.168.216.144:3306 --demote-master switchover
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Performing switchover from master at 192.168.216.145:3306 to slave at 192.168.216.144:3306.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+------------------+-------+---------+--------+------------+---------+
| 192.168.216.144 | 3306 | MASTER | UP | ON | OK |
| 192.168.216.145 | 3306 | SLAVE | UP | ON | OK |
| 192.168.216.147 | 3306 | SLAVE | UP | ON | OK |
+------------------+-------+---------+--------+------------+---------+
# ...done.
切换后发现，failover脚本监控失败：
Replication Health Status
+------------------+-------+---------+--------+------------+------------------------------------+
| host | port | role | state | gtid_mode | health |
+------------------+-------+---------+--------+------------+------------------------------------+
| 192.168.216.145 | 3306 | MASTER | UP | ON | OK |
| 192.168.216.144 | 3306 | SLAVE | WARN | | Slave is not connected to master. |
| 192.168.216.147 | 3306 | SLAVE | WARN | | Slave is not connected to master. |
+------------------+-------+---------+--------+------------+------------------------------------+
看来这种情下必须重启mysqlfailover脚本！
- 使用sysbench 对主库压测,监控信息会显示延迟情况。
Replication Health Status
+------------------+-------+---------+--------+------------+--------------------------------------------------------------------------------------------+
| host | port | role | state | gtid_mode | health |
+------------------+-------+---------+--------+------------+--------------------------------------------------------------------------------------------+
| 192.168.216.144 | 3306 | MASTER | UP | ON | OK |
| 192.168.216.145 | 3306 | SLAVE | UP | ON | Slave delay is 6 seconds behind master., No, Slave has 3916 transactions behind master. |
| 192.168.216.147 | 3306 | SLAVE | UP | ON | Slave delay is 59 seconds behind master., No, Slave has 33416 transactions behind master. |
+------------------+-------+---------+--------+------------+--------------------------------------------------------------------------------------------+
在主从复制延迟的情况下，停掉主库，查看failover脚本切换状况。
Failed to reconnect to the master after 3 attemps.
Failover starting in 'auto' mode...
# Candidate slave 192.168.216.145:3306 will become the new master.
# Checking slaves status (before failover).
# Preparing candidate for failover.
发现切换时间明显增长，在Preparing candidate for failover.等待。查看从库状态，发现有一个尚未应用完binlog，当所有从库都应用完binglog时，才正式切换。这对保证数据一致性很重要！mysqlfailover已经做到这一点儿了。另外如果切换时主从延迟严重，切换时间会较长，这可以通过启用MTS来缓解。
问题
- 切换后，从库被提升为master ， 但只读属性没有被取消。这会导致应用依然不能做业务。还一个问题是切换后，应该通知应用，连接新的master. (如修改DNS，或zookeeper中的masterIP,这取决于应用数据源的配置方式) 这些都可以通过脚本来实现。
mysqlfailover 提供了两个注入脚本的接口:- --exec-before=script ,在切换开始前执行。
- --exec-after=script ， 在切换完成后执行。
这意味着我们可以在切换完成后写个脚本去修改新master的只读属性，已经通知应用修改数据源地址（使用vip来解决更为优雅，但有些云环境不支持vip）。
- 使用--daemon=start 启动脚本时，不能使用--daemon=stop来终止。会造成从库sql_thread报错断开。须使用kill -9 pid . 启动时，加--force参数。我已向官方递交此bug (91562)
参考资料：
https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlfailover.html
网友评论