MySQL主从复制-异步复制

陈曦
Page content

复制是MySQL数据库提供的一种高可用高性能解决方案，一般用来建立大型应用（《MySQL技术内幕：innodb存储引擎（第二版）》）。

常见的主从复制方案有：异步复制、半同步复制、组复制。本文主要介绍传统的异步复制方式。

1. 环境准备

操作系统：macOS Big Sur 11.0.1

MySQL: mysql 5.7

为方便操作，这里使用docker来进行配置。配置3个MySQL实例，用于后续进行主从复制实验（1主2从）。

1.1. 拉取MySQL 5.7的镜像

docker pull mysql:5.7

1.2. 启动MySQL实例

  1. 启动 master 实例
docker run -d \
-p 3307:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
-v ~/tools/mysql/master/data:/var/lib/mysql \
-v ~/tools/mysql/master/conf.d:/etc/mysql/conf.d \
--name mysql-5.7-master \
mysql:5.7

将MySQL数据文件挂在到本地目录 ~/tools/mysql/master/data，这样数据文件就可以直接在本地查看。

将MySQL配置文件目录挂在到本地目录 ~/tools/mysql/master/conf.d，这样就可以在本地自定义配置文件，而不需要每次修改配置都登入容器内部。

自定义配置文件 my-custom.cnf 如下，

[mysqld]
server_id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
log_bin=mysql-bin
binlog-format=Row
  1. 启动 slave01 实例
docker run -d \
-p 3317:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
-v ~/tools/mysql/slave01/data:/var/lib/mysql \
-v ~/tools/mysql/slave01/conf.d:/etc/mysql/conf.d \
--name mysql-5.7-slave01 \
mysql:5.7

自定义配置文件 my-custom.cnf 如下，

[mysqld]
server_id = 3

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
log_bin=mysql-bin
binlog-format=Row
  1. 启动 slave02 实例
docker run -d \
-p 3327:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-e TZ=Asia/Shanghai \
-v ~/tools/mysql/slave02/data:/var/lib/mysql \
-v ~/tools/mysql/slave02/conf.d:/etc/mysql/conf.d \
--name mysql-5.7-slave02 \
mysql:5.7

自定义配置文件 my-custom.cnf 如下，

[mysqld]
server_id = 4

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
log_bin=mysql-bin
binlog-format=Row

使用 docker ps -a | grep mysql 查看已有的MySQL容器。下图中可以看到已经启动了3个MySQL实例。

img.png

2. 传统的复制（异步复制 ）

什么是异步复制？

2.1. 在主库中创建用于复制的账号

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;

2.2. 查看主库状态

show master status;

查询结果如下，可以看到当前数据库的binlog文件以及当前的位置，需要记住当前的position值，后面配置从库时需要使用。

+-----------------+----------------+
|File             |mysql-bin.000013|
+-----------------+----------------+
|Position         |351             |
+-----------------+----------------+
|Binlog_Do_DB     |                |
+-----------------+----------------+
|Binlog_Ignore_DB |                |
+-----------------+----------------+
|Executed_Gtid_Set|                |
+-----------------+----------------+

2.3. 配置从库 slave01

  1. 使用在主库中创建的复制账号连接主库
CHANGE MASTER TO
MASTER_HOST='172.17.0.2', # 主库ip
MASTER_PORT = 3306, # 连接主库的端口
MASTER_USER='repl', # 主库的复制账号
MASTER_PASSWORD='123456', # 主库的复制账号密码
MASTER_LOG_FILE='mysql-bin.000013', # 主库的binlog文件
MASTER_LOG_POS=351; # 主库的binlog偏移量

在docker环境中，可以使用如下命令查询主库的ip

docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-5.7-master
  1. 启动从库同步
start slave;
  1. 查看同步状态
show slave status;

可以看到如下输出

+-----------------------------+------------------------------------------------------+
|Slave_IO_State               |Waiting for master to send event                      | # 从库IO线程的状态，等待主库发送事件
+-----------------------------+------------------------------------------------------+
|Master_Host                  |172.17.0.2                                            |
+-----------------------------+------------------------------------------------------+
|Master_User                  |repl                                                  |
+-----------------------------+------------------------------------------------------+
|Master_Port                  |3306                                                  |
+-----------------------------+------------------------------------------------------+
|Connect_Retry                |60                                                    |
+-----------------------------+------------------------------------------------------+
|Master_Log_File              |mysql-bin.000013                                      |# 主库的binlog文件
+-----------------------------+------------------------------------------------------+
|Read_Master_Log_Pos          |351                                                   |# 主库的binlog偏移量
+-----------------------------+------------------------------------------------------+
|Relay_Log_File               |d7ffdbdbdf3a-relay-bin.000002                         |# 从库relaylog（中继日志）文件
+-----------------------------+------------------------------------------------------+
|Relay_Log_Pos                |517                                                   |# 从库relaylog（中继日志）偏移量
+-----------------------------+------------------------------------------------------+
|Relay_Master_Log_File        |mysql-bin.000013                                      |
+-----------------------------+------------------------------------------------------+
|Slave_IO_Running             |Yes                                                   |# 从库IO线程是否运行
+-----------------------------+------------------------------------------------------+
|Slave_SQL_Running            |Yes                                                   |# 从库SQL线程是否运行
+-----------------------------+------------------------------------------------------+
|Replicate_Do_DB              |                                                      |
+-----------------------------+------------------------------------------------------+
|Replicate_Ignore_DB          |                                                      |
+-----------------------------+------------------------------------------------------+
|Replicate_Do_Table           |                                                      |
+-----------------------------+------------------------------------------------------+
|Replicate_Ignore_Table       |                                                      |
+-----------------------------+------------------------------------------------------+
|Replicate_Wild_Do_Table      |                                                      |
+-----------------------------+------------------------------------------------------+
|Replicate_Wild_Ignore_Table  |                                                      |
+-----------------------------+------------------------------------------------------+
|Last_Errno                   |0                                                     |
+-----------------------------+------------------------------------------------------+
|Last_Error                   |                                                      |
+-----------------------------+------------------------------------------------------+
|Skip_Counter                 |0                                                     |
+-----------------------------+------------------------------------------------------+
|Exec_Master_Log_Pos          |351                                                   |
+-----------------------------+------------------------------------------------------+
|Relay_Log_Space              |731                                                   |
+-----------------------------+------------------------------------------------------+
|Until_Condition              |None                                                  |
+-----------------------------+------------------------------------------------------+
|Until_Log_File               |                                                      |
+-----------------------------+------------------------------------------------------+
|Until_Log_Pos                |0                                                     |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Allowed           |No                                                    |
+-----------------------------+------------------------------------------------------+
|Master_SSL_CA_File           |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_SSL_CA_Path           |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Cert              |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Cipher            |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Key               |                                                      |
+-----------------------------+------------------------------------------------------+
|Seconds_Behind_Master        |0                                                     |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Verify_Server_Cert|No                                                    |
+-----------------------------+------------------------------------------------------+
|Last_IO_Errno                |0                                                     |
+-----------------------------+------------------------------------------------------+
|Last_IO_Error                |                                                      |
+-----------------------------+------------------------------------------------------+
|Last_SQL_Errno               |0                                                     |
+-----------------------------+------------------------------------------------------+
|Last_SQL_Error               |                                                      |
+-----------------------------+------------------------------------------------------+
|Replicate_Ignore_Server_Ids  |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_Server_Id             |1                                                     |
+-----------------------------+------------------------------------------------------+
|Master_UUID                  |6c5c3a18-32f9-11eb-b82b-0242ac110002                  |
+-----------------------------+------------------------------------------------------+
|Master_Info_File             |/var/lib/mysql/master.info                            |
+-----------------------------+------------------------------------------------------+
|SQL_Delay                    |0                                                     |
+-----------------------------+------------------------------------------------------+
|SQL_Remaining_Delay          |NULL                                                  |
+-----------------------------+------------------------------------------------------+
|Slave_SQL_Running_State      |Slave has read all relay log; waiting for more updates|# 从库SQL线程状态，已经读取了所有relaylog，等待处理后续更新
+-----------------------------+------------------------------------------------------+
|Master_Retry_Count           |86400                                                 |
+-----------------------------+------------------------------------------------------+
|Master_Bind                  |                                                      |
+-----------------------------+------------------------------------------------------+
|Last_IO_Error_Timestamp      |                                                      |
+-----------------------------+------------------------------------------------------+
|Last_SQL_Error_Timestamp     |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Crl               |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_SSL_Crlpath           |                                                      |
+-----------------------------+------------------------------------------------------+
|Retrieved_Gtid_Set           |                                                      |
+-----------------------------+------------------------------------------------------+
|Executed_Gtid_Set            |                                                      |
+-----------------------------+------------------------------------------------------+
|Auto_Position                |0                                                     |
+-----------------------------+------------------------------------------------------+
|Replicate_Rewrite_DB         |                                                      |
+-----------------------------+------------------------------------------------------+
|Channel_Name                 |                                                      |
+-----------------------------+------------------------------------------------------+
|Master_TLS_Version           |                                                      |
+-----------------------------+------------------------------------------------------+

2.4. 配置从库 slave02

同 2.3. 配置从库 slave01

2.5. 测试异步主从复制

2.5.1. 测试前主库和从库的状态

查看从库中目前的数据库

show databases;

可以看到如下输出，处理MySQL自带的数据库，暂时没有其他数据库。

+------------------+
|Database          |
+------------------+
|information_schema|
|mysql             |
|performance_schema|
|sys               |
+------------------+

2.5.2. 修改主库，并查看从库同步状态

  1. 在主库中创建数据库 db_0。
create database db_0;

在从库中查看是否有 db_0 库。

show databases;

+------------------+
|Database          |
+------------------+
|information_schema|
|db_0              | # 新增了db_0
|mysql             |
|performance_schema|
|sys               |
+------------------+
  1. 在主库的 db_0 库中新建数据表 t_01。
CREATE TABLE `t_01` (
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    `name` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '姓名',
    PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

在主库中查看数据表，可以看到执行建表语句成功，新增 t_01 表。

show tables;

在从库中查看数据表，观察到表 t_01 也新建成功。

use db_0;
show tables;
  1. 在主库的 db_0.t_01 表中进行增删改查操作。

此前的操作并未在 t_01 表中增加数据，所以不论是主库还是从库都没有数据。

INSERT INTO `t_01` (name)
VALUES ('chenxi0'),
       ('chenxi1'),
       ('chenxi2');

在主库和从库执行查询，可以看到3条数据被成功插入。

SELECT *
FROM `t_01`;

+--+-------+
|id|name   |
+--+-------+
|1 |chenxi0|
|2 |chenxi1|
|3 |chenxi2|
+--+-------+

在主库执行 UPDATE 操作之后，从库 db_0.t_01 表中id=3的记录被修改。

UPDATE `t_01`
SET `name` = 'chenxi4'
WHERE `id` = 3;

2.6. 测试环境恢复

在测试完异步主从同步之后，需要停止 slave01 和 slave02 的从库同步。

执行如下命令。

stop slave;

可以执行如下命令检查是否停止成功。

show slave status;

可以看到 Slave_IO_Running 和 Slave_SQL_Running 都已经是 No 状态，说明同步已经停止。


+-----------------+--+
|Slave_IO_Running |No|
+-----------------+--+
|Slave_SQL_Running|No|
+-----------------+--+