利用Percona xtrabackup恢复误删除数据

在利用Percona xtrabackup做了一个全备及两个增量备份后，一个表的部分数据被误删除了，怎样恢复误删除数据呢？

 

思路：将全备及增量备份，最新的备份后产生的二进制日志全部拷贝到测试环境，在测试环境把删除的数据恢复以后，将误删除数据生成insert语句，在原机器上执行。

 

一：情景模拟

现有数据如下：

mysql> use dba;

Reading table information for completion oftable and column names

You can turn off this feature to get aquicker startup with -A

 

Database changed

mysql> select * from t2;

+----+

| id |

+----+

|  1|

|  2|

+----+

2 rows in set (0.00 sec)

此时，做完全备份：

innobackupex --user=root--password=……  /backup/mysql/

在该路径下产生了备份文件：2016-03-27_01-18-13

 

插入两条数据：

mysql> insert into t2(id) values(3),(4);

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0  Warnings: 0

 

做增量备份：

innobackupex --user=root--password=…… --incremental /backup/mysql/--incremental-basedir=/backup/mysql/2016-03-27_01-18-13

在该备份路径下产生了备份文件：2016-03-27_01-19-33

 

再插入两条数据：

mysql> insert into t2(id) values(5),(6);

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0  Warnings: 0

 

做增量备份：

innobackupex --user=root--password=…… --incremental /backup/mysql/--incremental-basedir=/backup/mysql/2016-03-27_01-19-33

在该备份路径下产生了备份文件：2016-03-27_01-20-43

 

注意：第二次增量备份是以上次增量备份为起点进行备份的。

 

再插入两条数据：

mysql> insert into t2(id) values(7),(8);

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0  Warnings: 0

模拟误删除一条数据：

mysql> delete from t2 where id = 4;

Query OK, 1 row affected (0.03 sec)

 

继续新增两条数据：

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)

 

二：着手恢复

 

现在我要恢复id=4的数据。

 

2.1 拷贝备份文件及二进制日志

为了不影响生产环境数据，将备份及之后产生的二进制日志拷贝到测试机器上：

 

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

最后一个备份文件名称为2016-03-27_01-20-43即是在1点20分43秒备份的，而二进制文件有下面这些：

[root@single binlog]# ll

total 92

……

rw-rw----. 1 mysql mysql   261 Mar 27 00:12 mysql-bin.000011

-rw-rw----. 1 mysql mysql   143 Mar 27 00:24 mysql-bin.000012

-rw-rw----. 1 mysql mysql  1466 Mar 27 00:57 mysql-bin.000013

-rw-rw----. 1 mysql mysql  1666 Mar 27 01:22 mysql-bin.000014

-rw-rw----. 1 mysql mysql   559 Mar 27 00:26 mysql-bin.index

 

根据二进制日志文件时间，所以我只需拷贝mysql-bin.000014到测试环境即可。

或者可以根据最新备份中xtrabackup_binlog_info中的二进制日志文件名来判断需要复制哪些二进制日志文件。

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

 

2.2 在测试环境应用日志

--应用全备日志：

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

将第一次做的增量备份数据进行合并到全备份中去：

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

将第二次做的增量备份数据进行合并到全备份中去：

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=……

 

2.3 利用备份恢复数据

#停库：

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

验证

mysql> select * from t2;

+------+

| id  |

+------+

|   2 |

|   1 |

|   3 |

|   4 |

|   5 |

|   6 |

+------+

6 rows in set (0.00 sec)

 

2.4 利用二进制日志恢复数据

 

查看一下最新的备份文件中记录的二进制文件和位置：

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

 

查看该文件b.log3,发现误删除数据所在Position：

# 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 生成insert语句

 

然后用navicat连接测试服务器，将t2表数据生成insert语句，如：

 

……

最后会生成类似脚本：

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 在源环境执行insert语句

 

加上数据库名称，选择value=4那条数据，在源环境服务器上插入即可。