Docker + MySQL 主从配置以实现读写分离

2023-04-13

【什么是 MySQL 主从备份】
引用一段最近很火的 ChatGPT（微软NewBing）给出的解释

MySQL的主从备份是为了确保数据安全，避免一台机器硬盘损坏导致数据永久丢失。主从备份的实现方式是将主数据库的数据同步到从数据库上，当主数据库出现故障时，可以快速切换到从数据库上，保证业务的正常运行。
在MySQL中，主从备份有两种实现方式：基于二进制日志的复制和基于GTID的复制。基于二进制日志的复制是MySQL自带的一种复制方式，它通过记录二进制日志来实现主从备份。而基于GTID的复制是MySQL 5.6版本之后引入的一种新的复制方式，它通过记录全局事务ID来实现主从备份。

这里我们不深究实现方式，以下介绍一种使用 docker-compose 配置主从备份实现读写分离的做法。

1. 首先，在服务器上创建一个存放 docker-compose 文件的目录，如：mysql-master-slave

ps：这里我假设你的服务器上已经安装好了 docker 以及 docker-compose。如果没有，请先参照有关 docker 安装配置的教程进行安装。

2. 在目录下方新建一个 docker-compose.yaml 文件，内容如下

ps：networks 节点中的 CIDR 和 IP 可自定义，后续会用到。

version: '3.1'
services:
  db-master:
    image: mysql:8.0.29-oracle
    restart: always
    container_name: mysql-writeread
    ports:
      - 33061:3306
    volumes:
      - ./master/config:/etc/mysql/conf.d
      - ./master/data:/var/lib/mysql
      - ./master/log:/var/log/mysql
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: asdf@123
    command: --default-authentication-plugin=mysql_native_password --lower_case_table_names=1
    networks:
      default:
        ipv4_address: 172.132.1.10
  db-slave:
    image: mysql:8.0.29-oracle
    restart: always
    container_name: mysql-readonly
    ports:
      - 33062:3306
    volumes:
      - ./slave/config:/etc/mysql/conf.d
      - ./slave/data:/var/lib/mysql
      - ./slave/log:/var/log/mysql
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: asdf@123
    command: --default-authentication-plugin=mysql_native_password --lower_case_table_names=1
    networks:
      default:
        ipv4_address: 172.132.1.11
networks:
  default:
    ipam:
      driver: default
      config:
        - subnet: 172.132.1.0/24

3. 在 mysql-master-slave 目录下新建目录 master、slave

4. 在 mysql-master-slave/master 和 mysql-master-slave 目录下方分别创建config目录

5. 在 master 和 slave 各自的 config 目录下方创建 docker-my.cnf 配置文件

mysql-master-slave/master/config/docker-my.cnf

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
max_connections=10240
max_user_connections=1024
max_allowed_packet = 16M
log-error=/var/log/mysql/error.log
# 主从配置 - 主库
default_authentication_plugin=mysql_native_password
collation-server=utf8mb4_0900_ai_ci
character-set-server=utf8mb4
init-connect='SET NAMES utf8'
skip-host-cache
skip-name-resolve
server-id=1 #此id在主从库不能相同
log-bin=mysql-bin #开启二进制日记文件
relay-log= mysql-relay-bin
binlog-format=ROW
read-only=0 #可读可写
log-slave-updates=1
expire_logs_days = 7
binlog_cache_size=32m
max_binlog_cache_size=512m
max_binlog_size=512m
replicate-do-db=target-database-name #需要同步的数据库，有多个时添加多行
binlog-ignore-db=mysql #不同步哪些数据库
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
performance_schema_max_table_instances=4096
table_definition_cache=4096
performance_schema=off
table_open_cache=2048
innodb_buffer_pool_chunk_size=2048M
innodb_buffer_pool_size=2048M
mysqlx_max_connections=3000
[mysqldump]
quick
max_allowed_packet = 16M

mysql-master-slave/slave/config/docker-my.cnf

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
max_connections=10240
max_user_connections=1024
max_allowed_packet = 16M
log-error=/var/log/mysql/error.log
# 主从配置 - 从库
default_authentication_plugin=mysql_native_password
collation-server=utf8mb4_0900_ai_ci
character-set-server=utf8mb4
init-connect='SET NAMES utf8'
skip-host-cache
skip-name-resolve
server-id=2 #此id在主从库不能相同
log-bin=mysql-bin #开启二进制日记文件
relay-log= mysql-relay-bin
binlog-format=ROW
read-only=1 #可读可写
log-slave-updates=1
expire_logs_days = 7
binlog_cache_size=32m
max_binlog_cache_size=512m
max_binlog_size=512m
replicate-do-db=target-database-name #需要同步的数据库，有多个时添加多行
binlog-ignore-db=mysql #不同步哪些数据库
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
performance_schema_max_table_instances=4096
table_definition_cache=4096
performance_schema=off
table_open_cache=2048
innodb_buffer_pool_chunk_size=2048M
innodb_buffer_pool_size=2048M
mysqlx_max_connections=3000
[mysqldump]
quick
max_allowed_packet = 16M

6. 执行 docker-compose up -d 运行两个MySQL主从容器

7. 使用root用户连接主库，创建用于同步数据的用户 repl ，并授予所需的权限（Replication）

1
2
3

CREATE USER `repl`@`[从库所在容器的IP]` IDENTIFIED WITH mysql_native_password BY '123456';
GRANT Replication Slave ON *.* TO `repl`@`[从库所在容器的IP]`;

其中 [从库所在容器的IP] 还可以通过 docker inspect mysql-readonly | grep IPAddress 查出

8. 连接主库，执行 `show master status;` 查询主库的状态信息，记录 File 和 Position 字段的值，下一步需要用到

查询结果示例：

1 2	File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set mysql-bin.000002 1710 mysql,sys,information_schema,performance_schema

9. 连接从库，执行以下命令以设置主库信息

需要将 MASTER_LOG_FILE 的值替换为上一步中 File字段的值；
将 MASTER_LOG_POS 的值替换为上一步中 Position字段的值；

CHANGE MASTER TO MASTER_HOST='[主库所在容器的IP]', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1710, MASTER_CONNECT_RETRY=10;

10. 使用 Navicat 连接主库，备份需要同步的数据库，在从库中创建同名数据库，并从备份文件中还原

11. 连接从库，执行 `start slave;` 启动从库同步

12. 通过命令行形式连接从库，执行 show slave status\G; 查询从库的状态信息，确保 Slave_IO_Running 和 Slave_SQL_Running 的状态都为 Yes

如果不确定从库是否能正常连上主库，可以执行 docker exec -it mysql-readonly /bin/bash 进入到从库容器，
然后执行 mysql -h mysql-writeread -urepl -p，输入密码看是否能连上

13. 验证同步是否成功

① 在主库中设置了主从同步的表中创建一个 test 表，在从库中可以看到 test 表被同步过来
② 在主库中设置了主从同步的表中插入/修改/删除一条数据，在从库中可以看到数据修改后的结果

注意，同步可能会存在些许延迟