MySQL - MHA架构搭建过程记录

MySQL集群架构之MHA高可用搭建

1. 环境准备

一台Master、2台Slave、一台MHA

1.1 环境说明

准备4台服务器如下

1.2 软件说明

软件地址

链接: https://pan.baidu.com/s/1o8oxACpQHfzpNuOg0if7Mg

提取码: iss7

1.3 服务器准备

移除mariadb相关依赖

rpm -qa | grep mariadb

mariadb-libs-5.5.64-1.el7.x86_64

rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps

关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

2. MySQL安装配置

2.1 安装MySQL

分别在 Master、Slave 安装MySQL

1. 解压相关包

tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar 

2. 安装common

rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm 

3. 安装lib

rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm 

4. 安装lib-compat

rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm 

5. 安装client

rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm 

6. 安装server

rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

7. 安装开发工具（非必要）

rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

2.2 数据库初始化

初始化数据库

mysqld --initialize --user=mysql

2.3 启动MySQL server

systemctl start mysqld.service 

2.4 更改MySQL密码

查看MySQL初始化密码

cat /var/log/mysqld.log

image-20201109132246618.png

连接数据库

mysql -uroot -p

修改root用户密码

mysql> set password=password('root');

3. 主从配置

3.1 Master配置

3.1.1 配置文件修改

修改 Master 配置文件 /etc/my.cnf

[mysqld]
# log_bin
# 开启bin_log功能
log_bin=mysql-bin
# 设置server-id
server-id=151
# 写入操作与磁盘同步
sync-binlog=1
# 忽略当前库不同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
relay_log_purge=0
relay_log=mysql-relay-bin
# 指定同步的数据库，多个库已逗号隔开，不指定，同步所有的库
#binlog-do-db=test_db

3.1.2 重启MySQL

systemctl restart mysqld

3.1.3 MySQL授权

Master授权

mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';

mysql> flush privileges;

查看Master状态

mysql> show master status;

image-20201111195831976.png

3.2 Slave配置

修改两台Slave配置文件 /etc/my.cnf

两台Slave进行相同的配置，唯一不同的是server-id不同，同一个集群中server-id是唯一的

3.2.1 配置文件修改

2.2.1.1 Slave1配置

[mysqld]
# 指定 server-id
server-id=152
# 指定中继日志文件名
relay_log=mysql-relay-bin
# 指定为只读
read_only=1
# 开启bin_log功能
log_bin=mysql-bin
# 写入操作与磁盘同步
sync-binlog=1
# 忽略当前库不同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
relay_log_purge=0

2.2.1.1 Slave1配置

[mysqld]
# 指定 server-id
server-id=152
# 指定中继日志文件名
relay_log=mysql-relay-bin
# 指定为只读
read_only=1
# 开启bin_log功能
log_bin=mysql-bin
# 写入操作与磁盘同步
sync-binlog=1
# 忽略当前库不同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
relay_log_purge=0

3.2.1.2 Slave2配置

[mysqld]
# 指定 server-id
server-id=153
# 指定中继日志文件名
relay_log=mysql-relay-bin
# 指定为只读
read_only=1
# 开启bin_log功能
log_bin=mysql-bin
# 写入操作与磁盘同步
sync-binlog=1
# 忽略当前库不同步
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
relay_log_purge=0

3.2.2 重启MySQL

分别重启两台Slave MySQL Server 服务

systemctl restart mysqld

3.2.3 执行同步命令

两台Slave分别执行同步命令

mysql> change master to master_host='192.168.110.151',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;

开启Slave同步

mysql> start slave;

查看Slave状态

mysql> show slave status \G;

image-20201111201129822.png

3.3 主从测试

master 下创建 ms_db 数据库

mysql> create database ms_db;

Slave 下查看 ms_db 数据库是否同步

Slave1下

mysql> show databases;

image-20201111201425539.png

Slave2下

mysql> show databases;

image-20201111201425539.png

MySQL主从环境搭建OK。

4. 主从配置之半同步复制机制

使用 semi 插件进行 半同步复制

4.1 Master 配置

4.1.1 semi插件安装

查看是否支持动态加载功能

mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

查看已安装的插件

mysql> show plugins;
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+
44 rows in set (0.00 sec)

安装 semi 插件（Master）

mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

4.1.2 开启 semi 功能

修改 master 配置文件，开启 semi 半同步复制功能

[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

4.1.3 重启Master

systemctl restart mysqld

查看 semi 相关参数

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 1000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+

4.2 Slave 配置

4.2.1 semi插件安装

两台Slave分别安装semi插件

mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

查看 semi 相关参数

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

需要开启 semi 同步复制功能

4.2.2 开启 semi 功能

分别修改两台Slave配置文件，开启 semi 同步复制功能

[mysqld]
rpl_semi_sync_slave_enabled=1

4.2.3 重启Slave

分别重启Slave、查看semi状态

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

4.3 验证

查看 Master 日志

tail -f /var/log/mysqld.log

image-20201111204131866.png

MySQL主从复制半同步复制搭建OK。

5. MHA高可用搭建

5.1 SSH互通

分别在4台服务器执行以下命令，回车默认即可。生成密钥对

# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:2HlPramOyOiEac844ONPgDUoVbwonCjiepKPLpm7V0c root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|  .o.            |
| o  .            |
|= +. .           |
|B+... Eo .   .   |
|=o   .. S . . .  |
|...o. .  . o o   |
|o=+o..      +    |
|B=== o . . .     |
|BX=+= o ..o      |
+----[SHA256]-----+

分别在三台MySQL服务器执行以下命令，将各自的公钥发布到MHA Manager服务器

ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.110.154

查看MHA Manager服务器上是否包含3个公钥

cat /root/.ssh/authorized_keys 

将MHA Manager服务器公钥发布到三台MySQL服务器

scp /root/.ssh/authorized_keys root@192.168.110.151:~/.ssh/
scp /root/.ssh/authorized_keys root@192.168.110.152:~/.ssh/
scp /root/.ssh/authorized_keys root@192.168.110.153:~/.ssh/

5.2 安装MHA服务

分别在四台服务器（Master、Slave、Manager）安装 mha4mysql-node-0.58-0.el7.centos.noarch.rpm

yum -y install perl-DBD-MySQL 
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

MHA Manager 服务器安装 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

[root@manager]# rpm -ivh epel-release-latest-7.noarch.rpm
[root@manager]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
[root@manager]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

5.3 MHA配置

Manager节点需要为每个监控的 Master/Slave 集群提供一个专用配置文件，而所有的 Master/Slave集群也可以共享全局配置。全局配置文件默认为 /etc/masterha_default.cnf。

5.3.1 全局配置

vim /etc/masterha_default.cnf

[server default]
# mha管理用户
user=root
# mha 管理密码
password=123
# ssh登陆用户
ssh_user=root
# 主从复制数据库用户
repl_user=root
# 主从复制数据库密码
repl_password=root
# ping间隔时长
ping_interval=1

5.3.2 实例配置文件

初始化配置目录

mkdir -p /var/log/mha/app1

初始化实例日志文件

touch /var/log/mha/app1/manager.log

配置监控实例配置文件

vim /etc/mha/app1.cnf

[server default]
#MHA监控实例根目录
manager_workdir=/var/log/mha/app1
#MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log

#[serverx] 服务器编号
#hostname 主机名
#candidate_master 可以做主库
#master_binlog_dir binlog日志文件目录

[server1]
hostname=192.168.110.151
candidate_master=1
master_binlog_dir="/var/lib/mysql"

[server2]MHA 配置检测
hostname=192.168.110.152
candidate_master=1
master_binlog_dir="/var/lib/mysql"

[server3]
hostname=192.168.110.153
candidate_master=1
master_binlog_dir="/var/lib/mysql"

5.4 MHA检测

5.4.1 SSH互通检测

Master上检测各个节点 ssh 互通配置是否ok

masterha_check_ssh --conf=/etc/mha/app1.cnf

image-20201112145057934.png

5.4.2 MySQL主从复制检测

masterha_check_repl --conf=/etc/mha/app1.cnf

返回信息MySQL Replication Health is OK.及说明MySQL复制集群没有问题

image-20201112152959406.png

5.5 启动MHA

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

查看MHA监控状态

masterha_check_status --conf=/etc/mha/app1.cnf

查看监控命令日志

tail -f /var/log/mha/app1/manager.log

MHA停止命令

masterha_stop -conf=/etc/mha/app1.cnf

5.6 MHA 架构测试

模拟故障

关闭Master

systemctl stop myslqd

查看MHA监控日志

tail -f /var/log/mha/app1/manager.log

----- Failover Report -----

app1: MySQL Master failover 192.168.110.151(192.168.110.151:3306) to 192.168.110.152(192.168.110.152:3306) succeeded

Master 192.168.110.151(192.168.110.151:3306) is down!

Check MHA Manager logs at localhost.localdomain:/var/log/mha/app1/manager.log for details.

Started automated(non-interactive) failover.
The latest slave 192.168.110.152(192.168.110.152:3306) has all relay logs for recovery.
Selected 192.168.110.152(192.168.110.152:3306) as a new master.
192.168.110.152(192.168.110.152:3306): OK: Applying all logs succeeded.
192.168.110.153(192.168.110.153:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.110.153(192.168.110.153:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.110.152(192.168.110.152:3306)
192.168.110.152(192.168.110.152:3306): Resetting slave info succeeded.
Master failover to 192.168.110.152(192.168.110.152:3306) completed successfully.

image-20201112162310363.png

可以根据日志得出，Master（192.168.110.151）故障，而后自动执行故障转移， 将192.168.110.152提升为主节点。

5.7 故障节点恢复

修复原有Master（192.168.110.151）节点，需要重启该MySQL Server ,将其节点重新挂到该Master下。

mysql> change master to master_host='192.168.110.152',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000002',master_log_pos=745;
mysql> start slave;
mysql> show slave status \G;

使用MHA在线将原主切换回来

masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.110.151 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

问题记录

• 问题一

manager 检测ssh互通

masterha_check_ssh --conf=/etc/mha/app1.cnf

Thu Nov 12 14:15:15 2020 - [info] Reading default configuration from /etc/masterha_default.cnf..
/etc/mha/app1.cnf:
 at /usr/share/perl5/vendor_perl/MHA/SSHCheck.pm line 148.

原因：ssh互通配置的有问题，

解决方案：重新配置了ssh互通

• 问题二

  manager 检测主从复制 is not ok!

masterha_check_repl --conf=/etc/mha/app1.cnf

Thu Nov 12 14:52:13 2020 - [info] Reading default configuration from /etc/masterha_default.cnf..
Thu Nov 12 14:52:13 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Nov 12 14:52:13 2020 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Nov 12 14:52:13 2020 - [info] MHA::MasterMonitor version 0.58.
Thu Nov 12 14:52:13 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.110.152(192.168.110.152:3306) :1130:Host '192.168.110.154' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
Thu Nov 12 14:52:13 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301]  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Thu Nov 12 14:52:13 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180] Got MySQL error when connecting 192.168.110.153(192.168.110.153:3306) :1130:Host '192.168.110.154' is not allowed to connect to this MySQL server, but this is not a MySQL crash. Check MySQL server settings.
Thu Nov 12 14:52:13 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301]  at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297.
Thu Nov 12 14:52:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Thu Nov 12 14:52:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Thu Nov 12 14:52:14 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Thu Nov 12 14:52:14 2020 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

image-20201112145915612.png

原因：从库没有配置root用户远程访问权限，

解决方案：配置远程访问权限即可

mysql> use mysql;
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.05 sec)
mysql> update user set host='%' where user='root';
mysql> flush privileges;

• 问题三

manager 检测主从复制 is not ok!

masterha_check_repl --conf=/etc/mha/app1.cnf

Slave IO thread is not running on 192.168.110.151(192.168.110.151:3306)

image-20201112150927148.png

原因：不小心在master启动了slave。

解决方案：关闭master上slave

mysql> stop slave;
mysql> reset slave all;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status \G;
Empty set (0.00 sec)

ERROR: 
No query specified

mysql> 

• 问题四

manager 检测主从复制 is not ok!

masterha_check_repl --conf=/etc/mha/app1.cnf

None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf

image-20201112151437131.png

原因：Slave没有开启log_bin功能。

解决方案：在Slave上开启log_bin

log_bin=mysql-bin
# 配置写入都同步到binlog
sync-binlog=1

• 问题五

manager 检测主从复制 is not ok!

masterha_check_repl --conf=/etc/mha/app1.cnf

All log-bin enabled servers must have same binlog filtering rules (same binlog-do-db and binlog-ignore-db). Check SHOW MASTER STATUS output and set my.cnf correctly.
Slave configurations is not valid.

image-20201112152107780.png

原因：Slave的忽略、同步规则和Master的不一致

解决方案：将Slave和Master的忽略、同步规则配置为一样

binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql