使用docker-compose配置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