为了账号安全，请及时绑定邮箱和手机立即绑定
首页 手记 MySQL删除数据表前三个月的数据

MySQL删除数据表前三个月的数据

标签：
MySQL 运维

testrecord数据库中存储着玩家行为记录，每三个月需要清理掉。

删除数据前的准备

查看testrecord数据库的大小：

MySQL [(none)]> USE information_schema;
Database changed
MySQL [information_schema]> SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024/1024),2),'GB')
    -> as DATA FROM TABLES WHERE
    -> table_schema='testrecord';
+---------+
| DATA    |
+---------+
| 56.70GB |
+---------+
1 row in set (0.00 sec)

查看testrecord数据库各数据表大小：

MySQL [information_schema]> SELECT
    -> table_schema as '数据库',
    -> table_name as '表名',
    -> table_rows as '记录数',
    -> TRUNCATE(data_length/1024/1024/1024, 2) as '数据容量(GB)',
    -> TRUNCATE(index_length/1024/1024/1024, 2) as '索引容量(GB)'
    -> FROM information_schema.tables
    -> WHERE table_schema='testrecord'
    -> ORDER BY data_length DESC, index_length DESC;
+----------- +-------------------+-----------+----------------+----------------+
| 数据库     | 表名               | 记录数    | 数据容量(GB)    | 索引容量(GB)   |
+----------- +-------------------+----------------------------+----------------+
| testrecord | playeritem        |  71206129 |           9.71 |           4.17 |
| testrecord | dropcord          | 102342337 |           7.92 |           6.22 |
| testrecord | ipcord            |  22256444 |           4.97 |           3.93 |
| testrecord | store             |  16583137 |           2.58 |           1.20 |
| testrecord | teip              |   6887136 |           2.43 |           1.28 |

以上得知：
需要清理数据的前三个数据表分别是：playeritem、dropcord、ipcord

查看创建数据表的SQL语句：

MySQL [(none)]> USE testrecord;
Database changed
MySQL [testrecord]> SHOW CREATE TABLE playeritem \G
*************************** 1. row ***************************
       Table: playeritem
Create Table: CREATE TABLE `playeritem` (
#——————————————————字段结构略————————————————————————#
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
/*!50100 PARTITION BY RANGE (`cordTime`)
(PARTITION `playeritem_2021-05` VALUES LESS THAN (1622476800) ENGINE = InnoDB,
 PARTITION `playeritem_2021-06` VALUES LESS THAN (1625068800) ENGINE = InnoDB,
 PARTITION `playeritem_2021-07` VALUES LESS THAN (1627747200) ENGINE = InnoDB,
 PARTITION `playeritem_2021-08` VALUES LESS THAN (1630425600) ENGINE = InnoDB,
 PARTITION `playeritem_2021-09` VALUES LESS THAN (1633017600) ENGINE = InnoDB,
 PARTITION `playeritem_2021-10` VALUES LESS THAN (1635696000) ENGINE = InnoDB,
 PARTITION `playeritem_2021-11` VALUES LESS THAN (1638288000) ENGINE = InnoDB,
 PARTITION `playeritem_2021-12` VALUES LESS THAN (1640966400) ENGINE = InnoDB,
 PARTITION playeritem_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

MySQL [testrecord]> SHOW CREATE TABLE dropcord \G
*************************** 1. row ***************************
       Table: dropcord
Create Table: CREATE TABLE `dropcord` (
#——————————————————字段结构略————————————————————————#
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(cordtime))
(PARTITION p202105 VALUES LESS THAN (738307) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN (738337) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN (738368) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (738399) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (738429) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (738460) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (738490) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (738521) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

MySQL [testrecord]> SHOW CREATE TABLE ipcord \G
*************************** 1. row ***************************
       Table: ipcord
Create Table: CREATE TABLE `ipcord` (
#——————————————————字段结构略————————————————————————#
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (to_days(cordtime))
(PARTITION p202105 VALUES LESS THAN (738307) ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN (738337) ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN (738368) ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN (738399) ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN (738429) ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN (738460) ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN (738490) ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN (738521) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

以上得知：
数据表采用了RNAGE分区，分别以月份天数创建了分区

查看数据表分区情况

MySQL [testrecord]> SELECT PARTITION_NAME part,
    -> PARTITION_EXPRESSION expr,
    -> PARTITION_DESCRIPTION descr,
    -> TABLE_ROWS
    -> FROM information_schema.PARTITIONS
    -> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'playeritem';
+--------------------+------------+------------+------------+
| part               | expr       | descr      | TABLE_ROWS |
+--------------------+------------+------------+------------+
| playeritem_2021-05 | `cordTime` | 1622476800 |   96549342 |
| playeritem_2021-06 | `cordTime` | 1625068800 |   26259322 |
| playeritem_2021-07 | `cordTime` | 1627747200 |   44541353 |
| playeritem_2021-08 | `cordTime` | 1630425600 |   27246100 |
| playeritem_2021-09 | `cordTime` | 1633017600 |   29226451 |
| playeritem_2021-10 | `cordTime` | 1635696000 |    6806093 |
| playeritem_2021-11 | `cordTime` | 1638288000 |          0 |
| playeritem_2021-12 | `cordTime` | 1640966400 |          0 |
| playeritem_max     | `cordTime` | MAXVALUE   |          0 |
+------------------------+-------------------+------------+------------+
9 rows in set (0.00 sec)

MySQL [testrecord]> SELECT PARTITION_NAME part,
    -> PARTITION_EXPRESSION expr,
    -> PARTITION_DESCRIPTION descr,
    -> TABLE_ROWS
    -> FROM information_schema.PARTITIONS
    -> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'dropcord';
+---------+-------------------+----------+------------+
| part    | expr              | descr    | TABLE_ROWS |
+---------+-------------------+----------+------------+
| p202105 | to_days(cordtime) | 738307   |   60519201 |
| p202106 | to_days(cordtime) | 738337   |   30759304 |
| p202107 | to_days(cordtime) | 738368   |   55525655 |
| p202108 | to_days(cordtime) | 738399   |   51800453 |
| p202109 | to_days(cordtime) | 738429   |   58897747 |
| p202110 | to_days(cordtime) | 738460   |   14470693 |
| p202111 | to_days(cordtime) | 738490   |          0 |
| p202112 | to_days(cordtime) | 738521   |          0 |
| pmax    | to_days(cordtime) | MAXVALUE |          0 |
+---------+---------------------+----------+------------+
9 rows in set (0.00 sec)

MySQL [testrecord]> SELECT PARTITION_NAME part,
    -> PARTITION_EXPRESSION expr,
    -> PARTITION_DESCRIPTION descr,
    -> TABLE_ROWS
    -> FROM information_schema.PARTITIONS
    -> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'ipcord';
+---------+---------------------+----------+------------+
| part    | expr                | descr    | TABLE_ROWS |
+---------+---------------------+----------+------------+
| p202105 | to_days(cordtime) | 738307   |   70512202 |
| p202106 | to_days(cordtime) | 738337   |   50759503 |
| p202107 | to_days(cordtime) | 738368   |   65529654 |
| p202108 | to_days(cordtime) | 738399   |   11692729 |
| p202109 | to_days(cordtime) | 738429   |   17989204 |
| p202110 | to_days(cordtime) | 738460   |    5010364 |
| p202111 | to_days(cordtime) | 738490   |          0 |
| p202112 | to_days(cordtime) | 738521   |          0 |
| pmax    | to_days(cordtime) | MAXVALUE |          0 |
+---------+---------------------+----------+------------+
9 rows in set (0.00 sec)

删除数据
依次删除5、6、7月的数据

ALTER TABLE playeritem DROP PARTITION playeritem_2021-05;
ALTER TABLE dropcord DROP PARTITION p202105;
ALTER TABLE ipcord DROP PARTITION p202105;

删除数据后的检查
依次检查分区表情况

MySQL [testrecord]> SELECT PARTITION_NAME part,
    -> PARTITION_EXPRESSION expr,
    -> PARTITION_DESCRIPTION descr,
    -> TABLE_ROWS
    -> FROM information_schema.PARTITIONS
    -> WHERE TABLE_SCHEMA = schema() AND TABLE_NAME = 'dropcord';
+---------+---------------------+----------+------------+
| part    | expr                | descr    | TABLE_ROWS |
+---------+---------------------+----------+------------+
| p202108 | to_days(recordtime) | 738399   |   51800453 |
| p202109 | to_days(recordtime) | 738429   |   58897747 |
| p202110 | to_days(recordtime) | 738460   |   14496732 |
| p202111 | to_days(recordtime) | 738490   |          0 |
| p202112 | to_days(recordtime) | 738521   |          0 |
| pmax    | to_days(recordtime) | MAXVALUE |          0 |
+---------+---------------------+----------+------------+
6 rows in set (0.00 sec)

并再次检查数据表大小确认存储空间是否已释放。

将以上操作写入脚本
clear_table_partition.sh

#!/bin/sh

source /root/.bash_profile

for db in $(mysql_cmd -Ne "SHOW DATABASES;" | grep testrecord)
do

    # look the table partitions
    l_t_cmd="SELECT PARTITION_NAME part, \
             PARTITION_EXPRESSION expr, \
             PARTITION_DESCRIPTION descr, \
             TABLE_ROWS \
             FROM information_schema.PARTITIONS \
             WHERE \
             TABLE_SCHEMA = schema() \
             AND TABLE_NAME = 'ipcord';"
    echo $l_t_cmd
    echo $db
    mysql_cmd -e "USE $db; $l_t_cmd;"

    # drop the table partitions
    d_t_cmd="ALTER TABLE playeritem DROP PARTITION playeritem_2021-05;
             ALTER TABLE playeritem DROP PARTITION playeritem_2021-06;
             ALTER TABLE playeritem DROP PARTITION playeritem_2021-07;
             ALTER TABLE dropcord DROP PARTITION p202105;
             ALTER TABLE dropcord DROP PARTITION p202106;
             ALTER TABLE dropcord DROP PARTITION p202107;
             ALTER TABLE ipcord DROP PARTITION p202105;
             ALTER TABLE ipcord DROP PARTITION p202106;
             ALTER TABLE ipcord DROP PARTITION p202107;"
    echo $d_t_cmd
    echo $db
    mysql_cmd -e "USE $db; $d_t_cmd;"

done

总结
1、source命令可在当前shell环境中读取并执行来自文件里的命令

2、环境变量文件.bash_profile对当前登录用户有效，在文件内设置登录MySQL别名，简化代码：
在这里插入图片描述

3、mysql命令行工具选项 -Ne 的解释：
-e, --execute=name 表示执行MySQL语句并退出
-N, --skip-column-names 选项表示不要在结果中写入列名
有无 -N选项的区别如下：

[root@mysql ~]# mysql -uroot -p -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@mysql ~]# mysql -uroot -p -Ne "SHOW DATABASES;"
+--------------------+
| information_schema |
|              mysql |
| performance_schema |
|                sys |
+--------------------+

4、$()是shell的命令替换字符，shell会先执行括号里面的命令，并返回命令执行的标准输出结果

5、变量名l_t_cmd 定义的是查看表分区的语句，由此得知数据表采用了表分区

6、echo ${l_t_cmd}、echo ${db}，使用echo将变量值输出，使脚本在执行过程中具有可读性

7、变量名d_t_cmd定义了删除表分区的语句，即使用ALTER TABLE table_name DROP PARTITION part_name语句删除表分区

8、INFORMATION_SCHEMA是每个 MySQL 实例中的数据库，存储有关 MySQL 服务器维护的所有其他数据库的详细信息

9、前面查看见建表语句时，descr列的数字代表的是时间戳，时间戳的值如何得来？
在这里插入图片描述
通过以下语句获得，
TO_DAY(date)函数返回日期date距离0000年1月1日的天数。
UNIX_TIMESTAMP(date)函数返回UNIX时间戳，自’1970-01-01 00:00:00’的到当前时间的秒数差

MariaDB [(none)]> SELECT TO_DAYS('2021-07-01 00:00:00');
+--------------------------------+
| TO_DAYS('2021-07-01 00:00:00') |
+--------------------------------+
|                         738337 |
+--------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT UNIX_TIMESTAMP('2021-07-01 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2021-07-01 00:00:00') |
+---------------------------------------+
|                            1625068800 |
+---------------------------------------+
1 row in set (0.00 sec)
点击查看更多内容
发表于 2021.10.07 17:13， 共 2483 人浏览

本文原创发布于慕课网 ，转载请注明出处，谢谢合作

举报
为 TA 点赞

若觉得本文不错，就分享一下吧！

评论

作者其他优质文章

正在加载中

fisherlife

Python工程师
手记
篇
粉丝
0
获赞与收藏
0

关注作者，订阅最新文章

阅读免费教程

  • 推荐
  • 评论
  • 收藏
  • 共同学习，写下你的评论
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消