二十一、搭建MHA高可用集群。

搭建MHA高可用集群。
一、MHA介绍
1.MHA 简介
• MHA （ Master High Availability ） – 由日本 DeNA 公司 youshimaton （现就职于 Facebook 公司）开发
– 是一套优秀的作为 MySQL 高可用性环境下故障切换和主从提升的高可用软件。
– 目前在 MySQL 高可用方面是一个相对成熟的解决方案。
– 在 MySQL 故障切换过程中， MHA 能做到在 0~30 秒之内自动完成数据库的故障切换操作
– 并且在进行故障切换的过程中， MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用
2.MHA 组成
• MHA Manager （管理节点）
– 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上。
• MHA Node （数据节点）
– 运行在每台 MySQL 服务器上。
3.MHA 集群架构

4.MHA 工作过程

• MHA Manager 会定时探测集群中的 master 节点，当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master ，然后将所有其他的 slave重新指向新的 master 。整个故障转移过程对应用程序完全透明。
– （ 1 ）从宕机崩溃的 master 保存二进制日志事件（ binlog events ）
– （ 2 ）识别含有最新更新的 slave
– （ 3 ）应用差异的中继日志（ relay log ）到其他的 slave
– （ 4 ）应用从 master 保存的二进制日志事件（ binlog events ）
– （ 5 ）提升一个 slave 为新的 master ；
– （ 6 ）使其他的 slave 连接新的 master 进行复制；

二、集群拓扑结构
1.拓扑结构
master10
|
|
___________________________________________________________
| | | | |
master11 master12 slave13 slave14 mgm15
备用主备用主 Manager

2.ip规划

三、准备集群环境

1.在每一台数据库服务器创建密钥对，然后把公钥拷贝给其他4台数据库服务器
这儿我们以master1（192.168.4.10）为例：
[root@master1 ~]# ssh-keygen
[root@master1 ~]# for i in 192.168.4.{10..15}; do ssh-copy-id root@$i; done
注：其余5台按照上述步骤操作。
2.安装软件包
2.1在所有主机上安装Perl依赖包。
[root@master1 mysql]# unzip mha-soft-student.zip
[root@master1 ~]# cd soft/mysql/mha-soft-student/
[root@master1 mha-soft-student]# ls perl-*.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-Mail-Sendmail-0.79-21.el7.art.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@master1 ~]# yum -y install perl-*.rpm
2.2在所有节点上授权监控用户
mysql> grant all on *.* to 'root'@'%' identified by '123456'；
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.3在所有数据节点主机上安装mha_node软件包
[root@master1 ~]# yum -y install perl-DBD-mysql perl-DBI
[root@master1 ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
2.4在管理主机上安装mha_node包和mha_manager包
[root@mha]# yum -y install perl-DBD-mysql perl-DBI
[root@mha]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha ~]# yum -y install perl-ExtUtils-* perl-CPAN-*
[root@mha ~]# cd /root/soft/mysql
[root@mha mysql]# tar -zxf mha4mysql-manager-0.56.tar.gz
[root@mha mysql]# cd mha4mysql-manager-0.56
[root@mha 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@mha mha4mysql-manager-0.56]# make && make install

2.5相关命令
安装 manager 软件包后产生的命令

四、配置MHA集群
1. 配置主从同步要求如下：
10 主库开半同步复制
11 从库（备用主库）开半同步复制
12 从库（备用主库）开半同步复制
13 从库不做备用主库所以不用开半同步复制
14 从库不做备用主库所以不用开半同步复制
15 管理主机
2.1 master1(192.168.4.10)配置：
[root@master1 ~]# 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 = 1
rpl-semi-sync-slave-enabled = 1
server_id=10
log-bin=master10
binlog-format="mixed"
validate_password_policy=0
validate_password_length=6
[root@master1 ~]# systemctl restart mysqld
[root@master1 ~]# ls /var/lib/mysql/master10.*
/var/lib/mysql/master10.000001 /var/lib/mysql/master10.index
[root@master1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"%" identified by "123456";
Query OK, 0 rows affected, 1 warning (10.02 sec)
//不自动删除本机的中继日志文件
mysql> set global relay_log_purge=off;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master10.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> quit;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.2 备用master2(192.168.4.11)的配置
[root@master2 ~]# 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 = 1
rpl-semi-sync-slave-enabled = 1
server_id=11
log-bin=master11
binlog-format="mixed"
validate_password_policy=0
validate_password_length=6
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# ls /var/lib/mysql/master11.*
/var/lib/mysql/master11.000001 /var/lib/mysql/master11.index
[root@master2 ~]# mysql -uroot -p123456
mysql> set global relay_log_purge=off; //不自动删除本机的中继日志文件
Query OK, 0 rows affected (0.00 sec)
mysql> change master to \
-> master_host="192.168.4.10",
-> master_user="repluser",
-> master_password="123456",
-> master_log_file="master10.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.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master10.000001
Read_Master_Log_Pos: 441
Relay_Log_File: master2-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master10.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
与上面命令类似
[root@master2 ~]# 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
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.3 备用master3（192.168.4.12）的配置
[root@master3 ~]# 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 = 1
rpl-semi-sync-slave-enabled = 1
server_id=12
log-bin=master12
binlog-format="mixed"
validate_password_policy=0
validate_password_length=6
:wq
[root@master3 ~]# systemctl restart mysqld
[root@master3 ~]# ls /var/lib/mysql/master12*
/var/lib/mysql/master12.000001 /var/lib/mysql/master12.index
mysql>set global relay_log_purge=off;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host="192.168.4.10",master_user="repluser",master_password="123456",master_log_file="master10.00001",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.4.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master10.000001
Read_Master_Log_Pos: 441
Relay_Log_File: master3-relay-bin.000002
Relay_Log_Pos: 652
Relay_Master_Log_File: master10.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@master3 ~]# 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
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.4 配置从服务器slave1（192.168.4.13）
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server_id=13
validate_password_policy=0
validate_password_length=6
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -uroot -p123456
mysql> change master to master_host="192.168.4.10",master_user='repluser',master_password="123456",master_log_file="master10.000001",master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.01 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.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master10.000001
Read_Master_Log_Pos: 441
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master10.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@slave1 ~]# 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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2.5 配置从服务器slave2（192.168.4.14）
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server_id=14
validate_password_policy=0
validate_password_length=6
[root@slave2 ~]# systemctl restart mysqld
[root@slave2 ~]# mysql -uroot -p123456
mysql> change master to master_host="192.168.4.10",master_user='repluser',master_password="123456",master_log_file="master10.000001",master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.01 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.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master10.000001
Read_Master_Log_Pos: 441
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master10.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@slave1 ~]# 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
--------------------------------------------------------------------------------------------------------------------------------------------------------
3. 在客户端测试主从同步配置
3.1 在主库上添加访问数据的授权用户
[root@master1 ~]# mysql -uroot -p123456
mysql> grant all on *.* to admin@"%" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database gamedb;
Query OK, 1 row affected (0.00 sec)
mysql> create table gamedb.t1 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into gamedb.t1 values(999);
Query OK, 1 row affected (0.02 sec)
mysql> insert into gamedb.t1 values(999);
Query OK, 1 row affected (0.02 sec)
mysql> use gamedb
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 * from t1;
+------+
| id |
+------+
| 999 |
| 999 |
+------+
2 rows in set (0.00 sec)
3.2 在客户端使用授权用户连接从库52-55,也能看到同样的库表及记录
mysql> select * from gamedb.t1;
+------+
| id |
+------+
| 999 |
| 999 |
+------+

五、编辑管理主机主配置文件
[root@mha ~]# cd soft/mysql/mha-soft-student/mha4mysql-manager-0.56/bin/
[root@mha bin]# ls
masterha_check_repl masterha_check_status masterha_manager masterha_master_switch masterha_stop
masterha_check_ssh masterha_conf_host masterha_master_monitor masterha_secondary_check
[root@mha bin]# cp ./* /usr/local/bin/
root@mha bin]# mkdir /etc/mha_manager
[root@mha mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager/
[root@mha mha4mysql-manager-0.56]# cd /etc/mha_manager
[root@mha mha_manager]# vim app1.cnf
[server default]
manager_workdir=/etc/mha_manager
manager_log=/etc/mha_manager/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover

ssh_user=root
ssh_port=22

repl_user=repluser
repl_password=123456

user=root
password=123456

[server1]
hostname=192.168.4.10
port=3306

[server2]
hostname=192.168.4.11
port=3306
candidate_master=1

[server3]
hostname=192.168.4.12
port=3306
candidate_master=1

[server4]
hostname=192.168.4.13
port=3306
no_master=1

[server5]
hostname=192.168.4.14
port=3306
no_master=1

[root@mha mha_manager]# masterha_check_ssh --conf /etc/mha_manager/app1.cnf
Mon Jun 28 03:01:45 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 28 03:01:45 2021 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Mon Jun 28 03:01:45 2021 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
Mon Jun 28 03:01:45 2021 - [info] Starting SSH connection tests..
Mon Jun 28 03:01:46 2021 - [debug]
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.10(192.168.4.10:22) to root@192.168.4.11(192.168.4.11:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.10(192.168.4.10:22) to root@192.168.4.12(192.168.4.12:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.10(192.168.4.10:22) to root@192.168.4.13(192.168.4.13:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.10(192.168.4.10:22) to root@192.168.4.14(192.168.4.14:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug]
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.11(192.168.4.11:22) to root@192.168.4.10(192.168.4.10:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.11(192.168.4.11:22) to root@192.168.4.12(192.168.4.12:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.11(192.168.4.11:22) to root@192.168.4.13(192.168.4.13:22)..
Mon Jun 28 03:01:45 2021 - [debug] ok.
Mon Jun 28 03:01:45 2021 - [debug] Connecting via SSH from root@192.168.4.11(192.168.4.11:22) to root@192.168.4.14(192.168.4.14:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:47 2021 - [debug]
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.12(192.168.4.12:22) to root@192.168.4.10(192.168.4.10:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.12(192.168.4.12:22) to root@192.168.4.11(192.168.4.11:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.12(192.168.4.12:22) to root@192.168.4.13(192.168.4.13:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.12(192.168.4.12:22) to root@192.168.4.14(192.168.4.14:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:47 2021 - [debug]
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.13(192.168.4.13:22) to root@192.168.4.10(192.168.4.10:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.13(192.168.4.13:22) to root@192.168.4.11(192.168.4.11:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.13(192.168.4.13:22) to root@192.168.4.12(192.168.4.12:22)..
Mon Jun 28 03:01:46 2021 - [debug] ok.
Mon Jun 28 03:01:46 2021 - [debug] Connecting via SSH from root@192.168.4.13(192.168.4.13:22) to root@192.168.4.14(192.168.4.14:22)..
Mon Jun 28 03:01:47 2021 - [debug] ok.
Mon Jun 28 03:01:48 2021 - [debug]
Mon Jun 28 03:01:47 2021 - [debug] Connecting via SSH from root@192.168.4.14(192.168.4.14:22) to root@192.168.4.10(192.168.4.10:22)..
Mon Jun 28 03:01:47 2021 - [debug] ok.
Mon Jun 28 03:01:47 2021 - [debug] Connecting via SSH from root@192.168.4.14(192.168.4.14:22) to root@192.168.4.11(192.168.4.11:22)..
Mon Jun 28 03:01:47 2021 - [debug] ok.
Mon Jun 28 03:01:47 2021 - [debug] Connecting via SSH from root@192.168.4.14(192.168.4.14:22) to root@192.168.4.12(192.168.4.12:22)..
Mon Jun 28 03:01:47 2021 - [debug] ok.
Mon Jun 28 03:01:47 2021 - [debug] Connecting via SSH from root@192.168.4.14(192.168.4.14:22) to root@192.168.4.13(192.168.4.13:22)..
Mon Jun 28 03:01:48 2021 - [debug] ok.
Mon Jun 28 03:01:48 2021 - [info] All SSH connection tests passed successfully.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

**检查主从同步时把app1.cnf文件中的此配置项#master_ip_failover_script=/usr/local/bin/master_ip_failover注释掉，不然检查失败。

[root@mha mhasoft]# masterha_check_repl --conf /etc/mha_manager/app1.cnf
Mon Jun 28 07:02:46 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 28 07:02:46 2021 - [info] Reading application default configuration from /etc/mha_manager/app1.cnf..
Mon Jun 28 07:02:46 2021 - [info] Reading server configuration from /etc/mha_manager/app1.cnf..
Mon Jun 28 07:02:46 2021 - [info] MHA::MasterMonitor version 0.56.
Mon Jun 28 07:02:47 2021 - [info] GTID failover mode = 0
Mon Jun 28 07:02:47 2021 - [info] Dead Servers:
Mon Jun 28 07:02:47 2021 - [info] Alive Servers:
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.10(192.168.4.10:3306)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.11(192.168.4.11:3306)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.12(192.168.4.12:3306)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.13(192.168.4.13:3306)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.14(192.168.4.14:3306)
Mon Jun 28 07:02:47 2021 - [info] Alive Slaves:
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.11(192.168.4.11:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Mon Jun 28 07:02:47 2021 - [info] Replicating from 192.168.4.10(192.168.4.10:3306)
Mon Jun 28 07:02:47 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.12(192.168.4.12:3306) Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Mon Jun 28 07:02:47 2021 - [info] Replicating from 192.168.4.10(192.168.4.10:3306)
Mon Jun 28 07:02:47 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.13(192.168.4.13:3306) Version=5.7.17 (oldest major version between slaves) log-bin:disabled
Mon Jun 28 07:02:47 2021 - [info] Replicating from 192.168.4.10(192.168.4.10:3306)
Mon Jun 28 07:02:47 2021 - [info] Not candidate for the new Master (no_master is set)
Mon Jun 28 07:02:47 2021 - [info] 192.168.4.14(192.168.4.14:3306) Version=5.7.17 (oldest major version between slaves) log-bin:disabled
Mon Jun 28 07:02:47 2021 - [info] Replicating from 192.168.4.10(192.168.4.10:3306)
Mon Jun 28 07:02:47 2021 - [info] Not candidate for the new Master (no_master is set)
Mon Jun 28 07:02:47 2021 - [info] Current Alive Master: 192.168.4.10(192.168.4.10:3306)
Mon Jun 28 07:02:47 2021 - [info] Checking slave configurations..
Mon Jun 28 07:02:47 2021 - [info] read_only=1 is not set on slave 192.168.4.11(192.168.4.11:3306).
Mon Jun 28 07:02:47 2021 - [info] read_only=1 is not set on slave 192.168.4.12(192.168.4.12:3306).
Mon Jun 28 07:02:47 2021 - [info] read_only=1 is not set on slave 192.168.4.13(192.168.4.13:3306).
Mon Jun 28 07:02:47 2021 - [warning] relay_log_purge=0 is not set on slave 192.168.4.13(192.168.4.13:3306).
Mon Jun 28 07:02:47 2021 - [warning] log-bin is not set on slave 192.168.4.13(192.168.4.13:3306). This host cannot be a master.
Mon Jun 28 07:02:47 2021 - [info] read_only=1 is not set on slave 192.168.4.14(192.168.4.14:3306).
Mon Jun 28 07:02:47 2021 - [warning] relay_log_purge=0 is not set on slave 192.168.4.14(192.168.4.14:3306).
Mon Jun 28 07:02:47 2021 - [warning] log-bin is not set on slave 192.168.4.14(192.168.4.14:3306). This host cannot be a master.
Mon Jun 28 07:02:47 2021 - [info] Checking replication filtering settings..
Mon Jun 28 07:02:47 2021 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jun 28 07:02:47 2021 - [info] Replication filtering check ok.
Mon Jun 28 07:02:48 2021 - [info] GTID (with auto-pos) is not supported
Mon Jun 28 07:02:48 2021 - [info] Starting SSH connection tests..
Mon Jun 28 07:02:55 2021 - [info] All SSH connection tests passed successfully.
Mon Jun 28 07:02:55 2021 - [info] Checking MHA Node version..
Mon Jun 28 07:02:57 2021 - [info] Version check ok.
Mon Jun 28 07:02:57 2021 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun 28 07:02:57 2021 - [info] HealthCheck: SSH to 192.168.4.10 is reachable.
Mon Jun 28 07:02:58 2021 - [info] Master MHA Node version is 0.56.
Mon Jun 28 07:02:58 2021 - [info] Checking recovery script configurations on 192.168.4.10(192.168.4.10:3306)..
Mon Jun 28 07:02:58 2021 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=master51.000001
Mon Jun 28 07:02:58 2021 - [info] Connecting to root@192.168.4.10(192.168.4.10:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to master51.000001
Mon Jun 28 07:02:58 2021 - [info] Binlog setting check done.
Mon Jun 28 07:02:58 2021 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Jun 28 07:02:58 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.4.11 --slave_ip=192.168.4.11 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.17-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Mon Jun 28 07:02:58 2021 - [info] Connecting to root@192.168.4.11(192.168.4.11:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to db108-relay-bin.000003
Temporary relay log file is /var/lib/mysql/db108-relay-bin.000003
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Jun 28 07:02:59 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.4.12 --slave_ip=192.168.4.12 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.17-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Mon Jun 28 07:02:59 2021 - [info] Connecting to root@192.168.4.12(192.168.4.12:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to db109-relay-bin.000002
Temporary relay log file is /var/lib/mysql/db109-relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Jun 28 07:03:00 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.4.13 --slave_ip=192.168.4.13 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.17 --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Mon Jun 28 07:03:00 2021 - [info] Connecting to root@192.168.4.13(192.168.4.13:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to db111-relay-bin.000002
Temporary relay log file is /var/lib/mysql/db111-relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Jun 28 07:03:00 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.4.14 --slave_ip=192.168.4.14 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.17 --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Mon Jun 28 07:03:00 2021 - [info] Connecting to root@192.168.4.14(192.168.4.14:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to db113-relay-bin.000002
Temporary relay log file is /var/lib/mysql/db113-relay-bin.000002
Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Jun 28 07:03:01 2021 - [info] Slaves settings check done.
Mon Jun 28 07:03:01 2021 - [info]
192.168.4.10(192.168.4.10:3306) (current master)
+--192.168.4.11(192.168.4.11:3306)
+--192.168.4.12(192.168.4.12:3306)
+--192.168.4.13(192.168.4.13:3306)
+--192.168.4.14(192.168.4.14:3306)

Mon Jun 28 07:03:01 2021 - [info] Checking replication health on 192.168.4.11..
Mon Jun 28 07:03:01 2021 - [info] ok.
Mon Jun 28 07:03:01 2021 - [info] Checking replication health on 192.168.4.12..
Mon Jun 28 07:03:01 2021 - [info] ok.
Mon Jun 28 07:03:01 2021 - [info] Checking replication health on 192.168.4.13..
Mon Jun 28 07:03:01 2021 - [info] ok.
Mon Jun 28 07:03:01 2021 - [info] Checking replication health on 192.168.4.14..
Mon Jun 28 07:03:01 2021 - [info] ok.
Mon Jun 28 07:03:01 2021 - [warning] master_ip_failover_script is not defined.
Mon Jun 28 07:03:01 2021 - [warning] shutdown_script is not defined.
Mon Jun 28 07:03:01 2021 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

----------------------------------------------------------------------------------------------------------------------------------------------------------

手动配置 vip 地址(10)
ifconfig eth0:1 192.168.4.100/24 // ifconfig ethX:1 x.x.x.x/32
ifconfig eth0:1 //查看
启动管理服务，并查看服务状态
masterha_manager //启动命令 --remove_dead_master_conf //在app1.cnf 文件里删除宕机的主库的信息（不加故障重启会出错）
--ignore_last_failover //忽略 .health 健康文件（不加则在8小时内连续出现故障就不切换主库）
masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover //启动mha服务
[root@host56 bin]# masterha_check_status --conf=/etc/mha_manager/app1.cnf //检查 mha 服务状态
app1 (pid:19513) is running(0:PING_OK), master:192.168.4.10
[root@host56 bin]# masterha_stop --conf=/etc/mha/app1.cnf //停止 mha 服务
测试高可用集群配置
在数据库服务器上添加访问数据时连接用户 webuser 123qqq...A
mysql> create database db13;
Query OK, 1 row affected (0.04 sec)
mysql> grant all on db13.* to webuser@"%" identified by "123qqq...A";
4.1 客户端连接VIP地址访问数据库
]# mysql -h192.168.4.100 -uwebuser -p123qqq...A
测试高用集群
最低：切换后 1主库，1备用主库否则会失效不会切换主库
把主机51上的数据库服务停止
把宕机的数据库服务器51 在添加到当前集群里
mysql> change master to master_host="192.168.4.11", master_user="repluser",master_password="123456",
master_log_file="master52.000001",master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.30 sec)
mysql> start slave;
mha服务器操作
]# vim /etc/mha_manager/app1.cnf
[server1]
candidate_master=1
hostname=192.168.4.10
]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
MySQL Replication Health is OK.
]#masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover
[root@mgm56 ~]# masterha_check_status --conf=/etc/mha_manager/app1.cnf
app1 (pid:8710) is running(0:PING_OK), master:192.168.4.51

posted @ 2021-06-25 11:39 落樰兂痕阅读(119) 评论(0) 收藏举报

刷新页面返回顶部

落樰兂痕

二十一、搭建MHA高可用集群。

公告