shell脚本定时自动备份mysql数据库和mysql恢复数据

1、设置一些测试的数据

创建一个database，一些tables和一些数据

create database test_bom default charset utf8 collate utf8_general_ci;

use test_bom;

create table users(
id int not null primary key auto_increment,
name varchar(64) not null,
password char(64) not null,
email varchar(64) not null,
age tinyint,
salary decimal(10,2),
ctime datetime
)default charset=utf8;

Insert into users(name,password,email,age,salary,ctime) values("小五","123456","xiaowu
@126.com",24,5023.56,"2011-11-11 12:00:01");
Insert into users(name,password,email,age,salary,ctime) values("小六","123456","xiaowu
@126.com",24,5023.56,"2011-11-11 12:00:01");
Insert into users(name,password,email,age,salary,ctime) values("小七","123456","xiaowu
@126.com",24,5023.56,"2011-11-11 12:00:01");
Insert into users(name,password,email,age,salary,ctime) values("小八","123456","xiaowu
@126.com",24,5023.56,"2011-11-11 12:00:01");
Insert into users(name,password,email,age,salary,ctime) values("小九","123456","xiaowu
@126.com",24,5023.56,"2011-11-11 12:00:01");

2、创建一些必要的文件夹

存放备份的文件和shell脚本，以后所有的shell脚本可以统一存放和管理

我在/data目录下新建了俩个文件夹

mkdir my_shell
mkdir mysqlbackup

3、使用mysqldump备份数据

执行以下语句，将test_bom这个database的数据备份到制定目录下，并且起名mydb.sql

mysqldump -uroot -p test_bom > /data/mysqlbackup/mydb.sql

相应目录下能看到这个文件啦，说明语句没问题啦~

给大家看一下备份的sql里都有什么，有生成表的语句和表中数据的生成语句，也就是说，恢复数据的时候，我们需要手动创建database，然后执行这个备份文件就可以自动生成表和数据了。

4、写一个简单的脚本自动执行备份

先给自己的备份文件起一个带时间的名字，利用

date +%Y-%m-%d_%H-%M-%S

与之前的语句结合一下，我们就能生成带有日期的备份数据了

mysqldump -uroot -p test_bom > /data/mysqlbackup/mydb-$(date +%Y-%m-%d_%H-%M-%S).sql

如果你服务器是免密登录的，你可以不写-p哦

mysqldump -uroot test_bom > /data/mysqlbackup/mydb-$(date +%Y-%m-%d_%H-%M-%S).sql

编写shell脚本

#!/bin/bash

mysqldump -uroot test_bom > /data/mysqlbackup/mydb-$(date +%Y-%m-%d_%H-%M-%S).sql

给你的脚本授权

chmod 777 mysqlback_shell.sh

执行脚本

bash mysqlback_shell.sh 

查看是否生成了新的备份文件

如果备份文件太多，就太占地方了，我们一般会备份成压缩文件，会小不少

mysqldump -uroot test_bom |gzip> /data/mysqlbackup/mydb-$(date +%Y-%m-%d_%H-%M-%S).sql.gz

5、设置自动执行备份

使用crontab命令

编辑

crontab -e

在尾部添加，测试一下是否可以正常执行，这样写相当于每分钟执行一次，写完保存，等一会看看是否正常执行了。

* * * * * /bin/bash /data/my_shell/mysqlback_shell.sh

重新编辑crontab，设置每天晚上9：30执行

6、补充crontab的设置（可以跳过这个部分）

基本配置

如下所示配置共6列，前5列是关于执行时间配置，最后1列是具体执行命令。

.---------------- 分 (0 - 59)
|  .------------- 时 (0 - 23)
|  |  .---------- 日 (1 - 31) 
|  |  |  .------- 月 (1 - 12) 
|  |  |  |  .---- 星期 (0 - 6) (星期日可为0或7) 
|  |  |  |  | 
*  *  *  *  * 执行的命令

第一列单位为分，表示每时第几分钟，范围为0-59；
第二列单位为时，表示每天第几小时，范围为0-23；
第三列单位为日，表示每月第几天，范围为1-31；
第四列单位为月，表示每年第几月，范围为1-12；
第五列单位为星期，表示每星期第几天，范围0-7，0与7表示星期日，其他分别为星期1-6；

时间配置段类型

根据时间列中值的不同设置方式，编者总结出以下五种类型：

固定某值，指定固定值，如指定1月1日0时0分执行任务

0 0 1 1 * command

月日时分都指定了固定数值。
注：*在crontab中表示任意值都满足条件。

列表值，时间值是一个列表，如指定一个月内2、12、22日零时执行任务

0 0 2,12,22 * * command

上述日指定多个值，2号、12号和22号，以逗号分隔；

连续范围值，时间为连续范围的值，如指定每个月1至7号零时执行任务

0 0 1-7 * * command

上述日期为连续范围的值1-7时

步长值，根据指定数值跳跃步长确定执行时间，如指定凌晨1时开始每割3个小时0分执行一次任务

0 1-24/3 * * * command

上述指定从凌晨1时每3个小时执行任务，如1点0分，4点0分，7点0分等。

混合值，支持以上类型的组合，如指定每小时0至10分，22、33分以及0-60分钟每隔20分钟执行任务，如下

0-10,22,33,*/20 * * * * command

这里的分钟值采取了多种类型组合指定，包括连续范围值(0-7)，列表值(22,33)，步长值(*/20)。

说明：这几种时间配置类型是编者自己总结，希望能帮助大家更好理解。

定时语句解析工具

工具地址：https://crontab.guru，下面是工具的截图，可以用来测试自己写的对不对哦~

7、用备份的脚本恢复数据

删除之前的database

drop database test_bom;

第一步：创建database，这个database里面是没有表和数据的。

create database test_bom default charset utf8 collate utf8_general_ci;

第二步：执行语句，用最新的备份恢复

gunzip < /data/mysqlbackup/mydb-2023-12-14_09-26-01.sql.gz | mysql -uroot test_bom

再去数据库中查询，数据恢复啦~