MHA集群概述
MHA介绍
- MHA（Master High Availability）
- 由日本DeNA公司youshimaton开发
- 是一套优秀的实现MySQL高可用的解决方案
- 数据库的自动解决故障切换操作能做到在0~30秒之内完成
- MHA能确保在故障切换过程中最大限度的保证数据的一致性，以达到真正意义上的高可用
- 是一个用Perl脚本语言写的开源软件
集群定义：用多台服务器提供相同的服务
集群的类型：LB（负载均衡集群，多台服务器平均分担客户请求） HA（高可用集群，提供热备） HPC（应用于专业领域）
集群软件：LVS HaProxy Nginx Keepalived（高可用）
Keepalived可以对所有服务进行高可用，MHA只对MySQL高可用，性能较高
MHA组成
- MHA Manager （管理节点）
- 管理所有数据库服务器
- 可以单独部署在一台独立的机器上
- 也可以部署在某台数据库服务器上
- MHA Node （数据节点）
- 存储数据的MySQL服务器
- 运行在每台MySQL服务器上
MHA工作过程
由Manager定时探测集群中的master节点，当master故障时，Manager自动将拥有最新数据的slave提升为新的master
部署MHA集群
环境准备
准备5台主机，三台用作数据库服务器，1台管理主机，1台客户机，设置一个VIP地址，用作地址漂移，要求每台主机都要有mha软件
软件包链接提取码：wvq4
- 拓扑图
一、环境准备：
1.将每个数据库还原成初始化状态：
[root@host51 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> delete from mysql.user where user not in ("root" ,"mysql.sys");
Query OK, 8 rows affected (0.11 sec)
mysql> select user ,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> show slave status;
Empty set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@host51 mysql]# ls
auto.cnf client-key.pem ib_logfile1 mysql.sock.lock server-cert.pem
ca-key.pem ib_buffer_pool ibtmp1 performance_schema server-key.pem
ca.pem ibdata1 mysql private_key.pem sys
client-cert.pem ib_logfile0 mysql.sock public_key.pem
2.在每台主机上面传mha软件
[root@host51 ~]# ls
mha
[root@host51 ~]# ls mha/
app1.cnf
master_ip_failover
mha4mysql-manager-0.56.tar.gz
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@host51 ~]#for i in 52 53 57
>do
>scp mha root@192.168.4.$i:/root
>done
二、部署集群环境准备：
1.在51、52、53、57主机上安装依赖的perl包
[root@host51 ~]# cd mha/
[root@host51 mha]# yum -y install perl-*.rpm
[root@host52 ~]# cd mha/
[root@host52 mha]# yum -y install perl-*.rpm
[root@host53 ~]# cd mha/
[root@host53 mha]# yum -y install perl-*.rpm
[root@host57 ~]# cd mha/
[root@host57 mha]# yum -y install perl-*.rpm
[root@host51 mha]# which yum #yum命令
/usr/bin/yum
[root@host51 mha]# rpm -q yum
yum-3.4.3-158.el7.centos.noarch
[root@host51 mha]# rpm -qf /usr/bin/yum
yum-3.4.3-158.el7.centos.noarch
2.配置ssh秘钥对认证登录
三台数据库彼此之间可以ssh免密登录
让51主机无密码登录52主机和53主机
[root@host51 mha]# ssh-keygen #一路回车
[root@host51 mha]# ls /root/.ssh
authorized_keys id_rsa id_rsa.pub known_hosts
[root@host51 mha]# for i in 52 53
> do
> ssh-copy-id root@192.168.4.$i
> done
同理，让52与53主机无密码登录其他两台主机
[root@host52 mha]# ssh-keygen
[root@host52 mha]# ls /root/.ssh
authorized_keys id_rsa id_rsa.pub known_hosts
[root@host52 mha]# for i in 51 53
> do
> ssh-copy-id root@192.168.4.$i
> done
[root@host53 mha]# ssh-keygen
[root@host53 mha]# ls /root/.ssh
authorized_keys id_rsa id_rsa.pub known_hosts
[root@host53 mha]# for i in 51 52
> do
> ssh-copy-id root@192.168.4.$i
> done
测试：
分别ssh 登录测试是否需要登录密码
（如果执行传输公钥的时候报错可以执行rm -rf /root/.ssh/known_hosts,删除曾经传输的记录，再重新ssh-copy-id即可）
管理主机57可以ssh免密登录三台数据库服务器
[root@host57 mha]# ssh-keygen
[root@host57 mha]# ls /root/.ssh/
id_rsa id_rsa.pub
[root@host57 mha]# for i in 51 52 53
> do
> ssh-copy-id root@192.168.4.$i
> done
二、配置mysql一主多从同步结构
配置主服务器51
[root@host51 mha]# vim /etc/my.cnf
4 [mysqld]
5 server_id=51
6 log_bin=master51
[root@host51 mha]# systemctl stop firewalld
[root@host51 mha]# systemctl enable firewalld
[root@host51 mha]# systemctl restart mysqld
[root@host51 mha]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从服务器52，查看从服务器的状态信息
[root@host52 mha]# vim /etc/my.cnf
4 [mysqld]
5 server_id=52
[root@host52 mha]# systemctl stop firewalld
[root@host52 mha]# systemctl enable firewalld
[root@host52 mha]# systemctl restart mysqld
[root@host52 mha]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.51" ,
-> master_user="repluser" ,
-> master_password="123qqq...A" ,
-> master_log_file="master51.000001" ,
-> master_log_pos=441 ;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
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.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 441
Relay_Log_File: host52-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 441
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
Master_UUID: 6d7b632c-4e3f-11ea-8a8d-000c29875030
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
配置从服务器53，查看从服务器的状态信息
[root@host53 mha]# vim /etc/my.cnf
4 [mysqld]
5 server_id=53
[root@host53 mha]# systemctl stop firewalld
[root@host53 mha]# systemctl enable firewalld
[root@host53 mha]# systemctl restart mysqld
[root@host53 mha]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.51" ,
-> master_user="repluser" ,
-> master_password="123qqq...A" ,
-> master_log_file="master51.000001" ,
-> master_log_pos=441 ;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
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.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 441
Relay_Log_File: host53-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 441
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 51
Master_UUID: 6d7b632c-4e3f-11ea-8a8d-000c29875030
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
部署MHA集群
一、配置管理节点
1.安装软件
[root@host57 mha]# ls
app1.cnf
master_ip_failover
mha4mysql-manager-0.56.tar.gz
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@host57 mha]# rm -rf perl-*.rpm
[root@host57 mha]# ls
app1.cnf mha4mysql-manager-0.56.tar.gz
master_ip_failover mha4mysql-node-0.56-0.el6.noarch.rpm
[root@host57 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm #安装mha-node软件
[root@host57 mha]# tar -xf mha4mysql-manager-0.56.tar.gz #解压mha-manager软件包
[root@host57 mha]# ls
app1.cnf mha4mysql-manager-0.56 mha4mysql-node-0.56-0.el6.noarch.rpm
master_ip_failover mha4mysql-manager-0.56.tar.gz
[root@host57 mha]# cd mha4mysql-manager-0.56/ #进入源码目录
[root@host57 mha4mysql-manager-0.56]# ls #查看文件列表
AUTHORS COPYING inc Makefile.PL META.yml rpm t
bin debian lib MANIFEST README samples tests
[root@host57 mha4mysql-manager-0.56]# perl Makefile.PL #执行Perl命令，报错没有ExtUtils
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.
BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4.
Compilation failed in require at inc/Module/Install.pm line 283.
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Can.pm line 6.
BEGIN failed--compilation aborted at inc/Module/Install/Can.pm line 6.
Compilation failed in require at inc/Module/Install.pm line 283.
Can't locate ExtUtils/MM_Unix.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Metadata.pm line 349.
[root@host57 mha4mysql-manager-0.56]# yum list | grep ExtUtils
perl-ExtUtils-CBuilder.noarch 1:0.28.2.6-292.el7 centos
perl-ExtUtils-Embed.noarch 1.30-292.el7 centos
perl-ExtUtils-Install.noarch 1.58-292.el7 centos
perl-ExtUtils-MakeMaker.noarch 6.68-3.el7 centos
perl-ExtUtils-Manifest.noarch 1.61-244.el7 centos
perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7 centos
[root@host57 mha4mysql-manager-0.56]# yum -y install perl-ExtUtils*
[root@host57 mha4mysql-manager-0.56]# perl Makefile.PL #执行Perl命令，报错没有CPAN
*** Module::AutoInstall version 1.03
*** Checking for Perl dependencies...
Can't locate CPAN.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/AutoInstall.pm line 277.
[root@host57 mha4mysql-manager-0.56]# yum list | grep CPAN
perl-CPAN.noarch 1.9800-292.el7 centos
perl-CPAN-Changes.noarch 0.20-2.el7 centos
perl-CPAN-Meta.noarch 2.120921-5.el7 centos
perl-CPAN-Meta-Requirements.noarch 2.122-7.el7 centos
perl-CPAN-Meta-YAML.noarch 0.008-14.el7 centos
perl-CPANPLUS.noarch 0.91.38-4.el7 centos
perl-CPANPLUS-Dist-Build.noarch 0.70-3.el7 centos
perl-Parse-CPAN-Meta.noarch 1:1.4404-5.el7 centos
perl-Test-CPAN-Meta.noarch 0.23-2.el7 centos
[root@host57 mha4mysql-manager-0.56]# yum -y install perl-CPAN*
[root@host57 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@host57 mha4mysql-manager-0.56]# make #编译
[root@host57 mha4mysql-manager-0.56]# make install #安装
[root@host57 mha4mysql-manager-0.56]# masterha_ [Tab][Tab] #查看安装的可执行的命令
masterha_check_repl masterha_conf_host masterha_master_switch
masterha_check_ssh masterha_manager masterha_secondary_check
masterha_check_status masterha_master_monitor masterha_stop
2.创建并编辑主配置文件
[root@host57 mha4mysql-manager-0.56]# mkdir /etc/mha #创建工作目录
[root@host57 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha/ #拷贝模板文件
[root@host57 mha4mysql-manager-0.56]# vim /etc/mha/app1.cnf #编辑主配置文件
1 [server default] #管理服务器默认配置
2 manager_workdir=/etc/mha #工作目录
3 manager_log=/etc/mha/manager.log #日志文件
4 master_ip_failover_script=/etc/mha/master_ip_failover #故障切换脚本
5
6 ssh_user=root #访问ssh服务用户
7 ssh_port=22 #ssh服务端口
8
9 repl_user=repluser #主服务器数据同步授权用户
10 repl_password=123qqq...A #密码
11
12 user=root #监控用户
13 password=123qqq...A #密码
14
15 [server1] #指定第一台数据库服务器
16 hostname=192.168.4.51 #服务器IP地址
17 port=3306 #服务端口
18 candidate_master=1 #参选竞选主服务器
19
20 [server2] #指定第二台数据库服务器
21 hostname=192.168.4.52 #服务器IP地址
22 port=3306 #服务端口
23 candidate_master=1 #参选竞选主服务器
24
25 [server3] #指定第三台数据库服务器
26 hostname=192.168.4.53 #服务器IP地址
27 port=3306 #服务端口
28 candidate_master=1 #参与主服务器竞选
3.创建并编辑故障切换脚本(当57监视到数据库服务器)
[root@host57 mha4mysql-manager-0.56]# ls samples/scripts/master_ip_failover
samples/scripts/master_ip_failover
[root@host57 mha4mysql-manager-0.56]# cp samples/scripts/master_ip_failover /etc/mha
[root@host57 mha4mysql-manager-0.56]# chmod +x /etc/mha/master_ip_failover #给脚本加权限
[root@host57 mha4mysql-manager-0.56]# vim +33 /etc/mha/master_ip_failover
33 );
34 my $vip = '192.168.4.100/24' ; #定义VIP地址
35 my $key = "1" ; #定义变量$key
36 my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip" ; #部署VIP地址命令，实验网卡IP为ens33
37 my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #释放VIP地址命令
[root@host57 mha4mysql-manager-0.56]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.57 netmask 255.255.255.0 broadcast 192.168.4.255
inet6 fe80::bbf7:a019:5cc1:a2b5 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:0a:92:78 txqueuelen 1000 (Ethernet)
RX packets 6172 bytes 1104619 (1.0 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 3988 bytes 741241 (723.8 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
4.把VIP地址配置在当前的主服务器51上
（如果配错了，可以在虚拟机本体上输入ifdown ens33禁用网卡，再重新ifup ens33启用网卡即可）
[root@host51 mha]# ifconfig ens33:1 192.168.4.100/24 #部署VIP地址
[root@host51 mha]# ifconfig ens33
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.51 netmask 255.255.255.0 broadcast 192.168.4.255
inet6 fe80::42bb:9312:f45:4e6a prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:87:50:30 txqueuelen 1000 (Ethernet)
RX packets 4255 bytes 386706 (377.6 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 3030 bytes 388018 (378.9 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@host51 mha]# ifconfig ens33:1
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255
ether 00:0c:29:87:50:30 txqueuelen 1000 (Ethernet)
测试：
在50主机ping
[root@host50 ~]# ping -c 2 192.168.4.100
PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.
64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=0.701 ms
64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.601 ms
二、配置数据节点
1.51/52/53安装mha_node软件包
[root@host51 ~]# cd mha
[root@host51 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@host52 ~]# cd mha
[root@host52 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@host53 ~]# cd mha
[root@host53 mha]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
2.用户授权
监控用户root,可以只在host51上执行授权命令，host52和host53会自动同步授权
[root@host51 mha]# mysql -uroot -p123456
mysql> grant all on *.* to root@"%" identified by "123qqq...A" ;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select user ,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| repluser | % |
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
4 rows in set (0.01 sec)
[root@host52 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
[root@host53 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
在从服务器52和53主机添加数据同步的连接用户repluser
[root@host52 mha]# mysql -uroot -p123456 -e 'grant replication slave on *.* to repluser@"%" identified by "123qqq...A"'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host52 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+-----------+
| user | host |
+-----------+-----------+
| repluser | % |
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
[root@host53 mha]# mysql -uroot -p123456 -e 'grant replication slave on *.* to repluser@"%" identified by "123qqq...A"'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host53 mha]# mysql -uroot -p123456 -e'select user ,host from mysql.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+-----------+
| user | host |
+-----------+-----------+
| repluser | % |
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
3.优化配置
在51/52/53数据库服务器启用主从角色的半同步复制模式，禁止自动删除中继日志文件
[root@host51 mha]# vim /etc/my.cnf
4 [mysqld]
5 server_id=51
6 log_bin=master51
7 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
8 rpl_semi_sync_master_enabled=1 #启用master模块
9 rpl_semi_sync_slave_enabled=1 #启用slave模块
10 relay_log_purge=0 #禁止删除中继日志文件
在52/53数据库服务器启用binlog日志
[root@host52 mha]# vim /etc/my.cnf
4 [mysqld]
5 server_id=52
6 log_bin=master52
7 plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
8 rpl_semi_sync_master_enabled=1 #启用master模块
9 rpl_semi_sync_slave_enabled=1 #启用slave模块
10 relay_log_purge=0 #禁止删除中继日志文件
[root@host53 ~]# vim /etc/my.cnf
4 [mysqld]
5 server_id=53
6 log_bin=master53
7 plugin-load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
8 rpl_semi_sync_master_enabled=1 #启用master模块
9 rpl_semi_sync_slave_enabled=1 #启用slave模块
10 relay_log_purge=0 #禁止删除中继日志文件
重启51/52/53主机的数据库服务
[root@host51 mha]# systemctl restart mysqld
[root@host52 mha]# systemctl restart mysqld
[root@host53 mha]# systemctl restart mysqld
检查52 53主机从服务器的状态
[root@host52 mha]# mysql -uroot -p123456 -e 'show slave status \G' | grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@host53 mha]# mysql -uroot -p123456 -e 'show slave status \G' | grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
检查配置
一检查集群环境
1.在管理主机，测试ssh配置
[root@host57 mha]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Tue Feb 25 01:12:09 2020 - [info] All SSH connection tests passed successfully.
#debug后面是ok即为成功
2.在管理主机，测试主从同步
[root@host57 mha]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK. #测试成功提示信息
3.启动管理服务
（启动时会占用一个终端，可以在启动之前再开一个终端连接，终端1用来启动服务，终端2用来查看状态）
[root@host57 ~]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover
#即使选项写错了，它的服务也可以照常启动，只是会忽略该选项
4.查看服务状态
[root@host57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4606) is running(0:PING_OK), master:192.168.4.51
#服务运行，监视主服务器192.168.4.51
[root@host57 ~]# ls /etc/mha #查看工作目录下的文件列表
app1.cnf app1.master_status.health manager.log master_ip_failover
测试配置
1.首先测试VIP地址是否依然存在，如果不存在则再次在51主机上添加VIP，这样才可以使该IP为浮动IP
[root@host51 ~]# ifconfig ens33:1 192.168.4.100/24
[root@host51 ~]# ifconfig ens33:1
ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255
ether 00:0c:29:87:50:30 txqueuelen 1000 (Ethernet)
[root@host50 ~]# ping -c 2 192.168.4.100
PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.
64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=0.801 ms
64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.535 ms
2.测试在主服务器上添加授权用户，让客户端可以登录数据库
[root@host51 ~]# mysql -uroot -p123456
mysql> create database gamedb;
Query OK, 1 row affected (0.10 sec)
mysql> create table gamedb.t1(id int);
Query OK, 0 rows affected (0.13 sec)
mysql> grant select ,insert on gamedb.* to tian@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> select user ,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| repluser | % |
| root | % |
| tian | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
5 rows in set (0.00 sec)
客户端50连接VIP访问集群
[root@host50 ~]# mysql -h192.168.4.100 -utian -p123qqq...A
MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| host51 |
+------------+
1 row in set (0.00 sec)
MySQL [(none)]> show grants;
+--------------------------------------------------+
| Grants for tian@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'tian'@'%' |
| GRANT SELECT, INSERT ON `gamedb`.* TO 'tian'@'%' |
+--------------------------------------------------+
2 rows in set (0.00 sec)
MySQL [(none)]> insert into gamedb.t1 values(222);
Query OK, 1 row affected (0.04 sec)
MySQL [(none)]> insert into gamedb.t1 values(555);
Query OK, 1 row affected (0.03 sec)
MySQL [(none)]> insert into gamedb.t1 values(555);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> insert into gamedb.t1 values(555);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> insert into gamedb.t1 values(555);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select * from gamedb.t1;
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
+------+
5 rows in set (0.00 sec)
在51主机上查看：
mysql> select * from gamedb.t1;
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
+------+
5 rows in set (0.01 sec)
在52主机上查看：
[root@host52 ~]# mysql -uroot -p123456 -e 'select * from gamedb.t1'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
+------+
3.测试高可用
```bash
1. 将51主机的mysql服务停止
[root@host51 ~]# systemctl stop mysqld
2.查看管理服务输出的监控信息
[root@host57 mha]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf \
> --ignore_last_failover #此时57管理主机的管理服务会报错，因为51主机已经宕掉，管理服务此时会自动找到执行故障切换脚本
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 17:35:59 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:35:59 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 17:35:59 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
[root@host57 mha]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING). //监控到主服务器宕机 管理服务自动停止
3.客户端依然连接VIP地址，可以访问到数据
[root@host50 ~]# ping -c 2 192.168.4.100 #30秒内ping不通
PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.
64 bytes from 192.168.4.100: icmp_seq=1 ttl=64 time=1.89 ms
64 bytes from 192.168.4.100: icmp_seq=2 ttl=64 time=0.777 ms
[root@host50 ~]# mysql -h192.168.4.100 -uyaya55 -p123qqq...A //连接vip地址
MySQL [(none)]> insert into gamedb.t1 values(222);
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]> insert into gamedb.t1 values(222);
Query OK, 1 row affected (0.02 sec)
MySQL [(none)]> insert into gamedb.t1 values(222);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> select * from gamedb.t1;
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
| 222 |
| 222 |
| 222 |
+------+
MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| host52 |
+------------+
1 row in set (0.01 sec)
4.查看VIP地址
在host52主机查看到VIP地址。说明此时host53主机是host52主机的从服务器
[root@host52 ~]# ifconfig eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.4.100 netmask 255.255.255.0 broadcast 192.168.4.255
ether 52:54:00:f5:c4:6a txqueuelen 1000 (Ethernet)
[root@host53 ~]# ifconfig eth0:1 //未查到vip地址
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
ether 52:54:00:28:22:2e txqueuelen 1000 (Ethernet)
[root@host53 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" | grep -i 192
mysql: [Warning] Using a password on the command line interface can be insecure.
Master_Host: 192.168.4.52 //主服务器Ip地址
[root@host53 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" | grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes //IO线程正常
Slave_SQL_Running: Yes //SQL线程正常
[root@host53 ~]# mysql -uroot -p123qqq...A -e "select * from gamedb.t1" //自动同步数据
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
| 222 |
| 222 |
| 222 |
+------+
5.查看主配置文件
[root@host57 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/manager.log
manager_workdir=/etc/mha
master_ip_failover_script=/etc/mha/master_ip_failover
password=123qqq...A
repl_password=123qqq...A
repl_user=repluser
ssh_port=22
ssh_user=root
user=root
[server2]
candidate_master=1
hostname=192.168.4.52
port=3306
[server3]
candidate_master=1
hostname=192.168.4.53
port=3306
#此时已经没有了server1的信息，因为故障切换脚本会自动将其剔除
修复故障服务器
1.配置数据库服务器
启动51主机数据库服务
[root@host51 mha]# systemctl restart mysqld
此时的51主机数据库内的数据还是在宕机之前的数据
mysql> select * from gamedb.t1;
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
+------+
备份数据，使其与当前主服务器数据一致
[root@host52 ~]# mysqldump -uroot -p123456 --master-data gamedb > /root/gamedb.sql #在主服务器52做完全备份
[root@host52 ~]# grep master52 /root/gamedb.sql #查看日志名以及偏移量
CHANGE MASTER TO MASTER_LOG_FILE='master52.000001', MASTER_LOG_POS=907;
恢复数据
[root@host52 ~]# scp /root/gamedb.sql root@192.168.4.51:/root #拷贝备份文件给51主机
[root@host51 ~]# mysql -uroot -p123456 gamedb < /root/gamedb.sql #51主机使用备份文件恢复数据
[root@host51 ~]# mysql -uroot -p123456 -e 'select * from gamedb.t1'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 222 |
| 555 |
| 555 |
| 555 |
| 555 |
| 222 |
| 222 |
| 222 |
+------+
[root@host51 ~]# grep master52 /root/gamedb.sql
CHANGE MASTER TO MASTER_LOG_FILE='master52.000001', MASTER_LOG_POS=907;
[root@host51 ~]# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.4.52",
-> master_user="repluser",
-> master_password="123qqq...A",
-> master_log_file="master52.000001",
-> master_log_pos=907; #指定主服务器信息
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #启动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.4.52
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master52.000001
Read_Master_Log_Pos: 907
Relay_Log_File: host51-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master52.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 907
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 52
Master_UUID: a2f5308e-4e6a-11ea-9941-000c29e6ec7f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2.配置管理服务器
[root@host57 ~]# vim /etc/mha/app1.cnf #手动添加51主机信息
[server1]
candidate_master=1
hostname=192.168.4.51
port=3306
3.测试集群环境
[root@host57 mha4mysql-manager-0.56]# masterha_check_ssh --conf=/etc/mha/app1.cnf #测试ssh
[info] All SSH connection tests passed successfully.//成功
[root@host57 mha4mysql-manager-0.56]# masterha_check_repl --conf=/etc/mha/app1.cnf #测试主从同步
MySQL Replication Health is OK. //成功
4.重启管理服务
[root@host57 mha4mysql-manager-0.56]# masterha_stop --conf=/etc/mha/app1.cnf //停止管理服务
Stopped app1 successfully.
[root@host57 mha4mysql-manager-0.56]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf \
--ignore_last_failover //启动管理服务
[root@host57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf #查看状态，服务运行，监视服务器52
app1 (pid:14134) is running(0:PING_OK), master:192.168.4.52
总结
- 配置Mysql集群（MHA+Mysql主从同步）
- 环境准备
ssh免密登录
配置MySQL一主二从 - 配置管理主机
安装依赖的Perl软件包
安装mha软件
创建并编辑主配置文件
创建故障切换脚本并指定VIP地址
把VIP地址配置在master主数据库服务器 - 配置数据库服务器
安装mha软件
授权监控用户和主从同步连接用户、启用半同步复制模式、禁止自动删除
在两台从服务器授权同步数据的连接用户 - 测试配置
测试ssh连接
测试主从同步
启动管理服务，查看服务状态（如何停止管理服务）
测试高可用 - 把宕机的主服务器添加到集群
配置数据库服务器
配置管理主机
531
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
