Shulou(Shulou.com)05/31 Report--

This article focuses on "how MySQL uses binary logs to recover databases". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how MySQL uses binary logs to recover databases".

A brief introduction to binary logs

MySQL has different types of logs, in which binaries record all changes to the database, and if the database loses data due to improper operation or other reasons, it can be recovered through binaries. Log-bin is set in the my.ini file, and binary logging is turned on after restarting MySQL. Each time the database restarts (or executes the flush logs command), a new binary log is generated, as set in the my.ini file

Log-bin=F:\ mysqllog\ logbin

The first startup of the database generates logbin.000001, the second startup generates logbin.000002, the third startup generates logbin.000003,., and so on.

MySQL binary log deletion and recovery http://www.linuxidc.com/Linux/2010-11/29576p2.htm

II. Data recovery instance

1. For illustration, execute the flush logs command to generate a new binary file

two。 Open the authors table in the bookstore database, where there are already three pieces of data, and insert two new pieces of data, namely, Article 4 and Article 5.

3. Suppose that due to operational error, articles 4 and 5 data are deleted.

4. At this point, we will consider using binaries to recover the 4th and 5th data. Open the path of the binaries and find that there are many binary log files.

The 000030 with the highest number is up to date, recording the record insertion and deletion operations described earlier.

5. First take a look at this file and run the following command to convert it to a text file

Open the text file and you can see that the insert and delete operations of the record have been recorded.

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 150204 20:24:10 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.25-log created 150204 20:24:10

# Warning: this binlog is either in use or was not closed properly.

BINLOG'

Ag/SVA8BAAAAZwAAAGsAAAABAAQANS41LjI1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/ *! * /

# at 107

# 150204 20:25:40 server id 1 end_log_pos 180 Query thread_id=8 exec_time=0 error_code=0

SET timestamp 1423052740

SET @ @ session.pseudoclinic thread readership idling 8plankton /

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards mode.1344274432

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C utf8 * /! * /

SET @ @ session. Session. Session setting setting clientmakers 33 minutes. Session. Collationalization connections 33 minutes.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 180

# 150204 20:25:40 server id 1 end_log_pos 369 Query thread_id=8 exec_time=0 error_code=0

Use bookstore

SET timestamp 1423052740

INSERT INTO `bookstore`.`authors` (`bookstore``author_ last`, `author_ first`, `authory`) VALUES (4, 'Li',' Si', 'China')

/ *! * /

# at 369

# 150204 20:25:40 server id 1 end_log_pos 557 Query thread_id=8 exec_time=0 error_code=0

SET timestamp 1423052740

INSERT INTO `bookstore`.`authors` (`bookstore``author_ last`, `author_ first`, `authory`) VALUES (5, 'Wang',' Wu', 'US')

/ *! * /

# at 557

# 150204 20:25:40 server id 1 end_log_pos 584 Xid = 391

Com _ MIT _ blank /

# at 584

# 150204 20:45:31 server id 1 end_log_pos 657 Query thread_id=8 exec_time=0 error_code=0

SET timestamp 1423053931

BEGIN

/ *! * /

# at 657

# 150204 20:45:31 server id 1 end_log_pos 780 Query thread_id=8 exec_time=0 error_code=0

SET timestamp 1423053931

DELETE FROM `bookstore`.`authors`WHERE `bookstore`id` ='4'

/ *! * /

# at 780

# 150204 20:45:31 server id 1 end_log_pos 903 Query thread_id=8 exec_time=0 error_code=0

SET timestamp 1423053931

DELETE FROM `bookstore`.`authors`WHERE `bookstore`id` ='5'

/ *! * /

# at 903

# 150204 20:45:31 server id 1 end_log_pos 930 Xid = 407

Com _ MIT _ blank /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

6. To start recovering data, execute the following command

The reason for having a-- stop-pos=584 parameter is that records have been deleted since 584, so it should be stopped at 584. After executing the command, open the authors table again and you can see that items 4 and 5 of the data have been restored!

At this point, I believe you have a deeper understanding of "how MySQL uses binary logs to recover the database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.