Introduction to MySQL Log recovery data method

Advantages and disadvantages of MySQL log backups

Benefits: is to revert to a point in time, or an operation SQL statement

Disadvantage: A large log file is generated

MySQL log recovery method in Windows

1. Open MySQL Log
Find [mysqld] in the My.ini file and add a row below it Log-bin

The code is as follows	Copy Code
[Mysqld] # The TCP/IP Port the MySQL Server would listen on port=3306 Log-bin

The default log file name is the host name, if you want to change to your own definition of the name

The code is as follows	Copy Code
[Mysqld] # The TCP/IP Port the MySQL Server would listen on port=3306 #mysql-bin for custom names Log-bin = Mysql-bin

2. Define log file path

The code is as follows	Copy Code
#Path to the database root store the path of the log Datadir= "d:/web/mysql/data/"

3. Restart MySQL

Two files will be generated under the above path

4. Enter MySQL with command

The code is as follows	Copy Code
Mysql> Show Binlog events;

The POS above is the end position of the start position End_log_pos
Related commands:
1. View current Log Open

The code is as follows	Copy Code
Mysql>show variables like ' log% ';

2. View current log status

The code is as follows	Copy Code
Mysql>show Master status;

3. Show current binary

The code is as follows	Copy Code
Mysql>show binary logs;

5. There are two types of recovery:
1. is to save the log file into a SQL file, and then use the source command
Locate save usage: Under MySQL Install Bin:

The code is as follows	Copy Code
Mysqlbinlog--start-position=4--stop-position=239 d:\web\mysql\data\mysqlbin-log.000001 >test1.txt

Fixed-time save usage: under MySQL installation bin

The code is as follows	Copy Code
Mysqlbinlog--start-datetime= "2013-03-16 13:00:00"--stop-datetime= "201 3-03-16 14:00:00 "d:\web\mysql\data\mysqlbin-log.000001 >test1.txt

Again

The code is as follows	Copy Code
SOURCE Test1.txt

2. Restore the log directly to a certain point
Fixed-time reduction method: Under the MySQL installation bin

The code is as follows	Copy Code
Mysqlbinlog--start-datetime= "2013-03-16 13:00:00"--stop-datetime= "201 3-03-16 14:00:00 "d:\web\mysql\data\mysqlbin-log.000001 | Mysql-uroot-p

Location Restore method: under the MySQL installation bin

The code is as follows	Copy Code
D:\web\mysql\bin>mysqlbinlog--start-position=3696--stop-position=4241 d:\web\ ysql\data\mysql-bin.000001 | Mysql-hlocalhost-uroot-p

PS: If there are errors in the process of creating the table and the action table, there will be errors in the restore, which is partially positioned to restore. (Here is a tabloid error in my operation,Duplicate entry ' 1 ' for key 1
Remove auto_increment, or do not give a auto_increment field assignment can be resolved), interested friends can simply create a table, add data, and then delete data, use the above steps to see ha