MySQL Database binlong recovery

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 Recovery
* 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, usingShow variables like '%bin% 'Check to see if the Bin-log is turned on
650) this.width=650; "Src=" http://dl.iteye.com/upload/attachment/0075/0762/ Fd73b02a-555a-3365-a95b-f68e5192a073.png "style=" border:none; "/>

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. Using in the clientshow Binlog events in ' mysql_bin.000001 'Statements to see, in order to be aesthetically pleasing, you can add \g at the end to make the result horizontal, 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 existsC:\ProgramData\MySQL\MySQL Server 5.1\dataFolder below, if there is no such folder, then we can through the configuration file datadir= "C:/programdata/mysql/mysql Server 5.1/data/" location, if not, then I would say "the search function of the various systems are 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
#140215 16:35:56 Server ID 1 end_log_pos 106 start:binlog v 4, Server v 5.1.51-community-log created 140215 16:35:56 a T startup
rollback/*!*/;
BINLOG '
7mp7ua8baaaazgaaagoaaaaaaaqans4xljuxlwnvbw11bml0es1sb2caaaaaaaaaaaaaaaaaaaaa
Aaaaaaaaaaaaaaaaaadsyntqezgnaagaegaebaqeegaauwaegggaaaaicagc
‘/*!*/;
# at 106
#140215 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
#140215 16:36:51 Server ID 1 end_log_pos 202 intvar
SET insert_id=3/*!*/;
# at 202
#140215 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
#140215 16:36:51 Server ID 1 end_log_pos 336 Xid = 28
commit/*!*/;
# at 336
#140215 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.
650) this.width=650; "class=" Magplus "title=" click to view original size picture "src=" http://dl.iteye.com/upload/attachment/0075/0764/ 025d529d-a557-351c-a674-32b30f9d8550.png "width=" "height=" 98 "style=" Border:none; "/>

3. Recovering data using Bin_log (date and position)

1. The longest use is to recover data from the specified data end, can be directly restored to the database:
Mysqlbinlog--start-date= "2014-02-18 16:30:00"--stop-date= "2014-02-18 17:00:00" mysql_bin.000001 |mysql-uroot-p123456
can also be exported as a SQL file, and then import into the database:
Mysqlbinlog--start-date= "2014-02-18 16:30:00"--stop-date= "2014-02-18 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"//Specify to 100 position end

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

Show master status;
650) this.width=650; "Src=" http://dl.iteye.com/upload/attachment/0075/0766/ E4ab9924-8db3-32ad-b719-a7494d61a8e4.png "style=" border:none; "/>
2. Enable the new log file, which is typically performed after the database has been backed up.

Flush logs;
650) this.width=650; "Src=" http://dl.iteye.com/upload/attachment/0075/0768/ 1508c1eb-4214-36e8-8581-01b5cde242f6.png "style=" border:none; "/>
3. Empty the existing Bin-log

650) this.width=650; "Src=" http://dl.iteye.com/upload/attachment/0075/0770/ 7e5d7d7f-1bc2-30e9-8d9a-d39a46b687c3.png "alt=" 7e5d7d7f-1bc2-30e9-8d9a-d39a46b687c3.png "/>

This article is from the "Technical Achievement Dream" blog, please be sure to keep this source http://2367685.blog.51cto.com/2357685/1639417

MySQL Database binlong recovery