安全快速地删除 MySQL 大表数据并释放空间

2023-12-11 05:45

本文主要是介绍安全快速地删除 MySQL 大表数据并释放空间,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、需求

  1. 按业务逻辑删除大量表数据
  2. 操作不卡库,不能影响正常业务操作
  3. 操作不能造成 60 秒以上的复制延迟
  4. 满足以上条件的前提下,尽快删除数据并释放所占空间

        表结构如下:

create table `space_visit_av` (`userid` bigint(20) not null comment '用户id',`avid` bigint(20) not null comment '作品id',`touserid` bigint(20) not null comment '被访问用户d',`createtime` timestamp not null default current_timestamp comment '创建时间',`updatetime` timestamp not null default current_timestamp on update current_timestamp comment '收藏时间',primary key (`userid`,`avid`),key `index_1` (`touserid`,`updatetime`) using btree,key `index_2` (`avid`,`updatetime`) using btree,key `idx_updatetime` (`updatetime`)
) engine=innodb default charset=utf8 comment='用户访问作品表';

        表中现有约 50 亿条数据,只保留 2023-10-01 以后的数据(约占总量的 1/10),其它删除。

二、实现

1. 主库按原表创建删除关联表,只保留原表的主键

mysql -uwxy -p123456 -h10.10.10.1 -P18251 -Dspace -e "
create table del (userid bigint(20) not null comment '用户id',avid bigint(20) not null comment '作品id',primary key (userid,avid));"

2. 导出需要删除数据的主键到文件

-- 在从库执行查询
select userid, avid into outfile '/data/del.txt' from space_visit_av where updatetime < '2023-10-01';

3. 将文件分割成 10 万行一个的小文件

cd /data
split -l 100000 -d -a 6 del.txt# 删除原文件
rm del.txt

4. 遍历文件执行删除

# 后台执行
nohup ~/del.sh > ~/del.log 2>&1 &

        del.sh 脚本文件内容如下:

#!/bin/bash
source ~/.bashrcdir="/data/"
ls $dir | while read line
dofile=${dir}${line}# 表关联删除数据mysql -wxy -p123456 -h10.10.10.1 -P18251 -Dspace --local-infile -e "delete from del;load data local infile '$file' into table del;analyze table del; analyze table space_visit_av;delete t1 from space_visit_av t1, del t2 where t1.userid=t2.userid and t1.avid=t2.avid;" -vvvecho ${line}# 取得所有从库的延迟秒数s1=`mysql -wxy -p123456 -h10.10.10.2 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`s2=`mysql -wxy -p123456 -h10.10.10.3 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`s3=`mysql -wxy -p123456 -h10.10.10.4 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`# 只有所有从库延迟小于等于 1 秒时继续执行删除,否则等待从库追赶while ((s1 > 1)) || ((s2 > 1)) || ((s3 > 1))dosleep 1;s1=`mysql -wxy -p123456 -h10.10.10.2 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`s2=`mysql -wxy -p123456 -h10.10.10.3 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`s3=`mysql -wxy -p123456 -h10.10.10.4 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`echo "$s1 $s2 $s3"donedone# 删除完成后,分析原表,删除关联表
mysql -wxy -p123456 -h10.10.10.1 -P18251 -Dspace -e "analyze table space_visit_av;drop table del;"

5. 所有从库分析表

mysql -wxy -p123456 -h10.10.10.2 -P18251 -Dspace -e "analyze table space_visit_av;"
mysql -wxy -p123456 -h10.10.10.3 -P18251 -Dspace -e "analyze table space_visit_av;"
mysql -wxy -p123456 -h10.10.10.4 -P18251 -Dspace -e "analyze table space_visit_av;"

6. 使用 pt-online-schema-change 释放删除数据所占空间

# 后台执行
nohup ~/shrink.sh > ~/shrink.log 2>&1 &

        shrink.sh 脚本文件内容如下:

#!/bin/bash
source ~/.bashrc# 连接主库执行
pt-online-schema-change \
--host="10.10.10.1" \
--port=18251 \
--user="wxy" \
--password="123456" \
--charset="utf8mb4" \
--chunk-size=10000 \
--recursion-method="processlist" \
--check-interval=5s \
--max-lag=30s \
--nocheck-replication-filters \
--critical-load="Threads_running=512" \
--max-load="Threads_running=256" \
D="space",t="space_visit_av" \
--progress=time,30 \
--execute

这篇关于安全快速地删除 MySQL 大表数据并释放空间的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/479622

相关文章

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中,有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I

Ubuntu如何分配​​未使用的空间

《Ubuntu如何分配​​未使用的空间》Ubuntu磁盘空间不足,实际未分配空间8.2G因LVM卷组名称格式差异(双破折号误写)导致无法扩展,确认正确卷组名后,使用lvextend和resize2fs... 目录1:原因2:操作3:报错5:解决问题:确认卷组名称​6:再次操作7:验证扩展是否成功8:问题已解

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构

Python使用vllm处理多模态数据的预处理技巧

《Python使用vllm处理多模态数据的预处理技巧》本文深入探讨了在Python环境下使用vLLM处理多模态数据的预处理技巧,我们将从基础概念出发,详细讲解文本、图像、音频等多模态数据的预处理方法,... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型(范围、哈希、列表、键值),主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区(Range Partitioning1、新建分区表:2、分

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后,有时候会需要自动创建分区。比如,一些表数据量非常大,有些数据是热点数据,按照日期分区MululbU

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一:桌面图标进入方法二:运行窗口进入检查版本号对照表php方法三:查找文件路径方法四:检查 S

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》:本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法: 示例:2.TRUNCATE语句✅ 基本语