How to restore data using binlog in mysql5.7

Step 1: Ensure that MySQL has binlog enabled

show variables like '%log_bin%';
log_bin is enabled when it is on.

Step 2: Enter the binlog file directory and find the binary log file

mysql> show binary logs; #Get the binlog file listmysql> show master status; #View the binlog file currently being writtenmysql> reset master; Reset binlog 

Step 3: Use the mysqlbinlog tool command to view the database addition, deletion, modification and query records (you must switch to the mysqlbinlog directory to be effective) or directly specify binlog

Example 1 : Query the operation log from 2021-3-12 14:00:00 to 2021-3-12 14:03:00. The database is g_xinxiangshop. Enter the following command to write the data into a spare txt file.

/usr/local/mysql/bin/mysqlbinlog --no-defaults --database=g_xinxiangshop --start-datetime="2021-3-12 14:00:00" --stop-datetime="2021-3-12 14:03:00" /usr/local/mysql/data/mysql-bin.000001 > /tmp/binlog.txt

Example 2 : Query the operation log of the database g_xinxiangshop from 2021-3-12 14:00:00 to 2021-3-12 14:03:00, and filter out the operation records that only include the data in the g_user table. Enter the following command to write the data to a spare txt file

/usr/local/mysql/bin/mysqlbinlog --no-defaults --database=g_xinxiangshop --start-datetime="2021-3-12 14:00:00" --stop-datetime="2021-3-12 14:03:00" /usr/local/mysql/data/mysql-bin.000001 | grep g_user > /tmp/binlog.txt

Example 3 : Query the operation log of the database g_shoptest from 2021-3-15 15:25:00 to 2021-3-15 15:35:00 and output it to the screen
/usr/local/mysql/bin/mysqlbinlog --no-defaults --database=g_shoptest --start-datetime="2021-3-15 15:25:00" --stop-datetime="2021-3-15 15:35:00" /data/mysql/mysql-bin.000001 |more

The picture is the same as Example 1 and Example 2. You can perform the recovery operation after seeing the truncate operation and the record point!

Step 4: Test using bin_log to recover data

Login to mysql
test :

1. reset master; reset binlog and regenerate logs

2. Test a table by inserting a piece of data and then accidentally deleting it.

3. View bin-log command: show binlog events in 'mysql-bin.000001';

As shown in the figure above, we can see that the deletion points are between 928 and 1294; the previously added data are between 154 and 520

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 --start-position 154 --stop-position 520 | mysql -uroot -p g_shoptest

After the execution is complete, go and see if you deleted the data and it is back.

binlog command

1. The most commonly used method is to restore the data at the specified data end, which can be directly restored to the database:

mysqlbinlog --start-date="2021-3-12 14:00:00" --stop-date="2021-3-12 14:03:00" mysql_bin.000001 |mysql -uroot -p123456

2. Specify the start and end positions. From the binary log generated above, we can know the start and end positions of a log. We can specify the log from position A to position B during the recovery process. The following two parameters are required to specify:

• --start-positon="50" //Specify starting from position 50
• --stop-position="100"//Specify to end at position 100
  

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 --start-position 7903538 --stop-position 7904498|mysql -uroot -p123456

**Common ErrorERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 31, event_type: 35

*The problem is caused by the mysqlbinlog version

View the mysqlbinlog used by the current os

shell> which mysqlbinlog
/usr/bin/mysqlbinlog

View the mysqlbinlog currently used by mysql

±--------------±------------------+
| Variable_name | Value |
±--------------±------------------+
| basedir | /usr/local/mysql/ |
±--------------±------------------+

mysql> show variables like 'basedir';

Comparing the two versions

shell> /usr/bin/mysqlbinlog --version
shell> /usr/local/mysql/bin/mysqlbinlog --version

To solve this problem, just specify the mysqlbinlog path

/usr/local/mysql/bin/mysqlbinlog --no-defaults --database=g_xinxiangshop --start-datetime="2021-3-12 14:00:00" --stop-datetime="2021-3-12 14:03:00" /usr/local/mysql/data/mysql-bin.000001 > /tmp/binlog.txt

This is the end of this article about how to use binlog to recover data in MySQL 5.7. For more information about MySQL binlog recovery, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!