mysql单表快速恢复_MySQL单表恢复

如果误删除的表只有10M,而备份有500G,该如何快速恢复误删除表?

drop table city;

create table city like city_bak;

alter table city discard tablespace;

cp /backup/full/world/city.ibd  /application/mysql/data/world/

chown -R mysql.mysql  /application/mysql/data/world/city.ibd

alter table city import  tablespace;

从mysqldump 全备中获取库和表的备份

1、获得表结构

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql