MySQL 备份方案

方案1 全备份+增量日志

技术实现：

全备份建议使用mysqldump，即生成可移植到其它机器的SQL脚本文件，过程较慢

其他方式比较：

l  直接拷贝：需要依赖服务器的操作系统，硬件，数据库的版本。必须关闭数据库服务或者锁定表，避免拷贝过程中的写操作。

l  Mysqlhotcopy：支持热备份，通过锁表，拷贝进行备份的perl脚本，但只用于备份MyISAM表，我们一般都使用InnoDB，InnoDB所有的表都保存在同一个数据文件 ibdata1 中

增量日志使用mysqlbinlog导出SQL脚本

执行方案：

每周做一次全备份，同时启用二进制日志备份

时间间隔可以根据数据库规模调整，只要能根据全备份和增量日志恢复任一时间点的数据即可

全备份检查，可以在测试环境恢复进行检查，没问题的话，可以删除上周的全备份（或者保留二周，视实际情况），同时清除对应时间的二进制日志

步骤示例：

全备份：

mysqldump --flush-logs --routines -uroot-p 数据库名称 > /服务器路径/文件名.sql

参数说明

l  --opt                 默认选项，相当于同时添加--add-drop-tables --add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset 选项

l  --add-drop-tables 创建表前先删除表

l  --add-locking    insert加上lock，在导入时防止并发的对表记录操作

l  --create-option 生成创建表语句

l  --disable-keys  导入时先insert数据，再创建索引

l  --extended-insert 生成bulk insert脚本，加快导入速度

l  --lock-tables     锁定所有表，以保证数据的一致性。这是一个全局读锁

l  --quick                用于大表导出，不经过内存缓存

l  --set-charset    保证数据使用相同字符集

l  --flush-logs       备份的时候顺便做bin-log日志切割

l  --routines        导出存储过程以及自定义函数

l  --triggers         备份触发器

l  --events         导出数据库中events

l  --no-data         不导出任何数据，只导出数据库表结构

l  --no-create-info只导出数据，而不添加 CREATE TABLE 语句

l  --single-transaction导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它适用于InnoDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。

日志备份

启用二进制日志，修改MySQL配置文件

[mysqld]

log-bin=文件名

需重启mysql服务生效

全备份恢复

USE 恢复数据库名;

source /服务器路径/文件名.sql;

日志备份恢复

l  通过mysqlbinlog导出对应全备份日期后的脚本：

mysqlbinlog --start-date="2011-09-07 17:05:00" /路径/日志文件名> /路径/文件名.sql

l  导入目标库：

USE 恢复数据库名;

source /服务器路径/文件名.sql;

l  删除对应全备份日期前的日志

purge master logs before '2011-09-07 17:05:00';

也可以通过设置MySQL配置文件expire-logs-days，自动删除日志

 

方案2 Master/Slave

l  前提是已经采用MySQL的Master/Slave结构实现读写分离，可以通过Master的二进制日志，实现两台服务器的数据同步

l  一旦Master拓机，可将Slave升级为Master解决了数据备份问题，同时也可以将备份方案1应用在Slave上。

l  缺点是成本高，而且数据同步也很消耗服务器的资源。