「MySQL」- 双主复制（互为主从、基于GTID复制）

最新推荐文章于 2025-12-03 14:39:35 发布

原创最新推荐文章于 2025-12-03 14:39:35 发布 · 993 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

文章目录

概要

在系统运维过程中，会出现数据容灾的需求，需要增加主从复制功能，但部分需求是需要双向主从复制。

例如：

A库增删改查，B库也需要做对应的操作，B库增删改查，A库也需要做对应的操作。

整体架构流程

环境

操作系统：CentOS Linux release 7.9.2009 (Core)

数据库：mysql_5.7.44-log

技术细节

前提：在A、B服务器分别安装mysql数据库，并创建相同数据库名的数据库，如果是生产环境配置，直接在B服务器安装对应的数据库即可，数据库安装此文章省略，可自行查询其他博主文章，docker和直接安装都可以。

步骤：

修改mysql配置文件
- 默认位置：/etc/my.cnf
  - docker：
    - 查询所有容器：docker ps -a
    - 进入容器命令：docker exec -it <容器id> /bin/bash
    - 远程复制命令：docker cp <本地路径> <容器id>:/etc/
      - ps:容器内没有默认安装vi等编辑工具，可在外部编辑修改后复制进容器（复制命令可以反过来从容器内复制文件出来），再重启即可
- 在[mysqld]下增加以下配置
  - A服务器数据库配置参数：
    - server-id = 1 #修改服务id，多数据不可重复
    - log-bin = mysql-bin #二进制日志文件名
    - auto_increment_offset = 1 #数据库自增id初始值，多数据库一致
    - auto_increment_increment = 2 #数据库自增id步长，两个数据库就分别配置1和2，三个分别配置1、2、3，以此类推，此文章为两个数据库，互为主从
    - gtid_mode = ON #开启GTID复制模式
    - enforce_gtid_consistency = ON #强制所有事务遵守GTID一致性，违反时终止操作
    - log-slave-updates = ON #当参数设置为ON时，从服务器会记录接收到的所有复制事件，并将这些事件写入自己的二进制日志。其他从服务器可通过中继日志继续复制数据，形成多级复制链（如链式复制或星形复制）
    - binlog_format = ROW #以行为单位记录数据变更，保证数据一致性
    - expire-logs-days = 7 #设置二进制日志（binlog）保留天数的参数
    - symbolic-links = 0 #禁止数据库使用符号链接，mysql8.0后默认禁止
    - log-bin-trust-function-creators=1 #开启二进制日志记录存储函数行为的系统变量
    - binlog-do-db = <数据库名> #复制数据库白名单，默认拷贝所有数据库，配置在主库，可不配置，此文章是互为主从，则A、B两个数据库都配置
    - replicate_wild_ignore_table=<数据库>.% #过滤某些库，默认不过滤，配置在从从库，可不配置，此文章是互为主从，则A、B两个数据库都配置
  - B服务器数据库配置参数：
    - server-id = 2 #修改服务id，多数据不可重复
    - log-bin = mysql-bin #二进制日志文件名
    - auto_increment_offset = 2 #数据库自增id初始值，多数据库一致
    - auto_increment_increment = 2 #数据库自增id步长，两个数据库就分别配置1和2，三个分别配置1、2、3，以此类推，此文章为两个数据库，互为主从
    - gtid_mode = ON #开启GTID复制模式
    - enforce_gtid_consistency = ON #强制所有事务遵守GTID一致性，违反时终止操作
    - log-slave-updates = ON #当参数设置为ON时，从服务器会记录接收到的所有复制事件，并将这些事件写入自己的二进制日志。其他从服务器可通过中继日志继续复制数据，形成多级复制链（如链式复制或星形复制）
    - binlog_format = ROW #以行为单位记录数据变更，保证数据一致性
    - expire-logs-days = 7 #设置二进制日志（binlog）保留天数的参数
    - symbolic-links = 0 #禁止数据库使用符号链接，mysql8.0后默认禁止
    - log-bin-trust-function-creators=1 #开启二进制日志记录存储函数行为的系统变量
    - binlog-do-db = <数据库名> #复制数据库白名单，默认拷贝所有数据库，配置在主库，可不配置，此文章是互为主从，则A、B两个数据库都配置
    - replicate_wild_ignore_table=<数据库>.% #过滤某些库，默认不过滤，配置在从从库，可不配置，此文章是互为主从，则A、B两个数据库都配置
  - Tips:数据库配置文件优先生效/etc/my.cnf，如无该文件会生效/etc/mysql/conf.d/my.cnf
- 重启数据库：
  - systemctl restart mysql
  - docker restart <容器ID>
- 查看状态
  - systemctl status mysql
  - docker status <容器ID>
创建复制用户并赋予权限
- 分别在两台数据库执行
  - A库：
    - 创建用户：CREATE USER '<用户名>'@'<B库IP>' IDENTIFIED BY '<用户密码>';
    - 赋予用户复制数据库权限：GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '<用户名>'@'<B库IP>';
    - 赋予用户增删改查数据权限：GRANT SELECT, INSERT, UPDATE, DELETE ON <B数据库库名>.* TO '<用户名>'@'<B库IP>';
    - 生效：FLUSH PRIVILEGES;
  - B库：
    - 创建用户：CREATE USER '<用户名>'@'<A库IP>' IDENTIFIED BY '<用户密码>';
    - 赋予用户复制数据库权限：GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '<用户名>'@'<A库IP>';
    - 赋予用户增删改查数据权限：GRANT SELECT, INSERT, UPDATE, DELETE ON <A数据库库名>.* TO '<用户名>'@'<A库IP>';
    - 生效：FLUSH PRIVILEGES;
- Tips：用户名可一致
拷贝数据库：
- 两种方案：
  - 方案一：
    - 停用应用服务
    - 锁定A库：FLUSH TABLES WITH READ LOCK;
    - 备份A库
    - 在B库导入A库
    - 开启复制（下一步介绍）
    - 解锁A库：UNLOCK TABLES;
    - 启用应用服务
  - 方案二：
    - 在A库的mysql再创建一个<新数据库>
    - 在B库的mysql创建一个相同名称的<新数据库>
    - 开启<新数据库>的复制（下一步介绍）
    - 停用应用服务
    - 锁定A库：FLUSH TABLES WITH READ LOCK;
    - 将A库的所有表、函数和视图复制进A库mysql的<新数据库>（可以使用可视化工具直接复制），同步已开启，A库的mysql里<新数据库>数据，会自动同步至B库的mysql里<新数据库>
    - 解锁A库：UNLOCK TABLES;
    - 将应用服务的数据库连接改为B库
    - 启用应用服务
- 优缺点：
  - 方案一的缺点是A库备份和B库导入都需要比较长的时间，并且期间需要防止对数据进行任何的增改删操作（如果有操作，需要重新对齐全局事务标识符（GTID）），并且有可能出现数据遗漏，优点是保留原库，应用无需切换数据库链接信息
  - 方案二的缺点是应用需要切换至<新数据库>，优点是应用停止和数据库锁库时间较短，因为从同一个mysql的A库复制到<新数据库>，省去数据库备份和导入流程，并且因为是生产sql并直接执行的操作，主从同步会自动同步到B库mysql的<新数据库>
开启复制
- A库：
  - 创建数据库复制：CHANGE MASTER TO
    MASTER_HOST='<B库IP>',
    MASTER_USER='<B库复制用户名>',
    MASTER_PORT=<B库端口>,
    MASTER_PASSWORD='<B库复制用户密码>',
    MASTER_AUTO_POSITION=1;
  - 启动复制：START SLAVE;
  - 查看复制状态：SHOW SLAVE STATUS \G
  - Slave_IO_Running和Slave_SQL_Running皆为YES，Last_SQL_Error为空即为开启成功
- B库：
  - 创建数据库复制：CHANGE MASTER TO
    MASTER_HOST='<A库IP>',
    MASTER_USER='<A库复制用户名>',
    MASTER_PORT=<A库端口>,
    MASTER_PASSWORD='<A库复制用户密码>',
    MASTER_AUTO_POSITION=1;
  - 启动复制：START SLAVE;
  - 查看复制状态：SHOW SLAVE STATUS \G
  - Slave_IO_Running和Slave_SQL_Running皆为YES，Last_SQL_Error为空即为开启成功
- 其他相关命令：
  - 停止复制：STOP SLAVE;
  - 重置复制相关配置：RESET SLAVE ALL;
其他补充
- 对齐全局事务标识符（GTID）
  - 假设B库开启复制时，Last_SQL_Error有报错表数据、表结构不一致等，则需要对其全局事务标识符
    - B库停止复制
    - A库查询当前状态：SHOW MASTER STATUS;
    - B库指定标识符：SET GLOBAL gtid_purged = '<A库查询到的Executed_Gtid_Set值>';
    - B库重新启动复制