Notes on restoring Mysql Data

Many kids 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 database operations 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
Find [mysqld] In the my. ini file and add a line of log-bin under it.

Copy Ruby code to clipboard

1. [Mysqld]
2. # The TCP/IP Port the MySQL server will listen on
3. Port = 3306
4. Log-bin

The default log file name is the host name.

Copy the content from Python code to the clipboard

1. [Mysqld]
2. # The TCP/IP Port the MySQL server will listen on
3. Port = 3306
4. # Mysql-bin is the custom name
5. Log-bin = mysql-bin

2. Define the log file path

Copy Ruby code to clipboard

1. # Path to the path where the Database Root stores logs
2. Datadir = "D:/web/MySQL/data /"

3. Restart MySQL
Two files are 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.
Related commands:
1. view the current log enabling status
Mysql> show variables like 'Log % ';
2. view the current log
Mysql> show Master status;
3. display the current binary
Mysql> show binary logs;
5. There are two types of recovery:
1. Save the log file as an SQL file and use the source command.
Locate save usage: mysqlbinlog -- start-position = 4 -- stop-position = 239 D: \ WEB \ mysql \ data \ mysqlbin-log.000001> test1.txt under the MySQL installation bin directory
Save time usage: mysqlbinlog -- start-datetime = "13:00:00" -- stop-datetime = "201
3-03-16 14:00:00 "D: \ WEB \ mysql \ data \ mysqlbin-log.000001> test1.txt
Source test1.txt
2. directly restore the log to a certain point
Time-Based Reduction Method: mysqlbinlog -- start-datetime = "13:00:00" -- stop-datetime = "201
3-03-16 14:00:00 "D: \ WEB \ mysql \ data \ mysqlbin-log.000001 | mysql-uroot-P
Location reduction: 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. (Below is a small error during my operation, duplicate entry '1' for key 1
Remove auto_increment, or do not assign values to fields with auto_increment.) If you are interested, you can create a table, add data, and delete data, use the above steps to check out ^ V ^,