使用场景
- 不增加生产负载的情况下生成 一个报表
- 在一个新的服务器上建立一个和生产上数据相同的表
- 做一个备份在发生问题或错误操作时用于恢复
- 快速将数据从一个服务器迁移到另一个服务器
限制条件
- 恢复环境不能跨版本
- 需要知道原表的建表语句
- 源和目标数据库必须具有相同的character set
- 要恢复的表不能有外键
启用一个全新的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)