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: 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上安装
如果安装0.56版本的,则需要添加epel源来提供需要的依赖
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管理用戶名
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/perl5/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.
我的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.
这次终于正常通过了
启动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: 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.01 sec) ERROR: No query specified
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上面的坑,问题还是挺多的,要多看日志多看报错,才能找出问题的所在,当然,一篇靠谱的教程还是要有的
转载于:https://blog.51cto.com/niubdada/1843217