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

程序员菠萝

于 2024-03-05 10:00:00 发布

阅读量1.5k

点赞数 20

文章标签：安全 mysql android

本文链接：https://blog.csdn.net/xnxqwzy/article/details/136437610

版权

本文详细描述了一个在MySQL数据库中高效删除旧数据并保持低延迟，同时不影响正常业务操作的需求。通过创建关联表、导出数据、文件处理、批量删除以及监控从库延迟等步骤，确保数据清理过程的顺利进行，最终释放空间并分析表结构。

摘要由CSDN通过智能技术生成

一、需求

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

表结构如下：

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 ~/.bashrc

dir="/data/"
ls $dir | while read line
do
    file=${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;" -vvv

    echo ${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))
    do
        sleep 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"
    done

done

# 删除完成后，分析原表，删除关联表
mysql -wxy -p123456 -h10.10.10.1 -P18251 -Dspace -e "
    analyze table space_visit_av;
    drop table del;"

如果只是简单的按时间字段删除历史数据，可以不用导出文件而是直接利用 limit 子句进行删除，例如：

#!/bin/bash
source ~/.bashrc

matched=""

while [ -z "$matched" ]; do
    r=`mysql -wxy -p123456 -h10.10.10.1 -P18251 -e "
         delete from test.user_task_record 
          where TaskDate < 20231201 
          limit 10000;" -vv 2>/dev/null`

    matched=$(echo "$r" | grep "Query OK, 0 rows affected")
	
    s1=`mysql -uwxy -p123456 -h10.10.10.2 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`
    s2=`mysql -uwxy -p123456 -h10.10.10.3 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`
    s3=`mysql -uwxy -p123456 -h10.10.10.4 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`

    while ((s1 > 1)) || ((s2 > 1)) || ((s3 > 1))
    do
        sleep 1;
        s1=`mysql -uwxy -p123456 -h10.10.10.2 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`
        s2=`mysql -uwxy -p123456 -h10.10.10.3 -P18251 -e "show slave status\G" 2>/dev/null | egrep 'Seconds_Behind_Master' | awk -F": " '{print $2}'`
        s3=`mysql -uwxy -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"

    done
done

where 条件中使用的时间字段最好有索引，否则大表执行 delete 会非常慢。联机给大表加索引也可以使用第6步引入的 pt-online-schema-
change 工具，在不卡库、复制不延迟的约束下执行操作。

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 陷入等待
mysql -uwxy -p123456 -h172.18.10.5 -P18251 -e "stop slave;" -vvv 2>/dev/null

sleep 60

# 连接主库执行
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=1s \
--max-lag=10s \
--nocheck-replication-filters \
--critical-load="Threads_running=512" \
--max-load="Threads_running=256" \
D="space",t="space_visit_av" \
--progress=time,30 \
--execute

sleep 60

# 从库执行表分析
mysql -uwxy -p123456 -h10.10.10.2 -P18251 -Dspace -e "analyze table space_visit_av;" -vvv 2>/dev/null
mysql -uwxy -p123456 -h10.10.10.3 -P18251 -Dspace -e "analyze table space_visit_av;" -vvv 2>/dev/null
mysql -uwxy -p123456 -h10.10.10.4 -P18251 -Dspace -e "analyze table space_visit_av;" -vvv 2>/dev/null

# 延迟复制实例开启复制
mysql -uwxy -p123456 -h10.10.10.5 -P18251 -e "start slave;" -vvv 2>/dev/null

接下来我将给各位同学划分一张学习计划表！

学习计划

那么问题又来了，作为萌新小白，我应该先学什么，再学什么？
既然你都问的这么直白了，我就告诉你，零基础应该从什么开始学起：

阶段一：初级网络安全工程师

接下来我将给大家安排一个为期1个月的网络安全初级计划，当你学完后，你基本可以从事一份网络安全相关的工作，比如渗透测试、Web渗透、安全服务、安全分析等岗位；其中，如果你等保模块学的好，还可以从事等保工程师。

综合薪资区间6k~15k

1、网络安全理论知识（2天）
①了解行业相关背景，前景，确定发展方向。
②学习网络安全相关法律法规。
③网络安全运营的概念。
④等保简介、等保规定、流程和规范。（非常重要）

2、渗透测试基础（1周）
①渗透测试的流程、分类、标准
②信息收集技术：主动/被动信息搜集、Nmap工具、Google Hacking
③漏洞扫描、漏洞利用、原理，利用方法、工具（MSF）、绕过IDS和反病毒侦察
④主机攻防演练：MS17-010、MS08-067、MS10-046、MS12-20等

3、操作系统基础（1周）
①Windows系统常见功能和命令
②Kali Linux系统常见功能和命令
③操作系统安全（系统入侵排查/系统加固基础）

4、计算机网络基础（1周）
①计算机网络基础、协议和架构
②网络通信原理、OSI模型、数据转发流程
③常见协议解析（HTTP、TCP/IP、ARP等）
④网络攻击技术与网络安全防御技术
⑤Web漏洞原理与防御：主动/被动攻击、DDOS攻击、CVE漏洞复现

5、数据库基础操作（2天）
①数据库基础
②SQL语言基础
③数据库安全加固

6、Web渗透（1周）
①HTML、CSS和JavaScript简介
②OWASP Top10
③Web漏洞扫描工具
④Web渗透工具：Nmap、BurpSuite、SQLMap、其他（菜刀、漏扫等）

那么，到此为止，已经耗时1个月左右。你已经成功成为了一名“脚本小子”。那么你还想接着往下探索吗？

阶段二：中级or高级网络安全工程师（看自己能力）

综合薪资区间15k~30k

7、脚本编程学习（4周）
在网络安全领域。是否具备编程能力是“脚本小子”和真正网络安全工程师的本质区别。在实际的渗透测试过程中，面对复杂多变的网络环境，当常用工具不能满足实际需求的时候，往往需要对现有工具进行扩展，或者编写符合我们要求的工具、自动化脚本，这个时候就需要具备一定的编程能力。在分秒必争的CTF竞赛中，想要高效地使用自制的脚本工具来实现各种目的，更是需要拥有编程能力。

零基础入门的同学，我建议选择脚本语言Python/PHP/Go/Java中的一种，对常用库进行编程学习
搭建开发环境和选择IDE，PHP环境推荐Wamp和XAMPP，IDE强烈推荐Sublime；

Python编程学习，学习内容包含：语法、正则、文件、网络、多线程等常用库，推荐《Python核心编程》，没必要看完

用Python编写漏洞的exp,然后写一个简单的网络爬虫

PHP基本语法学习并书写一个简单的博客系统

熟悉MVC架构，并试着学习一个PHP框架或者Python框架 (可选)

了解Bootstrap的布局或者CSS。