mysql 删除大表

mysql> show table status like 'log_collect_ip_793774858'\G;
*************************** 1. row ***************************
           Name: log_collect_ip_793774858
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
Avg_row_length: 0
    Data_length: 11909742592
Max_data_length: 0
   Index_length: 451952640
      Data_free: 217867878400
Auto_increment: 1799217963
    Create_time: 2013-07-11 14:26:22
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

# ln log_collect_ip_793774858.ibd log_collect_ip_793774858.ibd.hklk

 

这是一个日志收集表，4天 200多G 大小，硬盘顶不住，开发的说程序已经drop 不掉，我试了一下，半小时无反应：

 

# ls -l *858*
-rw-rw---- 1 mysql mysql         8870 Jul 11 14:26 log_collect_ip_793774858.frm
-rw-rw---- 2 mysql mysql 221362782208 Aug 15 10:55 log_collect_ip_793774858.ibd
-rw-rw---- 2 mysql mysql 221362782208 Aug 15 10:55 log_collect_ip_793774858.ibd.hklk

 

做硬链接之后drop ，还是没有反应：

 

mysql> drop table log_collect_ip_793774858;
^CCtrl-C -- sending "KILL QUERY 2477" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql>

 

rename 表后再删除：

 

mysql> rename table log_collect_ip_793774858 to log_collect_ip_793774858_tmp;
Query OK, 0 rows affected (0.06 sec)

#ls -l *858*
-rw-rw---- 1 mysql mysql         8870 Aug 15 11:21 log_collect_ip_793774858.frm
-rw-rw---- 1 mysql mysql     20971520 Aug 15 11:22 log_collect_ip_793774858.ibd
-rw-rw---- 2 mysql mysql 221362782208 Aug 15 11:03 log_collect_ip_793774858.ibd.hklk
-rw-rw---- 1 mysql mysql         8870 Jul 11 14:26 log_collect_ip_793774858_tmp.frm
-rw-rw---- 2 mysql mysql 221362782208 Aug 15 11:03 log_collect_ip_793774858_tmp.ibd


# rm -rf log_collect_ip_793774858.ibd.hklk

mysql> set session sql_log_bin=0;
 Query OK, 0 rows affected (0.00 sec)


mysql> drop table log_collect_ip_793774858_tmp;
Query OK, 0 rows affected (0.67 sec)