14.3.4 使用二进制日志还原数据库

在数据库出现异常情况或者数据丢失时，可以使用二进制日志恢复数据。通过SHOW命令查看二进制日志文件的路径和文件名，然后通过mysqlbinlog工具从指定的时间点开始直到现在，或者一个指定的时间点的日志中恢复数据。

mysqlbinlog恢复数据的语法如下。

Mysqlbinlog [option] filename|mysql–uuser–ppass

其中，Option是可选项，其参数为：--start-date（起始时间点）、--stop-date（结束时间点）、--start-position（开始位置）和——stop-position（结束位置）；filename是日志文件名。

1.指定恢复时间

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

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

其中，stop-date 稍早于重大删除时间。该命令将恢复截止到在--stop-date 选项中以DATETIME格式给出的日期和时间的所有数据。这样，系统会自动执行从二进制日志有记录开始截止到2015-04-20 9:59:59，用户所做过的所有操作。与--stop-date对应的，还有--start-date属性，可以设定执行记录的开始时间。也可以两个属性都设置。

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 日志将反映事务执行的原时间。

以下是操作数据库xscj并通过二进制日志文件对数据库的恢复过程。

mysql> use xscj; Database changed mysql> show tables; +----------------+ | Tables_in_xscj | +----------------+ |kc | |xs | |xs_kc | |xs_kc_st | +----------------+ 4 rows in set (0.00 sec) mysql> insert into kc value('407','Introduction IOT','1','48','3'); Query OK, 1 row affected (0.00 sec) //在数据库xscj中向表kc插入一条信息； mysql> select from kc; +-----------+-----------------------------------+--------------+--------+-----+ |课程号 |课程名 |开课学期 |学时 |学分| +-----------+-----------------------------------+--------------+--------+-----+ |101 |计算机基础 | 1| 80| 5| |102 |程序设计与语言 | 2| 68| 4| |206 |离散数学 | 4| 68| 4| |208 |数据结构 | 5| 68| 4| |209 |操作系统 | 6| 68| 4| |210 |计算机原理 | 5| 85| 5| |310 |Computer English | 7| 64| 4| |301 |计算机网络 | 7| 51| 3| |302 |软件工程 | 7| 51| 3| |303 |物联网与短距离无线通信 | 6| 48| 3| |309 |物联网高级应用 | 8| 64| 4| |407 |Introduction IOT | 1| 48| 3| +-----------+-----------------------------------+--------------+--------+-----+ 12 rows in set (0.00 sec) //通过查询kc表可以看到插入信息成功，使用flush logs mysql> drop table kc; Query OK, 0 rows affected (0.05 sec) //删除数据库表kc； mysql> show tables; +----------------+ | Tables_in_xscj | +----------------+ |xs | |xs_kc | |xs_kc_st | +----------------+ 3 rows in set (0.00 sec) //已经成功删除，通过mysqlbinlog可以查看二进制日志文件内容： C:>mysqlbinlog --no-defaults e:\mysql-5.6.22-winx64\data\binary_log.000027 /!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/; /!40019 SET @@session.max_insert_delayed_threads=0/; /!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/; DELIMITER /!*/;

at 4

#150315 12:18:19 server id 1 end_log_pos 120 CRC32 0x9a3163b0 Start:binlog v
4, server v 5.6.22-log created 150315 12:18:19
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
CwgFVQ8BAAAAdAAAAHgAAAABAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKG RkAAbBj
MZo=
'/*!*/;
# at 120
#150315 12:25:21 server id 1 end_log_pos 235 CRC32 0x977d462d Query thread_i
d=6  exec_time=0  error_code=0
use 'xscj'/*!*/;
SET TIMESTAMP=1426393521/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.uniq
ue_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/
;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@sessio
n.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE 'kc' /* generated by server */
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


通过以上日志文件可以看到删除表kc的记录。

还可使用mysqlbinlog恢复MySQL数据库，如将数据库恢复到2015年3月11日23:38:00时的状态，执行命令及结果如下。

C:>mysqlbinlog --stop-date="2015-03-11 23:38:00" e:\mysql-5.6.22-winx64\data\binary_log.000004