MySQL database recovery (using mysqlbinlog command) _ MySQL

MySQL database recovery (using mysqlbinlog command) bitsCN. com1: enable binlog logging
Modify the mysql configuration file mysql. ini and add it under the [mysqld] node.

# 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

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

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

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

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

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

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

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

/*! 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

C:/mysql/bin/> mysqlbinlog e:/log/logbin.000001 | mysql-u root-p

3.2 restore at the specified position:
SQL code

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

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

C:/mysql/bin/> mysql-e "source e:/log. SQL"

4. other common operations
4.1 View all log files

SQL code

Mysql> show master logs;

4.2 binlog file currently used
SQL code

Mysql> show binlog events/g;

4.3 generate a new binlog log file
SQL code

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

Mysql> flush logs;
Mysql> reset master;

4.5 quickly back up data to SQL files

SQL code

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

C:/mysql/bin/> mysql-e "source e:/log/mysqldump. SQL"
BitsCN.com