MySQL MasterHA：安装好后的配置
Adamhuan发表于 2016-12-13 10:11:41
开始本文前，你需要有一个安装好了MHA架构的四台服务器。
关于MySQL MasterHA架构的安装，不是本文的重点，如果需要了解，可以去参考我的朋友的系列博文，写的非常详细：
http://soft.dog/2015/03/26/mysql-HA-build-percona-server5.1-install/
http://soft.dog/2015/03/27/mysql-HA-build-mha-install-and-config/
http://soft.dog/2015/03/30/mysql-HA-build-keepalived-install-and-config/
http://soft.dog/2015/03/31/mysql-HA-build-failover-test/
在这个系列的MHA架构部署的博文中，也写了关于MHA安装好后的配置的内容。
但是，我希望可以做出自己的探索，所以，有了本文。
下面开始。
————————————————————————————
创建需要的目录结构：
[root@mha1 mha]# mkdir -p /script/{shell,mha}
[root@mha1 mha]#
[root@mha1 mha]# mkdir -p /work_dir/mha{_manager,_node}
[root@mha1 mha]#
[root@mha1 mha]# tree /{script,work_dir}
/script
├── mha
└── shell
/work_dir
├── mha_manager
└── mha_node
4 directories, 0 files
[root@mha1 mha]#
在我的设计中：
/script，存放脚本
/script/mha，与MHA有关的脚本，包括MANAGER，Node，以及来自tar.gz的额外的脚本
/script/shell，与Linux SHELL有关的脚本
/work_dir，为工作目录
/work_dir/mha_manager，MHA Manager端的工作目录
/work_dir/mha_node，MHA NODE端的工作目录
将脚本放在指定目录下：
[root@mha1 scripts]# [root@mha1 scripts]# pwd /software/mha/mha4mysql-manager-0.56/samples/scripts [root@mha1 scripts]# [root@mha1 scripts]# ls -ltr total 32 -rwxr-xr-x 1 4984 users 3648 Apr 1 2014 master_ip_failover -rwxr-xr-x 1 4984 users 1360 Apr 1 2014 send_report -rwxr-xr-x 1 4984 users 11867 Apr 1 2014 power_manager -rwxr-xr-x 1 4984 users 9870 Apr 1 2014 master_ip_online_change [root@mha1 scripts]# [root@mha1 scripts]# cp * /script/mha/ [root@mha1 scripts]# [root@mha1 scripts]# ls /script/mha/ master_ip_failover master_ip_online_change power_manager send_report [root@mha1 scripts]# [root@mha1 scripts]#
修改环境变量：
[root@mha1 scripts]# cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:/script/mha/:$HOME/bin
export PATH
[root@mha1 scripts]#
[root@mha1 scripts]# source ~/.bash_profile
[root@mha1 scripts]#
[root@mha1 scripts]# env | grep --color mha
HOSTNAME=mha1
OLDPWD=/software/mha/mha4mysql-manager-0.56/samples
PATH=/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/script/mha/:/root/bin
PWD=/software/mha/mha4mysql-manager-0.56/samples/scripts
[root@mha1 scripts]#
[root@mha1 scripts]#
MasterHA，全局配置文件：
[root@mha1 ~]# cat /etc/masterha_default.cnf [server default] user=root password=oracle ssh_user=root master_binlog_dir=/var/lib/mysql remote_workdir=/work_dir/mha_node master_ip_failover_script=/script/mha/master_ip_failover master_ip_online_change_script=/script/mha/master_ip_online_change [root@mha1 ~]#
注意：
1. master_ip_failover_script，后面会开放这个参数的设定，但是现在，关闭
1. /script/mha/master_ip_failover，需要注释其中，包含【FIXME】的行，如下：
[root@mha1 scripts]# cat /script/mha/master_ip_failover | grep --color FIXME
#FIXME_xxx_create_user( $new_master_handler->{dbh} );
#FIXME_xxx;
[root@mha1 scripts]#
MasterHA，应用配置文件：
[root@mha1 ~]# cat /etc/masterha_application_1.cnf [server default] manager_workdir=/work_dir/mha_manager manager_log=/work_dir/mha_manager/mha_manager.log [server1] hostname=mha2 candidate_master=1 [server2] hostname=mha3 candidate_master=1 [server3] hostname=mha4 #candidate_master=1 no_master=1 [root@mha1 ~]#
配置SSH可信关系：
根据上面【全局配置文件】的指定【ssh_user】，这里配置可信关系的用户为：root
所有节点，均如下设置：
[root@mha1 ~]# ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is: 1f:ce:35:3e:59:a9:ce:9e:97:85:27:a1:6d:cc:ef:20 root@mha1 The key's randomart image is: +--[ RSA 2048]----+ | | | | | | | .. | | S . o=oo | | + +.=B o| | + E..* | | o +o..| | .=. ..| +-----------------+ [root@mha1 ~]# [root@mha1 ~]# ssh-copy-id -i .ssh/id_rsa.pub mha1 The authenticity of host 'mha1 (10.158.1.94)' can't be established. RSA key fingerprint is f4:3d:38:c4:89:2b:d3:32:be:06:7d:54:7e:a8:74:b3. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'mha1,10.158.1.94' (RSA) to the list of known hosts. root@mha1's password: Now try logging into the machine, with "ssh 'mha1'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting. [root@mha1 ~]# [root@mha1 ~]# ssh-copy-id -i .ssh/id_rsa.pub mha2 root@mha2's password: Now try logging into the machine, with "ssh 'mha2'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting. [root@mha1 ~]# [root@mha1 ~]# ssh-copy-id -i .ssh/id_rsa.pub mha3 root@mha3's password: Now try logging into the machine, with "ssh 'mha3'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting. [root@mha1 ~]# [root@mha1 ~]# ssh-copy-id -i .ssh/id_rsa.pub mha4 root@mha4's password: Now try logging into the machine, with "ssh 'mha4'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting. [root@mha1 ~]# [root@mha1 ~]# ssh mha1 date Tue Dec 13 13:53:38 CST 2016 [root@mha1 ~]# [root@mha1 ~]# ssh mha2 date Tue Dec 13 13:58:52 CST 2016 [root@mha1 ~]# [root@mha1 ~]# ssh mha3 date Tue Dec 13 13:57:10 CST 2016 [root@mha1 ~]# [root@mha1 ~]# ssh mha4 date Tue Dec 13 13:57:04 CST 2016 [root@mha1 ~]# [root@mha1 ~]#
添加MySQL的访问权限：
[root@mha2 ~]# mysql -uroot -p'Abcd1@34' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 54 Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user,host from mysql.user; +-----------+------------+ | user | host | +-----------+------------+ | root | % | | mhame | 10.158.1.% | | replme | 10.158.1.% | | mysql.sys | localhost | | root | localhost | | root | mha1 | +-----------+------------+ 6 rows in set (0.02 sec) mysql> grant all privileges on *.* to root@'mha1' identified by 'Abcd1@34'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> grant all privileges on *.* to root@'mha2' identified by 'Abcd1@34'; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> grant all privileges on *.* to root@'mha3' identified by 'Abcd1@34'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> grant all privileges on *.* to root@'mha4' identified by 'Abcd1@34'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> select user,host from mysql.user; +-----------+------------+ | user | host | +-----------+------------+ | root | % | | mhame | 10.158.1.% | | replme | 10.158.1.% | | mysql.sys | localhost | | root | localhost | | root | mha1 | | root | mha2 | | root | mha3 | | root | mha4 | +-----------+------------+ 9 rows in set (0.00 sec) mysql>
MHA脚本：检查SSH
[root@mha1 ~]# masterha_check_ssh --conf=/etc/masterha_application_1.cnf Tue Dec 13 13:58:36 2016 - [info] Reading default configuration from /etc/masterha_default.cnf.. Tue Dec 13 13:58:36 2016 - [info] Reading application default configuration from /etc/masterha_application_1.cnf.. Tue Dec 13 13:58:36 2016 - [info] Reading server configuration from /etc/masterha_application_1.cnf.. Tue Dec 13 13:58:36 2016 - [info] Starting SSH connection tests.. Tue Dec 13 13:58:37 2016 - [debug] Tue Dec 13 13:58:36 2016 - [debug] Connecting via SSH from root@mha2(10.158.1.95:22) to root@mha3(10.158.1.96:22).. Tue Dec 13 13:58:37 2016 - [debug] ok. Tue Dec 13 13:58:37 2016 - [debug] Connecting via SSH from root@mha2(10.158.1.95:22) to root@mha4(10.158.1.97:22).. Tue Dec 13 13:58:37 2016 - [debug] ok. Tue Dec 13 13:58:37 2016 - [debug] Tue Dec 13 13:58:37 2016 - [debug] Connecting via SSH from root@mha4(10.158.1.97:22) to root@mha2(10.158.1.95:22).. Tue Dec 13 13:58:37 2016 - [debug] ok. Tue Dec 13 13:58:37 2016 - [debug] Connecting via SSH from root@mha4(10.158.1.97:22) to root@mha3(10.158.1.96:22).. Tue Dec 13 13:58:37 2016 - [debug] ok. Tue Dec 13 13:58:38 2016 - [debug] Tue Dec 13 13:58:36 2016 - [debug] Connecting via SSH from root@mha3(10.158.1.96:22) to root@mha2(10.158.1.95:22).. Tue Dec 13 13:58:37 2016 - [debug] ok. Tue Dec 13 13:58:37 2016 - [debug] Connecting via SSH from root@mha3(10.158.1.96:22) to root@mha4(10.158.1.97:22).. Tue Dec 13 13:58:38 2016 - [debug] ok. Tue Dec 13 13:58:38 2016 - [info] All SSH connection tests passed successfully. [root@mha1 ~]# [root@mha1 ~]#
MHA脚本：检查MySQL主从复制状态
[root@mha1 ~]# masterha_check_repl --conf=/etc/masterha_application_1.cnf Tue Dec 13 13:59:49 2016 - [info] Reading default configuration from /etc/masterha_default.cnf.. Tue Dec 13 13:59:49 2016 - [info] Reading application default configuration from /etc/masterha_application_1.cnf.. Tue Dec 13 13:59:49 2016 - [info] Reading server configuration from /etc/masterha_application_1.cnf.. Tue Dec 13 13:59:49 2016 - [info] MHA::MasterMonitor version 0.56. Tue Dec 13 13:59:50 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting mha3(10.158.1.96:3306) :1045:Access denied for user 'root'@'mha1' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297 Tue Dec 13 13:59:50 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting mha4(10.158.1.97:3306) :1045:Access denied for user 'root'@'mha1' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297 Tue Dec 13 13:59:50 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting mha2(10.158.1.95:3306) :1045:Access denied for user 'root'@'mha1' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297 Tue Dec 13 13:59:50 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations Tue Dec 13 13:59:50 2016 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326 Tue Dec 13 13:59:50 2016 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers. Tue Dec 13 13:59:50 2016 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK! [root@mha1 ~]#
上面，报错的原因非常明显：
【Access denied for user ‘root’@’mha1’ (using password: YES)】
进入数据库做出更正：
mysql> grant all privileges on *.* to root@'%' identified by 'Abcd!234'; Query OK, 0 rows affected, 1 warning (0.12 sec) mysql>
修改配置文件的对应记录：
[root@mha1 ~]# cat /etc/masterha_default.cnf | grep password password=Abcd!234 [root@mha1 ~]#
再次检查：
[root@mha1 scripts]# masterha_check_repl --conf=/etc/masterha_application_1.cnf
Tue Dec 13 14:21:17 2016 - [info] Reading default configuration from /etc/masterha_default.cnf..
Tue Dec 13 14:21:17 2016 - [info] Reading application default configuration from /etc/masterha_application_1.cnf..
Tue Dec 13 14:21:17 2016 - [info] Reading server configuration from /etc/masterha_application_1.cnf..
Tue Dec 13 14:21:17 2016 - [info] MHA::MasterMonitor version 0.56.
Tue Dec 13 14:21:17 2016 - [info] GTID failover mode = 0
Tue Dec 13 14:21:17 2016 - [info] Dead Servers:
Tue Dec 13 14:21:17 2016 - [info] Alive Servers:
Tue Dec 13 14:21:17 2016 - [info] mha2(10.158.1.95:3306)
Tue Dec 13 14:21:17 2016 - [info] mha3(10.158.1.96:3306)
Tue Dec 13 14:21:17 2016 - [info] mha4(10.158.1.97:3306)
Tue Dec 13 14:21:17 2016 - [info] Alive Slaves:
Tue Dec 13 14:21:17 2016 - [info] mha3(10.158.1.96:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Tue Dec 13 14:21:17 2016 - [info] Replicating from 10.158.1.95(10.158.1.95:3306)
Tue Dec 13 14:21:17 2016 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Dec 13 14:21:17 2016 - [info] mha4(10.158.1.97:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Tue Dec 13 14:21:17 2016 - [info] Replicating from 10.158.1.95(10.158.1.95:3306)
Tue Dec 13 14:21:17 2016 - [info] Not candidate for the new Master (no_master is set)
Tue Dec 13 14:21:17 2016 - [info] Current Alive Master: mha2(10.158.1.95:3306)
Tue Dec 13 14:21:17 2016 - [info] Checking slave configurations..
Tue Dec 13 14:21:17 2016 - [info] Checking replication filtering settings..
Tue Dec 13 14:21:17 2016 - [info] binlog_do_db= , binlog_ignore_db=
Tue Dec 13 14:21:17 2016 - [info] Replication filtering check ok.
Tue Dec 13 14:21:17 2016 - [info] GTID (with auto-pos) is not supported
Tue Dec 13 14:21:17 2016 - [info] Starting SSH connection tests..
Tue Dec 13 14:21:19 2016 - [info] All SSH connection tests passed successfully.
Tue Dec 13 14:21:19 2016 - [info] Checking MHA Node version..
Tue Dec 13 14:21:19 2016 - [info] Version check ok.
Tue Dec 13 14:21:19 2016 - [info] Checking SSH publickey authentication settings on the current master..
Tue Dec 13 14:21:19 2016 - [info] HealthCheck: SSH to mha2 is reachable.
Tue Dec 13 14:21:19 2016 - [info] Master MHA Node version is 0.56.
Tue Dec 13 14:21:19 2016 - [info] Checking recovery script configurations on mha2(10.158.1.95:3306)..
Tue Dec 13 14:21:19 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/work_dir/mha_node/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000003
Tue Dec 13 14:21:19 2016 - [info] Connecting to root@10.158.1.95(mha2:22)..
Creating /work_dir/mha_node if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000003
Tue Dec 13 14:21:20 2016 - [info] Binlog setting check done.
Tue Dec 13 14:21:20 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Dec 13 14:21:20 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha3 --slave_ip=10.158.1.96 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Dec 13 14:21:20 2016 - [info] Connecting to root@10.158.1.96(mha3:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to relay-bin.000002
Temporary relay log file is /var/lib/mysql/relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 13 14:21:20 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha4 --slave_ip=10.158.1.97 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Dec 13 14:21:20 2016 - [info] Connecting to root@10.158.1.97(mha4:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to relay-bin.000003
Temporary relay log file is /var/lib/mysql/relay-bin.000003
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 13 14:21:21 2016 - [info] Slaves settings check done.
Tue Dec 13 14:21:21 2016 - [info]
mha2(10.158.1.95:3306) (current master)
+--mha3(10.158.1.96:3306)
+--mha4(10.158.1.97:3306)
Tue Dec 13 14:21:21 2016 - [info] Checking replication health on mha3..
Tue Dec 13 14:21:21 2016 - [info] ok.
Tue Dec 13 14:21:21 2016 - [info] Checking replication health on mha4..
Tue Dec 13 14:21:21 2016 - [info] ok.
Tue Dec 13 14:21:21 2016 - [info] Checking master_ip_failover_script status:
Tue Dec 13 14:21:21 2016 - [info] /script/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=mha2 --orig_master_ip=10.158.1.95 --orig_master_port=3306
Tue Dec 13 14:21:21 2016 - [info] OK.
Tue Dec 13 14:21:21 2016 - [warning] shutdown_script is not defined.
Tue Dec 13 14:21:21 2016 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@mha1 scripts]#
[root@mha1 scripts]#
[root@mha1 scripts]#可以看到，MySQL主从复制，状态正常。
————————————————————————————————————
【接下来，要考虑VIP资源的配置策略了。】
方案一：keepalive
仅仅在MySQL的【主】与【备主】上操作。
KeepAlive的配置：
MySQL主从：Master
[root@mha2 ~]# cat /etc/keepalived/keepalived.conf | grep -v '#' | strings
! Configuration File for keepalived
global_defs {
router_id mha2
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 1
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.158.1.111
}
[root@mha2 ~]#
MySQL主从：Master Behind
传递到远端：
[root@mha2 ~]# rsync -v /etc/keepalived/keepalived.conf mha3:/etc/keepalived/ keepalived.conf sent 728 bytes received 67 bytes 1590.00 bytes/sec total size is 649 speedup is 0.82 [root@mha2 ~]#
修改配置后的状态：
[root@mha3 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
# notification_email {
# acassen@firewall.loc
# failover@firewall.loc
# sysadmin@firewall.loc
# }
# notification_email_from Alexandre.Cassen@firewall.loc
# smtp_server 192.168.200.1
# smtp_connect_timeout 30
# router_id LVS_DEVEL
router_id mha2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 2
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#192.168.200.16
#192.168.200.17
#192.168.200.18
10.158.1.111
}
}
[root@mha3 ~]#
启动KEEPALIVED。
mha2：
[root@mha2 ~]# ip a
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:50:56:84:75:0f brd ff:ff:ff:ff:ff:ff
inet 10.158.1.95/23 brd 10.158.1.255 scope global eth0
inet6 fe80::250:56ff:fe84:750f/64 scope link
valid_lft forever preferred_lft forever
[root@mha2 ~]#
[root@mha2 ~]# service keepalived start
Starting keepalived: [ OK ]
[root@mha2 ~]#
[root@mha2 ~]# ip a
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:50:56:84:75:0f brd ff:ff:ff:ff:ff:ff
inet 10.158.1.95/23 brd 10.158.1.255 scope global eth0
inet6 fe80::250:56ff:fe84:750f/64 scope link
valid_lft forever preferred_lft forever
[root@mha2 ~]#
[root@mha2 ~]#
mha3：
[root@mha3 ~]# ip a
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:50:56:84:2b:49 brd ff:ff:ff:ff:ff:ff
inet 10.158.1.96/23 brd 10.158.1.255 scope global eth0
inet6 fe80::250:56ff:fe84:2b49/64 scope link
valid_lft forever preferred_lft forever
[root@mha3 ~]#
[root@mha3 ~]# service keepalived start
Starting keepalived: [ OK ]
[root@mha3 ~]#
[root@mha3 ~]# ip a
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:50:56:84:2b:49 brd ff:ff:ff:ff:ff:ff
inet 10.158.1.96/23 brd 10.158.1.255 scope global eth0
inet 10.158.1.111/32 scope global eth0
inet6 fe80::250:56ff:fe84:2b49/64 scope link
valid_lft forever preferred_lft forever
[root@mha3 ~]#
[root@mha3 ~]# ip a
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:50:56:84:2b:49 brd ff:ff:ff:ff:ff:ff
inet 10.158.1.96/23 brd 10.158.1.255 scope global eth0
inet 10.158.1.111/32 scope global eth0
inet6 fe80::250:56ff:fe84:2b49/64 scope link
valid_lft forever preferred_lft forever
[root@mha3 ~]#
注意：
keepalive全部启用的时候，VIP活跃在【priority】数值较大的服务器端。
——————————————————————
修改脚本：masterha_ip_failover
注意：
修改过的地方，只有三处。
修改脚本：master_ip_online_change
[root@mha1 scripts]# grep -A 2 "new_master_handler->connect" master_ip_online_change
$new_master_handler->connect( $new_master_ip, $new_master_port,
"root", "Abcd!234", 1 );
--
$new_master_handler->connect( $new_master_ip, $new_master_port,
"root", "Abcd!234", 1 );
[root@mha1 scripts]#
[root@mha1 scripts]#
[root@mha1 scripts]# grep "FIXME" master_ip_online_change
#FIXME_xxx_drop_app_user($orig_master_handler);
#FIXME_xxx_create_app_user($new_master_handler);
[root@mha1 scripts]#
[root@mha1 scripts]# grep -A 2 "Update master ip on the catalog database, etc" master_ip_online_change
## Update master ip on the catalog database, etc
`/usr/bin/ssh -t root\@${orig_master_ip} "service keepalived stop"`
[root@mha1 scripts]#
这样，对两个脚本的配置与修改就结束了。
——————————————
下面开始测试MySQL MHA。
测试前：
看看当前的IP分配的情况：
（这里跟上面的配置不同，我调整了mha2的keepalived配置中的priority为3，以便于VIP活跃在mha2上面）
watch -n .1 -d “ip a; echo ‘—————-‘; service keepalived status; service mysql status”
检查Mysql的情况：
Master：mha2
[root@mha2 ~]# mysql -uroot -p'Abcd!234' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 1930 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> mysql> show variables like 'relay_log_purge'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | relay_log_purge | OFF | +-----------------+-------+ 1 row in set (0.02 sec) mysql> mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> mysql>
Master Behind：mha3
[root@mha3 ~]# mysql -uroot -p'Abcd!234'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.158.1.95
Master_User: replme
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1930
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2143
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1930
Relay_Log_Space: 2344
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: ffc6cbb6-be85-11e6-8a10-00505684750f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql> show variables like 'relay_log_purge';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| relay_log_purge | OFF |
+-----------------+-------+
1 row in set (0.04 sec)
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql>
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1140 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> exit
Bye
[root@mha3 ~]#
Slave：mha4
[root@mha4 ~]# mysql -uroot -p'Abcd!234'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.158.1.95
Master_User: replme
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1930
Relay_Log_File: relay-bin.000003
Relay_Log_Pos: 883
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1930
Relay_Log_Space: 2711
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: ffc6cbb6-be85-11e6-8a10-00505684750f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show variables like 'relay_log_purge';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| relay_log_purge | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 846 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> exit
Bye
[root@mha4 ~]#
MHA脚本：复制检查：
[root@mha1 scripts]# masterha_check_repl --conf=/etc/masterha_application_1.cnf
Tue Dec 13 16:42:42 2016 - [info] Reading default configuration from /etc/masterha_default.cnf..
Tue Dec 13 16:42:42 2016 - [info] Reading application default configuration from /etc/masterha_application_1.cnf..
Tue Dec 13 16:42:42 2016 - [info] Reading server configuration from /etc/masterha_application_1.cnf..
Tue Dec 13 16:42:42 2016 - [info] MHA::MasterMonitor version 0.56.
Tue Dec 13 16:42:43 2016 - [info] GTID failover mode = 0
Tue Dec 13 16:42:43 2016 - [info] Dead Servers:
Tue Dec 13 16:42:43 2016 - [info] Alive Servers:
Tue Dec 13 16:42:43 2016 - [info] mha2(10.158.1.95:3306)
Tue Dec 13 16:42:43 2016 - [info] mha3(10.158.1.96:3306)
Tue Dec 13 16:42:43 2016 - [info] mha4(10.158.1.97:3306)
Tue Dec 13 16:42:43 2016 - [info] Alive Slaves:
Tue Dec 13 16:42:43 2016 - [info] mha3(10.158.1.96:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Tue Dec 13 16:42:43 2016 - [info] Replicating from 10.158.1.95(10.158.1.95:3306)
Tue Dec 13 16:42:43 2016 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Dec 13 16:42:43 2016 - [info] mha4(10.158.1.97:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled
Tue Dec 13 16:42:43 2016 - [info] Replicating from 10.158.1.95(10.158.1.95:3306)
Tue Dec 13 16:42:43 2016 - [info] Not candidate for the new Master (no_master is set)
Tue Dec 13 16:42:43 2016 - [info] Current Alive Master: mha2(10.158.1.95:3306)
Tue Dec 13 16:42:43 2016 - [info] Checking slave configurations..
Tue Dec 13 16:42:43 2016 - [info] Checking replication filtering settings..
Tue Dec 13 16:42:43 2016 - [info] binlog_do_db= , binlog_ignore_db=
Tue Dec 13 16:42:43 2016 - [info] Replication filtering check ok.
Tue Dec 13 16:42:43 2016 - [info] GTID (with auto-pos) is not supported
Tue Dec 13 16:42:43 2016 - [info] Starting SSH connection tests..
Tue Dec 13 16:42:44 2016 - [info] All SSH connection tests passed successfully.
Tue Dec 13 16:42:44 2016 - [info] Checking MHA Node version..
Tue Dec 13 16:42:45 2016 - [info] Version check ok.
Tue Dec 13 16:42:45 2016 - [info] Checking SSH publickey authentication settings on the current master..
Tue Dec 13 16:42:45 2016 - [info] HealthCheck: SSH to mha2 is reachable.
Tue Dec 13 16:42:45 2016 - [info] Master MHA Node version is 0.56.
Tue Dec 13 16:42:45 2016 - [info] Checking recovery script configurations on mha2(10.158.1.95:3306)..
Tue Dec 13 16:42:45 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/work_dir/mha_node/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000003
Tue Dec 13 16:42:45 2016 - [info] Connecting to root@10.158.1.95(mha2:22)..
Creating /work_dir/mha_node if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000003
Tue Dec 13 16:42:46 2016 - [info] Binlog setting check done.
Tue Dec 13 16:42:46 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Dec 13 16:42:46 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha3 --slave_ip=10.158.1.96 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Dec 13 16:42:46 2016 - [info] Connecting to root@10.158.1.96(mha3:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to relay-bin.000002
Temporary relay log file is /var/lib/mysql/relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 13 16:42:46 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mha4 --slave_ip=10.158.1.97 --slave_port=3306 --workdir=/work_dir/mha_node --target_version=5.7.15-9-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Tue Dec 13 16:42:46 2016 - [info] Connecting to root@10.158.1.97(mha4:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to relay-bin.000003
Temporary relay log file is /var/lib/mysql/relay-bin.000003
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 13 16:42:47 2016 - [info] Slaves settings check done.
Tue Dec 13 16:42:47 2016 - [info]
mha2(10.158.1.95:3306) (current master)
+--mha3(10.158.1.96:3306)
+--mha4(10.158.1.97:3306)
Tue Dec 13 16:42:47 2016 - [info] Checking replication health on mha3..
Tue Dec 13 16:42:47 2016 - [info] ok.
Tue Dec 13 16:42:47 2016 - [info] Checking replication health on mha4..
Tue Dec 13 16:42:47 2016 - [info] ok.
Tue Dec 13 16:42:47 2016 - [info] Checking master_ip_failover_script status:
Tue Dec 13 16:42:47 2016 - [info] /script/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=mha2 --orig_master_ip=10.158.1.95 --orig_master_port=3306
Tue Dec 13 16:42:47 2016 - [info] OK.
Tue Dec 13 16:42:47 2016 - [warning] shutdown_script is not defined.
Tue Dec 13 16:42:47 2016 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@mha1 scripts]#
MHA脚本：执行在线切换：
[root@mha1 scripts]# masterha_master_switch --master_state=alive --conf=/etc/masterha_application_1.cnf --new_master_host=mha3 --orig_master_is_new_slave --interactive=0 Tue Dec 13 17:42:59 2016 - [info] MHA::MasterRotate version 0.56. Tue Dec 13 17:42:59 2016 - [info] Starting online master switch.. Tue Dec 13 17:42:59 2016 - [info] Tue Dec 13 17:42:59 2016 - [info] * Phase 1: Configuration Check Phase.. Tue Dec 13 17:42:59 2016 - [info] Tue Dec 13 17:42:59 2016 - [info] Reading default configuration from /etc/masterha_default.cnf.. Tue Dec 13 17:42:59 2016 - [info] Reading application default configuration from /etc/masterha_application_1.cnf.. Tue Dec 13 17:42:59 2016 - [info] Reading server configuration from /etc/masterha_application_1.cnf.. Tue Dec 13 17:43:00 2016 - [info] GTID failover mode = 0 Tue Dec 13 17:43:00 2016 - [info] Current Alive Master: mha2(10.158.1.95:3306) Tue Dec 13 17:43:00 2016 - [info] Alive Slaves: Tue Dec 13 17:43:00 2016 - [info] mha3(10.158.1.96:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled Tue Dec 13 17:43:00 2016 - [info] Replicating from 10.158.1.95(10.158.1.95:3306) Tue Dec 13 17:43:00 2016 - [info] Primary candidate for the new Master (candidate_master is set) Tue Dec 13 17:43:00 2016 - [info] mha4(10.158.1.97:3306) Version=5.7.15-9-log (oldest major version between slaves) log-bin:enabled Tue Dec 13 17:43:00 2016 - [info] Replicating from 10.158.1.95(10.158.1.95:3306) Tue Dec 13 17:43:00 2016 - [info] Not candidate for the new Master (no_master is set) Tue Dec 13 17:43:00 2016 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time.. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] Checking MHA is not monitoring or doing failover.. Tue Dec 13 17:43:00 2016 - [info] Checking replication health on mha3.. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] Checking replication health on mha4.. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] mha3 can be new master. Tue Dec 13 17:43:00 2016 - [info] From: mha2(10.158.1.95:3306) (current master) +--mha3(10.158.1.96:3306) +--mha4(10.158.1.97:3306) To: mha3(10.158.1.96:3306) (new master) +--mha4(10.158.1.97:3306) +--mha2(10.158.1.95:3306) Tue Dec 13 17:43:00 2016 - [info] Checking whether mha3(10.158.1.96:3306) is ok for the new master.. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] mha2(10.158.1.95:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host. Tue Dec 13 17:43:00 2016 - [info] mha2(10.158.1.95:3306): Resetting slave pointing to the dummy host. Tue Dec 13 17:43:00 2016 - [info] ** Phase 1: Configuration Check Phase completed. Tue Dec 13 17:43:00 2016 - [info] Tue Dec 13 17:43:00 2016 - [info] * Phase 2: Rejecting updates Phase.. Tue Dec 13 17:43:00 2016 - [info] Tue Dec 13 17:43:00 2016 - [info] Executing master ip online change script to disable write on the current master: Tue Dec 13 17:43:00 2016 - [info] /script/mha/master_ip_online_change --command=stop --orig_master_host=mha2 --orig_master_ip=10.158.1.95 --orig_master_port=3306 --orig_master_user='root' --orig_master_password='Abcd1@34' --new_master_host=mha3 --new_master_ip=10.158.1.96 --new_master_port=3306 --new_master_user='root' --new_master_password='Abcd1@34' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave Tue Dec 13 17:43:00 2016 322581 Set read_only on the new master.. ok. Tue Dec 13 17:43:00 2016 335173 Drpping app user on the orig master.. Tue Dec 13 17:43:00 2016 335754 Set read_only=1 on the orig master.. ok. Tue Dec 13 17:43:00 2016 337727 Killing all application threads.. Tue Dec 13 17:43:00 2016 337768 done. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] Locking all tables on the orig master to reject updates from everybody (including root): Tue Dec 13 17:43:00 2016 - [info] Executing FLUSH TABLES WITH READ LOCK.. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] Orig master binlog:pos is mysql-bin.000003:3754. Tue Dec 13 17:43:00 2016 - [info] Waiting to execute all relay logs on mha3(10.158.1.96:3306).. Tue Dec 13 17:43:00 2016 - [info] master_pos_wait(mysql-bin.000003:3754) completed on mha3(10.158.1.96:3306). Executed 0 events. Tue Dec 13 17:43:00 2016 - [info] done. Tue Dec 13 17:43:00 2016 - [info] Getting new master's binlog name and position.. Tue Dec 13 17:43:00 2016 - [info] mysql-bin.000003:1572 Tue Dec 13 17:43:00 2016 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='mha3 or 10.158.1.96', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1572, MASTER_USER='replme', MASTER_PASSWORD='xxx'; Tue Dec 13 17:43:00 2016 - [info] Executing master ip online change script to allow write on the new master: Tue Dec 13 17:43:00 2016 - [info] /script/mha/master_ip_online_change --command=start --orig_master_host=mha2 --orig_master_ip=10.158.1.95 --orig_master_port=3306 --orig_master_user='root' --orig_master_password='Abcd1@34' --new_master_host=mha3 --new_master_ip=10.158.1.96 --new_master_port=3306 --new_master_user='root' --new_master_password='Abcd1@34' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave Tue Dec 13 17:43:00 2016 470031 Set read_only=0 on the new master. Tue Dec 13 17:43:00 2016 470839 Creating app user on the new master.. Tue Dec 13 17:43:00 2016 - [info] ok. Tue Dec 13 17:43:00 2016 - [info] Tue Dec 13 17:43:00 2016 - [info] * Switching slaves in parallel.. Tue Dec 13 17:43:00 2016 - [info] Tue Dec 13 17:43:00 2016 - [info] -- Slave switch on host mha4(10.158.1.97:3306) started, pid: 32319 Tue Dec 13 17:43:00 2016 - [info] Tue Dec 13 17:43:01 2016 - [info] Log messages from mha4 ... Tue Dec 13 17:43:01 2016 - [info] Tue Dec 13 17:43:00 2016 - [info] Waiting to execute all relay logs on mha4(10.158.1.97:3306).. Tue Dec 13 17:43:00 2016 - [info] master_pos_wait(mysql-bin.000003:3754) completed on mha4(10.158.1.97:3306). Executed 0 events. Tue Dec 13 17:43:00 2016 - [info] done. Tue Dec 13 17:43:00 2016 - [info] Resetting slave mha4(10.158.1.97:3306) and starting replication from the new master mha3(10.158.1.96:3306).. Tue Dec 13 17:43:01 2016 - [info] Executed CHANGE MASTER. Tue Dec 13 17:43:01 2016 - [info] Slave started. Tue Dec 13 17:43:01 2016 - [info] End of log messages from mha4 ... Tue Dec 13 17:43:01 2016 - [info] Tue Dec 13 17:43:01 2016 - [info] -- Slave switch on host mha4(10.158.1.97:3306) succeeded. Tue Dec 13 17:43:01 2016 - [info] Unlocking all tables on the orig master: Tue Dec 13 17:43:01 2016 - [info] Executing UNLOCK TABLES.. Tue Dec 13 17:43:01 2016 - [info] ok. Tue Dec 13 17:43:01 2016 - [info] Starting orig master as a new slave.. Tue Dec 13 17:43:01 2016 - [info] Resetting slave mha2(10.158.1.95:3306) and starting replication from the new master mha3(10.158.1.96:3306).. Tue Dec 13 17:43:01 2016 - [info] Executed CHANGE MASTER. Tue Dec 13 17:43:01 2016 - [info] Slave started. Tue Dec 13 17:43:01 2016 - [info] All new slave servers switched successfully. Tue Dec 13 17:43:01 2016 - [info] Tue Dec 13 17:43:01 2016 - [info] * Phase 5: New master cleanup phase.. Tue Dec 13 17:43:01 2016 - [info] Tue Dec 13 17:43:01 2016 - [info] mha3: Resetting slave info succeeded. Tue Dec 13 17:43:01 2016 - [info] Switching master to mha3(10.158.1.96:3306) completed successfully. [root@mha1 scripts]#
切换后，查看MHA各个资源的状态：
MySQL：mha2
[root@mha2 ~]# mysql -uroot -p'Abcd!234'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 89
Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mha3
Master_User: replme
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1572
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1572
Relay_Log_Space: 521
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: c5da07fe-be85-11e6-8b17-005056842b49
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
MySQL：mha3
[root@mha3 ~]# mysql -uroot -p'Abcd1@34'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 101
Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show slave status\G
Empty set (0.00 sec)
mysql>
mysql> show master status
-> ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 1572 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
MySQL：mha4
[root@mha4 ~]# mysql -uroot -p'Abcd1@34'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 79
Server version: 5.7.15-9-log Percona Server (GPL), Release 9, Revision 9f0fd0a
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.158.1.96
Master_User: replme
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1572
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1572
Relay_Log_Space: 521
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: c5da07fe-be85-11e6-8b17-005056842b49
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>可以看到，手动切换（计划切换）完成，旧的Master已经作为slave加入了新的Master。
————————————————————
Done。