How to restore MySQL data through log files

1. Find the latest binlog file

Enter the mysql command line and execute the following command

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

Generally the latest number is larger, the latest one above The one is binlog.000002

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

There are two ways to determine the start position and end position, one is to use time As the start and end, one is to use the position of the log as the start and end position

2.1, use the time range

View the log content through the mysqlbinlog mysql-bin.000002 command, and then find the deleted time point ：

# at 131708213
#210610 11:27:01 server id 1  end_log_pos 131708311 CRC32 0x0fc755e2     Table_map: `loongwind_base`.`xxxx` mapped to number 139
# at 131708311
#210610 11:27:01 server id 1  end_log_pos 131708411 CRC32 0xa91616b9     Write_rows: table id 139 flags: STMT_END_F

BINLOG '
BffBYBMBAAAAYgAAAJe12QcAAIsAAAAAAAEADmR4bWhfYmFzZV9oenN5ABpkeF9zeV9hc3NldHNf
ZXh0ZW5kc19jb3B5MQAICAgIDwgSCBIEAAgAAPgBAQACA/z/AOJVxw8=
BffBYB4BAAAAZAAAAPu12QcAAIsAAAAAAAMAAgAI/wDRAwAAm1M8AUIAAADRUjwBCgAAAFYL5gAM
AOWFrOWuieacuuWFs7EBAADbwZkAmama6E+xAQAA28GZAJmpmuhPuRYWqQ==
'/*!*/;

Then determine the time point of the last backup. If you cannot find the time point of the last backup through the log, you can fill in a time 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 -p'password' -e "show binlog events in 'binlog.000002'"|grep -i 'DROP TABLE'

The execution 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 replace the keyword through the above command Find the position point of the last backup

3. Restore

3.1 Restore through time

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 -p'password' -s -N -f -D loongwind_base

where dxmh_base_hzsy is the database name

3.2 Restore through position

mysqlbinlog  --start-position=1178  --stop-position=2751 -d dxmh-sy binlog.000002|mysql -uroot -p'password' -s -N -f -D loongwind_base
loongwind_base 为数据库名称

If you really can't find the start time or start position, you don't need to write --start-datetime or --start-position. This will use the beginning of this log file to restore to the end. In order to prevent conflicts with existing For data conflicts, you need to add -f, that is, force to skip the error and continue execution.

The above is the detailed content of How to restore MySQL data through log files. For more information, please follow other related articles on the PHP Chinese website!