Notes on restoring mysql Data

Mysql data recovery function notes many children's shoes have used the mysqldump and source functions when backing up and restoring the database. However, I feel that the operation is rather inconvenient. I cannot see all operations on the database and the backup is not timely. Maybe a programmer may not be able to find any data after a certain backup time. I checked the data and used mysql logs to solve the problem. Advantages: is to restore to a certain point in time, or an SQL statement, the disadvantage: generate a large log file. the following is how I operate in my own window service: 1. enable mysql Log in my. find [mysqld] In the ini file and add a line of log-bin under it.

Ruby [mysqld]  # The TCP/IP Port the MySQL Server will listen on  port=3306  log-bin  

 

The default log file name is the host name.

Python [mysqld] # The TCP/IP Port the MySQL Server will listen on port = 3306 # mysql-bin is The custom name log-bin = mysql-bin

 

2. Define the log file path

Ruby # Path to the Path where the database root stores logs datadir = "D:/web/mysql/Data /"

 

3. Restart mysql. Two files will be generated in the above path. 4. Run the command to enter mysql.

mysql> show binlog events ;

 

You will see an interface like this: The above pos is the end position of end_log_pos:

1. view the current log enabling status mysql> show variables like 'Log % '; 2. view the current log status mysql> show master status; 3. display the current binary mysql> show binary logs;

 

5. There are two types of recovery:

1. is to save the log file as an SQL file, and then use the source command to locate and save usage: In the mysql installation bin directory: mysqlbinlog -- start-position = 4 -- stop-position = 239 d: \ web \ mysql \ data \ mysqlbin-log.000001> test1.txt save time usage: mysqlbinlog -- start-datetime = "13:00:00" -- stop-datetime = "14:00:00" d: \ web \ mysql \ data \ mysqlbin-log.000001> test1.txt and then source test1.txt2. restore the log directly to a specific point. Time Reduction Method: Under the mysql installation bin directory, mysqlbinlog -- start-datetime = "2013-03-16 13:00:00" -- stop-datetime = "2013-03-16 14:00:00" d: \ web \ mysql \ data \ mysqlbin-log.000001 | mysql-uroot-p positioning Reduction Method: Under the mysql installation bin directory D: \ web \ mysql \ bin> mysqlbinlog -- start-position = 3696 -- stop-position = 4241 d: \ web \ ysql \ data \ mysql-bin.000001 | mysql-hlocalhost-uroot-p

 

PS: if an error occurs during the process of creating and operating a table, it is also wrong during restoration. You must partially locate the problem to restore it. (The following is a small error during my operation. Remove auto_increment from Duplicate entry '1' for key 1, or do not assign values to fields with auto_increment ), if you are interested, you can simply create a table, add data, and then delete the data. Use the above steps to see Ha ^ V ^