Summary of various methods of MySQL data recovery

1. Introduction
2. Direct recovery

2.1 Full recovery of mysqldump backup
2.2 xtrabackup backup full recovery
2.3 Recovery based on a point in time

3. Recover a table

3.1 Restoring a table from a mysqldump backup
3.2 Restoring a table from an xtrabackup backup

4. Skip incorrect SQL operations

4.1 Restore Skip using backup file
4.2 Skipping using the Delay Library

5. Flashback.

5.1 binlog2sql
5.2 MyFlash

1. Introduction

The premise of data recovery is to make a good backup and enable binlog in row format. If there is no backup file, then once the library table is deleted, it is really deleted. If there are still records in lsof, it is possible to restore some files. But if the database happens not to open this table file, then you can only run away. If binlog is not enabled, all data from the backup point in time will be lost after the data is restored. If the binlog format is not row, there is no way to perform a flashback operation after an erroneous operation on the data, and you can only go through the backup and recovery process.

2. Direct recovery

Direct recovery is to use the backup file to perform full recovery, which is the most common scenario.

2.1 Full recovery of mysqldump backup

Restoring data using the mysqldump file is very simple. Simply unzip and execute:

gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p

2.2 xtrabackup backup full recovery

Recovery process:

# Step 1: Decompress (if there is no compression, you can ignore this step)
innobackupex --decompress <backup file directory>

# Step 2: Apply log innobackupex --apply-log <backup file directory>

 # Step 3: Copy the backup file to the data directory innobackupex --datadir=<MySQL data directory> --copy-back <backup file directory>

2.3 Recovery based on a point in time

Point-in-time recovery relies on binlog logs. You need to search all logs from the backup point to the recovery point in the binlog and then apply them. Let's test it.

Create a new test table:

chengqm-3306>>show create table mytest.mytest \G;
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ctime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert one piece of data per second:

[mysql@mysql-test ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert in

Backup:

[mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --defa

Find out the log location at the time of backup:

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000032', MASTER_LOG_POS=39654;

Assuming that we want to restore to the time point of 2019-08-09 11:01:54, we search the binlog for logs from 39654 to 019-08-09 11:01:54.

[mysql@mysql-test ~]$ mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql
[mysql@mysql-test-83 ~]$ tail -n 20 backup_inc.sql
......
### INSERT INTO `mytest`.`mytest`
### SET
### @1=161 /* INT meta=0 nullable=0 is_null=0 */
### @2='2019-08-09 11:01:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
......

Current number of data entries:

-- The number of data before 2019-08-09 11:01:54chengqm-3306>>select count(*) from mytest.mytest where ctime < '2019-08-09 11:01:54';
+----------+
| count(*) |
+----------+
| 161 |
+----------+
1 row in set (0.00 sec)

All data items

chengqm-3306>>select count(*) from mytest.mytest;
+----------+
| count(*) |
+----------+
| 180 |
+----------+
1 row in set (0.00 sec)

Then perform the restore:

# Full recovery [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql

 # Apply incremental log [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql

Check the data:

chengqm-3306>>select count(*) from mytest.mytest;
+----------+
| count(*) |
+----------+
| 161 |
+----------+
1 row in set (0.00 sec)

chengqm-3306>>select * from mytest.mytest order by id desc limit 5;
+-----+---------------------+
| id | ctime |
+-----+---------------------+
| 161 | 2019-08-09 11:01:53 |
| 160 | 2019-08-09 11:01:52 |
| 159 | 2019-08-09 11:01:51 |
| 158 | 2019-08-09 11:01:50 |
| 157 | 2019-08-09 11:01:49 |
+-----+---------------------+
5 rows in set (0.00 sec)

It has been restored to the time point of 2019-08-09 11:01:54.

3. Recover a table

3.1 Restoring a table from a mysqldump backup

Assume that the table to be restored is mytest.mytest:

# Extract all data from a database sed -n '/^-- Current Database: `mytest`/,/^-- Current Database:/p' backup.sql > backup_mytest.sql

# Extract the table creation statement from the library backup file sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `mytest`/!d;q' backup_mytest.sql > mytest_table_create.sql

# Extract the insert data statement from the library backup file grep -i 'INSERT INTO `mytest`' backup_mytest.sql > mytest_table_insert.sql

# Restore the table structure to the mytest library mysql -u<user> -p mytest < mytest_table_create.sql

# Restore table data to mytest.mytest tablemysql -u<user> -p mytest < mytest_table_insert.sql

3.2 Restoring a table from an xtrabackup backup

Assume that the ./backup_xtra_full directory contains the backup files with the logs applied after decompression.

3.2.1 MyISAM table Assume that the table mytest.t_myisam is restored from the backup file. Find the three files t_myisam.frm, t_myisam.MYD, and t_myisam.MYI from the backup file, copy them to the corresponding data directory, and authorize

Enter MySQL. Checklist Condition:

chengqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| mytest |
| t_myisam |
+------------------+
2 rows in set (0.00 sec)

chengqm-3306>>check table t_myisam;
+-----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| mytest.t_myisam | check | status | OK |
+-----------------+-------+----------+----------+
1 row in set (0.00 sec)

3.2.2 Innodb table Assume that the table mytest.t_innodb is restored from the backup file. The premise of restoration is that innodb_file_per_table = on is set:

Start a new instance;
Create a table on the instance that is exactly the same as the original one;
Execute alter table t_innodb discard tablespace; to delete the tablespace. This operation will delete t_innodb.ibd.
Find the t_innodb.ibd file from the backup file, copy it to the corresponding data directory, and authorize it;
Execute alter table t_innodb IMPORT tablespace; to load the tablespace;
Execute flush table t_innodb;check table t_innodb; to check the table;
Use mysqldump to export the data and then import it into the database to be restored.

Notice:

Restoring on a new instance and then dumping it out is to avoid risks. If it is a test, you can directly perform steps 2-6 on the original database;
Only valid in versions prior to 8.0.

4. Skip incorrect SQL operations

Skipping incorrect SQL is generally used to execute operations that cannot be flashed back, such as drop table\database.

4.1 Restore Skip using backup file

4.1.1 Disable GTID

The steps for restoring using a backup file are similar to those for point-in-time recovery, except that there is an additional binlog search operation. For example, I have created two tables a and b, inserted a piece of data every minute, then made a full backup, and then deleted table b. Now I want to skip this SQL.

The database status after deleting table b:

chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
+------------------+
1 row in set (0.00 sec)

Find out the log location at the time of backup

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=38414;

Find the pos position where the drop table statement was executed

[mysql@mysql-test mysql_test]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`';
# at 120629
#190818 19:48:30 server id 83 end_log_pos 120747 CRC32 0x6dd6ab2a Query thread_id=29488 exec_time=0 error_code=0
SET TIMESTAMP=1566128910/*!*/;
DROP TABLE `b` /* generated by server */

From the results we can see that the start position of the drop statement is 120629 and the end position is 120747.

Extract other records from binglog that skip this statement

# The start-position of the first line is the pos position of the backup file, and the stop-position is the start position of the drop statement mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql

# The start-position of the second line is the end position of the drop statement mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.00003

Restoring a backup file

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql

Status after full recovery:

chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
2 rows in set (0.00 sec)

chgnqm-3306>>select count(*) from a;
+----------+
| count(*) |
+----------+
| 71 |
+----------+
1 row in set (0.00 sec)

Restore incremental data

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql

After the restoration, you can see that the drop statement has been skipped:

chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
2 rows in set (0.00 sec)

chgnqm-3306>>select count(*) from a;
+----------+
| count(*) |
+----------+
| 274 |
+----------+
1 row in set (0.00 sec)

4.1.2 Enable GTID
Using GTID, you can directly skip the incorrect SQL:

Find out the log location at the time of backup;
Find out the GTID value that executed the drop table statement;
When exporting backup, the log location is moved to the latest binglog log;
Restore backup files;
Skip this GTID;

SET SESSION GTID_NEXT='corresponding GTID value';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;

Apply the incremental binlog obtained in step 3.

4.2 Skipping using the Delay Library

4.2.1 Disable GTID

The key operation of using delayed library recovery is to start slave until. I set up two MySQL nodes in the test environment. Node 2 was delayed for 600 seconds. Two tables, a and b, were created. One piece of data was inserted every second to simulate business data insertion.

localhost:3306 -> localhost:3307 (delay 600)

Current node 2 status:

chengqm-3307>>show slave status \G;
...
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000039
          Read_Master_Log_Pos: 15524
               Relay_Log_File:mysql-relay-bin.000002
                Relay_Log_Pos: 22845
        Relay_Master_Log_File: mysql-bin.000038
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
        Seconds_Behind_Master: 600
...

Current node two table:

chengqm-3307>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+

Delete table b at node 1:

chengqm-3306>>drop table b;
Query OK, 0 rows affected (0.00 sec)

chengqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
+------------------+
1 row in set (0.00 sec)

The next step is to skip the SQL statement.

Delay library stop sync

stop slave;

Find the POS position of the statement before the drop table statement is executed

[mysql@mysql-test ~]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000039 | grep -i -B 10 'drop table `b`';
...
# at 35134
#190819 11:40:25 server id 83 end_log_pos 35199 CRC32 0x02771167 Anonymous_GTID last_committed=132 sequence_number=133 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 35199
#190819 11:40:25 server id 83 end_log_pos 35317 CRC32 0x50a018aa Query thread_id=37155 exec_time=0 error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=1566186025/*!*/;
DROP TABLE `b` /* generated by server */

From the results, we can see that the start position of the statement before the drop statement is 35134, so we synchronize to 35134 (don’t make the wrong choice).

Delay database synchronization to the SQL to be skipped Previous

change master to master_delay=0;
start slave until master_log_file='mysql-bin.000039',master_log_pos=35134;

Check the status to see that it has been synchronized to the corresponding node:

chengqm-3307>>show slave status \G;
...
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000039
          Read_Master_Log_Pos: 65792
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
          Exec_Master_Log_Pos: 35134
...
               Until_Log_File: mysql-bin.000039
                Until_Log_Pos: 35134

Start synchronization after skipping a SQL statement

set global sql_slave_skip_counter=1;
start slave;

Check the synchronization status and the statement to delete table b has been skipped:

chengqm-3307>>show slave status \G;
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)

chengqm-3307>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
2 rows in set (0.00 sec)

4.2.2 Enable GTID
The steps to skip using GTID are much simpler. You only need to execute a transaction with the same GTID as the SQL to be skipped.

Stop syncing;
Find out the GTID that executed the drop table statement;
Execute the transaction of this GTID;

SET SESSION GTID_NEXT='corresponding GTID value';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;

Continue syncing;

5. Flashback.

The flashback operation is the reverse operation. For example, if delete from a where id=1 is executed, the flashback will execute the corresponding insert operation insert into a (id,...) values(1,...). It is used to correct erroneous data operations. It is only valid for DML statements and requires the binlog format to be set to ROW. This chapter introduces two relatively useful open source tools.

5.1 binlog2sql

binlog2sql is an open source tool developed by Dianping.com for parsing binlogs. It can be used to generate flashback statements. The project address is binlog2sql.

5.1.1 Installation

wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip
unzip binlog2sql.zip
cd binlog2sql-master/

# Install dependencies pip install -r requirements.txt

5.1.2 Generate Rollback SQL

python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\
--start-file='<binlog_file>' \
--start-datetime='<start_time>' \
--stop-datetime='<stop_time>' > ./flashback.sql

python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \
--start-file='<binlog_file>' \
--start-position=<start_pos> \
--stop-position=<stop_pos> > ./flashback.sql

5.2 MyFlash

MyFlash is a tool for rolling back DML operations developed and maintained by the Technical Engineering Department of Meituan Dianping. The project link is MyFlash.

limit:

The binlog format must be row, and binlog_row_image=full;
Only supports 5.6 and 5.7;
Only DML (add, delete, modify) can be rolled back.

5.2.1 Installation

# Dependencies (centos)
yum install gcc* pkg-config glib2 libgnomeui-devel -y

# Download the file wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip
unzip MyFlash.zip
cd MyFlash-master

# Compile and install gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
mv binary /usr/local/MyFlash
ln -s /usr/local/MyFlash/flashback /usr/bin/flashback

5.2.2 Use the generated rollback statement:

flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<s

After execution, a binlog_output_base.flashback file will be generated, which needs to be parsed by mysqlbinlog before use:

mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p

The above is the detailed content of the various methods of MySQL data recovery. For more information about MySQL data recovery, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to restore data using binlog in mysql5.7
MySQL restores data through binlog
MySQL uses frm files and ibd files to restore table data
MySQL uses binlog logs to implement data recovery
Implementation of mysql using mysqlbinlog command to restore accidentally deleted data
Two methods of restoring MySQL data
MySQL database backup and recovery implementation code
MySQL uses mysqldump+binlog to completely restore the deleted database principle analysis
Analysis of MySQL data backup and recovery implementation methods
MySQL Binlog Data Recovery: Detailed Explanation of Accidentally Deleting a Database

<<: Using Docker+jenkins+python3 environment to build a super detailed tutorial

>>: 10 excellent Web UI libraries/frameworks