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

This article mainly gives you a brief introduction to the specific methods of deleting the MySQL super-large table. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring you some practical help.

Make a big table first, as shown in the following figure:

You can see that the ibd size of the T2 table is 2.7GB.

1. Create a hard link:

Cd/bdata/data/nowdb2/test

Ln t2.ibdt2.ibd_hdlk

The benefits of creating a hard link are:

Hard links add references to files that can be deleted only if the references to the files on disk are completely gone.

After we establish a hard link to t2.ibd, when we execute drop table T2;, we only delete a file reference to t2.ibd, our t2.ibd_hdlk reference to the physical file still exists, we will not perform OS-level delete operations, there will not be a large number of IO operations. This impact on online mysql has been reduced to very low.

2. Perform the actual deletion

Droptable test.t2

3. Use the coreutils toolset to perform OS-level file deletion

Download address: ftp://alpha.gnu.org/gnu/coreutils/

Tar xf coreutils-8.0.tar.xz

Cdcoreutils-8.0

. / configure

Make & & make install

The cleanup script is as follows:

#! / bin/bash

TRUNCATE=/usr/local/bin/truncate

# delete 100MB every time starting from 2835MB (2.7GB is almost in 2768MB). Finally, if some files are left after the script truncate, you can delete them using rm

Fori in `seq 2768-10010`; do

$TRUNCATE-s ${I} M / bdata/data/nowdb2/test/t2.ibd_hdlk

Sleep 1

Done

After executing the above script, there is still room for 35MB, as shown in the following figure. We can delete this small file directly using rm.

In this way, the T2 table will be deleted completely.

The specific method of deleting MySQL super-large table will first stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.