第一节 前言
1.1MHA（master hight availability）（至少3台）
由日本DeNA公司youshimaton开发
是一套优秀的实现mysql高可用的解决方案
数据库的自动故障切换操作能做到0～30秒之内
mha能确保在故障切换过程中保证数据的一致性，以达到真正意义上的高可用
1.2MHA组成
MHA Manager
可以单独部署在一台独立的机器上，管理其他节点
也可以部署在一台slave节点上
MHA Node
运行在每台mysql服务器上
1.3MHA集群构架
1.4MHA工作过程
由manager定时探测集群中的master节点
当master故障时,manager自动将拥有最新数据的slave提升为新的master
关键点：
从宕机崩溃的master保存二进制日志文件事件
识别含有最新更新的slave
应用差异的中继日志(relay-log)到其他的slave
应用从master保存的二进制日志事件
提升一个slave为新的master
使其他的slave连接新的master进行复制
第二节工具准备
linux==>CentOS Linux release 7.4.1708 (Core)
mysql==>mysql-5.7.26
mha==>mha5-0.58
链接：https://pan.baidu.com/s/1mH37WCTkbmJjJiuPqG51PA 提取码：jt7e
第三节实战操作
为了试验方便所有主机作如下操作
[root@mysql102 ~]# setenforce 0
[root@mysql102 ~]# vim /etc/selinux/config
SELINUX=enforcing
[root@mysql102 ~]# systemctl stop firewalld
[root@mysql102 ~]# systemctl disable firewalld
[root@mysql102 ~]# iptables -F
[root@mysql102 ~]# yum -y install iptables-services
[root@mysql102 ~]# service iptables save
3.1安装mysql
50，102-105主机
[root@ mysql103 mysql]# ls
mysql-community-client-5.7.26-1.el7.x86_64.rpm
mysql-community-common-5.7.26-1.el7.x86_64.rpm
mysql-community-devel-5.7.26-1.el7.x86_64.rpm
mysql-community-embedded-5.7.26-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.26-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.26-1.el7.x86_64.rpm
mysql-community-libs-5.7.26-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm
mysql-community-server-5.7.26-1.el7.x86_64.rpm
[root@ mysql103 mysql]# yum -y install *.rpm
[root@ mysql103 ~]# systemctl start mysqld
[root@ mysql103 ~]# systemctl enable mysqld
[root@ mysql103 ~]# vim /etc/my.cnf
[mysqld]
character_set_server=utf8
validate_password_length=6
validate_password_policy=0
[root@mysql103 ~]# systemctl restart mysqld
[root@ mysql103 ~]# grep 'temporary password' /var/log/mysqld.log
2019-05-21T02:11:44.275556Z 1 [Note] A temporary password is generated for root@localhost: s8!jd=Ju=dHn
[root@ mysql103 ~]# mysql -uroot -p's8!jd=Ju=dHn'
mysql> alter user user() identified by 'Luojun.com522527';
3.2主从配置
1）102主机
[root@mysql102 ~]# vim /etc/my.cnf
[mysqld]
.......
log_bin=master102
server_id=1
binlog_format="mixed"
relay_log_purge=off
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1
.........
[root@mysql102 ~]# systemctl restart mysqld
[root@mysql102 ~]# mysql -uroot -pLuojun.com522527
mysql> grant replication slave on *.* to repluser@'%' identified by '123456';
mysql> show master status\G;
*************************** 1. row ***************************
File: master102.000014
Position: 441
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> exit
2）103主机
[root@ mysql103 ~]# vim /etc/my.cnf
......
log_bin=master103
server_id=3
binlog_format="mixed"
relay_log_purge=off
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1
......
[root@ mysql103 ~]# systemctl restart mysqld
[root@ mysql103 ~]# mysql -uroot -pLuojun.com522527
mysql> grant replication slave on *.* to repluser@'%' identified by '123456';
mysql> change master to
-> master_host='192.168.68.102',
-> master_user='repluser',
-> master_password='123456',
-> master_log_file='master102.000014',
-> master_log_pos=441;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.68.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master102.000014
Read_Master_Log_Pos: 441
Relay_Log_File: dispatch103-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master102.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..........
mysql> exit
3）104主机
[root@mysql104 ~]# vim /etc/my.cnf
[mysqld]
......
log_bin=master104
server_id=4
binlog_format="mixed"
relay_log_purge=off
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled=1
rpl-semi-sync-slave-enabled=1
[root@ mysql104 ~]# systemctl restart mysqld
[root@ mysql104 ~]# mysql -uroot -pLuojun.com522527
mysql> grant replication slave on *.* to repluser@'%' identified by '123456';
mysql> change master to
-> master_host='192.168.68.102',
-> master_user='repluser',
-> master_password='123456',
-> master_log_file='master102.000014',
-> master_log_pos=441;
mysql> start slave;
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.68.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master102.000014
Read_Master_Log_Pos: 441
Relay_Log_File: dispatch104-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master102.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..............
mysql> exit
4）105主机
[root@mysql105 ~]# vim /etc/my.cnf
[mysqld]
.........
server_id=5
relay_log_purge=off
plugin_load="rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled=1
........
[root@mysql105 ~]# systemctl restart mysqld
[root@mysql105 ~]# mysql -uroot -pLuojun.com522527
mysql> change master to
-> master_host='192.168.68.102',
-> master_user='repluser',
-> master_password='123456',
-> master_log_file='master102.000014',
-> master_log_pos=441;
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.68.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master102.000014
Read_Master_Log_Pos: 441
Relay_Log_File: mysql105-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master102.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....
mysql> exit
5）50主机
[root@mysql50 ~]# vim /etc/my.cnf
[mysqld]
.........
server_id=2
relay_log_purge=off
plugin_load="rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled=1
.....
[root@ mysql50 ~]# systemctl restart mysqld
[root@ mysql50 ~]# mysql -uroot -pLuojun.com522527
mysql> change master to
-> master_host='192.168.68.102',
-> master_user='repluser',
-> master_password='123456',
-> master_log_file='master102.000014',
-> master_log_pos=441;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.68.102
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master102.000014
Read_Master_Log_Pos: 441
Relay_Log_File: web50-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: master102.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......
mysql> exit
6）测试主从
在master主102上创建库，在其他103,104,105,50上验证
建库：
mysql> create database testDB;
验证：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbsdb |
| mysql |
| performance_schema |
| sys |
| testDB |
3.3集群环境准备
1）50,102-105相互无密码连接(以102为例，所有主机都做)
[root@mysql102 ~]# ssh-keygen -f "/root/.ssh/id_rsa" -N ""
[root@mysql102 ~]# for i in 50 102 103 104 105
> do
> ssh-copy-id 192.168.68.$i
> done
2）106无密码连接50,102-105
[root@mysql106 ~]# ssh-keygen -f "/root/.ssh/id_rsa" -N ""
[root@mysql106 ~]# for i in 50 102 103 104 105 106
> do
> ssh-copy-id 192.168.68.$i
> done
3）50,102-106安装perl软件包（以102为例）
[root@mysql102 ~]# yum -y install epel-release
[root@mysql102 ~]# yum -y install perl-*
3.4安装MHA集群软件
1）50，102-105主机安装mha-node
[root@mysql102 iso]# ls
mha4mysql-node-0.58.tar.gz
[root@mysql102 iso]# tar -xvf mha4mysql-node-0.58.tar.gz
[root@mysql102 mha4mysql-node-0.58]# perl Makefile.PL
[root@mysql102 mha4mysql-node-0.58]# make
[root@mysql102 mha4mysql-node-0.58]# make install
2）106主机安装mha-manage
[root@mysql106 iso]# tar -xvf mha4mysql-manager-0.58.tar.gz
[root@mysql106 mha4mysql-manager-0.58]# perl Makefile.PL
[root@mysql106 mha4mysql-manager-0.58]# make
[root@mysql106 mha4mysql-manager-0.58]# make install
3.5配置集群
1）查看master管理命令是否正确
[root@mysql106 iso]# ls /root/perl5/bin/master*
/root/perl5/bin/masterha_check_repl /root/perl5/bin/masterha_master_monitor
/root/perl5/bin/masterha_check_ssh /root/perl5/bin/masterha_master_switch
/root/perl5/bin/masterha_check_status /root/perl5/bin/masterha_secondary_check
/root/perl5/bin/masterha_conf_host /root/perl5/bin/masterha_stop
/root/perl5/bin/masterha_manager
[root@mysql106 ~]# echo 'export PATH=/root/perl5/bin:$PATH' >> /etc/profile[root@mysql106 ~]# source /etc/profile
２）配置管理节点主机配置文件
#新建目录
[root@mysql106 mha4mysql-manager-0.58]# mkdir /etc/mha
#拷贝配置文件过去
[root@mysql106 mha4mysql-manager-0.58]# cp samples/conf/app1.cnf /etc/mha/
[root@mysql106 ~]# vim /etc/mha/app1.cnf
[server default]
manager_workdir=/etc/mha #管理主机的工作目录
manager_log=/etc/mha/manager.log #日志
master_ip_failover_script=/etc/mha/master_ip_failover #故障切换脚本位置和名称（perl）
ssh_user=root #106登录50,102-105的用户名
ssh_port=22 #端口号
repl_user=repluser #主从同步用户名
repl_password=123456
user=root #数据库用户名，106监视主库用的
password=123456
[server1]
hostname=192.168.68.102 #主机ip
port=3306
candidate_master=1 #设置为候选主库master
[server2]
hostname=192.168.68.103
port=3306
candidate_master=1
[server3]
hostname=192.168.68.104
port=3306
candidate_master=1
[server4]
hostname=192.168.68.50
port=3306
no_master=1 #不竟选主库master
[server4]
hostname=192.168.68.105
port=3306
no_master=1
３）创建vip地址切换脚本
[root@mysql106 ~]# cp /iso/master_ip_failover /etc/mha/ #此脚本已经分享了
[root@mysql106 ~]# chmod +x /etc/mha/master_ip_failover
[root@mysql106 ~]# vim /etc/mha/master_ip_failover
.......
my $vip = '192.168.68.240/24'; # Virtual IP
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #网卡名称具体看主机，我的是ens33
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #网卡名称具体看主机，我的是ens33
...........
４）50,102-105授权root给106
直接在主库102操作自动会同步过去
[root@mysql102 ~]# mysql -uroot -pLuojun.com522527
mysql> grant all on *.* to root@'192.168.68.%' identified by '123456'; # 具体网段看实际情况，我的是192.168.68.0/24
mysql> select user,host from mysql.user where user='root'; #验证是否添加成功
５）把vip地址绑定在当前主库102的eth0接口上
[root@mysql102 ~]# ifconfig ens33:1 192.168.68.240 #临时绑定vip,不能设置永久的，否则切换不了
[root@mysql102 ~]# ifconfig ens33:1ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.68.240 netmask 255.255.255.0 broadcast 192.168.68.255
ether 00:0c:29:bc:30:ca txqueuelen 1000 (Ethernet)
3.6启动
1）检查
##在管理节点上做ssh检查
[root@mysql106 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Mon Nov 26 16:47:08 2018 - [info] All SSH connection tests passed successfully.
##在管理节点上查看集群状态
[root@mysql106 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
2）启动服务
masterha_manager --conf=/etc/mha/app1.cnf [选项]
--remove_dead_master_conf #在/etc/mha/app1.cnf删除宕机主库配置
--ignore_last_failover #忽略xxx.headlth文件，即8个小时连续故障切换vip
命令：
[root@mysql106 ~]# masterha_manager --conf=/etc/mha/app1.cnf \
> --remove_dead_master_conf --ignore_last_failover #注意终端会挂起
[root@mysql106 ~]# masterha_check_status --conf=/etc/mha/app1.cnf #查看状态
app1 (pid:9196) is running(0:PING_OK), master:192.168.4.51
第四节测试
测试一：插入查看
50客户端:
[root@mysql50 ~]# mysql -uroot -p123456 -h192.168.68.240
mysql>create database db8;
mysql>create table db8.t1(id int);
mysql> insert into db8.t1 values (51);
mysql> insert into db8.t1 values (50);
mysql> insert into db8.t1 values (88);
mysql> select * from db8.t1;
+------+
| id |
+------+
| 51 |
| 50 |
| 88 |
测试二：关闭102主机查看主库位置
[root@mysql102 jluo]# systemctl stop mysqld
刚才50上
mysql> select @@hostname; #刚才开的终端并没有断开
+------------+
| @@hostname |
+------------+
| mysql103 | #说明已经切换主库了
也可在54从库中查看主库信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.68.103 #主机ip是主机103的
在106配置文件查看是否有server1的配置
[root@mysql-56 ~]# cat /etc/mha/app1.cnf | grep server1 #无显示
[root@mysql-56 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING). #主库挂了，hma-manager（管理节点）就被杀死，最后触发脚本执行主库切换操作
154 692
测试三：恢复102数据库服务
[root@mysql106 ~]# vim /etc/mha/app1.cnf #添加如下几行
[server1]
candidate_master=1
hostname=192.168.68.102
port=3306
102主机：设置为103的从库
mysql> change master to \
-> master_host='192.168.68.103',
-> master_user='repluser',
-> master_password='123456',
-> master_log_file='master103.000008',
-> master_log_pos=441;
mysql> start slave;
参数解读
参数信息参数含义备注
manager_workdir #设置manager的工作目录
manager_log #设置manager的日志
master_binlog_dir #设置master 保存binlog的位置，以便MHA可以找到master的日志
master_ip_failover_script #设置自动failover时候的切换脚本
master_ip_online_change_script #设置手动切换时候的切换脚本
password #设置mysql中root用户的密码
user #设置监控用户root
ping_interval #设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
remote_workdir #设置远端mysql在发生切换时binlog的保存位置
repl_password #设置复制用户的密码
repl_user #设置复制环境中的复制用户名
report_script #设置发生切换后发送的报警的脚本
shutdown_script #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用）
ssh_user #设置ssh的登录用户名
hostname #主机名／IP
port #端口号
candidate_master #设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
check_repl_delay #默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master
错误汇总
1，可能错误1
[root@dispatch103 mha4mysql-node-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...missing.
[root@dispatch103 mha4mysql-node-0.58]# yum -y install perl-DBD-MySQL
2，可能错误2
[root@mysql106 mha4mysql-manager-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...missing.
- Log::Dispatch ...missing.
- Parallel::ForkManager ...missing.
- MHA::NodeConst ...loaded. (0.58)
[root@mysql106 mha4mysql-manager-0.58]# yum -y install liblog-dispatch-perl libparallel-forkmanager-per libconfig-tiny-perl
看官福利：
结束语：
更多精彩内容持续更新中，关注我，有你更精彩。
网友评论