概述

MySQL发展至今，在企业级高可用方面进化出了两大架构，一个是InnoDB Cluster架构， 另一个是基于binlog的Master-Slave同步架构。即使在MySQL 8.0中推出了redo log归档等多个飞跃式改进的情况下，Master-Slave同步依然不可替代。

此文档细致讲述如何为MySQL 5.7 Master节点在线无锁添加Slave节点。

一：环境信息

Master节点信息：

Slave节点信息：

二：从库准备

从库节点安装RHEL7.4，与主库节点保持一致，安装后关键配置如下：

2.1 修改limits

编辑/etc/security/limits.conf 文件，添加如下配置内容：

mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65535
mysql hard nofile 65535
* soft memlock unlimited
* hard memlock unlimited

编辑/etc/security/limits.d/20-nproc.conf 文件，添加如下内容：

* soft nproc 65536

2.2 关闭防火墙

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

2.3 安装MySQL SLAVE 实例

从库节点安装MySQL 5.7.29，配置参数与主库节点保持一致，注意server-id与主库不一致。

• 从官网下载MySQL 5.7.29，下载地址：https://downloads.mysql.com/archives/community/

• 解压MySQL

tar xzvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar

• 安装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-devel-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

• 配置MySQL

在Slave节点执行以下命令取得主库my.cnf

rsync -avh IMMASTER:/etc/my.cnf   /etc/

编辑my.cnf 文件,修改server-id与log_slave_updates

server-id=101  ##此值不能与Master节点的值相同
log_slave_updates=ON

根据my.cnf内容创建对应的数据目录

mkdir /data/mysql -p
mkdir /data/tmp -p
chown mysql.mysql data -R
chmod 755 data -R

初始化mysql

mysqld --initialize --user=mysql --datadir=/data/mysql

取得随机密码

grep password /data/mysql/mysqld.log

• 启动mysql

systemctl start mysqld

登录修改用户信息

mysql -uroot -p (此处为上面取得的随机密码)

mysql> set password='LaoGeDB123!';
mysql> grant all on *.* to root@localhost identified by 'LaoGeDB123!';
mysql> grant all on *.* to root@'%' identified by 'LaoGeDB123!';
mysql> flush privileges;

三：工具准备

percona公司开发的xtrabackup工具包是一款可以对MySQL做数据库级别物理备份的利器，其在备份过程中既可以保持事务一致性，又不会对数据库造成锁表，当然前提是InnoDB引擎数据表，对于MyISAM引擎的数据表无法避免锁表。

3.1 工具下载

下载方式

cd /root/Downloads
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/tarball/percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz

3.2 部署安装

cd /root/Downloads 
tar xzvf percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz
mv ./install/percona-xtrabackup-2.4.20-Linux-x86_64  /usr/local/
cd /usr/local/
ln -s percona-xtrabackup-2.4.20-Linux-x86_64.el7.tar.gz  xtrabackup

3.3 配置环境变量

编辑/etc/profile.d/xtra.sh 文件，添加如下内容:

PATH=$PATH:/usr/local/xtrabackup/bin
export PATH

source文件让配置生效：

source /etc/profile.d/xtra.sh

此时可以直接执行xtrabckup命令了

四：核心流程

4.1 主库前置条件检查

• 检查MySQL版本

• 检查MySQL默认数据引擎

• 检查binlog格式为ROW

此处binlog设置为ROW是为了采用GTID同步方式。

• 确认binlog开启

此处红色框表示binlog开启，蓝色框表示我同时开启了GTID。

4.2 主库在线无锁备份

• 使用xtrabackup对数据库做在线无锁备份

以下是Master节点中数据库，其中红色框为系统数据库，蓝色框为生产业务数据库。

主库整体备份，包括系统数据库(mysql、performance_schema)，命令如下:

innobackupex --defaults-file=/etc/my.cnf --user=root \
--password=Lenovo123 –parallel=4  \
/data/dbbackup

• 主库传输备份数据到从库节点

rsync -avh  /data/dbbackup  IMSLAVE:/data/dbbackup

• 看一下备份数据究竟是什么内容

1). backup-my.cnf是主库中关键配置参数，xtrabackup在APPLY阶段会根据它生成ib_logfile以及ib_tmp等关键数据文件;2). ibdata1是共享表空间;3). tpcc是生产业务数据库,此状态下tpcc中的数据文件是不一致的;4). xtrabackup_binlog_info中记录了备份后的binlog信息，是做从库同步的关键内容;5). xtrabackup_logfile则是备份过程中的redo日志，因为是无锁表备份，因此在备份过程中所有的事务修改都被记录在这个文件中。

4.3 从库还原数据库备份

• Apply Redo Log数据到数据文件

正如上面所述，备份所得数据文件本身是不一致的，需要通过redo log来apply事务到数据文件才能达到数据一致，也就是把xtrabackup_logfile中的事务重做一遍，从而recovery ibdata1和tpcc中的数据文件到一致性状态。

在从库节点上apply redo log到数据文件，命令如下：

cd /data/dbbackup
innobackupex --apply-log 2020-05-27_21-09-50

Apply过程中会同时根据backup-my.cnf创建ib_logfile、ibtmp。完成后状态如下：

上图红色框内是新生产的文件，蓝色框内为APPLY过redo后的共享表空间文件，当然tpcc库内是ibd数据文件也同样被APPLY redo。

至此，主库的数据库一致性快照我们已经拿到了，那么接下来就是把这个快照加载到从库节点数据库实例中。

• Copy Back数据库到数据目录

在从库执行以下命令，完成数据库copy back

rm -rf  /data/mysql/*
innobackupex --defaults-file=/etc/my.cnf  \
--socket=/data/mysql/mysql.sock --user=root --password=LaoGeDB123!  \
--copy-back /data/dbbackup/2020-05-27_21-09-50

• 启动从库实例

启动前要将/data/mysql目录下的文件属主修改为mysql

chown mysql.mysql /data/mysql -R
systemctl start mysqld

4.4 配置主从

配置主从可以采用传统的binlog_file+binlog_position的方式，也可以采用MySQL 5.6开始引入的GTID方式。此文档中，我们先采用传统的方式配置主从同步，再切换为GTID方式。

• 在主库上执行以下SQL命令创建同步用户并赋予其最低权限，其中192.168.11.62为从库节点IP：

mysql> CREATE USER 'repl'@'192.168.11.62' IDENTIFIED BY 'LaoGeDB123!';
mysql> GRANT REPLICATION SLAVE ON *.*TO 'repl'@'192.168.11.62';

• 在从库上执行以下命令清理本地binlog：

mysql> RESET MASTER;
mysql> RESET SLAVE ALL;

• 在从库上建立与主库的主从同步关系

首先，查看xtrabackup数据备份中的xtrabackup_binlog_info文件，此文件中记录了三个重要信息，分别是：

1). 备份主库时binlog文件名--下图中红色框内标记的值

2). 备份主库时最后一个事务ID--下图中黄色框内标记的值

3).备份主库时最后一个事务的GTID--下图中蓝色框内标记的值

然后，根据以上三个值建立主从同步

如果要使用传统binlog_file+binlog_position同步模式,则可以在从库上执行以下命令：

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.11.61',
MASTER_USER='repl',
MASTER_PASSWORD='LaoGeDB123!',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=565400722;

如果要使用binog_file+GTID同步模式，则可以在从库上执行以下命令：

mysql> SET GLOBAL GTID_PURGED='7a62a100-a00b-11ea-848c-e04f43073594:1-23129';
mysql > CHANGE MASTER TO
MASTER_HOST='192.168.11.61',
MASTER_USER='repl',
MASTER_PASSWORD='LaoGeDB123!',
MASTER_AUTO_POSTION= 1;

最后，在从库上执行以下命令启动slave的IO_thread和SQL_thread

mysql> START SLAVE;

• 在从库上执行以下命令查看slave同步状态

mysql> SHOW SLAVE STATUS\G

IO_thread和SQL_thread都已启动，且已经sync master的binlog到mysql-bin.000008，验证数据一致性后证明数据同步正常，传统同步模式数据库主从建立完成。

五：传统POS与GTID切换

GTID同步模式更符合全局事务唯一性的概念，一个事务在主从中的事务ID是全局唯一的，而要使用GTID同步模式，需要具备如下2个前提：

1). 主库实例binlog_format=ROW

2). 主从库均开启GTID

下面我们就将上面的传统同步模式切换为GTID同步模式。

• 开启强一致性检测参数

mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

• 开启GTID_MODE状态

检查GTID_MODE是否为ON，若不是则需要在mysql主库实例和从库实例上设置GTID_MODE为ON，其GTID_MODE需要由低到高逐步推进。

首先，检查状态

mysql>show variables like '%gtid%';

然后，开启GTID_MODE

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
mysql >SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
mysql >SET @@GLOBAL.GTID_MODE = ON;

• 在从库上停止slave同步

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;

• 在从库上查看此时slave端的GTID值，此值中23130-23133很重要，后面建立同步会用到，这里要记录好。

mysql> SHOW MASTER STATUS\G

• 在从库上清空GTID_EXECUTED状态

mysql> RESET MASTER;
mysql> SHOW MASTER STATUS\G

• 在从库设置GTID_PURGED，此值要用到在上面记录的23130-23133这个值。

mysql>show global variables like ‘%gtid%’ ; --此时为空

mysql> SET GLOBAL GTID_PURGED='7a62a100-a00b-11ea-848c-e04f43073594:1-23133';

• 在从库建立主从同步关系

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.11.61',
MASTER_USER='repl',
MASTER_PASSWORD='LaoGeDB123!',
MASTER_AUTO_POSITION =1;

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

IO_thread和SQL_thread都已启动，且已经sync master的binlog到mysql-bin.000012，验证数据一致性后证明数据同步正常，传统同步模式数据库主从切换为GTID模式完成。

一切正常，至此MySQL在线无锁添加SLAVE节点完成。

六：延伸讨论

6.1 前文收尾

MySQL数据库在线无锁表添加slave节点，关键在于主库的在线无锁备份，而这是建立在InnoDB引擎之上，对于MyISAM引擎则无法达到无锁备份。

MySQL的在线无锁表备份有两种方式:

1). 物理备份--percona公司的xtrabackup工具与mysql官方的mysqlbackup工具

2). 逻辑备份--mysql原生的mysqldump或mysqlpump工具

• 本文档中讲述的percona公司开发的xtrabackup工具集方式

xtrabackup是基于数据文件级别的物理备份，因此在体量较大的数据库上强烈建议使用xtrabackup。

• MySQL官方的企业级备份工具MySQL Enterprise Backup

MySQL Enterprise Backup与Percona xtrabackup有同样的功能，使用方式与工作原理也几乎一样，后面我会单独写文章讲解如何使用MySQL Enterprise Backup在线无锁添加slave节点。

• 采用MySQL原生自带的mysqldump或mysqlpump工具方式

mysqldump要想达到无锁备份数据库则要启用single-transaction，它在备份过程中会开启一个事务，并将数据库隔离级别设置为可重复读，这保证了事务一致性，也就保证了数据一致性。对于体量较小的数据库则可以使用mysqldump。

mysqlpump是mysqldump的一个衍生品，在MySQL 5.7中开始出现的工具，与mysqldump一样都属于逻辑备份，它最主要的特点是增加了并行备份数据库和数据库中的对象的功能，加快备份过程，并且可以灵活选择要备份的数据库schema。

关于如何使用mysqldump和mysqlpump在线无锁添加slave节点的不再讨论，这种方式太不友好。

6.2 主从同步先天缺陷

MySQL 5.7对GTID做了大量改进，相对于MySQL 5.6已经有了非常大的提高，且在MySQL 8.0中得到进一步增强，这极大改善了MySQL数据库主从同步的灵活性和扩展性。

然而，无论Oracle对MySQL做出了多大的改进，迄今为止，MySQL主从同步架构与生俱来的致命缺陷从来都没有得到过修复，其缺陷如下：

• 主库并行而从库串行

尽管现在已经添加了从库并行解析relay log以及replay log的功能，但其性能依然低下，串行化的本质未变。

• 同步基于binlog

基于binlog的同步其本质上是逻辑同步，这导致了binlog在从库上需要再做一次SQL解析、优化器调度、SQL Plan、多次回盘取数据、索引读取与更新、排序、刷新redo、占用undo、最后flush的全套流程。每条SQL都必须跟在主库上一样被再次执行一遍，消耗大量资源。

以上两点则会导致主从同步延迟频发、从库IO抖动、CPU负载升高，这一切都增加了从库的易碎性。

Oracle在MySQL 8.0.18以及之后的版本中添加了redo log归档功能，这就类似Oracle数据库的redo log归档，这种特性让MySQL实现类似Oracle物理Active Dataguard的主从同步更近了一步，也许将来Oracle真的会把MySQL物理主从同步功能带来，否则它做这个redo log归档的意义是什么呢？

作者：老哥讲数据库

简介：数据库高级架构师，oracle 11g OCM认证，MySQL 5.7 & 8.0 OCP认证。

原创文章，转载请注明来源。