MySQL主从同步

主库配置

mysqld.cnf

# 主从同步配置（主节点）
# 基础配置
server-id = 1
log-bin = mysql-bin
binlog_format = ROW

# GTID 配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 复制相关
binlog_cache_size = 1M
binlog_checksum = CRC32
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
expire_logs_days = 7

compose.yml

services:

  mysql:
    container_name: mysql
    image: mysql:5.7.34
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 40001:3306
    volumes:
      - ./mysql/db2:/var/lib/mysql
      - ./config/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
    environment:
      MYSQL_ROOT_PASSWORD: DOkTrUJJRgXXSCaFwjaPlAAB6FBPlF1c
      TZ: Asia/Shanghai

从库配置

mysqld.cnf

# 主从同步配置 (从节点)
# 基础配置
server-id = 2
relay_log = mysql-relay-bin
read_only = ON

# GTID 配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 复制相关
binlog_cache_size = 1M
binlog_checksum = CRC32
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
expire_logs_days = 7
log_slave_updates = ON
skip_slave_start = 1

compose.yml

services:

  mysql-slave:
    container_name: mysql-slave
    image: mysql:5.7.34
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    ports:
      - 40001:3306
    volumes:
      - ./mysql/db2:/var/lib/mysql
      - ./config/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
    environment:
      MYSQL_ROOT_PASSWORD: gQos6vldkfTGK5U3xK196JVaDgksVQIx
      TZ: Asia/Shanghai

全量同步

停止主库和从库

docker-compose stop

同步主库数据文件到从库

rsync -avP --exclude='auto.cnf' \
 --exclude='*.pid' \
 --exclude='ib_logfile*' \ 
 --exclude='mysql.sock' \ 
 /opt/docker/mysql/db2/ \ 
 root@mysql-salve:/opt/docker/mysql/db2/

WARNING

⚠️注意，不要同步 auto.cnf 文件。这个是服务器UUID文件。

增量同步

新建同步账号

启动主节点，在主节点上操作

CREATE USER 'repl'@'%' IDENTIFIED BY '5GtXr5PCXw3DbzymUIoUFfWUfkQDdzUe';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

获取主节点状态：

SHOW MASTER STATUS;

启动从节点

启动从节点，在从节点上操作

CHANGE MASTER TO MASTER_HOST='mysql', 
  MASTER_USER='repl', 
  MASTER_PASSWORD='5GtXr5PCXw3DbzymUIoUFfWUfkQDdzUe', 
  MASTER_AUTO_POSITION=1;

开始同步

START SLAVE;

检查复制状态

SHOW SLAVE STATUS;