MySQL复制之异步复制

记录备忘。

参考博文：https://blog.csdn.net/wzy0623/article/details/90081518

---

目录

一、概述

二、binlog简介

三、刷盘方式

四、复制流程

五、实验

5.1 环境规划

5.2 mysql安装

5.3 配置复制

5.3.1 停机

5.3.2 联机

---

一、概述

简单说，复制就是将一个MySQL数据库服务器（主库）的数据复制到一个或多个MySQL数据库服务器（从库）。复制的桥梁就是二进制日志。

复制给主库增加的开销主要体现在启用二进制日志带来的I/O，但是开销并不大，MySQL官方文档中称开启二进制日志会产生1%的性能损耗。出于对历史事务备份以及从介质失败中恢复的目的，这点开销是非常必要的。除此之外，每个从库也会对主库产生一些负载，例如网络和I/O开销。当从库读取主库的二进制日志时，可能会造成一定的I/O开销。如果从一个主库上复制到多个从库，唤醒多个复制线程发送二进制日志内容的开销将会累加。但所有这些复制带来的额外开销相对于应用对MySQL服务器造成的高负载来说是很小的。

 

二、binlog简介

二进制日志包含描述数据库更改的事件，如建表操作或对表数据的更改等。通过设置log-bin系统变量开启二进制日志，MySQL 8中缺省是开启的。

二进制日志有STATEMENT、ROW、MIXED三种格式，通过binlog-format系统变量设置：

• STATMENT
  基于SQL语句的复制（statement-based replication，SBR）。每一条会修改数据的SQL语句会记录到binlog中。
  优点：不需要记录每行的数据变化，这样二进制日志会比较少，减少磁盘I/O，提高性能。
  缺点：在某些情况下会导致主库与从库中的数据不一致，例如last_insert_id()、now()等非确定性函数，以及用户自定义函数（user-defined functions，udf）等易出现问题。
• ROW
  基于行的复制（row-based replication，RBR）。不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子，能清楚记录每一行数据的修改细节。
  优点：不会出现某些特定情况下的存储过程、函数或触发器的调用和触发无法被正确复制的问题。
  缺点：缺点是通常会产生大量的日志，尤其像大表上执行alter table操作时候会让日志暴涨。
• MIXED
  混合复制（mixed-based replication，MBR）。是语句和行两种格式的混合使用，默认使用STATEMENT模式保存二进制日志，对于STATEMENT模式无法正确复制的操作，会自动切换到基于行的格式，MySQL会根据执行的SQL语句选择日志保存方式。MySQL 8 之前的默认格式。

 

二进制日志的存放位置最好设置到与MySQL数据目录不同的磁盘分区，以降低磁盘I/O的竞争，提升性能，并且在数据磁盘故障的时候还可以利用备份和二进制日志恢复数据

 

三、刷盘方式

主从数据是否一致还与重做日志和二进制日志的写盘方式有关。innodb_flush_log_at_trx_commit和sync_binlog系统变量分别控制两者的落盘策略

• innodb_flush_log_at_trx_commit

        控制事物提交是否刷盘，有0、1、2三个可选值。
        0：表示每秒进行一次刷新，但是每次事务提交不进行任何操作
        1：表示每次事务提交都会进行刷新，这是最安全的模式
        2：表示每秒刷新，每次事务提交时不刷新，而是调用write将重做日志缓冲区里面的内容刷到操作系统页面缓存

从数据安全性和性能比较三种策略的优劣为：0表示每秒刷新，每次事务提交不进行任何操作，所以MySQL或操作系统崩溃时最多丢失一秒的事务。1由于每次事务提交都会是重做日志落盘，所以是最安全的，但是由于fsync的次数增多导致性能下降比较严重。2相对于0来说了多了每次事务提交时的一个write操作，此时数据虽然没有落磁，但是只要没有操作系统崩溃，即使MySQL崩溃，那么事务也是不会丢失的。

• sync_binlog

MySQL在提交事务时调用MYSQL_LOG::write完成写二进制日志，并根据sync_binlog决定是否进行刷盘。
        0：由系统决定什么时候刷
        1：每提交一次事务，写一次binlog，并使用fdatasync()同步到硬盘
        n：每提交一次事务，写一次binlog，达到sync_binlog 设定的值后，调用fdatasync()同步到硬盘

sync_binlog=N和innodb_flush_log_at_trx_commit=N推荐配置组合
N=1,1	适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统
N=1,0	适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制
N=2,0或2,m(0<m<100)	适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受
N=0,0	磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务

当两个参数设置为双1的时候，写入性能最差，当俩个参数设置为N(N>1 ),M(0<M<100)时，MySQL的写操作才能达到最高性能

 

四、复制流程

总的来说，MySQL复制有五个步骤：

• 在主库上把数据更改事件记录到二进制日志中
      MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。
• 从库上的I/O线程向主库询问二进制日志中的事件
      从库会启动一个工作线程，称为I/O线程，I/O线程跟主库建立一个普通的客户端连接
• 主库上的binlog dump线程向I/O线程发送二进制事件
      主库上启动一个特殊的二进制日志转储（binlog dump）线程，它会读取主库上二进制日志中的事件，但不会对事件进行轮询。如果从库追赶上了主库，它将进入睡眠状态，直到主库发送信号通知其有新的事件时才会被唤醒。
• 从库上的I/O线程将二进制日志事件复制到自己的中继日志中
      从库I/O线程会将接收到的事件记录到中继日志中
• 从库上的SQL线程读取中继日志中的事件，并将其重放到从库上
      SQL线程执行最后一步，该线程从中继日志中读取事件并在从库上执行。当SQL线程追赶I/O线程时，中继日志通常已经在系统缓存中，所以重放中继日志的开销很低。

 

五、实验

5.1 环境规划

• 虚拟服务器

IP	角色
192.168.56.11	主
192.168.56.12	从

• 软件

软件名称	软件版本
OS	CentOS Linux release 7.3.1611 (Core)
MySQL	mysql-8.0.21

• 路径

路径	用途
/home/mysql/mysql8	MySQL主目录
/home/mysql/mysql8/data	MySQL数据目录
/home/mysql/mysql8/logs/binlog	MySQL二进制日志目录
/home/mysql/mysql8/logs/relaylog	MySQL中继日志目录

5.2 mysql安装

参考 “MySQL安装脚本”

 主库上建立复制专属用户（主库）

-- 主库执行SQL
create user 'repl'@'192.168.56.%' identified with mysql_native_password by '123456';
grant replication client,replication slave on *.* to 'repl'@'192.168.56.%';

5.3 配置复制

复制为异步复制，基于master_log_file和master_log_pos，只做整服务器级别的复制，不考虑对个别库表或使用过滤复制的情况。

5.3.1 停机

如果应用允许一定时间的脱机，这种场景下常用的做法是先直接将主库的数据目录整体拷贝到从库，再启动复制。具体步骤如下：

1、停掉复制涉及的实例（主、从库）

# 主从库都执行
/home/mysql/mysql8/bin/mysqladmin -uroot -p111111 -S/home/mysql/mysql8/logs/mysqld.sock shutdown

2、 将主库的数据目录复制到从库

# 从库执行，删除原文件否则主从关系建立会有问题
rm -fr /home/mysql/mysql8/data
rm -fr /home/mysql/mysql8/logs

# 主库执行，拷贝数据文件
scp -r /home/mysql/mysql8/data root@192.168.56.12:/home/mysql/mysql8/

# 主库执行，如果日志不在data文件夹下，需要吧binlog也拷贝过去
scp -r /home/mysql/mysql8/logs root@192.168.56.12:/home/mysql/mysql8/

# 从库执行，修改权限
chown mysql.mysql /home/mysql/mysql8/* -R

# 从库执行，注意！server_id和server_uuid不能重复，确保server_uuid不重复，删掉auto.cnf
rm -fr /home/mysql/mysql8/data/auto.cnf

3、启动所有实例（主、从）

# 主从库都执行
/home/mysql/mysql8/mysqld start

4、配置主从关系

-- 主库执行SQL，查看binlog信息。
show master status;

mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000006 |      156 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+


-- 从库执行以下SQL,指向主库
CHANGE MASTER TO 
MASTER_HOST='192.168.56.11', 
MASTER_PORT=3308, 
MASTER_USER='repl', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='bin-log.000006', 
MASTER_LOG_POS=156;

5、查看主从关系是否正常（从库）

-- 从库执行SQL，查看主从关系
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
               ...............
                Connect_Retry: 60
              Master_Log_File: bin-log.000006    -- IO线程正在读取主库的binlog
          Read_Master_Log_Pos: 156               -- IO线程正在读取主库binlog的pos
               Relay_Log_File: relay-log.000002  -- 正在应用的本地应用中继日志
                Relay_Log_Pos: 322               -- 中继日志的位置
        Relay_Master_Log_File: bin-log.000006    -- SQL线程正在执行操作对应的master的binlog
             Slave_IO_Running: Yes               -- IO线程状态（No为停止，Yes运行）
            Slave_SQL_Running: Yes               -- SQL线程状态（No为停止，Yes运行）
            ..................                   -- 省略一些不重要状态
          Exec_Master_Log_Pos: 156               -- SQL线程正在执行操作对应的master的pos
          ....................                   -- 省略一些不重要状态
        Seconds_Behind_Master: 0                 -- 从库落后主库时间，单位秒
        ......................                   -- 省略一些不重要状态
1 row in set (0.00 sec)

另外在MySQL的日志中也有主从同步信息记录。

5.3.2 联机

大多数情况下是不允许停机的，配置复制被要求在不影响线上业务的情况下进行，而且还要求对线上库的影响越小越好。这种场景下有两种备选的复制方案：使用mysqldump程序或使用如XtraBackup的第三方工具。

这两种方案有各自的适用场合：

• mysqldump：逻辑备份，恢复速度慢，适用于数据量小的库
      优点：无需额外安装，支持部分复制，replicate-do-table=db1.*用这种方法可以只复制db1库，忽略其它事件
      缺点：生成主库转储数据的SQL语句，占用空间，主要是恢复慢
• xtrabackup：物理备份，适用于数据量大的库
      优点：支持在线热备份，物理备份备份和恢复速度快
      缺点：不支持部分复制，需要额外安装xtrabackup及依赖包

 

5.3.2.1 mysqldump方式

1、安装MySQL，见上文“5.2 MySQL安装”

2、从库配置主库的信息（从库）

-- 从库执行SQL
CHANGE MASTER TO 
MASTER_HOST='192.168.56.11', 
MASTER_PORT=3308, 
MASTER_USER='repl', 
MASTER_PASSWORD='123456';

注意！在上面这条命令中并没有指定主库二进制文件的文件名和位置。

3、用mysqldump建立复制（从库）

# 从库执行
/home/mysql/mysql8/bin/mysqldump \
--single-transaction \
--all-databases \
--master-data=1 \
--host=192.168.56.11 \
--user=admin \
--password=admin \
--apply-slave-statements | \
/home/mysql/mysql8/bin/mysql -uroot -p111111 -S/home/mysql/mysql8/logs/mysqld.sock

• --single-transaction
      对Innodb表执行非锁定导出。
• --master-data
      转储输出包含 CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1480的语句。
      如果选项值为2，则CHANGE MASTER TO语句将写为SQL注释，因此仅提供信息，不会执行。
      如果参数值为1，则该语句不会写为注释，并在重新加载转储文件时执行。
      如果未指定选项值，则默认值为1。
• --apply-slave-statements
      转储输出包含STOP SLAVE语句，在输出结尾处添加START SLAVE语句，自动开启复制。
• 通过管道操作符，导出导入一步进行，不需要中间落盘生成文件

4、确认复制状态，见上文“5.3.1 停机”中的第5步

5.3.2.2 xtrabackup方式

1、安装MySQL，见上文“5.2 MySQL安装”

2、安装xtrabackup（主、从）

# 主库从库都执行

# 下载xtrabackup
cd /usr/local/src
curl -O https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.14/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

# 安装
yum -y install \
perl \
perl-devel \
libaio \
libaio-devel \
perl-Time-HiRes \
perl-DBD-MySQL\
perl-Digest-MD5 \
perl-DBI \
percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

3、主库导出备份

# 主库执行

# 主库xtrabackup备份
xtrabackup -uadmin -padmin --socket=/home/mysql/mysql8/logs/mysqld.sock \
--no-lock --backup --compress --compress-threads=4 \
--target-dir=/home/mysql/mysql8/backup

# 复制到从库
scp -r /home/mysql/mysql8/backup root@192.168.56.12:/home/mysql/mysql8/

注意！ MySQL用户的密码认证方式是mysql_native_password。MySQL8默认认证方式报错

4、从库解压并恢复

# 从库执行

# 解压备份需要安装qpress，如果验证不通过执行下 yum update percona-release
yum -y install https://www.percona.com/redir/downloads/percona-release/redhat/1.0-24/percona-release-1.0-24.noarch.rpm
yum -y install  qpress

# 解压备份
rm -fr /home/mysql/mysql8/data
mv /home/mysql/mysql8/backup /home/mysql/mysql8/data
xtrabackup --decompress --parallel=4 --remove-original --target-dir=/home/mysql/mysql8/data

# 准备备份
xtrabackup --prepare --target-dir=/home/mysql/mysql8/data

# 启动MySQL
chown mysql.mysql /home/mysql/mysql8/* -R

4、配置复制

查看备份中xtrabackup_binlog_info文件，其中记录了binlog-file和binlog-pos

# 从库执行

cat /home/mysql/mysql8/data/xtrabackup_binlog_info

执行命令向主库

-- 从库执行
CHANGE MASTER TO 
MASTER_HOST='192.168.56.11', 
MASTER_PORT=3308, 
MASTER_USER='repl', 
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='bin-log.000021', 
MASTER_LOG_POS=156;

5、确认复制状态，见上文“5.3.1 停机”中的第5步

 

异步复制的几种搭建方式记录完成。