Ubuntu 通过Shell脚本自动备份数据库

Ubuntu 通过Shell脚本自动备份数据库

1. 创建mysql备份账户

-- 创建专用备份账号（如果尚未创建）
CREATE USER 'backup_user'@'%' IDENTIFIED BY 'strong_password';

-- 授予基础权限
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS ON *.* TO 'backup_user'@'%';

-- 如果数据库含事件调度
GRANT EVENT ON my_db.* TO 'backup_user'@'%';

FLUSH PRIVILEGES;

2. 方式1：密码单独存放（推荐）

1) 将账号密码存储到mysql配置文件中

# /etc/mysql/conf.d/backup.cnf文件中添加
# 如果是docker容器，则需要将配置文件挂载到容器中，host填写容器名
sudo vim /etc/mysql/conf.d/backup.cnf
[client]
user = backup_user
password = strong_password
host = 127.0.0.1

# 更改配置文件权限
sudo chmod 600 /etc/mysql/conf.d/backup.cnf

2)新建shell脚本 dbbackup.sh

#!/bin/bash

# 设置备份文件的保存目录
backup_dir="/backup/mysqldata"

# 设置备份文件的登录凭证
mysql_dbname="my_db"

# 设置备份文件的名称和格式(以日期命名)
backup_file="$(date +%Y-%m-%d-%H-%M-%S).sql.gz"

# 使用mysqldump命令备份数据库
mysqldump $mysql_dbname |gzip > $backup_dir/$backup_file
# docker容器使用mysqldump命令备份数据库
docker exec mysql_container mysqldump $mysql_dbname |gzip > $backup_dir/$backup_file

# 删除30天前的备份文件
find "$backup_dir" -name "*.sql.gz" -mtime +30 -exec rm {} \;

# 打印备份完成的信息
echo " MYSQL backup complete: $backup_dir/$backup_file"

3. 方式2：命令行使用密码

直接新建shell脚本 dbbackup.sh，将密码写在文件中

#!/bin/bash

# 设置备份文件的保存目录
backup_dir="/backup/mysqldata"

# 设置备份文件的登录凭证
mysql_user="backup_user"
mysql_password="strong_password"
mysql_host="127.0.0.1"
mysql_dbname="my_db"

# 设置备份文件的名称和格式(以日期命名)
backup_file="$(date +%Y-%m-%d-%H-%M-%S).sql.gz"

# 使用mysqldump命令备份数据库
mysqldump -u$mysql_user -p$mysql_password -h$mysql_host $mysql_dbname |gzip > $backup_dir/$backup_file
# docker容器使用mysqldump命令备份数据库
docker exec mysql_container mysqldump -u$mysql_user -p$mysql_password -h$mysql_host $mysql_dbname |gzip > $backup_dir/$backup_file

# 删除30天前的备份文件
find "$backup_dir" -name "*.sql.gz" -mtime +30 -exec rm {} \;

# 打印备份完成的信息
echo " MYSQL backup complete: $backup_dir/$backup_file"

# 可以一句话搞定
# /usr/bin/mysqldump -ubackup_user -pstrong_password -h 127.0.0.1 my_db |gzip > /backup/mysqldata/`date +%Y-%m-%d-%H-%M-%S`.sql.gz

4. 赋予文件权限以及测试shell脚本

# 创建备份文件夹(保证mysqldump有访问权限)
mkdir -p /backup/mysqldata
# 移动shell脚本到备份文件夹
sudo mv dbbackup.sh /backup/mysqldata
# 赋予dbbackup.sh文件可执行权限
sudo chmod +x /backup/mysqldata/dbbackup.sh
# 测试dbbackup.sh脚本执行
./dbbackup.sh
 MYSQL backup complete: /backup/mysqldata/2024-10-15-11-34-26.sql.gz

5. 编辑定时任务

# 编辑定时任务，系统跳出编辑器选择界面，选择常用的编辑器打开定时任务
crontab -e
# 加入以下代码，表示每天凌晨3点，执行sh文件
00 03 * * * bash /backup/mysqldata/dbbackup.sh
# 代表意义  分钟  小时   日期  月份   周 
# 数字范围  0-59  0-23  1-31  1-12  0-6

6. 验证定时任务设置

# 重启cron服务
sudo systemctl restart cron
# 检查cron服务状态
sudo systemctl status cron
# 查看定时任务列表
crontab -l
# 出现下面这行，说明设置成功
00 03 * * * bash /backup/mysqldata/dbbackup.sh

7. Linux crontab 命令

语法

crontab [ -u user ] file

或

crontab [ -u user ] { -l | -r | -e }

说明：

crontab 是用来让使用者在固定时间或固定间隔执行程序之用，换句话说，也就是类似使用者的时程表。

-u user 是指设定指定 user 的时程表，这个前提是你必须要有其权限(比如说是 root)才能够指定他人的时程表。如果不使用 -u user 的话，就是表示设定自己的时程表。

参数说明：

• -e : 执行文字编辑器来设定时程表，内定的文字编辑器是 Vi/Vim，如果你想用别的文字编辑器，则请先设定 VISUAL 环境变数来指定使用那个文字编辑器(比如说 setenv VISUAL joe)
• -r : 删除目前的时程表
• -l : 列出目前的时程表

查看当前用户的 crontab 文件：

crontab -l

编辑当前用户的 crontab 文件：

crontab -e

删除当前用户的 crontab 文件：

crontab -r

列出某个用户的 crontab 文件（需要有相应的权限）：

crontab -u username -l

编辑某个用户的 crontab 文件（需要有相应的权限）：

crontab -u username -e

---

格式

时间格式如下：

f1 f2 f3 f4 f5 program

• 其中 f1 是表示分钟，f2 表示小时，f3 表示一个月份中的第几日，f4 表示月份，f5 表示一个星期中的第几天。program 表示要执行的程序。
• 当 f1 为 * 时表示每分钟都要执行 program，f2 为 * 时表示每小时都要执行程序，其馀类推
• 当 f1 为 a-b 时表示从第 a 分钟到第 b 分钟这段时间内要执行，f2 为 a-b 时表示从第 a 到第 b 小时都要执行，其馀类推
• 当 f1 为 */n 时表示每 n 分钟个时间间隔执行一次，f2 为 */n 表示每 n 小时个时间间隔执行一次，其馀类推
• 当 f1 为 a, b, c,… 时表示第 a, b, c,… 分钟要执行，f2 为 a, b, c,… 时表示第 a, b, c…个小时要执行，其馀类推

*    *    *    *    *
-    -    -    -    -
|    |    |    |    |
|    |    |    |    +----- 星期中星期几 (0 - 6) (星期天 为0)
|    |    |    +---------- 月份 (1 - 12) 
|    |    +--------------- 一个月中的第几天 (1 - 31)
|    +-------------------- 小时 (0 - 23)
+------------------------- 分钟 (0 - 59)

时间格式设置：

时间设置	含义
* * * * *	每分钟执行一次
0 * * * *	每小时的第 0 分钟执行一次
0 0 * * *	每天的午夜（0 点）执行一次
0 0 * * 0	每周日的午夜（0 点）执行一次
0 0 1 * *	每个月的第一天午夜（0 点）执行一次
0 0 L * *	每个月的最后一天午夜（0 点）执行一次
0 0 1 1 *	每年的第一天午夜（0 点）执行一次
0 0 * * 3	每周三的午夜（0 点）执行一次
0 0 1,15 * *	每个月的第 1 和第 15 天午夜（0 点）执行一次
0 0 * * FRI	每周五的午夜（0 点）执行一次
0 0 * * 5	每周五的午夜（0 点）执行一次
0 8-17 * * *	每天的上午 8 点到下午 5 点每小时执行一次
0 12 * * MON	每周一的中午（12 点）执行一次
0 0 15 * *	每个月的第 15 天午夜（0 点）执行一次
0 0 * * 3	每周三的午夜（0 点）执行一次
0 8-17 * * *	每天的上午 8 点到下午 5 点每小时执行一次
0 0 * * 1-5	每个工作日的午夜（0 点）执行一次
0 0 1 * FRI	每个月的第一个星期五午夜（0 点）执行一次
0 0 1,15 * *	每个月的第 1 和第 15 天午夜（0 点）执行一次
0 0 15 1 *	每年的 1 月 15 日午夜（0 点）执行一次
0 0 * * 7	每周日的午夜（0 点）执行一次
0 0 * * 5	每周五的午夜（0 点）执行一次