Skip to main content

Mysql MHA

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 生成证书

这里可以注意的是,其实只用生成一套公钥和私钥,复制到其他机器上使用就可以了。

  1. 创建用户
在所有服务器上创建mhamysql用户
useradd -d /home/mhamysql mhamysql  
然后切换到新用户
su mhamysql  
  1. 生成公钥和私钥
    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 ~]$
  1. 创建完成之后,可以在.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]$
  1. 将公钥放入进行连接的服务器的authorized_keys中:
cd ~/.ssh  
cat id_rsa.pub >> authorized_keys  
  1. 给私钥、公钥、authorized_keys分配正确的权限:
chmod 0644 authorized_keys  
chmod 0600 id_rsa  
  1. 在管理端测试能否连接到各台服务器:
[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参数忽略最后一次故障切换。