MHA实现MySQL半同步高可用

一、MHA介绍

---

1、MHA两部分组成

MHA 由两部分组成: MHA Manager(管理节点)和 MHA Node(数据节点)。 MHA Manager可以单独部署在一台独立的机器上管理多个 master-slave 集群,也可以部署在一台 slave 节点上。

2、MHA实现原理

MHA Node 运行在每台 MySQL 服务器上, MHA Manager 会定时探测集群中的 master节点,当 master 出现故障时,它可以自动将最新数据的 slave 提升为新的 master,然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。

二、实验环境

---

1、下载地址

百度网盘链接: https://pan.baidu.com/s/1gkeIvFKxwDCFEBIgUWXeVw 密码: q9gi

2、安装环境

redhat6.5 selinux iptables off

 server1:10.10.10.1(安装 MHA)
 server2:10.10.10.2(Mysql Master)
 server3:10.10.10.3(Mysql Slave)
 server4:10.10.10.4(Mysql Slave)

三、Mysql 安装(server2,3,4)

---

1、安装mysql

 tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
 yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm 
 mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm
 
 /etc/init.d/mysqld restart
 grep "temporary password" /var/log/mysqld.log           ###临时密码 localhost:后面就是密码
 mysql_secure_installation                               ###初始化,密码必须8位数以上,而且大小写字母数字及特殊符号 

2、设置server-id（server2,3,4）

 vim /etc/my.cnf
 server-id=2                           ###2的32次方减 1,唯一标识符,3 个都不一样(随意)
 log-bin=mysql-bin

 /etc/init.d/mysqld restart            ###3 台都重启

四、半同步复制安装

---

1、在master中授权(server2)

 mysql> grant REPLICATION SLAVE on *.* to repl@'10.10.10.%' identified by 'Q1w2e3r4t5y6.';
 mysql> show master status;

2、在slave启动二个线程(serve3、4)

 mysql> change master to master_host='10.10.10.2',master_user='repl',master_password='Q1w2e3r4t5y6.',master_log_file='mysql-bin.000001',master_log_pos=446;          ###master_log_pos:为 master 的 position
 mysql> start slave;
 mysql> show slave status\G;                ###查询的信息这2个为yes
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes 

3、开启master(server2)

 mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
 mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
 mysql> show status like 'Rpl_semi_sync_master_status';                     ###查询主是否开启成功
 +-----------------------------+-------+
 | Variable_name               | Value |
 +-----------------------------+-------+
 | Rpl_semi_sync_master_status | ON    |
 +-----------------------------+-------+

4、开启slave(serve3、4)

 mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
 mysql> STOP SLAVE IO_THREAD;
 mysql> START SLAVE IO_THREAD;
 mysql> show status like 'Rpl_semi_sync_slave_status';                    ###查询从是否开启成功
 +----------------------------+-------+
 | Variable_name              | Value |
 +----------------------------+-------+
 | Rpl_semi_sync_slave_status | ON    |
 +----------------------------+-------+

MHA manager 通过 SSH 访问所有的 node 节点,各个 node 节点也同样需要通过 SSH 来相互发送不同的 relay log文件,所以有必要在每一个node和manager上配置SSH无密码登陆。MHAmanager可通过 masterha_check_ssh 脚本检测 SSH 连接是否配置正常。

五、SSH 无密码连接

---

1、生成rsa key pair

 ssh-keygen

2、server1中把公钥和私钥发送给 server2.3.4

 scp /root/.ssh/id_rsa /root/.ssh/id_rsa.pub root@10.10.10.4:/root/.ssh/

3、server2.3.4 执行

 ssh-copy-id -i /root/.ssh/id_rsa.pub root@自身IP

4、成功安装后,会在/usr/bin 目录下生成如下一系列命令工具

 ll /usr/bin/masterha_*

六、安装 MHA

---

1、安装节点(4 个都要安装):

 yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm

2、依赖性—需要安装的包

 perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
 perl-Email-Date-Format-1.002-5.el6.noarch.rpm
 perl-Log-Dispatch-2.27-1.el6.noarch.rpm
 perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
 perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
 perl-MailTools-2.04-4.el6.noarch.rpm
 perl-MIME-Lite-3.027-2.el6.noarch.rpm
 perl-MIME-Lite-HTML-1.23-2.el6.noarch.rpm
 perl-MIME-Types-1.28-2.el6.noarch.rpm
 perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
 perl-Params-Validate-0.92-3.el6.x86_64.rpm
 perl-TimeDate-1.16-13.el6.noarch.rpm

3、安装manager

 yum install -y mha4mysql-manager-0.56-0.el6.noarch.rpm

七、配置 MHA

---

1、配置app1.cnf

 mkdir /etc/masterha/
 
 vim /etc/masterha/app1.cnf
 [server default]
 user=root
 password=Q1w2e3r4t5y6.
 repl_user=repl
 repl_password=Q1w2e3r4t5y6.
 manager_workdir=/var/log/masterha/app1
 remote_workdir=/var/log/masterha/app1

 [server2]
 hostname=server2                ###也可以写 IP
 [server3]
 hostname=server3
 candidate_master=1              ###设置为候选 master
 [server4]
 hostname=server4

2、验证 ssh 通讯:

 [root@server4 .ssh]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
 Mon Apr 23 17:14:16 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found.  Skipping.
 Mon Apr 23 17:14:16 2018 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
 Mon Apr 23 17:14:16 2018 - [info] Reading server configuration from /etc/masterha/app1.cnf..
 Mon Apr 23 17:14:16 2018 - [info] Starting SSH connection tests..
 Mon Apr 23 17:14:17 2018 - [debug]
 Mon Apr 23 17:14:16 2018 - [debug] Connecting via SSH from
 root@10.10.10.5(10.10.10.5:22) to root@10.10.10.6(10.10.10.6:22)..
 Mon Apr 23 17:14:16 2018 - [debug]
 ok.
 Mon Apr 23 17:14:16 2018 - [debug] Connecting via SSH from
 root@10.10.10.5(10.10.10.5:22) to root@10.10.10.7(10.10.10.7:22)..
 Warning: Permanently added '10.10.10.7' (RSA) to the list of known hosts.
 Mon Apr 23 17:14:16 2018 - [debug]
 ok.
 Mon Apr 23 17:14:17 2018 - [debug]
 Mon Apr 23 17:14:17 2018 - [debug] Connecting via SSH from
 root@10.10.10.6(10.10.10.6:22) to root@10.10.10.5(10.10.10.5:22)..
 Mon Apr 23 17:14:17 2018 - [debug]
 ok.
 Mon Apr 23 17:14:17 2018 - [debug] Connecting via SSH from
 root@10.10.10.6(10.10.10.6:22) to root@10.10.10.7(10.10.10.7:22)..
 Warning: Permanently added '10.10.10.7' (RSA) to the list of known hosts.
 Mon Apr 23 17:14:17 2018 - [debug]
 ok.
 Mon Apr 23 17:14:18 2018 - [debug]
 Mon Apr 23 17:14:17 2018 - [debug] Connecting via SSH from
 root@10.10.10.7(10.10.10.7:22) to root@10.10.10.5(10.10.10.5:22)..
 Mon Apr 23 17:14:17 2018 - [debug]
 ok.
 Mon Apr 23 17:14:17 2018 - [debug] Connecting via SSH from
 root@10.10.10.7(10.10.10.7:22) to root@10.10.10.6(10.10.10.6:22)..
 Mon Apr 23 17:14:17 2018 - [debug]
 ok.
 Mon Apr 23 17:14:18 2018 - [info] All SSH connection tests passed successfully.

3、报错解决

（1）报错权限不对

 [root@server1 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
 [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln393] server3(10.10.10.3:3306)

（2）解决方案:开启 root 和 repl 用户的权限

 mysql> grant all privileges on *.* to 'root'@'%' identified by 'Q1w2e3r4t5y6.' with grant option;
 mysql> grant all privileges on *.* to 'repl'@'%' identified by 'Q1w2e3r4t5y6.' with grant option;
 mysql> flush privileges;

 [root@server1 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf            ###显示 MySQL Replication Health is OK.即为正确

4、server1:开启MHA Manager监控

 [root@server1 app1]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
 [1] 1628
 [root@server1 app1]# masterha_check_status --conf=/etc/masterha/app1.cnf
 app1 (pid:1628)  isrunning(0:PING_OK), master:server2

5、测试

（1）master(server2) down 掉

 /etc/init.d/mysqld stop

（2）在 server4查看slave状态

 mysql> show slave status\G;
 *************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 10.10.10.3
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60

（3）测试结果

发现可以发现自动切换为 server3，MHA实现Mysql半同步高可用搭建完成！！！