如果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文件,我们可以用它来恢复大部分最新的数据更改,而不会丢失到崩溃时的数据。