Solution to accidental deletion of mysql table physical files

This method only describes how to save the table name (data is not recovered). If you want to restore the original data, use extundelete to restore the file and put it back.

In addition, it is suitable for storing the full-backup frm and idb files directly in the case of no full-backup at ordinary times.
This method is also suitable for data table migration (only migrating one table) because discard re-import is much faster than dumping and then recovering.
Suggestion: it is very important to back up the table structure at ordinary times.

-- If you directly delete the mysql table file (. frm. idb) in mysql5.6, you may be miserable, and you may no longer need to return this table name.
Example:

-- All operations under the datadir Directory

-- The physical file of tracking20160501 is deleted directly.

Rm-rf tracking20160501 .*
-- Deleted the frm and idb files of the tracking20160501 table.

 
-- The table is invisible to the database.

Mysql> show tables;
-- View database tables

 
-- But if you want to create or delete the table again, it may be a tragedy.

Mysql> create table tracking20160501 (id int );
ERROR 1050 (42S01): Table 'tracking20160501 'already exists
-- The table is invisible but the table already exists.
Mysql> drop table tracking20160501;
ERROR 1051 (42S02): Unknown table 'dnet _ analyze. Tracking20160501'
-- The tragedy cannot be created or deleted ..
-- View the current physical file status

Ls tracking20160501 .*
Tracking20160501.ibd
                         
-- The tablespace file that was previously deleted is created by himself. It may be caused by the create table command.
Cause: The physical file of the table is deleted directly, but the information of the mysql database information_schema or mysql database on the table still exists (which is not found yet) as a result, mysql still thinks that the table exists, so it cannot be deleted because the corresponding physical file is not found during table creation and deletion !! Can this table name be reused? The solution is as follows:
Solution:

-- Find other tables (preferably with the same table structure). The name of the table here is ip_taobao. Copy the. frm (table structure) file of this table and change it to the name of the accidentally deleted table.

Cp-a ip_taobao.frm tracking20160501.frm
                   
-- To keep the owner and group of mysql files, use the-a parameter.
-- If the following operations have some strange issues, restart the database.
-- Use the discard space Command in mysql to discard the tablespace files accidentally deleted from the table.

Alter table tracking20160501 discard tablespace;

-- Copy the tablespace file of the ip_taobao table and change it to the name of the accidentally deleted table.

Cp-a ip_taobao.ibd tracking20160501.ibd -- use-a to keep owners and groups

-- Use the import space Command in mysql to import new tablespace files

Mysql> alter table tracking20160501 import tablespace; -- the import may take a long time.
Query OK, 0 rows affected, 5 warnings (7 min 36.94 sec)

-- Now the table can be queried, deleted, and created.

Mysql> select * from tracking20160501 limit 1
Mysql> drop table tracking20160501;
Mysql> create table tracking20160501 (id int );