xtrabackup 备份 mysql 5.6_MYSQL 5.6从XTRABACKUP完整备份中恢复单表

root@master 15:27: [test]> select * from t1;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | 9 |

| 2 | bb | 9 |

| 3 | cc | 9 |

| 4 | dd | 9 |

| 5 | ee | 9 |

| 6 | ff | 9 |

| 7 | NULL | 10 |

| 8 | NULL | 11 |

+----+------+------+

8 rows in set (0.00 sec)

APPLY LOG一次：

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=xQT0USuwNKNrQ --apply-log /data/backup/20170718/2017-07-18_15-25-24/

首先需要恢复表结构：

root@i-u3a6lqi8:/data/backup/20170718/2017-07-18_15-25-24# mysqlfrm --diagnostic test/t1.frm

# WARNING: Cannot generate character set or collation names without the --server option.

# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.

# Reading .frm file for test/t1.frm:

# The .frm file is a TABLE.

# CREATE TABLE Statement:

CREATE TABLE `test`.`t1` (

`id` int(8) NOT NULL,

`name` varchar(27) DEFAULT NULL,

`age` int(8) DEFAULT NULL,

PRIMARY KEY `PRIMARY` (`id`),

KEY `name` (`name`)

) ENGINE=InnoDB;

将读取的表结构拿到数据库进行创建：

root@master 15:30: [test]> CREATE TABLE `test`.`t1` (

-> `id` int(8) NOT NULL,

-> `name` varchar(27) DEFAULT NULL,

-> `age` int(8) DEFAULT NULL,

-> PRIMARY KEY `PRIMARY` (`id`),

-> KEY `name` (`name`)

-> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

为表加写锁：

root@master 15:32: [test]> lock tables t1 write;

Query OK, 0 rows affected (0.00 sec)

COPY备份中的IBD文件，并授权：

root@i-u3a6lqi8:/data/backup/20170718/2017-07-18_15-25-24# cp test/t1.ibd /data/database/mysql/3388/data/test/

root@i-u3a6lqi8:/data/backup/20170718/2017-07-18_15-25-24# chown -R mysql.mysql /data/database/mysql/3388/data/test/t1.ibd

载入表空间：

root@master 15:33: [test]> alter table t1 import tablespace;

Query OK, 0 rows affected, 1 warning (0.02 sec)

注： 在有从库的时候，需要把上面的IBD文件也COPY到备库一份，否则执行IMPORT会在从库上报错，从而导致复制中断。

检查数据：

root@master 15:36: [test]> select * from t1;

+----+------+------+

| id | name | age |

+----+------+------+

| 1 | aa | 9 |

| 2 | bb | 9 |

| 3 | cc | 9 |

| 4 | dd | 9 |

| 5 | ee | 9 |

| 6 | ff | 9 |

| 7 | NULL | 10 |

| 8 | NULL | 11 |

+----+------+------+

8 rows in set (0.00 sec)

无问题后， 解锁表：

root@master 15:37: [test]> unlock tables;

Query OK, 0 rows affected (0.00 sec)