Mysql table physical files have been mistakenly deleted solution _mysql

Objective

1, this method only describes how to save the table name (data does not recover) if you want to restore the original data directly with Extundelete to restore the file back to the

2, and is applicable to usually not fully prepared in the case if there is a full preparation of the FRM and IDB documents can be put back on it

3, this method is also suitable for data table migration (migrate only one table) because the speed of discard and import is much faster than the first dump and then recover.

Suggestion: usually back up the table structure is very important

--If you delete the MySQL table file (. frm. IDB) directly in mysql5.6 you might be in a tragedy, you might not be able to use the table name again.

Examples are as follows

--All in the DataDir directory operation

--Deletes the physical file of the table tracking20160501 directly

RM-RF tracking20160501.*       
--deleted the FRM and IDB files of table tracking20160501

--The table is not visible in the database at this time

Mysql> show tables;                            
--View database tables

--but it might be a tragedy if you want to create the table again or delete the table.

Mysql> CREATE TABLE tracking20160501 (id int);
ERROR 1050 (42S01): Table ' tracking20160501 ' already exists            
--it is clear that the table is already visible and the table already exists
mysql> drop table Tracking20 160501;
ERROR 1051 (42S02): Unknown table ' kdnet_analyze.tracking20160501 '-    
-tragedy is not to be created or deleted.

--look at the current physical file situation

LS tracking20160501.*
tracking20160501.ibd               
 --Previously deleted tablespace files He created a new one out of it. It's probably just the CREATE TABLE command.

reason: because the physical file of the table was deleted directly but the information of the MySQL database information_schema or MySQL library on the table still exists (specifically where it has not been found), causing MySQL to also believe that the table exists so it cannot be created Delete the table because it can not find the corresponding physical file so also delete it!! Can't this table name be used anymore?

There are solutions for the following

--Find other tables (preferably the same as the table structure) here is the table called Ip_taobao to copy the table first. frm (table structure) file renamed as mistakenly deleted table name

Cp-a ip_taobao.frm tracking20160501.frm            
--here to keep the MySQL file owner and the owning group so use the-a parameter

--if there are any wonderful problems with the following operations, you can restart the database
--Use the Discard Space command in MySQL to discard the tablespace file

ALTER TABLE tracking20160501 discard tablespace;

--Duplicate the table space of the Ip_taobao table renamed the name of the table that was mistakenly deleted

Cp-a ip_taobao.ibd tracking20160501.ibd            --same use-A to keep the owner and the owning group

--Using the Import Space command in MySQL to import a new tablespace file

mysql> ALTER TABLE tracking20160501 import tablespace;       --The import time may be a little bit
Query OK, 0 rows affected, 5 warnings (7 min 36.94 sec)

--You can now query, delete, and create back to this table correctly.

Mysql> select * from tracking20160501 limit 1
mysql> drop table tracking20160501;
Mysql> CREATE TABLE tracking20160501 (id int);

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.