Docker+MySQL主从配置

2022年6月4日

主要内容

利用docker进行主从配置，数据保存在容器外部，容器可任意删除数据不丢失
附带中文版配置，重点介绍几个常用需更改配置
对于大多数人其实没那么熟悉docker，只是用来本地模拟多机器学习使用。生产还是使用直接部署。

步骤

创建工作目录。我的目录是 /etc/docker/mysql。(如果是mac或者windows本机的docker，先确认映射的目录是否有权限)
创建主数据库
- 创建my-master.cnf文件，拷贝下方的配置。依据机器配置更改
  - 更改 innodb_buffer_pool_size 和 innodb_buffer_pool_instances
  - 更改 innodb_read_io_threads 和 innodb_write_io_threads ，主从配置中，写多分配
- 运行。 docker run -d -p 3306:3306 --name mysql-master --restart=always -v /etc/docker/mysql/my-master.cnf:/etc/my.cnf -v /etc/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql
- 稍等几秒后连接数据库。
- 创建从库账号 slave1。
```
CREATE USER slave1 IDENTIFIED BY '123456';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave1'@'%';
FLUSH PRIVILEGES;
```
创建从库
- cp my-master.cnf my-slave.cnf
- 修改配置文件my-slave.cnf
  - 更改 server-id
  - 注释掉 log-bin = mysql-bin ，从库不需要开启binlog
  - 更改 innodb_buffer_pool_size 和 innodb_buffer_pool_instances
  - 更改 innodb_read_io_threads 和 innodb_write_io_threads
- 运行。docker run -d -p 3307:3306 --name mysql-slave --restart=always -v /etc/docker/mysql/my-slave.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql (从库不映射)
- 查看主库IP。线上环境可以直接知道，注意端口开放。同机器docker搭建需要查看 docker inspect mysql-master
- 在主库执行 SHOW MASTER STATUS; 获取参数。
- 进入从库，执行类似 CHANGE MASTER TO MASTER_HOST='172.17.0.4', MASTER_USER='slave1', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=158;
- 启动从库 START SLAVE;
- 查看从库同步状态 SHOW SLAVE STATUS; 查看Slave_IO_Running 和 Slave_SQL_Running 为 Yes
测试
- 在主库创建数据库和表，从库也有相应增加。
- 值的注意的是，如果从库是在主库已经有数据之后再订阅，则从库不会补充数据。需要先数据迁移冷启动，这又是复杂的问题。
- 另外从库如果使用 root 去更改数据也是可以改成功的，会造成主从不一致。服务中使用slave1只有读权限是OK的。

配置文件

[mysqld]
# 服务器序号
server-id=1
# 启动mysql服务进程的用户
user=mysql
# 数据存放位置
datadir=/var/lib/mysql

# 默认字符集
character-set-server = utf8mb4
# 密码插件改为旧的（有些可视化软件不支持新的密码加密方式）
# default-authentication-plugin=mysql_native_password

# SELECT最大执行时间(毫秒)
max_execution_time=10000
# 最大连接数，防止 Too Many Connections
max_connections = 10000
# 最大错误数，超出则禁止新连接
max_connect_errors = 10000
# 空闲等待秒数
wait_timeout=3600
interactive_timeout=3600

# binlog 大小1g滚动写入  (从库不需要开启) 
log-bin=mysql-bin
# 仅记录受影响的列
binlog-row-image=minimal
# 忽略mysql库
binlog-ignore-db=mysql
# binlog每N条语句刷盘
sync_binlog=1


# 错误日志
# 1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息。
log_error_verbosity = 3
# 错误日志文件地址
log_error = /var/log/mysql/error.log

# 慢查询开启
slow_query_log = ON
# 慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
# 超过1秒
long_query_time = 1
# 开启记录没有使用索引查询语句
# log-queries-not-using-indexes = 1


# 普通查询日志，保持默认不开启
general_log = 0
# 普通查询日志存放地址
general_log_file = /var/log/mysql/sql.log


# 缓冲池大小，单独服务器的话官方建议为内存的50%到75%，通常大家设置为80%
# 受到innodb_buffer_pool_instances参数影响自动再调节 innodb_buffer_pool = n * (innodb_buffer_pool_instances * innodb_buffer_pool_chunk_size)
# 可以动态调节 SET GLOBAL innodb_buffer_pool_size=;
innodb_buffer_pool_size = 4g
# 缓冲池区域数，可以提高并发性 (1-64) ，官方建议每个至少1g. (但是官方默认innodb_buffer_pool_size大于1g时这个值就是8也太奇怪了)
# 网上有人说设置为CPU核心说，但是官方手册并没有看到这点，仅供参考
# https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html
innodb_buffer_pool_instances = 4
# 缓冲池块大小 默认128M，不需要更改
# innodb_buffer_pool_chunk_size = 134217728

# 读写线程数，依据机器支持的线程数设置。每个后台线程最多处理256个待处理的I/O请求
# 查看逻辑核心数 cat /proc/cpuinfo| grep "processor"| wc -l 
# 查看每个核心支持的线程 lscpu | grep "Thread(s) per core"
innodb_read_io_threads = 4
innodb_write_io_threads = 2

# 每秒IO数，和系统繁忙程度、磁盘写入能力有关，并非越大越好。
# https://dev.mysql.com/doc/refman/8.0/en/innodb-configuring-io-capacity.html
#innodb_io_capacity = 200
#innodb_io_capacity_max = 2000 

# 事务redo log刷盘模式 
# 1:每次。默认值，更安全。最多丢失1个事务。 
# 2:每秒。更快，最多丢失1秒，在操作系统崩溃的情况下，mysql崩溃不会丢失。
innodb_flush_log_at_trx_commit=1

# 刷盘方式 https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method
# 在MySQL 5.7.25和8.0.14后使用O_DIRECT_NO_FSYNC，在此之前的版本使用O_DIRECT
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_use_fdatasync = 1


host-cache-size=0
skip-name-resolve
secure-file-priv=NULL
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock