MySQL8 主从同步容器化部署_Mysql

一、引言

本章主要讲解通过docker容器化部署的方式实现mysql的主从运行模式（一主一从）。默认双节点都安装了docker、docker-compose。

详解

1、示例环境

主节点	从节点	主从架构
192.168.25.130	192.168.25.131	主从同步；异步模式

2、主节点安装mysql

1）创建目录

>> mkdir -p /opt/software/mysql8/{conf,logs,init,secrets} /data/mysql8/{data,backup}
>> cd /opt/software/mysql8

2）创建docker-compose.yml文件

>> vim docker-compose.yml  # 内容如下，复制粘贴即可

---
# 定义密钥
secrets:
  passwd_root:
    file: ./secrets/passwd_root.txt
  passwd_sync:
    file: ./secrets/passwd_sync.txt
services:
  mysql8:
    container_name: mysql-master
    image: mysql:8.0.45
    user: root
    restart: unless-stopped
    privileged: true
    ports:
      - 30096:3306
    volumes:
      - /data/mysql8/data:/var/lib/mysql  # 数据目录挂载
      - ./conf/my.cnf:/etc/my.cnf:ro # 配置文件只读挂载（不允许容器内对其写操作）
      - ./logs:/var/log/mysql
      - ./init/:/docker-entrypoint-initdb.d/:ro
      - /etc/localtime:/etc/localtime
    secrets:  # 挂载secrets; 默认挂载至容器内的 /run/secrets/ 路径
      - passwd_root
      - passwd_sync
    environment:
      tz: asia/shanghai
      mysql_root_password_file: "/run/secrets/passwd_root"
      mysql_user_password_file: "/run/secrets/passwd_sync"
    healthcheck:
      test: ["cmd-shell", "mysql_pwd=\"$$(cat /run/secrets/passwd_root)\" mysql -h 127.0.0.1 -uroot --protocol=tcp -e 'select 1' >/dev/null 2>&1 || exit 1"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 60s
...

3）创建配置文件

>> vim ./conf/my.cnf  # 内容如下，复制粘贴即可

[client]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock
[mysql]
default-character-set = utf8mb4
[mysqld]
server-id = 1
user = mysql
autocommit = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/log/mysql/mysqld.pid
log-error = /var/log/mysql/mysqld.log
symbolic-links = 0
character-set-server=utf8mb4
# binlog
log-bin = master-bin
# 指定哪些库表不进行同步
replicate-wild-ignore-table = information_schema.%
# 字段变化增量值
auto_increment_increment = 2
# 初始字段id
auto_increment_offset = 1
# 忽略所有复制产生的错误
slave-skip-errors = all
binlog_cache_size=16m
binlog_format=row
binlog_row_image=full
max_binlog_cache_size=1gb
# 设置单个 binlog 文件的最大大小
max_binlog_size = 300m
# binlog日志保留时间，单位秒，30天
binlog_expire_logs_seconds=2592000
ft_min_word_len=4
connect_timeout=600
interactive_timeout=1800
wait_timeout=600
# 启用gtid
gtid_mode = on
enforce_gtid_consistency = on
group_concat_max_len = 102400
lower-case-table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-storage-engine=innodb
# 密码认证插件
default_authentication_plugin=mysql_native_password
default-time-zone = '+08:00'
slow_query_log=1
#输出死锁
innodb_print_all_deadlocks=1
long_query_time=10
max_connections=4096
max_connect_errors=1024
max_allowed_packet=64m
back_log=512
interactive_timeout=3600
sort_buffer_size=2m
join_buffer_size=2m
read_buffer_size=2m
read_rnd_buffer_size=2m
thread_cache_size=256
table_open_cache=10000
max_heap_table_size=64m
tmp_table_size=64m
thread_stack=192kb
local_infile=0
skip-name-resolve
skip-external-locking
# innodb
innodb_buffer_pool_size=2048m
#ibdata1的数据设置应该和本地ibdata1大小的一致
innodb_data_file_path=ibdata1:1g;ibdata2:1g:autoextend
innodb_temp_data_file_path=ibtmp1:12m:autoextend:max:5g
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_file_per_table=1
#将innodb的日志刷新方式调整为o_direct，可以减少数据在缓存和磁盘之间的复制
innodb_flush_method=o_direct
#将innodb的日志刷新策略调整为每秒刷新，可以提高性能
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=120
innodb_thread_concurrency=16
innodb_log_buffer_size=8m
#将innodb日志文件大小调整为256m，可以减少写入磁盘的次数，提高性能
innodb_log_file_size=256m
innodb_redo_log_capacity=268435456
innodb_buffer_pool_size=4g
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90
innodb_open_files=10000
skip_replica_start
log_replica_updates
sql_mode="no_engine_substitution,strict_trans_tables,no_zero_date,error_for_division_by_zero"
relay-log=relay.log
relay-log-index=slave-relay.index
log_bin_trust_function_creators = 1

4）创建初始化需要的文件

# 创建密码文件 
>> echo "super@admin_2026" > ./secrets/passwd_root.txt  # root密码
>> echo "sync@admin_2026" > ./secrets/passwd_sync.txt  # 主从同步用户密码
>> vim ./init/init.sh  # 可根据需要修改

#! /bin/bash

set -euo pipefail 
sync_password="$(cat "$mysql_user_password_file")"

# 执行指定sql，可自定义；这里只用于创建用户
mysql -uroot -p"$mysql_root_password" << eof
create user sync@'%' identified with mysql_native_password by "${sync_password}";
grant replication slave on *.* to sync@'%';
flush privileges;
set global max_connect_errors=1000;
eof

5）启动主节点mysql

# 授权 
>> chmod -r 755 /data/mysql8 /opt/software/mysql8; chmod +x ./init/*.sh; chmod 644 secrets/* 

# 启动
>> docker-compose up -d
>> docker-compose logs -f  # 启动后，查看日志如下图

3、从节点安装mysql

1）创建目录

>> mkdir -p /opt/software/mysql8/{conf,logs,init,secrets,scripts} /data/mysql8/data
>> cd /opt/software/mysql8

2）创建docker-compose.yml文件

>> vim docker-compose.yml  # 内容如下，复制粘贴即可

---
---
# 定义密钥
secrets:
  passwd_root:
    file: ./secrets/passwd_root.txt
services:
  mysql8:
    container_name: mysql-slave
    image: mysql:8.0.45
    user: root
    restart: unless-stopped
    privileged: true
    ports:
      - 30096:3306
    volumes:
      - /data/mysql8/data:/var/lib/mysql  # 数据目录挂载
      - ./conf/my.cnf:/etc/my.cnf
      - ./logs:/var/log/mysql
      - ./init/:/docker-entrypoint-initdb.d/:ro
      - /etc/localtime:/etc/localtime
    secrets:  # 挂载secrets; 默认挂载至容器内的 /run/secrets/ 路径
      - passwd_root
    environment:
      tz: asia/shanghai
      mysql_root_password_file: "/run/secrets/passwd_root"
      mysql_master_host: 192.168.25.130  # mysql master节点，根据实际情况修改
      mysql_master_port: 30096  # mysql master节点监听端口
    healthcheck:
      test: ["cmd-shell", "mysql_pwd=\"$$(cat /run/secrets/passwd_root)\" mysql -h127.0.0.1 -uroot --protocol=tcp -e 'select 1' >/dev/null 2>&1 || exit 1"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 60s
...

3）创建配置文件

>> vim ./conf/my.cnf  # 内容如下，复制粘贴即可

[client]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock
[mysql]
default-character-set = utf8mb4
[mysqld]
server-id = 2
user = mysql
autocommit = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/log/mysql/mysqld.pid
log-error = /var/log/mysql/mysqld.log
symbolic-links = 0
character-set-server=utf8mb4
# binlog
log-bin = slave-bin
# 指定哪些库表不进行同步
replicate-wild-ignore-table = information_schema.%
# 字段变化增量值
auto_increment_increment = 2
# 初始字段id
auto_increment_offset = 1
# 忽略所有复制产生的错误
slave-skip-errors = all
binlog_cache_size=16m
binlog_format=row
binlog_row_image=full
max_binlog_cache_size=1gb
# 设置单个 binlog 文件的最大大小
max_binlog_size = 300m
# binlog日志保留时间，单位秒，30天
binlog_expire_logs_seconds=2592000
ft_min_word_len=4
connect_timeout=600
interactive_timeout=1800
wait_timeout=600
# 启用gtid
gtid_mode = on
enforce_gtid_consistency = on
group_concat_max_len = 102400
lower-case-table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-storage-engine=innodb
# 密码认证插件
default_authentication_plugin=mysql_native_password
default-time-zone = '+08:00'
slow_query_log=1
#输出死锁
innodb_print_all_deadlocks=1
long_query_time=10
max_connections=4096
max_connect_errors=1024
max_allowed_packet=64m
back_log=512
interactive_timeout=3600
sort_buffer_size=2m
join_buffer_size=2m
read_buffer_size=2m
read_rnd_buffer_size=2m
thread_cache_size=256
table_open_cache=10000
max_heap_table_size=64m
tmp_table_size=64m
thread_stack=192kb
local_infile=0
skip-name-resolve
skip-external-locking
# innodb
innodb_buffer_pool_size=2048m
#ibdata1的数据设置应该和本地ibdata1大小的一致
innodb_data_file_path=ibdata1:1g;ibdata2:1g:autoextend
innodb_temp_data_file_path=ibtmp1:12m:autoextend:max:5g
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_file_per_table=1
#将innodb的日志刷新方式调整为o_direct，可以减少数据在缓存和磁盘之间的复制
innodb_flush_method=o_direct
#将innodb的日志刷新策略调整为每秒刷新，可以提高性能
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=120
innodb_thread_concurrency=16
innodb_log_buffer_size=8m
#将innodb日志文件大小调整为256m，可以减少写入磁盘的次数，提高性能
innodb_log_file_size=256m
innodb_redo_log_capacity=268435456
innodb_buffer_pool_size=4g
innodb_log_files_in_group=3
innodb_max_dirty_pages_pct=90
innodb_open_files=10000
skip_replica_start
log_replica_updates
sql_mode="no_engine_substitution,strict_trans_tables,no_zero_date,error_for_division_by_zero"
relay-log=relay.log
relay-log-index=slave-relay.index
log_bin_trust_function_creators = 1

4）创建初始化需要的文件

# 创建密码文件 
>> echo "super@admin_2026" > ./secrets/passwd_root.txt  # root密码，和主节点一致

>> vim ./init/backup.sh  # 可根据需要修改

#!/bin/bash
set -euo pipefail

# 备份主节点的数据
echo "开始备份主节点数据..." 
sleep 5
/usr/bin/mysqldump -h${mysql_master_host} -p${mysql_master_port} -uroot -p"${mysql_root_password}" --all-databases --source-data=2 --single-transaction > /var/lib/mysql/backup.sql

# 导入备份数据
echo "正在导入备份数据..."
sleep 5
/usr/bin/mysql -uroot -p"${mysql_root_password}"  < /var/lib/mysql/backup.sql

5）启动从节点mysql

# 授权 
>> chmod -r 755 /data/mysql8 /opt/software/mysql8; chmod +x ./init/*.sh; chmod 644 secrets/* 
>> docker-compose up -d  # 若要重新执行，则需要先清空data目录

4、配置主从同步

# 创建主从同步sql脚本
>> cd /opt/software/mysql8
>> vim scripts/replica.sql  # 内容如下，根据实际情况调整

stop replica;
reset replica;
change replication source to source_host = '192.168.25.130',  source_port = 30096, source_auto_position = 1, get_source_public_key = 1, source_ssl = 1, source_retry_count = 60, source_connection_auto_failover = 1, source_connect_retry = 30; start replica user = 'sync' password = 'sync@admin_2026';

# 执行
>> sleep 30; mysql -h192.168.25.131 -p30096 -uroot -p'super@admin_2026' --socket=/data/mysql8/data/mysql.sock < ./scripts/replica.sql

# 验证同步状态
>> mysql -h192.168.25.131 -p30096 -uroot -p'super@admin_2026' --socket=/data/mysql8/data/mysql.sock -e "show replica status\g"