How to delete a large table in Mysql

This article is about how to delete the big table in Mysql. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

[solution]

1. Reduce mysql DDL time through hard links and speed up lock release

two。 Delete files in segments through truncate to avoid IO hang

[production case]

For a pair of mysql master / slave, insufficient space is found when the master database writes too much, so it is necessary to urgently clean up the discarded large table, but it cannot affect the application access response:

$ll / u01/mysql/data/test/tmp_large.ibd

-rw-r-- 1 mysql dba 289591525376 Mar 30 2012 tmp_large.ibd

The process of deleting and changing large tables in 270GB is as follows:

# (make grayscale first)

Ln tmp_large.ibd / u01/bak/tmp_tbl.ibd # establish hard links

-rw-r-- 2 mysql dba 289591525376 Mar 30 2012 tmp_large.ibd

Set session sql_log_bin=0

# not counting bin log to save performance and prevent inconsistencies between master and backup

Desc test.tmp_large

Drop table test.tmp_large

Query OK, 0 rows affected (10.46 sec) mysql-uroot-e "start slave;"

Cd / u01Compact bakterScreen-S weixi_drop_table for i in `seq 270-1 1`

Do sleep 2 × truncate-s ${I} G tmp_tbl.ibd;done

Rm-rf tmp_tbl.ibd

[performance comparison]

Once in the middle ctrl-C, you can see the comparison of io before and after truncate, which basically has little impact.

The file size was also updated successfully

[introduction to tools]

Truncate-shrink or extend the size of a file to the specified size

# from the coreutils toolset

Wget ftp.gnu.org/gnu/coreutils/coreutils-8.9.tar.gz

Tar-zxvf coreutils-8.9.tar.gz

Cd coreutils-8.9. / configure

Make

Sudo cp src/truncate / usr/bin/

The above is how to delete the big table in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.