Docker本地搭建RDS数据库从库

步骤一 下载RDS数据库的全备文件

• 点击下载链接并复制外网地址

• 本地创建mysql目录和备份文件目录

mkdir -p /data/mysql/backup

• 进入备份文件目录并下载全备文件

cd /data/mysql/backup
wget -c '上一步的外网地址' -O xxx.tar.gz

步骤二 Docker部署mysql

mysql配置文件

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links = 0
default-time_zone = '+8:00'
server-id = 20201026

gtid_mode = on
enforce_gtid_consistency = on

innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000
innodb_page_size=16384

# skip-grant-tables

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

innodb_flush_log_at_trx_commit=2

mysql部署脚本

#!/bin/bash

docker run -d --restart=always \
  -p 3306:3306 \
  -v /data/mysql-web/config:/etc/mysql/mysql.conf.d \
  -v /data/mysql-web/data:/var/lib/mysql \
  -v /data/mysql-web/backup:/mysql/backup \
  -e MYSQL_ROOT_PASSWORD=root密码 \
  --name mysql-web \
  mysql:5.7

XtraBackup安装参考https://blog.csdn.net/cyfblog/article/details/101017375

步骤三 进入容器恢复数据

• 进入mysql容器

docker exec -it 容器名称 bash

• 解压全备数据

cd /mysql/backup
tar xf xxx.tar.gz
rm -f xxx.tar.gz

• 恢复全备数据

innobackupex --apply-log /mysql/backup
rm -rf /var/lib/mysql/*
innobackupex --copy-back /mysql/backup
chown -R mysql.mysql /var/lib/mysql

步骤四 修改aliyun_root密码

• 配置文件添加 skip-grant-tables 重启mysql容器

docker restart mysql容器名称

• 进入容器并连接mysql

docker exec -it 容器名称 bash
mysql

• aliyun_root密码

select trigger_schema,trigger_name from information_schema.triggers;

drop trigger sys.sys_config_insert_set_user;
drop trigger sys.sys_config_update_set_user;

mv /mysql/backup/mysql/*.TRG /tmp

update mysql.user set authentication_string=password('新密码') where user='aliyun_root';

• 注释配置文件skip-grant-tables 重启mysql容器

步骤五 配置mysql从库

• 进入mysql容器并连接mysql

docker exec -it 容器名称 bash
mysql -h127.0.0.1 -uroot -p密码

• 主库创建复制账号

 grant replication slave on *.* to '用户'@'%' identified by '密码';																

• 查看binlog位点

cat /mysql/backup/xtrabackup_slave_filename_info

• 删除mysql库下slave相关表信息

use mysql
truncate  slave_master_info;
truncate  slave_relay_log_info;
truncate  slave_worker_info;

• 配置从库

stop slave;
reset slave;
change master to master_host='主库IP或域名',master_port=端口,master_user='复制账号',master_password='密码',master_auto_position=0;
change master to master_log_file='binlog文件',master_log_pos=日志位点;
start slave;

• 查看从库状态

show slave status\G;

• 配置主从复制延时

stop slave;
CHANGE MASTER TO MASTER_DELAY = 3600；
start slave;
show slave status \G;