MHA
MHA 安装
安装依赖
yum -y install perl-DBD-MySQL*
yum -y install perl-Config-Tiny*
yum -y install perl-Parallel-ForkManager*
yum -y install perl-MailTools*
yum -y install perl-Email-Date-Format*
yum -y install perl-Mail-Sender*
yum -y install perl-MIME-Types*
yum -y install perl-MIME-Lite*
yum -y install perl-Mail-Sendmail*
yum -y install perl-Log-Dispatch*
下载MHA Manager和Node：下载
安装MHA
[root@centos ~]# rpm -ivh mha4mysql-*
Preparing... ########################################### [100%]
1:mha4mysql-node ########################################### [ 50%]
2:mha4mysql-manager ########################################### [100%]
[root@centos ~]#
配置MHA
Mysql 5.7
Master 66(VIP on 65)
Slave 67, 68
Manager on 66(Mac 开4个双核Centos要炸……)
GTID
建立主从
Master & Slave:
grant replication slave, replication client on *.* to 'repl'@'192.168.199.%' identified by 'xiaocan';
Master:
不用干啥……
Slave:
change master to master_host='192.168.199.66',master_password='xiaocan',master_user='repl',master_auto_position=1;
start slave;
show slave status\G
Master建立MHA用户
grant all privileges on *.* to 'mhager'@'192.168.199.%' identified by 'xiaocan';
建立Master VIP
建立VIP实际上就是建立一个内网IP:
ifconfig eth0:1 192.168.199.65 netmask 255.255.255.0 broadcast 192.168.199.255 up
如果其他服务器无法ping通，需要进行一下广播:
arping -f -q -c 5 -w 5 -I eth0 -s 192.168.199.65 -U 192.168.199.1
配置SSH 生成证书
这里可以注意的是，其实只用生成一套公钥和私钥，复制到其他机器上使用就可以了。
- 创建用户
在所有服务器上创建mhamysql用户
useradd -d /home/mhamysql mhamysql
然后切换到新用户
su mhamysql
- 生成公钥和私钥
ssh-keygen -t rsa
[mhamysql@centos ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/mhamysql/.ssh/id_rsa):
Created directory '/home/mhamysql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/mhamysql/.ssh/id_rsa.
Your public key has been saved in /home/mhamysql/.ssh/id_rsa.pub.
The key fingerprint is:
dc:37:7e:67:59:3c:d7:67:59:26:25:8c:d7:d0:8b:92 mhamysql@centos.mysql01
The key's randomart image is:
+--[ RSA 2048]----+
| oo+.|
| . ooo|
| ..o +|
| . . E . =+|
| S . + oB|
| o . .*|
| . ..o|
<table>
<thead>
<tr>
<th id="._o" style="text-align:left;"> . o </th>
</tr>
</thead>
<tbody>
</tbody>
</table>
[mhamysql@centos ~]$
- 创建完成之后，可以在.ssh目录中找到公钥和私钥：
[mhamysql@centos .ssh]$ ls -al
总用量 20
drwx------. 2 mhamysql mhamysql 4096 10月 27 03:18 .
drwx------. 3 mhamysql mhamysql 4096 10月 27 03:24 ..
-rw-------. 1 mhamysql mhamysql 1675 10月 27 00:44 id_rsa
-rw-r--r--. 1 mhamysql mhamysql 405 10月 27 00:44 id_rsa.pub
-rw-r--r--. 1 mhamysql mhamysql 792 10月 27 03:18 known_hosts
[mhamysql@centos .ssh]$
- 将公钥放入进行连接的服务器的authorized_keys中:
cd ~/.ssh
cat id_rsa.pub >> authorized_keys
- 给私钥、公钥、authorized_keys分配正确的权限:
chmod 0644 authorized_keys
chmod 0600 id_rsa
- 在管理端测试能否连接到各台服务器:
[mhamysql@centos .ssh]$ chmod 644 authorized_keys
[mhamysql@centos .ssh]$ ssh mhamysql@192.168.199.66
The authenticity of host '192.168.199.66 (192.168.199.66)' can't be established.
RSA key fingerprint is f6:7a:c3:ec:11:1c:f7:32:b5:ea:ef:44:0e:37:1d:8a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.199.66' (RSA) to the list of known hosts.
Last login: Thu Oct 27 04:12:19 2016 from 192.168.199.68
[mhamysql@centos ~]$ exi
-bash: exi: command not found
[mhamysql@centos ~]$ exit
logout
Connection to 192.168.199.66 closed.
[mhamysql@centos .ssh]$ ssh mhamysql@192.168.199.67
[mhamysql@centos ~]$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:74:86:9E
inet addr:192.168.199.67 Bcast:192.168.199.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe74:869e/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:14661 errors:0 dropped:0 overruns:0 frame:0
TX packets:6006 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:3086340 (2.9 MiB) TX bytes:596233 (582.2 KiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
[mhamysql@centos ~]$ exit
logout
Connection to 192.168.199.67 closed.
[mhamysql@centos .ssh]$ ssh mhamysql@192.168.199.68
[mhamysql@centos ~]$ ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:CA:1B:53
inet addr:192.168.199.68 Bcast:192.168.199.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:feca:1b53/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:13391 errors:0 dropped:0 overruns:0 frame:0
TX packets:5330 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:2976597 (2.8 MiB) TX bytes:501918 (490.1 KiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:0 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:0 (0.0 b) TX bytes:0 (0.0 b)
[mhamysql@centos ~]$ exit;
配置文件设置
创建MHA配置文件/mysql/mha/cnf/3306.cnf:
[server default]
# MHA Mysql User Info
user=mhager
password=xiaocan
# MHA Work Directory
manager_workdir=/mha/3306
manager_log=/mha/3306/3306.log
remote_workdir=/mha/3306
# MHA SSH User
ssh_user=mhamysql
# Mysql Replication User
repl_user=repl
repl_password=xiaocan
ping_interval=1
# 故障恢复脚本，传递2个参数，VIP的地址和网卡下子IP的个数(eth0:x)
master_ip_failover_script="/mha/master_ip_failover.sh 192.168.199.65 1"
# 在线切换脚本，同样传递2个参数
master_ip_online_change_script="/mha/master_ip_online_change.sh 192.168.199.65 1"
# Mysql服务器
[server1]
hostname=192.168.199.66
port=3306
master_binlog_dir=/mysql/3306
candidate_master=1
master_pid_file=/mysql/3306/mysql.pid
[server2]
hostname=192.168.199.67
port=3306
master_binlog_dir=/mysql/3306
candidate_master=1
master_pid_file=/mysql/3306/mysql.pid
[server3]
hostname=192.168.199.68
port=3306
master_binlog_dir=/mysql/3306
candidate_master=1
master_pid_file=/mysql/3306/mysql.pid
# 不选举为Master服务器
no_master=1
#[binlog1]
#hostname=192.168.1.190
#master_binlog_dir=/data1/mha/binlog/3400
#no_master=1
#ignore_fail=1
放置masteripfailover.sh 和 masteriponline_change.sh 在/mha下:
scp /Users/ResidueXiao/Desktop/Mycat/mycat_1024_MHA/master_ip_* root@192.168.199.66:/mha
设置权限:
chown -R mhamysql:mhamysql mha/
chmod 755 master_ip_*
检查配置:
masterha_check_ssh --conf=/mha/cnf/3306.cnf
masterha_check_repl --conf=/mha/cnf/3306.cnf
正确结果如下:
[mhamysql@centos mha]$ masterha_check_ssh --conf=/mha/cnf/3306.cnf
Thu Oct 27 06:35:48 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Oct 27 06:35:48 2016 - [info] Reading application default configuration from /mha/cnf/3306.cnf..
Thu Oct 27 06:35:48 2016 - [info] Reading server configuration from /mha/cnf/3306.cnf..
Thu Oct 27 06:35:48 2016 - [info] Starting SSH connection tests..
Thu Oct 27 06:35:51 2016 - [debug]
Thu Oct 27 06:35:48 2016 - [debug] Connecting via SSH from mhamysql@192.168.199.66(192.168.199.66:22) to mhamysql@192.168.199.67(192.168.199.67:22)..
Thu Oct 27 06:35:50 2016 - [debug] ok.
Thu Oct 27 06:35:50 2016 - [debug] Connecting via SSH from mhamysql@192.168.199.66(192.168.199.66:22) to mhamysql@192.168.199.68(192.168.199.68:22)..
Thu Oct 27 06:35:51 2016 - [debug] ok.
Thu Oct 27 06:35:52 2016 - [debug]
Thu Oct 27 06:35:50 2016 - [debug] Connecting via SSH from mhamysql@192.168.199.68(192.168.199.68:22) to mhamysql@192.168.199.66(192.168.199.66:22)..
Thu Oct 27 06:35:51 2016 - [debug] ok.
Thu Oct 27 06:35:51 2016 - [debug] Connecting via SSH from mhamysql@192.168.199.68(192.168.199.68:22) to mhamysql@192.168.199.67(192.168.199.67:22)..
Thu Oct 27 06:35:52 2016 - [debug] ok.
Thu Oct 27 06:35:54 2016 - [debug]
Thu Oct 27 06:35:49 2016 - [debug] Connecting via SSH from mhamysql@192.168.199.67(192.168.199.67:22) to mhamysql@192.168.199.66(192.168.199.66:22)..
Thu Oct 27 06:35:52 2016 - [debug] ok.
Thu Oct 27 06:35:52 2016 - [debug] Connecting via SSH from mhamysql@192.168.199.67(192.168.199.67:22) to mhamysql@192.168.199.68(192.168.199.68:22)..
Warning: Permanently added '192.168.199.68' (RSA) to the list of known hosts.
Thu Oct 27 06:35:54 2016 - [debug] ok.
Thu Oct 27 06:35:54 2016 - [info] All SSH connection tests passed successfully.
[mhamysql@centos mha]$ masterha_check_repl --conf=/mha/cnf/3306.cnf
Thu Oct 27 06:40:17 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Oct 27 06:40:17 2016 - [info] Reading application default configuration from /mha/cnf/3306.cnf..
Thu Oct 27 06:40:17 2016 - [info] Reading server configuration from /mha/cnf/3306.cnf..
Thu Oct 27 06:40:17 2016 - [info] MHA::MasterMonitor version 0.56.
Thu Oct 27 06:40:17 2016 - [info] GTID failover mode = 1
Thu Oct 27 06:40:17 2016 - [info] Dead Servers:
Thu Oct 27 06:40:17 2016 - [info] Alive Servers:
Thu Oct 27 06:40:17 2016 - [info] 192.168.199.66(192.168.199.66:3306)
Thu Oct 27 06:40:17 2016 - [info] 192.168.199.67(192.168.199.67:3306)
Thu Oct 27 06:40:17 2016 - [info] 192.168.199.68(192.168.199.68:3306)
Thu Oct 27 06:40:17 2016 - [info] Alive Slaves:
Thu Oct 27 06:40:17 2016 - [info] 192.168.199.67(192.168.199.67:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Thu Oct 27 06:40:17 2016 - [info] GTID ON
Thu Oct 27 06:40:17 2016 - [info] Replicating from 192.168.199.66(192.168.199.66:3306)
Thu Oct 27 06:40:17 2016 - [info] Primary candidate for the new Master (candidate_master is set)
Thu Oct 27 06:40:17 2016 - [info] 192.168.199.68(192.168.199.68:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Thu Oct 27 06:40:17 2016 - [info] GTID ON
Thu Oct 27 06:40:17 2016 - [info] Replicating from 192.168.199.66(192.168.199.66:3306)
Thu Oct 27 06:40:17 2016 - [info] Not candidate for the new Master (no_master is set)
Thu Oct 27 06:40:17 2016 - [info] Current Alive Master: 192.168.199.66(192.168.199.66:3306)
Thu Oct 27 06:40:17 2016 - [info] Checking slave configurations..
Thu Oct 27 06:40:17 2016 - [info] read_only=1 is not set on slave 192.168.199.67(192.168.199.67:3306).
Thu Oct 27 06:40:17 2016 - [info] read_only=1 is not set on slave 192.168.199.68(192.168.199.68:3306).
Thu Oct 27 06:40:17 2016 - [info] Checking replication filtering settings..
Thu Oct 27 06:40:17 2016 - [info] binlog_do_db= , binlog_ignore_db=
Thu Oct 27 06:40:17 2016 - [info] Replication filtering check ok.
Thu Oct 27 06:40:17 2016 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Thu Oct 27 06:40:17 2016 - [info] Checking SSH publickey authentication settings on the current master..
Thu Oct 27 06:40:17 2016 - [info] HealthCheck: SSH to 192.168.199.66 is reachable.
Thu Oct 27 06:40:17 2016 - [info]
192.168.199.66(192.168.199.66:3306) (current master)
+--192.168.199.67(192.168.199.67:3306)
+--192.168.199.68(192.168.199.68:3306)
Thu Oct 27 06:40:17 2016 - [info] Checking replication health on 192.168.199.67..
Thu Oct 27 06:40:17 2016 - [info] ok.
Thu Oct 27 06:40:17 2016 - [info] Checking replication health on 192.168.199.68..
Thu Oct 27 06:40:17 2016 - [info] ok.
Thu Oct 27 06:40:17 2016 - [info] Checking master_ip_failover_script status:
Thu Oct 27 06:40:17 2016 - [info] /mha/master_ip_failover.sh 192.168.199.65 1 --command=status --ssh_user=mhamysql --orig_master_host=192.168.199.66 --orig_master_ip=192.168.199.66 --orig_master_port=3306
Checking the Status of the script.. OK
Thu Oct 27 06:40:17 2016 - [info] OK.
Thu Oct 27 06:40:17 2016 - [warning] shutdown_script is not defined.
Thu Oct 27 06:40:17 2016 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
使用MHA
启动MHA Manager
启动:
masterha_manager --conf=/mha/cnf/3306.cnf --ignore_last_failover < /dev/null >2&>1
检查是否启动:
[mhamysql@centos root]$ masterha_check_status --conf=/mha/cnf/3306.cnf
3306 (pid:2472) is running(0:PING_OK), master:192.168.199.66
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xiaocan |
+--------------------+
5 rows in set (0.00 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user,host,command from processlist;
+--------+----------------------+------------------+
| user | host | command |
+--------+----------------------+------------------+
| repl | 192.168.199.68:59594 | Binlog Dump GTID |
| repl | 192.168.199.67:53296 | Binlog Dump GTID |
| mhager | 192.168.199.66:52194 | Sleep |
| root | localhost | Query |
+--------+----------------------+------------------+
4 rows in set (0.00 sec)
mysql>
故障切换
停止Master服务器:
mysqladmin -u root -p -S /mysql/3306/my3306.sock shutdown
检查MHA状态:
[mhamysql@centos mha]$ masterha_check_status --conf=/mha/cnf/3306.cnf
3306 is stopped(2:NOT_RUNNING).
此时检查VIP的IP和Mysql主库状态就会发现已经切换完成。
注意事项
MHA只适用于一次的故障切换，当Master故障切换之后，Manager进程就会断掉。如果还需要切换的话，需要手动去维护MHA和Mysql复制的状态、复制进程。每次在故障切换的时候会在本地保存一个文件，如果不删除的话，就无法启动，除非使用--ignorelastfailover参数忽略最后一次故障切换。