搭建MySQL主从复制

本文最后更新于：2025年5月20日 下午

Docker部署MySQL主从复制

MySQL8.0 主从集群

docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
name: mysql-ms
services:
  master:
    image: mysql:8.0.36
    container_name:  master
    hostname: host_01
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    ports:
      - "33065:3306"
    networks:
      mysql-network:
        ipv4_address: 172.36.0.15
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/conf.d:/etc/mysql/conf.d


  slave_1:
    image: mysql:8.0.36
    container_name: slave_1
    hostname: host_02
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    ports:
      - "33066:3306"
    networks:
      mysql-network:
        ipv4_address: 172.36.0.16
    volumes:
      - ./slave1/data:/var/lib/mysql
      - ./slave1/conf.d:/etc/mysql/conf.d

  slave_2:
    image: mysql:8.0.36
    container_name: slave_2
    hostname: host_03
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    ports:
      - "33067:3306"
    networks:
      mysql-network:
        ipv4_address: 172.36.0.17
    volumes:
      - ./slave2/data:/var/lib/mysql
      - ./slave2/conf.d:/etc/mysql/conf.d


networks:
  mysql80-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.36.0.0/24

步骤 1：创建容器

1
2
# 在docker-compose.yml文件目录下
docker compose up -d

步骤 2：修改配置

进入容器系统，查找配置文件位置

1
docker exec -it master /bin/bash

一般路径：/etc/my.cnf

主库配置：

复制Docker内的主库的配置文件到主机当前目录

1
docker cp master:/etc/my.cnf .

修改配置内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
# 1号数据库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

server-id=1
log_bin=mysql-bin
binlog_format=ROW
default_authentication_plugin=mysql_native_password
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON

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

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

将修改后的配置文件放回主库

1
docker cp my.cnf master:/etc/my.cnf
1 号从库配置：

复制Docker内的 1 号从库的配置文件到主机当前目录

1
docker cp slave_1:/etc/my.cnf .

修改配置内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# 2号数据库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql



server-id=2
log_bin=mysql-bin
relay-log=mysql-relay-bin
binlog_format=ROW
read_only=1 # 从服务器只读
default_authentication_plugin=mysql_native_password
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON

log_replica_updates=1  # 如果从服务器可能成为其他服务器的主服务器
relay_log_recovery=1  # 启用中继日志恢复

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

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

将修改后的配置文件放回 1 号从库

1
docker cp my.cnf slave_1:/etc/my.cnf
2 号从库配置：

复制Docker内的 2 号从库的配置文件到主机当前目录

1
docker cp slave_2:/etc/my.cnf .

修改配置内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
# 3号数据库配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql



server-id=3
log_bin=mysql-bin
relay-log=mysql-relay-bin
binlog_format=ROW
read_only=1 # 从服务器只读
default_authentication_plugin=mysql_native_password
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON

log_replica_updates=1  # 如果从服务器可能成为其他服务器的主服务器
relay_log_recovery=1  # 启用中继日志恢复

# 排除不同步的数据库
replicate-ignore-db=information_schema 
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

将修改后的配置文件放回 2 号从库

1
docker cp my.cnf slave_2:/etc/my.cnf

步骤 3：重启容器

1
2
# 重启MySQL
docker restart master slave_1 slave_2

步骤 4：配置主从复制

方式一：GTID主从复制

主库

1
2
3
4
5
6
7
8
9
10
# 创建复制用户
CREATE USER 'replica'@'%' IDENTIFIED BY '123';
# 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO replica@'%';
# 更改验证方式为本地密码验证
ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY '123';
# 刷新权限
FLUSH PRIVILEGES;
-- 查看主服务器状态，记录File和Position值
SHOW MASTER STATUS;

从库

1
2
3
4
5
6
7
8
9
10
11
12
-- 停止从库复制
STOP SLAVE;

-- 配置主库连接信息（使用GTID自动定位）
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='replica',
MASTER_PASSWORD='123',
MASTER_AUTO_POSITION = 1;  # 关键参数，启用GTID自动定位

-- 启动复制
START SLAVE;
方式二：传统主从复制

主库：

1
2
3
4
5
6
7
8
9
10
# 创建复制用户
CREATE USER 'replica'@'%' IDENTIFIED BY '123';
# 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO replica@'%';
# 更改验证方式为本地密码验证
ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY '123';
# 刷新权限
FLUSH PRIVILEGES;
-- 查看主服务器状态，记录File和Position值
SHOW MASTER STATUS;

从库：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 停止从库复制
STOP SLAVE;

-- 配置主库连接信息（使用GTID自动定位）
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='replica',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000007', -- 对应主库状态中的file
MASTER_LOG_POS=157;-- 对应主库状态中的position

-- 启动复制
START SLAVE;

步骤 5：测试主从同步

主库

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建数据库和数据表并新增数据，在其他从属数据库查看复制状态
create DATABASE `db_01`;
use db_01;
CREATE TABLE tb_user(
	id int(11) AUTO_INCREMENT PRIMARY key not null ,
	name varchar(50) not null
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4;
# 插入数据
insert into tb_user(id,name)VALUES(null,'Tom'),(null,'Jerry');
# 修改数据
update tb_user set name='Peter' where id =1;
# 删除数据
delete from tb_user where id = 2;

从库

1
2
use db_01;
select * from tb_user;

MySQL5.7 主从集群

docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
name: mysql57-group
services:
  mysql57_1:
    image: mysql:5.7.22
    container_name:  mysql57_1
    hostname: mysql57_1
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    ports:
      - "33065:3306"
    networks:
      mysql-network:
        ipv4_address: 172.37.0.15
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/conf.d:/etc/mysql/conf.d


  mysql57_2:
    image: mysql:5.7.22
    container_name: mysql57_2
    hostname: mysql57_2
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    ports:
      - "33066:3306"
    networks:
      mysql-network:
        ipv4_address: 172.37.0.16
    volumes:
      - ./slave1/data:/var/lib/mysql
      - ./slave1/conf.d:/etc/mysql/conf.d

  mysql57_3:
    image: mysql:5.7.22
    container_name: mysql57_3
    hostname: mysql57_3
    environment:
      MYSQL_ROOT_PASSWORD: root
      TZ: Asia/Shanghai
    ports:
      - "33067:3306"
    networks:
      mysql-network:
        ipv4_address: 172.37.0.17
    volumes:
      - ./slave2/data:/var/lib/mysql
      - ./slave2/conf.d:/etc/mysql/conf.d


networks:
  mysql57-network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.37.0.0/24

步骤 1：创建容器

1
2
# 在docker-compose.yml文件目录下
docker compose up -d

步骤 2：修改配置

进入容器系统，查找配置文件位置

1
docker exec -it mysql57_1 bash

一般路径：/etc/mysql/mysql.conf.d/mysqld.cnf

主库配置：

复制Docker内的主库的配置文件到主机当前目录

1
docker cp mysql57_1:/etc/mysql/mysql.conf.d/mysqld.cnf .

修改配置内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log_bin=mysql-bin
binlog_format=ROW
default_authentication_plugin=mysql_native_password
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON

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

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

将修改后的配置文件放回主库

1
docker cp mysqld.cnf mysql57_1:/etc/mysql/mysql.conf.d/mysqld.cnf
1 号从库配置：

复制Docker内的 1 号从库的配置文件到主机当前目录

1
docker cp mysql57_2:/etc/mysql/mysql.conf.d/mysqld.cnf .

修改配置内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


server-id=2
log_bin=mysql-bin
relay-log=mysql-relay-bin
binlog_format=ROW
read_only=1 # 从服务器只读
default_authentication_plugin=mysql_native_password
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON

#log_replica_updates=1  # 如果从服务器可能成为其他服务器的主服务器
relay_log_recovery=1  # 启用中继日志恢复

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

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

将修改后的配置文件放回 1 号从库

1
docker cp mysqld.cnf mysql57_2:/etc/mysql/mysql.conf.d/mysqld.cnf
2 号从库配置：

复制Docker内的 2 号从库的配置文件到主机当前目录

1
docker cp mysql57_3:/etc/mysql/mysql.conf.d/mysqld.cnf .

修改配置内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=3
log_bin=mysql-bin
relay-log=mysql-relay-bin
binlog_format=ROW
read_only=1 # 从服务器只读
default_authentication_plugin=mysql_native_password
gtid_mode=ON
# 强制GTID一致性
enforce_gtid_consistency=ON

#log_replica_updates=1  # 如果从服务器可能成为其他服务器的主服务器
relay_log_recovery=1  # 启用中继日志恢复

# 排除不同步的数据库
replicate-ignore-db=information_schema 
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

将修改后的配置文件放回 2 号从库

1
docker cp mysqld.cnf mysql57_3:/etc/mysql/mysql.conf.d/mysqld.cnf

步骤 3：重启容器

1
2
# 重启MySQL
docker restart mysql57_1 mysql57_2 mysql57_3

步骤 4：配置主从复制

方式一：GTID主从复制

主库

1
2
3
4
5
6
7
8
9
10
# 创建复制用户
CREATE USER 'replica'@'%' IDENTIFIED BY '123';
# 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO replica@'%';
# 更改验证方式为本地密码验证
ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY '123';
# 刷新权限
FLUSH PRIVILEGES;
-- 查看主服务器状态，记录File和Position值
SHOW MASTER STATUS;

从库

1
2
3
4
5
6
7
8
9
10
11
12
-- 停止从库复制
STOP SLAVE;

-- 配置主库连接信息（使用GTID自动定位）
CHANGE MASTER TO
MASTER_HOST='mysql57_1',
MASTER_USER='replica',
MASTER_PASSWORD='123',
MASTER_AUTO_POSITION = 1;  # 关键参数，启用GTID自动定位

-- 启动复制
START SLAVE;
方式二：传统主从复制

主库：

1
2
3
4
5
6
7
8
9
10
# 创建复制用户
CREATE USER 'replica'@'%' IDENTIFIED BY '123';
# 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO replica@'%';
# 更改验证方式为本地密码验证
ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY '123';
# 刷新权限
FLUSH PRIVILEGES;
-- 查看主服务器状态，记录File和Position值
SHOW MASTER STATUS;

从库：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 停止从库复制
STOP SLAVE;

-- 配置主库连接信息（使用GTID自动定位）
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='replica',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000007', -- 对应主库状态中的file
MASTER_LOG_POS=157;-- 对应主库状态中的position

-- 启动复制
START SLAVE;

步骤 5：测试主从同步

主库

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建数据库和数据表并新增数据，在其他从属数据库查看复制状态
create DATABASE `db_01`;
use db_01;
CREATE TABLE tb_user(
	id int(11) AUTO_INCREMENT PRIMARY key not null ,
	name varchar(50) not null
)ENGINE = INNODB DEFAULT CHARSET=utf8mb4;
# 插入数据
insert into tb_user(id,name)VALUES(null,'Tom'),(null,'Jerry');
# 修改数据
update tb_user set name='Peter' where id =1;
# 删除数据
delete from tb_user where id = 2;

从库

1
2
use db_01;
select * from tb_user;

主从复制
#mysql
搭建MySQL主从复制
https://superlovelace.top/2025/03/27/MySQL主从复制/
作者
棱境
发布于
2025年3月27日
更新于
2025年5月20日
许可协议