Tips for deleting DROP from large tables

In daily work, we often encounter the migration of large historical tables from the main database to the backup machine to free up the main database space. If you directly drop table, it may cause database jitter, increase in the number of connections and other problems, thus affecting the business.

Then with a little trick, you can easily and smoothly delete the history table from the main library.

1. Create a hard link, and the "spoof" MySQL has been deleted when the drop table table is in progress.

Ln test.ibd test.ibd.hdlk

2. Do not rm test.ibd.hdlk directly at this time, which will cause the disk IO speed to rise and the MySQL performance jitter.

Here we write a script that cycles 1G at a time and hibernates for 2 seconds until it is deleted.

1) move test.ibd.hdlk to the / data/bak/ directory first

Mv test.ibd.hdlk / data/bak/

2) execute the following script:

#! / bin/bashTRUNCATE=/usr/bin/truncatefor I in `Get100-10` # decrease 1G at a time starting from 100g, and finally make the file 0do sleep 2 echo "$TRUNCATE-s ${I} G / data/bak/test.ibd.hdlk" $TRUNCATE-s ${I} G / data/bak/test.ibd.hdlkdone

Note: first ll-h test.ibd.hdlk to see how many gigabytes are in the file, and then enter seq, the above example is 100g.