[大数据]MySql主从复制实践 原理分析 搭建过程 优化和监控

前言：

项目中最近搭建了MySQL主从复制架构，MySQL版本是8.0.27。架构拓扑是一主一从的模式。本文是在MySQL主从搭建过程中的总结和扩展。包括了原理的简单分析、搭建流程及注意事项（当前示例搭建的是一主多从，其中从库1作为实时同步，从库2延时同步，作为备份库）、梳理MySQL复制类型及应用方式、主从复制的优化和监控、以及一些个人实施过程中的总结梳理等等

（一）原理简单分析

数据库复制本质上就是数据同步。MySQL 数据库是基于二进制日志（binary log）进行数据增量同步，而二进制日志记录了所有对于 MySQL 数据库的变更操作。至少需要两个 MySQL 服务（可以是同一台机器，也可以是不同机器之间进行），主从服务器通过binlog日志记录同步数据。

通过主从复制可以有效的实现实时灾备，用于故障切换；数据库数据备份；读写分离，负载均衡；数据热备等等。

在 MySQL 复制中，一台数据库的角色是 Master（也叫 Primary），剩下的服务器角色是 Slave（也叫 Standby）：

1：Master 服务器会把数据变更产生的二进制日志通过 Dump 线程发送给 Slave 服务器；如果有多个Slave则创建多个 Dump 线程；

2：Slave 服务器中的 I/O 线程负责接受二进制日志，并保存为中继日志；

3：SQL/Worker 线程负责并行执行中继日志，即在 Slave 服务器上回放 Master 产生的日志。

（二）主从复制搭建过程

描述

搭建一主多从的架构；一从：实时；一从：延迟1小时，备份库
因资源有限在一台服务器上进行构建；主服务器 13306端口；从1:13307端口；从2:13308端口

安装配置

1：准备docker环境
2：创建三个目录作为主从数据库的配置和数据挂载路径

其中每个目录下创建 conf和datadir目录，conf用于存放配置文件，datadir用于挂载数据映射
3：在每个conf目录中准备一个初始的配置文件。当前所使用的方式是创建一个相同版本的docker容器然后通过docker cp命令的方式将配置文件复制出来

此时创建了一个临时的 mysql:8.0.27版本的容器，依次复制配置文件到本地.复制结束后就可以结束该临时容器。

$docker cp mysql-test:/etc/mysql/my.cnf /home/mysql/master/conf
$docker cp mysql-test:/etc/mysql/my.cnf /home/mysql/slave1/conf
$docker cp mysql-test:/etc/mysql/my.cnf /home/mysql/slave2/conf

Master主节点

配置文件

[mysqld]
server-id = 1
#要同步的数据库
binlog-do-db = test
##要生成二进制日志文件
log-bin = mysql-bin
# gtid配置
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
relay_log_recovery = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

gtid配置设置都是用于保证 crash safe，即无论 Master 还是 Slave 宕机，当它们恢复后，连上主机后，主从数据依然一致，不会产生任何不一致的问题。

启动mysql-master数据库服务

docker run --name mysql-master \
-p 13306:3306 -e MYSQL_ROOT_PASSWORD=root \
--mount type=bind,src=/home/mysql/master/conf/my.cnf,dst=/etc/mysql/my.cnf \
--mount type=bind,src=/home/mysql/master/datadir,dst=/var/lib/mysql \
--restart=on-failure:3 \
-d mysql

创建test库

CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

创建从库用户并分配权限

create user 'slave'@'%' identified by '123456'; -- 创建用户
grant replication slave,replication client on *.* to 'slave'@'%'; -- 设置用户权限
flush privileges; -- 刷新权限
show grants for 'slave'@'%'; -- 查看用户权限

主节点数据备份和同步

如果新创建的数据库，可以忽略这一步，如果是修改已有的数据库，那么需要将数据库进行锁库并且dump出需要同步的数据

在主库上准备了表和几条历史数据用作已有数据。

下面进入到mysql-master的docker容器内部

docker  exec -it mysql-master bash

1. 登录master，执行锁表操作
mysql -uroot -p

FLUSH TABLES WITH READ LOCK;

2.将master中需要同步的db的数据dump出来,-p后面参数是数据库名称
退出mysql会话，在容器内执行mysqldump 
 mysqldump -uroot -p  test> test.dump --set-gtid-purged=off

3. 将数据导入slave
mysql -uroot -h192.168.208.10  -P13307  -p'root'  test < test.dump
mysql -uroot -h192.168.208.10  -P13308  -p'root'  test < test.dump

4. 解锁master
UNLOCK TABLES;

Tips：如果锁库切记等待从库创建完毕之后在锁定，选择一个合适的时间段，不要影响线上业务

从库1 实时同步

从库1配置，注意设置server-id不能和其他数据库服务相同

[mysqld]
server-id = 2
#要同步的数据库
binlog-do-db = test
##要生成二进制日志文件
log-bin = mysql-bin
# 选择 ROW 模式
binlog-format = ROW
# gtid配置
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
relay_log_recovery = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

注意事项：从库需要配置一个# 选择 ROW 模式
binlog-format=ROW，这里是后面Canal中间件进行数据同步配置要求，因为目前项目中使用到了该同步组件，如果不需要该组件可不设置

docker run --name mysql-slave1 \
-p 13307:3306 -e MYSQL_ROOT_PASSWORD=root \
--mount type=bind,src=/home/mysql/slave1/conf/my.cnf,dst=/etc/mysql/my.cnf \
--mount type=bind,src=/home/mysql/slave1/datadir,dst=/var/lib/mysql \
--restart=on-failure:3 \
-d mysql

创建test库

CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

从库1绑定Mater节点

-- 停止slave
stop slave;
-- 绑定master
change master to 
master_host='192.168.208.10',
master_user='slave',
master_log_file='mysql-bin.000001',
master_log_pos=156,
master_port=13306,
master_password='123456';
-- 启动slave
start slave;
-- 查看状态
show slave status 

从库配置是否正常需要观看如上俩个字段，如果不是则需要观察error信息

从库2 延时同步

从库2配置文件

[mysqld]
server-id = 3
#要同步的数据库
binlog-do-db = test
##要生成二进制日志文件
log-bin = mysql-bin
# gtid配置
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
relay_log_recovery = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

启动服务

docker run --name mysql-slave2 \
-p 13308:3306 -e MYSQL_ROOT_PASSWORD=root \
--mount type=bind,src=/home/mysql/slave2/conf/my.cnf,dst=/etc/mysql/my.cnf \
--mount type=bind,src=/home/mysql/slave2/datadir,dst=/var/lib/mysql \
--restart=on-failure:3 \
-d mysql

创建test库

CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

从库2绑定Mater节点

-- 停止slave
stop slave;
-- 绑定master
change master to 
master_delay = 3600 ,
master_host='192.168.208.10',
master_user='slave',
master_log_file='mysql-bin.000001',
master_log_pos=156,
master_port=13306,
master_password='123456';
-- 启动slave
start slave;
-- 查看状态
show slave status 

注意：上面配置了从库和主从延迟配置，CHANGE MASTER TO master_delay = 3600表示设置了 Slave 落后 Master 服务器1个小时，通过该机制可以实现数据库的备份策略。

测试

【1】主库创建测试表并进行一些数据变更操作

CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

【2】在主库创建表之后从库1会实时同步执行回放SQL。从库2会在1个小时候执行回放SQL。如果未实现数据同步可以参考“问题整理”部分。

（三）MySQL复制类型及应用选项

MySQL 复制可以分为以下几种类型：

【1】默认是异步复制，在异步复制（async replication）中，Master 不用关心 Slave 是否接收到二进制日志，所以 Master 与 Slave 没有任何的依赖关系，数据最终会通过二进制日志达到一致。
异步复制的性能最好，因为它对数据库本身几乎没有任何开销，除非主从延迟非常大，Dump Thread 需要读取大量二进制日志文件。
如果业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失，甚至大量的丢失，推荐用异步复制，这样性能最好（比如像微博这样的业务，虽然它对性能的要求极高，但对于数据丢失，通常可以容忍）。但往往核心业务系统最关心的就是数据安全，比如监控业务、告警系统。
【2】半同步复制要求 Master 事务提交过程中，至少有 N 个 Slave 接收到二进制日志，这样就能保证当 Master 发生宕机，至少有 N 台 Slave 服务器中的数据是完整的。

半同步复制并不是 MySQL 内置的功能，而是要安装半同步插件，并启用半同步复制功能，设置 N 个 Slave 接受二进制日志成功，比如：

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
rpl_semi_sync_master_wait_no_slave = 1

plugin-load：数据库启动时安装半同步插件
rpl-semi-sync-master-enabled和rpl-semi-sync-slave-enabled表示分别启用半同步 Master 和半同步 Slave 插件；
rpl_semi_sync_master_wait_no_slave表示半同步复制过程中，提交的事务必须至少有一个 Slave 接收到二进制日志。

在半同步复制中，有损半同步复制是 MySQL 5.7 版本前的半同步复制机制，这种半同步复制在Master 发生宕机时，Slave 会丢失最后一批提交的数据，若这时 Slave 提升（Failover）为Master，可能会发生已经提交的事务不见了，发生了回滚的情况。
有损半同步复制主库会先提交事务，然后等待从库的ACK响应，表示至少有 Slave 接收到了二进制日志，这会存在主库提交COMMIT后宕机后，如果这时二进制日志还未发送到 Slave，Master 就发生宕机，则此时 Slave 就会丢失 Master 已经提交的数据。

无损半同步复制 WAIT ACK 发生在事务提交之前，这样即便 Slave 没有收到二进制日志，但是 Master 宕机了，由于最后一个事务还没有提交，所以本身这个数据对外也不可见，不存在丢失的问题。

所以，对于任何有数据一致性要求的业务，如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务，务必使用无损半同步复制。这样数据才是安全的、有保障的、即使发生宕机，从机也有一份完整的数据。

【3】无论是异步复制还是半同步复制，都是 1 个 Master 对应 N 个 Slave。其实 MySQL 也支持 N 个 Master 对应 1 个 Slave，这种架构就称之为多源复制。
多源复制允许在不同 MySQL 实例上的数据同步到 1 台 MySQL 实例上，方便在 1 台 Slave 服务器上进行一些统计查询，如常见的 OLAP 业务查询。

【4】前面介绍的复制架构，Slave 在接收二进制日志后会尽可能快地回放日志，这样是为了避免主从之间出现延迟。而延迟复制却允许Slave 延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失。
我们可以通过以下命令设置延迟复制：

CHANGE MASTER TO master_delay = 3600

设置了 Slave 落后 Master 服务器1个小时。

延迟复制在数据库的备份架构设计中非常常见，比如可以设置一个延迟一天的延迟备机，这样本质上说，用户可以有 1 份 24 小时前的快照。

那么当线上发生误操作，如 DROP TABLE、DROP DATABASE 这样灾难性的命令时，用户有一个 24 小时前的快照，数据可以快速恢复。

对金融行业来说，延迟复制是你备份设计中，必须考虑的一个架构部分。

（四）主从复制的优化和监控

1、避免大事务

逻辑日志简单易懂，方便数据之间的同步，但它的缺点是：事务不能太大，否则会导致二进制日志非常大，一个大事务的提交会非常慢。
在项目中可能涉及到定期删除一些流水日志表的情况，如果每次删除的数量几十万或者更多，那么就会产生较大的二进制日志文件，Mater节点和Slave节点需要完成日志文件的磁盘写入和日志解析，这样过程是非常耗时的，提交事务的时间也会比较大。大事务除了会导致提交速度变慢，还会导致主从复制延迟。
对于类似的情况，可以通过俩个方面进行优化，一方面可以尝试把 DELETE 删除操作转化为 DROP TABLE/PARTITION 操作。另一方面可以将大事务转换为多个小事务。

2、设置从机并行复制

要彻底避免 MySQL 主从复制延迟，数据库版本至少要升级到 5.7，因为之前的MySQL 版本从机回放二进制都是单线程的（5.6 是基于库级别的单线程）。

从 MySQL 5.7 版本开始，MySQL 支持了从机多线程回放二进制日志的方式，通常把它叫作“并行复制”，官方文档中称为“Multi-Threaded Slave（MTS）”。
MySQL 的从机并行复制有两种模式。

COMMIT ORDER： 主机怎么并行，从机就怎么并行。

WRITESET： 基于每个事务，只要事务更新的记录不冲突，就可以并行。

COMMIT ORDER 模式的从机并行复制，从机完全根据主服务的并行度进行回放。理论上来说，主从延迟极小。但如果主服务器上并行度非常小，事务并不小，比如单线程每次插入 1000 条记录，则从机单线程回放，也会存在一些复制延迟的情况。

而 WRITESET 模式是基于每个事务并行，如果事务间更新的记录不冲突，就可以并行。还是以“单线程每次插入 1000 条记录”为例，如果插入的记录没有冲突，比如唯一索引冲突，那么虽然主机是单线程，但从机可以是多线程并行回放！！！

所以在 WRITESET 模式下，主从复制几乎没有延迟。那么要启用 WRITESET 复制模式，你需要做这样的配置：

binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16

因为主从复制延迟会影响到后续高可用的切换，以及读写分离的架构设计，所以在真实的业务中，你要对主从复制延迟进行监控。

3、监控Seconds_Behind_Master属性值

通过命令 SHOW SLAVE STATUS，其中的 Seconds_Behind_Master 可以查看复制延迟，如：

通过该属性可以观察主从复制延迟，但是注意该属性值并不是非常准确的，仅可以作为一个参考的指标。

4、主从同步监控

【1】通过编写Shell脚本监控从库服务

/usr/bin/mysql -h192.168.208.10 -P13307 -uroot -proot -e  'show slave status\G' | grep Running | awk '{print $2}' | grep "Yes" | wc  -l

描述：
可以用shell脚本操作mysql数据库，使用mysql的-e参数可以执行各种sql的(创建，删除，增，删，改、查)等各种操作。上面通过-e参数查看从库状态，然后通过grep和awk监控Slave_IO_Running和Slave_SQL_Running是否正常运行，"Slave_IO_Running: Yes“和“Slave_SQL_Running: Yes”，这两个值全是"Yes"就表明主从复制正常，否则就是有问题。，该脚本可以配合Crontab和邮件服务进行定期检查以及告警通知。上面我仅仅做列出示例，生产环境肯定需要单独创建一个权限用户，切不可使用root用户。

【2】利用zabbix+Shell脚本监控Mysql主从复制，当从库出现异常的时候报警。上面使用的Shell脚本的方式适合比较简单的复制架构，如果是比较大的集群架构肯定需要一些专业的监控中间件来辅助，比如可以结合zabbix。除此之外，也可以利用开源的监控产品，比如我曾经了解过得监控平台Spug，该平台上就有一些Shell脚本的功能，可以辅助实现监控

（五）问题整理

当搭建MySQL主从架构的时候的，检查是否配置成功的方式是在从库检查 show slave status\G;

要求红色框内Slave_IO_Running: Yes；Slave_SQL_Running: Yes。

如果配置不成功，可以通过观察Last_error属性描述来定位分析问题。

以上面的异常举例，可以看到如下错误信息中提示我们可以在performance_schema.replication_applier_status_by_worker中查看更多错误细节
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at master log mysql-bin.000003, end_log_pos 410. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

【注意】一定要注意的一点是，在处理类似的问题的时候不要把别人的解决方式拿来就用，要根据当前出现的具体问题在做处理，当时我因为比较急错误的使用了很多被人的解决方式，结果浪费了很多时间，走了很多弯路。

在从库查看当前表查看更多的信息
select * from performance_schema.replication_applier_status_by_worker\G；

错误：retry-time: 60 retries: 4 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
Error ‘Operation ALTER USER failed for ‘slave’@’%’’ on query. Default database: ‘test’. Query: ‘ALTER USER ‘slave’@’%’ IDENTIFIED WITH ‘sha256_password’ AS ‘$5$w7S{>l1A5<wYK*yRE$kdTTxTlafSNx1oc7UbDHtEKm5kXA4468N/OCJR41XN4’’

经过排查，发现该问题是因为MySQL 8以上版本的密码认证机制和MySQL5.7版本不同。mysql 8 全部采用了 caching_sha2_password 的方式，这导致很多之前可以使用的工具以及工作的方式都需要改变。
快速低成本解决方法：

在my.cnf 中添加下面一行，将MYSQL 与MYSQL 5.7 的密码认证方式一致，则上面的问题解决
default_authentication_plugin=mysql_native_password
重新启动数据库服务，上面的问题可以解决

上面的是修改配置文件的，也可以通过执行SQL语句的方式解决
ALTER USER ‘slave’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

错误：Error executing row event: ‘Unknown database ‘test’’

第二个问题是因为自己的测试过程中在主库执行了创建库语句，但是我是先在从库配置主库的binlog的position，然后在主库执行了该SQL语句，导致日志点不一致，这个可以通过手动创建库或者修改从库的master日志position修改。

错误：@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

这是因为我们设置gtid模式，我们需要在导出时在原数据库导出时加了–set-gtid-purged=OFF，导入新数据库时，才会触发记录到新数据库的binlog日志。如果不加，则新数据库不记录binlog日志。
所以在我们做主从用了gtid时，用mysqldump备份时就要加–set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。

错误：MySQL Master command COM_REGISTER_SLAVE failed: Access denied for user
通过客户端连接正确，但是在执行如下语句时错误，该问题是因为slave账号在主库的权限不足，需要授权

change master to 
master_host='192.168.208.10',
master_user='slave',
master_log_file='mysql-bin.000001',
master_log_pos=156,
master_port=13306,
master_password='123456';

grant replication slave,replication client on *.* to 'slave'@'%';
flush privileges; 
show grants for 'slave'@'%'; 

（六）知识小结

【1】MySQL复制架构可以有多种形式，包括
一主一从、主主复制、一主多从、多主一从（5.7后开始支持）、联级复制（从服务器下级联从服务器）

【2】在搭建主从架构时一定要注意server-id不能相同，该属性是集群服务器的标识，配置错误影响数据同步

【3】对于已在使用的数据库搭建主从要注意在复制过程中使用FLUSH TABLES WITH READ LOCK;进行锁库的操作并在结束时UNLOCK TABLES;解锁Master.

【4】通过show master status;查看mater节点binlog日志情况；通过show slave status 观察从服务器IO线程和SQL线程是否正常运行；通过show PROCESSLIST观察Mater节点Dump线程运行情况。
注意：在一主多从的架构中，有多少个Slave，那么Mater端就会创建多少个Dump线程，通过SHOW PROCESSLIST可以观察到当前Master节点正常运行多少个Dump线程，该命令可以帮助我们简单快速的发现集群Slave运行情况。

【5】二进制日志能完整记录一条记录，但是它也存在一些缺点；由于二进制日志记录一整行记录的信息，UPDATE时还会记录前项和后项，以及保存会话变量信息，因此占用存储空间较大。

【6】通过 MySQL 数据库自带的命令 mysqlbinlog，可以解析二进制日志，观察到更为详细的每条记录的信息，比如：
更新当前记录信息

进入到/var/lib/mysql目录下执行mysqlbinlog -vv mysql-bin.000001; 其中binlog日志文件名可以通过show master status查看

【7】虽然 MySQL 复制原理和实施非常简单，但在配置时却容易出错，请你务必在配置文件中设置如下配置：下面的设置都是用于保证 crash safe，即无论 Master 还是 Slave 宕机，当它们恢复后，连上主机后，主从数据依然一致，不会产生任何不一致的问题。

gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
relay_log_recovery = ON
master_info_repository = TABLE 
relay_log_info_repository = TABLE

【8】MySQL复制拓扑有很多的方式，例如主从实时同步可以缓解数据库压力，灾备恢复，读写分离；通过延迟复制可以替换传统的脚本化数据库备份策略；通过多源复制方式可以方便对多系统数据库做聚合分析等等