mysql 实时恢复_MYSQL定时、实时备份/恢复的方法

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

MYSQL定时、实时备份/恢复的方法

一、备份恢复策略

进行备份或恢复操作时需要考虑一些因素：

确定要备份的表的存储引擎是事务型还是非事务型，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。

确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复的时候可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。增量备份相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间长一些。

可以考虑采用复制的方法来做异地备份，但不能代替备份，它对数据库的误操作也无能为力。

要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候进行

· 确保 MySQL 打开 log-bin 选项，有了 binlog，MySQL 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。

· 经常做备份恢复测试，确保备份时有效的，是可以恢复的。

二、逻辑备份和恢复

在 MySQL 中，逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法，因此，对于不同存储引擎混合的数据库，逻辑备份会简单一点。

1. 备份

MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在 MySQL 中，可以使用 mysqldump 工具来完成逻辑备份,如果没有指定数据库中的任何表，默认导出所有数据库中的所有表。

示例：

1. 备份所有数据库：

2. 备份数据库 test

3. 备份数据库 test 下的表 emp

4. 备份数据库 test 下的表 emp 和 dept

5. 备份数据库test 下的所有表为逗号分割的文本，备份到 /tmp:

3. 基于时间点恢复

由于误操作，比如误删除了一张表，这时使用完全恢复时没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成恢复。这种恢复叫不完全恢复，在 MySQL 中，不完全恢复分为基于时间点的恢复和基于位置的恢复。

基于时间点恢复的操作步骤：

(1) 如果是上午 10 点发生了误操作，可以用以下语句用备份和 binlog 将数据恢复到故障前：

(2) 跳过故障时的时间点，继续执行后面的 binlog，完成恢复。

4. 基于位置恢复

和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条 sql 语句同时执行。恢复的操作步骤如下：

(1) 在 shell 下执行命令：

该命令将在 /tmp 目录创建小的文本文件，编辑此文件，知道出错语句前后的位置号，例如前后位置号分别为 368312 和 368315。

(2) 恢复了以前的备份文件后，应从命令行输入下面的内容：

上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 sql 语句记录之前的 set timestamp 语句，因此恢复的数据和相关的 mysql 日志将反应事务执行的原时间。

三、物理备份和恢复

物理备份又分为冷备份和热备份两种，和逻辑备份相比，它的最大优点是备份和恢复的速度更快，因为物理备份的原理都是基于文件的 cp。

1. 冷备份

冷备份其实就是停掉数据库服务，cp 数据文件的方法。(基本不考虑这种方法)

2. 热备份

在 MySQL 中，对于不同的存储引擎热备份的方法也有所不同。

四、表的导入导出

在数据库的日常维护中，表的导入导出时很频繁的一类操作。

1. 导出

在某些情况下，为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是 sql 语句：

用来作为 Excel 显示；

单纯为了节省备份空间；

为了快速的加载数据，load data 的加载速度比普通 sql 加载要快 20 倍以上。

当导出命令中包含字段引用符时，数据中含有转义字符本身和字段引用符的字符需要被转义；

当导出命令中不包含字段引用符时，数据中含有转义字符本身和字段分割符的字符需要被转义。

注意： select ... intooutfile ... 产生的输出文件如果在目标目录下有重名文件，将不会被创建成功，源文件不会被自动覆盖。