一、背景
在一个风和日丽的下午，姜同学正在研究动态规划算法，突然被临时传递了一个需求，大致就是测试的同学想要做自动化测试。具体的细节略过，姜同学认为需求还比较合理，可以做。要求如下： ● 无损备份线上数据库到文件 ● 支持表级备份 ● 支持字段脱敏 ● 支持版本管理 ● 支持一键还原
二、需求分析
三.实施方案
备份
还原
编写脚本
看着就是复制黏贴很机械化的需求呀，一般这种操作姜同学都不会干第二次的，所以就写个脚本安排吧。
启动
参数列表：罗列代码中涉及的参数和含义
参数 | 含义 |
|---|---|
SLAVE_PASS | 密码，备份和设置主从的时候用 |
MYSQL_PASS | 新容器的密码，登录用 |
#!/bin/bash
RDS_PASS=$1
MYSQL_PASS=$2
RDS_HOST='源库IP'
RDS_PORT='源库端口'
RDS_USER='源库用户名'
read -p "请输入想要同步的库用英文,隔开:" TODO_DB
read -p "请输入想要同步的表用英文,支持通配(没有直接回车):" TODO_TABLE
if test -z $RDS_PASS
then
read -p "请输入RDS从库密码:" RDS_PASS
fi
if test -z $MYSQL_PASS
then
read -p "请输入新的MySQL容器的初始密码:" MYSQL_PASS
fi
echo "RDS从库密码为：$RDS_PASS"
echo "MySQL容器初始化密码为：$MYSQL_PASS"
mkdir -p /pins/rds-slave/data/mysql/conf/
touch /pins/rds-slave/data/mysql/conf/my.cnf
cat > /pins/rds-slave/data/mysql/conf/my.cnf << EOF
[mysqld]
server-id=66666
default-storage-engine=INNODB
character_set_server = utf8
gtid_mode=on #开启gtid模式
enforce_gtid_consistency=on #强制gtid一致性，开启后对于特定create table不被支持
#复制的表
#复制的库
#不复制的库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
[mysqld_safe]
character_set_server=utf8
[mysql]
default-character-set=utf8
[mysql.server]
default-character-set=utf8
[client]
default-character-set=utf8
EOF
REPLICATE__DB=(${TODO_DB//,/ })
for DB in ${REPLICATE__DB[@]}
do
sed -i "8areplicate-do-db=$DB" /pins/rds-slave/data/mysql/conf/my.cnf
done
REPLICATE__TABLE=(${TODO_TABLE//,/ })
for TABLE in ${REPLICATE__TABLE[@]}
do
sed -i "7areplicate-do-table=$TABLE" /pins/rds-slave/data/mysql/conf/my.cnf
done
#安装MySQL 5.7.30与RDS同步
docker run -p 13007:3306 --name version-mysql -v /pins/rds-slave/data/mysql/conf:/etc/mysql -e MYSQL_ROOT_PASSWORD="$MYSQL_PASS" -e TZ="Asia/Shanghai" -d --restart=always mysql:5.7.30
if [ $? -eq 0 ]; then
echo -e " 33[32m 版本库安装成功! 33[0m"
else
echo -e " 33[31m 版本库安装失败! 33[0m"
exit 8
fi
echo "倒计时60s,RDS从库准备关闭主从复制"
sleep 60
docker exec -it version-mysql /bin/bash -c "mysql -h $RDS_HOST -P $RDS_PORT -u $RDS_USER -p'$RDS_PASS' -e 'stop slave'"
#备份想要同步的库 RDS从库IP和端口后续有修改自己改脚本吧就不当做参数传递了
for DB in ${REPLICATE__DB[@]}
do
docker exec -it version-mysql /bin/bash -c "mysqldump -h $RDS_HOST -P $RDS_PORT -u $RDS_USER -p'$RDS_PASS' --set-gtid-purged=OFF $DB > /$DB.sql"
docker exec -it version-mysql sed -i "1iuse $DB;" /$DB.sql
docker exec -it version-mysql /bin/bash -c "mysql -uroot -p'$MYSQL_PASS' -e 'create database $DB'"
docker exec -it version-mysql /bin/bash -c "mysql -uroot -p'$MYSQL_PASS' -e 'source /$DB.sql'"
done
# 新的容器设置为RDS从库的从库进行级联复制
docker exec -it version-mysql mysql -h $RDS_HOST -P $RDS_PORT -u $RDS_USER -p"$RDS_PASS" -e 'show master statusG' > /tmp/master.info
MASTER_LOG_FILE=`awk -F: '{print $2}' /tmp/master.info | sed -n '3p' | sed 's/ //g'`
MASTER_LOG_POS=`awk -F: '{print $2}' /tmp/master.info | sed -n '4p' | sed 's/ //g'`
cat > /tmp/change.sql << EOF
CHANGE MASTER TO MASTER_HOST='$RDS_HOST',
MASTER_PORT=$RDS_PORT,
MASTER_USER='$RDS_USER',
MASTER_PASSWORD='$RDS_PASS',
MASTER_LOG_FILE='$MASTER_LOG_FILE',
MASTER_LOG_POS=$MASTER_LOG_POS;
start slave;
EOF
//' /tmp/change.sql > /tmp/change_master.sql
docker cp /tmp/change_master.sql version-mysql:/
docker exec -it version-mysql /bin/bash -c "mysql -uroot -p'$MYSQL_PASS' -e 'source /change_master.sql'"
echo "主从配置结束,解锁RDS从库,开始级联复制......"
docker exec -it version-mysql /bin/bash -c "mysql -h $RDS_HOST -P $RDS_PORT -u $RDS_USER -p'$RDS_PASS' -e 'start slave'"
shell停止
停止版本步骤如下：
- 获取记录脱敏字段的文件
- 清掉版本库的主从关系
- 字段脱敏
- 停止版本库
- 使用版本库容器制作镜像
#!/bin/bash
MySQL_PASS=$1
VERSION_INFO=$2
# 下载脱敏字段文件
rm -rf /tmp/desensitization.field
wget http://pinslife-res.pinsmedical.com/database/desensitization.field -O /tmp/desensitization.field
FIELDS=$(cat /tmp/desensitization.field)
echo "需要脱敏的字段：${FIELDS}"
# 清除主从关系
docker exec -it version-mysql /bin/bash -c "mysql -uroot -p'$MYSQL_PASS' -e 'STOP SLAVE;RESET SLAVE ALL'"
FIELDS=(${FIELDS//,/ })
for FIELD in ${FIELDS[@]}
do
echo "${FIELD} 开始脱敏......"
TABLE_FIELD=(${FIELD//./ })
DB=${TABLE_FIELD[0]}
TABLE=${TABLE_FIELD[1]}
COLUMN=${TABLE_FIELD[2]}
echo "数据库${DB} 表${TABLE} 列${COLUMN}"
docker exec -it version-mysql /bin/bash -c "mysql -uroot -p'$MYSQL_PASS' -e 'UPDATE ${DB}.${TABLE} SET ${COLUMN}=LEFT(PASSWORD(${COLUMN}),10)'"
if [ $? -eq 0 ]; then
echo -e " 33[32m ${FIELD}...脱敏成功! 33[0m"
else
echo -e " 33[31m ${FIELD}...脱敏失败! 33[0m"
exit 8
fi
done
#转移数据和配置
docker exec -it version-mysql /bin/bash -c "mkdir -p /pins/mysql/data"
docker exec -it version-mysql /bin/bash -c "mkdir -p /pins/mysql/conf"
docker exec -it version-mysql /bin/bash -c "cp /etc/mysql/my.cnf /pins/mysql/conf/"
docker exec -it version-mysql /bin/bash -c "mv /var/lib/mysql/* /pins/mysql/data/"
docker exec -it version-mysql /bin/bash -c "sed -i '1adatadir=/pins/mysql/data/' /pins/mysql/conf/my.cnf"
#停止从库
docker stop version-mysql
VERSION=$(date ' %Y-%m-%d')
docker commit -a "jiangtongxue@pinsmedical.com" -m "${VERSION_INFO}" version-mysql version-mysql:${VERSION}
shell一键还原版本库
代码语言：javascript复制docker run -d -p 13307:3306 --name=test-mysql version-mysql:2022-09-16 mysqld --defaults-file=/pins/mysql/conf/my.cnf
