MySQL uses mysqldump+binlog to completely restore the deleted database principle analysis

1. Overview

In the daily operation and maintenance of MySQL databases, users may accidentally delete data. Common operations for accidentally deleting data include:

• The user executes delete and deletes data that should not be deleted due to incorrect conditions (DML operation);
• The user executes update, and the update data fails due to incorrect conditions (DML operation);
• The user accidentally deletes the table drop table (DDL operation);
• The user mistakenly empties the table truncate (DDL operation);
• User deletes database drop database and runs away (DDL operation)
• …wait

Although these situations do not occur often, if they do occur, we need to be able to recover from them. The following describes how to do so.

(II) Restoration Principle

If you want to restore the database to the point before the failure, you need a full backup of the database and all binary logs generated after the full backup.

Full backup function: Use full backup to restore the database to the location of the last complete backup;

Function of binary log: After restoring the database to the location of the last full backup using a full backup set, you need to redo all actions that occurred in the database after the last full backup. The redo process is to parse the binary log file into SQL statements and then put them into the database for execution again.

For example: Xiao Ming used mysqldump to back up the database at 8:00 p.m. on April 1st. At 12:00 a.m. on April 2nd, Xiao Hua accidentally deleted the database. Then, when performing database recovery, you need to use the complete backup taken on the evening of April 1st to restore the database to "8:00 p.m. on April 1st". How to recover the data after 8:00 p.m. on April 1st and before 12:00 a.m. on April 2nd? You have to parse the binary log to redo the SQL executed during this period.

(III) Deletion database recovery test

(3.1) Experimental purpose

In this experiment, I directly tested deleting the database and executed drop database lijiamandb to confirm whether it can be restored.

(3.2) Testing process

Create test tables test01 and test02 in the test database lijiamandb, then execute mysqldump to fully prepare the database, and then execute drop database to confirm whether the database can be restored.

STEP 1: Create test data. In order to simulate the busy daily production environment, frequent database operations generate a large amount of binary logs. I specifically use stored procedures and EVENTs to generate a large amount of data.

Create a test table:

use lijiamandb;create table test01
 (
 id1 int not null auto_increment,
 name varchar(30),
 primary key(id1)
 );

create table test02
 (
 id2 int not null auto_increment,
 name varchar(30),
 primary key(id2)
 );

Create a stored procedure to insert data into the test table. Each time the stored procedure is executed, 10,000 records are inserted into test01 and test02 respectively:

CREATE DEFINER=`root`@`%` PROCEDURE `p_insert`()
BEGIN
#Routine body goes here...
DECLARE str1 varchar(30);
DECLARE str2 varchar(30);
DECLARE i int;
set i = 0;

while i < 10000 do
 set str1 = substring(md5(rand()),1,25);
 insert into test01(name) values(str1);
 set str2 = substring(md5(rand()),1,25);
 insert into test02(name) values(str1);
 set i = i + 1;
 end while;
 END

Create an event to execute the above stored procedure every 10 seconds:

use lijiamandb;
 create event if not exists e_insert
 on schedule every 10 seconds
 on completion preserve
 do call p_insert();

Start EVENT and automatically insert 10,000 records into test01 and test02 every 10 seconds

mysql> show variables like '%event_scheduler%';
+----------------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------------+-------+
| event_scheduler | OFF |
+----------------------------------------------------------+-------+

mysql> set global event_scheduler = on;
 Query OK, 0 rows affected (0.08 sec)

--After 3 minutes. . .
STEP2: After generating a large amount of test data in the first step, use mysqldump to perform a full backup of the lijiamandb database
mysqldump -h192.168.10.11 -uroot -p123456 -P3306 --single-transaction --master-data=2 --events --routines --databases lijiamandb > /mysql/backup/lijiamandb.sql

Note: You must add --master-data=2 to set the end point of the mysqldump backup in the backup set.

--After 3 minutes. . .

STEP3: To facilitate the consistency check of the data before and after the deletion of the database, stop inserting data into the table first. At this time, both test01 and test02 have 930,000 rows of data. We must also ensure that there are 930,000 rows of data in subsequent recovery.

mysql> set global event_scheduler = off;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from test01;
 +----------+
 | count(*) |
 +----------+
 | 930000 |
 +----------+
row in set (0.14 sec)

mysql> select count(*) from test02;
 +----------+
 | count(*) |
 +----------+
 | 930000 |
 +----------+
row in set (0.13 sec)

STEP4: Delete the database

mysql> drop database lijiamandb;
Query OK, 2 rows affected (0.07 sec)

STEP5: Full import using mysqldump

mysql> create database lijiamandb;
Query OK, 1 row affected (0.01 sec)

mysql> exit
 Bye
 [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb < /mysql/backup/lijiamandb.sql 
 mysql: [Warning] Using a password on the command line interface can be insecure.

After performing a full backup and restore, it was found that there were only 753,238 records:

[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb 

mysql> select count(*) from test01;
 +----------+
 | count(*) |
 +----------+
 |753238|
 +----------+
row in set (0.12 sec)

mysql> select count(*) from test02;
 +----------+
 | count(*) |
 +----------+
 |753238|
 +----------+
row in set (0.11 sec)

Obviously, after the full import, the data is incomplete. Next, use mysqlbinlog to perform incremental recovery of the binary log.

The most important thing about using mysqlbinlog for incremental log recovery is to determine the start position (start-position) and end position (stop-position) to be recovered. The start position (start-position) is the position after we execute all, and the end position is the position before the failure occurs.
STEP6: Confirm the final location of the mysqldump backup

[root@masterdb backup]# cat lijiamandb.sql |grep "CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000044', MASTER_LOG_POS=8526828

If the backup is to position 8526828 of log No. 44, the starting point of recovery can be set to: 8526828 of log No. 44.

--Next, confirm the end point to be restored, that is, the position before executing "DROP DATABASE LIJIAMAN". You need to confirm it in the binlog.

[root@masterdb binlog]# ls
 master-bin.000001 master-bin.000010 master-bin.000019 master-bin.000028 master-bin.000037 master-bin.000046 master-bin.000055
 master-bin.000002 master-bin.000011 master-bin.000020 master-bin.000029 master-bin.000038 master-bin.000047 master-bin.000056
 master-bin.000003 master-bin.000012 master-bin.000021 master-bin.000030 master-bin.000039 master-bin.000048 master-bin.000057
 master-bin.000004 master-bin.000013 master-bin.000022 master-bin.000031 master-bin.000040 master-bin.000049 master-bin.000058
 master-bin.000005 master-bin.000014 master-bin.000023 master-bin.000032 master-bin.000041 master-bin.000050 master-bin.000059
 master-bin.000006 master-bin.000015 master-bin.000024 master-bin.000033 master-bin.000042 master-bin.000051 master-bin.index
 master-bin.000007 master-bin.000016 master-bin.000025 master-bin.000034 master-bin.000043 master-bin.000052
 master-bin.000008 master-bin.000017 master-bin.000026 master-bin.000035 master-bin.000044 master-bin.000053
 master-bin.000009 master-bin.000018 master-bin.000027 master-bin.000036 master-bin.000045 master-bin.000054

# After searching for many times, it is found that drop database is in log file No. 54 [root@masterdb binlog]# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 | grep -i "drop database lijiamandb"
drop database lijiamandb

# Save to text for easy searching [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 > master-bin.txt


# Confirm that the location before drop database is: 9019487 of file 54
 # at 9019422
 #200423 16:07:46 server id 11 end_log_pos 9019487 CRC32 0x86f13148 Anonymous_GTID last_committed=30266 sequence_number=30267 rbr_only=no
 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 # at 9019487
 #200423 16:07:46 server id 11 end_log_pos 9019597 CRC32 0xbd6ea5dd Query thread_id=100 exec_time=0 error_code=0
 SET TIMESTAMP=1587629266/*!*/;
 SET @@session.sql_auto_is_null=0/*!*/;
 /*!\C utf8 *//*!*/;
 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 drop database lijiamandb
 /*!*/;
 # at 9019597
 #200423 16:09:25 server id 11 end_log_pos 9019662 CRC32 0x8f7b11dc Anonymous_GTID last_committed=30267 sequence_number=30268 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 # at 9019662
 #200423 16:09:25 server id 11 end_log_pos 9019774 CRC32 0x9b42423d Query thread_id=100 exec_time=0 error_code=0
 SET TIMESTAMP=1587629365/*!*/;
 create database lijiamandb

STEP7: Determine the start and end points, and start incremental recovery: 8526828 of log No. 44
End: File 54 9019487

This is divided into three commands for execution. The start log file involves the start-position parameter and is executed separately. The stop log file involves the stop-position parameter and is executed separately. The intermediate log files do not involve special parameters and are all executed together.

# Start log file

# Starting log file mysqlbinlog --start-position=8526828 /mysql/binlog/master-bin.000044 | mysql -uroot -p123456

 
# Intermediate log file mysqlbinlog /mysql/binlog/master-bin.000045 /mysql/binlog/master-bin.000046 /mysql/binlog/master-bin.000047 /mysql/binlog/master-bin.000048 /mysql/binlog/master-bin.000049 /mysql/binlog/master-bin.000050 /mysql/binlog/master-bin.000051 /mysql/binlog/master-bin.000052 /mysql/binlog/master-bin.000053 | mysql -uroot -p123456

 
# Terminate the log file mysqlbinlog --stop-position=9019487 /mysql/binlog/master-bin.000054 | mysql -uroot -p123456

STEP8: Recovery is complete, confirm that all data has been restored

[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb
mysql> select count(*) from test01;
+----------+
| count(*) |
+----------+
| 930000 |
+----------+
row in set (0.15 sec)

mysql> select count(*) from test02;
+----------+
 | count(*) |
+----------+
 | 930000 |
+----------+
row in set (0.13 sec)

4. Conclusion

1. For DML operations, binlog records all DML data changes:
--For insert, binlog records the row data of insert
--For update, binlog records the row data before and after the change
--For delete, binlog records the data before deletion. If the user accidentally executes a DML operation, mysqlbinlog can be used to restore the database to the point before the failure.

2. For DDL operations, binlog only records user behavior, not row changes, but this does not affect our ability to restore the database to the point before the failure.

In short, using mysqldump full backup plus binlog log, you can restore data to any time before the failure.

This is the end of this article about how to use mysqldump+binlog to completely recover a deleted database in MySQL. For more information about how to recover a deleted database in MySQL, 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!