MHA配置_Mysql5.5+centos6.5_完整版

文中相关RPM包已经上传http://pan.baidu.com/s/1kTy20gZ

准备条件：

主：168.5.130.115

从：168.5.130.107

从：168.5.130.99（PS：Manager节点）

（主从配置需保持一致）

注意事项：

1.如有空用户：删除掉select * from mysql.user where user=''; flush privileges;

2.每个节点都做好mysql命令的软链  
ln -s /usr/local/mysql/bin/* /usr/local/bin/

配置：

1 配置好hosts、ssh相互关系 、主从关系（至少3台）

2 创建用户mha管理的账号，在所有mysql服务器上都需要执行：

 GRANT SUPER,RELOAD,REPLICATION CLIENT,SELECT,CREATE,INSERT,UPDATE,DELETE,DROP ON *.* TO 'manager'@'168.5.130.%' IDENTIFIED BY '123456';

Node节点安装（所有服务器）：

加载centos镜像到目录： 

mount -t iso9660 /dev/cdrom /mnt/cdrom

安装依赖rpm：

cd /mnt/cdrom/Packages

rpm -ivh perl-DBI-1.609-4.el6.i686.rpm

rpm -ivh perl-DBD-MySQL-4.013-3.el6.i686.rpm

rpm -ivh  perl-Digest-SHA-5.47-136.el6.i686.rpm

rpm -ivh perl-CPAN-1.9402-136.el6.i686.rpm

下载mha4mysql-node-0.56.tar.gz

tar -zxvf mha4mysql-node-0.56.tar.gz

perl Makefile.PL

make && make install

Manager节点安装（168.5.130.99）：

cd /mnt/cdrom/Packages

rpm -ivh compat-db43-4.3.29-15.el6.i686.rpm

rpm -ivh perl-TimeDate-1.16-11.1.el6.noarch.rpm

rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm

rpm -ivh perl-Config-General-2.52-1.el6.noarch.rpm

rpm -ivh perl-Time-HiRes-1.9721-136.el6.i686.rpm

cd /soft #需要将以下RPM包下载（centos中镜像没有）

rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm

rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm

 rpm -ivh perl-Email-Date-Format-1.002-5.el6.noarch.rpm

rpm -ivh perl-MIME-Types-1.28-2.el6.noarch.rpm

rpm -ivh perl-MIME-Lite-3.027-2.el6.noarch.rpm

rpm -ivh perl-Params-Validate-0.92-3.el6.i686.rpm

rpm -ivh perl-Log-Dispatch-2.27-1.el6.noarch.rpm

rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

rpm -ivh perl-Config-Tiny-2.12-7.1.el6.noarch.rpm

解压缩安装：

tar -xvf mha4mysql-manager-0.56.tar.gz

cd mha4mysql-manager-0.56

perl Makefile.PL

make && make install

配置Manager参数：

# vi/etc/masterha/app1.cnf

[server default]
user=manager
password=123456
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
## scripts to support MHA functions
## secondary_check_script= /usr/bin/masterha_secondary_check -s remote_host1 -s remote_host2
## master_ip_failover_script= /usr/local/masterha/scripts/master_ip_failover
## shutdown_script= /usr/local/masterha/scripts/power_manager
## report_script= /usr/local/masterha/scripts/send_report
## master_ip_online_change_script= /usr/local/masterha/scripts/master_ip_online_change


ssh_user=root
repl_user=rel
repl_password=123456
ping_interval=1
 
[server1]
hostname=168.5.130.115
#candidate_master=1
master_binlog_dir=/data/mysql/binlog/


[server2]
hostname=168.5.130.107
candidate_master=1
master_binlog_dir=/data/mysql/binlog/
 
[server3]
hostname=168.5.130.99
#candidate_master=1
master_binlog_dir=/data/mysql/binlog/

检测SSH：masterha_check_ssh

# masterha_check_ssh --conf=/etc/masterha/app1.cnf

[root@centos3 soft]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Thu May 28 10:21:36 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 28 10:21:36 2015 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu May 28 10:21:36 2015 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu May 28 10:21:36 2015 - [info] Starting SSH connection tests..
Thu May 28 10:21:38 2015 - [debug]
Thu May 28 10:21:36 2015 - [debug]  Connecting via SSH from root@168.5.130.115(168.5.130.115:22) to root@168.5.130.107(168.5.130.107:22)..
Thu May 28 10:21:37 2015 - [debug]   ok.
Thu May 28 10:21:37 2015 - [debug]  Connecting via SSH from root@168.5.130.115(168.5.130.115:22) to root@168.5.130.99(168.5.130.99:22)..
Thu May 28 10:21:38 2015 - [debug]   ok.
Thu May 28 10:21:39 2015 - [debug]
Thu May 28 10:21:36 2015 - [debug]  Connecting via SSH from root@168.5.130.107(168.5.130.107:22) to root@168.5.130.115(168.5.130.115:22)..
Thu May 28 10:21:37 2015 - [debug]   ok.
Thu May 28 10:21:37 2015 - [debug]  Connecting via SSH from root@168.5.130.107(168.5.130.107:22) to root@168.5.130.99(168.5.130.99:22)..
Thu May 28 10:21:39 2015 - [debug]   ok.
Thu May 28 10:21:39 2015 - [debug]
Thu May 28 10:21:37 2015 - [debug]  Connecting via SSH from root@168.5.130.99(168.5.130.99:22) to root@168.5.130.115(168.5.130.115:22)..
Thu May 28 10:21:38 2015 - [debug]   ok.
Thu May 28 10:21:38 2015 - [debug]  Connecting via SSH from root@168.5.130.99(168.5.130.99:22) to root@168.5.130.107(168.5.130.107:22)..
Thu May 28 10:21:39 2015 - [debug]   ok.
Thu May 28 10:21:39 2015 - [info] All SSH connection tests passed successfully.<strong>
</strong>

主从检查：masterha_check_repl

#  masterha_check_repl --conf=/etc/masterha/app1.cnf

[root@centos3 soft]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Thu May 28 10:25:45 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 28 10:25:45 2015 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu May 28 10:25:45 2015 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu May 28 10:25:45 2015 - [info] MHA::MasterMonitor version 0.56.
Thu May 28 10:25:45 2015 - [info] GTID failover mode = 0
Thu May 28 10:25:45 2015 - [info] Dead Servers:
Thu May 28 10:25:45 2015 - [info] Alive Servers:
Thu May 28 10:25:45 2015 - [info]   168.5.130.115(168.5.130.115:3306)
Thu May 28 10:25:45 2015 - [info]   168.5.130.107(168.5.130.107:3306)
Thu May 28 10:25:45 2015 - [info]   168.5.130.99(168.5.130.99:3306)
Thu May 28 10:25:45 2015 - [info] Alive Slaves:
Thu May 28 10:25:45 2015 - [info]   168.5.130.107(168.5.130.107:3306)  Version=5.5.11-log (oldest major version between slaves) log-bin:enabled
Thu May 28 10:25:45 2015 - [info]     Replicating from 168.5.130.115(168.5.130.115:3306)
Thu May 28 10:25:45 2015 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu May 28 10:25:45 2015 - [info]   168.5.130.99(168.5.130.99:3306)  Version=5.5.11-log (oldest major version between slaves) log-bin:enabled
Thu May 28 10:25:45 2015 - [info]     Replicating from 168.5.130.115(168.5.130.115:3306)
Thu May 28 10:25:45 2015 - [info] Current Alive Master: 168.5.130.115(168.5.130.115:3306)
Thu May 28 10:25:45 2015 - [info] Checking slave configurations..
Thu May 28 10:25:45 2015 - [info]  read_only=1 is not set on slave 168.5.130.107(168.5.130.107:3306).
Thu May 28 10:25:45 2015 - [warning]  relay_log_purge=0 is not set on slave 168.5.130.107(168.5.130.107:3306).
Thu May 28 10:25:45 2015 - [info]  read_only=1 is not set on slave 168.5.130.99(168.5.130.99:3306).
Thu May 28 10:25:45 2015 - [warning]  relay_log_purge=0 is not set on slave 168.5.130.99(168.5.130.99:3306).
Thu May 28 10:25:45 2015 - [info] Checking replication filtering settings..
Thu May 28 10:25:45 2015 - [info]  binlog_do_db= , binlog_ignore_db= information_schema,mysql
Thu May 28 10:25:45 2015 - [info]  Replication filtering check ok.
Thu May 28 10:25:45 2015 - [info] GTID (with auto-pos) is not supported
Thu May 28 10:25:45 2015 - [info] Starting SSH connection tests..
Thu May 28 10:25:49 2015 - [info] All SSH connection tests passed successfully.
Thu May 28 10:25:49 2015 - [info] Checking MHA Node version..
Thu May 28 10:25:50 2015 - [info]  Version check ok.
Thu May 28 10:25:50 2015 - [info] Checking SSH publickey authentication settings on the current master..
Thu May 28 10:25:50 2015 - [info] HealthCheck: SSH to 168.5.130.115 is reachable.
Thu May 28 10:25:50 2015 - [info] Master MHA Node version is 0.56.
Thu May 28 10:25:50 2015 - [info] Checking recovery script configurations on 168.5.130.115(168.5.130.115:3306)..
Thu May 28 10:25:50 2015 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/ --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000014
Thu May 28 10:25:50 2015 - [info]   Connecting to root@168.5.130.115(168.5.130.115:22)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mysql/, up to mysql-bin.000014
Thu May 28 10:25:51 2015 - [info] Binlog setting check done.
Thu May 28 10:25:51 2015 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu May 28 10:25:51 2015 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='manager' --slave_host=168.5.130.107 --slave_ip=168.5.130.107 --slave_port=3306 --workdir=/var/tmp --target_version=5.5.11-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Thu May 28 10:25:51 2015 - [info]   Connecting to root@168.5.130.107(168.5.130.107:22)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to centos1-relay-bin.000002
    Temporary relay log file is /data/mysql/centos1-relay-bin.000002
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu May 28 10:25:51 2015 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='manager' --slave_host=168.5.130.99 --slave_ip=168.5.130.99 --slave_port=3306 --workdir=/var/tmp --target_version=5.5.11-log --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info  --relay_dir=/data/mysql/  --slave_pass=xxx
Thu May 28 10:25:51 2015 - [info]   Connecting to root@168.5.130.99(168.5.130.99:22)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to centos3-relay-bin.000002
    Temporary relay log file is /data/mysql/centos3-relay-bin.000002
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu May 28 10:25:52 2015 - [info] Slaves settings check done.
Thu May 28 10:25:52 2015 - [info]
168.5.130.115(168.5.130.115:3306) (current master)
 +--168.5.130.107(168.5.130.107:3306)
 +--168.5.130.99(168.5.130.99:3306)

Thu May 28 10:25:52 2015 - [info] Checking replication health on 168.5.130.107..
Thu May 28 10:25:52 2015 - [info]  ok.
Thu May 28 10:25:52 2015 - [info] Checking replication health on 168.5.130.99..
Thu May 28 10:25:52 2015 - [info]  ok.
Thu May 28 10:25:52 2015 - [warning] master_ip_failover_script is not defined.
Thu May 28 10:25:52 2015 - [warning] shutdown_script is not defined.
Thu May 28 10:25:52 2015 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

启动manager

#nohup masterha_manager --conf=/etc/masterha/app1.cnf < /dev/null >/data/mha/app1/manager.log 2>&1 &

#tail -f /data/mha/app1/manager.log

Mon May 25 17:36:09 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon May 25 17:36:09 2015 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Mon May 25 17:36:09 2015 - [info] Reading server configuration from /etc/masterha/app1.cnf..

检查MHA状态masterha_check_status：

# masterha_check_status --conf=/etc/masterha/app1.cnf

app1 (pid:29845) is running(0:PING_OK), master:168.5.130.115

搭建完毕!!

MHA自动切换：

当主库停止或者异常时，MHA自动进行切换。不做演示。

MHA手动切换：

停止Manager服务：

#masterha_stop --conf=/etc/masterha/app1.cnf

先Dead掉主机：

（由于主机实际正常，故此命令跳过：

# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=dead --dead_master_host=168.5.130.115）

将168.5.130.107 设置为主：

# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=168.5.130.107 --new_master_port=3306

根据提示输入YES即可。

切换完成后注意将app1.cnf主备顺序内容修改。