MySQL集群架构之MHA高可用搭建
1. 环境准备
一台Master、2台Slave、一台MHA
1.1 环境说明
准备4台服务器如下
| 序号 | IP | 说明 |
|---|---|---|
| 1 | 192.168.110.151 | Master |
| 2 | 192.168.110.152 | Slave1 |
| 3 | 192.168.110.153 | Slave2 |
| 4 | 192.168.110.154 | MHA Manger |
1.2 软件说明
| 软件 | 版本 |
|---|---|
| MySQL | 5.7.28 |
| mha4mysql | 1.58 |
| CentOS | 7 |
软件地址
链接: 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
- 解压相关包
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
- 安装common
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
- 安装lib
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
- 安装lib-compat
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
- 安装client
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
- 安装server
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
- 安装开发工具（非必要）
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
