mysql数据库被破坏,只剩下ibd文件时如何恢复
发布网友
发布时间:2022-04-22 00:15
我来回答
共1个回答
热心网友
时间:2023-07-01 12:28
mysql> CREATE DATABASE sakila;
mysql> USE sakila;
mysql> CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE {库名}.{表名} DISCARD TABLESPACE;
例:
mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;
cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/
ALTER TABLE {库名}.{表名} IMPORT TABLESPACE;SHOW WARNINGS;
例:
mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS;
Query OK, 0 rows affected, 1 warning (0.15 sec)
Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
Error opening './sakila/actor.cfg', will attempt to import
without schema verification
mysql> SELECT COUNT(*) FROM sakila.actor;
+----------+
| count(*) |
+----------+
| 200 |
+----------+