background:Company production environment server space Alarm, more than 80%, emergency check found is MySQL backup server, there is a record detail of the large table, occupy space around 150G. If he finds a problem, he will be solved. Plan to move this table, re-build an empty new table, continue to record. In the course of the operation because of mistakes, resulting in a lot of more process, now recorded.
Original plan:
1. Back up this table and use Xtrabackup
2, a hard link to the table space (ln command), the purpose is not to delete the file itself, and only delete its pointer, so as to achieve rapid deletion, does not affect the use, the purpose of not occupying resources. (Dependency principle: OS hard LINK When multiple file names point to the same inode, the Inode reference number n>1, deleting any of the filenames simply deletes a pointer and does not delete the data file. When the Inode reference number is n=1, deleting the file needs to erase all data blocks associated with the file, so it will be time consuming.
3. Delete the table using drop and finally delete the table file.
Problem: But the ideal is very plump reality is very backbone, hit me after landing the server, found that the server is not enough space to allow me to hard link. This is embarrassing, no way, can only find another way.
actual operation:
1. Back up data
2, think about how to delete or remove the big table. Finally decided to rename the tablespace (MV), and then operation, but in the MV renaming process, because the error of the IBD file coverage, the problem is that the large file disappeared instantly, but the view server space found that its occupied space is still there. In principle, if the MV causes the file to overwrite, the file equivalent is deleted, the space should also be freed, however not. Considering the file as a MySQL tablespace file, there may be a link problem, after restarting MySQL, find hard disk space released.
3, solve the hard disk space problems, ushered in a new problem. When a table with the same name is established in the original database, the database has been prompted for the existence of the table, which is the problem of directly deleting the table space. Because you want to create a new empty table and continue to use it, you also need to solve this problem. Of course, if the conditions allow, you can see a different structure as the table name is not the same as the new table used. I think now that we have met, I will solve the problem.
4, the Landing Database to drop table test, found that the hint can not find this table t1. Create an empty table space manually, considering that the tablespace does not exist. The first is a table t2 with the same structure, but with a different table name, copy the T2 tablespace and rename the table space to T1. Restart the database, and then into the database, separate the table space, and then drop the table, found to be successful. Then create a new T1 table, or you can succeed. Finally, the T2 table is deleted.
5, although this operation is not a problem, but the original data in the T1 table is lost, so be sure to back up the data before you manipulate the database.
Summary:Although this is a problem of a moderate, special record, the need for friends can look. Of course, there is an unexpected harvest, that is, the deletion is much faster than the drop speed, and does not occupy resources.
Troubleshooting MySQL issues with server space
