MySQL database recovery (using the mysqlbinlog command)

1: Enable binlog Logging
Modify the mysql configuration file mysql. ini and add it under the [mysqld] node.
Copy codeThe Code is as follows:
# Log-bin
Log-bin = E:/log/logbin. log

Do not include Chinese characters or spaces in the path. Restart the mysql service. Use command lines to stop and start mysql services
Copy codeThe Code is as follows:
C: \> net stop mysql;
C: \> net start mysql;

Go to the command line to enter mysql and check whether the binary log has been started
SQL code
Copy codeThe Code is as follows:
Mysql> show variables like 'Log _ % ';

After the log is successfully enabled, the logbin. index and logbin.000001 files are created in the E:/log/directory. Logbin.000001 is the backup file of the database. You can use this file to restore the database.

2: view the backup binary file
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog e:/log/logbin.000001

More operations will be recorded in the future, and the command line method will basically not be used. You can view the log Content by exporting the log file.
2.1 Export
Xml Code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog e:/log/logbin.000001> e:/log/log.txt

">": Import to file; ">": append to file
If multiple log files exist
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog e:/log/logbin.000001> e:/log. SQL
C: \ mysql \ bin \> mysqlbinlog e:/log/logbin.000002> e:/log. sq

2.2 export at the specified position:
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog -- start-position = 185 -- stop-position = 338 e:/log/logbin.000001> e:/log/log3.txt

2.3 export at the specified time:
Xml Code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog -- start-datetime = "11:25:56" -- stop-datetime = "13:23:50" e:/log/logbin.000001> e:/log/log_by_date22.txt

3. Restore the database from backup
After an update operation is performed, the log content is as follows:
SQL code
Copy codeThe Code is as follows:
/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 4
#110107 13:23:50 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.53-community-log created 110107 13:23:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK /*! */;
Binlog'
Bytes
AAAAAAAAAAAAAAAAAABmoyZNEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*! */;
# At 106
#110107 13:26:58 server id 1 end_log_pos 185 Query thread_id = 44 exec_time = 1 error_code = 0
Set timestamp = 1294378018 /*! */;
SET @ session. pseudo do_thread_id = 44 /*! */;
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 185
#110107 13:26:58 server id 1 end_log_pos 338 Query thread_id = 44 exec_time = 1 error_code = 0
Use ncl-interactive /*! */;
Set timestamp = 1294378018 /*! */;
UPDATE't _ system_id 'set' id _ value' = '000000' WHERE ('table _ name' = 't_ working_day ')
/*! */;
# At 338
#110107 13:26:58 server id 1 end_log_pos 365 Xid = 8016
COMMIT /*! */;
DELIMITER;
DELIMITER /*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;
/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 4
#110107 13:23:50 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.53-community-log created 110107 13:23:50 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK /*! */;
Binlog'
Bytes
AAAAAAAAAAAAAAAAAABmoyZNEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*! */;
# At 106
#110107 13:26:58 server id 1 end_log_pos 185 Query thread_id = 44 exec_time = 1 error_code = 0
Set timestamp = 1294378018 /*! */;
SET @ session. pseudo do_thread_id = 44 /*! */;
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 185
#110107 13:26:58 server id 1 end_log_pos 338 Query thread_id = 44 exec_time = 1 error_code = 0
Use ncl-interactive /*! */;
Set timestamp = 1294378018 /*! */;
UPDATE't _ system_id 'set' id _ value' = '000000' WHERE ('table _ name' = 't_ working_day ')
/*! */;
# At 338
#110107 13:26:58 server id 1 end_log_pos 365 Xid = 8016
COMMIT /*! */;
DELIMITER;
DELIMITER /*! */;
DELIMITER;
# End of log file
ROLLBACK/* added by mysqlbinlog */;
/*! 50003 SET COMPLETION_TYPE = @ OLD_COMPLETION_TYPE */;

3.1 recovery:
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog e:/log/logbin.000001 | mysql-u root-p

3.2 restore at the specified position:
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog -- start-position = 185 -- stop-position = 338 e:/log/logbin.000001 | mysql-u root-p

3.3 restore at the specified time:
Xml Code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysqlbinlog -- start-datetime = "11:25:56" -- stop-datetime = "13:23:50" e:/log/logbin.000001 | mysql-u root-p

3.4 restore from exported script files
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysql-e "source e:/log. SQL"

4. Other Common Operations
4.1 view all log files

SQL code
Copy codeThe Code is as follows:
Mysql> show master logs;

4.2 binlog file currently used
SQL code
Copy codeThe Code is as follows:
Mysql> show binlog events \ g;

4.3 generate a new binlog Log File
SQL code
Copy codeThe Code is as follows:
Mysql> flush logs;

4.4 delete all binary logs and record them from the beginning (note: the reset master command will delete all binary logs)
SQL code
Copy codeThe Code is as follows:
Mysql> flush logs;
Mysql> reset master;

4.5 quickly back up data to SQL files

SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin> mysqldump-u root-p -- opt -- quick interactive> e:/log/mysqldump. SQL

For ease of viewing, write the command restored from the script once
SQL code
Copy codeThe Code is as follows:
C: \ mysql \ bin \> mysql-e "source e:/log/mysqldump. SQL"