Mysql MHA配置文档

Mysql MHA配置文档：


环境说明:
Master：10.100.251.221:3306
Slave1：10.100.251.222:3306 （候选master）
Slave2：10.100.251.223:3306  (+MHA Manager)
vip:10.100.251.228


1、配置主从同步
一、主服务器
1.1、创建一个复制用户，具有replication slave 权限。
mysql> grant replication slave on *.* to 'slave001'@'%' identified by 'slave001';
1.2、编辑my.cnf文件
vi /etc/my.cnf
添加:
server-id=221
1.3、并开启log-bin二进制日志文件
log-bin=mysql-bin
1.4、启动mysql数据库
service mysql start
1.5、得到binlog日志文件名和偏移量
mysql> show master status;  
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      551 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1.6、备份要同步的数据库
mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases repdb > /tmp/backup/db.sql
或：
mysqldump --master-data=2 --single-transaction --events -R --flush-logs --triggers --databases repdb > all.sql
1.7、查看position
[root@mytest01 backup]# grep MASTER /tmp/backup/db.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=120;
[root@mytest01 data]#


二、从服务器
2.0、创建一个复制用户，具有replication slave 权限。
mysql> grant replication slave on *.* to 'slave001'@'%' identified by 'slave001';
2.1、编辑my.cnf文件
vi /etc/my.cnf
添加
server-id=222
log-bin = mysql-bin
log-bin-index = mysql-bin.index
read_only=1
relay_log_purge=0 #（一主一丛不需要此项，两从及以上建议开次参数，防止切换为成主库的从库自动删除中继日志后，无法给其他从库应用这部分日志）
注:需要把默认的server-id=1去掉
不要尝试把master配置属性写在my.cnf 中，5.1.7以后，mysql已经不支持这样做了
2.2、启动从数据库
service mysql start
2.3、把生产的数据导进从服务器：
mysql -uroot -proot123</tmp/backup/db.sql
2.3、对从数据库进行相应设置
mysql> change master to  
master_host='10.100.251.221', 
master_user='slave001',
master_password='slave001', 
master_log_file='mysql-bin.000009', 
master_log_pos=120;
2.4、启动从服务器slave线程
mysql>start slave; 
执行show processlist命令显示以下进程:
mysql>show processlist\G  
 
*************************** 2. row ***************************  
Id: 2  
User: system user  
Host:  
db: NULL  
Command: Connect  
Time: 2579  
State: Has read all relay log; waiting for the slave I/O thread to update it 
Info: NULL表示slave已经连接上master，开始接受并执行日志
2.5、查看slave线程状态
mysql>show slave status;  
*************************** 1. row ***************************  
Slave_IO_State: Waiting for master to send event  
Master_Host: 10.100.251.221  
Master_User: repl  
Master_Port: 3306  
Connect_Retry: 60  
Master_Log_File: mysql-bin.0000010  
Read_Master_Log_Pos: 106  
Relay_Log_File: centos-relay-bin.000002  
Relay_Log_Pos: 529  
Relay_Master_Log_File: mysql-bin.0000010  
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: 106  
Relay_Log_Space: 830  
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:  
1 row in set (0.00 sec) 
验证是否配置正确
在从服务器上执行
mysql> show slave status\G
Waiting for master to send event  
Slave_IO_Running: Yes  
Slave_SQL_Running: Yes 
如以上二行同时为Yes 说明配置成功
PS：show slave status\G 后不要添加 ； 号， 不然会出 ERROR




2、配置ssh公钥互信


一、本例中manager节点和node节点ip
manager：10.100.251.223
node1：10.100.251.221
node2：10.100.251.222
node3：10.100.251.223


注：manager节点可以安装独立的服务器上，本例为了节省机器，manager安装在了主库（10.100.251.223）上.


二. 配置manager和node各节点间的root用户的ssh公钥互信
配置ssh免密码连入
注意要以root用户登录，在root用户的主目录下进行操作。--一台服务器上操作就可以
# ssh-keygen
# ssh-copy-id -p 22 root@10.100.251.221
# ssh-copy-id -p 22 root@10.100.251.222
# ssh-copy-id -p 22 root@10.100.251.223




3、安装 MHA 包


一. 下载MHA安装包
下载网址：
https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2


下载rpm包或tar all均可，建议用rpm包，因为安装简单。


二. 安装 MHA Node
在manager和node的所有节点均需安装MHA Node。
rpm安装方式：
# yum install perl-DBD-MySQL
# rpm -ivh mha4mysql-node-0.56-0.el5.noarch.rpm
如果有依赖：
[root@10.100.251.221 ~]# cat install.sh 
#!/bin/bash
wget http://xrl.us/cpanm --no-check-certificate
mv cpanm /usr/bin
chmod 755 /usr/bin/cpanm
cat > /root/list << EOF
install DBD::mysql
EOF
for package in `cat /root/list`
do
   cpanm $package
done


tar all的安装方式：
tar -zxf mha4mysql-node-0.56.tar.gz
cd mha4mysql-node-0.56
perl Makefile.PL
make
make install


三. 安装 MHA Manager
# yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
# rpm -ivh mha4mysql-node-0.56-0.el5.noarch.rpm
# rpm -ivh mha4mysql-manager-0.56-0.el5.noarch.rpm


注：
上面有些包需要先安装附加软件包(EPEL)才能使用yum安装，



MHA Manager另一种安装方式：
MHA Manager 0.56 tar all源码安装
tar -zxf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56
perl Makefile.PL
make
make install






4、MHA Manager 端配置


各参数含义：https://code.google.com/p/mysql-master-ha/wiki/Parameters#no_master
MHA Manager端配置，建议使用root操作系统用户执行，因为涉及到vip 启停。


#mkdir -p /etc/masterha/app1
#vi /etc/masterha/app1/app1.cnf
[server default]
manager_workdir=/etc/masterha/app1
manager_log=/etc/masterha/app1/manager.log


user=root
password=root123


ssh_user=root


repl_user=slave001
repl_password=slave001


secondary_check_script=/usr/local/bin/masterha_secondary_check -s 10.100.251.222 -s 10.100.251.223 --user=root --master_host=mytest01 --master_ip=10.100.251.221 --master_port=3306
ping_interval=3
master_ip_failover_script=/etc/masterha/app1/master_ip_failover #master failover时执行,不配置vip时不用配
#shutdown_script=/etc/masterha/power_manager
report_script=/etc/masterha/app1/send_report #master failover时执行,可选
master_ip_online_change_script=/etc/masterha/app1/master_ip_online_change #masterswitchover时执行,不配置vip时不用配


[server1]
hostname=10.100.251.221
port=3306
master_binlog_dir=/usr/local/mysql/data/
candidate_master=1
check_repl_delay=0


[server2]
hostname=10.100.251.222
port=3306
master_binlog_dir=/usr/local/mysql/data/
candidate_master=1 #如果候选master有延迟的话，relay日志超过100m，failover切换不能成功，加上此参数后会忽略延迟日志大小。
check_repl_delay=0


[server3]
hostname=10.100.251.223
port=3306
master_binlog_dir=/usr/local/mysql/data/
ignore_fail=1 #如果这个节点挂了，mha将不可用，加上这个参数，slave挂了一样可以用
no_master=1 #从不将这台主机转换为master


二. 检查SSH配置
masterha_check_ssh --conf=/etc/masterha/app1/app1.cnf


#masterha_check_ssh --conf=/etc/masterha/app1/app1.cnf
Mon May 22 11:27:51 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon May 22 11:27:51 2017 - [info] Reading application default configuration from /etc/masterha/app1/app1.cnf..
Mon May 22 11:27:51 2017 - [info] Reading server configuration from /etc/masterha/app1/app1.cnf..
Mon May 22 11:27:51 2017 - [info] Starting SSH connection tests..
Mon May 22 11:27:52 2017 - [debug] 
Mon May 22 11:27:51 2017 - [debug]  Connecting via SSH from root@10.100.251.221(10.100.251.221:22) to root@10.100.251.222(10.100.251.222:22)..
Mon May 22 11:27:51 2017 - [debug]   ok.
Mon May 22 11:27:51 2017 - [debug]  Connecting via SSH from root@10.100.251.221(10.100.251.221:22) to root@10.100.251.223(10.100.251.223:22)..
Mon May 22 11:27:52 2017 - [debug]   ok.
Mon May 22 11:27:52 2017 - [debug] 
Mon May 22 11:27:51 2017 - [debug]  Connecting via SSH from root@10.100.251.222(10.100.251.222:22) to root@10.100.251.221(10.100.251.221:22)..
Mon May 22 11:27:51 2017 - [debug]   ok.
Mon May 22 11:27:51 2017 - [debug]  Connecting via SSH from root@10.100.251.222(10.100.251.222:22) to root@10.100.251.223(10.100.251.223:22)..
Mon May 22 11:27:52 2017 - [debug]   ok.
Mon May 22 11:27:53 2017 - [debug] 
Mon May 22 11:27:52 2017 - [debug]  Connecting via SSH from root@10.100.251.223(10.100.251.223:22) to root@10.100.251.221(10.100.251.221:22)..
Mon May 22 11:27:52 2017 - [debug]   ok.
Mon May 22 11:27:52 2017 - [debug]  Connecting via SSH from root@10.100.251.223(10.100.251.223:22) to root@10.100.251.222(10.100.251.222:22)..
Mon May 22 11:27:53 2017 - [debug]   ok.
Mon May 22 11:27:53 2017 - [info] All SSH connection tests passed successfully.
成功！


3. 检查MHA配置
masterha_check_repl --conf=/etc/masterha/app1/app1.cnf


[root@mytest01 data]# masterha_check_repl --conf=/etc/masterha/app1/app1.cnf 
Mon May 22 13:23:53 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon May 22 13:23:53 2017 - [info] Reading application default configuration from /etc/masterha/app1/app1.cnf..
Mon May 22 13:23:53 2017 - [info] Reading server configuration from /etc/masterha/app1/app1.cnf..
Mon May 22 13:23:53 2017 - [info] MHA::MasterMonitor version 0.56.
Mon May 22 13:23:54 2017 - [info] GTID failover mode = 0
Mon May 22 13:23:54 2017 - [info] Dead Servers:
Mon May 22 13:23:54 2017 - [info] Alive Servers:
Mon May 22 13:23:54 2017 - [info]   10.100.251.221(10.100.251.221:3306)
Mon May 22 13:23:54 2017 - [info]   10.100.251.222(10.100.251.222:3306)
Mon May 22 13:23:54 2017 - [info]   10.100.251.223(10.100.251.223:3306)
Mon May 22 13:23:54 2017 - [info] Alive Slaves:
Mon May 22 13:23:54 2017 - [info]   10.100.251.222(10.100.251.222:3306)  Version=5.6.28-log (oldest major version between slaves) log-bin:enabled
Mon May 22 13:23:54 2017 - [info]     Replicating from 10.100.251.221(10.100.251.221:3306)
Mon May 22 13:23:54 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon May 22 13:23:54 2017 - [info]   10.100.251.223(10.100.251.223:3306)  Version=5.6.28-log (oldest major version between slaves) log-bin:enabled
Mon May 22 13:23:54 2017 - [info]     Replicating from 10.100.251.221(10.100.251.221:3306)
Mon May 22 13:23:54 2017 - [info]     Not candidate for the new Master (no_master is set)
Mon May 22 13:23:54 2017 - [info] Current Alive Master: 10.100.251.221(10.100.251.221:3306)
Mon May 22 13:23:54 2017 - [info] Checking slave configurations..
Mon May 22 13:23:54 2017 - [info] Checking replication filtering settings..
Mon May 22 13:23:54 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon May 22 13:23:54 2017 - [info]  Replication filtering check ok.
Mon May 22 13:23:54 2017 - [info] GTID (with auto-pos) is not supported
Mon May 22 13:23:54 2017 - [info] Starting SSH connection tests..
Mon May 22 13:23:56 2017 - [info] All SSH connection tests passed successfully.
Mon May 22 13:23:56 2017 - [info] Checking MHA Node version..
Mon May 22 13:23:56 2017 - [info]  Version check ok.
Mon May 22 13:23:56 2017 - [info] Checking SSH publickey authentication settings on the current master..
Mon May 22 13:23:57 2017 - [info] HealthCheck: SSH to 10.100.251.221 is reachable.
Mon May 22 13:23:57 2017 - [info] Master MHA Node version is 0.56.
Mon May 22 13:23:57 2017 - [info] Checking recovery script configurations on 10.100.251.221(10.100.251.221:3306)..
Mon May 22 13:23:57 2017 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000009 
Mon May 22 13:23:57 2017 - [info]   Connecting to root@10.100.251.221(10.100.251.221:22).. 
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /usr/local/mysql/data, up to mysql-bin.000009
Mon May 22 13:23:57 2017 - [info] Binlog setting check done.
Mon May 22 13:23:57 2017 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon May 22 13:23:57 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=10.100.251.222 --slave_ip=10.100.251.222 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.28-log --manager_version=0.56 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
Mon May 22 13:23:57 2017 - [info]   Connecting to root@10.100.251.222(10.100.251.222:22).. 
  Checking slave recovery environment settings..
    Opening /usr/local/mysql/data/relay-log.info ... ok.
    Relay log found at /usr/local/mysql/data, up to mysql-relay-bin.000003
    Temporary relay log file is /usr/local/mysql/data/mysql-relay-bin.000003
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon May 22 13:23:58 2017 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=10.100.251.223 --slave_ip=10.100.251.223 --slave_port=3306 --workdir=/var/tmp --target_version=5.6.28-log --manager_version=0.56 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
Mon May 22 13:23:58 2017 - [info]   Connecting to root@10.100.251.223(10.100.251.223:22).. 
  Checking slave recovery environment settings..
    Opening /usr/local/mysql/data/relay-log.info ... ok.
    Relay log found at /usr/local/mysql/data, up to mysql-relay-bin.000007
    Temporary relay log file is /usr/local/mysql/data/mysql-relay-bin.000007
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Mon May 22 13:23:58 2017 - [info] Slaves settings check done.
Mon May 22 13:23:58 2017 - [info] 
10.100.251.221(10.100.251.221:3306) (current master)
 +--10.100.251.222(10.100.251.222:3306)
 +--10.100.251.223(10.100.251.223:3306)


Mon May 22 13:23:58 2017 - [info] Checking replication health on 10.100.251.222..
Mon May 22 13:23:58 2017 - [info]  ok.
Mon May 22 13:23:58 2017 - [info] Checking replication health on 10.100.251.223..
Mon May 22 13:23:58 2017 - [info]  ok.
Mon May 22 13:23:58 2017 - [warning] master_ip_failover_script is not defined.
Mon May 22 13:23:58 2017 - [warning] shutdown_script is not defined.
Mon May 22 13:23:58 2017 - [info] Got exit code 0 (Not master dead).


MySQL Replication Health is OK.






4. MHA Manager 端日常主要操作步骤
① 检查是否有下列文件，有则删除。
发生主从切换后，MHAmanager服务会自动停掉，且在manager_workdir目录下面生成文件app1.failover.complete，若要启动MHA，必须先确保无此文件
 # ll /etc/masterha/app1/app1.failover.complete
 # ll /etc/masterha/app1/app1.failover.error


② 检查MHA当前置：
 # masterha_check_repl --conf=/etc/masterha/app1/app1.cnf


③ 启动MHA：
 # nohup masterha_manager --conf=/etc/masterha/app1/app1.cnf > /etc/masterha/app1/mha_manager.log 2>&1 &


 当有slave 节点宕掉时，默认是启动不了的，加上 --ignore_fail_on_start 即使有节点宕掉也能启动MHA，如下：
 # nohup masterha_manager --conf=/etc/masterha/app1/app1.cnf --ignore_fail_on_start >/etc/masterha/app1/mha_manager.log 2>&1 &


④ 检查状态：
 # masterha_check_status --conf=/etc/masterha/app1/app1.cnf


⑤ 检查日志：
 #tail -f /etc/masterha/app1/manager.log


⑥ 主从切换后续工作
 主库切换后，把原主库修复成新从库，然后重新执行以上步骤。原主库数据文件完整的情况下，可通过以下方式找出最后执行的CHANGE MASTER命令：
 # grep "CHANGE MASTER TO MASTER" /etc/masterha/app1/manager.log | tail -1
 CHANGE MASTER TO MASTER_HOST='10.100.251.222',MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=120,MASTER_USER='slave001', MASTER_PASSWORD='xxx';
 --最后启动新从库
 # start slave;
 # show slave status\G
 
 
5、Failover应用场景测试


自动failover测试
应用场景1：
master dead后，MHA当时已经开启，候选Master库（Slave）会自动failover为Master.
--shutdown mysql master node
# service mysql stop
--check new master node
mysql> show master status\G;
*************************** 1.row ***************************
 File: master-bin.000001
 Position: 330
 
--check slave node


mysql>  show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.100.251.222
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000010
          Read_Master_Log_Pos: 360
               Relay_Log_File: mariadbtest03-relay-bin.000002
                Relay_Log_Pos: 534
        Relay_Master_Log_File: binlog.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


--check manager.log
[root@mariadbtest03 ~]# tail -100f /etc/masterha/app1/manager.log
----- Failover Report -----


app1: MySQL Master failover mariadbtest01(10.100.251.221:3306) to mariadbtest02(10.100.251.222:3306) succeeded


Master mariadbtest01(10.100.251.221:3306) is down!


Check MHA Manager logs at mariadbtest03 for details.


Started automated(non-interactive) failover.
Invalidated master IP address on mariadbtest01(10.100.251.221:3306)
The latest slave mariadbtest02(10.100.251.222:3306) has all relay logs for recovery.
Selected mariadbtest02(10.100.251.222:3306) as a new master.
mariadbtest02(10.100.251.222:3306): OK: Applying all logs succeeded.
mariadbtest02(10.100.251.222:3306): OK: Activated master IP address.
mariadbtest03(10.100.251.223:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
mariadbtest03(10.100.251.223:3306): OK: Applying all logs succeeded. Slave started, replicating from mariadbtest02(10.100.251.222:3306)
mariadbtest02(10.100.251.222:3306): Resetting slave info succeeded.
Master failover to mariadbtest02(10.100.251.222:3306) completed successfully.


--最后把原主库修复成一个新的slave
#grep "CHANGE MASTER TO MASTER" /etc/masterha/app1/manager.log | tail -1
CHANGE MASTER TO MASTER_HOST='10.100.251.222', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000010', MASTER_LOG_POS=360, MASTER_USER='slave001', MASTER_PASSWORD='xxx';
mysql>CHANGE MASTER TO MASTER_HOST='10.100.251.222', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000010', MASTER_LOG_POS=360, MASTER_USER='slave001', MASTER_PASSWORD='slave001';


Query OK, 0 rows affected, 2warnings (0.17 sec)


mysql> start slave;


mysql> show slave status\G;
*************************** 1.row ***************************
 Slave_IO_State: Waiting formaster to send event
Master_Host: 10.100.251.222
 Master_User: slave001


手动Failover
应用场景2：master dead，但是当时MHA没有开启，可以通过手工failover。
1．检查是否有下列文件，有则删除。
# ll /etc/masterha/app1/app1.failover.complete
 # ll /etc/masterha/app1/app1.failover.error
2. 如果MHA在运行，需先停止MHA：masterha_stop --conf=/etc/masterha/app1/app1.cnf
3. 检查MHA当前置：masterha_check_repl --conf=/etc/masterha/app1/app1.cnf
4. 手动切换：masterha_master_switch--conf=/etc/masterha/app1/app1.cnf --master_state=dead --dead_master_host=10.100.251.222 --dead_master_port=3306
# 接以上的
以下为切换时指定了new_master_host和new_master_port，如果不指定new_master_host，则根据配置文件app1.cnf选出new_master_host，但new_master_port默认是3306。
# masterha_master_switch --conf=/etc/masterha/app1/app1.cnf --master_state=dead --dead_master_host=10.100.251.222 --dead_master_port=3306 --new_master_host=10.100.251.221 --new_master_port=3306






应用场景3
Scheduled(Online) Master Switch(手动在线主库切换)
master和slave正常，MHA正常开启，维护操作时(例如更换新主机硬件、添加/删除列或主键)手动在线切换master到其他主机。
1. 如果MHA在运行，需先停止MHA
masterha_stop --conf=/etc/masterha/app1/app1.cnf
2. 检查MHA当前置
masterha_check_repl --conf=/etc/masterha/app1/app1.cnf
3. 手动切换
masterha_master_switch --master_state=alive --conf=/etc/masterha/app1/app1.cnf --orig_master_is_new_slave --running_updates_limit=3600 --interactive=0
注意：执行masterha_master_switch调用的不是master_ip_failover_script脚本，而是master_ip_online_change_script脚本，可把启动和停止VIP放到这个脚本中，如果没有配置VIP，则需要手动执行VIP切换，如下：
ssh root@$orig_master_ip /sbin/ifconfig eth0:1 down
ssh root@$new_master_ip /sbin/ifconfig eth0:1 10.100.251.228/24


以下为切换时指定了new_master_host和new_master_port，如果不指定new_master_host，则根据配置文件app1.cnf选出new_master_host，但new_master_port默认是3306。
masterha_master_switch --master_state=alive --conf=/etc/masterha/app1/app1.cnf --orig_master_is_new_slave --running_updates_limit=3600 --interactive=0 --new_master_host=10.100.251.222 --new_master_port=3306
参数 --running_updates_limit 如果现在的master执行写操作的执行时间大于这个参数，或者任何一台slave的Seconds_Behind_Master大于这个参数，那么master switch将自动放弃。默认参数为1s
参数 --interactive=0 非交互切换，建议加上，可大大加快切换速度，加上后库不忙时大概3秒内切换完成。




7、定期删除中继日志


由于在第一步中，每个slave上设置了参数relay_log_purge=0，所以slave节点需要定期删除中继日志，建议每个slave节点删除中继日志的时间错开。
corntab -e
0 5 * * *  /usr/bin/purge_relay_logs --user=root--password=123456 --port=3306 --disable_relay_log_purge >> /var/lib/mysql/purge_relay.log 2>&1




8、附录脚本：
#######master_ip_failover：


#!/usr/bin/env perl  
use strict;  
use warnings FATAL =>'all';  
  
use Getopt::Long;  
  
my (  
$command,          $ssh_user,        $orig_master_host, $orig_master_ip,  
$orig_master_port, $new_master_host, $new_master_ip,    $new_master_port  
);  
  
my $vip = '10.100.251.228/24';  # Virtual IP  
my $key = "1";  
my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";  
my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down";  
my $exit_code = 0;  
  
GetOptions(  
'command=s'          => \$command,  
'ssh_user=s'         => \$ssh_user,  
'orig_master_host=s' => \$orig_master_host,  
'orig_master_ip=s'   => \$orig_master_ip,  
'orig_master_port=i' => \$orig_master_port,  
'new_master_host=s'  => \$new_master_host,  
'new_master_ip=s'    => \$new_master_ip,  
'new_master_port=i'  => \$new_master_port,  
);  
  
exit &main();  
  
sub main {  
  
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";  
  
if ( $command eq "stop" || $command eq "stopssh" ) {  
  
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.  
        # If you manage master ip address at global catalog database,  
        # invalidate orig_master_ip here.  
        my $exit_code = 1;  
        eval {  
            print "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\n";  
&stop_vip();  
            $exit_code = 0;  
        };  
        if ($@) {  
            warn "Got Error: $@\n";  
            exit $exit_code;  
        }  
        exit $exit_code;  
}  
elsif ( $command eq "start" ) {  
  
        # all arguments are passed.  
        # If you manage master ip address at global catalog database,  
        # activate new_master_ip here.  
        # You can also grant write access (create user, set read_only=0, etc) here.  
my $exit_code = 10;  
        eval {  
            print "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\n";  
&start_vip();  
            $exit_code = 0;  
        };  
        if ($@) {  
            warn $@;  
            exit $exit_code;  
        }  
        exit $exit_code;  
}  
elsif ( $command eq "status" ) {  
        print "Checking the Status of the script.. OK \n";  
        `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;  
        exit 0;  
}  
else {  
&usage();  
        exit 1;  
}  
}  
  
# A simple system call that enable the VIP on the new master  
sub start_vip() {  
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;  
}  
# A simple system call that disable the VIP on the old_master  
sub stop_vip() {  
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;  
}  
  
sub usage {  
print  
"Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po  
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";  
}  






##########master_ip_online_change


#!/usr/bin/env perl  
use strict;  
use warnings FATAL =>'all';  
  
use Getopt::Long;  
  
my $vip = '10.100.251.228/24';  # Virtual IP  
my $key = "1";  
my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip";  
my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down";  
my $exit_code = 0;  
  
my (  
  $command,              $orig_master_is_new_slave, $orig_master_host,  
  $orig_master_ip,       $orig_master_port,         $orig_master_user,  
  $orig_master_password, $orig_master_ssh_user,     $new_master_host,  
  $new_master_ip,        $new_master_port,          $new_master_user,  
  $new_master_password,  $new_master_ssh_user,  
);  
GetOptions(  
  'command=s'                => \$command,  
  'orig_master_is_new_slave' => \$orig_master_is_new_slave,  
  'orig_master_host=s'       => \$orig_master_host,  
  'orig_master_ip=s'         => \$orig_master_ip,  
  'orig_master_port=i'       => \$orig_master_port,  
  'orig_master_user=s'       => \$orig_master_user,  
  'orig_master_password=s'   => \$orig_master_password,  
  'orig_master_ssh_user=s'   => \$orig_master_ssh_user,  
  'new_master_host=s'        => \$new_master_host,  
  'new_master_ip=s'          => \$new_master_ip,  
  'new_master_port=i'        => \$new_master_port,  
  'new_master_user=s'        => \$new_master_user,  
  'new_master_password=s'    => \$new_master_password,  
  'new_master_ssh_user=s'    => \$new_master_ssh_user,  
);  
  
  
exit &main();  
  
sub main {  
  
#print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";  
  
if ( $command eq "stop" || $command eq "stopssh" ) {  
  
        # $orig_master_host, $orig_master_ip, $orig_master_port are passed.  
        # If you manage master ip address at global catalog database,  
        # invalidate orig_master_ip here.  
        my $exit_code = 1;  
        eval {  
            print "\n\n\n***************************************************************\n";  
            print "Disabling the VIP - $vip on old master: $orig_master_host\n";  
            print "***************************************************************\n\n\n\n";  
&stop_vip();  
            $exit_code = 0;  
        };  
        if ($@) {  
            warn "Got Error: $@\n";  
            exit $exit_code;  
        }  
        exit $exit_code;  
}  
elsif ( $command eq "start" ) {  
  
        # all arguments are passed.  
        # If you manage master ip address at global catalog database,  
        # activate new_master_ip here.  
        # You can also grant write access (create user, set read_only=0, etc) here.  
my $exit_code = 10;  
        eval {  
            print "\n\n\n***************************************************************\n";  
            print "Enabling the VIP - $vip on new master: $new_master_host \n";  
            print "***************************************************************\n\n\n\n";  
&start_vip();  
            $exit_code = 0;  
        };  
        if ($@) {  
            warn $@;  
            exit $exit_code;  
        }  
        exit $exit_code;  
}  
elsif ( $command eq "status" ) {  
        print "Checking the Status of the script.. OK \n";  
        `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`;  
        exit 0;  
}  
else {  
&usage();  
        exit 1;  
}  
}  
  
# A simple system call that enable the VIP on the new master  
sub start_vip() {  
`ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;  
}  
# A simple system call that disable the VIP on the old_master  
sub stop_vip() {  
`ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;  
}  
  
sub usage {  
print  
"Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po  
rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";  
} 




###########send_report


#/bin/bash  
source /root/.bash_profile  
  
orig_master_host=`echo "$1" | awk -F = '{print $2}'`  
new_master_host=`echo "$2" | awk -F = '{print $2}'`  
new_slave_hosts=`echo "$3" | awk -F = '{print $2}'`  
subject=`echo "$4" | awk -F = '{print $2}'`  
body=`echo "$5" | awk -F = '{print $2}'`  
  
#判断日志结尾是否有successfully，有则表示切换成功，成功与否都发邮件。  
tac /etc/masterha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null  
if [ $? -eq 0 ]  
    then  
    echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts" | mutt  
 -s "MySQL实例宕掉,MHA $subject 切换成功" -- 1234@qq.com  
else  
    echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" | mutt -s "MySQL实例宕掉,MHA $subje  
ct 切换失败" -- 1234@qq.com  
fi