Shulou(Shulou.com)05/31 Report--

In this issue, Xiaobian will bring you about how to solve the problem of misdeletion of physical files in mysql. The article is rich in content and analyzes and narrates from a professional perspective. After reading this article, I hope you can gain something.

It is very important to have a backup of the table structure.

--If you delete the mysql table file (.frm .idb) directly in mysql 5.6, you may have a tragedy and may never use this table name again.

Examples are as follows:

--All under datadir directory

--Directly deleted the physical file of table tracking20160501

rm -rf tracking20160501.*

--deleted frm file and idb file of table tracking20160501

--The table is no longer visible in the database at this time

mysql> show tables;

--View database tables

--But it may be tragic to re-create or delete the table

mysql> create table tracking20160501(id int);

ERROR 1050 (42S01): Table 'tracking20160501' already exists

--The table already exists when it is no longer visible

mysql> drop table tracking20160501;

ERROR 1051 (42S02): Unknown table 'kdnet_analyze.tracking20160501'

--Tragedy, right? It can't be created or deleted.

--Check the current physical file situation

ls tracking20160501.*

tracking20160501.ibd

--The table space file that was deleted before, he created another one himself, probably caused by the create table command just now. Ignore it here.

Reason: Because the physical file of the table is deleted directly, but the information of the table still exists in the mysql information base information_schema or mysql library (where the specific record has not been found), resulting in mysql also thinks that the table exists, so it cannot be created. When deleting the table, because the corresponding physical file cannot be found, it cannot be deleted!! Is this name no longer valid? There are solutions as follows:

Solution:

--Find other tables (preferably the same table structure) The table you are looking for here is called ip_taobao. Copy the.frm(table structure) file of this table first and rename it to the name of the table deleted by mistake.

cp -a ip_taobao.frm tracking20160501.frm

--Here, in order to keep the mysql file owner and group, use the-a parameter

--If there is any strange problem in the following operation, you can restart the database

--Use the discard space command in mysql to discard the table space file of the deleted table

1alter table tracking20160501 discard tablespace;

--Copy the table space file of ip_taobao table and rename it to the table name deleted by mistake.

1cp -a ip_taobao.ibd tracking20160501.ibd --also use-a to keep owner and group

--Use import space command in mysql to import new tablespace files

mysql> alter table tracking20160501 import tablespace; --Import may take longer

Query OK, 0 rows affected, 5 warnings (7 min 36.94 sec)

--It is now possible to query, delete and create back this table normally

mysql> select * from tracking20160501 limit 1

mysql> drop table tracking20160501;

mysql> create table tracking20160501(id int);

The above is how to solve the problem of misdeleting the physical file in mysql shared by everyone. If there is a similar doubt, please refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.