Talk about how to perform data recovery based on docker deployment of mysql

foreword

By chance, a friend said that his department's database had been deleted by mistake, and he wanted to restore it. He Baidu searched some information and tried it. But he found that some errors would be reported, so he asked me for help. For me, because the database of the company is controlled by the DBA, there is no chance to operate it at ordinary times, and it basically stays in theory.

But in order to maintain my glorious image in his heart and not let him see that I am a half-ass, I pretended to force him to send me the wrong information, and I would like to Baidu. So there is such a hydrology today.

The friend's database is built based on docker, and the friend once suspected that his recovery was unsuccessful because of docker.

Binlog prerequisite knowledge for data recovery

Note: The example in this article is a demo example, and mysql is built based on docker. Version is mysql 8.0

1. First confirm whether mysql has binlog enabled

 SHOW VARIABLES LIKE '%log_bin%';

Note: mysql8.0 has binlog enabled by default, and binlog-format is row format. If it is below mysql8.0, it needs to be configured and enabled through /etc/my.cnf. The configuration content is as follows

 ##配置binlog日志路径以及日志文件名前缀、生成的日志格式为：binlog.000001
log-bin=/var/lib/mysql/binlog
##注意配置server-id，保证唯一性
server-id=1
##binlog格式，有3种statement,row,mixed
binlog-format=row

2. View the binlog log list

 SHOW MASTER LOGS;

3. View the current binlog and current location

 SHOW MASTER STATUS;

4. View binlog content

 SHOW BINLOG EVENTS IN 'binlog.000008';

5. Specify the site query, such as starting the query from pos 746

 SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746;

6. Paging query of specified site

 SHOW BINLOG EVENTS IN 'binlog.000008' FROM 746 LIMIT 0,5

7. Refresh to generate new binlog

 FLUSH LOGS;

Data recovery case

There is such a user table

Suppose we delete the lisi record by mistake

Now we want to get lisi back.

1. View binlog

 SHOW MASTER LOGS;

2. View binlog events

 SHOW BINLOG EVENTS IN 'binlog.000010';

3. Recover data from the specified location

 /usr/bin/mysqlbinlog  --start-position=65945--stop-position=66150 --database=demo_test  /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v demo_test

Note:

 /usr/bin/mysqlbinlog 为binlog命令
--start-position=65945为恢复的开始位置
--stop-position=66150为恢复的结束位置
--database=demo_test 指定数据库为demo_test
/var/lib/mysql/binlog.000010 为binlog日志

| /usr/bin/mysql -uroot -p123456 -v demo_test
通过管道连接数据库，并通过-v显示详细信息

At this point, it may appear that

 ERROR 1062 (23000) at line 19: Duplicate entry '1' for key 't_user.PRIMARY'

This error is also a problem with a friend. This literally means that there is a primary key conflict. At this time, there are two ways

a. Method 1: Delete the conflicting records in the database, and then execute the recovery statement. Check the database now

You will find that the data has been restored

b. Method 2: export binlog to sql statement and change the insert statement to replace into.

The specific operations are as follows:

1. Enter the binlog content into a sql file

 /usr/bin/mysqlbinlog  --start-position=65945 --stop-position=66150 --database=demo_test --base64-output=decode-rows -v  /var/lib/mysql/binlog.000010 --result-file=/var/lib/mysql/binlog000010.sql

2. Copy the generated sql file to the host

 docker cp e0b7fb702f75:/var/lib/mysql/binlog000010.sql /

3. The contents of the exported file are as follows

Note: If --base64-output=decode-rows is not added, the output will be base64 encrypted content

4. Change insert into to replace into

5. Re-import the modified sql statement into docker

 docker cp /binlog000010.sql  e0b7fb702f75:/var/lib/mysql

6. Execute sql statement

 mysql -uroot -p123456 -f </var/lib/mysql/binlog000010.sql

4. Recover data through time

First run the following command to check the time point

 /usr/bin/mysqlbinlog --no-defaults /var/lib/mysql/binlog.000010 > /var/lib/mysql/bin_log000010.sql

Note: Because there are many logs, please import them to the file first and then view them

Execute the following statement to restore

 /usr/bin/mysqlbinlog --no-defaults --start-datetime="2022-04-25 16:10:00" --stop-datetime="2022-04-25 16:11:00" --database=demo_test /var/lib/mysql/binlog.000010 | /usr/bin/mysql -uroot -p123456 -v demo_test

Note:

 --start-datetime="2022-04-25 16:10:00" 为恢复的开始时间
-stop-datetime="2022-04-25 16:11:00" 为恢复的结束时间

Execute the statement and observe the database, and you can find that the data is also restored.

Summarize

Although I have seen the eight-part article on data recovery based on mysqlbinlog before, I have never tried it. Just took the opportunity of a friend to try it out. This article is a record. Really answering a sentence on paper, I feel shallow, and I absolutely know that this matter has to be done.