测试流程:
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 */; /*!50003 SET ;
|
上面标示的加粗的部分,记录了创建数据库和表的脚本。非常之强悍。
再插入几条记录看看
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过滤数据库日志,如果你一个不小心没漏掉了一个数据库,那不悲剧了?而且无法挽回。
阅读(1190) | 评论(2) | 转发(1) |