使用 MYSQLBINLOG 来恢复数据
标签：
原创作品，允许转载，转载时请务必以超链接形式标明文章 、作者信息和本声明。否则将追究法律责任。
BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。不过只是她是二进制存储，普通的是十进制存储罢了。
1、配置文件里要写的东西：[mysqld]log-bin=mysql-bin(名字可以改成自己的，如果不改名字的话，默认是以主机名字命名)重新启动MSYQL服务。二进制文件里面的东西显示的就是执行所有语句的详细记录，当然一些语句不被记录在内，要了解详细的，见手册页。2、查看自己的BINLOG的名字是什么。show binlog events;query result(1 records)
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| yueliangdao_binglog.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.22-rc-community-log, Binlog ver: 4 |
3、我做了几次操作后，她就记录了下来。又一次 show binlog events 的结果。
query result(4 records)
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
| yueliangdao_binglog.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.22-rc-community-log, Binlog ver: 4 |
| yueliangdao_binglog.000001 | 106 | Intvar | 1 | 134 | INSERT_ID=1 |
| yueliangdao_binglog.000001 | 134 | Query | 1 | 254 | use `test`; create table a1(id int not null auto_increment primary key, str varchar(1000)) engine=myisam |
| yueliangdao_binglog.000001 | 254 | Query | 1 | 330 | use `test`; insert into a1(str) values ('I love you'),('You love me') |
| yueliangdao_binglog.000001 | 330 | Query | 1 | 485 | use `test`; drop table a1 |
4、用mysqlbinlog 工具来显示记录的二进制结果，然后导入到文本文件，为了以后的恢复。详细过程如下：D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=4 --stop-position=106 yueliangdao_binglog.000001 > c:\\test1.txttest1.txt的文件内容：/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#7122 16:9:18 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.22-rc-community-log created 7122 16:9:18 at startup# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.ROLLBACK/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;第二行的记录：D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=106 --stop-position=134 yueliangdao_binglog.000001 > c:\\test1.txttest1.txt内容如下：/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 106#7122 16:22:36 server id 1 end_log_pos 134 IntvarSET INSERT_ID=1/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;第三行记录：D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=254 yueliangdao_binglog.000001 > c:\\test1.txt内容：/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 134#7122 16:55:31 server id 1 end_log_pos 254 Query thread_id=1 exec_time=0 error_code=0use test/*!*/;SET TIMESTAMP=1196585731/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;SET @@session.sql_mode=1344274432/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;create table a1(id int not null auto_increment primary key,str varchar(1000)) engine=myisam/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;第四行的记录：D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=254 --stop-position=330 yueliangdao_binglog.000001 > c:\\test1.txt/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 254#7122 16:22:36 server id 1 end_log_pos 330 Query thread_id=1 exec_time=0 error_code=0use test/*!*/;SET TIMESTAMP=1196583756/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;SET @@session.sql_mode=1344274432/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;use `test`; insert into a1(str) values ('I love you'),('You love me')/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;5、查看这些东西是为了恢复数据，而不是为了好玩。所以我们最中还是为了要导入结果到MYSQL中。D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yueliangdao_binglog.000001 | mysql -uroot -p或者D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yueliangdao_binglog.000001 >test1.txt进入MYSQL导入mysql> source c:\\test1.txtQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Database changedQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Charset changedQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.03 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)6、查看数据：mysql> show tables;+----------------+| Tables_in_test |+----------------+| a1 |+----------------+1 row in set (0.01 sec)mysql> select * from a1;+----+-------------+| id | str |+----+-------------+| 1 | I love you || 2 | You love me |+----+-------------+2 rows in set (0.00 sec)
将一个mysqlbinlog文件导为sql文件
cd cd /usr/local/mysql./mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/001.sql
将mysql-bin.000001日志文件导成001.sql
可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间
./mysqlbinlog --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 > /opt/004.sql
将mysql-bin.000002文件中截止到2009-04-10 17:41:28的日志导成004.sql
./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 /usr/local/mysql/data/mysql-bin.0000023> /opt/004.sql
----如果有多个binlog文件，中间用空格隔开，打上完全路径
./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456
或者 source /opt/004.sql
将mysql-bin.000002日志文件中从2009-04-10 17:30:05到2008-04-10 17:41:28截止的sql语句导入到mysql中