备份单表：
1 制作备份
[root@localhost ~]# innobackupex --user=backup --password=123456 --databases="cumcm.cumcm_sys_country" /home/pack
查看备份
[root@localhost 2019-05-31_13-17-57]# ls
backup-my.cnf cumcm ibdata1 xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
2 准备恢复文件
[root@localhost 2019-05-31_13-17-57]# innobackupex --apply-log --export /home/pack/2019-05-31_13-17-57/
[root@localhost cumcm]# ls
cumcm_sys_country.cfg cumcm_sys_country.exp cumcm_sys_country.frm cumcm_sys_country.ibd
3 创建表（库）
mysql> CREATE TABLE cumcm_sys_country (
Id int(11) NOT NULL AUTO_INCREMENT,
Name varchar(255) DEFAULT NULL,
AreaCode varchar(10) DEFAULT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=193 DEFAULT CHARSET=utf8;
4 丢弃表空间
mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
5 恢复文件拷贝到对应表或者库目录下
[root@localhost cumcm]# cp /home/pack/2019-05-31_13-17-57/cumcm/cumcm_sys_country.* /var/lib/mysql/cumcm
cp：是否覆盖"/var/lib/mysql/cumcm/cumcm_sys_country.frm"？ y
查看覆盖情况
[root@localhost cumcm]# ls
cumcm_sys_country.cfg cumcm_sys_country.exp cumcm_sys_country.frm cumcm_sys_country.ibd
[root@localhost cumcm]# cd /var/lib/mysql
6 授权文件
[root@localhost cumcm]# chown -R mysql:mysql /var/lib/mysql
7 加载表空间
mysql> ALTER TABLE cumcm.cumcm_sys_country IMPORT TABLESPACE;

物理全备需要满足的条件：
innodb_file_per_table开启
innodb存储引擎
表有对应的.ibd文件
满足以上条件就可以通过物理备份集生成以表为单位的export文件

测试

物理全备

/usr/local/bin/xtrabackup --defaults-file=/etc/3309.cnf --user=root --socket=/tmp/3309.sock --parallel=4 --backup --target-dir=/data0/sql/20171016.bak

export表：还原，加选项--export
xtrabackup --prepare --export --target-dir=/data0/sql/20171016.bak/

如果备份时时加密的，那么还原时还需指定密钥文件，选项--keyring-file-data

查看某个表对应的备份文件
find /tmp/20171016.bak/ -name 't1*'
/data0/sql/20171016.bak/test/t1.frm
/data0/sql/20171016.bak/test/t1.cfg
/data0/sql/20171016.bak/test/t1.exp
/data0/sql/20171016.bak/test/t1.ibd
import 表

复制代码

删除ibd文件

ALTER TABLE test.t1 DISCARD TABLESPACE;

.cfg文件可以不拷贝并重新授权

cp /data0/sql/20171016.bak/test/t1.* /data0/mysql/3309_test/test/

ll /data0/mysql/3309_test/test/t1.*
-rw-r----- 1 mysql mysql 16384 Oct 16 23:11 /data0/mysql/3309_test/test/t1.exp
-rw-r----- 1 mysql mysql 8676 Oct 16 23:11 /data0/mysql/3309_test/test/t1.frm
-rw-r----- 1 mysql mysql 163840 Oct 16 23:12 /data0/mysql/3309_test/test/t1.ibd

导入

ALTER TABLE test.t1 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.11 sec)
复制代码

###########操作期间遇到的报错###################

ALTER TABLE test.t1 IMPORT TABLESPACE;
ERROR 1812 (HY000): Tablespace is missing for table test.t1.

是未拷贝ibd文件导致

ALTER TABLE test.t1 IMPORT TABLESPACE;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table test.t1 : Tablespace not found

是文件未授权导致