By 兰春

Updated:

Contents
  1. 1. 基本命令
  2. 2. 重点函数
  3. 3. 状态检查 health
    1. 3.1. health
    2. 3.2. elect
  4. 4. 主动切换
    1. 4.1. switchover
    2. 4.2. switchover + —demote-master
    3. 4.3. switchover 的Bug
  5. 5. 故障切换
    1. 5.1. failover
    2. 5.2. QA
  6. 6. 总结

http://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlrpladmin.html
传统的复制切换，由于是base file和position的，切换非常复杂，是个技术活
MHA最大的优点就是日志补偿机制，现在有了GTID，日志补偿分分钟的事情，完全可以替代MHA

基本命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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)

重点函数

1
2
select WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()
select GTID_SUBTRACT()

下面会重点分析和实现部分重要的操作

状态检查 health

health

—master
—slaves

1
2
3
4
5
6
7
8
9
10
11
12
13
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 

1
2
3
4
5
6
7
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指定哪个 

1
2
3
4
5
6
7
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中，重新搭建主从关系 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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，并且重新搭建主从关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
1
2
3
4
5
假设：--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
1
2
3
4
5
6
7
8
9
10
11
12
13
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
1) 建议不用这个命令，发现不及时   
2）需要每台服务器配置report_host,report_port

总结

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

utilities

Contents
  1. 1. 基本命令
  2. 2. 重点函数
  3. 3. 状态检查 health
    1. 3.1. health
    2. 3.2. elect
  4. 4. 主动切换
    1. 4.1. switchover
    2. 4.2. switchover + —demote-master
    3. 4.3. switchover 的Bug
  5. 5. 故障切换
    1. 5.1. failover
    2. 5.2. QA
  6. 6. 总结

幸福,不在于得到的多

而在于计较的少