[Mysql] mysql使用备份的.frm文件恢复表结构


本文总阅读量次

0、例如备份的表为test_order,则备份出来的.frm文件为test_order.frm

1、在只知道表名的情况下，随意创建一个表名为test_order的表：

1
create table test_order (id1 int(2));

替换test_order.frm文件，替换完成之后重启mysql数据库,查看表信息

1
2
3
4
root@db 11:29:  [test]> desc test_order;
ERROR 1146 (42S02): Table 'test.test_order' doesn't exist
root@db 11:29:  [test]> show create table test_order;
ERROR 1146 (42S02): Table 'test.test_order' doesn't exist

报错提示表不存在，之后查看error.log：

1
2
3
4
2018-11-01T11:29:10.359514Z 2 [Warning] InnoDB: Table test/test_order contains 1 user defined columns in InnoDB, but 25 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2018-11-01T11:29:10.359631Z 2 [Warning] InnoDB: Cannot open table test/test_order from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2018-11-01T11:29:25.514189Z 2 [Note] InnoDB: Table `test`.`test_order` is corrupted. Please drop the table and recreate it
2018-11-01T11:29:25.514313Z 2 [Warning] InnoDB: Cannot open table test/test_order from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

报错提示手动创建的表只有1列，但是mysql中记录的表有25列

2、删除test_order表，并创建一个25列的test_order表：

1
2
3
root@db 11:40:  [test]> drop table test_order;
Query OK, 0 rows affected (0.06 sec)
root@db 11:40:  [test]> create table test_order (id1 int(2),id2 int(2),id3 int(2),id4 int(2),id5 int(2),id6 int(2),id7 int(2),id8 int(2),id9 int(2),id10 int(2),id11 int(2),id12 int(2),id13 int(2),id14 int(2),id15 int(2),id16 int(2),id17 int(2),id18 int(2),id19 int(2),id20 int(2),id21 int(2),id22 int(2),id23 int(2),id24 int(2),id25  int(2));

替换test_order.frm文件，替换完成之后重启mysql数据库,查看表信息

1
2
3
4
5
6
7
8
root@db 11:41:  [test]> desc test_order;
ERROR 2013 (HY000): Lost connection to MySQL server during query
root@db 11:42:  [test]> show create table test_order;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query

查看error.log：

1
2
3
4
5
6
7
8
9
10
11
2018-11-01T11:42:25.566095Z 2 [ERROR] Build InnoDB index translation table for Table ./test/test_order failed
2018-11-01T11:42:25.566445Z 2 [ERROR] Table ./test/test_order has no primary key in InnoDB data dictionary, but has one in MySQL! If you created the table with a MySQL version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MySQL internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.
2018-11-01T11:42:25.566519Z 2 [Warning] Table ./test/test_order key_used_on_scan is 0 even though there is no primary key inside InnoDB.
2018-11-01T11:42:25.566576Z 2 [ERROR] InnoDB could not find key no 0 with name PRIMARY from dict cache for table test/test_order
11:42:25 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

报错提示test_order表没有主键

3、删除test_order表，并创建一个25列的带有主键的test_order表：

1
2
3
root@db 11:45:  [test]> drop table test_order;
Query OK, 0 rows affected (0.06 sec)
root@db 11:45:  [test]> create table test_order (id1 int(2) primary key,id2 int(2),id3 int(2),id4 int(2),id5 int(2),id6 int(2),id7 int(2),id8 int(2),id9 int(2),id10 int(2),id11 int(2),id12 int(2),id13 int(2),id14 int(2),id15 int(2),id16 int(2),id17 int(2),id18 int(2),id19 int(2),id20 int(2),id21 int(2),id22 int(2),id23 int(2),id24 int(2),id25  int(2));

替换test_order.frm文件，替换完成之后重启mysql数据库,查看表信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
root@db 11:46:  [test]> desc test_order;
+-----------------+----------------+------+-----+---------+----------------+
| Field           | Type           | Null | Key | Default | Extra          |
+-----------------+----------------+------+-----+---------+----------------+
| id              | bigint(20)     | NO   | PRI | NULL    | auto_increment |
| order_no        | varchar(24)    | NO   | UNI | NULL    |                |
| broker_id       | varchar(24)    | NO   | MUL | NULL    |                |
| broker_uid      | bigint(20)     | NO   |     | NULL    |                |
| plat_id         | varchar(24)    | NO   |     | NULL    |                |
| price_asset     | varchar(8)     | NO   |     | NULL    |                |
| test_type      | varchar(16)    | NO   |     | NULL    |                |
| order_type      | varchar(16)    | NO   |     | NULL    |                |
| price           | decimal(32,20) | NO   |     | NULL    |                |
| number          | decimal(32,20) | NO   |     | NULL    |                |
| test_asset     | varchar(8)     | NO   | MUL | NULL    |                |
| testd_number   | decimal(32,20) | NO   |     | NULL    |                |
| over_number     | decimal(32,20) | NO   |     | NULL    |                |
| testd_money    | decimal(32,20) | NO   |     | NULL    |                |
| fee_asset       | varchar(8)     | NO   |     | NULL    |                |
| fee             | decimal(32,20) | NO   |     | NULL    |                |
| broker_fee      | decimal(32,20) | NO   |     | NULL    |                |
| cloud_fee       | decimal(32,20) | NO   |     | NULL    |                |
| client_order_no | varchar(36)    | NO   |     | NULL    |                |
| state           | varchar(16)    | NO   |     | NULL    |                |
| send_state      | varchar(16)    | NO   |     | NULL    |                |
| error_code      | varchar(24)    | YES  |     | NULL    |                |
| error_msg       | varchar(64)    | YES  |     | NULL    |                |
| create_time     | datetime       | NO   |     | NULL    |                |
| update_time     | datetime       | NO   |     | NULL    |                |
+-----------------+----------------+------+-----+---------+----------------+
25 rows in set (0.01 sec)

root@db 11:46:  [test]> show create table test_order\G
*************************** 1. row ***************************
       Table: test_order
Create Table: CREATE TABLE `test_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(24) NOT NULL,
  `broker_id` varchar(24) NOT NULL,
  `broker_uid` bigint(20) NOT NULL,
  `plat_id` varchar(24) NOT NULL,
  `price_asset` varchar(8) NOT NULL,
  `test_type` varchar(16) NOT NULL,
  `order_type` varchar(16) NOT NULL,
  `price` decimal(32,20) NOT NULL,
  `number` decimal(32,20) NOT NULL,
  `test_asset` varchar(8) NOT NULL,
  `testd_number` decimal(32,20) NOT NULL,
  `over_number` decimal(32,20) NOT NULL,
  `testd_money` decimal(32,20) NOT NULL,
  `fee_asset` varchar(8) NOT NULL,
  `fee` decimal(32,20) NOT NULL,
  `broker_fee` decimal(32,20) NOT NULL,
  `cloud_fee` decimal(32,20) NOT NULL,
  `client_order_no` varchar(36) NOT NULL,
  `state` varchar(16) NOT NULL,
  `send_state` varchar(16) NOT NULL,
  `error_code` varchar(24) DEFAULT NULL,
  `error_msg` varchar(64) DEFAULT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `test_order_order_no_uindex` (`order_no`),
  UNIQUE KEY `test_order_broker_id_client_order_no_uindex` (`broker_id`,`client_order_no`),
  KEY `multi_price_test_id_uid_state_index` (`test_asset`,`price_asset`,`broker_uid`,`broker_id`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

查看error.log有无其他报错：

1
2
3
4
5
6
2018-11-01T11:45:09.272752Z 2 [ERROR] Build InnoDB index translation table for Table ./test/test_order failed
2018-11-01T11:45:09.272904Z 2 [ERROR] InnoDB: MySQL and InnoDB data dictionaries are out of sync. Unable to find the AUTOINC column id in the InnoDB table `test`.`test_order`. We set the next AUTOINC column value to 0, in effect disabling the AUTOINC next value generation.
2018-11-01T11:45:09.272935Z 2 [Note] InnoDB: You can either set the next AUTOINC value explicitly using ALTER TABLE or fix the data dictionary by recreating the table.
2018-11-01T11:45:09.272959Z 2 [ERROR] InnoDB: Table test/test_order contains 1 indexes inside InnoDB, which is different from the number of indexes 4 defined in MySQL
2018-11-01T11:45:09.272976Z 2 [ERROR] InnoDB could not find key no 1 with name test_order_order_no_uindex from dict cache for table test/test_order
2018-11-01T11:45:09.272988Z 2 [ERROR] Table test/test_order contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

log日志依旧有报错，提示innodb只有一个索引，但mysql下存在4个索引，但是已经通过show create table test_order获取到了建表的语句，因此可以通过上面的建表语句，重新创建test_order表了。

mysql
目录
  1. 1. 0、例如备份的表为test_order,则备份出来的.frm文件为test_order.frm
  2. 2. 1、在只知道表名的情况下，随意创建一个表名为test_order的表：
  3. 3. 2、删除test_order表，并创建一个25列的test_order表：
  4. 4. 3、删除test_order表，并创建一个25列的带有主键的test_order表：

Proudly powered by Hexo and Theme by Lap
本站访客数人次
© 2020 zeven0707's blog