MySQL快速清理大表操作

缺乏、安全感 2023-02-11 07:11 7阅读 0赞

# 概述 #

MySQL如何快速清理大表操作，比如Zabbix监控历史表未定时清理，导致磁盘空间不足，下面讲下Zabbix历史表的清理操作：

<table> 
 <thead> 
  <tr> 
   <th align="left">表名</th> 
   <th align="left">磁盘空间</th> 
   <th align="left">表注释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">history</td> 
   <td align="left">198G</td> 
   <td align="left">历史表信息</td> 
  </tr> 
  <tr> 
   <td align="left">history_uint</td> 
   <td align="left">439G</td> 
   <td align="left">历史表信息（整型）</td> 
  </tr> 
  <tr> 
   <td align="left">history_log</td> 
   <td align="left">2.6</td> 
   <td align="left">历史表信息（长整型）</td> 
  </tr> 
  <tr> 
   <td align="left">history_str</td> 
   <td align="left">8.6G</td> 
   <td align="left">历史表信息（字符串型）</td> 
  </tr> 
  <tr> 
   <td align="left">history_text</td> 
   <td align="left">11G</td> 
   <td align="left">历史表信息（文本型）</td> 
  </tr> 
 </tbody> 
</table>

history\_开头的表都大同一致，唯一不同的是保存的数据类型，history\_unit保存的数据类型是int即整型类型的，history\_str保存的数据类型是str即字符类型的。这个是和采集时设置的数据类型一致的。

因为history表有这么多的类型，那自己写SQL语句等去查询数据时，就需要判断下数据的采集类型，如果查错了表，是没有数据的。SQL查询可以参考以下：

-- 根据 item的id 查询历史明细 值， 一般保留7d， 这个不同的item项保留时间不同
    select itemid, FROM_UNIXTIME(clock,'%Y-%m-%d %H:%i:%s') as date, value , ns from history_uint where itemid=95086 order by clock desc limit 10;
    select itemid, FROM_UNIXTIME(clock,'%Y-%m-%d %H:%i:%s') as date, value , ns from history_log  where itemid=95086 order by clock desc limit 10;
    select itemid, FROM_UNIXTIME(clock,'%Y-%m-%d %H:%i:%s') as date, value , ns from history_str  where itemid=95086 order by clock desc limit 10;
    select itemid, FROM_UNIXTIME(clock,'%Y-%m-%d %H:%i:%s') as date, value , ns from history_text where itemid=95086 order by clock desc limit 10;
    select itemid, FROM_UNIXTIME(clock,'%Y-%m-%d %H:%i:%s') as date, value , ns from history_uint where itemid=95086 order by clock desc limit 10;

这里我们要清理的两张表是 history :198G 和 history\_uint :439G

## 重命名 ##

当历史表非常大时，直接删除数据，会有几个风险问题，一是DELETE语句会产生较多的binlog文件占用较多的磁盘空间，二是会有主从延迟，且删除时间较长， 这里我们使用RENAME事务的方式来实现秒级修改历史表。RENAME语法参考如下：

RENAME TABLE
        tbl_name TO new_tbl_name
        [, tbl_name2 TO new_tbl_name2] ...

重命名表重命名一个或多个表。您必须具有原表的 ALTER 和 DROP 特权，以及新表的创建和插入特权。

-- 重命名 history 表
    zabbix@localhost [(none)] > use zabbix; 
    zabbix@localhost [zabbix]>  CREATE TABLE `history_new` (
      `itemid` bigint(20) unsigned NOT NULL,
      `clock` int(11) NOT NULL DEFAULT '0',
      `value` double(16,4) NOT NULL DEFAULT '0.0000',
      `ns` int(11) NOT NULL DEFAULT '0',
      KEY `history_1` (`itemid`,`clock`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    zabbix@localhost [zabbix]> rename table history to history_old, history_new to history; 
    
    -- 重命名 history_uint 表
    zabbix@localhost [(none)] >  CREATE TABLE `history_uint_new` (
      `itemid` bigint(20) unsigned NOT NULL,
      `clock` int(11) NOT NULL DEFAULT '0',
      `value` bigint(20) unsigned NOT NULL DEFAULT '0',
      `ns` int(11) NOT NULL DEFAULT '0',
      KEY `history_uint_1` (`itemid`,`clock`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
    
    zabbix@localhost [zabbix]> rename table history_uint to history_uint_old, history_uint_new to history_uint;

## 备份表 ##

由于历史表保留了一年多的监控数据，有时候我们可能还需要回溯查看更久的历史监控数据来排查问题，此时如果直接删除就无法追溯了。 这里我们备份下表，备份前确保磁盘空间足够，并压缩备份：

# history 单表备份
    root@localhost:~# mysqldump -uroot -p -h127.0.0.1 zabbix history_old --single-transaction --events | gzip > /data/backup/zabbix.history_old.sql.gz
    
    # history_uint 单表备份
    root@localhost:~# mysqldump -uroot -p -h127.0.0.1 zabbix history_uint_old --single-transaction --events | gzip > /data/backup/zabbix.history_uint_old.sql.gz

由于备份时间较长，为避免中断，可以使用脚本方式在后台执行。 注意确保磁盘空间足够！

## 硬链接 ##

我们知道文件都有文件名与数据，这在 Linux 上被分成两个部分：用户数据 (user data) 与元数据 (metadata)。用户数据，即文件数据块 (data block)，数据块是记录文件真实内容的地方；而元数据则是文件的附加属性，如文件大小、创建时间、所有者等信息。在 Linux 中，元数据中的 inode 号（inode 是文件元数据的一部分但其并不包含文件名，inode 号即索引节点号）才是文件的唯一标识而非文件名。文件名仅是为了方便人们的记忆和使用，系统或程序通过 inode 号寻找正确的文件数据块。图 1.展示了程序通过文件名获取文件内容的过程。  
![在这里插入图片描述][20200522184816398.png]  
在 Linux 系统中查看 inode 号可使用命令 stat 或 ls -i（若是 AIX 系统，则使用命令 istat）

由于硬链接是有着相同 inode 号仅文件名不同的文件，因此硬链接存在以下几点特性：

文件有相同的 inode 及 data block；  
只能对已存在的文件进行创建；  
不能交叉文件系统进行硬链接的创建；  
不能对目录进行创建，只可对文件创建；  
删除一个硬链接文件并不影响其他有相同 inode 号的文件。

供助硬链接的特性，我们可以对历史表创建一个硬链接，然后对表执行DROP操作。

root@localhost:/data/mysql/zabbix/# du -shx * | grep history | grep G 
    2.5G    history.ibd
    2.6G    history_log.ibd
    198G    history_old.ibd
    8.6G    history_str.ibd
    11G     history_text.ibd
    4.6G    history_uint.ibd
    439G    history_uint_old.ibd
    
    # 创建硬链接
    root@localhost:/data/mysql/zabbix/#ln /data/mysql/zabbix/history_old.ibd /data/mysql/zabbix/history_old.ibd.hdlk 
    root@localhost:/data/mysql/zabbix/#ln /data/mysql/zabbix/history_uint_old.ibd /data/mysql/zabbix/history_uint_old.ibd.hdlk 
    
    root@localhost:/data/mysql/zabbix/# ls -lit | grep -E "history_old*.ibd|history_uint_old*.ibd"
    539081974 -rwxr-xr-x 2 mysql mysql 470978396160 May 21 10:48 history_uint_old.ibd
    539081974 -rwxr-xr-x 2 mysql mysql 470978396160 May 21 10:48 history_uint_old.ibd.hdlk
    539230890 -rwxr-xr-x 2 mysql mysql 211581665280 May 21 10:32 history_old.ibd
    539230890 -rwxr-xr-x 2 mysql mysql 211581665280 May 21 10:32 history_old.ibd.hdlk

通过命令我们创建了硬链接，其中history\_uint\_old.ibd和history\_uint\_old.ibd.hdlk的Inode（539081974）号一样， history\_old.ibd和history\_old.ibd.hdlk的Inode（539230890 ）号一样。

如果你的数据库是主从架构，那么同时需要在从库也创建硬链接

## 删除表 ##

创建了硬链接之后，接下来我们就可以使用DROP TABLE操作快速删除大表了，如下：

zabbix@localhost [(none)] > use zabbix; 
    zabbix@localhost [zabbix]> show tables like 'history%'; 
    +-----------------------------+
    | Tables_in_zabbix (history%) |
    +-----------------------------+
    | history                     |
    | history_log                 |
    | history_old                 |
    | history_str                 |
    | history_text                |
    | history_uint                |
    | history_uint_old            |
    +-----------------------------+
    7 rows in set (0.00 sec)
    zabbix@localhost [zabbix]>  drop table zabbix.history_old; 
    Query OK, 0 rows affected (5.37 sec)
    
    zabbix@localhost [zabbix]>  drop table zabbix.history_uint_old; 
    Query OK, 0 rows affected (11.89 sec)
    
    zabbix@localhost [zabbix]>    show tables like 'history%';
    +-----------------------------+
    | Tables_in_zabbix (history%) |
    +-----------------------------+
    | history                     |
    | history_log                 |
    | history_str                 |
    | history_text                |
    | history_uint                |
    +-----------------------------+
    5 rows in set (0.00 sec)

至此，大表已经清理结束，检查下磁盘文件发现原来的那个文件已经删除：

root@localhost:/data/mysql/zabbix/# ls -lit | grep -E "history_old*.ibd|history_uint_old*.ibd"
    539081974 -rwxr-xr-x 1 mysql mysql 470978396160 May 21 10:48 history_uint_old.ibd.hdlk
    539230890 -rwxr-xr-x 1 mysql mysql 211581665280 May 21 10:32 history_old.ibd.hdlk

这里只是把引用删除了，实质上磁盘物理空间并没有释放，需要执行下面的清理操作。

## 文件清理 ##

在生产环境，直接用rm命令来删大文件，会造成磁盘IO开销飙升，CPU负载过高，是会影响其他程序运行的。这里推荐用truncate命令来删，truncate 命令在 coreutils 工具集中。truncate命令对磁盘IO，CPU负载几乎无影响。

root@localhost:~# whereis truncate
    truncate: /usr/bin/truncate /usr/share/man/man2/truncate.2.gz /usr/share/man/man1/truncate.1.gz
    # 如果系统没有安装，通过以下命令来安装
     root@localhost:~# apt-get install coreutils

这里我们先写好脚本，通过执行脚本来自动清理操作，写个循环，每10G执行清理，中间间隔2秒。脚本如下：

root@localhost:/opt# vim truncate_limit.sh
    #!/bin/bash 
    for i in `seq 198 -10 10 `; 
    do
     sleep 2
     echo "/usr/bin/truncate -s ${i}G /data/mysql/zabbix/history_old.ibd.hdlk"
     /usr/bin/truncate -s ${i}M /data/mysql/zabbix/history_old.ibd.hdlk  
     echo -n "当前大小：" ;  du -shx /data/mysql/zabbix/history_old.ibd.hdlk 
     echo '==========================================================='
    done
    rm -f /data/mysql/zabbix/history_old.ibd.hdlk 
    echo "文件已删除!"
    ls -lt /data/mysql/zabbix/history_old.ibd.hdlk

从198G开始，每次缩减10G，停2秒，继续，直到文件只剩10G，最后使用rm命令删除剩余的部分，脚本执行过程日志显示如下

root@localhost:/opt# bash truncate_limit.sh 
    /usr/bin/truncate -s 198G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：194G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 188G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：189G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 178G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：179G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 168G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：169G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 158G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：159G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 148G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：149G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 138G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：139G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 128G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：129G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 118G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：119G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 108G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：109G  /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 98G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：99G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 88G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：89G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 78G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：79G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 68G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：69G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 58G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：58G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 48G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：48G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 38G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：38G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 28G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：28G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    /usr/bin/truncate -s 18G /data/mysql/zabbix/history_old.ibd.hdlk
    当前大小：18G   /data/mysql/zabbix/history_old.ibd.hdlk
    ===========================================================
    文件已删除!
    ls: cannot access '/data/mysql/zabbix/history_old.ibd.hdlk': No such file or directory

通过truncate命令完成了对物理文件的删除操作，删除期间观察系统IO和CPU，可根据资源情况调整每次清理的大小和时间间隔，这样就不会影响服务器的性能。 按照上面的方法，对剩下的history\_old.ibd.hdlk 表执行相同的操作即可。

[20200522184816398.png]: https://img-blog.csdnimg.cn/20200522184816398.png