mysql日志公有云testcharsetuuidsourcestatistics
摘要：一险绘、引言 最近由于業(yè)務(wù)需求纱意，需要將公有云RDS（業(yè)務(wù)庫(kù)）的大表數(shù)據(jù)歸檔至私有云MySQL（歷史庫(kù)），以縮減公有云RDS的體積和成本武契。 那么問題來了啥酱，數(shù)據(jù)歸檔的方式有n種合是，選擇哪種呢？經(jīng)過一番折騰飒赃，發(fā)現(xiàn)使用percona的pt-archiver就可以輕松并優(yōu)雅地對(duì)MySQL進(jìn)行數(shù)據(jù)歸檔利花。
一、引言
最近由于業(yè)務(wù)需求载佳，需要將公有云RDS（業(yè)務(wù)庫(kù)）的大表數(shù)據(jù)歸檔至私有云MySQL（歷史庫(kù)）炒事，以縮減公有云RDS的體積和成本。
那么問題來了蔫慧，數(shù)據(jù)歸檔的方式有n種挠乳，選擇哪種呢？經(jīng)過一番折騰，發(fā)現(xiàn)使用percona的pt-archiver就可以輕松并優(yōu)雅地對(duì)MySQL進(jìn)行數(shù)據(jù)歸檔睡扬。
待我娓娓道來~
1.1 pt-archive是啥
屬于大名鼎鼎的percona工具集的一員盟蚣，是歸檔MySQL大表數(shù)據(jù)的最佳輕量級(jí)工具之一。
注意卖怜，相當(dāng)輕屎开，相當(dāng)方便簡(jiǎn)單。
1.2 pt-archive能干啥
清理線上過期數(shù)據(jù)马靠；
導(dǎo)出線上數(shù)據(jù)奄抽，到線下數(shù)據(jù)作處理；
清理過期數(shù)據(jù)甩鳄，并把數(shù)據(jù)歸檔到本地歸檔表中逞度，或者遠(yuǎn)端歸檔服務(wù)器。
二妙啃、基本信息
2.1 MySQL環(huán)境
2.2 pt-archiver信息
2.3 歸檔表信息
注意：pt-archiver操作的表必須有主鍵
三档泽、模擬場(chǎng)景
3.1 場(chǎng)景1-1：全表歸檔，不刪除原表數(shù)據(jù)彬祖，非批量插入
pt-archiver \--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \--charset=UTF8--where'1=1'--progress10000--limit=10000--txn-size10000--statistics--no-delete
3.2 場(chǎng)景1-2：全表歸檔茁瘦，不刪除原表數(shù)據(jù)，批量插入
pt-archiver \--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \--charset=UTF8--where'1=1'--progress10000--limit=10000--txn-size10000--bulk-insert--bulk-delete--statistics--no-delete
3.3 場(chǎng)景2-1：全表歸檔储笑，刪除原表數(shù)據(jù)甜熔，非批量插入，非批量刪除
pt-archiver \--source h=10.73.129.187,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \--dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \--charset=UTF8--where'1=1'--progress10000--limit=10000--txn-size10000--statistics--purge
3.4 場(chǎng)景2-2：全表歸檔突倍，刪除原表數(shù)據(jù)腔稀，批量插入，批量刪除
pt-archiver \ --source h=10.73.129.187,P=3306,u=backup_user,p='xxx',,D=test123,t=c1 \ --dest h=10.73.129.188,P=3306,u=backup_user,p='xxx',D=test123,t=c1 \ --charset=UTF8 --where '1=1' --progress 10000 --limit=10000 --txn-size 10000 --bulk-insert --bulk-delete --statistics --purge
四羽历、小結(jié)
4.1 性能對(duì)比
通過下表可以看出焊虏，批量操作和非批量操作的性能差距非常明顯，批量操作花費(fèi)時(shí)間為非批量操作的十分之一左右秕磷。
場(chǎng)景2-1：全表歸檔诵闭，刪除原表數(shù)據(jù)，非批量插入澎嚣，非批量刪除4.2 general log分析
從日志看起來疏尿，源庫(kù)的查詢和目標(biāo)庫(kù)的插入有先后順序
從日志看起來，目標(biāo)庫(kù)的插入和源庫(kù)的刪除易桃，并無先后順序褥琐。在特定條件下，萬一目標(biāo)庫(kù)插入失敗晤郑，源庫(kù)刪除成功敌呈，咋搞贸宏？感覺這里并不十分嚴(yán)謹(jǐn)
刪除采用DELETE FROM TABLE WHERE ... ，每次刪除一行數(shù)據(jù)
插入采用INSERT INTO TABLE VALUES('...')磕洪，每次插入一行數(shù)據(jù)
源庫(kù)general log：
set autocommit=0
批量查詢（對(duì)應(yīng)參數(shù)limit）
SELECT/*!40001 SQL_NO_CACHE */`uuid`FORCEINDEX(`PRIMARY`)WHERE(1=1)AND((`uuid`>='266431'))ORDERBY`uuid`LIMIT10000
3. 逐行刪除
DELETEFROM`test123`.`c1`WHERE(`uuid`='000002f0d9374c56ac456d76a68219b4')
4. COMMIT(對(duì)應(yīng)參數(shù)--txn-size吭练，操作數(shù)量達(dá)到--txn-size，則commit)
目標(biāo)庫(kù)general log：
set autocommit=0
逐行插入
INSERTINTO`test123`.`c1`(`uuid`)VALUES('0436dcf30350428c88e3ae6045649659')
3. COMMIT(對(duì)應(yīng)參數(shù)--txn-size褐鸥，操作數(shù)量達(dá)到--txn-size线脚，則commit)
場(chǎng)景2-2：全表歸檔，刪除原表數(shù)據(jù)叫榕，批量插入浑侥，批量刪除
從日志看起來，源庫(kù)的批量查詢和目標(biāo)庫(kù)的批量插入有先后順序
從日志看起來晰绎，目標(biāo)庫(kù)的批量插入和源庫(kù)的批量刪除寓落，并無先后順序。
批量刪除采用DELETE FROM TABLE WHERE ... LIMIT 10000
批量插入采用LOAD DATA LOCAL INFILE 'file' INTO TABLE ...
源庫(kù)：
set autocommit=0
批量查詢（對(duì)應(yīng)limit參數(shù)）
SELECT/*!40001 SQL_NO_CACHE */`uuid`FORCEINDEX(`PRIMARY`)WHERE(1=1)AND((`uuid`>='266431'))ORDERBY`uuid`LIMIT10000
3. 批量刪除
DELETE FROM `test123`.`c1` WHERE (((`uuid` >= '266432'))) AND (((`uuid` <= '273938'))) AND (1=1) LIMIT 10000
4. COMMIT(對(duì)應(yīng)參數(shù)--txn-size荞下，操作數(shù)量達(dá)到--txn-size伶选，則commit)
目標(biāo)庫(kù)：
set autocommit=0
批量插入
LOADDATALOCALINFILE'/tmp/vkKXnc1VVApt-archiver'INTOTABLE`test123`.`c1`CHARACTER SETUTF8(`uuid`)
3. COMMIT(對(duì)應(yīng)參數(shù)--txn-size，操作數(shù)量達(dá)到--txn-size尖昏，則commit)
五仰税、附錄
常用參數(shù)
原文發(fā)布時(shí)間為：2017-11-28
本文作者：藍(lán)劍鋒@知數(shù)堂
本文來自云棲社區(qū)合作伙伴“老葉茶館”，了解相關(guān)信息可以關(guān)注“老葉茶館”微信公眾號(hào)
如果您發(fā)現(xiàn)本社區(qū)中有涉嫌抄襲的內(nèi)容抽诉，歡迎發(fā)送郵件至：yqgroup@service.aliyun.com 進(jìn)行舉報(bào)陨簇，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)迹淌，本社區(qū)將立刻刪除涉嫌侵權(quán)內(nèi)容河绽。
