MySQL官方的HA切换方案

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

总结

1. 未来就是用mysql-utilities来替代所有第三方工具
2. 目前1.5版本还有点问题，期待1.6 快点到来