MySQL 搭建主从高可用

CDSY,CDSY.XYZ

本篇文章是 MySQL 学习课程中的一部分笔记。

本博文详细说明了如何搭建 MySQL 高可用并对解决普通主从复制延迟不理想提供了解决方案，最后借助 MHA 实现了主从的自动切换。

1. mysql 下载

MySQL 官方下载地址：https://downloads.mysql.com/archives/community/

操作系统：CentOS 7

MySQL：mysql-5.7.29

在这个页面中选择操作系统和对应版本，如下图所示：

在 CentOS 中只用使用 wget 命令下载即可，下载完成后进行解压：

> wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
> tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-test-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-5.7.29-1.el7.x86_64.rpm
mysql-community-client-5.7.29-1.el7.x86_64.rpm
mysql-community-server-5.7.29-1.el7.x86_64.rpm
mysql-community-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-common-5.7.29-1.el7.x86_64.rpm

2 安装

在开始安装之前，需要确认系统中没有 MySQL 以及相关产品，使用 CentOS 镜像安装的操作系统的话，默认会自带一个 MariaDB，如果使用云主机，一般不带，不过也需要检查一下：

> rpm -qa | grep mariadb
> rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps     # 如果有的话，使用这种方式移除

上面 MySQL 的安装包解压后有很多组件，一般不需要全部安装，挑常用的几个安装即可，并且要注意安装顺序：

> rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm
> rpm -ivh mysql-community-libs-5.7.29-1.el7.x86_64.rpm
> rpm -ivh mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
> rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm
> rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm
> rpm -ivh mysql-community-devel-5.7.29-1.el7.x86_64.rpm

上面组件安装完成后就完全够日常使用了。

3 初始化

使用下面命令进行初始化：

> mysqld --initialize --user=mysql # --user 是可选的参数，意思是创建一个名称是 mysql 的用户

初始化完成后，mysql 会给 root 用户创建一个临时的登录密码，登录进去后再对 root 用户的密码进行自定义修改，临时密码在 mysql 的日志中，默认路径是 /var/log/mysqld.log，一般在最后一行类似下面：

A temporary password is generated for root@localhost: hSQ&zl/3m?oF

一会使用上面的临时密码登录。

使用系统方式启动 mysql，同时还把 mysql 的加入到系统自启动列表中：

> systemctl start mysqld.service
> systemctl status mysqld.service     # 检查是否启动成功

然后使用上面日志中的临时密码登录，登录后修改密码，否则不能做任何操作：

> mysql -uroot -p
....
> set password=password('your_new_password');

为了让集群中的各个数据库节点能正常通信，需要关闭防火墙或者在防火墙中放行 3306 的端口，这里就直接停掉了：

> systemctl stop iptables      # iptables 防火墙
> systemctl stop firewalld     # firewalld 防火墙，CentOS 自带
> systemctl disable firewalld.service   # 彻底一点，从开机启动中禁用掉

4 配置主从

4.1 配置主库

修改主库的配置文件 /etc/my.cnf,增加以下配置：

# log_bin
server-id=1                          # 设置 server-id，每个数据库不能重复，必须
log_bin=mysql-bin                    # 指定 binlog 的名称，相当于开启 bin log, 必须
sync-binlog=1                        # 开始刷新 bin log 到磁盘，每次有更新事务，完成后都要马上刷新到磁盘
binlog-ignore-db=performance_schema  # binlog 中忽略的库
binlog-ignore-db=information_schema
binlog-ignore-db=sys

#binlog-do-db=lagou                   # 可以使用这个参数指定只同步那个库

保存退出后，重启数据库：

> systemctl restart mysqld

开始 bin log 后，就需要对 master 库进行一下权限设置，设置哪些 ip 的 slave 可以从本 master 同步 binlog。

登录进 mysql 执行一些授权操作：

grant replication slave on *.* to 'root'@'%' identified by 'root';    # 复制授权
grant all privileges on *.* to 'root'@'%' identified by 'root';
flush privileges;                                                     # 刷新权限

在查看一下数据库作为 master 的一些状态

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                          | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000001 |      869 |              | performance_schema,information_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------+-------------------

重点已关注 当前使用的 bin log 文件名称以及 bin log 的写入位置。

4.2 配置从库

修改每个从库的 /etc/my.cnf，添加一下内容：

server-id=3   # 注意一个集群环境中的 id 不能冲突
relay_log=mysql-relay-bin

# read_only=1    # 开启只读

重启数据库，然后登陆到 mysql 中做一些相关配置：

> show slave status;      # 查看 salve 状态，如果已经是开启状态，需要先 stop slave；修改配置后，在 start slave；
# 设置主库的 bin log 信息（用到了主库中当前 bin log 的文件名称以及当前写位置）
> change master to master_host='172.17.42.6',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
> show slave status;    # 查看从库状态, 第一次配置，此时连接 master 的状态应该是 No
> start slave;          # 开启 salve 模式

经过以上步骤，主库中新建库以及DDL 操作都能同步到 salve 数据库中了。

5 配置半同步复制

主从结构的 MySQL 的一个缺点就是主从同步延迟，5.7 后 MySQL 提供了半同步复制和并行复制来减小这个同步延迟，其实就是从并发执行 bin log 回放线程。

主库配置

查看当前版本是否支持插件：

> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+

查看当前已经安装的插件：

> show plugins;

默认应该是没有 rpl_semi_sync插件的，那就需要安装：

> install plugin rpl_semi_sync_master soname 'semisync_master.so';  #  soname 后面是个别名

安装完成后，对参数做一下调整(开启 rpl 以及调整超时时长)：

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| 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 |
+-------------------------------------------+------------+

mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global rpl_semi_sync_master_timeout=1000;
Query OK, 0 rows affected (0.00 sec)

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 |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

从库配置

同样需要安装 rpl_semi 插件, 注意和主库中安装的略有不同，主库插件后缀为 master，从库后缀名称为 slave：

> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

同样需要开启 rpl：

mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

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

然后在重启一下 slave 模式加载新的配置就可以了：

mysql> stop slave;
mysql> start slave;

6 配置并行复制

这里针对并行复制的组提交模式进行配置：

主库参数

mysql> show variables like '%binlog_group%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)

mysql> set global binlog_group_commit_sync_delay=1000;     # 组提交延迟时间
Query OK, 0 rows affected (0.00 sec)

mysql> set global binlog_group_commit_sync_no_delay_count=100;  # 组中最大事务数量
Query OK, 0 rows affected (0.00 sec)

从库配置

因为部分参数在 msyql 控制台是只读的，所以需要在 my.cnf 中修改

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=1

保存后，重启数据库，然后登陆后查看参数：

mysql> show variables like '%slave_parallel%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 8             |
+------------------------+---------------+

mysql> show variables like '%relay_log%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| max_relay_log_size        | 0                                    |
| relay_log                 | mysql-relay-bin                      |
| relay_log_basename        | /var/lib/mysql/mysql-relay-bin       |
| relay_log_index           | /var/lib/mysql/mysql-relay-bin.index |
| relay_log_info_file       | relay-log.info                       |
| relay_log_info_repository | TABLE                                |
| relay_log_purge           | ON                                   |
| relay_log_recovery        | ON                                   |
| relay_log_space_limit     | 0                                    |
| sync_relay_log            | 10000                                |
| sync_relay_log_info       | 10000                                |
+---------------------------+--------------------------------------+

配置完成。

验证

• 在主库中做一下 DDL 操作或者插入删除操作
• 查看从库已经同步成功
• 查询从库中关于复制的线程信息，有 8 个线程：

  mysql> use performance_schema;
  mysql> select * from replication_applier_status_by_worker;
  +--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
  | CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
  +--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
  |              |         1 |        27 | ON            | ANONYMOUS             |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         2 |        28 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         3 |        29 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         4 |        30 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         5 |        31 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         6 |        32 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         7 |        33 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  |              |         8 |        34 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
  +--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
  

7 配置 MHA

MHA 的功能就是监控主从集群中主节点的运行状态，以及 master 挂掉后，自动在可用的 slave 中选出一个新的 master。

MHA 要求集群环境中至少有一主两从三台数据库。

本次操作集群列表如下：

主机名	IP	初始主从角色	MHA 角色
mysql-master		master	MHA Node
ysql-slave-1		slave	MHA Node
mysql-slave-2		slave	MHA Node
mha-manager			MHA Manager

根据主机名称，在各自机器的 /etc/hosts 中配置其他机器的 host。

安装基本依赖

所有数据库服务器和要作为 MHA Manager 的服务器都要安装：

# 安装一个epel源
wget -O /etc/yum.repos.d/epel-7.repo http://mirrors.aliyun.com/repo/epel-7.repo
 
# 用yum安装依赖包  (CentOS 8 安装 perl-DBD-MySQL 即可)
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y

配置 SSH 免密登录

配置所有机器之间可以通过 SSH 免密登录，比如在 mha-manager 机器中，先生成对称秘钥，在把公钥复制到其他三台机器上：

> ssh-keygen -t rsa     # 一路回车，全部使用默认值
> ssh-copy-id -i /root/.ssh/id_rsa.pub mysql-slave-1
> ssh-copy-id -i /root/.ssh/id_rsa.pub mysql-slave-2
> ssh-copy-id -i /root/.ssh/id_rsa.pub mysql-master

其他三台机器依照上述过程，把本机生成的公钥复制到其他三台机器中。

修改从库的配置

从库的 relay_log 不能被自动删除：

relay_log_purge = 0       # 不自动删除relay log
log_bin=mysql-bin         # 同样开启 bin log

# 因为主库中配置了 bin log 库过滤，从库也必须配置一样的库过滤
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys

安装 MHA Node

所有的数据库节点都需要安装 MHA Node，从 https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads 中下载 MHA Node 0.56 rpm RHEL6，可能需要手动下载在上传到服务器节点。

> rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm     # 安装 MHA Node

安装完成在 /usr/bin目录下出现以下脚本文件：

[root@mysql-slave-2 ~]# ll /usr/bin | grep 'logs'
-rwxr-xr-x  1 root root      16367 Apr  1  2014 apply_diff_relay_logs
-rwxr-xr-x  1 root root       1465 Jun 10  2014 dbilogstrip
-rwxr-xr-x  1 root root       8261 Apr  1  2014 purge_relay_logs
-rwxr-xr-x  1 root root       7525 Apr  1  2014 save_binary_logs

这些脚本工具通常由MHA Manager的脚本触发，无需人为操作。

mha 授权

在所有数据库节点上给 mha 数据库角色授权：

> grant all on *.* to 'mha'@'%' identified by 'mha';    # 这块和下面 manager 中配置文件配置的用户要相同

所有的从库上配置只读：

> set global read_only=1;

安装 MHA Manager

在机器mha-manager 中安装。

Manager 也会依赖 mha-node ，因此要先安装MHA Node

从 https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads 下载[MHA Manager 0.56 rpm RHEL6]，安装：

[root@MHA-Manager ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
[root@MHA-Manager ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

配置 manager

首先创建 mha 工作目录和配置文件

> mkdir -p /etc/mha
> touch /etc/mha/app1.cnf
> vim /etc/mha/app1.cnf

配置文件内容（基本内容）：

[server default]
#MHA日志名字
manager_log=/etc/mha/manager.log
#MHA的工作目录
manager_workdir=/etc/mha
#数据库binlog存放路径, 如果在 maser 库中自定义了 bin log 的目录，则需要在这里配置
#master_binlog_dir=/var/logs/

#mha管理用户的用户名, 全面所有数据库中已经给这个用户做了授权
user=root
#mha管理用户的密码
password=root

#监测心跳，每隔2秒监测一次（默认是3秒）
ping_interval=2

#ssh远程连接用户（做完免密的）
ssh_user=root

[server1]
hostname=172.17.42.6
port=3306
[server2]
hostname=172.17.42.8
port=3306
[server3]
hostname=172.17.42.10
port=3306

测试

> masterha_check_ssh --conf=/etc/mha/app1.cnf    # 测试 ssh 免密登录

> masterha_check_repl --conf=/etc/mha/app1.cnf   # 测试主从复制状态

这两部测试出现问题，根据输出提示进行修复。

启动

后台启动 manager

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

上面部分参数说明：

• remove_dead_master_conf ： 在配置文件中删除master信息
• ignore_last_failver ： 忽律上一次故障切换

查看运行状态：

> masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:13778) is running(0:PING_OK), master:172.17.42.6

测试主库自动切换

1. 先用上面的命令检查 mha manager 的运行状态，保持 Running 状态。
2. 在停到主库 systemctl stop mysqld
3. 等 5-10 秒，查看两个从库的 slave 状态show slave status \G;，会发现有一台 slave 已经变成了master (show slave status;返回空，查询其master 状态)。

恢复上一步停掉的主库

现在恢复只能先已从库的角色恢复，如需要在切换成主库，可以手动切换。

1. 原主库中启动数据库 systemctl start mysqld
2. 在 MHA Manager 机器中，查看 MHA 日志，找到以下内容：

   > grep -i 'change master to' /etc/mha/manager.log
   Wed May 27 11:00:30 2020 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.17.42.8', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154, MASTER_USER='root', MASTER_PASSWORD='xxx';
   

3. 登录原主库，执行日志中的 change master 命令，注意密码部分要调整，然后启动 slave，在查看 slave 状态：

   mysql> CHANGE MASTER TO MASTER_HOST='172.17.42.8', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154, MASTER_USER='root', MASTER_PASSWORD='root';
   Query OK, 0 rows affected, 2 warnings (0.02 sec)
   
   mysql> start slave;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> show slave status \G;
   

4. 重新修复 MHA Manager 配置文件，因为完成一次切换后，MHA 会自动把失效的主库信息从配置文件移除掉并且自动退出，所以需要把上面修复好的数据库信息补充到 MHA 配置文件中，然后重新启动 MHA Manager。

手动热切换主库

上面方式是手动停掉主库后触发 MHA 的自动切换，如果要在主从都运行正常情况下切换主从，可使用一下命令：

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

运行中间会涉及交互，仔细阅读提示后进行下一步的操作。

附

提示缺少 libssl 等库

如果安装过程中提示提示缺少依赖：

error: Failed dependencies:
	libcrypto.so.10()(64bit) is needed by mysql-community-libs-compat-5.7.29-1.el7.x86_64
	libcrypto.so.10(libcrypto.so.10)(64bit) is needed by mysql-community-libs-compat-5.7.29-1.el7.x86_64
	libssl.so.10()(64bit) is needed by mysql-community-libs-compat-5.7.29-1.el7.x86_64
	libssl.so.10(libssl.so.10)(64bit) is needed by mysql-community-libs-compat-5.7.29-1.el7.x86_64

下载安装一个 openssl-libs-1.0.2k-19.el7.x86_64.rpm(link:http://rpmfind.net/linux/centos/7.8.2003/os/x86_64/Packages/openssl-libs-1.0.2k-19.el7.x86_64.rpm) （来源网址：rpmfind）

下载后强制安装：

> rpm -ivh openssl-libs-1.0.2k-19.el7.x86_64.rpm --force

提示缺少 libaio.so

下载：wget http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm

安装：

> rpm -ivh libaio-0.3.107-10.el6.x86_64.rpm

MySQL 启动失败原因分析思路

启动失败的原因很多，在 /var/log/mysqld.log中可以查看具体原因，最常见的原因就是数据文件写入没有权限或者binlog 写入没有权限，比如默认 mysql 的数据目录为 /var/lib/mysql，如果提示The innodb_system data file 'ibdata1' must be writable一般就是这个数据目录没有权限了，增加写权限就行：

> chmod -R 777 /var/lib/mysql

或者把 mysql 用户放到 root 组中。

MHA Manager 错误

没有活跃的 server

首先要排查是不是防火墙的端口没有放行，然后排查 mysql 是否可以接受远程连接。

CDSY,CDSY.XYZ