Drop tables is such a common content of a DBA’s daily work. Drop a small table( such as less than 100 Mega Bytes ), we can just do DROP operations during low load. But in most situations, the tables are more than 100MB, even reach to 100GB(called large table). Then if you drop this kind of tables( including during the low load ), the whole system may be freezing till the DROP finished, or even been unavailable because of the disk I/O overloaded.
Currently, we use separate tablespaces for different tables (innodb_file_per_table=ON). And MySQL server was often built on the LINUX. That means the data of a table is actually stored in a separate data file. So if we can remove the data file progressively and don’t let the MySQL server knows, then the DROP LARGE TABLE operation will be done gracefully.
Then how can we remove a data file and don’t let MySQL knows?
(1) LINUX allows us to build a hard link to the file, that makes us could remove the data file after DROP TABLE on the MySQL server, and this is invisible to database.
(2) We can then remove the data file step by step by using the TRUNCATE command of LINUX( http://man7.org/linux/man-pages/man1/truncate.1.html ).
How to drop large tables gracefully:
1. Build a hard link to the data file
Build a hard link named testbigfile.ibd.rm, the table name is testbigfile, then we can find the data file on the database
datadir( mysql> show variables like 'datadir'; )
ln testbigfile.ibd testbigfile.ibd.rm
We can use
ls -l statement to showing the details:
It shows that the link has been used by two files, testbigfile.idb and testbigfile.idb.rm.
So when we remove a file, it’s not actually removing the file, just the link, that makes the DROP TABLE statement can be finished in a very short time. And if we remove the remaining file, it’s the real data removing.
2. DROP TABLE
After building a hard link to the data file, we can DROP the table on MySQL server:
> drop table testbigfile;
Then only one file named testbigfile.idb.rm left.
3. Remove the actual file
truncate command allows to remove file step by step, for example:
truncate -s -1GB testbigfile.ibd.rm
This means truncate testbigfile.ibd.rm for 1GB per time. Then we can run this command multiple times during low load until the file is empty, and use rm command to remove the file at last.