How to restore mysql logs

Using logs to recover data is generally caused by your failure to effectively and reasonably back up the database. However, if you want to restore logs in advance, you must enable mysql logs, otherwise, logs cannot be backed up.

Using logs to recover data is generally caused by your failure to effectively and reasonably back up the database. However, if you want to restore logs in advance, you must enable mysql logs, otherwise, logs cannot be backed up.

Advantages and disadvantages of mysql Log backup

Advantage: You want to restore to a certain time point or an SQL statement

Disadvantage: a huge log file is generated.

Mysql Log restoration method in window

1. Enable mysql Log
Find [mysqld] In the my. ini file and add a line of log-bin under it.

The Code is as follows:
[Mysqld] # The TCP/IP Port the MySQL Server will listen on Port = 3306 Log-bin

The default log file name is the host name.

The Code is as follows:
[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

The Code is as follows:
# Path to the Path where the database root stores logs Datadir = "D:/web/mysql/Data /"

3. Restart mysql

Two files are generated in the above path.

4. Run the command to enter mysql

The Code is as follows:
Mysql> show binlog events;

The above pos is the end position of end_log_pos.
Related commands:
1. view the current log enabling status

The Code is as follows:
Mysql> show variables like 'Log % ';

2. view the current log

The Code is as follows:
Mysql> show master status;

3. display the current binary

The Code is as follows:
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 and save usage: Under the bin directory:

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

Save time usage: Under the bin installation directory of mysql

The Code is as follows:
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:
Source test1.txt

2. directly restore the log to a certain point
Time-Based Reduction: Under the bin installation directory of mysql

The Code is as follows:
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

Positioning reduction method:Under the bin installation directory of mysql

The Code is as follows:
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 simply create a table, add data, and delete the data.