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

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 377Query   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 404Xid = 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.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)

Query OK, 0 rows affected (0.00 sec)

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, 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)

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错了记录，都可以用这个办法恢复到正确的状态。