MYSQL--如何用日志文件恢复数据

时间：2010-07-18 来源：909413335

MYSQL的二进制日志文件是MYSQL安全系统里的非常重要的东西，虽然打开日志开关后，所有非SELECT的操作都会记录下来，这在某些场合显得很难堪，比如还原数据库，或者导入数据等，日志文件会一条不漏的记录下所有的命令，这会使日志文件迅速增肥。但正是因为它的这一身肥肉，让数据库的安全性变得更加可靠，不知道这是否可用解释为什么胖人看起来都比较可靠呢？下面具体操作下，看是怎么样通过日志文件，找回丢失的数据的。

1：首先打开日志文件记录的开关，在MY.INI里，MYSQLD项下添加log-bin=mysql，这样在DATA目录下，就会产生如MYSQL.000001,MYSQL.000002之类的二进制文件了。如果只对某个库进行日志文件记录，那么再添加binlog-do-db=TEST，这样就只记录TEST数据库的日志，而放弃其他的。添加完后，重新启动MYSQL的服务，日志文件开始工作了。

2：到TEST库里新增加一个表，如T1，再添加几条记录进去。

mysql> use test;
Database changed
mysql> create table t1(a int,b char(10));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into t1 values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

此时T1表里有3条记录了，而且也产生了日志文件MYSQL.000001.

3：下面模拟数据丢失的情况。如果因为误操作，做了如下操作
mysql> delete from t1;
Query OK, 3 rows affected (0.14 sec)

mysql> select * from t1;
Empty set (0.00 sec)

表记录被清空了。

4：现在通过日志文件来找回前面的三条记录

打开日志文件，看看里面到底有什么

D:\mysql6\bin>mysqlbinlog d:\mysql6\data\mysql.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100602 20:08:02 server id 1 end_log_pos 106   Start: binlog v 4, server v 5.1.45-community-log created 100602 20:08:02 at startu
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
okkGTA8BAAAAZgAAAGoAAAABAAQANS4xLjQ1LWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACiSQZMEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#100602 20:09:39 server id 1 end_log_pos 202   Query   thread_id=2     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1275480579/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1(a int,b char(10))
/*!*/;
# at 202
#100602 20:10:00 server id 1 end_log_pos 270   Query   thread_id=2     exec_time=1     error_code=0
SET TIMESTAMP=1275480600/*!*/;
BEGIN
/*!*/;
# at 270
#100602 20:10:00 server id 1 end_log_pos 377   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1275480600/*!*/;
insert into t1 values(1,'a'),(2,'b'),(3,'c')
/*!*/;
# at 377
#100602 20:10:00 server id 1 end_log_pos 404   Xid = 5
COMMIT/*!*/;
# at 404
#100602 20:12:45 server id 1 end_log_pos 472   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1275480765/*!*/;
BEGIN
/*!*/;
# at 472
#100602 20:12:45 server id 1 end_log_pos 549   Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1275480765/*!*/;
delete from t1
/*!*/;
# at 549
#100602 20:12:45 server id 1 end_log_pos 576   Xid = 7
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

D:\mysql6\bin>

哦，卖糕的，用MYSQLBINLOG工具打开后，里面记录的居然是前面操作的SQL命令，注意看红色字体部分。因为只是删除了数据，表结构还是存在的，那么只需要把前面的INSERT的语句给提取出来，再导回到数据库就可以了。

5：提取需要的日志文件段落

D:\mysql6\bin>mysqlbinlog d:\mysql6\data\mysql.000003 --start-position=270 --stop-position=377 -r 3.sql

270和377，是日志的记录位置，只取这一段的就可以了。如蓝色字体所示。

执行成功后，到BIN目录下，找到3.SQL文件，用写字板打开看看，里面是否有前面的INSERT语句？希望就在前头了。

6：把提取出来的日志文件段导入到数据库中

mysql> source d:\mysql6\bin\3.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> use test;
Database changed
mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
a +------+------+
3 rows in set (0.00 sec)

数据回来了。一切尽在掌握中。

对于如把表DROP掉了，或者UPDATE错了记录，都可以用这个办法恢复到正确的状态。