前言
在生产环境上，为了避免数据的丢失，通常情况下都会定时的对数据库进行备份。而Linux的crontab指令则可以帮助我们实现对数据库定时进行备份。首先我们来简单了解 crontab 指令。
1. contab定时任务
1.1 概述
如同 windows ， centos7 也有计划任务，而且设置更为灵活，好用。在 centos7 上可以利用 crontab 来执行计划任务， 依赖于 系统服务crond ，该服务是系统自带的，可以直接查看状态，启动和停止。
1.2 安装 crontabs 服务并设置开机自启
yum install crontabs
systemctl enable crond （设为开机启动）
systemctl start crond（启动crond服务）
systemctl status crond （查看状态）
1.3 设置用户自定义定时任务
任务格式如下：
Example of job definition:
.---------------- minute (0 - 59)
| .------------- hour (0 - 23)
| | .---------- day of month (1 - 31)
| | | .------- month (1 - 12) OR jan,feb,mar,apr ...
| | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
| | | | |
* * * * * user-name command to be executed
即：分钟(0-59)--小时(0-23)--日(1-31)--月(11-12)--星期(0-6,0表示周日)--用户名--要执行的命令
*/30 * * * * root /usr/local/mycommand.sh (每天，每30分钟执行一次 mycommand命令)
* 3 * * * root /usr/local/mycommand.sh (每天凌晨三点，执行命令脚本，PS:这里由于第一个的分钟没有设置，那么就会每天凌晨3点的每分钟都执行一次命令)
0 3 * * * root /usr/local/mycommand.sh (这样就是每天凌晨三点整执行一次命令脚本)
*/10 11-13 * * * root /usr/local/mycommand.sh (每天11点到13点之间，每10分钟执行一次命令脚本，这一种用法也很常用)
10-30 * * * * root /usr/local/mycommand.sh (每小时的10-30分钟，每分钟执行一次命令脚本，共执行20次)
10,30 * * * * root /usr/local/mycommand.sh (每小时的10,30分钟，分别执行一次命令脚本，共执行2次）
1.3.1 vim创建任务
1.3.1.1 vim编辑文件
vim /etc/crontab
1.3.1.2 加载任务使之生效
crontab /etc/crontab
1.3.2 使用crontab -e来编写定时任务（即时生效）
* * * * * [command]
此处不能添加用户，默认以编辑crontab文件的用户
扩展：
crontab -l 可以查看自己的定时任务
crontab -r 删除当前用户的所有定时任务
crontab -u username -l（列出用户的定时任务列表）
2. MySQL备份
2.1 快速上手
这里我的mysql数据库是docker容器。假如你需要在每天晚上8点整执行定时任务，那么可以这么写。
创建数据库备份目录:
mkdir -p /var/backups/mysql
执行数据库备份命令：
docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/test.sql
查看/var/backups/mysql/test.sql是否存在，如果存在说明备份命令没有问题。
mysql_container 数据库容器名
--single-transaction 不会锁表，也确保数据的一致性
mysqldump mysql数据库导出数据的指令
-u mysql的root账号
-p mysql的root密码
database_name 需要备份的数据库名
最后执行命令crontab -e，把mysql的备份命令写到定时任务中。
0 8 * * * docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql 备份文件，后面是文件名的格式
2.2. crontab优化
我建议直接在crontab -e里面写要执行的命令，推荐把数据库备份的命令写成一个bash脚本，在crontab这里调用。
如：创建一个/var/backups/mysql/mysqldump.sh文件，内容如下：
docker exec mysql_container mysqldump -uroot --single-transaction -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
2.3 MySQL备份优化
因为sql文件比较大，所以一般情况下都会对sql文件进行压缩，不然的话磁盘占用就太大了。
假设你做了上面这一步 crontab优化，我们可以把mysqldump.sh脚本改成下面这样：
export mysqldump_date=$(date +%Y%m%d_%H%M%S) && \
docker exec mysql_container mysqldump --single-transaction -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天之前的都删除掉。
可以使用以下指令直接导出压缩文件 mysqldump -uroot -ppass myweb | gzip > d:/backupfile.sql.gz 恢复数据时 gunzip < backupfile.sql.gz | mysql -uroot -ppass myweb
3. 数据恢复
若一不小心你执行drop database，稳住，淡定。我们首先要创建数据库被删除的数据库。
>mysql create database database_name;
然后恢复最近备份的数据。恢复备份的命令：
docker exec -i mysql_container mysql --single-transaction -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql
虽然恢复了备份文件的数据，但是备份时间点之后的数据我们却没有恢复回来。
如：晚上8点进行定时备份，但是却在晚上9点drop database，那么晚上8点到晚上9点这一个小时之内的数据却没有备份到。这时候就要使用binlog日志了。
4. binlog日志
binlog 是mysql的一个归档日志，记录的数据修改的逻辑，如：给 ID = 3 的这一行的 money 字段 + 1。
首先登录mysql后查询当前有多少个binlog文件:
Copy> 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文件当中。
5.1 恢复数据
拿回上面例子的这段话。
晚上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
总结
因为mysql定时备份是在生产环境上必须的任务，很常用。
评论区