一、需求
- 按业务逻辑删除大量表数据
- 操作不卡库,不能影响正常业务操作
- 操作不能造成 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。
阶段三:顶级网络安全工程师
如果你对网络安全入门感兴趣,那么你需要的话可以点击这里👉网络安全重磅福利:入门&进阶全套282G学习资源包免费分享!
学习资料分享
当然,只给予计划不给予学习资料的行为无异于耍流氓,这里给大家整理了一份【282G】的网络安全工程师从入门到精通的学习资料包,可点击下方二维码链接领取哦。
![](https://i-blog.csdnimg.cn/blog_migrate/c46a18d8f2d4d69a43a97c3c8ec2ad4e.jpeg)