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 快點到來