Delete database without running away – explain MySQL data recovery in detail

Time:2019-10-21

In daily work, there will always be some mistakes in deleting database tables and data caused by shaking hands, writing wrong conditions, writing wrong table names, and connecting the production database wrongly. Then, if you can’t recover the data, what DBAs are needed.

Related articles

  • MySQL backup strategy
  • MySQL data recovery

1 Preface

The premise of data recovery should be backed up and enabled.binlogThe format isrow。 If there is no backup file, the database table will be deleted.lsofIf there are records in, it is possible to recover some files, but if the database does not open the table file, then it will have to run. If it is not turned onbinlog, after data recovery, the data from the backup point in time is not available. IfbinlogFormat notrow, so there is no way to do flashback operation after misoperation of data. You can only go through the backup and recovery process honestly.

2 direct recovery

Direct recovery is to use backup files for full recovery, which is the most common scenario

2.1 full recovery of mysqldump backup

Using mysqldump file to recover data is very simple, and it directly decompresses the execution.

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

2.2 xtrabackup backup full recovery

Recovery process

#Step 1: decompress (ignore this step if there is no compression)
Innobackupex -- decompress

#Step 2: application log
Innobackupex -- apply log 

#Step 3: copy the backup file to the data directory
Innobackupex -- dataDir = < MySQL data Directory > -- copy back < backup file directory >

2.3 point in time recovery

Point in time recovery depends onbinlogLog, frombinlogFind all logs from backup point to recovery point in, and then apply them. Let’s test them.

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 data per second

[[email protected] ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())';date;sleep 1;done

backups

[[email protected] ~]$ mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql

Find the log location at the time of backup

[[email protected] ~]$ 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;

Suppose to restore to2019-08-09 11:01:54At this point in time, we search binlog from39654reach019-08-09 11:01:54Log

[[email protected] ~]$ mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql
[[email protected] ~]$ 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

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

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

Then perform the recovery

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

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

Check 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)

Recovered to2019-08-09 11:01:54This time

3 restore a table

3.1 restore a table from mysqldump backup

Suppose the table to be recovered ismytest.mytest

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

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

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

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

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

3.2 restore a table from xtrabackup backup

hypothesis./backup_xtra_fullThe directory is the backup file to which the log has been applied after decompression.

3.2.1 MyISAM table

Suppose the table is recovered from the backup filemytest.t_myisam, found in backup filet_myisam.frm t_myisam.MYD t_myisam.MYIThese three files are copied to the corresponding data directory and authorized

Enter MySQL and check the table

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

Suppose the table is recovered from the backup filemytest.t_innodb, restore if it is setinnodb_file_per_table = on

  1. Take a new example
  2. Build an identical table on the example.
  3. implementalter table t_innodb discard tablespace;, delete tablespace, this operation willt_innodb.ibddelete
  4. Found in backup filet_innodb.ibdCopy this file to the corresponding data directory and authorize
  5. implementalter table t_innodb IMPORT tablespace;Load tablespace
  6. implementflush table t_innodb;check table t_innodb;Checklist
  7. UsemysqldumpExport the data and then import it to the database to be recovered

Be careful:

  1. To recover and then dump the new instance is to avoid risks. If it is a test, you can directly operate on the original database.2-6
  2. Only valid before 8.0

4 skip misoperation SQL

Skip misoperation SQL is generally used to perform operations that cannot be flashed back, such asdrop table\database

4.1 use backup file recovery skip

4.1.1 do not open gtid

The procedure of using backup file recovery is similar to that of point in time recovery. The difference is that there is one more binlog search operation.

For example, I have two tables here.aandb, insert a piece of data every minute, then make a full backup, and then delete the tableb, skip this SQL now.

Delete tablebDatabase status after

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

1 find out the log location at the time of backup

[[email protected] ~]$ 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;

2 find out the implementationdrop tablePOS location of statement

[[email protected] 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 */

We can see from the resultsdropThe starting position of the statement is120629, ending at120747

3 extract other records from binglog that skip this statement

#The start position of the first article 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 article is the end position of the drop statement
mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql

4 restore backup files

[[email protected] ~]$ mysql -S /tmp/mysql.sock < backup.sql

State 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)

5 recover incremental data

[[email protected] ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql
[[email protected] ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql

After recovery, you can see that it has been skipped.dropSentence

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 start gtid

Use gtid to skip wrong SQL directly

  1. Find the log location at the time of backup
  2. Find out the gtid value of the drop table statement executed
  3. Log location to the latest binglog log when exporting backup
  4. restore backed up files
  5. Skip this gtid

    Set session gtid [next = 'corresponding gtid value';
    BEGIN; COMMIT;
    SET SESSION GTID_NEXT = AUTOMATIC;
  6. Apply the incremental binlog log obtained in step 3

4.2 use delay library to skip

4.2.1 do not open gtid

The key operation of using delay library recovery isstart slave until

I built two MySQL nodes in the test environment, node 2 delayed by 600 seconds, created two tables a and B, and inserted one data simulation business data insertion per second.

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 table II

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 this SQL operation step

1 delay library stop synchronization

stop slave;

2 find out the implementationdrop tablePOS position of the previous sentence

[[email protected] ~]$ 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 */

We can see from the resultsdropOf the statementPrevious sentenceThe starting position is35134, so we synced to35134(don’t choose this one wrong.)

3 delay the synchronization of the library to the previous SQL to be skipped

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

View the status and 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

4 start synchronization after skipping one SQL

set global sql_slave_skip_counter=1;
start slave;

View the synchronization status. 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 start gtid

The steps to skip with gtid will be much simpler, as long as a transaction with the same gtid of the SQL to be skipped is executed.

1 stop synchronization

2 find out the implementationdrop tableGtid of the statement

3 execute the gtid transaction

Set session gtid [next = 'corresponding gtid value';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;

4 continue synchronization

5 flashbacks

Flashback operations are reverse operations, such as executingdelete from a where id=1, flashback will execute the corresponding insert operationinsert into a (id,...) values(1,...), for misoperation data, only forDMLStatement is valid and requiresbinlogFormat is set toROW。 This chapter introduces two easy-to-use open source tools

5.1 binlog2sql

Binlog2sql is an open-source tool for public comment to parse binlog. 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/

#Installation dependency
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 rollback DML operation developed and maintained by the technical engineering department of meituan review company. The project is linked to Myflash.

Restrictions:

  • Binlog format must be row and binlog row image = full
  • Only 5.6 and 5.7 are supported
  • Only DML can be rolled back (add, delete, change)

5.2.1 installation

#Dependency (CentOS)
yum install gcc*  pkg-config glib2 libgnomeui-devel -y

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

#Build 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

Generate rollback statement

flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos> 

Generated after executionbinlog_output_base.flashbackFile, requiredmysqlbinlogParse it out and use it again

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

Recommended Today

Database lock (notes)

Introduction MyISAMTransaction is not supported. What is the difference between MyISAM and InnoDB in terms of locks MyISAMTable level lock is used by default, row level lock is not supported. InnoDBRow level locks are used by default, and table level locks are also supported. Read lock / shared lock: othersessionCan read, but can’t write. Write […]