mha下载地址，需要翻墙
|
1
|
https://code.google.com/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://ronaldbradford.com/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测试完成!
文章转载来自：ttlsa.com
