MySQL使用ibd文件批量恢复数据

今天连接nacos的数据库挂掉了无法启动，所以需要把对应的数据迁移到其他的数据库，

1、首先在新的数据库吧nacos的初始化数据刷进去

注意：只刷入建表语句，需要把nacos的原始的insert语句删除

刷入成功后

服务器连接新的数据库
Mysql -u root -p

然后use nacos_8035;
（注意：nacos_8035是我使用的数据库名称）
执行下面的命令

mysql> SELECT concat('alter table ', table_name, ' discard tablespace;')  FROM information_schema.tables WHERE table_schema = 'nacos_8035';

±-----------------------------------------------------------+
| concat('alter table ‘, table_name, ’ discard tablespace;’) |
±-----------------------------------------------------------+
| alter table config_info discard tablespace; |
| alter table config_info_aggr discard tablespace; |
| alter table config_info_beta discard tablespace; |
| alter table config_info_tag discard tablespace; |
| alter table config_tags_relation discard tablespace; |
| alter table group_capacity discard tablespace; |
| alter table his_config_info discard tablespace; |
| alter table permissions discard tablespace; |
| alter table roles discard tablespace; |
| alter table tenant_capacity discard tablespace; |
| alter table tenant_info discard tablespace; |
| alter table users discard tablespace; |
±-----------------------------------------------------------+
12 rows in set (0.01 sec)

然后依次执行上述查询出来的SQL ,目的是删除新建的表空间，如果实际的表过多，可以将上述生成的SQL文件放到.sql文件，然后使用soucre命令

mysql> alter table users discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table config_info discard tablespace;  
Query OK, 0 rows affected (0.01 sec)

mysql> alter table config_info_aggr discard tablespace; 
Query OK, 0 rows affected (0.00 sec)

mysql> alter table config_info_beta discard tablespace; 
Query OK, 0 rows affected (0.01 sec)

mysql> alter table config_info_tag discard tablespace;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table config_tags_relation discard tablespace;  
Query OK, 0 rows affected (0.01 sec)

mysql> alter table group_capacity discard tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table his_config_info discard tablespace;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table permissions discard tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table roles discard tablespace;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table tenant_capacity discard tablespace; 
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tenant_info discard tablespace;  
Query OK, 0 rows affected (0.01 sec)

然后将原来的ibd文件复制到新库的data目录下
然后将ibd文件的权限修改为mysql

chown -R mysql:mysql *

然后执行

mysql> SELECT concat('alter table ', table_name, ' import tablespace;') FROM information_schema.tables WHERE table_schema = 'nacos_8035';

±----------------------------------------------------------+
| concat('alter table ‘, table_name, ’ import tablespace;’) |
±----------------------------------------------------------+
| alter table config_info import tablespace; |
| alter table config_info_aggr import tablespace; |
| alter table config_info_beta import tablespace; |
| alter table config_info_tag import tablespace; |
| alter table config_tags_relation import tablespace; |
| alter table group_capacity import tablespace; |
| alter table his_config_info import tablespace; |
| alter table permissions import tablespace; |
| alter table roles import tablespace; |
| alter table tenant_capacity import tablespace; |
| alter table tenant_info import tablespace; |
| alter table users import tablespace; |
±----------------------------------------------------------+
12 rows in set (0.01 sec)

然后依次执行上面的SQL，目的是导入表空间

mysql> alter table config_info import tablespace; 
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> alter table config_info_aggr import tablespace; 
Query OK, 0 rows affected, 2 warnings (0.09 sec)

mysql> alter table config_info_beta import tablespace;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> alter table config_info_tag import tablespace; 
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> alter table config_tags_relation import tablespace;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> alter table group_capacity import tablespace;  
Query OK, 0 rows affected, 2 warnings (0.08 sec)

mysql> alter table his_config_info import tablespace; 
Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql> alter table permissions import tablespace;    
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> alter table roles import tablespace; 
Query OK, 0 rows affected, 2 warnings (0.13 sec)

mysql> alter table tenant_capacity import tablespace;   
Query OK, 0 rows affected, 2 warnings (0.10 sec)

mysql> alter table tenant_info import tablespace;  
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> alter table users import tablespace;   
Query OK, 0 rows affected, 2 warnings (0.04 sec)

执行完后，刷新数据库 原来的数据就已经恢复