MYSQL搭建MHA集群主从竞主

MHA软件介绍
实现MySQL高可用的解决方案-数据库的自动故障切换操作能做到在0~30秒之内完成 期间会丢失数据
MHA能确保在故障切换过程中最大限度保证数据的一致性，以达到真正意义上的高可用

db主机 192.168.1.11-13 vip 192.168.1.100
管理admin主机 192.168.1.14

集群配置

管理主机

[root@admin ~]# yum -y install perl-ExtUtils-* perl-CPAN*
[root@admin mha]# ls
master_ip_failover                              perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.56.tar.gz                   perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm            perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm

所有db相互免密登录，验证。admin免密登录node。

ssh-keygen  ....

3台数据库服务运行参数的相同配置

加载master、slave模块
启用master、slave半同步复制模式
启用binlog日志文件
禁止删除本机的中继日志文件
添加拷贝sql命令的连接用户repluser

[root@db1 ~]# vim /etc/my.cnf
[mysqld]
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=
rpl_semi_sync_slave_enabled=1
relay_log_purge=0
server_id=11
log_bin=db1
...

配置一主db1 两从db2 db3

[root@db1 ~]# systemctl restart mysqld
[root@db1 ~]# mysql -uroot -p123456
mysql> grant replication slave on  *.* to repluser@"%" identified by "123qqq...A";
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| db1.000002 |      154 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+

[root@db2 ~]# mysql -uroot -p123456
mysql> change master to master_host="192.168.1.11",master_user="repluser", master_password="123qqq...A",master_log_file="db1.000002",master_log_pos=154;
mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

配置管理主机编译mha

[root@admin mha]# tar xf mha4mysql-manager-0.56.tar.gz
[root@admin mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@admin mha4mysql-manager-0.56]# perl  Makefile.PL
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.627)
- DBD::mysql            ...loaded. (4.023)
- Time::HiRes           ...loaded. (1.9725)
- Config::Tiny          ...loaded. (2.14)
- Log::Dispatch         ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst        ...loaded. (0.56)
*** Module::AutoInstall configuration finished.				#保证完成
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager
Writing MYMETA.yml and MYMETA.json
[root@admin mha4mysql-manager-0.56]# make && make install
Appending installation info to /usr/lib64/perl5/perllocal.pod

创建并编辑主配置文件

[root@admin mha4mysql-manager-0.56]# mkdir /etc/mha
[root@admin mha4mysql-manager-0.56]# cp samples/conf/app1.cnf  /etc/mha
[root@admin ~]# 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
ssh_user=root
ssh_port=22
repl_user=repluser
repl_password=123qqq...A
user=admin
password=123qqq...A
[server1]
hostname=192.168.1.11
port=3306
candidate_master=1
[server2]
hostname=192.168.1.12
port=3306
candidate_master=1
[server3]
hostname=192.168.1.13
port=3306
candidate_master=1

配置介绍
[server default]								#管理服务默认配置 
manager_workdir=/etc/mha						#工作目录 
manager_log=/etc/mha/manager.log				#日志文件
master_ip_failover_script=/etc/mha/master_ip_failover			#故障切换脚本 
ssh_user=root								#访问ssh服务用户 
ssh_port=22									#ssh服务端口 
repl_user=repluser 						#数据同步授权用户 
repl_password=123qqq...A				#密码 	
user=root									#监控用户   数据库的监视账号
password=123qqq...A						#密码
[server1]										#定义被管理的数据库服务器
hostname=192.168.4.51		#serverip
port=3306				#端口
candidate_master=1			#竞选主服务器

管理主机指定vip,创建故障切换脚本

[root@admin ~]# cp mha/master_ip_failover  /etc/mha/
[root@admin ~]#  chmod  +x  /etc/mha/master_ip_failover
[root@admin ~]# vim +35 /etc/mha/master_ip_failover
 35 my $vip = '192.168.1.100/24';  # Virtual IP
 36 my $key = "1";
 37 my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
 38 my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";

db[1-3] 添加监控用户，安装mha的rpm包，均需要设置vip

[root@admin ~]# scp mha/mha4mysql-node-0.56-0.el6.noarch.rpm  root@192.168.1.11:/root/
[root@db1 ~]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db1 ~]# mysql -uroot -p123456 -e 'grant all on *.* to admin@"%" identified by "123qqq...A"'
[root@db1 ~]# mysql -uroot -p123456 -e "select user from mysql.user"
+-----------+
| user      |
+-----------+
| admin     |
| repluser  |
| mysql.sys |
| root      |
+-----------+
[root@db1 ~]# mysql -uroot -p123456 -e 'show grants for plj@"%"'
+------------------------------------------+
| Grants for admin@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' |
+------------------------------------------+
[root@db1 ~]# ifconfig  ens33:1 192.168.1.100
[root@db1 ~]# ip a

2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:36:9f:60 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.11/24 brd 192.168.1.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.1.100/24 brd 192.168.1.255 scope global secondary ens33:1

管理主机测试配置
1）测试ssh免密登录配置、测试主从同步配置

[root@admin ~]# masterha_check_ssh  --conf=/etc/mha/app1.cnf
Fri Mar  5 11:58:53 2022 - [info] All SSH connection tests passed successfully.
[root@admin ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is  OK!
masterha_check_ssh 			检查MHA的SSH配置状况 
masterha_check_repl			检查MySQL复制状况 
masterha_manager 			启动MHA 
masterha_check_status 		检测MHA运行状态 
masterha_stop				停止MHA

启动管理服务

[root@admin ~]# nohup masterha_manager  --conf=/etc/mha/app1.cnf  --remove_dead_master_conf  --ignore_last_failover  2> /dev/null & 	#挂在后台运行

[root@admin ~]# masterha_check_status --conf=/etc/mha/app1.cnf 			#查看服务状态 
app1 (pid:29769) is running(0:PING_OK), master:192.168.1.11	#主为1.11
[root@admin ~]# masterha_stop  --conf=/etc/mha/app1.cnf 				#停止服务的命令 

测试高可用集群

模拟主服务器db1故障

[root@client ~]# mysql -h192.168.1.100 -uadmin -p123qqq...A  -e 'create database testdb'
[root@client ~]# mysql -h192.168.1.100 -uadmin -p123qqq...A  -e 'create table  testdb.name(id int,name char(10))'
[root@client ~]# mysql -h192.168.1.100 -uadmin -p123qqq...A  -e 'insert into testdb.name  values(1,"tom")'
[root@client ~]# mysql -h192.168.1.100 -uadmin -p123qqq...A  -e 'select * from testdb.name'
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
+------+------+

[root@db1 ~]# systemctl stop mysqld
触发故障脚本执行，执行后自动停止， 漂移vip到对db2-3.删除app1.cnf里的[server1],自动切换master

[root@admin ~]# fg
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover 2> /dev/null
[root@admin ~]#		#自动停掉
[root@admin ~]# grep server /etc/mha/app1.cnf	#配置server1删除了
[server default]
[server2]
[server3]

[root@db3 ~]# mysql -uroot -p123456 -e 'show slave status\G'
                  Master_Host: 192.168.1.12		#master切换
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: db2.000004

[root@admin ~]# nohup masterha_manager  --conf=/etc/mha/app1.cnf  --remove_dead_master_conf  --ignore_last_failover  2> /dev/null &
[1] 14568
[root@admin ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:14568) is running(0:PING_OK), master:192.168.1.12			#master为1.12

把宕机的数据库服务器再添加到MHA集群里

配置数据库服务器
启动mysql服务，与主服务器数据一致，指定主服务器信息，启动slave进程，查看状态信息

管理主机重新加入server1 检测主从同步、ssh，重启管理服务，查看服务状态

mysql 锁表备份 （待测试）

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| db2.000004 |   468687 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+

mysql> exit
Bye
[root@db2 ~]#  mysqldump -uroot -p123456 -A -B --events --master-data 2  >/opt/rep.sql
[root@db2 ~]# scp /opt/rep.sql root@192.168.1.11:/root/
root@192.168.1.11's password:
rep.sql                                                                                100%  203   387.5KB/s   00:00

新数据不再写入，也查询不到

[root@db3 ~]# mysql -uroot -p123456
mysql> select  *  from testdb.name   where name ="jerry" order by id  desc limit 10 ;
+------+-------+
| id   | name  |
+------+-------+
| 281 | jerry |				

[root@db2 ~]# mysql -uroot -p123456
mysql>  unlock tables;

[root@db3 ~]# mysql -uroot -p123456
mysql> select  *  from testdb.name   where name ="jerry"  ;
|  280 | jerry |
|  281 | jerry |
|  282 | jerry |
|  283 | jerry |
|  284 | jerry |
....