docker配置mysql主从同步

1. 创建Docker网络

docker network create mysql-network

 2. 创建数据卷

docker volume create mysql-master-volume
docker volume create mysql-slave-volume

 3. 准备MySQL配置文件

 主库配置 (master.cnf)

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW
gtid_mode=ON   #参数启用了 GTID（全局事务标识）模式
enforce_gtid_consistency=ON #参数用于强制执行 GTID 一致性

 从库配置 (slave.cnf)

[mysqld]
server-id=2
relay-log=mysql-relay-bin
read_only=1
gtid_mode=ON
enforce_gtid_consistency=ON

 4. 启动主库容器

docker run -d \
  --name mysql-master \
  --network mysql-network \  # 若使用自定义网络
  -v mysql-master-volume:/var/lib/mysql \
  -v ./master.cnf:/etc/mysql/conf.d/master.cnf \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3306:3306 \
  mysql:8.0.33

 5. 在主库创建复制用户

进入主库容器：

docker exec -it mysql-master mysql -uroot -proot

执行SQL：

CREATE USER 'replicator'@'%' IDENTIFIED  BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

 6. 获取主库的binlog状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      879 |              |                  | 4529fb73-2ed5-11f0-a015-0242ac120002:1-8 |

7. 启动从库容器 

docker run -d \
  --name mysql-slave \
  --network mysql-network \  # 与主库同一网络
  -v mysql-slave-volume:/var/lib/mysql \
  -v ./slave.cnf:/etc/mysql/conf.d/slave.cnf \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3307:3306 \  # 映射不同端口避免冲突
  mysql:8.0.33

8. 配置从库复制

 进入从库容器：

docker exec -it mysql-slave mysql -uroot -proot

 执行SQL（替换主库IP、端口、文件名和位置）：

CHANGE MASTER TO
MASTER_HOST='',  # 若使用Docker网络，使用容器名称如 mysql-master
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=879;

START SLAVE;

9. 验证复制状态 

在从库中执行：

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 879
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:

 10. 测试主从同步

在主库插入测试数据：

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test
Database changed
mysql> CREATE TABLE test_table (id INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test_table VALUES (1);
Query OK, 1 row affected (0.02 sec)

在从库检查数据：

mysql> SELECT * FROM test.test_table;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.08 sec)

mysql>

补充 :查看Docker网络容器信息

[root@kafka2 docker_mysql]# docker network  inspect mysql-network
[
    {
        "Name": "mysql-network",
        "Id": "cb9a57550a6dbbc1498df3fc16f52792ceb9a56e1b5355c7f9f6b62d2bfe690f",
        "Created": "2025-05-12T09:33:02.385924497+08:00",
        "Scope": "local",
        "Driver": "bridge",
        "EnableIPv6": false,
        "IPAM": {
            "Driver": "default",
            "Options": {},
            "Config": [
                {
                    "Subnet": "172.18.0.0/16",
                    "Gateway": "172.18.0.1"
                }
            ]
        },
        "Internal": false,
        "Attachable": false,
        "Ingress": false,
        "ConfigFrom": {
            "Network": ""
        },
        "ConfigOnly": false,
        "Containers": {
            "7b25c52319b7b9a4ce1a2949ec7fd39c08cf2550b9ee1a590a86672d3fe3676f": {
                "Name": "mysql-slave",
                "EndpointID": "964ebd18f6d0415cb3e26c93c0f2f6dd9efb38e705e13c2233b6e74ef4a5f938",
                "MacAddress": "02:42:ac:12:00:03",
                "IPv4Address": "172.18.0.3/16",
                "IPv6Address": ""
            },
            "a778107c84a1ff20aafd57aaf34a53186331306880014db34f7afc03e88af40e": {
                "Name": "mysql-master",
                "EndpointID": "617dcd7b976482c35f42900a24a9f88add5eb7c61e092986dd5d31c819a6b12a",
                "MacAddress": "02:42:ac:12:00:02",
                "IPv4Address": "172.18.0.2/16",
                "IPv6Address": ""
            }
        },
        "Options": {},
        "Labels": {}
    }
]

补充 Docker容器数据卷 挂载信息

[root@kafka2 docker_mysql]# docker volume inspect  mysql-master-volume
[
    {
        "CreatedAt": "2025-05-12T09:33:45+08:00",
        "Driver": "local",
        "Labels": null,
        "Mountpoint": "/var/lib/docker/volumes/mysql-master-volume/_data",
        "Name": "mysql-master-volume",
        "Options": null,
        "Scope": "local"
    }
]
[root@kafka2 docker_mysql]# docker volume inspect  mysql-slave-volume
[
    {
        "CreatedAt": "2025-05-12T09:33:49+08:00",
        "Driver": "local",
        "Labels": null,
        "Mountpoint": "/var/lib/docker/volumes/mysql-slave-volume/_data",
        "Name": "mysql-slave-volume",
        "Options": null,
        "Scope": "local"
    }
]