MySQL定时备份方案

虽说现在这世道有些爱情是有价的,但是数据是无价的,数据备份是尤为的重要,可以在你未来的某一天不小心删库了,不用着急跑路。

image

本片文章介绍的方案是利用Linux自身的crontab定时任务功能,定时执行备份数据库的脚本。

技术要点:

  • 数据库备份dump命令
  • shell脚本
  • Linux定时任务crontab

数据备份dump

数据库都有一个导出数据库内数据和结构的命令,就是备份。
将备份的数据还原会将原来的数据中的表删了重建,再插入备份中的数据,这是恢复。
这一点需要注意,如果恢复之前的数据比备份的多,恢复后多的数据就没有了。

列出我常用的两种数据库的备份和恢复命令

postgresql:

备份 pg_dump -h [ip] -U [用户名] [库名] >[导出的.sql 文件]
恢复 psql -s [库名] -f [导出.sql 文件]

mysql:

备份 mysqldump -h -u [用户名] -p [库名] > [导出的.sql 文件]
恢复 mysql -u [用户名] -p [库名] < [导出的.sql 文件]

shell脚本

要完成一个功能完善的备份方案,就需要shell脚本。
我们要让这个脚本备份到指定路径,并压缩存放,最多30个,超过30个删除最早的,并记录操作日志。
啥也不说了,话都在脚本里,干了!

#用户名
username=root
#密码
password=nicai
#将要备份的数据库
database_name=l_love_you

#保存备份文件最多个数
count=30
#备份保存路径
backup_path=/app/mysql_backup
#日期
date_time=`date +%Y-%m-%d-%H-%M`

#如果文件夹不存在则创建
if [ ! -d $backup_path ]; 
then     
    mkdir -p $backup_path; 
fi
#开始备份
mysqldump -u $username -p$password $database_name > $backup_path/$database_name-$date_time.sql
#开始压缩
cd $backup_path
tar -zcvf $database_name-$date_time.tar.gz $database_name-$date_time.sql
#删除源文件
rm -rf $backup_path/$database_name-$date_time.sql
#更新备份日志
echo "create $backup_path/$database_name-$date_time.tar.gz" >> $backup_path/dump.log

#找出需要删除的备份
delfile=`ls -l -crt  $backup_path/*.tar.gz | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于阈值
number=`ls -l -crt  $backup_path/*.tar.gz | awk '{print $9 }' | wc -l`

if [ $number -gt $count ]
then
  #删除最早生成的备份,只保留count数量的备份
  rm $delfile
  #更新删除文件日志
  echo "delete $delfile" >> $backup_path/dump.log
fi

给脚本起个顾名思义的漂亮名字 dump_mysql.sh
给脚本赋予可执行权限 chmod +x dump_mysql.sh, 执行后脚本变绿了就是可实行文件
执行方法:./加脚本名称

chmod命令参数含义--
+ 代表添加某些权限
x 代表可执行权限

定时任务crontab

crontab是Linux自带的一个定时任务功能,我们可以利用它每天凌晨执行一次dump_mysql.sh脚本。

crontab用法:
  • crontab -l 查看定时任务列表
  • crontab -e 编辑(新增/删除)定时任务

运行crontab -e命令,打开一个可编辑的文本,输入00 01 * * * /app/dump_mysql.sh
保本并退出即添加完成。

内容解释:

00 01 * * * /app/dump_mysql.sh 分两部分看,
第一部分00 01 * * * 是定时任务的周期,第二部分/app/dump_mysql.sh到时间做的事情。
周期表达式是五个占位符,分别代表:分钟、小时、日、月、星期

占位符用*表示,用在第一位就是每分钟,第二位每小时,依此类推
占位符用具体数字表示具体时间,10用在第一位就是10分,用在第三位表示10号,依此类推
占位符用-表示区间,5-7用在第一位就是5分到7分,用在第五位表示周5到周日,依此类推
占位符用/表示间隔,5-10/2用在第一位就是5分到10分间隔2分钟,用在第二位表示5点到10点间隔2小时,依此类推
占位符用,表示列表,5,10用在第一位就是5分和10分,用在第四位表示5月和10月,依此类推


2020年7月20日更新

脚本中密码安全性问题

多谢思友hack的提醒,脚本里面直接放数据库密码是不安全的。
我们脚本执行后msyql也会打印一行警告:
mysqldump: [Warning] Using a password on the command line interface can be insecure
译:在命令行界面上使用密码可能不安全

关于这个问题网上的处理方法也比较一致,mysqldump命令去掉-u用户名-p密码参数,使用--defaults-file参数读取配置的用户名密码。

也是官网给出的方案:
image.png

在命令行上指定密码应该被认为是不安全的。为了避免在命令行上给出密码,请使用选项文件。参见 第6.1.2.1节: 用户密码安全指南。

image.png

编辑my.cnf

编辑数据库家目录的my.cnf,我的路径是/etc/my.cnf。在[client]下面添加密码,如果没有[client],就编写一个,不然下面的password 会无效。

[client]
password="nicai"

默认使用当前登录Linux服务器的账号作为连接数据库的账号,我刚好都是root ,用户名就可以不配,如果不一致,需要加一条

user=xxx

编辑完了最好给my.cnf 设置600的权限,600代表只有拥有者有读写权限。

shell> chmod 600 my.cnf

如果密码中含有特殊字符,一定要用双引号引起来,不然运行mysqldump命令会报错
mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

修改参数

运行mysqldump命令时,使用--defaults-file=/etc/my.cnf参数代替-uroot -ppassword的参数即可。
脚本中去掉用户名和密码,mysqldump命令按照下面修改:

mysqldump --defaults-file=/etc/my.cnf $database_name >$backup_path/$database_name-$date_time.sql

上面的方法虽然是官方提供的,但还是把明文写在文件里,只不过是加了读写权限而已,还是没太有说服力,可以考虑下面的方法
使用SHC对脚本加密:

Linux下脚本加密工具SHC对shell脚本进行加密,SHC可以将shell脚本转换为一个可执行的二进制文件,这也是一个办法。

对备份的文件也需要加密:

tar配合openssl完成加密压缩:
tar -czvf - 源文件 | openssl des3 -salt -k 密码 -out 压加密文件.tar.gz
解密:
openssl des3 -d -k 密码 -salt -in 加密文件.tar.gz | tar xvf -

阅读 731

推荐阅读
苟且偷生
用户专栏

大树的专栏

4 人关注
19 篇文章
专栏主页