MYSQL数据库定时自动备份设置

目前流行几种MYSQL数据库定时自动备份方式：

1、逻辑备份：使用mysql自带的mysqldump工具进行备份。备份成sql文件形式。

优点：最大好处是能够与正在运行的mysql自动协同工作，在运行期间可以确保备份是当时的点，它会自动将对应操作的表锁定，不允许其他用户修改(只能访问)。可能会阻止修改操作。sql文件通用方便移植。
缺点：备份的速度比较慢。如果是数据量很多的时候。就很耗时间。如果数据库服务器处在提供给用户服务状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)。那么服务就会影响的。

备注：所谓的与mysql服务器能够自动协同工作，实际上是指加参数来控制mysql服务器，比如锁定所有表只能进行读，不能进行写操作。

--lock-all-tables

2、物理备份：直接拷贝mysql的数据目录。直接拷贝只适用于myisam类型的表。这种类型的表是与机器独立的。但实际情况是，你设计数据库的时候不可能全部使用myisam类型表。你也不可能：因为myisam类型表与机器独立，方便移植，于是就选择这种表，这并不是选择它的理由。

缺点：你不能去操作正在运行的mysql服务器(在拷贝的过程中有用户通过应用程序访问更新数据，这样就无法备份当时的数据)可能无法移植到其他机器上去。更多的情况是，你会根据业务特点(比如你需要支持事务机制就必须使用innodb)，查询速度和服务性能来选择表类型的。必须保证表不被使用中。如果服务器在你正在拷贝一个表时(某个用户在进行update、insert操作)改变它，拷贝的数据就失去了备份的意义(没法准确还原到当时的点)。如果数据库表在文件系统备份过程中被修改，进入备份的表文件主语不一致的状态，而对以后的恢复表将失去意义。保证你的拷贝完整性的最好方法是：关闭服务器，拷贝文件，然后重启服务器。或者是，要锁定对应的表(对前端用户造成访问问题)。

解释直接拷贝文件，为什么不具备可移植性？

mysqldump 产生可移植到其他机器、甚至具有不同硬件结构的机器上的文本文件。直接拷贝文件不能够移植到其他机器上，除非要拷贝的表使用MyISAM 存储格式。ISAM 表只能在具有相同硬件结构的机器之间进行拷贝。例如，将文件从S PARC 的Solaris 机器拷贝到Intel 的Solaris 机器（或者相反）是行不通的。由MySQL3.23 引进的MyISAM 表存储格式可以解决这个问题，因为该格式与机器独立。因此，如果以下两个条件都满足的话，直接拷贝文件可以移植到具有不同硬件结构的机器上：即另一台机器上也必须运行MySQL3.23 以上的版本，并且文件必须表示成MyISAM 表，而不是ISAM 表。

3、双机热备份。

mysql数据库没有增量备份的机制。当数据量太大的时候备份是一个很大的问题。还好mysql数据库提供了一种主从备份的机制(也就是双机热备)
优点：适合数据量大的时候。现在明白了。大的互联网公司对于mysql数据备份，都是采用热机备份。搭建多台数据库服务器，进行主从复制。主从复制经常遇到的问题就是，如何保证数据不堵塞，不延迟。这个问题还是可以容忍的，有一些方案可以改善。毕竟有得有失的。这已经是很省心省力的方式了。

================================================

我目前应该使用什么样的备份策略的权衡：

物理备份，恢复快，当然最好是存储在一个机器上。我现在是用物理备份还是逻辑备份为好呢？
考虑到以后会迁移平台。为了保证通用性。恢复速度1分钟左右的差距我是可以容忍的(sql文件备份方式，恢复速度没有物理配备快，物理备份直接拷贝覆盖掉原来文件即可)。所以我为了跨平台，我更加愿意使用逻辑备份。存储sql文件形式。

双热机备份方式，目前硬件没有多个。技术人员有限，需要人力去维护，比较麻烦。所以排除在外。

1、总体策略：写个定时执行任务。定时在晚上或凌晨自动备份(考虑数据库服务器在运行中不能停机)代码中做成备份成功后，把以前的删掉。避免很多数据占据磁盘。
2、考虑到初期数据量这么小。使用mysqldump进行备份吧。设置在凌晨几点(4-6点这个时候基本上没什么人访问)的时候自动备份。
3、使用逻辑备份方式：恢复速度1分钟左右的差距我是可以容忍的。所以我为了跨平台，我更加愿意使用逻辑备份。存储sql文件形式。
4、每天都进行备份。由于是在凌晨的时候mysqldump去锁定，访问数据库服务器。对服务器几乎没什么影响。所以每天都可以备份。每天都一个sql文件。那么将会很多文件。所以，每次备份成功后。删除以前的文件。保留最近一个星期的备份sql文件。

备份工具的路径：/usr/bin/mysqldump
备份数据保存路径：/data/backdata/

5、备份脚本的编写

5.1 在shell脚本中调用mysqldump生成备份文件(这个工具可以生成sql文件到磁盘上去)
5.2 为了方便以后查找。每次备份的记录记录成日志形式。几点进行了备份操作，生成了什么文件名称。这样可以方便以后查阅哪天是否没有成功备份，删除的文件作为日志信息也记录下来。
5.3 让linux下的crontab进程调用脚本执行。

命令：crontab -e

打开的文件中加入代码：0 05 * * * 脚本的路径/mysqlback.sh

mysqlback.sh的内容：

# /bin/bash
DB_NAME="****"
DB_USER="****"
DB_PASS="****"
BIN_DIR="/usr/bin"
BACK_DIR="/data/backdata"
DATE="mysql-`date +'%Y%m%d-%H:%M:%S'`"
LogFile="$BACK_DIR"/dbbakup.log #日志记录保存的目录
BackNewFile=$DATE.sql

$BIN_DIR/mysqldump --opt --force -u$DB_USER  -p$DB_PASS $DB_NAME > $BACK_DIR/$DATE.sql
echo -----------------------"$(date +"%y-%m-%d %H:%M:%S")"----------------------- >> $LogFile 
echo  createFile:"$BackNewFile" >> $LogFile

#find "/data/backdata/" -cmin +1 -type f -name "*.sql" -print > deleted.txt
#-ctime表示创建时间，这里表示删除创建时间为多少天之前的文件，也就是结果只保留多少天的数据
find "/data/backdata/" -ctime +7 -type f -name "*.sql" -print > deleted.txt

echo -e "delete files:\n" >> $LogFile 

#循环删除匹配到的文件
cat deleted.txt | while read LINE
do
    rm -rf $LINE
    echo $LINE>> $LogFile
done

echo "---------------------------------------------------------------" >> $LogFile

详细步骤案例：

第一步：在服务器上配置备份目录代码：

mkdir /var/lib/mysqlbackup 
cd /var/lib/mysqlbackup 

第二步：编写备份脚本代码： vi dbbackup.sh 粘帖以下代码，务必更改其中的username,password和dbname。

#!/bin/sh
mysqldump -uuser -ppassword dbname | gzip > /var/lib/mysqlbackup/dbname`date +%Y-%m-%d_%H%M%S`.sql.gz
cd  /var/lib/mysqlbackup
rm -rf `find . -name '*.sql.gz' -mtime 10`  #删除10天前的备份文件 

其中mysqldump在LNMP环境的默认路径为：/usr/local/mysql/bin/mysqldump，SH脚本可以放于/usr/bin目录下。
Windows环境下写shell脚本在linux下运行发现如下错误：/bin/sh^M：损坏的解释器: 没有那个文件或目录

在 /etc/my.cnf 文件的 mysqldump 处设置MYSQL的用户名与密码后，SH文件内可以不填写用户密码内容！

出现这个问题的原因如下：在Windows下，每一行的结尾是\n\r，但是在Linux下文件的结尾是\n。因此在Windows环境下编辑过的文件在Linux下打开看的时候每一行的结尾就会多出来一个字符\r。
因此，想要在Windows环境下编辑的脚本应该保存为UNIX而不是DOS格式，很多文本编辑器都支持另存为Unix格式的功能。

如果脚本文件保存时使用了DOS格式，我们就可以通过以下几种方法来将它转换为UNIX格式：

用vim打开该脚本，在命令行模式下输入：set ff=unix来转换.
使用命令：sed -i ‘s/\r$//’ urfile.
使用UE打开该脚本，然后另存为UNIX换行符的脚本.

第三步：更改备份脚本权限

chmod +x dbbackup.sh 

或者：

chmod 755 /usr/bin/dbbackup.sh 

第四步：用crontab定时执行备份脚本代码：

crontab -e 

若每天晚上21点00备份，添加如下代码，

00 21 * * * /var/lib/mysqlbackup/dbbackup.sh 

1、任务调度设置文件的写法
可用crontab -e命令来编辑,编辑的是/var/spool/cron下对应用户的cron文件,也可以直接修改/etc/crontab文件
具体格式如下：
Minute Hour Day Month Dayofweek command
分钟 小时 天 月 天每星期 命令
每个字段代表的含义如下：
Minute 每个小时的第几分钟执行该任务
Hour 每天的第几个小时执行该任务
Day 每月的第几天执行该任务
Month 每年的第几个月执行该任务
DayOfWeek 每周的第几天执行该任务
Command 指定要执行的程序
在这些字段里，除了“Command”是每次都必须指定的字段以外，其它字段皆为可选

字段，可视需要决定。对于不指定的字段，要用“*”来填补其位置。
举例如下：
5       *       *          *     *     ls            指定每小时的第5分钟执行一次ls命令
30     5       *         *     *     ls            指定每天的 5:30 执行ls命令
30     7       8         *     *     ls            指定每月8号的7：30分执行ls命令
30     5       8        6     *     ls            指定每年的6月8日5：30执行ls命令
30     6       *        *     0     ls           指定每星期日的6:30执行ls命令[注：0表示星期天，1表示星期1，

以此类推，也可以用英文来表示，sun表示星期天，mon表示星期一等。]

30 3 10,20 * * ls 每月10号及20号的3：30执行ls命令[注：“，”用来连接多个不连续的时段]
25 8-11 * * * ls 每天8-11点的第25分钟执行ls命令[注：“-”用来连接连续的时段]
*/15 * * * * ls 每15分钟执行一次ls命令 [即每个小时的第0 15 30 45 60分钟执行ls命令 ]
30 6 */10 * * ls 每个月中，每隔10天6:30执行一次ls命令[即每月的1、11、21、31日是的6：30执行一次ls 命令。 ]

 每天7：50以root 身份执行/etc/cron.daily目录中的所有可执行文件

 50   7       *             *     *     root     run-parts     /etc/cron.daily   [ 注：run-parts参数表示，执行后面目录中的所有可执行文件。 ] 

2、新增调度任务可用两种方法：
1)、在命令行输入: crontab -e 然后添加相应的任务，wq存盘退出。
2)、直接编辑/etc/crontab 文件，即vi /etc/crontab，添加相应的任务。