问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

如何实现大数据量数据库的历史数据归档

发布网友 发布时间:2022-04-22 06:15

我来回答

5个回答

热心网友 时间:2022-04-08 07:54

使用工具pt-archiver
原理解析
作为MySQL DBA,可以说应该没有不知道pt-archiver了,作为pt-toolkit套件中的重要成员,往往能够轻松帮助DBA解决数据归档的问题。例如线上一个流水表,业务仅仅只需要存放最近3个月的流水数据,三个月前的数据做归档即可,那么pt-archiver就可以轻松帮你完成这件事情,甚至你可以配置成自动任务,无需人工干预。

作为DBA,我们应该知其然更应该知其所以然,这样我们也能够放心地使用pt工具。相信很多DBA都研究过pt-online-schema-change的原理,那么今天我们深入刨一刨pt-archiver的工作原理。
一、原理观察
土人有土办法,我们直接开启general log来观察pt-archiver是如何完成归档的。
命令
pt-archiver --source h=127.0.0.1,u=xucl,p=xuclxucl,P=3306,D=xucl,t=t1 --dest h=127.0.0.1,P=3306,u=xucl,p=xuclxucl,D=xucl_archive,t=t1 --progress 5000 \
--statistics --charset=utf8mb4 --limit=10000 --txn-size 1000 --sleep 30
常用选项
--analyze
指定工具完成数据归档后对表执行'ANALYZE TABLE'操作。指定方法如'--analyze=ds',s代表源端表,d代表目标端表,也可以单独指定。
--ask-pass
命令行提示密码输入,保护密码安全,前提需安装模块perl-TermReadKey。
--buffer
指定缓冲区数据刷新到选项'--file'指定的文件并且在提交时刷新。
只有当事务提交时禁用自动刷新到'--file'指定的文件和刷新文件到磁盘,这意味着文件是被操作系统块进行刷新,因此在事务进行提交之前有一些数据隐式刷新到磁盘。默认是每一行操作后进行文件刷新到磁盘。
--bulk-delete
指定单个语句删除chunk的方式来批量删除行,会隐式执行选项'--commit-each'。
使用单个DELETE语句删除每个chunk对应的表行,通常的做法是通过主键进行逐行的删除,批量删除在速度上会有很大的提升,但如果有复杂的'WHERE'条件就可能会更慢。
--[no]bulk-delete-limit
默认值:yes
指定添加选项'--bulk-delete'和'--limit'到进行归档的语句中。
--bulk-insert
使用LOAD DATA LOCAL INFILE的方法,通过批量插入chunk的方式来插入行(隐式指定选项'--bulk-delete'和'--commit-each')
而不是通过逐行单独插入的方式进行,它比单行执行INSERT语句插入的速度要快。通过隐式创建临时表来存储需要批量插入的行(chunk),而不是直接进行批量插入操作,当临时表中完成每个chunk之后再进行统一数据加载。为了保证数据的安全性,该选项会强制使用选项'--bulk-delete',这样能够有效保证删除是在插入完全成功之后进行的。
--channel
指定当主从复制环境是多源复制时需要进行归档哪个主库的数据,适用于多源复制中多个主库对应一个从库的情形。
--charset,-A
指定连接字符集。
--[no]check-charset
默认值:yes
指定检查确保数据库连接时字符集和表字符集相同。
--[no]check-columns
默认值:yes
指定检查确保选项'--source'指定的源端表和'--dest'指定的目标表具有相同的字段。
不检查字段在表的排序和字段类型,只检查字段是否在源端表和目标表当中都存在,如果有不相同的字段差异,则工具报错退出。如果需要禁用该检查,则指定'--no-check-columns'。
--check-slave-lag
指定主从复制延迟大于选项'--max-lag'指定的值之后暂停归档操作。默认情况下,工具会检查所有的从库,但该选项只作用于指定的从库(通过DSN连接方式)。
--check-interval
默认值:1s
如果同时指定了选项'--check-slave-lag',则该选项指定的时间为工具发现主从复制延迟时暂停的时间。每进行操作100行时进行一次检查。
--columns,-c
指定需要归档的表字段,如有多个则用','(逗号)隔开。
--commit-each
指定按每次获取和归档的行数进行提交,该选项会禁用选项'--txn-size'。
在每次获取表数据并进行归档之后,在获取下一次数据和选项'--sleep'指定的休眠时间之前,进行事务提交和刷新选项'--file'指定的文件,通过选项'--limit'控制事务的大小。
--host,-h
指定连接的数据库IP地址。
--port,-P
指定连接的数据库Port端口。
--user,-u
指定连接的数据库用户。
--password,-p
指定连接的数据库用户密码。
--socket,-S
指定使用SOCKET文件连接。
--databases,-d
指定连接的数据库
--source
指定需要进行归档操作的表,该选项是必须指定的选项,使用DSN方式表示。
--dest
指定要归档到的目标端表,使用DSN方式表示。
如果该选项没有指定的话,则默认与选项'--source'指定源端表为相同表。

--where
指定通过WHERE条件语句指定需要归档的数据,该选项是必须指定的选项。不需要加上'WHERE'关键字,如果确实不需要WHERE条件进行*,则指定'--where 1=1'。
--file
指定表数据需要归档到的文件。使用类似MySQL DATE_FORMAT()格式化命名方式。
文件内容与MySQL中SELECT INTO OUTFILE语句使用相同的格式,文件命名选项如下所示:
%Y:年,4位数(Year, numeric, four digits)
%m:月,2位数(Month, numeric (01..12))
%d:日,2位数(Day of the month, numeric (01..31))
%H:小时(Hour (00..23))
%i:分钟(Minutes, numeric (00..59))
%s:秒(Seconds (00..59))
%D:数据库名(Database name)
%t:表名(Table name)
二、原理解析
根据general log的输出,我们整理出时序表格如下
三、其他说明
咋一看这个过程貌似也没有什么问题,但是,假如在原表扫描出数据,插入到新表的过程中,旧数据发生了变化怎么办?
带着这个疑问,我们进行了源码的跟踪,我们在pt-archiver的6839行打上了断点
然后我分别在几个session窗口做了如下动作
最后pt-archiver输出如下:
# A software update is available:
TIME ELAPSED COUNT
2020-04-08T09:13:21 0 0
2020-04-08T09:13:21 0 1
Started at 2020-04-08T09:13:21, ended at 2020-04-08T09:13:51
Source: A=utf8mb4,D=xucl,P=3306,h=127.0.0.1,p=...,t=t1,u=xucl
Dest: A=utf8mb4,D=xucl_archive,P=3306,h=127.0.0.1,p=...,t=t1,u=xucl
SELECT 1
INSERT 1
DELETE 1
Action Count Time Pct
sleep 1 30.0002 99.89
inserting 1 0.0213 0.07
commit 2 0.0080 0.03
select 2 0.0017 0.01
deleting 1 0.0005 0.00
other 0 0.0008 0.00
很明显,id=3这条记录并没有进行归档(我们这里是改了条件列,实际生产中可能是更改了其他列,造成归档数据不准确)
那么如何来解决这种情况的发生呢?
显然,数据库在数据库中可以通过加排它锁来防止其他程序修改对应的数据,pt-archiver其实早就已经帮我们考虑到了这样的情况,pt-archiver提供了两种选择
--for-update:Adds the FOR UPDATE modifier to SELECT statements
--share-lock:Adds the LOCK IN SHARE MODE modifier to SELECT statements
四、总结
pt-archiver作为归档工具无疑是MySQL DBA日常运维的大利器之一,在使用过程中在知道如何使用的基础上也能够知晓其原理
归档过程中最好能对归档记录进行加锁操作,以免造成归档数据不准确
在主从环境中,归档过程最好控制速度,以免造成主从延迟
尽量控制好chunk的大小,不要过大,造成大事务

热心网友 时间:2022-04-08 09:12

//打开数据库
con.Open();
//读取数据
OdbcDataReader reader = cmd.ExecuteReader();
//把数据加载到临时表
dt.Load(reader);
//在使用完毕之后,一定要关闭,要不然会出问题
reader.Close();

热心网友 时间:2022-04-08 10:46

处理办法
生产库仅对活跃数据做备份,减少了直接备份8T数据库的IO等资源消耗,非活跃数据,考虑建立和生产库表相同表结构的分区表,历史表可以按照月进行分区,周期性的把非活跃数据导入到历史表,同时历史备份的备份策略调整为完整+差异模式,这样不仅提高了历史数据的查询效率,而且可以避免每有新增数据进来时,导致需要完整备份,增加了备份时间,而且不利于数据的灵活恢复。

热心网友 时间:2022-04-08 12:38

历史数据归档的前提是数据进入了终态,也就是不在改变。在符合自己的业务逻辑的情况下,可以选择每天或者每周或每月(视你数据量和业务逻辑而定),对不再常用的终态数据归入历史表,以保证当前表的数据量大小。

热心网友 时间:2022-04-08 14:46

程序: public class Move { public static void main(String args[]){ System.out.println(-1<<2); System.out.println(-1>>2); System.out.println(-1>>>2); } } 输出答案: -4 -1 1073741823
声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
海尔热水器着火一会就熄火出现E2怎么回事 什么网页游戏人气高 求十大网游游戏排行榜 弹弹堂万圣节活动怎么收集糖果 弹弹堂s勋章怎么获得 ...银行说会协同工商局和税务局去我的公司调查,是真的吗? 高中生必须住校吗 双力神洗衣机(双力神洗衣机怎么拆开清洗) 双力神ⅩQB50一397sN洗衣机如何使用? 怎么样加快宫缩开指 如何促进宫缩提前生产 核酸提取试剂属于体外诊断试剂盒吗 做噩梦见一群鸡在我耳边乱叫,心跳的厉害,然后就被... 亚能生物技术(深圳)有限公司亚能医学检验实验室怎... 核酸提取试剂盒归哪一类医疗器戒 红茶茶叶袋长黄色的毛了,茶叶还能喝嘛? 和对象分手后又舍不得该怎么复合? 涛思数据什么时候上市 和女朋友分手两天了 她现在有新对象了我是不是该离... 我要做茶叶包装袋,要怎么测量茶叶袋的规格咧? 核酸提取试剂盒剖析 计算社会学是什么? 对象提出分手有挽回的办法吗? 工业大数据有什么特征? 分手后对象不取关我的那几个社交软件? 图片 茶叶袋里藏“粉”,究竟什么情况 如何通过抓取教育大数据来深化课堂教学改革 QB怎么送人啊 和对象分手挽留但是不严肃是什么心理 有没有一种可以设计茶叶包装袋的软件 QQ上的QB怎样赠送人 八马茶叶大红袍礼品盒里面有一根塑料棒,究竟用来... 国内有哪些生物企业主做磁珠法核酸提取试剂盒,自... 我梦见鸡是怎么回事 核酸提取试剂属于体外诊断试剂吗?属于医疗器械几类... 茶叶可以直接放在铁罐中吗 iphone5进水后打开提示链接itunes 梦见妖怪咬鸡,鸡乱叫好不 核酸提取试剂盒对应的PCR反应体系 在亚能生物有限公司工作对身体有危害吗? 针刺棉与无纺布区别 晚上做梦喂鸡,鸡叫了,还啄了自己头,还让我抓它,让... 视觉人工智能做什么的? 连续几次梦见鸡,怕的吓的叫醒有什么预示 亚能生物技术(深圳)有限公司怎样?在里面做研发助... 职场中的MT是什么意思? 电雕版的价格 几种病毒核酸提取试剂盒的比较 韩国娱乐节目中提到的MT什么意思 FDA怎么管理核酸提取试剂的 美国犹他大学计算机视觉人工智能方向前景怎么样