使用docker-compose配置mysql主从备份

本文参考自：docker部署mysql主从备份，并将配置过程中遇到的相关问题解决后重新总结记录下了整个配置过程。

胖蔡

起因

起因是由于我有两台云服务器，然后跑了几个服务连接的数据库都是同一个，导致我发现这段时间服务器的mysql经常性的负载过大，想着给服务器分分流，但又想要将数据同步到另外一台数据库（其中一个服务器有效期较短，不想到时候花费过多时间迁移），为此我就想要通过主从数据库的配置来方便数据自动复制备份。

环境

• 云服务器（阿里云、华为云）
• linux 系统
• docker、docker-compose

原理

主从备份的原理就是主数据库服务将每次修改的sql记录保存日志，并将这些记录发送给从数据库服务，然后从数据库服务将改记录应用到自己的数据库的过程。其原理图如下：

具体实现步骤如下：

• master 将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
• ​ slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；
•  slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化的

环境配置

主数据库服务

1、创建主数据的docker-compose.yml文件如下：

version: '3'
services:
  node1:
    image: mysql:latest
    hostname: mysql-master
    container_name: mysql-master
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=wordpress

      - MYSQL_DATABASE=wordpress
      - MYSQL_USER=slave
      - MYSQL_PASSWORD=slave
      - TZ=Asia/Shanghai
      - LANG=C.UTF-8
    volumes:
      - /data/mysql/slowSql:/data/mysql
      - /data/mysql/data:/var/lib/mysql
      - /data/mysql/logs:/var/log/mysql
      - /data/mysql/conf/my.cnf:/etc/mysql/my.cnf
    privileged: true
    command: ['mysqld','--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
  	entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"

2、创建mysql配置文件my.cnf，路径就是上面映射到主机的： /data/mysql/conf/my.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1000
secure-file-priv= NULL
# 开启binlog
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 服务器唯一id，默认值1
server-id=1101
max_binlog_size=1G
max_binlog_cache_size=1G
# 设置日志格式，默认值ROW
binlog_format=mixed
expire_logs_days=7
# 开启慢查询
slow_query_log=1
long_query_time=2
slow_query_log_file=/data/mysql/slow.log
# 错误日志
log_error=/var/log/mysql/error.log
# 设置需要复制的数据库，默认复制全部数据库
binlog-do-db=wordpress
# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema


# Custom config should go here
!includedir /etc/mysql/conf.d/

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

需要注意，这里需要给my.cnf改下权限，my.cnf 权限不能是777，会被忽略

$ chmod 644 /data/mysql/conf/my.cnf

3、启动容器，并配置复制权限

$ docker-compose u -d
$ docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash
$ mysql -uroot -p
# 授权用户数据库操作权限
mysql > GRANT ALL PRIVILEGES on wordpress.* TO 'slave'@'%';
# 授予复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
# 刷新权限
mysql > FLUSH PRIVILEGES;
# 查看主机的master状态
mysql > SHOW MASTER STATUS;

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

从数据库服务

1、创建docker-compose.yml文件

version: '3'
services:
  node1:
    image: mysql:latest
    hostname: mysql-slave
    container_name: mysql-slave
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=wordpress
      - MYSQL_DATABASE=wordpress
      - MYSQL_USER=slave
      - MYSQL_PASSWORD=slave
      - TZ=Asia/Shanghai
      - LANG=C.UTF-8
    volumes:
      - /data/mysql/slowSql:/data/mysql
      - /data/mysql/data:/var/lib/mysql
      - /data/mysql/logs:/var/log/mysql
      - /data/mysql/conf/my.cnf:/etc/mysql/my.cnf
    privileged: true
    command: ['mysqld','--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci','--default-time-zone=+08:00']
  	entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"

2、同上，创建my.cnf，地址：/data/mysql/conf/my.cnf 【我这是因为是不同的服务器上，所以可以用同i一个地址，若是同一台服务器部署，需要区分映射到主机的地址不能与主数据服务覆盖】

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time-zone=+08:00
max_connections=1000
secure-file-priv= NULL
# 开启binlog
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 服务器唯一id，默认值1
server-id=1102
max_binlog_size=1G
max_binlog_cache_size=1G
# 设置日志格式，默认值ROW
binlog_format=mixed
expire_logs_days=7
# 开启慢查询
slow_query_log=1
long_query_time=2
slow_query_log_file=/data/mysql/slow.log
# 错误日志
log_error=/var/log/mysql/error.log



# Custom config should go here
!includedir /etc/mysql/conf.d/

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

同上，修改权限：

$ chmod 644 /data/mysql/conf/my.cnf # my.cnf 权限不能是777，会被忽略

3、启动从服务器，配置主数据地址

$docker-compose up -d
$docker exec -it mysql-slave env LANG=C.UTF-8 /bin/bash
$mysql -uroot -p
# 配置主Mysql地址
mysql> CHANGE MASTER TO MASTER_HOST='192.168.80.122',MASTER_USER='slave',MASTER_PASSWORD='slave', MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=939; 

4、启动从数据复制功能

mysql> START SLAVE;
#查看状态（不需要分号）
mysql> SHOW SLAVE STATUS\G

主从常见操作命令

#在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
mysql> stop slave; 
#从机执行。 功能说明： 执行I/O线程和SQL线程的操作。
mysql> start slave;

#删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。在从机上执行
mysql> reset slave;

#删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。在主机上执行。
#用于第一次进行搭建主从库时，进行主库binlog初始化工作；
mysql>reset master;

注意

1、从机执行change命令前需要先确认主机对应的master+log_file名称和master_log_pos偏移量是否发送改变，主机每次重启都可能会导致便宜了的改变，这时候就需要我们重新执行change命令了，查看主机状态的命令：

$ show master status;

2、配置完成，slove数据库状态报错，报错信息如下：Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

错误原因是由于主从复制的slave账号在主库中的数据库加密是caching_sha2_password，我们需要修改成mysql_native_password，修改命令如下：

mysql > ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';

修改过后重新在从数据库执行：

mysql >start slave;
mysql>  SHOW SLAVE STATUS\G