mysql 数据库二进制日志恢复

如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog：用于处理二进制日志文件的实用工具”。

要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为-- log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：

SHOW BINLOG EVENTS \G

你还可以从命令行输入下面的内容：

mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS \G'

将密码my_pwd替换为服务器的root密码。

1. 指定恢复时间

对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2006年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd

该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \

在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2. 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" \
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \

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

 

备份与恢复

完全备份和增量备份，以及如何执行表的维护(skipping mysiam engine)

InnoDB热备份工具是一个在线备份工具，你可以用它来在InnoDB数据库运行之时备份你的InnoDB数据库。InnoDB热备份工具不要求你关闭数据库，并且它不设置任何锁定或干扰你正常的数据库处理。InnoDB热备份工具是非免费（商业的）附加软件，它每年的证书费用是每台MySQL服务器运行的计算机 390。 穷人用不起 嘎嘎

如果你可以关闭你的MySQL服务器，你可以生成一个包含InnoDB用来管理它的表的所有文件的二进制备份。使用如下步骤：

1.    关闭MySQL服务器，确信它是无错误关闭。

2.  复制你所有数据文件（ibdata文件和.ibd文件）到一个安全的地方。  

3.   复制你所有ib_logfile文件到一个安全的地方。

4.    复制my.cnf配置文件或文件到一个安全的地方。

5.    为你InnoDB表复制.frm文件到一个安全的地方。

复制对InnoDB表起作用，所以你可以使用MySQL复制能力来在需要高可用性的数据库站点保有一份数据库的复制。

除了刚才描述的二进制备份，你也应该周期性地用mysqldump转储你的数据库。这么做的原因是，二进制文件可能被破坏而你没有注意到。转储的文件被存储成为人可读的文本文件，所以定点表的损坏修复变得更容易。再者，因为形式更简单，严重数据损坏的机会就更小。mysqldump 也有一个--single-transaction选项，你可以用它来做一个一致的快照而不用把其它客户端排除在外面。

强制恢复

用SELECT INTO OUTFILE从从数据库转储你的表，通常以这种方法获取的大多数数据是完好的。

尽管如此，你可以用它来强制InnoDB存储引擎启动同时阻止后台操作运行，以便你能转储你的表。例如：你可以在重启服务器之前，在选项文件的[mysqld]节添加如下的行：

[mysqld]

innodb_force_recovery = 4

备份策略(*)

mysqldump提供在线逻辑备份。这里使用mysqldump。

假定我们在星期日下午1点进行了备份，此时负荷较低。下面的命令可以完全备份所有数据库中的所有InnoDB表：
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
这是在线非块备份，不会干扰对表的读写。我们以前假定我们的表为InnoDB表，因此--single-transaction使用一致性地读，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见）。如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。



需要进行完全备份，但有时不方便。会产生大的备份文件并需要花时间来生成。从某个角度，完全备份并不理想，因为每个成功的完全备份包括所有数据，甚至自从上一次完全备份以来没有更改的部分。

完成了初使完全备份后，进行增量备份会更有效。这样备份文件要小得多，备份时间也较短。不利之处是，恢复时不能只重载完全备份来恢复数据。还必须要用增量备份来恢复增量更改。



要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。
恢复时MySQL二进制日志很重要，因为它们是增量备份。如果进行完全备份时确保清空了日志，则后面创建的二进制日志文件包含了备份后的所有数据更改。让我们稍稍修改前面的mysqldump命令，让它在完全备份时能够清空 MySQL二进制日志，以便转储文件包含包含新的当前的二进制日志：
shell> mysqldump --single-transaction --flush-logs --master-data=2
              --all-databases > backup_sunday_1_PM.sql
执行该命令后，数据目录则包含新的二进制日志文件，gbichot2-bin.000007。
在星期一下午1点，我们可以清空日志开始新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令创建gbichot2-bin.000008。星期日下午1点的完全备份和星期一下午1点之间的所有更改为文件gbichot2-bin.000007。该增量备份很重要，因此最好将它复制到安全的地方。(例如，备份到磁带或DVD上，或复制到另一台机器上）。在星期二下午1点，执行另一个mysqladmin flush-logs命令。星期一下午1点和星期二下午1点之间的所有所有更改为文件gbichot2-bin.000008(也应复制到某个安全的地方)。


 



为恢复进行备份






现在假设在星期三上午8点出现了灾难性崩溃，需要使用备份文件进行恢复。恢复时，我们首先恢复最后的完全备份(从星期日下午1点开始)。完全备份文件是一系列SQL语句，因此恢复它很容易：
shell> mysql < backup_sunday_1_PM.sql
在该点，数据恢复到星期日下午1点的状态。要想恢复从那时起的更改，我们必须使用增量备份，也就是，gbichot2-bin.000007和gbichot2-bin.000008二进制日志文件。根据需要从备份处取过这些文件，然后按下述方式处理：
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
我们现在将数据恢复到星期二下午1点的状态，但是从该时刻到崩溃之间的数据仍然有丢失。要想恢复，我们需要MySQL服务器将MySQL二进制日志保存到安全的位置(RAID disks, SAN, ...)，应为与数据文件的保存位置不同的地方，保证这些日志不在毁坏的硬盘上。(也就是，我们可以用--log-bin选项启动服务器，指定一个其它物理设备上的与数据目录不同的位置。这样，即使包含该目录的设备丢失，日志也不会丢失）。如果我们执行了这些操作，我们手头上会有gbichot2-bin.000009文件，我们可以用它来恢复大部分最新的数据更改，而不会丢失到崩溃时的数据。