mysqlfailover ためす #MySQL

mysqlfailover とは

MySQL Utilitiesに入ってるツール（Oracle公式のMySQL マスタ切り替え自動化ツール）
- GTID（コミット時に発行されるグローバルなトランザクションID）ベース
- ポジション情報(バイナリログファイル、バイナリログポジション)なしでchange masterができる
- MySQL Utilities には、ヨサソウな便利ツールたくさんあるっぽい（mysqlrepliate, mysqlrplcheckなど）

https://dev.mysql.com/doc/mysql-utilities/16/en/

ヨサソウなところ

MySQLコミュニティが保守
MySQL 5.6.5 から使える
- GTIDベース
監視エージェントみたいなのを入れなくて良い
- スケールし易い
  - slaveの分だけ無限にfailoverしてくれる
  - 現状の master-slave 構成への導入が楽

環境

Vagrant 2.0
- bento/ubuntu-16.04 (v201708.22.0)
MySQL 5.7.19
MySQL Utilities 1.6.5

ロール

ubuntu_1 : master
ubutnu_2 : slave
ubuntu_3 : mysql-util (mysqlfailover)

Vagrantfile


VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "bento/ubuntu-16.04"

  config.vm.define :ubuntu_1 do |ubuntu_1|
    ubuntu_1.vm.network :private_network, ip: "192.168.33.10"
    ubuntu_1.vm.hostname = "ubuntu01"
  end

  config.vm.define :ubuntu_2 do |ubuntu_2|
    ubuntu_2.vm.network :private_network, ip: "192.168.33.11"
    ubuntu_2.vm.hostname = "ubuntu02"
  end

  config.vm.define :ubuntu_3 do |ubuntu_3|
    ubuntu_3.vm.network :private_network, ip: "192.168.33.12"
    ubuntu_3.vm.hostname = "ubuntu03"
  end

  config.vm.provision "shell", inline: <<-SHELL
    sudo sh -c 'echo 127.0.1.1 $(hostname) >> /etc/hosts'
    sudo sh -c 'echo 192.168.33.10 ubuntu_1 >> /etc/hosts'
    sudo sh -c 'echo 192.168.33.11 ubuntu_2 >> /etc/hosts'
    sudo sh -c 'echo 192.168.33.12 ubuntu_3 >> /etc/hosts'
    sudo timedatectl set-timezone Asia/Tokyo

    sudo apt-get update -y && sudo apt-get upgrade -y
    debconf-set-selections <<< 'mysql-server mysql-server/root_password password root'
    debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password root'
    sudo apt-get install -y mysql-server mysql-client
    sudo apt-get remove -y python-mysql.connector
    wget https://dev.mysql.com/get/mysql-connector-python_2.1.6-1ubuntu16.04_all.deb
    wget https://dev.mysql.com/get/mysql-utilities_1.6.5-1ubuntu16.04_all.deb
    sudo dpkg -i mysql-connector-python_2.1.6-1ubuntu16.04_all.deb
    sudo dpkg -i mysql-utilities_1.6.5-1ubuntu16.04_all.deb
  SHELL

end

起動

$ vagrant up
$ vagrant status
Current machine states:

ubuntu_1                  running (virtualbox)
ubuntu_2                  running (virtualbox)
ubuntu_3                  running (virtualbox)

Master

conf

vagrant@vagrant:~$ diff -u /etc/mysql/mysql.conf.d/mysqld.cnf.bak /etc/mysql/mysql.conf.d/mysqld.cnf
--- /etc/mysql/mysql.conf.d/mysqld.cnf.bak  2017-09-13 23:10:58.268303996 +0900
+++ /etc/mysql/mysql.conf.d/mysqld.cnf  2017-09-13 23:12:28.337316000 +0900
@@ -40,7 +40,7 @@
 #
 # Instead of skip-networking the default is now to listen only on
 # localhost which is more compatible and is not less secure.
-bind-address       = 127.0.0.1
+bind-address       = 0.0.0.0
 #
 # * Fine Tuning
 #
@@ -80,8 +80,17 @@
 # The following can be used as easy to replay backup logs or for replication.
 # note: if you are setting up a replication slave, see README.Debian about
 #       other settings you may need to change.
-#server-id     = 1
-#log_bin           = /var/log/mysql/mysql-bin.log
+server-id      = 1
+log_bin            = /var/log/mysql/mysql-bin.log
+log_slave_updates
+master_info_repository  = TABLE
+relay_log_info_repository=TABLE
+relay-log-recovery = ON
+
+# gtid
+gtid_mode = ON
+enforce_gtid_consistency
+
 expire_logs_days   = 10
 max_binlog_size   = 100M
 #binlog_do_db      = include_database_name

全権限与えておく ※ 本番では、repl などつくる　

mysql> GRANT all on *.* to root@'%' identified by 'root' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

restart

vagrant@vagrant:~$ sudo service mysql status
vagrant@vagrant:~$ sudo service mysql restart
vagrant@vagrant:~$ sudo service mysql status

db, table つくる

mysql> create database sample;
Query OK, 1 row affected (0.00 sec)

mysql> create table sample.users(id int auto_increment, name varchar(20), index(id));
Query OK, 0 rows affected (0.01 sec)

mysql>  insert into sample.users (name) values ("7kaji");
Query OK, 1 row affected (0.01 sec)

mysql> select * from sample.users;
+----+-------+
| id | name  |
+----+-------+
|  1 | 7kaji |
+----+-------+
1 row in set (0.00 sec)

dump, 転送

vagrant@vagrant:~$ mysqldump -uroot -p --all-databases --lock-all-tables --flush-logs --master-data=2 --triggers --routines --events > dump.sql
vagrant@vagrant:~$ scp dump.sql vagrant@ubuntu_2:/tmp/dump.sql
vagrant@ubuntu_2's password:
dump.sql

Slave

conf

master と変わるのは、

server-id
read_only

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

dumpからつくる

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

vagrant@vagrant:~$ mysql -uroot -p < /tmp/dump.sql
Enter password:
vagrant@vagrant:~$ mysql -uroot -p -e 'select * from sample.users;'
Enter password:
+----+-------+
| id | name  |
+----+-------+
|  1 | 7kaji |
+----+-------+

mysql> change master to master_host='ubuntu_1', master_user='root', master_password='root';
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

mysql-util (mysqlfailover)

MySQL Utilities には、 mysqlfailover　以外にもにも便利ツールが含まれている

mysqlrplcheck (レプリケーションのチェック)

vagrant@vagrant:~$ mysqlrplcheck --master=root:root@ubuntu_1 --slave=root:root@ubuntu_2
WARNING: Using a password on the command line interface can be insecure.
# master on ubuntu_1: ... connected.
# slave on ubuntu_2: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Checking server_uuid values                                          [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [pass]
# ...done.

mysqlrpladmin (レプリケーション管理ユーティリティ)

vagrant@vagrant:~$ mysqlrpladmin --master=root:root@ubuntu_1 --slaves=root:root@ubuntu_2 health
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
#
# Replication Topology Health:
+-----------+-------+---------+--------+------------+---------+
| host      | port  | role    | state  | gtid_mode  | health  |
+-----------+-------+---------+--------+------------+---------+
| ubuntu_1  | 3306  | MASTER  | UP     | ON         | OK      |
| ubuntu_2  | 3306  | SLAVE   | UP     | ON         | OK      |
+-----------+-------+---------+--------+------------+---------+
# ...done.

failover

failover 後に実行される shell 用意しておく

read_only 変更のシェル

#!/bin/sh
mysql -uroot -p"root" -h ubuntu_2 -Ee 'set global read_only = 0;'

-（VIP を変えたり、Route53 で参照先を変更するスクリプト）

本番だったら、 --exec-post-failover に指定しておく

※ ほかにも、hook するするタイミングいろいろある

--exec-fail-check デフォルトのチェックのそれぞれ事前に定義された間隔で定期的に実行するスクリプトを指定
--exec-before フェイルオーバー開始する前に実行するスクリプトを指定
--exec-after フェイルオーバープロセスの終了時に実行するスクリプトを指定
--exec-post-failover フェイルオーバー後に実行するスクリプトを指定(ヘルスレポート等)

起動

vagrant@vagrant-ubuntu-trusty-64:~$ mysqlfailover \
--master=root:root@ubuntu_1 \
--slaves=root:root@ubuntu_2 \
--log=failover.log --failover-mode=auto \
--candidates=root:root@ubuntu_2 \
--rpl-user=’root:root’ \
--daemon=start \
--exec-after=/path/to/exec_read_only_off.sh

WARNING: Using a password on the command line interface can be insecure.
Starting failover daemon...
Multiple instances of failover daemon found for master ubuntu_1:3306.
If this is an error, restart the daemon with --force.
Failover mode changed to 'FAIL' for this instance.
Daemon will start in 10 seconds.
.........starting Daemon.

memo

--masterには現在のMaster DBを指定
--slavesには現在のSlave DBを指定
--logにはmysqlfailoverのログ出力ファイルを指定
--failover-modeにはautoを指定
--candidatesにはMaster DB障害時にMasterに昇格するSlave DBを指定（Masterに昇格するSlave候補を複数指定できますが意図的にSlaveを指定）
--rpl-userにはレプリケーションで利用しているユーザーを指定
--daemonにはstartを指定してデーモンとして起動するように指定

log

vagrant@vagrant:~$ tail -f log.txt
2017-09-17 21:34:37 PM INFO Health Status:
2017-09-17 21:34:37 PM INFO host: ubuntu_1, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK
2017-09-17 21:34:37 PM INFO host: ubuntu_2, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2017-09-17 21:34:55 PM INFO Master Information
2017-09-17 21:34:55 PM INFO Binary Log File: mysql-bin.000008, Position: 194, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A

Master を落とす

log

2017-09-17 22:27:07 PM INFO host: ubuntu_1, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK
2017-09-17 22:27:07 PM INFO host: ubuntu_2, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK
2017-09-17 22:27:30 PM INFO Master may be down. Waiting for 3 seconds.
2017-09-17 22:27:31 PM INFO Master may be down. Waiting for 3 seconds.
2017-09-17 22:27:45 PM INFO Failed to reconnect to the master after 3 attempts.
2017-09-17 22:27:45 PM CRITICAL Master is confirmed to be down or unreachable.
2017-09-17 22:27:45 PM CRITICAL Master has failed and automatic failover is not enabled. Check server for errors and run the mysqlrpladmin utility to perform manual failover.
2017-09-17 22:27:45 PM INFO Unregistering instance on master.
2017-09-17 22:27:45 PM INFO Failover daemon stopped.
2017-09-17 22:27:46 PM INFO Failed to reconnect to the master after 3 attempts.
2017-09-17 22:27:46 PM CRITICAL Master is confirmed to be down or unreachable.
2017-09-17 22:27:46 PM INFO Failover starting in 'auto' mode...
2017-09-17 22:27:46 PM INFO Candidate slave ubuntu_2:3306 will become the new master.
2017-09-17 22:27:46 PM INFO Checking slaves status (before failover).
2017-09-17 22:27:46 PM INFO Preparing candidate for failover.
2017-09-17 22:27:46 PM INFO Creating replication user if it does not exist.
2017-09-17 22:27:46 PM INFO Stopping slaves.
2017-09-17 22:27:46 PM INFO Performing STOP on all slaves.
2017-09-17 22:27:46 PM INFO Switching slaves to new master.
2017-09-17 22:27:46 PM INFO Disconnecting new master as slave.
2017-09-17 22:27:46 PM INFO Starting slaves.
2017-09-17 22:27:46 PM INFO Performing START on all slaves.
2017-09-17 22:27:46 PM INFO Checking slaves for errors.
2017-09-17 22:27:46 PM INFO Failover complete.
2017-09-17 22:27:51 PM INFO Unregistering existing instances from slaves.
2017-09-17 22:27:51 PM INFO Registering instance on new master ubuntu_2:3306.
2017-09-17 22:27:51 PM INFO Master Information
2017-09-17 22:27:51 PM INFO Binary Log File: mysql-bin.000004, Position: 2393, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2017-09-17 22:27:51 PM INFO GTID Executed Set: 12470d01-44d9-11e7-9923-080027e6df08:1-7[...]
2017-09-17 22:27:51 PM INFO Getting health for master: ubuntu_2:3306.
2017-09-17 22:27:51 PM INFO Health Status:
2017-09-17 22:27:51 PM INFO host: ubuntu_2, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK
2017-09-17 22:28:09 PM INFO Master Information
2017-09-17 22:28:09 PM INFO Binary Log File: mysql-bin.000004, Position: 2393, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2017-09-17 22:28:09 PM INFO GTID Executed Set: 12470d01-44d9-11e7-9923-080027e6df08:1-7[...]
2017-09-17 22:28:09 PM INFO Getting health for master: ubuntu_2:3306.
2017-09-17 22:28:09 PM INFO Health Status:
2017-09-17 22:28:09 PM INFO host: ubuntu_2, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK

failover 確認

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show slave status;

mysql> show master status\G

よくわかってないところ

mysqlfailover のデーモンが落ちたときに、slave のレプリケーションも止まってしまう?

SET SQL_LOG_BIN=0 しないで?、master に DELETE文を発行している

https://github.com/mysql/mysql-utilities/commit/a8ffdb25e03e131ecdf29df8d22e2b8257b7b6fc#diff-8ce25b7747043065acc05aff0c2906b1

=> slave では、mysql.failover_console というテーブルがないと怒られ、SQL スレッドが停止してしまう（レプリケーション停止）
=> mysqlfailover だけ落ちた場合に、レプリケーションに影響あるのは微妙すぎる
=> cleanup 時に self.master.toggle_binlog("DISABLE") を入れる対応をすればよい?

レプリケーション再開手順

※ GTID モードでは、 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; は使えない

mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G  # GTID確認
mysql> SET GTID_NEXT = 'スキップしたいGTID';
mysql> BEGIN;
mysql> COMMIT;
mysql> SET GTID_NEXT = AUTOMATIC;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

REF

MySQL :: MySQL Utilities 1.6 Manual : https://dev.mysql.com/doc/mysql-utilities/1.6/en/
mysqlfailoverを使ってみた | GMOインターネット次世代システム研究室 : http://recruit.gmo.jp/engineer/jisedai/blog/mysqlfailover/
MySQLレプリケーションをVagrantで試してみる。 - Qiita : https://qiita.com/7kaji/items/02f3940a8055a72f8ebe