Shulou(Shulou.com)06/01 Report--

Background: the company's production environment server space alarm, more than 80%, emergency inspection found that it is a mysql backup server, there is a large table recording details, occupying about 150G of space. If you find a problem, you have to solve it. Plan to remove this table, create a new empty table, and continue to record. In the process of operation because of mistakes, resulting in a lot of spare process, now recorded.

The original plan:

1. Back up the table and use xtrabackup

2, the tablespace hard link (ln command), the purpose is not to delete the file itself, but only delete its pointer, so as to achieve fast deletion, does not affect the use, does not occupy resources. (dependency principle: OS HARD LINK when multiple file names point to the same INODE at the same time, the number of references to this INODE is N > 1. Deleting any one of the file names only deletes a pointer, not the data file. When the reference number of INODE is Number1, deleting a file needs to erase all data blocks related to the file, so it will be time-consuming.

3. Use drop to delete the table, and finally delete the table file.

Problem: however, the ideal is very full and the reality is very backbone. After I logged in to the server, I found that the remaining space on the server was not enough for me to make hard links. This is awkward. I have no choice but to find another way.

Actual operation:

1. Back up the data

2. Think about how to delete or remove the big watch. Finally, it was decided to rename the tablespace (mv) and then operate, but during the mv renaming process, the ibd file was overwritten by an error, resulting in the instant disappearance of this large file, but looking at the server space found that the size of its space still existed. According to reason, if mv causes the file to be overwritten, the file is deleted and the space should be freed, but not. Considering that the file is a mysql tablespace file, there may be a link problem, after restarting mysql, it is found that the hard disk space is freed.

3. The problem of hard disk space is solved and new problems are ushered in. When a table with the same name is created in the original database, the database always prompts that the table already exists, which is the problem left by directly deleting the tablespace. Because you want to create a new empty table and continue to use it, you also need to solve this problem. Of course, if conditions permit, you can see another new table with the same structure and a different name. I think now that we have encountered it, we should solve the problem.

4. Log in to the database to test the drop table and find that the table T1 can not be found. Considering that the tablespace does not exist, manually create an empty tablespace. The first is to create a table T2 with the same structure but a different name, copy the table space of T2 and rename the table space to T1. Restart the database, and then enter the database, first separate the tablespace, and then drop the table, and find that it can be successful. Then create a new T1 table, which can also be successful. Finally, delete the T2 table.

Although there is no problem with this operation, the original data in the T1 table will be lost, so be sure to back up the data before operating the database.

Summary: although this is a medium-sized problem, special record, friends in need can take a look. Of course, there is also a windfall, that is, this deletion is much faster than drop, and does not consume resources.

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.