重庆思庄Oracle、Redhat认证学习论坛

标题: mysql5.7只拷贝某个数据库目录下文件来还原该数据库 [打印本页]

作者: 郑全 时间: 2023-7-27 21:05
标题: 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)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)