记录下 Mysql 主主同步 Docker-compose Debian12

eezd 收录于类别 Linux

2025-06-20 2025-06-20 约 466 字预计阅读 3 分钟

1 安装

首先配置好 docker

services:
  mysql8:
    image: mysql:8.0.42
    container_name: mysql8
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - TZ=Asia/Shanghai
    volumes:
      - /docker/mysql8/data:/var/lib/mysql
      - /docker/mysql8/config:/etc/mysql/conf.d
    ports:
      - 33306:3306
    restart: unless-stopped

然后配置下 conf.d

要注意！！你只需要改动下面三个地方
- server-id：这个是唯一的！不可以重复
- auto_increment_increment：表示 ID 每次增长 N
- auto_increment_offset：表示从 M 开始

服务器	server-id	auto_increment_increment	auto_increment_offset
A	1	3	1
B	2	3	2
C	3	3	3

[mysqld]
# 基本设置
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address = 0.0.0.0

# 字符集和排序规则
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

# 主从
server-id=2 # 唯一的
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
relay_log=relay-log
auto_increment_increment=2 # 从2开始避免冲突
auto_increment_offset=1 # 记得是唯一的 避免自增冲突

# 内存相关设置 (适合1GB内存)
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 4M
key_buffer_size = 16M
max_connections = 30
max_allowed_packet = 16M
table_open_cache = 64
sort_buffer_size = 512K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 128K
thread_stack = 192K
tmp_table_size = 16M
max_heap_table_size = 16M

# 查询缓存 (MySQL 8.0已移除query cache功能)
# query_cache_type = 0

# InnoDB设置
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_file_size = 64M
innodb_io_capacity = 200
innodb_io_capacity_max = 400

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2

# 错误日志
log_error = /var/log/mysql/error.log

# 二进制日志
# 如非必要，建议1G内存服务器关闭二进制日志
disable_log_bin = 1

# 性能优化
table_definition_cache = 400
skip_name_resolve = 1
innodb_buffer_pool_instances = 1
innodb_read_io_threads = 2
innodb_write_io_threads = 2

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

然后就可以运行了

docker-compose up -d --remove-orphans

2 配置

这边建议你进入到容器里面操作，不要使用 navicat 等数据库软件操作

推荐使用容器名称进入，我们数据库容器名称是 mysql8。

docker exec -it mysql8 bash

输入密码登录

mysql -uroot -p

接下来我们需要分别给各自数据库创建用于同步的用户。

我们先假设数据库的信息是这样的，请根据你自己实际情况修改
A 数据库信息
- 地址：88.88.88.88
- 端口：3306
B 数据库信息
- 地址：99.99.99.99
- 端口：3306

-- A数据库(自己的IP是 88.88.88.88)
-- 创建一个只允许 99.99.99.99 地址登录的用户
CREATE USER 'repl'@'99.99.99.99' IDENTIFIED WITH mysql_native_password BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'99.99.99.99';
FLUSH PRIVILEGES;

-- B数据库(自己的IP是 99.99.99.99)
-- 创建一个只允许 88.88.88.88 地址登录的用户
CREATE USER 'repl'@'88.88.88.88' IDENTIFIED WITH mysql_native_password BY 'replpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'88.88.88.88';
FLUSH PRIVILEGES;

然后我们分别创建同步连接

-- A数据库(自己的IP是 88.88.88.88)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='99.99.99.99',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='replpass',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

-- B数据库(自己的IP是 99.99.99.99)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='88.88.88.88',
  SOURCE_PORT=3306,
  SOURCE_USER='repl',
  SOURCE_PASSWORD='replpass',
  SOURCE_AUTO_POSITION=1;

START REPLICA;

确保 Last_IO_Error 或 Last_SQL_Error，是空的。

SHOW REPLICA STATUS\G;

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 38.71.124.47
                  Source_User: repl
                  Source_Port: 33306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000003
          Read_Source_Log_Pos: 2986082
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 1499
        Relay_Source_Log_File: mysql-bin.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:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 2986082
              Relay_Log_Space: 2986344
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 2
                  Source_UUID: 89a320e1-4dda-11f0-b7d9-6ea8d28807f0
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 89a320e1-4dda-11f0-b7d9-6ea8d28807f0:1-8
            Executed_Gtid_Set: 876915e3-4dda-11f0-a25d-f6e54b60858c:1-8,
89a320e1-4dda-11f0-b7d9-6ea8d28807f0:1-8
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

SHOW MASTER STATUS;

结束

目录

目录

记录下 Mysql 主主同步 Docker-compose Debian12

1 安装

2 配置