Mysql从备份中恢复单个表

背景

适用从备份目录(全备或是增备)，恢复单表 (利用可传输表空间)[使用的是Xtrabackup]
原理

从MySQL 5.6版本开始，支持可传输表空间（Transportable Tablespace），
那么利用这个功能就可以实现单表的恢复，同样利用这个功能还可以把innodb表移动到另外一台服务器上。
可以参考：https://yq.aliyun.com/articles/59271

link

前提

1.开启了参数innodb_file_per_table(使用独立表空间)
2.安装工具：mysql-utilities，其中mysqlfrm可以读取表结构。
$ yum install mysql-utilities -y

写在前面
参考以下链接进行的测试，已整理和补充了一些内容 #请读者进行选择性读取(也许实际中您只需要cp之后进行discard和import tablespace)
link

命令集合

1.全备备份
/usr/local/percona-xtrabackup/bin/innobackupex 
--defaults-file=/usr/local/sandboxes/msb_5_7_26/master/my.sandbox.cnf 
--socket=/tmp/mysql_sandbox17261.sock --user=msandbox --password='123456' 
--no-timestamp /tools/backup/11
2.模拟误删表
 drop table test110;
3.利用mysql-utilities工具读取表结构（不支持MariaDB）
 mysqlfrm --diagnostic /tools/backup/11/test/test110.frm #请记录下表结构
(随便说一句，yum 和mysqlfrm依赖本地python.本次使用的是python2.7 )
终端显示的表结构和实际别结构有些不同。(后面介绍)
4.加一个写锁，确保安全
lock tables test110 write;
5.丢弃表空间
alter table test110 discard tablespace; 
6.从备份中拷贝ibd文件到具体库的数据目录(test库)
主库：
cp /tools/backup/11/test/test110.ibd   /usr/local/sandboxes/msb_5_7_26/master/data/test/   
从库：
cp /tools/backup/11/test/test110.ibd /usr/local/sandboxes/msb_5_7_26/node1/data/test/
7.并且修改权限
chown -R mysql.mysql  /usr/local/sandboxes/msb_5_7_26/node1/data/test/test110.ibd
有警告，可以忽略。详情可以看：https://yq.aliyun.com/articles/59271
8.导入表空间
alter table test110 import tablespace;
9.验证
select count(*) from test110;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
10.解锁
unlock tables;

较为重要的补充

#如果有主从架构需要在从库上执行同样的操作。(操作之前提前stop slave或是sql_thread)避免以下报错：

(保证主库alter 表空间的语句  传到  从库也是有表空间文件的,主从表空间文件均cp之后再alter import)

有出处的地方(存疑)

mysqlfrm读取的表结构

实际的表结构

测试数据使用的存储过程

--创建测试表
CREATE TABLE `test110` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comp` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `aid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--创建插入存储过程
DELIMITER $$
CREATE PROCEDURE test_insert()
BEGIN
declare i int;
set i=0;
while i<1000000 do
INSERT INTO `test110` VALUES (null,'TMP01','user11','会员中台',
'2019-08-15 00:00:02','11','22');
set i=i+1;
end while;
end $$
DELIMITER ;

测试如下，速度很快:
mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.74 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> select count(*) from test110;
（LT:本地虚拟机1G 1C测试100W,耗时2min45s 文件大小81M 平均测试三次，平均耗时3min/100W）
set sql_log_bin = off;  (插入测试数据可以先设置为记录binlog) 以上插入日志大约增大了300M

删除存储过程或是查看:
DROP PROCEDURE test_insert
SELECT * FROM information_schema.routines WHERE routine_name='test_insert';

再次鸣谢
link

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​