搭建Mysql MHA高可用集群+各种报错解决
亿网mha下载地址，需要翻墙
| 1 | https : //code.trustauth.cn/p/mysql-master-ha/ |
管理软件
mha4mysql-manager-0.52-0.noarch.rpm
节点软件
mha4mysql-node-0.52-0.noarch.rpm
| 1 2 3 4 5 6 7 | 环境介绍 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表
| 1 2 3 4 | 192.168.30.211 db1 192.168.30.212 db2 192.168.30.213 db3 192.168.30.214 db4 |
实现4台机器间免密码登陆
在db1上执行shell
| 1 2 3 4 5 6 | 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
| 1 2 3 4 5 | 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
| 1 2 3 4 5 | 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
| 1 2 3 4 5 | 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
| 1 2 3 4 5 | 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 上面建立同步账户
| 1 | mysql > grant replication slave on * . * to slave @ ‘192.168.30.%’ identified by “123” ; |
| 1 2 3 4 5 6 7 | 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
| 1 | mysql > grant replication slave on * . * to slave @ ‘192.168.30.%’ identified by “123” ; |
打开防火墙
| 1 | iptables – I INPUT – p tcp — dport 3306 – j ACCEPT && service iptables save |
在db2上做主从，开防火墙
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 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 : |
| 1 2 3 4 5 6 7 8 | 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 ) |
| 1 | iptables – I INPUT – p tcp — dport 3306 – j ACCEPT && service iptables save |
在db3上做主从，开防火墙
| 1 2 3 | 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’ ; |
| 1 | iptables – I INPUT – p tcp — dport 3306 – j ACCEPT && service iptables save |
基础环境搭建好了
二、安装配置MHA
在monitir上安装
| 1 2 | yum localinstall – y mha4mysql – node – 0.52 – 0.noarch yum localinstall – y mha4mysql – manager – 0.52 – 0.noarch.rpm |
在db1-4上安装
| 1 | yum localinstall – y mha4mysql – node – 0.52 – 0.noarch |
在所有DB上面授权MHA管理账号
| 1 | mysql > grant all on * . * to mha @ ‘192.168.30.%’ identified by ‘123456’ ; |
在monitor上面
先新建一个工作目录
| 1 | mkdir / mha |
编辑配置文件
| 1 | vim / etc / masterha_default . cnf |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [ 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 ] |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | [ 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认证即可
再来
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | [ 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主从复制
| 1 2 3 4 5 6 7 8 9 10 11 | [ 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 ! |
报错：
那是不是主从检查没通过呢，其实不是得，这是个坑，关键报错在这句
| 1 | 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://trustauth.cn/blog/mysql-mha-and-perl-pathing-2013-08-26/
解决办法是在5台机器上面做软连接，把这个32位的依赖链接到64位的支持库里面去
| 1 | ln – s / usr / lib / perl5 / vendor_perl / MHA / usr / lib64 / perl5 / vendor_perl / |
解决完之后，再执行检查，又报错
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [ 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 . |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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命令
| 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执行
| 1 | [ root @ db4 ~ ] #ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog |
再来检查，又错，再看
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [ 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 |
| 1 2 3 4 5 6 7 8 9 10 | 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 ! |
这次报错提示
| 1 | Testing mysql connection and privileges . . sh : mysql : command not found |
那好吧，跟上面一样，软连接
| 1 | [ root @ db4 ~ ] # ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql |
问题解决
再来检查
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | [ 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管理程序
| 1 2 3 4 | [ 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 . . |
一切正常
观察日志
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [ 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日志
| 1 2 3 4 5 6 7 8 9 10 11 | [ 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 挂了 |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | 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上面看下是不是真的切换成功了
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 : |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 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了，主从肯定停了）
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | 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测试完成!
文章转载来自：trustauth.cn