一、概述
这里分享学习的内容是MySQL在Linux服务器上的定时备份，根据实际业务需求，一般都是在凌晨某个时间段，对数据库进行备份。
二、准备备份脚本
1. 创建备份目录
首先以 /home/backup 为备份路径，Linux命令home路径下创建文件夹
/#home mkdir backup
2. 创建Shell脚本
然后创建备份shell脚本，注意把以下命令中的DatabaseName换为实际的数据库名称
vi bkDatabaseName.sh
输入以下命令（数据库导出命令）
mysqldump -uusername -ppassword DatabaseName > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql
也可以把导出的sql自动压缩（建议压缩，实践备份出来800多M的sql是个错误文件，已踩坑= =）
mysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz
（可选）需要自动删除90天前的备份文件的话，命令另起一行，添加
find /home/backup -name "DatabaseName_*.sql.gz" -type f -mtime +90 -exec rm -rf {} \; > /dev/null 2>&1
保存文件后，给命令添加可执行权限
chmod u+x bkDatabaseName.sh
添加完可执行权限后，可以先执行一下，看脚本能不能正常使用
./bkDatabaseName.sh
三、添加计划任务
1. 检测是否安装
#如果已安装，则直接跳过安装步骤
执行 crontab 命令如果报 command not found，就表明没有安装，需要先安装 crontab
# crontab
-bash: crontab: command not found
#开始安装
yum -y install vixie-cron
Loaded plugins: fastestmirror, refresh-packagekit, security
Existing lock /var/run/yum.pid: another copy is running as pid 25960.
Another app is currently holding the yum lock; waiting for it to exit...
可能是系统自动升级正在运行，yum在锁定状态中。
可以通过强制关掉yum进程：
#rm -f /var/run/yum.pid
然后就可以使用yum命令了。
# yum -y install vixie-cron
Loaded plugins: fastestmirror, refresh-packagekit, security
Determining fastest mirrors
* base: mirrors.btte.net
* extras: mirrors.sina.cn
* updates: mirrors.sina.cn
base | 3.7 kB 00:00
extras | 3.4 kB 00:00
extras/primary_db | 32 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 1.9 MB 00:07
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package cronie.x86_64 0:1.4.4-15.el6 will be installed
--> Processing Dependency: dailyjobs for package: cronie-1.4.4-15.el6.x86_64
--> Processing Dependency: /usr/sbin/sendmail for package: cronie-1.4.4-15.el6.x86_64
--> Running transaction check
---> Package cronie-anacron.x86_64 0:1.4.4-15.el6 will be installed
--> Processing Dependency: crontabs for package: cronie-anacron-1.4.4-15.el6.x86_64
---> Package sendmail.x86_64 0:8.14.4-9.el6 will be installed
--> Processing Dependency: procmail for package: sendmail-8.14.4-9.el6.x86_64
--> Running transaction check
---> Package crontabs.noarch 0:1.10-33.el6 will be installed
---> Package procmail.x86_64 0:3.22-25.1.el6_5.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===============================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================
Installing:
cronie x86_64 1.4.4-15.el6 base 74 k
Installing for dependencies:
cronie-anacron x86_64 1.4.4-15.el6 base 31 k
crontabs noarch 1.10-33.el6 base 10 k
procmail x86_64 3.22-25.1.el6_5.1 base 162 k
sendmail x86_64 8.14.4-9.el6 base 716 k
Transaction Summary
===============================================================================================================================================================================================
Install 5 Package(s)
Total download size: 994 k
Installed size: 2.1 M
Downloading Packages:
(1/5): cronie-1.4.4-15.el6.x86_64.rpm | 74 kB 00:00
(2/5): cronie-anacron-1.4.4-15.el6.x86_64.rpm | 31 kB 00:00
(3/5): crontabs-1.10-33.el6.noarch.rpm | 10 kB 00:00
(4/5): procmail-3.22-25.1.el6_5.1.x86_64.rpm | 162 kB 00:00
(5/5): sendmail-8.14.4-9.el6.x86_64.rpm | 716 kB 00:00
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 1.3 MB/s | 994 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : procmail-3.22-25.1.el6_5.1.x86_64 1/5
Installing : sendmail-8.14.4-9.el6.x86_64 2/5
Installing : cronie-1.4.4-15.el6.x86_64 3/5
Installing : crontabs-1.10-33.el6.noarch 4/5
Installing : cronie-anacron-1.4.4-15.el6.x86_64 5/5
ERROR:dbus.proxies:Introspect error on :1.2513:/org/freedesktop/PackageKit: dbus.exceptions.DBusException: org.freedesktop.DBus.Error.NoReply: Did not receive a reply. Possible causes include: the remote application did not send a reply, the message bus security policy blocked the reply, the reply timeout expired, or the network connection was broken.
Unable to send message to PackageKit
Verifying : crontabs-1.10-33.el6.noarch 1/5
Verifying : cronie-anacron-1.4.4-15.el6.x86_64 2/5
Verifying : sendmail-8.14.4-9.el6.x86_64 3/5
Verifying : cronie-1.4.4-15.el6.x86_64 4/5
Verifying : procmail-3.22-25.1.el6_5.1.x86_64 5/5
Installed:
cronie.x86_64 0:1.4.4-15.el6
Dependency Installed:
cronie-anacron.x86_64 0:1.4.4-15.el6 crontabs.noarch 0:1.10-33.el6 procmail.x86_64 0:3.22-25.1.el6_5.1 sendmail.x86_64 0:8.14.4-9.el6
Complete!
安装后记得启动
2. 添加定时计划
#执行以下命令查看crontab状态
service crond status
服务状态控制
service crond start //启动服务
service crond stop //关闭服务
service crond restart //重启服务
service crond reload //重新载入配置
#开机启动
你也可以将这个服务在系统启动的时候自动启动:
在/etc/rc.d/rc.local这个脚本的末尾加上:
/sbin/service crond start
执行命令：
crontab -e
然后输入以下内容，代表每分钟执行一次脚本
*/1 * * * * /home/backup/bkDatabaseName.sh
0 3 * * * /home/sql/qiling.sh 凌晨3点执行
保存后可以用以下命令查看执行日志
tail -f /var/log/cron
如果正常执行，再更改前边的corn表达式，代表每晚12点执行
0 0 * * * /home/backup/bkDatabaseName.sh
如果测试发现备份出来的文件内容是空= =，则试试在脚本mysqldump前加上绝对路径
这个绝对路径指的是运行mysqldump这个命令的mysql服务的安装路径，到bin
/usr/local/mysql/bin/mysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz
四、总结
数据库的定时备份，
如果有分主从库，最好在从库上做备份；
如果只有一个数据库，条件允许的情况下，备份地址不建议和应用数据库放在一个服务器上，
以免服务器受到攻击，备份文件也丢失。
3227
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
