Using Mysqlbinlog to recover data2009-04-05 12:47:05Tags: mysql mysqlbinlog restore Database Data original works, allow reprint, when reproduced please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://kerry.blog.51cto.com/172631/146259
Binlog is a procedure for logging SQL statements, just like a normal log. But she's just a binary store, and the average is a decimal store.
1. What to write in the configuration file:
[Mysqld]
log-bin=Mysql-bin (name can be changed to their own, if not change the name, the default is the host name)
Restart the MSYQL service.
The contents of the binary file show the detailed record of executing all the statements, of course, some statements are not recorded, to understand the details, see the manual page.
2. See what the name of your binlog is.
Show Binlog events;
Query result (1 records)
| log_name |
pos |
event_type |
server_id< /td> |
end_log_pos |
info |
| yueliangdao_binglog.000001 |
4 |
format_desc |
1 |
|
server ver:5.1.22-rc-community-log, Binlog ver:4 |
3, I have done several operations, she recorded down.
The result of show Binlog events again.
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 ()) 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, use the Mysqlbinlog tool to display the binary results of the record, and then import into a text file, in order to recover later.
The detailed procedure is as follows:
D:\lamp\mysql5\data>mysqlbinlog--start-position=4--stop-position=106 Yueliangd
ao_binglog.000001 > C:\\test1.txt
Test1.txt's file content:
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @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 is not closed properly. Most probably mysqld crashed writing it.
rollback/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
Record for the second row:
D:\lamp\mysql5\data>mysqlbinlog--start-position=106--stop-position=134 Yuelian
gdao_binglog.000001 > C:\\test1.txt
Test1.txt content is as follows:
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 106
#7122 16:22:36 Server ID 1 end_log_pos 134 intvar
SET insert_id=1/*!*/;
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
The third line records:
D:\lamp\mysql5\data>mysqlbinlog--start-position=134--stop-position=254 Yuelian
gdao_binglog.000001 > C:\\test1.txt
Content:
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @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=0
Use 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 (engine=myisam/*!*/);
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
Record of line four:
D:\lamp\mysql5\data>mysqlbinlog--start-position=254--stop-position=330 Yuelian
gdao_binglog.000001 > C:\\test1.txt
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 254
#7122 16:22:36 Server ID 1 end_log_pos Query thread_id=1 exec_time=0 error_code=0
Use 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 file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;
5. Look at these things to recover data, not for fun. So we're mostly trying to import results into MySQL.
D:\lamp\mysql5\data>mysqlbinlog--start-position=134--stop-position=330 Yuelian
gdao_binglog.000001 | Mysql-uroot-p
Or
D:\lamp\mysql5\data>mysqlbinlog--start-position=134--stop-position=330 Yuelian
gdao_binglog.000001 >test1.txt
Enter MySQL Import
Mysql> Source C:\\test1.txt
Query OK, 0 rows Affected (0.00 sec)
Query OK, 0 rows Affected (0.00 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)
Charset changed
Query 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. View data:
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) To convert a mysqlbinlog file to a SQL file CD Cd/usr/local/mysql
./mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 >/opt/001.sql mysql-bin.000001 log file to 001.sql
You can specify the start and end time of the DateTime format in the Mysqlbinlog statement through the--start-date and--stop-date options./mysqlbinlog--stop-date= "2009-04-10 17:41:28"/usr/local/mysql/data/mysql-bin.000002 >/opt/004.sqlThe log in the mysql-bin.000002 file as of 2009-04-10 17:41:28 is directed to 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----If there is more than one Binlog file, the middle is separated by a space, and the full path is played ./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-p123456or Source/opt/004.sql . Import SQL statements from 2009-04-10 17:30:05 to 2008-04-10 17:41:28 in the mysql-bin.000002 log file into MySQL
[Go] Use Mysqlbinlog to recover data
