pt-archiver 歸檔資料
pt-archiver 引數說明
pt-archiver是Percona-Toolkit工具集中的一個元件,是一個主要用於對MySQL表資料進行歸檔和清除工具。它可以將資料歸檔到另一張表或者是一個檔案中。pt-archiver在清除表資料的過程中並不會影響OLTP事務的查詢效能。對於資料的歸檔,它可以歸檔到另一臺伺服器上的另一張表,也可歸檔到一個檔案中,檔案可以用LOAD DATA INFILE進行資料裝載,這個功能其實就類似是表歷史資料的增量刪除。
基本說明
pt-archiver [OPTIONS] --source DSN --where WHERE
常用選項(OPTIONS)
--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)
例如:--file '/var/log/archive/%Y-%m-%d-%D.%t'
'
--output-format
指定選項'--file'檔案內容輸出的格式。
預設不指定該選項是以製表符進行欄位的分隔符,如果指定該選項,則使用','(逗號)作為欄位分隔符,使用'"'(雙引號)將欄位括起。用法示例:'--output-format=dump'。
--for-update
指定為每次歸檔執行的SELECT語句新增FOR UPDATE子句。
--share-lock
指定為每次歸檔執行的SELECT語句新增LOCK IN SHARE MODE子句。
--header
指定在檔案中第一行寫入欄位名稱作為標題。
--ignore
指定為INSERT語句新增IGNORE選項。
--limit
預設值:1
指定每條語句獲取表和歸檔表的行數。
--local
指定不將OPTIMIZE和ANALYZE語句寫入binlog。
--max-lag
預設值:1s
指定允許主從複製延遲時長的最大值,單位秒。如果在每次獲取行資料之後主從延遲超過指定的值,則歸檔操作將暫停執行,暫停休眠時間為選項'--check-interval'指定的值。待休眠時間結束之後再次檢查主從延遲時長,檢查方法是透過從庫查詢的'Seconds_Behind_Master'值來確定。如果主從複製延遲一直大於該引數指定值或者從庫停止複製,則操作將一直等待直到從庫重新啟動並且延遲小於該引數指定值。
--no-delete
指定不刪除已被歸檔的表資料。
--optimize
指定工具完成資料歸檔後對錶執行'OPTIMIZE TABLE'操作。指定方法如'--analyze=ds',s代表源端表,d代表目標端表,也可以單獨指定。
--primary-key-only
指定只歸檔主鍵欄位,是選項'--columns=主鍵'的簡寫。
如果工具歸檔的操作是進行DELETE清除時最有效,因為只需讀取主鍵一個欄位而無需讀取行所有欄位。
--progress
指定每多少行列印進度資訊,列印當前時間,已用時間以及多少行進行歸檔。
--purge
指定執行的清除操作而不是歸檔操作。允許忽略選項'--dest'和'--file'進行操作,如果只是清除操作可以結合選項'--primary-key-only'會更高效。
--quiet,-q
指定工具靜默執行,不輸出任何的執行資訊。
--replace
指定寫入選項'--dest'指定目標端表時改寫INSERT語句為REPLACE語句。
--retries
預設值:1
指定歸檔操作遇到死鎖或超時的重試次數。當重試次數超過該選項指定的值時,工具將報錯退出。
--run-time
指定工具歸檔操作在退出之前需要執行的時間。允許的時間字尾名為s=秒,m=分,h=小時,d=天,如果沒指定,預設為s。
--[no]safe-auto-increment
預設值:yes
指定不使用自增列(AUTO_INCREMENT)最大值對應的行進行歸檔。
該選項在進行歸檔清除時會額外新增一條WHERE子句以防止工具刪除單列升序欄位具有的具有AUTO_INCREMENT屬性最大值的資料行,為了在資料庫重啟之後還能使用到AUTO_INCREMENT對應的值,但這會引起無法歸檔或清除欄位對應最大值的行。
--set-vars
預設:
wait_timeout=10000
innodb_lock_wait_timeout=1
lock_wait_timeout=60
工具歸檔時指定引數值,如有多個用','(逗號)分隔。如'--set-vars=wait_timeout=5000'。
--skip-foreign-key-checks
指定使用語句SET FOREIGN_KEY_CHECKS = 0禁用外來鍵檢查。
--sleep
指定工具在透過SELECT語句獲取歸檔資料需要休眠的時間,預設值是不進行休眠。在休眠之前事務並不會提交,並且選項'--file'指定的檔案不會被重新整理。如果指定選項'--commit-each',則在休眠之前會進行事務提交和檔案重新整理。
--statistics
指定工具收集並列印操作的時間統計資訊。
統計資訊示例如下:
'
Started at 2008-07-18T07:18:53, ended at 2008-07-18T07:18:53
Source: D=db,t=table
SELECT 4
INSERT 4
DELETE 4
Action Count Time Pct
commit 10 0.1079 88.27
select 5 0.0047 3.87
deleting 4 0.0028 2.29
inserting 4 0.0028 2.28
other 0 0.0040 3.29
'
--txn-size
預設:1
指定每個事務處理的行數。如果是0則禁用事務功能。
--version
顯示工具的版本並退出。
--[no]version-check
預設值:yes
檢查Percona Toolkit、MySQL和其他程式的最新版本。
--why-quit
指定工具列印當非因完成歸檔行數退出的原因。
在執行一個自動歸檔任務時該選項與選項'--run-time'一起使用非常方便,這樣可以確定歸檔任務是否在指定的時間內完成。如果同時指定了選項'--statistics',則會列印所有退出的原因。
DSN選項(DSN)
可以使用DSN方式來連線資料庫,DSN選項為key=value方式,在等號的兩側不能有空格出現,並且區分大小寫,多個選項之前以','(逗號)隔開,主要選項如下:
a
歸檔操作是在哪個庫下進行的,相當於USE操作。
A
指定預設字符集。
b
當值為true時,禁止SQL_LOG_BIN,相當於SQL_LOG_BIN = 0。
D
指定包含需要歸檔表的資料庫。
h
指定連線的主機。
u
指定連線的使用者。
p
指定連線需要的密碼。
P
指定連線的埠。
S
指定連線的SOCKET檔案。
t
指定需要歸檔的表。
i
指定需要使用的索引。
選項用法說明
工具至少需指定選項--dest、--file或--purge其中之一;
選項--ignore和--replace是互斥的;
選項--txn-size和--commit-each是互斥的;
選項--share-lock和--for-update是互斥的;
--analyze和--optimize是互斥的。
本次測試是基於employees表以及新建的yoon表
CREATE TABLE `yoon` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`v_int` int(11) DEFAULT NULL,
`v_string` varchar(50) DEFAULT NULL,
`s_string` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80001 DEFAULT CHARSET=utf8;
建立儲存過程i_yoon插入測試資料
delimiter $$
CREATE PROCEDURE i_yoon (IN row_num INT)
BEGIN
DECLARE i INT DEFAULT 0 ;
WHILE i < row_num DO
INSERT INTO yoon (v_int, v_string, s_string)
VALUES
(
floor(1 + rand() * 1000000),
substring(
MD5(RAND()),
1,
floor(1 + rand() * 20)
),
substring(MD5(RAND()), 1, 20)
) ;
SET i = i + 1 ;
END
WHILE ; END$$
delimiter ;
call i_yoon(200000);
在目標端建立測試庫hank,表結構yoon:
CREATE TABLE `yoon` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`v_int` int(11) DEFAULT NULL,
`v_string` varchar(50) DEFAULT NULL,
`s_string` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80001 DEFAULT CHARSET=utf8;
執行歸檔,不刪除源端資料:--source 源端,--dest:目標端,--no-delete:不刪除源端資料
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='admin',D=yoon,t=yoon,A=utf8 --dest h=127.0.0.1,P=3307,u=admin,p='admin',D=hank,t=yoon,A=utf8 --charset=utf8 --where 'id <= 20000' --progress 50 --txn-size=1000 --statistics --no-delete --ask-pass --limit=200 --sleep=1 --dry-run
執行歸檔,刪除源端資料:--source 源端,--dest:目標端
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='admin',D=yoon,t=yoon,A=utf8 --dest h=127.0.0.1,P=3307,u=admin,p='admin',D=hank,t=yoon,A=utf8 --charset=utf8 --where 'id <= 20000' --progress 50 --txn-size=1000 --statistics --ask-pass --limit=200 --sleep=1 --dry-run
表歸檔到表(批次進行)
批次進行歸檔涉及的選項是--limit,批次進行插入涉及的選項為--bulk-insert,指定選項--bulk-insert同時也會指定選項--bulk-delete,如果不刪除已歸檔資料,則需要指定選項--no-delete
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='admin',D=yoon,t=yoon,A=utf8 --dest h=127.0.0.1,P=3308,u=admin,p='admin',D=hank,t=yoon,A=utf8 --charset=utf8 --where "id <= 20000" --progress=50 --txn-size=1000 --limit=50 --statistics --no-delete --bulk-insert --ask-pass --dry-run
表歸檔到檔案
表歸檔到檔案將選項--dest換成--file,並且根據需要新增選項--output-format
pt-archiver --source h=127.0.0.1,P=3306,u=admin,p='admin',D=employees,t=employees,A=utf8 --file='/tmp/yoon_%Y-%m-%d.sql' --charset=utf8 --output-format='dump' --where "id <= 20000" --progress=50 --txn-size=1000 --limit=50 --statistics --no-delete --ask-pass --dry-run
表清除資料:--txn-size 表示200行提交一次失誤,若最後刪除的資料低於200行報錯,若改--txn-size值
如果只是進行表資料清除操作而不做歸檔操作,則可以忽略選項--dest或--file,透過指定選項--purge,可以先使用選項--dry-run列印查詢需要清除資料的執行語句,做好確認之後再執行。
pt-archiver --source h=localhost,P=3306,u=root,p='Asd.123@#',D=yoon,t=yoon --purge --charset=utf8 --where "id <= 400000" --progress=200 --limit=200 --sleep=1 --txn-size=200 --statistics --dry-run
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28939273/viewspace-2668573/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- pt-archiver資料歸檔Hive
- MySQL使用pt-archiver歸檔歷史資料MySqlHive
- 優雅地使用pt-archiver進行資料歸檔Hive
- pt-archiver工具歸檔和恢復資料一例Hive
- pt-archiver結合crontab定時自動歸檔資料Hive
- ORACLE資料庫歸檔改為非歸檔Oracle資料庫
- SQL資料庫怎麼進行資料歸檔和歸檔管理?SQL資料庫
- 清除閃回資料歸檔區資料
- MySQL 中如何歸檔資料MySql
- Oracle閃回資料歸檔Oracle
- 檢視oracle資料庫是否歸檔和修改歸檔模式Oracle資料庫模式
- 資料檔案重建,從歸檔日誌中回退資料
- IOS資料儲存之歸檔/解檔iOS
- 檢視oracle資料庫是否歸檔和修改歸檔模式(轉)Oracle資料庫模式
- 歸檔日誌無法歸檔導致資料庫hang住資料庫
- RAC開啟資料庫歸檔資料庫
- 資料庫啟動歸檔模式資料庫模式
- 開啟資料庫歸檔模式資料庫模式
- Oracle資料庫開啟歸檔Oracle資料庫
- 資料庫歸檔以及flashbak操作資料庫
- 開啟oracle資料庫歸檔Oracle資料庫
- 修改資料庫的歸檔模式資料庫模式
- 更改資料庫為歸檔模式資料庫模式
- 資料儲存(歸檔解檔,沙河儲存)
- UAVStack之檔案資料歸集
- 非歸檔模式恢復資料庫模式資料庫
- oracle資料庫更改歸檔路徑Oracle資料庫
- 將資料庫修改為歸檔模式資料庫模式
- oracle 修改資料庫為歸檔模式Oracle資料庫模式
- 閃回資料歸檔-- Flashback Data ArchiveHive
- Oracle資料庫切換歸檔模式Oracle資料庫模式
- 11g閃回資料歸檔
- 非歸檔資料庫RMAN備份資料庫
- SAP SD客戶主資料歸檔
- 在歸檔下恢復系統資料檔案
- 利用歸檔來做資料檔案的恢復
- RMAN備份資料檔案+控制檔案+歸檔日誌
- Oralce資料庫關閉歸檔日誌並且刪除歸檔日誌資料庫