定时备份数据库

前言

由于业务原因，经常需要操作数据库。可能一个手抖，误操作将外网数据进行删除或者批量更新。这样的后果，无疑是惨痛的。本着居安思危的想法，本文将介绍通过定时任务，执行数据库的备份。以及备份优化的常用方法。

一、Crond 定时任务

Linux crontab是用来定期执行程序的命令。

1.进入编辑窗口

# 通过管理员权限启动
# username: 默认使用 root 用户
# 例如: sudo crontab -u root -e 
$ sudo crontab -u username -e 

执行上述指令后，会进入一个编辑窗口。然后便开始编辑定时脚本。

2. 编写定时脚本

具体规则如下:

# 每5分钟，执行命令
*/5 * * * * [command]

前面的5个数字，分别代表：分、时、日、月、周
command: 表示想要执行的命令。此处可以编写shell脚本，执行复杂指令集。

例如:

$ 10 1 * * 1,3,5 sh /usr/data/backup.sh

10 1 * * 1,3,5: 是指每周1，3，5的1点10分
sh /usr/data/backup.sh：执行shell脚本

3.定时规则说明

在线小公举

说明：
Linux
*    *    *    *    *
-    -    -    -    -
|    |    |    |    |
|    |    |    |    +----- week (0 - 7) (Sunday=0 or 7) 
|    |    |    +---------- month (1 - 12) 
|    |    +--------------- day (1 - 31)
|    +-------------------- hour (0 - 23)
+------------------------- minute (0 - 59)

字段	允许值	特殊字符	备注
minute	0-59	* , - /	/x 代表每x分钟（x：数值类型）
hour	0-23	* , -
day	1-31	* , - ?	? 表示将某一天留空
month	1-12	* , -
week	0-7	* , - ?	0和7都表示周日

二、数据库备份

1. 数据备份

• 1.1 备份数据库

$ mysqldump --single-transaction  -h127.0.0.1 -P3306 -uroot -p'user_password' db_name > /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql;

-h 远程DB IP
-P 远程DB 端口号
-u 登录用户用户名
-p 登录用户密码
db_name 数据库名称
/usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql 导出的路径+文件名

• 1.2 备份数据库某一张表

$ mysqldump -h127.0.0.1 -P3306 -uroot -p'user_password' -d db_name --default-character-set=utf8 --skip-lock-tables --tables [table_name1] [table_name2] > /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql;

--tables 选择导出的数据库表名

• 1.3 按查询条件，备份数据库某一张表

$ mysqldump --single-transaction  -h127.0.0.1 -P3306 -uroot -p'user_password' db_name  table_name1 --where "1=1 limit 10"> /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql;

--where: 筛选条件
--single-transaction： 解决异常 mysqldump: 1044 Access denied when using LOCK TABLES

• 1.4 只导出表结构

$ mysqldump -h127.0.0.1 -P3306 -uroot -p'user_password' -d db_name tb_name> /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql;

• 1.5 只导出数据，不导出表结构

$ mysqldump -h127.0.0.1 -P3306 -uroot -p'user_password' -t db_name  > /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql;

# 或者
$ mysql -h127.0.0.1 -P3306 -uroot -p'user_password'  -D db_name -A --default-character-set=utf8 -e "select * from  tb_name where 1=1 limit 10" > /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql;

-t: 导出数据

• 1.6 备份优化

因为 *.sql 数据量一般比较大，定时存储的话，会消耗磁盘空间。因此，可以尝试对 sql 文件，进行压缩处理。

$ mysqldump -h127.0.0.1 -uroot -p 'user_password' db_name | gzip >  /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql.gz

| gzip 使用 gzip 压缩导出的 *.sql 文件

• 1.7导出csv

mysql -h 127.0.0.1 -P 3306 -uroot -p 'user_passwprd' -D db_name -A --default-character-set=utf8 -e "select * from guild_users" > /data/kube-backup/guild_manager/data.$(date "+%Y%m%d").csv

2. 数据恢复

• 2.1 通过命令行导入

$ mysql -h127.0.0.1 -uroot -p 'user_password'   <  /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql

• 2.2 通过source 导入

# 使用创建的数据库
$ mysql> use db_name;
# 设置编码格式，防止中文乱码
$ mysql> set names utf8;
# 导入备份数据
$ mysql> source /usr/backup/mysql/$(date + %Y%m%d_%H%M%S).sql

三、优化方案

上面我们提到了数据备份的优化，可以对原始sql文件进行压缩处理。
类似的，我们也可以考虑优化我们的定时脚本，通过定时清理过期的无效文件，达到磁盘优化的效果。

$ find ./usr/backup -name "*.sql" -mtime +5 -exec rm -rf {} \

find ./usr/backup -name "*.sql": 这可以理解为，查询文件目录下，以 *.sql 结尾的所有文件。
-mtime +5: 筛选出，创建日期超过5天的文件
-exec rm -rf {} \: 删除文件操作 (请在你充分理解你再干什么的情况下，再执行该语句！！！)

总的意思就是，清空创建时间超过5天的sql文件。

轻松一刻

在学习 crontab 规则的时候，看到了一个同行的悲惨小故事。所以，还是要提醒各位，日常开发的时候，一定要仔细检查，你是不是正在执行毁天灭地的代码!!!
以此为鉴~

参考链接

crontab 在线工具
crontab常用方法
crotab常用指令
mysql定时任务
mysql导入数据