Recover data using MySQL's Binlog

MySQL binary log is often said to be the Bin-log, is the MySQL implementation of the changes produced by the binary logging file, its main role is two:
* Data reply
* Master-slave database. For the slave side to perform additions and deletions to maintain synchronization with master.

1. Turn on binary log function

Need to modify the MySQL configuration file, the experimental environment is Win7, the configuration file for the MySQL installation directory \mysql Server 5.1 under My.ini, add a sentence Log_bin = Mysql_bin can
eg
[Mysqld]
......
Log_bin = Mysql_bin
......
Log_bin is the file name of the generated bin-log, the suffix is the 6-digit encoding, starting from 000001, according to the above configuration, the resulting file is:
mysql_bin.000001
mysql_bin.000002
......

Configure the server to restart MySQL after saving, with show variables like '%bin% ' to see if Bin-log is turned on,


2. View the resulting binary log

Bin-log because it is a binary file, can not be opened to view through the editor, such as Notepad, MySQL provides two ways to view the way, before the introduction, we first to make additions and deletions to the database operation, or log inside the data is a bit empty.
Create TABLE bin (id int () PRIMARY key auto_increment,name varchar (255));(I have built the watch before testing)
Insert into Bin (name) values (' orange ');

1. Use the show Binlog events in ' mysql_bin.000001 ' statement in the client to view, in order to be aesthetically pleasing, you can add \g at the end to make the result horizontal vertical, at which end no need to add; Statement terminator.
eg
Mysql> show Binlog events in ' mysql_bin.000001 ' \g
............... Omitted...............
3. Row ***************************
log_name:mysql_bin.000001
pos:174
Event_type:intvar
Server_id:1
end_log_pos:202
info:insert_id=2
4. Row ***************************
log_name:mysql_bin.000001
pos:202
Event_type:query
Server_id:1
end_log_pos:304
Info:use ' test '; Insert into Bin (name) values (' Orange ')
5. Row ***************************
............... Omitted...............
Log_name: This log exists in the file, from the above can be seen that the 2 logs are present in the mysql_bin.000001 file.
Pos:log start position in Bin-log
Type information for Event_type:log
SERVER_ID: You can view server_id in the configuration, indicating that log is the server that generated
End_log_pos:log the end position in the Bin-log
Info:log Some of the notes information, you can visually see what is doing

2. With MySQL's own tool Mysqlbinlog, this is what we need to know bin-log where the hard disk is located, win7 default exists C:\ProgramData\MySQL\MySQL Server 5.1\data folder below, If there is no such folder, then we can go through the configuration file datadir= "C:/programdata/mysql/mysql Server 5.1/data/" location, if not, then I would say "the search function of each system is doing a good job!" ”。 This way of viewing is not that beautiful, as follows

C:\ProgramData\MySQL\MySQL Server 5.1\data>mysqlbinlog mysql_bin.000001
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected] @COMPLETION_TYPE, completion_type=0*/;
DELIMITER/*!*/;
# at 4
#121015 16:35:56 Server ID 1 end_log_pos 106 start:binlog v 4, Server v 5.1.51-community-log created 121015 16:35:56 a T startup
rollback/*!*/;
BINLOG '
7mp7ua8baaaazgaaagoaaaaaaaqans4xljuxlwnvbw11bml0es1sb2caaaaaaaaaaaaaaaaaaaaa
Aaaaaaaaaaaaaaaaaadsyntqezgnaagaegaebaqeegaauwaegggaaaaicagc
‘/*!*/;
# at 106
#121015 16:36:51 Server ID 1 end_log_pos 174 Query thread_id=2 exec_time=0 error_code=0
SET timestamp=1350290211/*!*/;
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=1344274432/*!*/;
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 174
#121015 16:36:51 Server ID 1 end_log_pos 202 intvar
SET insert_id=3/*!*/;
# at 202
#121015 16:36:51 Server ID 1 end_log_pos 309 Query thread_id=2 exec_time=0 error_code=0
Use test/*!*/;
SET timestamp=1350290211/*!*/;
Insert into Bin (name) values (' Xishizhaohua ')
/*!*/;
# at 309
#121015 16:36:51 Server ID 1 end_log_pos 336 Xid = 28
commit/*!*/;
# at 336
#121015 16:37:25 Server ID 1 end_log_pos 379 Rotate to mysql_bin.000002 pos:4
DELIMITER;
# End of log file
ROLLBACK/* Added by Mysqlbinlog */;
/*!50003 SET [email protected]_completion_type*/;

Although typography is a bit messy, we can get more information such as timestamps, self-increment offsets, and whether transactions are automatically committed. As part of the extract from.


3. Recovering data with Bin_log

1. The longest use is to reply to the data end of the specified data, you can directly restore to the database:
Mysqlbinlog--start-date= "2012-10-15 16:30:00"--stop-date= "2012-10-15 17:00:00" mysql_bin.000001 |mysql-uroot- p123456
It can also be exported to a SQL file and then imported into the database:
Mysqlbinlog--start-date= "2012-10-15 16:30:00"--stop-date= "2012-10-15 17:00:00" mysql_bin.000001 >d:\1.sql
SOURCE D:\1.sql
2. Specify the start \ End position, from the above view generated by the binary log we can know the beginning of a log to the end of the location, we can in the process of recovery to specify the reply from a position to B location log. The following two parameters are required to specify:
--start-positon= "50"//Specify start from 50 position
--stop-postion= "100"//designation to 100 position end

Finally, several bin_log operations are introduced:
1. Production look at the last bin log file is that, now location

2. Enable the new log file, typically after the database has been backed up and executed

3. Empty the existing Bin-log

Recover data using MySQL's Binlog