mysqlrpladmin + GTID HA切换
传统的复制切换,由于是base file和position的,切换非常复杂,是个技术活
MHA最大的优点就是日志补偿机制,现在有了GTID,日志补偿分分钟的事情,完全可以替代MHA
基本命令
Available Commands:
elect - perform best slave election and report best slave
failover - conduct failover from master to best slave
gtid - show status of global transaction id variables
also displays uuids for all servers
health - display the replication health
reset - stop and reset all slaves
start - start all slaves
stop - stop all slaves
switchover - perform slave promotion
Note:
elect, gtid and health require --master and either
--slaves or --discover-slaves-login;
failover requires --slaves;
switchover requires --master, --new-master and either
--slaves or --discover-slaves-login;
start, stop and reset require --slaves (and --master is optional)
重点函数
select WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()
select GTID_SUBTRACT()
下面会重点分析和实现部分重要的操作
状态检查 health
health
--master
--slaves
mysqlrpladmin --master=rpl_admin:rpl_admin@$host1:3306 --slaves=rpl_admin:rpl_admin@$host2,rpl_admin:rpl_admin@$host3 health
# Checking privileges.
#
# Replication Topology Health:
+-----------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+-----------------+-------+---------+--------+------------+---------+
| $host1 | 3306 | MASTER | UP | ON | OK |
| $host3 | 3306 | SLAVE | UP | ON | OK |
| $host2 | 3306 | SLAVE | UP | ON | OK |
+-----------------+-------+---------+--------+------------+---------+
# ...done.
elect
选举报告最新的从
--master
--slaves
--candidates
mysqlrpladmin --master=rpl_admin:rpl_admin@$host3:3306 --slaves=rpl_admin:rpl_admin@$host2,rpl_admin:rpl_admin@$host1 elect
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Electing candidate slave from known slaves.
# Best slave found is located on $host2:3306.
# ...done.
- 加上--candidates=rpl_admin:rpl_admin@$host1
如果想指定选举哪个,就candidates指定哪个
mysqlrpladmin --master=rpl_admin:rpl_admin@$host3:3306 --slaves=rpl_admin:rpl_admin@$host2,rpl_admin:rpl_admin@$host1 --candidates=rpl_admin:rpl_admin@$host1 elect
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
# Electing candidate slave from known slaves.
# Best slave found is located on $host1:3306.
# ...done.
主动切换
为了维护,主动更换主从关系
switchover
--master
--slaves
--new-master
switchover
下线master,从剩余的slave中,重新搭建主从关系
mysqlrpladmin --master=rpl_admin:rpl_admin@$host1:3306 --slaves=rpl_admin:rpl_admin@$host2,rpl_admin:rpl_admin@$host3 --new-master=rpl_admin:rpl_admin@$host3:3306 switchover
# Checking privileges.
# Performing switchover from master at $host1:3306 to slave at $host3: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.
# 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 |
+-----------------+-------+---------+--------+------------+---------+
| $host3 | 3306 | MASTER | UP | ON | OK |
| $host2 | 3306 | SLAVE | UP | ON | OK |
+-----------------+-------+---------+--------+------------+---------+
# ...done.
同事,老master $host1已经下线
switchover + --demote-master
--master
--slaves
--new-master
--demote-master
switchover
降级master为slave,并且重新搭建主从关系
mysqlrpladmin --master=rpl_admin:rpl_admin@$host3:3306 --slaves=rpl_admin:rpl_admin@$host2,rpl_admin:rpl_admin@$host1 --new-master=rpl_admin:rpl_admin@$host1:3306 --demote-master switchover
# Checking privileges.
# Performing switchover from master at $host3:3306 to slave at $host1: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 |
+-----------------+-------+---------+--------+------------+---------+
| $host1 | 3306 | MASTER | UP | ON | OK |
| $host2 | 3306 | SLAVE | UP | ON | OK |
| $host3 | 3306 | SLAVE | UP | ON | OK |
+-----------------+-------+---------+--------+------------+---------+
# ...done.
switchover 的Bug
- 无故创建一个user
假设:--rpl-user=rpl
CREATE USER 'rpl'@'$host' IDENTIFIED WITH 'mysql_native_password' AS 'xx';
问题:创建的这个用户,权限是usage,并不是replication slave. 所以会导致连接报错
故障切换
master已经挂了,没办法访问,只能用failover命令
这是被动切换
failover
--slaves
--candidates
- 1.5 failover的bug
mysqlrpladmin --slaves=rpl_admin:rpl_admin@$host1,rpl_admin:rpl_admin@$host2 --candidates=rpl_admin:rpl_admin@$host2 failover --verbose
# Checking privileges.
# Performing failover.
# Checking eligibility of slave $host2:3306 for candidate.
# GTID_MODE=ON ... Ok
# Replication user exists ... Ok
ERROR: The server $host2:3306 does not comply to the latest GTID feature support. Errors:
Missing gtid_executed system variable.
http://bugs.mysql.com/bug.php?id=80189
1.6版本会fixed掉
QA
- --discover-slaves-login=rpl_admin:rpl_admin 为什么不用这个命令
1) 建议不用这个命令,发现不及时
2)需要每台服务器配置report_host,report_port
总结
- 未来就是用mysql-utilities来替代所有第三方工具
- 目前1.5版本还有点问题,期待1.6 快点到来