MySQL docker 主从

将如下配置映射到容器中的 /etc/mysql/conf.d/ 下，my.cnf 按需修改 可将配置按需写入到单个配置文件中

基础配置

/etc/mysql/my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
# sock 文件映射出来用于备份
socket          = /var/lib/mysql/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/

/etc/mysql/conf.d/docker.cnf

[mysqld]
skip-host-cache
skip-name-resolve

公共配置 /etc/mysql/conf.d/common.cnf

[mysqld]
# 建议使用内存的 70%
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M

# port = 3306

# join_buffer_size = 256M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 4M

log-bin = binlog
# binlog-format = mixed
sync_binlog = 1
binlog_cache_size = 2M
relay_log = relaybin
relay_log_recovery = on

default-time_zone = '+8:00'

# 0:区分大小写
lower_case_table_names = 1

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

max_connections = 500
max_connect_errors = 1000

transaction-isolation = REPEATABLE-READ
explicit_defaults_for_timestamp = ON

# default_authentication_plugin = mysql_native_password

slow_query_log_file = slow.log
slow_query_log = 1
long_query_time = 5

performance_schema = ON

innodb_flush_log_at_trx_commit = 2

# max_binlog_size = 200m
# expire_logs_days = 15
# 8.0 版本新参数 2592000 (30 天)
binlog_expire_logs_seconds = 2592000

# log-error = /var/log/mysql/mysqld.log

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

[mysqldump]
quick
quote-names

MySQL 主节点配置

/etc/mysql/conf.d/master.cnf

[mysqld]
server-id = 1

gtid_mode = on
enforce_gtid_consistency = on

# binlog-do-db = test
# binlog-ignore-db = test

MySQL 从节点配置

/etc/mysql/conf.d/slaves.cnf

[mysqld]
server-id = 2

gtid_mode = on
enforce_gtid_consistency = on


# 从库不允许修改数据
read_only = 1
# log_bin_trust_function_creators = 1

# replicate-do-db = test
# replicate-ignore-db = performance_schema

# 级联同步开启
# log-slave-updates = 1
# skip-log-bin

# 不让 slave 随数据库启动
# skip_slave_start
# slave-skip-errors = 1032,1053,1062,1146,2003

启动服务

docker run -dit \
-p 3306:3306 \
--restart unless-stopped \
--name mysql \
--cap-add sys_nice \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD="acdiost" \
-v /etc/mysql/conf.d:/etc/mysql/conf.d \
-v /etc/localtime:/etc/localtime:ro \
-v /var/lib/mysql:/var/lib/mysql \
mysql

# 防火墙设置
firewall-cmd --permanent --zone=public --add-service=mysql
firewall-cmd --reload

docker-compose.yml

version: "3"

services:

  mysql:
    image: mysql
    container_name: mysql
    restart: always
    volumes:
      - "/etc/localtime:/etc/localtime:ro"
      - "./conf/my.cnf:/etc/mysql/my.cnf"
      - "./conf/docker.cnf:/etc/mysql/conf.d/docker.cnf"
      - "./conf/common.cnf:/etc/mysql/conf.d/common.cnf"
      - "./conf/master.cnf:/etc/mysql/conf.d/master.cnf"
      - "./db/:/var/lib/mysql/"
      - "./log:/var/log/mysql"
    environment:
      - "TZ=Asia/Shanghai"
      - "MYSQL_ROOT_PASSWORD=password"
    healthcheck:
      test: mysqladmin -uroot -ppassword ping
      interval: 1m
      timeout: 10s
      retries: 15
    network_mode: "host"

create user 'replicate'@'%' identified by 'acdiost';
grant replication slave on *.* to 'replicate'@'%';
flush privileges;

stop slave;

# GTID 模式:
CHANGE MASTER TO
MASTER_HOST='ip',
MASTER_USER='replicate',
MASTER_PASSWORD='acdiost',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

# 未开启 GTID 模式, 需修改 MASTER_LOG_POS 为主库位置
CHANGE MASTER TO
MASTER_HOST='ip',
MASTER_USER='replicate',
MASTER_PASSWORD='acdiost',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=123;

start slave;
show slave status\G;

停止主从

# 清除 slave 同步复制
stop slave;
reset slave all;