mysql_数据恢复篇04_表空间迁移_mysql-utilities工具和ibd2sdi工具的使用

使用场景

1. 不增加生产负载的情况下生成 一个报表
2. 在一个新的服务器上建立一个和生产上数据相同的表
3. 做一个备份在发生问题或错误操作时用于恢复
4. 快速将数据从一个服务器迁移到另一个服务器

限制条件

1. 恢复环境不能跨版本
2. 需要知道原表的建表语句
3. 源和目标数据库必须具有相同的character set
4. 要恢复的表不能有外键

启用一个全新的8.0版本的3307实例来做本次演示

内容 : 将3306实例中的t100w表迁移到3307实例中

[root@db01 ~]# mysql -S /tmp/mysql3307.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

db02 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

1. 获取建表语句

(1) 因为迁移表空间需要创建一个与故障表一模一样的表,所以需要知道故障表的建表语句而8.0版本之前的建表语句在frm文件中,所以需要下载 mysql-utilities 工具(需要python环境)来获取frm文件中的建表语句

[root@db02 ~]# yum install python3
[root@db02 ~]# cd /opt
[root@db02 ~]# wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz
[root@db02 ~]# tar xvf mysql-utilities-1.6.5.tar.gz
[root@db02 ~]# cd mysql-utilities-1.6.5
[root@db02 ~]# python setup.py build
[root@db02 ~]# python setup.py install

执行以下命令就可以获取t100w表的建表语句,但是含有注释和空格
mysqlfrm --diagnostic /data/3356/data/test/t100w.frm
所以直接过滤掉无用信息,复制建表语句,在数据库中创建即可

[root@mysql02 ~]# mysqlfrm --diagnostic /data/3356/data/test/t100w.frm |grep -Ev '^#|^$'
CREATE TABLE `t100w` (
  `id` int DEFAULT NULL,
  `num` int DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

(2) 8.0 版本开始 ,建表语句都存到了ibd文件中,所以需要8.0版本自带的 ibd2sdi 工具来获取建表语句,但是该工具获取到的信息过于冗余,所以还需要 配合 jq 工具来使用, jq工具直接yum下载即可

[root@db02 ~]# yum install jq
[root@db02 ~]# ibd2sdi /data/3306/data/test/t100w.ibd ## 不推荐
[root@db02 ~]# ibd2sdi /data/3306/data/test/t100w.ibd |jq  '.[]?|.[]?|.dd_object?|({table:.name?},(.columns?|.[]?|{name:.name,type:.column_type_utf8}))' > t100w.json
[root@db02 ~]# cat t100w.json 
{
  "table": "t100w"
}
{
  "name": "id",
  "type": "int"
}
{
  "name": "num",
  "type": "int"
}
{
  "name": "k1",
  "type": "char(2)"
}
{
  "name": "k2",
  "type": "char(4)"
}
{
  "name": "dt",
  "type": "timestamp"
}
.....................不可描述的过程,这已经被jq工具简化很多了,直接ibd2sdi出来的数据简直就是一坨屎
{
  "table": "test/t100w"
}

2. 迁移表空间

(1)在3307实例创建测试数据库test,进入到其中用刚才获取的t100w建表语句生成表t100w

db02 [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)
db02 [(none)]>use test;
Database changed
db02 [test]>CREATE TABLE `t100w` (
    ->   `id` int DEFAULT NULL,
    ->   `num` int DEFAULT NULL,
    ->   `k1` char(2) DEFAULT NULL,
    ->   `k2` char(4) DEFAULT NULL,
    ->   `dt` timestamp NOT NULL 
    -> ) ENGINE=InnoDB;

(2)创建完成后,执行以下操作,删除表空间,保留表结构

db02 [test]> alter table t100w discard tablespace;
Query OK, 0 rows affected (0.01 sec)

(3)锁定3306中需要迁移的表

db02 [test]>flush table test.t100w with read lock;
Query OK, 0 rows affected (0.00 sec)

(4)将3306实例world库中的t100w表的ibd文件拷贝到3307实例test库目录下

[root@db02 ~]# cp -a /data/3306/data/test/t100w.ibd /data/3307/data/test/
[root@db02 ~]# ll /data/3307/data/test/
total 57344
-rw-r----- 1 mysql mysql 58720256 Dec 30 21:32 t100w.ibd

(5)目标端导入表空间文件,查看文件,恢复成功!

db02 [test]>alter table t100w import  tablespace;
Query OK, 0 rows affected, 1 warning (0.59 sec)

db02 [test]>select * from t100w limit 10;
+------+--------+------+------+---------------------+
| id   | num    | k1   | k2   | dt                  |
+------+--------+------+------+---------------------+
|    1 |  25503 | 0M   | IJ56 | 2019-08-12 11:41:16 |
|    2 | 756660 | rx   | bc67 | 2019-08-12 11:41:16 |
|    3 | 876710 | 2m   | tu67 | 2019-08-12 11:41:16 |
|    4 | 279106 | E0   | VWtu | 2019-08-12 11:41:16 |
|    5 | 641631 | At   | rsEF | 2019-08-12 11:41:16 |
|    6 | 584039 | QJ   | VWlm | 2019-08-12 11:41:16 |
|    7 | 541486 | vc   | ijKL | 2019-08-12 11:41:16 |
|    8 | 771751 | 47   | ghLM | 2019-08-12 11:41:16 |
|    9 | 752847 | aQ   | CDno | 2019-08-12 11:41:16 |
|   10 | 913759 | ej   | EFfg | 2019-08-12 11:41:16 |
+------+--------+------+------+---------------------+

最后别忘了把3306的表解锁😁

db02 [(none)]>unlock tables;
Query OK, 0 rows affected (0.00 sec)