We can do drop table but it takes long time
mysql> DROP table erp;
We should enable innodb file per table in my.cnf
first
innodb_file_per_table = 1
The reason that make delete table slow is because we have large .ibd file
ls -l /data/mysql/mytest/
-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm
-rw-r----- 1 mysql mysql 2356792000512 8 18 05:21 erp.ibd
1.Create a hard link to ibd file
ln /data/mysql/mytest/erp.ibd /data/mysql/mytest/erp.ibd.hdlk
ls -l /data/mysql/mytest/
-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm
-rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd
-rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd.hdlk
2.Now we can drop table
mysql> drop table erp;
Query OK, 0 rows affected (0.99 sec)
3.Remove the real file erp.ibd.hdlk
on OS
We could directly do rm
on it but it cost high disk I/O
alternatively, we could use TRUNCATE
#!/bin/bash
TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `;
do
sleep 2
$TRUNCATE -s ${i}G /data/mysql/mytest/erp.ibd.hdlk
done
rm -rf /data/mysql/mytest/erp.ibd.hdlk ;
0