Recovering mistakenly deleting data using Percona Xtrabackup

After using Percona xtrabackup to do a full standby and two incremental backups, part of a table's data has been mistakenly deleted, how to recover mistakenly deleted data.

Thinking: will be fully prepared and incremental backup, the latest backup generated binary log all copied to the test environment, in the test environment after the deleted data recovery, will mistakenly delete data generation INSERT statement, on the original machine execution.

One: Scenario simulation

The available data are as follows:

mysql> use DBA;

Reading table information for completion oftable and column names

You can turn off the feature to get Aquicker startup with-a

Database changed

Mysql> select * from T2;

+----+

| ID |

+----+

| 1|

| 2|

+----+

2 rows in Set (0.00 sec)

At this point, make a full backup:

Innobackupex--user=root--password=system@123/backup/mysql/

A backup file was generated under this path: 2016-03-27_01-18-13

Insert two data:

mysql> INSERT into t2 (ID) VALUES (3), (4);

Query OK, 2 rows affected (0.02 sec)

Records:2 duplicates:0 warnings:0

To do an incremental backup:

Innobackupex--user=root--password=system@123--incremental/backup/mysql/--incremental-basedir=/backup/mysql/ 2016-03-27_01-18-13

A backup file was generated under this backup path: 2016-03-27_01-19-33

Insert two more data:

mysql> INSERT into t2 (ID) VALUES (5), (6);

Query OK, 2 rows affected (0.02 sec)

Records:2 duplicates:0 warnings:0

To do an incremental backup:

Innobackupex--user=root--password=system@123--incremental/backup/mysql/--incremental-basedir=/backup/mysql/ 2016-03-27_01-19-33

A backup file was generated under this backup path: 2016-03-27_01-20-43

Note: The second incremental backup is backed up as a starting point for the last incremental backup.

Insert two more data:

mysql> INSERT into t2 (ID) VALUES (7), (8);

Query OK, 2 rows affected (0.02 sec)

Records:2 duplicates:0 warnings:0

Simulate mistakenly delete a piece of data:

mysql> Delete from t2 where id = 4;

Query OK, 1 row affected (0.03 sec)

Continue to add two new data:

mysql> INSERT into t2 (ID) VALUES (9), (10);

Query OK, 2 rows affected (0.01 sec)

Records:2 duplicates:0 warnings:0

Mysql> select * from T2;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

+------+

9 Rows in Set (0.00 sec)

Two: Proceed to restore

Now I'm going to restore the id=4 data.

2.1 Copy backup files and binary logs

In order not to affect the production environment data, copy the backup and the resulting binary log to the test machine:

Scp-r/backup/mysql/2016-03-27_01*192.168.1.21:/backup/mysql/

The last backup file name, called 2016-03-27_01-20-43, is backed up at 1 points, 20 minutes, 43 seconds, and the binaries have the following:

[Root@single binlog]# LL

Total 92

......

RW-RW----. 1 MySQL mysql 261 Mar 00:12 mysql-bin.000011

-RW-RW----. 1 MySQL mysql 143 Mar 00:24 mysql-bin.000012

-RW-RW----. 1 MySQL mysql 1466 Mar 00:57 mysql-bin.000013

-RW-RW----. 1 mysql mysql 1666 Mar 01:22 mysql-bin.000014

-RW-RW----. 1 MySQL mysql 559 Mar 00:26 mysql-bin.index

Based on the binary log file time, I just copy mysql-bin.000014 to the test environment.

Alternatively, you can determine which binary log files need to be replicated based on the binary log file name in Xtrabackup_binlog_info in the latest backup.

SCP mysql-bin.000014 192.168.1.21:/data/server/mysql/binlog/b.log

2.2 Application Log in test environment

--Apply Full log:

Innobackupex--apply-log--redo-only/backup/mysql/2016-03-27_01-18-13

Merge the first incremental backup data into a full backup:

Innobackupex--apply-log--redo-only--incremental/backup/mysql/2016-03-27_01-18-13/--incremental-dir=/backup/ Mysql/2016-03-27_01-19-33

Merge the second incremental backup data into a full backup:

Innobackupex--apply-log--incremental/backup/mysql/2016-03-27_01-18-13/--incremental-dir=/backup/mysql/ 2016-03-27_01-20-43--user=root--password=system@123

2.3 Using Backup to recover data

#停库:

Service MySQL Stop

#备份数据文件:

cd/data/server/mysql/

MV Data data_bak_03270131

#重建数据文件目录:

mkdir data

#利用备份恢复数据:

Innobackupex--copy-back/backup/mysql/2016-03-27_01-18-13/

#更改数据库数据目录的拥有者和所属组

Chown Mysql:mysql/data/server/mysql/data-r

chmod 755/data/server/mysql/data

#启动服务

Service MySQL Start

Verify

Mysql> select * from T2;

+------+

| ID |

+------+

| 2 |

| 1 |

| 3 |

| 4 |

| 5 |

| 6 |

+------+

6 rows in Set (0.00 sec)

2.4 Recover data with binary logs

Check out the binaries and locations recorded in the latest backup files:

cd/backup/mysql/2016-03-27_01-20-43

[Root@single 2016-03-27_01-20-43]# Catxtrabackup_binlog_info

mysql-bin.000014 1004

cd/data/server/mysql/binlog/

Mysqlbinlog-v B.log--start-position=1004> B.log3

View the file B.log3 and find the position of the data that was accidentally deleted:

# at 1308

#160327 1:21:51 Server ID 3 end_log_pos1412 Query thread_id=1 exec_time=0 error_code=0

Use ' DBA '/*!*/;

SET timestamp=1459012911/*!*/;

Delete from t2 where id = 4

#恢复最新备份至误删除数据前的数据

Mysqlbinlog/data/server/mysql/binlog/b.log--start-position=1004--stop-position=1308 > D.sql

Mysql> Source/data/server/mysql/binlog/d.sql

Mysql> select * from T2;

+------+

| ID |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

2.5 Generate INSERT statement

Then connect the test server with Navicat and generate the INSERT statement with the T2 table data, such as:

......

A similar script is generated at the end:

INSERT into ' T2 ' VALUES (1);

INSERT into ' T2 ' VALUES (2);

INSERT into ' T2 ' VALUES (3);

INSERT into ' T2 ' VALUES (4);

INSERT into ' T2 ' VALUES (5);

INSERT into ' T2 ' VALUES (6);

INSERT into ' T2 ' VALUES (7);

INSERT into ' T2 ' VALUES (8);

2.6 Executing INSERT statements in the source environment

Add the database name, select the value=4 data, insert on the source environment server.