Solution to the accidental deletion of mysql table physical files

Solution to the accidental deletion of mysql table physical files

Preface

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

2. It is applicable to the scenario where full backup is not available at ordinary times. If there is full backup, the full backup frm and idb files can be put back.

3. 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 -- displays that the table already exists but does not exist. mysql> drop table tracking20160501; ERROR 1051 (42S02): Unknown table 'dnet _ analyze. tracking20160501 ..

-- View the current physical File status

Ls tracking20160501. * tracking20160501.ibd -- The tablespace file that was previously deleted was created by himself. It may be caused by the create table command.

Cause:Because the physical file of the table is deleted directly, the information of the table still exists in the mysql database information_schema or mysql database (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:

-- 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 time may take a long time to 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 1mysql> drop table tracking20160501;mysql> create table tracking20160501(id int);

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.