MySQL database recovery

Use the mysqlbinlog command to restore the database.

BINLOG backs up data by recording binary files, and then restores data from the binary file to a certain time period or operation point.

1: Enable BINLOG Logging

Modify the MySQL configuration file mysql. ini and add it under the [mysqld] node.

XML Code

1. # Log-bin
2. Log-bin = E:/log/logbin. Log

# log-binlog-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

XML Code

1. C :\>Net stop MySQL;
2. C :\>Net start MySQL;

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

1. Mysql> show variables like 'Log _ % ';

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

1. C: \ mysql \ bin \> mysqlbinlog E:/log/logbin.000001

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

1. C: \ mysql \ bin \>Mysqlbinlog E:/log/logbin.000001>E:/log/log.txt

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

1. C: \ mysql \ bin \> mysqlbinlog E:/log/logbin.000001> E:/log. SQL
2. C: \ mysql \ bin \> mysqlbinlog E:/log/logbin.000002> E:/log. SQL

c:\mysql\bin\> mysqlbinlog e:/log/logbin.000001 >  e:/log/log.sqlc:\mysql\bin\> mysqlbinlog e:/log/logbin.000002 >> e:/log/log.sql 

2.2 export at the specified position:

SQL code

1. C: \ mysql \ bin \> mysqlbinlog -- start-position = 185 -- stop-position = 338 E:/log/logbin.000001> E:/log/log3.txt

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

1. C: \ mysql \ bin \>Mysqlbinlog -- start-datetime = "11:25:56" -- stop-datetime = "13:23:50" E:/log/logbin.000001>E:/log/log_by_date22.txt

c:\mysql\bin\>mysqlbinlog --start-datetime="2010-01-07 11:25:56" --stop-datetime="2010-01-07 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

1. /*! 40019Set@ Session. max_insert_delayed_threads = 0 */;
2. /*! 50003Set@ Old_completion_type = @ completion_type, completion_type = 0 */;
3. Delimiter /*! */;
4. #At4
5. #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:50AtStartup
6. # Warning: This BINLOGIsEither in use or was not closed properly.
7. Rollback/*! */;
8. Binlog'
9. Bytes
10. Aaaaaaaaaaaaaaaaaabmoyznezgnaagaegaebaqeegaauwaegggaaaaicagc
11. '/*! */;
12. #At106
13. #110107 13:26:58 server Id 1 end_log_pos 185 query thread_id = 44 exec_time = 1 error_code = 0
14. Set Timestamp= 1294378018 /*! */;
15. Set@ Session. pseudo do_thread_id = 44 /*! */;
16. Set@ Session. foreign_key_checks = 1, @ session. SQL _auto_is_null = 1, @ session. unique_checks = 1, @ session. autocommit = 1 /*! */;
17. Set@ Session. SQL _mode = 1344274432 /*! */;
18. Set@ Session. auto_increment_increment = 1, @ session. auto_increment_offset = 1 /*! */;
19. /*! \ C utf8 *//*! */;
20. Set@ Session. character_set_client = 33, @ session. collation_connection = 33, @ session. collation_server = 33 /*! */;
21. Set@ Session. lc_time_names = 0 /*! */;
22. Set@ Session. collation_database =Default/*! */;
23. Begin
24. /*! */;
25. #At185
26. #110107 13:26:58 server Id 1 end_log_pos 338 query thread_id = 44 exec_time = 1 error_code = 0
27. Use NCL-interactive /*! */;
28. Set Timestamp= 1294378018 /*! */;
29. Update'T_ system_id'Set'Id _ value' = '123'Where('Table _ name' = 't_ working_day ')
30. /*! */;
31. #At338
32. #110107 13:26:58 server Id 1 end_log_pos 365 Xid = 8016
33. Commit/*! */;
34. Delimiter;
35. Delimiter /*! */;
36. Delimiter;
37. #End OfLog File
38. Rollback/* AddedByMysqlbinlog */;
39. /*! 50003SetCompletion_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 'ZqMmTQ8BAAAAZgAAAGoAAAABAAQANS4xLjUzLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABmoyZNEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC'/*!*/;# at 106#110107 13:26:58 server id 1  end_log_pos 185 Querythread_id=44exec_time=1error_code=0SET TIMESTAMP=1294378018/*!*/;SET @@session.pseudo_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 Querythread_id=44exec_time=1error_code=0use ncl-interactive/*!*/;SET TIMESTAMP=1294378018/*!*/;UPDATE `t_system_id` SET `id_value`='3000' WHERE (`table_name`='t_working_day')/*!*/;# at 338#110107 13:26:58 server id 1  end_log_pos 365 Xid = 8016COMMIT/*!*/;DELIMITER ;DELIMITER /*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

3.1 recovery:

SQL code

1. C: \ mysql \ bin \> mysqlbinlog E:/log/logbin.000001 | mysql-u root-P

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

3.2 restore at the specified position:

SQL code

1. C: \ mysql \ bin \> mysqlbinlog -- start-position = 185 -- stop-position = 338 E:/log/logbin.000001 | mysql-u root-P

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

1. C: \ mysql \ bin \>Mysqlbinlog -- start-datetime = "11:25:56" -- stop-datetime = "13:23:50" E:/log/logbin.000001 | mysql-u root-P

c:\mysql\bin\>mysqlbinlog --start-datetime="2010-01-07 11:25:56" --stop-datetime="2010-01-07 13:23:50" e:/log/logbin.000001 | mysql -u root -p 

3.4 restore from exported script files

SQL code

1. C: \ mysql \ bin \> mysql-e "Source E:/log. SQL"

c:\mysql\bin\>mysql -e "source e:/log/log.sql"

4. Other Common Operations

4.1 view all log files

SQL code

1. Mysql> show Master logs;

mysql>show master logs;

4.2 BINLOG file currently used

SQL code

1. Mysql> show BINLOG events \ G;

mysql>show binlog events \g;

4.3 generate a new BINLOG Log File

SQL code

1. Mysql> flush logs;

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

1. Mysql> flush logs;
2. Mysql> Reset master;

mysql > flush logs;mysql > reset master;

4.5 quickly back up data to SQL files

SQL code

1. C: \ mysql \ bin> mysqldump-u root-p -- Opt -- quick interactive> E:/log/mysqldump. SQL

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

1. C: \ mysql \ bin \> mysql-e "Source E:/log/mysqldump. SQL"