Oliver's Blog

MySQL数据库定时备份

  • Apr 15, 2019

挂载Docker数据卷

挂载用于放置备份数据脚本的数据卷，如不需要在容器内访问备份的数据库脚本，则不需要挂载

docker run -p 3306:3306 --name mysql \
--restart always \
--network language-trainer \
-v /opt/volume/mysql/log:/var/log/mysql \
-v /opt/volume/mysql/data:/var/lib/mysql \
-v /opt/volume/mysql/conf:/etc/mysql \
-v /opt/volume/mysql/bak:/var/bak \
-e MYSQL_ROOT_PASSWORD=mysqlrootroot \
-d mysql:5.7

mysqldump设置

修改配置文件/opt/volume/mysql/conf/conf.d/my.cnf，增加用户名密码设置，并重启MySQL

[mysqldump]
user=root
password=mysqlrootroot

数据库备份脚本

编写shell脚本/opt/mysql/backupDatabase.sh

#!/bin/bash
mysqlContainer=mysql
database=language_trainer
bakPath=/opt/volume/mysql/bak
bakFileName=$(date +%Y%m%d%H%M%S)

sudo echo "-----Cleaning backup history(7 days ago) begins-----"
sudo find /opt/volume/mysql/bak -type f -mtime +6 -name "*.sql.gz" -exec rm -f {} \;
sudo echo "-----Cleaning backup history(7 days ago) ends-----"

sudo echo "-----Database:${database} backup begins.-----"
sudo docker exec ${mysqlContainer} mysqldump --databases ${database} | gzip > ${bakPath}/${bakFileName}.sql.gz
sudo echo "-----Database:${database} backup ends.-----"

sudo echo "-----Synchronizing backup with OSS begins-----"
sudo ossutil sync /opt/volume/mysql/bak/ oss://oliver-language-trainer/bak/ --delete -f
sudo echo "-----Synchronizing backup with OSS ends-----"

ossutil同步文件夹

脚本执行权限

chmod u+x /opt/mysql/backupDatabase.sh

定时任务

# 修改定时任务列表
crontab -e
# 查看定时任务列表
crontab -l

00 03 * * * /opt/mysql/backupDatabase.sh

数据库脚本解压

gunzip -c 20230210220815.sql.gz > 20230210220815.sql
Search