Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to recover MySQL data through log files" related knowledge, editor through the actual case to show you the operation process, the method of operation is simple and fast, practical, I hope that this "how to recover MySQL data through log files" article can help you solve the problem.

1. Find the latest binlog file

Go to the mysql command line and execute the following command

Mysql > show master status;+-+-+ | Log_name | File_size | +-+-+ | binlog.000001 | 967 | binlog.000002 | 965 | +-+-+

Generally speaking, the latest number is large, and the latest one above is binlog.000002.

2. Find the beginning and end of the data we want to recover in the log file

There are two ways to determine the start and end positions, one is to use time as the start and end, and the other is to use the log's position as the start and end position

2.1. Use time range

View the contents of the log through the mysqlbinlog mysql-bin.000002 command, and then find the point in time to delete:

# at 131708213 server id 210610 11:27:01 server id 1 end_log_pos 131708311 CRC32 0x0fc755e2 Table_map: `BBBMBAAAYAAJe12QcAIsAAAAAAAEADmYmFzZV9oenN5ABpkeF9ZV9oenN5ABpkeF9zeV9c3NdHNfZXh0ZW5kc19jb3B5MQAICAgIDwgBIEAAgABAPQACAZACAZGAJVx8BffBYBBMBAAAYAAAAJe12QcAIsAAAAAAAAAAAAAGUU9enN5ABpkeF9zeV9c3NdHNfZXh0ZW5kc19jb3B5MQAICAgIDwgBIEAAgBAAPACAZACAZGGOJVx8

Then determine the time point of the last backup. If you can't find the time point of the last backup through the log, you can fill in a point in your memory that is smaller than the last backup.

2.2.Use the position range

Use the following command to view the position of the log event

Mysql-uroot-paired password'- e "show binlog events in 'binlog.000002'" | grep-I' DROP TABLE'

The implementation results are as follows:

Binlog.000002 820474948 Query 1 820475111 use `loongwind_ Base`; DROP TABLE IF EXISTS `undo_ log` / * generated by server * / * xid=11790691 * /

That is, the deleted position is 820474948.

Or through the above command, replace the keyword to find the position point of the last backup

3. Restore 3.1Using time to restore mysqlbinlog-- no-defaults-- database=loongwind_base-- start-datetime= "2021-06-07 09:00:00"-- stop-datetime= "2021-06-10 16:37:58" binlog.000005 | mysql-uroot-paired password`-s-N-f-D loongwind_base

Where dxmh_base_hzsy is the database name

3.2 recover mysqlbinlog via position-- start-position=1178-- stop-position=2751-d dxmh-sy binlog.000002 | mysql-uroot-paired password'- s-N-f-D loongwind_baseloongwind_base is the database name

If you can't find the start time or start position, you don't have to write-- start-datetime or-- start-position, so you can use the start of the log file to restore to the end. In order to prevent conflicts with existing data, you need to add-f, that is, force skips the error and continues to execute.

This is the end of the content about "how to recover MySQL data through log files". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.