MySQL:MHA安装配置

MHA结构如下
IP MySQL MHA
192.168.100.42 主 node
192.168.100.241 从(备主) master

192.168.100.121 从 node

1：三台服务器配互信

三台服务器执行ssh-keygen -t rsa
把三台服务器的~/.ssh/id_rsa.pub全部追加进~/.ssh/authorized_keys然后分发
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

1：ssh-keygen -t rsa
生成密钥
2：ssh-copy-id -i ~/.ssh/id_rsa.pub jifeng@IP
把本机的公钥追到IP的 .ssh/authorized_keys 里  
3：如果ssh的端口不是22，可用下面命令
ssh-copy-id -i ~/.ssh/id_rsa.pub "-p 23 jifeng@jifeng03"

2：安装依赖

yum install perl-DBD-MySQL 
yum install perl-Config-Tiny 
yum install perl-Log-Dispatch 
yum install perl-Parallel-ForkManager

3：安装node及master

tar -zxf mha4mysql-node-X.Y.tar.gz
perl Makefile.PL
make
sudo make install
(需安装node在安装master)
tar -zxf mha4mysql-master-X.Y.tar.gz
perl Makefile.PL
make
sudo make install

4：修改配置文件

[root@dbo_100_241 mha]# cat app1.cnf 
[server default]
user=root
password=Qe091cs74
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1

manager_workdir=/etc/mha/data
manager_log=/etc/mha/data/app1.log
remote_workdir=/etc/mha/data

master_ip_failover_script=/etc/mha/scripts/master_ip_failover
master_ip_online_change_script=/etc/mha/scripts/master_ip_online_change
#shutdown_script=/usr/bin/power_manager
report_script =/etc/mha/scripts/send_report
secondary_check_script= /usr/local/bin/masterha_secondary_check -s dbo_100_241 -s db121 -s db42 --user=root --master_host=db42 --master_ip=192.168.100.42 --master_port=3307   

[server1]
hostname=db42
master_binlog_dir=/oracle/mysqllog/mysql3307
candidate_master=1
check_repl_delay=0
port=3307

[server2]
hostname=dbo_100_241 
master_binlog_dir=/oracle/mysqllog/mysql3307
candidate_master=1
check_repl_delay=0
port=3307

[server3]
hostname=db121
master_binlog_dir=/oracle/mysqllog/mysql3307
#candidate_master=1
port=3307
ignore_fail=1
no_master=1

5：开启前准备测试

[root@dbo_100_241 mha]# masterha_check_ssh -conf=./app1.cnf
[root@dbo_100_241 mha]# masterha_check_repl -conf=./app1.cnf

6：启动MHA

第5步检查都OK后，开始MHA
masterha_manager -conf=/etc/mha/app1.cnf --ignore_last_failover > /tmp/app.log 2>&1 &
MHA切换命令
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=db42 --new_master_port=3307 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0

相关：
masterha_check_ssh              检查MHA的SSH配置状况
masterha_check_repl             检查MySQL复制状况
masterha_manger                 启动MHA
masterha_check_status           检测当前MHA运行状态
masterha_master_monitor         检测master是否宕机
masterha_master_switch          控制故障转移（自动或者手动）
masterha_conf_host              添加或删除配置的server信息

7：删除relay_log脚本

定时删除relay_log脚本，可放在crontab定时执行
[root@dbo_100_241 scripts]# cat purge_relay_log.sh 
#!/bin/bash
user=root
passwd=Qe091cs74
port=3307
log_dir='/etc/mha/data'
work_dir='/etc/mha/data'
purge='/usr/local/bin/purge_relay_logs'

if [ ! -d $log_dir ]
then
   mkdir $log_dir -p
fi

$purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

问题1：

[root@dbo_100_241 mha]# masterha_check_repl --conf=./app1.cnf          
Tue Feb 23 11:55:55 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Feb 23 11:55:55 2016 - [info] Reading application default configuration from ./app1.cnf..
Tue Feb 23 11:55:55 2016 - [info] Reading server configuration from ./app1.cnf..
Tue Feb 23 11:55:55 2016 - [info] MHA::MasterMonitor version 0.57.
Tue Feb 23 11:55:55 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln671] Master 172.16.10.45:3306 from which slave db42(192.168.100.42:3307) replicates is not defined in the configuration file!
Tue Feb 23 11:55:55 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329
Tue Feb 23 11:55:55 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Feb 23 11:55:55 2016 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

解决办法：
确保主库没有slave信息，即
(root@127.0.0.1) [(none)]> show slave status\G
Empty set (0.00 sec)

问题2：

Tue Feb 23 13:47:18 2016 - [info]  read_only=1 is not set on slave dbo_100_241(192.168.100.241:3307).
Tue Feb 23 13:47:18 2016 - [warning]  relay_log_purge=0 is not set on slave dbo_100_241(192.168.100.241:3307).
Tue Feb 23 13:47:18 2016 - [info]  read_only=1 is not set on slave db121(192.168.100.121:3307).
Tue Feb 23 13:47:18 2016 - [warning]  relay_log_purge=0 is not set on slave db121(192.168.100.121:3307).

解决办法：
备库执行
set global read_only=1;
set global relay_log_purge=0;

问题3：

[root@dbo_100_241 mha]# masterha_check_repl -conf=./app1.cnf
Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/bin/masterha_check_repl line 48
Tue Feb 23 13:43:35 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

解决办法：
ln -s /usr/local/mysql55/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql55/bin/mysql /usr/bin/mysql 

参考：

MHA下载
https://code.google.com/p/mysql-master-ha/
epel下载
https://fedoraproject.org/wiki/EPEL
MHA参考资料
http://www.tuicool.com/articles/EBjmy2
http://www.cnblogs.com/gomysql/p/3675429.html
http://www.cnblogs.com/kissdb/p/4009620.html

mha切换资料
http://www.slideshare.net/matsunobu/automated-master-failover/17

参数说明参考：
http://wubx.net/mha-parameters/
http://isadba.com/upload/mha_Parameters.htm