mysql是一个免费、开源中一款非常优秀关系型数据库，在现在的互联网中使用的非常广泛，无论是大型IT项目还是个人开发者的小项目，mysql都能很好的协助人们处理数据库相关的工作，同时数据库对于我们来说是非常重要，所以经常备份数据库是一个基本的操作，这会为你或者你的团队，减少非常多不必要的麻烦。

mysqldump是一个简单而且非常流行的mysql全量备份方式，配合crontab添加自动备份任务，很好的完成了我们针对数据库备份的需求，下面我会通过一个例子来说明如何完成这项操作。

mysqldump是mysql自带的备份工具，所以只要你安装mysql应用包，就无需单独安装mysqldump

测试环境

我搭建了一个Ghost博客环境，数据库采用是的Mysql，接下来我想在每天00:00执行数据库备份操作，并在备份完成之后，告诉我是否备份成功。

HowTo Automatic EasyBackup MysqlDB - 图1

所以我们的步骤应该：

  1. 测试备份命令是否可以正常执行
  2. 安装测试命令行邮件工具mailx
  3. 安装计划任务工具Crontab
  4. 编写备份脚本
  5. 添加计划任务

测试备份命令

首先你要获得你要备份的数据库对应的select权限，仅需要select权限即可，mysql在管理方面，应该坚持只赋予必须权限的原则。

  1. mysql> grant select on ghost.* to 'ghost_backuser'@'localhost' identified by 'backupPass';
  2. Query OK, 0 rows affected (0.00 sec)

因为我只在本地执行备份操作，所以我只赋予了localhost的权限，你的权限应该要是执行备份工作的服务器主机信息。

  1. # 创建仅授权本地访问的用户
  2. mysql> create user dbackuser@'localhost';
  3. # 创建授权所有来源地址的用户
  4. mysql> create user dbackuser@'%';
  5. # 创建仅授权从特定IP的用户
  6. mysql> create user dbackuser@'192.168.0.230';
  7. # 创建仅授权从特定IP段访问的用户
  8. mysql> create user dbackuser@'192.168.0.0/23';
  9. # 创建仅授权从特定域名来访问的用户
  10. mysql> create user dbackuser@'samzong.me';

好了，接下来我们测试对应用户是否有权限。

  1. ➜  ~ mysql -u ghost_backuser -pbackupPass
  2. Warning: Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 7
  5. Server version: 5.6.35 MySQL Community Server (GPL)
  7. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> show databases;
  16. +--------------------+
  17. | Database           |
  18. +--------------------+
  19. | information_schema |
  20. | ghost              |
  21. +--------------------+
  22. 2 rows in set (0.00 sec)
  24. mysql> use ghost;
  25. Reading table information for completion of table and column names
  26. You can turn off this feature to get a quicker startup with -A
  28. Database changed
  30. mysql> select name from users;
  31. +------+
  32. | name |
  33. +------+
  34. | ALEX |
  35. +------+
  36. 1 row in set (0.00 sec)

测试mysqldump备份命令，注意mysqldump备份会锁表，但对于正在工作的数据库，锁表会影响到正常业务，所以我们可以使用--single-transaction参数，不锁表备份。

  1. ➜  ~ mysqldump -u ghost_backuser -pbackupPass ghost > ghost.bak.sql
  2. Warning: Using a password on the command line interface can be insecure.
  3. mysqldump: Got error: 1044: Access denied for user 'ghost_backuser'@'localhost' to database 'ghost' when using LOCK TABLES
  4. ➜  ~ mysqldump -u ghost_backuser -pbackupPass --single-transaction ghost > ghost.bak.sql
  5. Warning: Using a password on the command line interface can be insecure.
  6. ➜  ~ ls -lh
  7. total 780K
  8. -rw-r--r-- 1 root   root  780K May 17 16:24 ghost.bak.sql
  9. ➜  ~

安装命令行邮件工具mailx

安装mailx 在CentOS/RehHat:

  1. ➜  ~ yum install -y mailx

测试发送邮件：

  1. ➜  ~ echo "test" | mail -s "this a test email" samzong.lu@gmail.com

HowTo Automatic EasyBackup MysqlDB - 图2

安装计划任务工具Crontab

crontab命令常见于Unix和类Unix的操作系统之中，用于设置周期性被执行的指令。该命令从标准输入设备读取指令，并将其存放于“crontab”文件中。通常，crontab储存的指令被守护进程激活， crond常常在后台运行，每一分钟检查是否有预定的作业需要执行。这类作业一般称为cron jobs。

  1. ➜  ~ yum install vixie-cron
  2. ➜  ~ yum install crontabs

vixie-cron软件包是cron的主程序；

crontabs软件包是用来安装、卸装、或列举用来驱动 cron 守护进程的表格的程序。

启动crond并设置为开机自启动：

  1. ➜  ~ service crond start
  2. Starting crond:                                            [  OK  ]
  3. ➜  ~ chkconfig crond on

crontab 基础命令：

  1. 语　　法：crontab [-u <用户名称>][配置文件] 或 crontab [-u <用户名称>][-elr]
  3. 补充说明：cron是一个常驻服务，它提供计时器的功能，让用户在特定的时间得以执行预设的指令或程序。只要用户会编辑计时器的配置文件，就可以使用计时器的功能。
  5. 配置文件格式：Minute Hour Day Month DayOFWeek Command
  7. 参　　数：
  8. -e 　编辑该用户的计时器设置。
  9. -l 　列出该用户的计时器设置。
  10. -r 　删除该用户的计时器设置。
  11. -u<用户名称> 　指定要设定计时器的用户名称。

crontab 配置的基本格式：

  1. *     * 　 *　  *　  *　　command
  2. 分　  时　 日　 月　 周　  命令
  4.  第1列表示分钟1～59 每分钟用*或者 */1表示
  5.  第2列表示小时1～23（0表示0点）
  6.  第3列表示日期1～31
  7.  第4列表示月份1～12
  8.  第5列标识号星期0～6（0表示星期天）
  9.  第6列要运行的命令

crontab 的一些例子：

  1. #每晚的21:30 重启apache
  2. 30 21 * * * /usr/local/etc/rc.d/lighttpd restart
  4. #每月1、10、22日的4 : 45重启apache
  5. 45 4 1,10,22 * * /usr/local/etc/rc.d/lighttpd restart
  7. #每周六、周日的1 : 10重启apache
  8. 10 1 * * 6,0 /usr/local/etc/rc.d/lighttpd restart
  10. #每天18 : 00至23 : 00之间每隔30分钟重启apache
  11. 0,30 18-23 * * * /usr/local/etc/rc.d/lighttpd restart
  13. #每星期六的11 : 00 pm重启apache
  14. 0 23 * * 6 /usr/local/etc/rc.d/lighttpd restart
  16. #晚上11点到早上7点之间，每隔一小时重启apache
  17. * 23-7/1 * * * /usr/local/etc/rc.d/lighttpd restart
  19. #每一小时重启apache
  20. * */1 * * * /usr/local/etc/rc.d/lighttpd restart
  22. #每月的4号与每周一到周三的11点重启apache
  23. 0 11 4 * mon-wed /usr/local/etc/rc.d/lighttpd restart
  25. #一月一号的4点重启apache
  26. 0 4 1 jan * /usr/local/etc/rc.d/lighttpd restart
  28. #每半小时同步一下时间
  29. */30 * * * * /usr/sbin/ntpdate 210.72.145.44

编写备份脚本

好了，以上我们测试需要用到的各个模块，下面我们要编写备份脚本：

  1. #!/bin/bash
  2. #  mysqldump scripts.
  3. # filepath: /usr/local/bin/ghost_sqldump.sh
  4. # Author: samzong
  6. #  set TIME variable
  7. TIME=`date "+%Y%m%d%H%M%S"`
  9. # backup db ghost to /mysqlbak/
  10. mysqldump --single-transaction -h localhost -u ghost_backuser -pbackupPass ghost  > /mysqlbak/ghost_$TIME.sql
  12. # tar sql files.
  13. tar czvf /mysqlbak/ghost_$TIME.sql.tgz /mysqlbak/ghost_$TIME.sql --remove-files
  15. # remove over 7 days sql files.
  16. find /mysqlbak/ -mtime +7 -exec rm -f {} \;
  18. # send mial to admin'mial
  19. if [ $? -eq 0 ]
  20. then
  21.     echo "ghost SQL dump is successfully. At time: `date` " | mail -s ghost-dump-successfully  samzong.lu@gmail.com
  22. else
  23.     echo " Error Error ghost SQL dump is Error. At time: `date` " | mail -s ghost-dump-error samzong.lu@gmail.com
  24. fi

注意脚本中的以下内容要根据你的实际情况修改:

  • -h “ “ 这是数据库所在的主机
  • -u “ “ 这是数据库可备份的用户名
  • -p” “ 备份用户的密码

添加计划任务

经过以上测试，已经很好的完成备份脚本，接下来将脚本添加到crontab内，并设置自动执行的时间：

  1. ➜  ~ crontab -e
  2. 00 00 * * * sh /usr/local/bin/ghost_sqldump.sh

重启crontab服务，并确认crontab已经设置为开机自启动

  1. ➜  ~ service crond restart
  2. Stopping crond:                                            [  OK  ]
  3. Starting crond:                                            [  OK  ]
  4. ➜  ~ chkconfig --list | grep crond
  5. crond              0:off    1:off    2:on    3:on    4:on    5:on    6:off