教你用mysql做定时备份任务

简介

在生产环境上，为了避免数据的丢失，通常情况下都会定时的对数据库进行备份。而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份。首先我们来简单了解crontab指令，如果你会了请跳到下一个内容mysql备份。
本文章的mysql数据库是安装在docker容器当中，以此为例进行讲解。没有安装到docker容器当中也可以参照参照。

contab定时任务

使用crontab -e来编写我们的定时任务。

0 5 * * 1 [command]

前面的5个数字分别代表分、时、日、月、周，后面的 command为你的执行命令。
假如你需要在每天晚上8点整执行定时任务，那么可以这么写

0 8 * * * [command]

扩展：
crontab -l 可以查看自己的定时任务
crontab -r 删除当前用户的所有定时任务

mysql备份

快速上手

这里我的mysql数据库是docker容器。假如你需要在每天晚上8点整执行定时任务，那么可以这么写。
首先执行命令crontab -e。

0 8 * * * docker exec mysql_container mysqldump -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

mysql_container 为你的数据库容器名
mysqldump 是mysql数据库导出数据的指令
-u 填写root账号
-p 填写root密码
database_name 需要备份的数据库名
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql 备份文件，后面是文件名的格式

如果你没什么要求，单纯的只是想要备份，那么上面那个命令就可以帮你进行定时备份。

小坑： mysql备份的时候我使用了docker exec -it mysqldump … 这样的命令去做bash脚本，因为-i参数是有互动的意思，导致在crontab中执行定时任务的时候，没有输出数据到sql文件当中。所以使用crontab定时的对docker容器进行备份命令的时候不要添加-i参数。

crontab优化

我不建议直接在crontab -e里面写要执行的命令，任务多了就把这个文件写的乱七八招了。
建议把数据库备份的命令写成一个bash脚本。在crontab这里调用就好了
如：建立一个/var/backups/mysql/mysqldump.sh文件，内容如下

docker exec mysql_container mysqldump -uroot -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

然后把文件改为当前用户可执行的：

chmod 711 /var/backups/mysql/mysqldump.sh

执行crontab -e命令修改成如下：

0 20 * * * /var/backups/mysql/mysqldump.sh

那么这样就比较规范了。

mysql备份优化

因为sql文件比较大，所以一般情况下都会对sql文件进行压缩，不然的话磁盘占用就太大了。 假设你做了上面这一步 crontab优化，我们可以把mysqldump.sh脚本改成下面这样：

export mysqldump_date=$(date +%Y%m%d_%H%M%S) && \
docker exec mysql_container mysqldump -uroot -pmypassword database_name> /var/backups/mysql/$mysqldump_date.sql && \
gzip /var/backups/mysql/$mysqldump_date.sql
find /var/backups/mysql/ -name "*.sql" -mtime +15 -exec rm -f {} \;

export 在系统中自定义了个变量mysqldump_date，给备份和压缩命令使用
gzip 为压缩命令，默认压缩了之后会把源文件删除，压缩成**.gz文件
find … 这行命令的意思为，查询/var/backups/mysql/目录下，创建时间15天之前(-mtime +15)，文件名后缀为.sql的所有文件 执行删除命令-exec rm -f {} ;**。总的意思就是：mysql的备份文件只保留15天之内的。15天之前的都删除掉。

数据恢复

若一不小心你执行drop database，稳住，淡定。我们首先要创建数据库被删除的数据库。

>mysql create database database_name;

然后恢复最近备份的数据。恢复备份的命令：

docker exec -i mysql_container mysql -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql

虽然恢复了备份文件的数据，但是备份时间点之后的数据我们却没有恢复回来。
如：晚上8点进行定时备份，但是却在晚上9点drop database，那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。这时候就要使用binlog日志了。

binlog日志

binlog 是mysql的一个归档日志，记录的数据修改的逻辑，如：给 ID = 3 的这一行的 money 字段 + 1。首先登录mysql后查询当前有多少个binlog文件:

> mysql show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       729 | No        |
| binlog.000002 |      1749 | No        |
| binlog.000003 |      1087 | No        |
+---------------+-----------+-----------+

查看当前正在写入的binlog

mysql> show master status\G;

生成新的binlog文件，mysql的后续操作都会写入到新的binlog文件当中，一般在恢复数据都时候都会先执行这个命令。

mysql> flush logs

查看binlog日志

mysql> show binlog events in 'binlog.000003';

小知识点：初始化mysql容器时，添加参数–binlog-rows-query-log-events=ON。或者到容器当中修改/etc/mysql/my.cnf文件，添加参数binlog_rows_query_log_events=ON，然后重启mysql容器。这样可以把原始的SQL添加到binlog文件当中。

恢复数据

拿回上面例子的这段话。

晚上8点进行定时备份，但是却在晚上9点drop database，那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。。

首先进入到mysql容器后，切换到/var/lib/mysql目录下，查看binlog文件的创建日期

cd /var/lib/mysql
ls -l
...
-rw-r----- 1 mysql mysql      729 Jun 19 15:54  binlog.000001
-rw-r----- 1 mysql mysql     1749 Jun 19 18:45  binlog.000002
-rw-r----- 1 mysql mysql     1087 Jun 19 20:58  binlog.000003
...

从文件日期可以看出：当天时间为2020-06-21，binlog.000002文件的最后更新时间是 18:45 分，那么晚上8点的备份肯定包含了binlog.000002的数据；

binlog.000003的最后更新日期为 20:58 分，那么我们需要恢复的数据 = 晚上8点的全量备份 + binlog.000003的 20:00 - 执行drop database命令时间前的数据。

恢复命令格式：

mysqlbinlog [options] file | mysql -uroot -proot_password database_name

mysqlbinlog常用参数：

–start-datetime 开始时间，格式 2020-06-19 18:00:00
–stop-datetime 结束时间，格式同上
–start-positon 开始位置，（需要查看binlog文件）
–stop-position 结束位置，同上

恢复备份数据和binlog数据前建议先登录mysql后执行flush logs生成新的binlog日志，这样可以专注需要恢复数据的binlog文件。

首先我们需要查看binlog日志，在哪个位置进行了drop database操作：

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                        |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4                                                                                                           |
| binlog.000003 | 125 | Previous_gtids |         1 |         156 |                                                                                                                                             |
| binlog.000003 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                        |
| binlog.000003 | 235 | Query          |         1 |         318 | BEGIN                                                                                                                                       |
| binlog.000003 | 318 | Rows_query     |         1 |         479 | # INSERT INTO `product_category` SET `name` = '床上用品' , `create_time` = 1592707634 , `update_time` = 1592707634 , `lock_version` = 0      |
| binlog.000003 | 479 | Table_map      |         1 |         559 | table_id: 139 (hotel_server.product_category)                                                                                               |
| binlog.000003 | 559 | Write_rows     |         1 |         629 | table_id: 139 flags: STMT_END_F                                                                                                             |
| binlog.000003 | 629 | Xid            |         1 |         660 | COMMIT /* xid=2021 */                                                                                                                       |
| binlog.000004 | 660 | Anonymous_Gtid |         1 |         739 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                        |
| binlog.000004 | 739 | Query          |         1 |         822 | drop database hotel_server /* xid=26 */                                                                                                     |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

根据上面的日志，我们可以看到，在End_log_pos= 822 的位置执行了drop database操作，那么使用binlog恢复的范围就在2020-06-19 20:00:00 - 660 的位置。为什么是660？因为drop database的上一个事务的提交是660的位置，命令如下：

mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password datbase_name

如果你的范围包括了822的位置，那么就会帮你执行drop database命令了。不信你试试？
执行完上面的命令，你的数据就会恢复到drop database前啦！开不开心，激不激动！

总结

因为mysql定时备份是在生产环境上必须的任务。是很常用的。如果帮到你了，关注我一波呗！谢谢。