mysql 二进制日志恢复_MYSQL如何二进制日志恢复数据库

测试流程：

1：打开二进制开关。在INI文件的MYSQLD下，添加log-bin=log，如果不指定=log，那么日志名称为hostname开头。

2:指定需要记录日志的数据库名称。在INI文件的MYSQLD下，添加binlog-do-db=dbbin。MYSQL官方建议不在这里过滤日志，在复制的时候到SLAVE用replication-do-db来过滤.

3:重新启动MYSQL服务，使前面修改的生效。NET STOP MYSQL   ,NET START MYSQL。

4：登录到MYSQL，创建dbbin数据库，并创建一个表T1。

mysql> create database dbbin;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 4

Current database: *** NONE ***

Query OK, 1 row affected (0.20 sec)

mysql> use dbbin;

Database changed

mysql> create table t1(a int);

Query OK, 0 rows affected (0.01 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.08 sec)

mysql>

刷新日志，现在我们来看看第一个日志记录了些什么

D:\mysql6\bin>mysqlbinlog D:\mysql6\data\log.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#100902 9:49:55 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-community-log created 100902 9:49:55 at startup

ROLLBACK/*!*/;

BINLOG '

wwJ/TA8EAAAAZgAAAGoAAAAAAAQANS4xLjQ1LWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAADDAn9MEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

'/*!*/;

# at 106

#100902 9:50:11 server id 4 end_log_pos 191 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1283392211/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

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 database dbbin

/*!*/;

# at 191

#100902 9:50:22 server id 4 end_log_pos 277 Query thread_id=4 exec_time=0 error_code=0

use dbbin/*!*/;

SET TIMESTAMP=1283392222/*!*/;

create table t1(a int)

/*!*/;

# at 277

#100902 9:50:30 server id 4 end_log_pos 314 Rotate to log.000002 pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

;

上面标示的加粗的部分，记录了创建数据库和表的脚本。非常之强悍。

再插入几条记录看看

mysql> insert into t1 values(1),(2);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

再看看第二个日志，记录了啥

D:\mysql6\bin>mysqlbinlog D:\mysql6\data\log.000002

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#100902 10:12:29 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-community-log created 100902 10:12:29

BINLOG '

DQh/TA8EAAAAZgAAAGoAAAAAAAQANS4xLjQ1LWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

'/*!*/;

# at 106

#100902 10:13:32 server id 4 end_log_pos 175 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1283393612/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

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/*!*/;

BEGIN

/*!*/;

# at 175

#100902 10:13:32 server id 4 end_log_pos 267 Query thread_id=4 exec_time=0 error_code=0

use dbbin/*!*/;

SET TIMESTAMP=1283393612/*!*/;

insert into t1 values(1),(2)

/*!*/;

# at 267

#100902 10:13:32 server id 4 end_log_pos 294 Xid = 10

COMMIT/*!*/;

# at 294

#100902 10:13:35 server id 4 end_log_pos 331 Rotate to log.000004 pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

没错，插入的命令被完美的记录了下来。继续往下看。

这时候有个小厮过来操作数据库，做了一个DROP DATABASE DBBIN的操作。

mysql> drop database dbbin;

Query OK, 1 row affected (0.02 sec)

mysql> use dbbin;

ERROR 1049 (42000): Unknown database 'dbbin'

mysql> flush logs;

Query OK, 0 rows affected (0.02 sec)

mysql>

没文化真可怕。灾难终于发生了。

看看第三个日志文件记录了什么。

D:\mysql6\bin>mysqlbinlog D:\mysql6\data\log.000004

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#100902 10:13:35 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-community-log created 100902 10:13:35

BINLOG '

Twh/TA8EAAAAZgAAAGoAAAAAAAQANS4xLjQ1LWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC

'/*!*/;

# at 106

#100902 10:17:55 server id 4 end_log_pos 189 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1283393875/*!*/;

SET @@session.pseudo_thread_id=4/*!*/;

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/*!*/;

drop database dbbin

/*!*/;

# at 189

#100902 10:18:23 server id 4 end_log_pos 226 Rotate to log.000005 pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

还好哥一早就就记录了日志文件，哥神一样的意识早就知道会有今天这一幕。下面看怎么恢复

D:\mysql6\bin>mysqlbinlog D:\mysql6\data\log.000001 D:\mysql6\data\log.000002 | mysql -uroot -p123

D:\mysql6\bin>

为什么只用到第一个和第二个日志呢？

你傻啊，第三个日志里有DROP掉了，还恢复个灰机？

再看看是否正确还原了。

mysql> use dbbin;

Database changed

mysql> select * from t1;

+------+

| a |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.00 sec)

mysql>

数据都回来了。

再回头看看第二步，为什么官方建议不用binlog-do-db过滤数据库日志，如果你一个不小心没漏掉了一个数据库，那不悲剧了？而且无法挽回。