一、原理

主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主

服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可

充当从机。MySQL 主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过

主服务器的二进制日志自动执行更新。

主数据库（Master）：负责处理写操作和更新数据，是业务操作的主要入口。

从数据库（Slave）：复制主数据库的数据，通常用于读操作和数据备份，以减轻主数据库的

压力。

二、流程

[root@docker ~]# mkdir /data/master/conf -p
[root@docker conf]# cd /data/master/conf/

2.查看容器

[root@docker conf]# docker ps

CONTAINER ID IMAGE COMMAND CREATED

STATUS PORTS

NAMES

4c94e107f065 mysql:9.3.0 "docker-entrypoint.s…" 2 seconds ago

Up 2 seconds 33060/tcp, 0.0.0.0:3306->3306/tcp, [::]:3306->3306/tcp

mysql-master

五、创建同步用户

1.进入主服务器数据库

[root@docker master]# docker exec -it mysql-master mysql -uroot - p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input

statement.

2.创建专门用于复制的用户

mysql> CREATE USER 'backups'@'%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.02 sec)

3.授予此账户复制所需要的权限并刷新权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backups'@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW BINARY LOG STATUS;

+---------------+----------+--------------+------------------+--------

-----------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

Executed_Gtid_Set |

+---------------+----------+--------------+------------------+--------

-----------+

| binlog.000003 | 864 | | |

|

+---------------+----------+--------------+------------------+--------

-----------+

1 row in set (0.000 sec)

mysql> SHOW BINARY LOG STATUS\G;

*************************** 1. row ***************************

File: binlog.000003

Position: 864

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.000 sec)

ERROR:

No query specified

六、配置从服务器

[root@docker data]# mkdir /data/slave/conf -p

[root@docker data]# cd /data/slave/conf/

[root@docker conf]# vim my.cnf

[mysqld]

# mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2的32次方-1，默认为1

server-id=2

# 是否只读，1 代表当前服务器只读(不能做增删改操作), 0代表读写

read-only=1

host-cache-size=0

skip-name-resolve

# mysql数据文件所在位置

datadir=/var/lib/mysql

# 设置socke文件所在目录

socket=/var/run/mysqld/mysqld.sock

secure-file-priv=/var/lib/mysql-files

user=mysql

# pid

pid-file=/var/run/mysqld/mysqld.pid

[client]

socket=/var/run/mysqld/mysqld.sock

七、创建从服务器

1.运行容器

[root@docker slave]# docker run -d \

--name mysql-slave \

-e MYSQL_ROOT_PASSWORD=123456 \

-v /data/slave/conf/my.cnf:/etc/my.cnf \

-p 3307:3306 \

mysql:9.3.0 \

--character-set-server=utf8mb4 \

--collation-server=utf8mb4_unicode_ci

注释：

  1.docker run：创建并启动 Docker 容器的核心命令

1. -d：以守护进程模式（后台）运行容器，并返回容器 ID

2. --name mysql-slave：指定容器名称为mysql-slave，便于识别和管理（与主库的mysql-master对应）

3. -e MYSQL_ROOT_PASSWORD=123456：设置环境变量，指定 MySQL 的 root 用户密码为 123456（建议与主库保持一致，便于管理）

4. -v /data/slave/conf/my.cnf:/etc/my.cnf：

   • 将宿主机的/data/slave/conf/my.cnf配置文件挂载到容器内的/etc/my.cnf

   • 用于从库的自定义配置（通常会包含与主库同步相关的配置）

5. -p 3307:3306：端口映射，将宿主机的 3307 端口映射到容器内的 3306 端口

   • 与主库的 3306 端口区分开，避免端口冲突

6. mysql:9.3.0：指定使用的 MySQL 镜像版本（需与主库版本保持一致，避免同步问题）

7. 最后的--character-set-server=utf8mb4和--collation-server=utf8mb4_unicode_ci：

   • 设置 MySQL 服务器的默认字符集为 utf8mb4（支持所有 Unicode 字符，包括 emoji）

   • 设置默认排序规则为 utf8mb4_unicode_ci（通用的 Unicode 排序规则）

2.查看容器

[root@docker master]# docker ps

CONTAINER ID IMAGE COMMAND CREATED

STATUS PORTS

NAMES

46ea3315dae5 mysql:9.3.0 "docker-entrypoint.s…" 3 seconds ago

Up 3 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, [::]:3307->3306/tcp

mysql-slave

507b10d78ec3 mysql:9.3.0 "docker-entrypoint.s…" 24 seconds ago

Up 24 seconds 0.0.0.0:3306->3306/tcp, [::]:3306->3306/tcp, 33060/tcp

mysql-maste

八、配置启动从库

1.进入从服务器容器数据库

[root@docker master]# docker exec -it mysql-slave mysql -uroot -

p123456

mysql: [Warning] Using a password on the command line interface can be

insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 9.3.0 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input

statement

2.配置MySQL主从复制

mysql> CHANGE REPLICATION SOURCE TO

SOURCE_HOST='192.168.72.135',SOURCE_USER='backups',SOURCE_PASSWORD='12

3456',SOURCE_LOG_FILE='binlog.000003',SOURCE_LOG_POS=864,GET_SOURCE_PU

BLIC_KEY=1;

Query OK, 0 rows affected, 2 warnings (0.011 sec)

3.执行语句并开启同步

mysql> START REPLICA;

Query OK, 0 rows affected (0.02 sec)

4.查看复制状态

mysql> SHOW REPLICA STATUS\G;

*************************** 1. row ***************************

Replica_IO_State: Waiting for source to send event

Source_Host: 192.168.72.135

Source_User: backups

Source_Port: 3306

Connect_Retry: 60

Source_Log_File: binlog.000003

Read_Source_Log_Pos: 864

Relay_Log_File: 46ea3315dae5-relay-bin.000002

Relay_Log_Pos: 325

Relay_Source_Log_File: binlog.000003

Replica_IO_Running: Yes

Replica_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

........

Source_public_key_path:

Get_Source_public_key: 1

Network_Namespace:

1 row in set (0.000 sec)

ERROR:

No query specified

九、主从复制测试

mysql> create database mydb;

Query OK, 1 row affected (0.01 sec)

mysql> use mydb;

Database changed

mysql> create table t1(id int,name varchar(20));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(id,name) values(1, 'zhangsan');

Query OK, 1 row affected (0.08 sec)

mysql> insert into t1(id,name) values(2, 'lisi');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(id,name) values(3, 'wangwu');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+------+----------+

| id | name |

+------+----------+

| 1 | zhangsan |

| 2 | lisi |

| 3 | wangwu |

+------+----------+

3 rows in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mydb |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.004 sec)

mysql> use mydb;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from t1;

+------+----------+

| id | name |

+------+----------+

| 1 | zhangsan |

+------+----------+

1 row in set (0.000 sec)