MySQL双主模式（2022/11/19）

MySQL双主模式（2022/11/19）

本文将介绍如何搭建 MySQL 双主模式，互为主从，均可读写。

一、搭建环境

• CentOS Linux release 7.8.2003；
• Docker version 20.10.14；
• MySQL Ver 14.14 Distrib 5.7.40；

二、搭建过程

2.1 MySQL 服务安装

此处笔者两台服务器的 IP 地址分别为 172.20.2.2 与 172.20.2.3，接下来将通过 Docker 分别在两台服务器上启动 MySQL 服务。

# 创建工作目录
$ mkdir -p /data/mysql

$ cd /data/mysql

# 启动 MySQL 服务
$ docker run --name mysql -itd --restart always \
-p 3306:3306 \
-v $PWD/config:/etc/mysql \
-v $PWD/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-e LANG=C.UTF-8 \
-e TZ=Asia/Shanghai \
--log-driver json-file \
--log-opt max-size=1g \
--log-opt max-file=3 \
mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci --lower_case_table_names=1

2.2 binlog 开启

# 在 172.20.2.2. 节点进行以下配置
# 编辑 $PWD/config/mysql.conf.d/mysqld.cnf 文件，不存在则手动创建，添加以下内容
$ mkdir -p $PWD/config/mysql.conf.d && vim $PWD/config/mysql.conf.d/mysqld.cnf
[mysqld]
server-id=1

auto_increment_offset=1
auto_increment_increment=2

binlog-format=row
expire_logs_days=7
log-bin=/var/lib/mysql/mysql-bin.log
log-slave-updates=true
sync_binlog=0

gtid-mode=on
enforce-gtid-consistency=true

replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# 重启 MySQL
$ docker restart mysql

# 在 172.20.2.3 节点进行以下配置
# 编辑 $PWD/config/mysql.conf.d/mysqld.cnf 文件，不存在则手动创建，添加以下内容
$ mkdir -p $PWD/config/mysql.conf.d && vim $PWD/config/mysql.conf.d/mysqld.cnf
[mysqld]
server-id=2

auto_increment_offset=2
auto_increment_increment=2

binlog-format=row
expire_logs_days=7
log-bin=/var/lib/mysql/mysql-bin.log
log-slave-updates=true
sync_binlog=0

gtid-mode=on
enforce-gtid-consistency=true

replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

# 重启 MySQL
$ docker restart mysql

注意：

1. server-id 为每个节点的标识，不可重复；
2. auto_increment_offset 与 auto_increment_increment 指定了 172.20.2.2 节点自增主键为奇数、172.20.2.3 节点自增主键为偶数，避免使用自增主键两节点同时写入时主键产生冲突；

2.3 创建节点用户

# 在 172.20.2.2 节点进行以下配置
$ docker exec -it mysql mysql -uroot -p
> CREATE USER master1@172.20.2.3 IDENTIFIED BY '123456';
> GRANT REPLICATION SLAVE ON *.* TO master1@172.20.2.3;
> FLUSH PRIVILEGES;

# 在 172.20.2.3 节点进行以下配置
$ docker exec -it mysql mysql -uroot -p
> CREATE USER master2@172.20.2.2 IDENTIFIED BY '123456';
> GRANT REPLICATION SLAVE ON *.* TO master2@172.20.2.2;
> FLUSH PRIVILEGES;

2.4 关联主从节点

# 在 172.20.2.2 节点进行以下配置
$ docker exec -it mysql mysql -uroot -p
> CHANGE MASTER TO MASTER_HOST='172.20.2.3', MASTER_PORT=3306, MASTER_USER='master2', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1;
> START SLAVE;
> SHOW SLAVE STATUS\G;
# 看到以下输出表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# 在 172.20.2.3 节点进行以下配置
$ docker exec -it mysql mysql -uroot -p
> CHANGE MASTER TO MASTER_HOST='172.20.2.2', MASTER_PORT=3306, MASTER_USER='master1', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1;
> START SLAVE;
> SHOW SLAVE STATUS\G;
# 看到以下输出表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

2.5 测试是否成功

# 在 172.20.2.2 节点创建 test 数据库
$ docker exec -it mysql mysql -uroot -p
> CREATE DATABASE IF NOT EXISTS `test` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 在 172.20.2.3 节点查看是否存在 test 数据库
$ docker exec -it mysql mysql -uroot -p
> SHOW DATABASES;

# 在 172.20.2.3 节点删除 test 数据库
$ docker exec -it mysql mysql -uroot -p
> DROP TABLE IF EXISTS `test`;

# 在 172.20.2.2 节点查看是否还存在 test 数据库
$ docker exec -it mysql mysql -uroot -p
> SHOW DATABASES;

参考链接：

• MySQL双主（主主）架构方案 - ygqygq2 - 博客园 (cnblogs.com)
• Mysql主从同步的实现原理与配置实战 - 知乎 (zhihu.com)
• MySQL主从同步详解与配置 - 知乎 (zhihu.com)
• slave复制进程不随mysql启动而启动 skip-slave-start参数_aeolus_pu的博客-CSDN博客
• MySQL主从复制，启动slave时报错Slave failed to initialize relay log info structure from the repository_无知的蜗牛的博客-CSDN博客_data for column ‘relay_log_name’ too long
• 故障案例：主从同步报错Fatal error: The slave I/O thread stops because master and slave have equal MySQL server_胡儿胡儿的博客-CSDN博客