mysql5.7只拷贝某个数据库目录下文件来还原该数据库

郑全

一、前提：

数据库版本需与恢复数据的版本一致
数据库名及字符集一致


二、主要步骤：
1.启动mysql服务，建表   
2.表空间卸载    (对应执行表ibd文件丢失)
3.停止mysql服务   
4.拷贝ibd文件   
5.启动mysql服务   
6.表空间装载   


三、问题背景
      如果把数据库的数据文件拷贝到data_dir位置，直接打开数据库，可以看到数据库，以及表，但打开会报 ERROR 1146：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| szapp                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;
+-------------------------------+
| Tables_in_medical             |
+-------------------------------+
| admin_logs                    |
| admin_permissions             |
| admin_roles                   |
| article                       |
| article_type                  |
| banner                        |
| complaint                     |
| complaint_event               |
| complaint_flow                |
| complaint_gj                  |
| complaint_method              |
| daily                         |
| daily_copy                    |
| daily_counter                 |
| daily_praise                  |
| daily_rules                   |
| daily_rules_type              |
| department                    |
| eval_rules                    |
| eval_rules_branch             |
| eval_rules_level              |
| eval_rules_range              |
| eval_rules_range_user         |
| failed_jobs                   |
| files                         |
| files_type                    |
| hospital                      |
| hospital_area                 |
| medical_eval_project          |
| medical_eval_project_auth     |
| medical_eval_rules            |
| medical_eval_rules_branch     |
| medical_eval_rules_level      |
| medical_eval_rules_range      |
| medical_eval_rules_range_user |
| migrations                    |
| notice                        |
| notice_read                   |
+-------------------------------+
38 rows in set (0.00 sec)

mysql> select * from admin_logs;
ERROR 1146 (42S02]: Table 'szapp.admin_logs' doesn't exist


四、具体操作步骤

    建表语句，可以通过 DBSAKE，或者mysqlfrm来解决。感觉DBSAKE工具要简单一些，
下面以 dbsake为例来说明：


1.下载dbsake工具并给工具赋予执行权限
   cd /tmp

   curl -s get.dbsake.net > dbsake && chmod u+x dbsake

2. 将frm文件全部上传至backup目录
   mkdir /tmp/backup
   cp $DATA_DIR/szapp/* /tmp/backup/.

3. 解析出所有表数据结构
   ./dbsake frmdump /tmp/backup/*.frm > /tmp/recover/create_database.sql

4. 创建同名数据库并指定字符集
      注：字符集需与恢复数据库一致

   CREATE DATABASE szapp DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

5. 恢复表结构
   use medical;
   source /tmp/recover/create_database.sql;

     至此：数据库表结构已恢复，只不过没有数据

     这里可能报错：
     ERROR 1067 (42000): Invalid default value for 'created_at'

   主要是 DATATIME类型的字段有 DEFAULT NULL 导致
      。。。
      `created_at` timestamp DEFAULT NULL,
   。。。

     可以通过SQL_MODE='';来解决，
    或者 去掉 NO_ZERO_IN_DATE,NO_ZERO_DATE



6. 卸载数据表空间
     不支持通配符，需挨个卸载

   ALTER TABLE 表名 DISCARD TABLESPACE;
   可以通过SQL生成批量的卸载表空间的SQL语句，拷贝出来执行即可。

   select concat('alter table ',table_name, ' discard tablespace;') from information_schema.tables where table_schema='szapp';
   +---------------------------------------------------------------+
| concat('alter table ',table_name, ' discard tablespace;')     |
+---------------------------------------------------------------+
| alter table admin_logs discard tablespace;                    |
| alter table admin_permissions discard tablespace;             |
| alter table admin_roles discard tablespace;                   |
| alter table article discard tablespace;                       |
| alter table article_type discard tablespace;                  |
| alter table banner discard tablespace;                        |
| alter table complaint discard tablespace;                     |
| alter table complaint_event discard tablespace;               |
| alter table complaint_flow discard tablespace;                |
| alter table complaint_gj discard tablespace;                  |
| alter table complaint_method discard tablespace;              |
| alter table daily discard tablespace;                         |
| alter table daily_copy discard tablespace;                    |
| alter table daily_counter discard tablespace;                 |
| alter table daily_praise discard tablespace;                  |
| alter table daily_rules discard tablespace;                   |
| alter table daily_rules_type discard tablespace;              |
| alter table department discard tablespace;                    |
| alter table eval_rules discard tablespace;                    |
| alter table eval_rules_branch discard tablespace;             |
| alter table eval_rules_level discard tablespace;              |
| alter table eval_rules_range discard tablespace;              |
| alter table eval_rules_range_user discard tablespace;         |
| alter table failed_jobs discard tablespace;                   |
| alter table files discard tablespace;                         |
| alter table files_type discard tablespace;                    |
| alter table hospital discard tablespace;                      |
| alter table hospital_area discard tablespace;                 |
| alter table medical_eval_project discard tablespace;          |
| alter table medical_eval_project_auth discard tablespace;     |
| alter table medical_eval_rules discard tablespace;            |
| alter table medical_eval_rules_branch discard tablespace;     |
| alter table medical_eval_rules_level discard tablespace;      |
| alter table medical_eval_rules_range discard tablespace;      |
| alter table medical_eval_rules_range_user discard tablespace; |
| alter table migrations discard tablespace;                    |
| alter table notice discard tablespace;                        |
| alter table notice_read discard tablespace;                   |
+---------------------------------------------------------------+
38 rows in set (0.00 sec)



7. 查看mysql数据路径
   show variables like 'datadir';
      进入上述路径，会有一个数据库同名文件夹，将拷贝的ibd文件上传至此目录即可
     并授权

     chown mysql:mysql 新的文件

8. 再进入mysql恢复表数据，就是重新加载表空间
   ALTER TABLE 表名 IMPORT TABLESPACE;

   通过SQL生成批量加载表空间的SQL语句。
     mysql> select concat('alter table ',table_name, ' import tablespace;') from information_schema.tables where table_schema='szapp';
+--------------------------------------------------------------+
| concat('alter table ',table_name, ' import tablespace;')     |
+--------------------------------------------------------------+
| alter table admin_logs import tablespace;                    |
| alter table admin_permissions import tablespace;             |
| alter table admin_roles import tablespace;                   |
| alter table article import tablespace;                       |
| alter table article_type import tablespace;                  |
| alter table banner import tablespace;                        |
| alter table complaint import tablespace;                     |
| alter table complaint_event import tablespace;               |
| alter table complaint_flow import tablespace;                |
| alter table complaint_gj import tablespace;                  |
| alter table complaint_method import tablespace;              |
| alter table daily import tablespace;                         |
| alter table daily_copy import tablespace;                    |
| alter table daily_counter import tablespace;                 |
| alter table daily_praise import tablespace;                  |
| alter table daily_rules import tablespace;                   |
| alter table daily_rules_type import tablespace;              |
| alter table department import tablespace;                    |
| alter table eval_rules import tablespace;                    |
| alter table eval_rules_branch import tablespace;             |
| alter table eval_rules_level import tablespace;              |
| alter table eval_rules_range import tablespace;              |
| alter table eval_rules_range_user import tablespace;         |
| alter table failed_jobs import tablespace;                   |
| alter table files import tablespace;                         |
| alter table files_type import tablespace;                    |
| alter table hospital import tablespace;                      |
| alter table hospital_area import tablespace;                 |
| alter table medical_eval_project import tablespace;          |
| alter table medical_eval_project_auth import tablespace;     |
| alter table medical_eval_rules import tablespace;            |
| alter table medical_eval_rules_branch import tablespace;     |
| alter table medical_eval_rules_level import tablespace;      |
| alter table medical_eval_rules_range import tablespace;      |
| alter table medical_eval_rules_range_user import tablespace; |
| alter table migrations import tablespace;                    |
| alter table notice import tablespace;                        |
| alter table notice_read import tablespace;                   |
+--------------------------------------------------------------+
38 rows in set (0.00 sec)

     执行以上语句后，如果没有问题，就可以看到表数据了。
     当然，如果遇到IBD文件本身有问题，会报下面错误：
     ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `medical`.`admin_logs` : Data structure corruption
   如果遇到这个错误，就需要单独处理这个IBD文件了。

mysql> select * from migrations;
+----+-------------------------------------------------------+-------+
| id | migration                                             | batch |
+----+-------------------------------------------------------+-------+
|  1 | 2014_10_12_000000_create_users_table                  |     1 |
|  2 | 2014_10_12_100000_create_password_resets_table        |     1 |
|  3 | 2019_03_01_000000_create_rules_table                  |     1 |
|  4 | 2019_08_19_000000_create_failed_jobs_table            |     1 |
|  5 | 2019_12_14_000001_create_personal_access_tokens_table |     1 |
|  6 | 2022_08_04_232818_create_admin_roles_table            |     1 |
|  7 | 2022_08_04_232950_create_admin_permissions_table      |     1 |
|  8 | 2022_08_04_233302_create_admin_log_table              |     1 |
|  9 | 2022_09_20_094843_create_notifications_table          |     2 |
+----+-------------------------------------------------------+-------+
9 rows in set (0.00 sec)