xtrabackup恢复单表

亦凉 2022-03-19 07:26 233阅读 0赞

在一般生产环境下可能会删表，这时候如果基于数据库恢复，然后再把那个表恢复过来，就比较麻烦，这里提供一个基于物理备份直接恢复单表的方法

在innodb引擎设置独立表空间，一般表对应的物理文件有两个

-rw-r-----. 1 mysql mysql     8728 Jan 19 07:42 OrderItems.frm
    -rw-r-----. 1 mysql mysql   114688 Jan 19 07:42 OrderItems.ibd

一个是ibd文件，里面存放表中的数据，frm文件是表格式文件，存放表的一些元数据信息，于是如果我们删表了，那么数据文件就没了，但是如果我们可以重新创建一个frm文件，然后再补上一个我们物理备份的对应表的idb文件，是不是就ok了。下面测试

首先我们先取得这个表的建表语句

mysql> SHOW CREATE TABLE OrderItems\G;
    *************************** 1. row ***************************
           Table: OrderItems
    Create Table: CREATE TABLE `OrderItems` (
      `order_num` int(11) NOT NULL,
      `order_item` int(11) NOT NULL,
      `prod_id` char(10) NOT NULL,
      `quantity` int(11) NOT NULL,
      `item_price` decimal(8,2) NOT NULL,
      PRIMARY KEY (`order_num`,`order_item`),
      KEY `FK_OrderItems_Products` (`prod_id`),
      CONSTRAINT `FK_OrderItems_Orders` FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`),
      CONSTRAINT `FK_OrderItems_Products` FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)

然后模拟故障删表

DROP TABLE OrderItems;

开始恢复，用先前备份的建表语句再创建一个表

mysql> CREATE TABLE `OrderItems` (
        ->   `order_num` int(11) NOT NULL,
        ->   `order_item` int(11) NOT NULL,
        ->   `prod_id` char(10) NOT NULL,
        ->   `quantity` int(11) NOT NULL,
        ->   `item_price` decimal(8,2) NOT NULL,
        ->   PRIMARY KEY (`order_num`,`order_item`),
        ->   KEY `FK_OrderItems_Products` (`prod_id`),
        ->   CONSTRAINT `FK_OrderItems_Orders` FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`),
        ->   CONSTRAINT `FK_OrderItems_Products` FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`)
        -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Query OK, 0 rows affected (0.02 sec)

然后删除表空间

-- 删除表空间，只剩表结构
    mysql> ALTER TABLE OrderItems DISCARD TABLESPACE;

然后从xtrabackup的全备中，找到这个文件复制过去

cp /backup/xtrabackup_backupfiles/mydb/OrderItems.ibd  /data/mydb/OrderItems.ibd
    chown mysql:mysql /data/mydb/OrderItems.ibd

导入表空间

-- 导入表空间，前提是数据符合表结构
    ALTER TABLE OrderItems IMPORT TABLESPACE;

检查数据

mysql> SELECT * FROM OrderItems;
    +-----------+------------+---------+----------+------------+
    | order_num | order_item | prod_id | quantity | item_price |
    +-----------+------------+---------+----------+------------+
    |     20005 |          1 | BR01    |      100 |       5.49 |
    |     20005 |          2 | BR03    |      100 |      10.99 |
    |     20006 |          1 | BR01    |       20 |       5.99 |
    |     20006 |          2 | BR02    |       10 |       8.99 |
    |     20006 |          3 | BR03    |       10 |      11.99 |
    |     20007 |          1 | BR03    |       50 |      11.49 |
    |     20007 |          2 | BNBG01  |      100 |       2.99 |
    |     20007 |          3 | BNBG02  |      100 |       2.99 |
    |     20007 |          4 | BNBG03  |      100 |       2.99 |
    |     20007 |          5 | RGAN01  |       50 |       4.49 |
    |     20008 |          1 | RGAN01  |        5 |       4.99 |
    |     20008 |          2 | BR03    |        5 |      11.99 |
    |     20008 |          3 | BNBG01  |       10 |       3.49 |
    |     20008 |          4 | BNBG02  |       10 |       3.49 |
    |     20008 |          5 | BNBG03  |       10 |       3.49 |
    |     20009 |          1 | BNBG01  |      250 |       2.49 |
    |     20009 |          2 | BNBG02  |      250 |       2.49 |
    |     20009 |          3 | BNBG03  |      250 |       2.49 |
    +-----------+------------+---------+----------+------------+
    18 rows in set (0.00 sec)

可以看到数据都回来了