利用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=system@123 /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=system@123 --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=system@123 --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=system@123

  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那條資料，在源環境伺服器上插入即可。