基于Docker 部署 MySQL 主从复制

Docker 的安装可参考这篇文章：Linux 安装 Docker；这里的主从复制是基于 GTID(Glbal Transation Identifier)全局事务标识符的。GTID 是 MySQL 5.6 新加入的一项技术，GTID 是完全基于事务的，不支持 MYISAM 存储引擎

Step 1：拉取镜像

docker pull mysql:5.7.40

Step 2：创建主 MySQL 容器

• 创建挂载挂载目录以及 MySQL 配置文件

mkdir -p /usr/local/mysql/data /usr/local/mysql/logs /usr/local/mysql/conf
chmod -R 777 /usr/local/mysql
touch /usr/local/mysql/conf/my.cnf

• 主 my.cnf 配置内容

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
character-set-server=utf8mb4
default-time-zone='+08:00'
max_connections=1024
max_connect_errors=10
default-storage-engine=INNODB
lower_case_table_names = 1
server-id=10
log-bin=/var/log/mysql/binlog
explicit_defaults_for_timestamp=true
# GTID 全局事务配置
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

• 启动

docker run -p 3306:3306 \
    --name mysql \
    --restart=always \
    -e MYSQL_ROOT_PASSWORD=Hwtech8888 \
    -v /usr/local/mysql/conf:/etc/mysql/conf.d \
    -v /usr/local/mysql/logs:/var/log/mysql \
    -v /usr/local/mysql/data:/var/lib/mysql \
    -e TZ=Asia/Shanghai \
    -d mysql:5.7.40

Step 3：主 MySQL 配置

• 进入容器内部

docker exec -it mysql bash

mysql -p
# 输入密码

• 创建同步账号

create user 'slave'@'%' identified by 'Hwtech8888';
grant replication slave on *.* to 'slave'@'%';
flush privileges;

• 设置只读模式

# 避免在此期间有数据变化
set @@global.read_only=ON;

Step 4：创建从 MySQL 容器

我这里用了两台服务器，如果是同一台服务器，主要更改文件夹以及端口号

步骤同上，从 my.cnf 配置内容

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
character-set-server=utf8mb4
default-time-zone='+08:00'
max_connections=1024
max_connect_errors=100
default-storage-engine=INNODB
lower_case_table_names = 1
server-id=100
log-bin=/var/log/mysql/binlog
relay-log=/var/log/mysql/relay-log
explicit_defaults_for_timestamp=true
# GTID 全局事务配置
gtid-mode=on
log-slave-updates=1
enforce-gtid-consistency
skip-slave-start

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

• 启动

docker run -p 3306:3306 \
    --name mysql \
    --restart=always \
    -e MYSQL_ROOT_PASSWORD=Hwtech8888 \
    -v /usr/local/mysql/conf:/etc/mysql/conf.d \
    -v /usr/local/mysql/logs:/var/log/mysql \
    -v /usr/local/mysql/data:/var/lib/mysql \
    -e TZ=Asia/Shanghai \
    -d mysql:5.7.40

Step 5：从 MySQL 配置

• 进入容器内部

docker exec -it mysql bash

mysql -p
# 输入密码

• 配置主服务监听

change master to 
    master_host='101.201.233.67',
    master_user='slave',
    master_password='Hwtech8888',
    master_port=10000,
    master_auto_position=1

• 启动数据同步

start slave;

show slave status\G;