搭建Mysql MHA高可用集群+各种报错解决

 

mha下载地址，需要翻墙

https://code.google.com/p/mysql-master-ha/
管理软件
mha4mysql-manager-0.52-0.noarch.rpm
节点软件
mha4mysql-node-0.52-0.noarch.rpm

环境介绍 Centos6.7 X64
192.168.30.210 monitor
192.168.30.211 db1 (master)
192.168.30.212 db2  (备master)
192.168.30.213 db3
192.168.30.214  db4
版本Mysql5.5.45
一、准备工作
db1-3需要先安装好Mysql，不会装的不用看下去了
 
三台机器都添加hosts表

192.168.30.211 db1
192.168.30.212 db2
192.168.30.213 db3
192.168.30.214  db4
 
实现4台机器间免密码登陆
在db1上执行shell

ssh-keygen -t rsa
 
ssh-copy-id 192.168.30.210
ssh-copy-id 192.168.30.212
ssh-copy-id 192.168.30.213
ssh-copy-id 192.168.30.214
 
在db2上执行shell

ssh-keygen -t rsa
ssh-copy-id 192.168.30.211
ssh-copy-id 192.168.30.210
ssh-copy-id 192.168.30.213
ssh-copy-id 192.168.30.214
在db3上执行shell

ssh-keygen -t rsa
ssh-copy-id 192.168.30.211
ssh-copy-id 192.168.30.212
ssh-copy-id 192.168.30.210
ssh-copy-id 192.168.30.214
在db4上执行shell

ssh-keygen -t rsa
ssh-copy-id 192.168.30.211
ssh-copy-id 192.168.30.212
ssh-copy-id 192.168.30.210
ssh-copy-id 192.168.30.213
在monitor上执行shell

ssh-keygen -t rsa
ssh-copy-id 192.168.30.211
ssh-copy-id 192.168.30.212
ssh-copy-id 192.168.30.213
ssh-copy-id 192.168.30.214
建立Mysql直接主从同步
特别注意：每台DB的server id必须唯一
在DB1 上面建立同步账户

mysql> grant replication slave on *.* to slave@'192.168.30.%' identified by "123";

mysql> show master status;
+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     5001 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在DB2 上面建立同步账户,因为是备用master

mysql> grant replication slave on *.* to slave@'192.168.30.%' identified by "123";
打开防火墙

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT  && service iptables save
在db2上做主从，开防火墙

mysql> CHANGE MASTER TO MASTER_HOST='192.168.30.211', MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=5001, MASTER_USER='slave',
 MASTER_PASSWORD='123';
 
 mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
 
查看同步状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.212
                  Master_User: slave
                  Master_Port: 3306
                    Connect_Retry: 60
                      Master_Log_File: mysql-bin.000003
                          Read_Master_Log_Pos: 107
                     Relay_Log_File: mysql-relay-bin.000005
                    Relay_Log_Pos: 253
                            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: 107
              Relay_Log_Space: 555
              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:

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
1 row in set (0.00 sec)

 

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT && service iptables save
在db3上做主从，开防火墙

CHANGE MASTER TO MASTER_HOST='192.168.30.211', MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=5001, MASTER_USER='slave',
 MASTER_PASSWORD='123';

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT && service iptables save
基础环境搭建好了
二、安装配置MHA
在monitir上安装

yum localinstall -y mha4mysql-node-0.52-0.noarch
yum localinstall -y mha4mysql-manager-0.52-0.noarch.rpm
在db1-4上安装

yum localinstall -y mha4mysql-node-0.52-0.noarch
在所有DB上面授权MHA管理账号

mysql> grant all on *.* to mha@'192.168.30.%' identified by '123456';
在monitor上面
先新建一个工作目录

mkdir /mha
编辑配置文件

vim /etc/masterha_default.cnf

[server default]
#刚才授权的mysql管理用?裘?br />
user=mha
password=123456
manager_workdir=/mha
manager_log=/mha/manager.log
remote_workdir=/mha
#ssh免密钥登录的帐号名
ssh_user=root
#mysql复制帐号，用来在主从机之间同步二进制日志等
repl_user=slave
repl_password=123
#ping间隔，用来检测master是否正常
ping_interval= 1

 
[server1]

hostname=db1
master_binlog_dir=/data/mysql
#候选人，master挂掉时候优先让它顶
candidate_master=1   
 
[server2]
hostname=db2
master_binlog_dir=/data/mysql
candidate_master=1
 
[server3]
hostname=db3
master_binlog_dir=/data/mysql
#不能成为master
no_master=1
 
[server4]
hostname=db4
master_binlog_dir=/data/mysql
#不能成为master
no_master=1
验证SSH互认是否成功
[root@monitor ~]# masterha_check_ssh --conf=/etc/masterha_default.cnf

[root@monitor ~]# masterha_check_ssh --conf=/etc/masterha_default.cnf
Fri Aug 26 17:59:44 2016 - [info] Reading default configuratoins from /etc/masterha_default.cnf..
Fri Aug 26 17:59:44 2016 - [info] Reading application default configurations from /etc/masterha_default.cnf..
Fri Aug 26 17:59:44 2016 - [info] Reading server configurations from /etc/masterha_default.cnf..
Fri Aug 26 17:59:44 2016 - [info] Starting SSH connection tests..
Fri Aug 26 17:59:45 2016 - [error][/usr/lib64/<a href="http://www.ttlsa.com/perl/" title="perl"target="_blank">perl</a>5/vendor_perl/MHA/SSHCheck.pm, ln63]
Fri Aug 26 17:59:44 2016 - [debug]  Connecting via SSH from root@db2(192.168.30.212) to root@db1(192.168.30.211)..
Fri Aug 26 17:59:44 2016 - [debug]   ok.
Fri Aug 26 17:59:44 2016 - [debug]  Connecting via SSH from root@db2(192.168.30.212) to root@db3(192.168.30.213)..
Fri Aug 26 17:59:45 2016 - [debug]   ok.
Fri Aug 26 17:59:45 2016 - [debug]  Connecting via SSH from root@db2(192.168.30.212) to root@db4(192.168.30.214)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Fri Aug 26 17:59:45 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln106] SSH connection from root@db2(192.168.30.212) to root@db4(192.168.30.214) failed!
Fri Aug 26 17:59:46 2016 - [debug]
Fri Aug 26 17:59:44 2016 - [debug]  Connecting via SSH from root@db1(192.168.30.211) to root@db2(192.168.30.212)..
Fri Aug 26 17:59:45 2016 - [debug]   ok.
Fri Aug 26 17:59:45 2016 - [debug]  Connecting via SSH from root@db1(192.168.30.211) to root@db3(192.168.30.213)..
Fri Aug 26 17:59:45 2016 - [debug]   ok.
Fri Aug 26 17:59:45 2016 - [debug]  Connecting via SSH from root@db1(192.168.30.211) to root@db4(192.168.30.214)..
Fri Aug 26 17:59:45 2016 - [debug]   ok.
Fri Aug 26 17:59:46 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Fri Aug 26 17:59:45 2016 - [debug]  Connecting via SSH from root@db4(192.168.30.214) to root@db2(192.168.30.212)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Fri Aug 26 17:59:45 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln106] SSH connection from root@db4(192.168.30.214) to root@db2(192.168.30.212) failed!
Fri Aug 26 17:59:46 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln63]
Fri Aug 26 17:59:45 2016 - [debug]  Connecting via SSH from root@db3(192.168.30.213) to root@db2(192.168.30.212)..
Fri Aug 26 17:59:45 2016 - [debug]   ok.
Fri Aug 26 17:59:45 2016 - [debug]  Connecting via SSH from root@db3(192.168.30.213) to root@db1(192.168.30.211)..
Fri Aug 26 17:59:46 2016 - [debug]   ok.
Fri Aug 26 17:59:46 2016 - [debug]  Connecting via SSH from root@db3(192.168.30.213) to root@db4(192.168.30.214)..
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
Fri Aug 26 17:59:46 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/SSHCheck.pm, ln106] SSH connection from root@db3(192.168.30.213) to root@db4(192.168.30.214) failed!
SSH Configuration Check Failed!
 at /usr/bin/masterha_check_ssh line 44

报错：这个错就是root@db2(192.168.30.212) to root@db4(192.168.30.214)之间互认还没完成，添加ssh认证即可
再来

[root@monitor ~]# masterha_check_ssh --conf=/etc/masterha_default.cnf
Fri Aug 26 18:03:00 2016 - [info] Reading default configuratoins from /etc/masterha_default.cnf..
Fri Aug 26 18:03:00 2016 - [info] Reading application default configurations from /etc/masterha_default.cnf..
Fri Aug 26 18:03:00 2016 - [info] Reading server configurations from /etc/masterha_default.cnf..
Fri Aug 26 18:03:00 2016 - [info] Starting SSH connection tests..
Fri Aug 26 18:03:02 2016 - [debug]
Fri Aug 26 18:03:00 2016 - [debug]  Connecting via SSH from root@db2(192.168.30.212) to root@db1(192.168.30.211)..
Fri Aug 26 18:03:01 2016 - [debug]   ok.
Fri Aug 26 18:03:01 2016 - [debug]  Connecting via SSH from root@db2(192.168.30.212) to root@db3(192.168.30.213)..
Fri Aug 26 18:03:01 2016 - [debug]   ok.
Fri Aug 26 18:03:01 2016 - [debug]  Connecting via SSH from root@db2(192.168.30.212) to root@db4(192.168.30.214)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:02 2016 - [debug]
Fri Aug 26 18:03:01 2016 - [debug]  Connecting via SSH from root@db1(192.168.30.211) to root@db2(192.168.30.212)..
Fri Aug 26 18:03:01 2016 - [debug]   ok.
Fri Aug 26 18:03:01 2016 - [debug]  Connecting via SSH from root@db1(192.168.30.211) to root@db3(192.168.30.213)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:02 2016 - [debug]  Connecting via SSH from root@db1(192.168.30.211) to root@db4(192.168.30.214)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:03 2016 - [debug]
Fri Aug 26 18:03:02 2016 - [debug]  Connecting via SSH from root@db4(192.168.30.214) to root@db2(192.168.30.212)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:02 2016 - [debug]  Connecting via SSH from root@db4(192.168.30.214) to root@db1(192.168.30.211)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:02 2016 - [debug]  Connecting via SSH from root@db4(192.168.30.214) to root@db3(192.168.30.213)..
Fri Aug 26 18:03:03 2016 - [debug]   ok.
Fri Aug 26 18:03:03 2016 - [debug]
Fri Aug 26 18:03:01 2016 - [debug]  Connecting via SSH from root@db3(192.168.30.213) to root@db2(192.168.30.212)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:02 2016 - [debug]  Connecting via SSH from root@db3(192.168.30.213) to root@db1(192.168.30.211)..
Fri Aug 26 18:03:02 2016 - [debug]   ok.
Fri Aug 26 18:03:02 2016 - [debug]  Connecting via SSH from root@db3(192.168.30.213) to root@db4(192.168.30.214)..
Fri Aug 26 18:03:03 2016 - [debug]   ok.
Fri Aug 26 18:03:03 2016 - [info] All SSH connection tests passed successfully.
通过检查
下一步
检查mysql主从复制

[root@monitor ~]# masterha_check_repl --conf=/etc/masterha_default.cnf
------------------------------省略号------------------------------------------------------
Can't locate MHA/BinlogManager.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/apply_diff_relay_logs line 24.
BEGIN failed--compilation aborted at /usr/bin/apply_diff_relay_logs line 24.
Fri Aug 26 18:11:55 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/ManagerUtil.pm, ln132] node version on db4 not found! Maybe MHA Node package is not installed?
 at /usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm line 278
Fri Aug 26 18:11:55 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln315] Error happend on checking configurations. Died at /usr/lib64/perl5/vendor_perl/MHA/ManagerUtil.pm line 133.
Fri Aug 26 18:11:55 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln396] Error happened on monitoring servers.
Fri Aug 26 18:11:55 2016 - [info] Got exit code 1 (Not master dead).
 
MySQL Replication Health is NOT OK!
报错：
那是不是主从检查没通过呢，其实不是得，这是个坑，关键报错在这句

Can't locate MHA/BinlogManager.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/bin/apply_diff_relay_logs line 24
百度一下结果是这样的
http://ronaldbradford.com/blog/mysql-mha-and-perl-pathing-2013-08-26/
解决办法是在5台机器上面做软连接，把这个32位的依赖链接到64位的支持库里面去

ln -s /usr/lib/perl5/vendor_perl/MHA /usr/lib64/perl5/vendor_perl/
解决完之后，再执行检查，又报错

[root@monitor ~]# masterha_check_repl --conf=/etc/masterha_default.cnf
------------------------------省略号------------------------------------------------------
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mysql, up to mysql-bin.000003
Fri Aug 26 18:21:23 2016 - [info] Master setting check done.
Fri Aug 26 18:21:23 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Aug 26 18:21:23 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db1 --slave_ip=192.168.30.211 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Fri Aug 26 18:21:23 2016 - [info]   Connecting to root@192.168.30.211(db1)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.000048
    Temporary relay log file is /data/mysql/mysql-relay-bin.000048
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.

Fri Aug 26 18:21:25 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db3 --slave_ip=192.168.30.213 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Fri Aug 26 18:21:25 2016 - [info]   Connecting to root@192.168.30.213(db3)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.000051
    Temporary relay log file is /data/mysql/mysql-relay-bin.000051
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Aug 26 18:21:27 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db4 --slave_ip=192.168.30.214 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Fri Aug 26 18:21:27 2016 - [info]   Connecting to root@192.168.30.214(db4)..
Can't exec "mysqlbinlog": No such file or directory at /usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pm line 99.
mysqlbinlog version not found!
 at /usr/bin/apply_diff_relay_logs line 425
Fri Aug 26 18:21:27 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln129] Slaves settings check failed!
Fri Aug 26 18:21:27 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln304] Slave configuration failed.
Fri Aug 26 18:21:27 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln315] Error happend on checking configurations.  at /usr/bin/masterha_check_repl line 48
Fri Aug 26 18:21:27 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln396] Error happened on monitoring servers.
Fri Aug 26 18:21:27 2016 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

这次报错提示找不到mysqlbinlog命令

Can't exec "mysqlbinlog": No such file or directory at /usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pm line 99.
1
Can't exec "mysqlbinlog": No such file or directory at /usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pm line 99.
我的mysql是编译安装的，添加了mysql bin目录的环境变量的，但是它竟然提示找不到这个命令，可能是没有读取/etc/profile文件吧，那我们就再做软连接到系统目录好了
解决：在所有db执行

[root@db4 ~]#ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
再来检查，又错，再看

[root@monitor ~]# masterha_check_repl --conf=/etc/masterha_default.cnf
 
------------------------------省略号------------------------------------------------------
  Checking output directory is accessible or not..
   ok.
  Binlog found at /data/mysql, up to mysql-bin.000003
Fri Aug 26 18:28:12 2016 - [info] Master setting check done.
Fri Aug 26 18:28:12 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Aug 26 18:28:12 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db1 --slave_ip=192.168.30.211 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Fri Aug 26 18:28:12 2016 - [info]   Connecting to root@192.168.30.211(db1)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.000464
    Temporary relay log file is /data/mysql/mysql-relay-bin.000464
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Aug 26 18:28:13 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db3 --slave_ip=192.168.30.213 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Fri Aug 26 18:28:13 2016 - [info]   Connecting to root@192.168.30.213(db3)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.000469
    Temporary relay log file is /data/mysql/mysql-relay-bin.000469
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Fri Aug 26 18:28:16 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db4 --slave_ip=192.168.30.214 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Fri Aug 26 18:28:16 2016 - [info]   Connecting to root@192.168.30.214(db4)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to db4-relay-bin.000002
    Temporary relay log file is /data/mysql/db4-relay-bin.000002
    Testing mysql connection and privileges..sh: mysql: command not found
mysql command failed with rc 127:0!
 at /usr/bin/apply_diff_relay_logs line 315

main::check() called at /usr/bin/apply_diff_relay_logs line 429
 eval {...} called at /usr/bin/apply_diff_relay_logs line 409
 main::main() called at /usr/bin/apply_diff_relay_logs line 97
Fri Aug 26 18:28:16 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln129] Slaves settings check failed!
Fri Aug 26 18:28:16 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln304] Slave configuration failed.
Fri Aug 26 18:28:16 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln315] Error happend on checking configurations.  at /usr/bin/masterha_check_repl line 48
Fri Aug 26 18:28:16 2016 - [error][/usr/lib64/perl5/vendor_perl/MHA/MasterMonitor.pm, ln396] Error happened on monitoring servers.
Fri Aug 26 18:28:16 2016 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

 

这次报错提示

 Testing mysql connection and privileges..sh: mysql: command not found

那好吧，跟上面一样，软连接

[root@db4 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

问题解决
再来检查

[root@monitor ~]# masterha_check_repl --conf=/etc/masterha_default.cnf
------------------------------省略号------------------------------------------------------
Sat Aug 27 10:27:38 2016 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user=mha --slave_host=db4 --slave_ip=192.168.30.214 --slave_port=3306 --workdir=/mha --target_version=5.5.45-log --manager_version=0.52 --relay_log_info=/data/mysql/relay-log.info  --slave_pass=xxx
Sat Aug 27 10:27:38 2016 - [info]   Connecting to root@192.168.30.214(db4)..
  Checking slave recovery environment settings..
    Opening /data/mysql/relay-log.info ... ok.
    Relay log found at /data/mysql, up to mysql-relay-bin.032494
    Temporary relay log file is /data/mysql/mysql-relay-bin.032494
    Testing mysql connection and privileges.. done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Sat Aug 27 10:27:39 2016 - [info] Slaves settings check done.
Sat Aug 27 10:27:39 2016 - [info]
db2 (current master)
 +--db1
 +--db3
 +--db4

Sat Aug 27 10:27:39 2016 - [info] Checking replication health on db1..
Sat Aug 27 10:27:39 2016 - [info]  ok.
Sat Aug 27 10:27:39 2016 - [info] Checking replication health on db3..
Sat Aug 27 10:27:39 2016 - [info]  ok.
Sat Aug 27 10:27:39 2016 - [info] Checking replication health on db4..
Sat Aug 27 10:27:39 2016 - [info]  ok.
Sat Aug 27 10:27:39 2016 - [warning] master_ip_failover_script is not defined.
Sat Aug 27 10:27:39 2016 - [warning] shutdown_script is not defined.
Sat Aug 27 10:27:39 2016 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

 
MySQL Replication Health is OK.
这次终于正常通过了
启动MHA管理程序

[root@monitor ~]# masterha_manager --conf=/etc/masterha_default.cnf &
Sat Aug 27 10:31:51 2016 - [info] Reading default configuratoins from /etc/masterha_default.cnf..
Sat Aug 27 10:31:51 2016 - [info] Reading application default configurations from /etc/masterha_default.cnf..
Sat Aug 27 10:31:51 2016 - [info] Reading server configurations from /etc/masterha_default.cnf..

观察日志

[root@monitor ~]# cat /mha/manager.log
------------------------------省略号---------------------------------------------------
Sat Aug 27 10:33:04 2016 - [info]
db2 (current master)
 +--db1
 +--db3
 +--db4

Sat Aug 27 10:33:04 2016 - [warning] master_ip_failover_script is not defined.
Sat Aug 27 10:33:04 2016 - [warning] shutdown_script is not defined.
Sat Aug 27 10:33:04 2016 - [info] Set master ping interval 1 seconds.
Sat Aug 27 10:33:04 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Sat Aug 27 10:33:04 2016 - [info] Starting ping health check on db2(192.168.30.212:3306)..
Sat Aug 27 10:33:04 2016 - [info] Ping succeeded, sleeping until it doesn't respond..

 

看到目前启动正常 db2是master（不是说好的master是db1么？好吧，我之前做完切过一次了，所以master飘到db2了，大家凑合着看哈） db1 db3 db3是从机
到目前为止 MHA就搭起来了

三、做故障测试，把db2关掉，看下会不会主从自动切换到db1

关掉db2 mysql，我们来tail monitor日志

[root@monitor ~]# tail -f /mha/manager.log
 
------------------------------省略号---------------------------------------------------
Sat Aug 27 11:15:40 2016 - [info] Master failover to db1(192.168.30.211:3306) completed successfully.
Sat Aug 27 11:15:40 2016 - [info]
 
----- Failover Report -----
 
masterha_default: MySQL Master failover db2 to db1 succeeded
 
Master db2 is down!        #DB2 挂了

 

Check MHA Manager logs at monitor:/mha/manager.log for details.
 
Started automated(non-interactive) failover.
The latest slave db1(192.168.30.211:3306) has all relay logs for recovery.
Selected db1 as a new master.
db1: OK: Applying all logs succeeded.
db4: This host has the latest relay log events.
db3: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
db4: OK: Applying all logs succeeded. Slave started, replicating from db1.###db4重新设置主从到db1
db3: OK: Applying all logs succeeded. Slave started, replicating from db1.###db3重新设置主从到db1
db1: Resetting slave info succeeded.
Master failover to db1(192.168.30.211:3306) completed successfully.    ###master 飘到db1 成功
在刷了一大堆日志后，出现了这个汇总报告，主从切换成功
我们去db3 db4上面看下是不是真的切换成功了

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.211
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1869
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:

db4 主从切到db1了，成功

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.30.211
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1869
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 1869
              Relay_Log_Space: 409
              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: 1
1 row in set (0.00 sec)

ERROR:
No query specified

 

再去看看db1，主从已经停止了（废话，都成master了，主从肯定停了）

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.30.212
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 0
              Relay_Log_Space: 126
              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: NULL
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
1 row in set (0.00 sec)

ERROR:
No query specified

至此，mha测试完成，搭建MHA上面的坑，问题还是挺多的，要多看日志多看报错，才能找出问题的所在，当然，一篇靠谱的教程还是要有的

时间： 2025-01-22 04:08:20

搭建Mysql MHA高可用集群+各种报错解决的相关文章

工作笔记：配置MySQL为高可用集群 （1）(2)

2.安装Heartbeat HA 安装到默认目录,两台服务器安装方法相同. (1)安装libnet,这是编译Heartbeat HA需要的 (T115)

轻松构建Mysql高可用集群系统

一. MySQL复制的实现原理 MySQL支持单向.双向复制.异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器.主服务器将更新写入一个二进制日志文件中,并创建一个索引文件以跟踪日志循环.这些日志可以记录发送到从服务器的更新.当一个从服务器连接主服务器时,日志文件会通知主服务器,从服务器在日志中读取的最后一次成功更新的位置.接着,从服务器在上次成功更新的位置处开始进入更新操作.更新完成后从服务器开始进入等待状态,等待主服务器后续的更新. 需要注意的是:在进行复制时,所

CentOS 7下怎么搭建高可用集群？

  CentOS 7下怎么搭建高可用集群.高可用集群是指以减少服务中断时间为目的的服务器集群技术.它通过保护用户的业务程序对外不间断提供的服务,把因软件/硬件/人为造成的故障对业务的影响降低到最小程度.那么新的centos下怎么来搭建高可用集群. 环境:本文以两台机器实现双集热备高可用集群,主机名node1的IP为192.168.122.168 ,主机名node2的IP为192.168.122.169 . 一.安装集群软件必须软件pcs,pacemaker,corosync,fence-agen

CentOS 7下搭建高可用集群

CentOS 7下搭建高可用集群 本文以两台机器实现双集热备高可用集群,主机名node1的IP为192.168.122.168 ,主机名node2的IP为192.168.122.169 . 一.安装集群软件 必须软件pcs,pacemaker,corosync,fence-agents-all,如果需要配置相关服务,也要安装对应的软件 二.配置防火墙 1.禁止防火墙和selinux # systemctl disable firewalld # systemctl stop firewalld

MongoDB高可用集群配置的几种方案

一.高可用集群的解决方案 高可用性即HA(High Availability)指的是通过尽量缩短因日常维护操作(计划)和突发的系统崩溃(非计划)所导致的停机时间,以提高系统和应用的可用性. 计算机系统的高可用在不同的层面上有不同的表现: (1)网络高可用 由于网络存储的快速发展,网络冗余技术被不断提升,提高IT系统的高可用性的关键应用就是网络高可用性,网络高可用性与网络高可靠性是有区别的,网络高可用性是通过匹配冗余的网络设备实现网络设备的冗余,达到高可用的目的. 比如冗余的交换机,冗余的路由器等

基于corosync+pacemaker的nginx高可用集群安装配置

  一.corosync.pacemaker介绍 corosync是用于高可用环境中的提供通讯服务的,它位于高可用集群架构中的底层(Message Layer),扮演着为各节点(node)之间提供心跳信息传递这样的一个角色; pacemaker是一个开源的高可用资源管理器(CRM),位于HA集群架构中资源管理.资源代理(RA)这个层次,它不能提供底层心跳信息传递的功能,它要想与对方节点通信需要借助底层的心跳传递服务,将信息通告给对方.通常它与corosync的结合方式有两种: pacemaker

Linux之HA高可用集群的基础概念总结

HA(High Availability)高可用集群,其特点为根据实际需求为前端Diretor,后端RS-server,数据库服务器,共享存储等集群节点做一个从备份服务器或者多个服务器互相备份,一旦主服务器挂掉,备份服务器能立马检测到并取代主服务器上的资源继续运行服务,从而最大限度避免了因服务器宕机造成的服务中止.  主节点(active/primary)备节点(passive/standby)  主调度器(Director)一般为集群中的关键节点,所以一般都有备份节点的存在:而后端RS-ser

RabbitMQ学习系列（六）: RabbitMQ 高可用集群

前面讲过一些RabbitMQ的安装和用法,也说了说RabbitMQ在一般的业务场景下如何使用.不知道的可以看我前面的博客,http://www.cnblogs.com/zhangweizhong/category/855479.html 本来一直想写一个介绍RabbitMQ高可用的集群的文章.不过,后来发现园子里,有个已经RabbitMQ大牛写了,关于高可用集群的文章了.特别巧合的是,还是以前公司的同事.所以,这里就不啰嗦.直接引用过来吧.原文地址:http://www.cnblogs.com/

高可用集群Heartbeat简述

一.什么是高可用集群?高可用集群是怎么工作的? 高可用集群(HA:High Availability),就是为了保证服务的持续可用性,使用1个或多个备用主机来保证主服务器宕掉后能自动接替其工作的方案. 这些主机中,正在工作的,我们称为主节点,看着主节点挂掉然后才能干活的,我们称之为备节点,一般高可用集群中,只有两个节点是很特殊的HA集群,一般都是3个或3个以上. 而主机工作一般都是提供各种服务,不管是网页服务还是邮件服务或者数据库服务等,我们将这些服务和服务所需的一些特性或者属性称为资源:(re