pt-archiver 歸檔數(shù)據(jù)

pt-archiver 參數(shù)說(shuō)明

pt-archiver是Percona-Toolkit工具集中的一個(gè)組件，是一個(gè)主要用于對(duì)MySQL表數(shù)據(jù)進(jìn)行歸檔和清除工具。它可以將數(shù)據(jù)歸檔到另一張表或者是一個(gè)文件中。pt-archiver在清除表數(shù)據(jù)的過(guò)程中并不會(huì)影響OLTP事務(wù)的查詢(xún)性能。對(duì)于數(shù)據(jù)的歸檔，它可以歸檔到另一臺(tái)服務(wù)器上的另一張表，也可歸檔到一個(gè)文件中，文件可以用LOAD DATA INFILE進(jìn)行數(shù)據(jù)裝載，這個(gè)功能其實(shí)就類(lèi)似是表歷史數(shù)據(jù)的增量刪除。

基本說(shuō)明

pt-archiver [OPTIONS] --source DSN --where WHERE

常用選項(xiàng)(OPTIONS)

--analyze

指定工具完成數(shù)據(jù)歸檔后對(duì)表執(zhí)行'ANALYZE TABLE'操作。指定方法如'--analyze=ds'，s代表源端表，d代表目標(biāo)端表，也可以單獨(dú)指定。

--ask-pass

命令行提示密碼輸入，保護(hù)密碼安全，前提需安裝模塊perl-TermReadKey。

--buffer

指定緩沖區(qū)數(shù)據(jù)刷新到選項(xiàng)'--file'指定的文件并且在提交時(shí)刷新。

只有當(dāng)事務(wù)提交時(shí)禁用自動(dòng)刷新到'--file'指定的文件和刷新文件到磁盤(pán)，這意味著文件是被操作系統(tǒng)塊進(jìn)行刷新，因此在事務(wù)進(jìn)行提交之前有一些數(shù)據(jù)隱式刷新到磁盤(pán)。默認(rèn)是每一行操作后進(jìn)行文件刷新到磁盤(pán)。

--bulk-delete

指定單個(gè)語(yǔ)句刪除chunk的方式來(lái)批量刪除行，會(huì)隱式執(zhí)行選項(xiàng)'--commit-each'。

使用單個(gè)DELETE語(yǔ)句刪除每個(gè)chunk對(duì)應(yīng)的表行，通常的做法是通過(guò)主鍵進(jìn)行逐行的刪除，批量刪除在速度上會(huì)有很大的提升，但如果有復(fù)雜的'WHERE'條件就可能會(huì)更慢。

--[no]bulk-delete-limit

默認(rèn)值：yes

指定添加選項(xiàng)'--bulk-delete'和'--limit'到進(jìn)行歸檔的語(yǔ)句中。

--bulk-insert

使用LOAD DATA LOCAL INFILE的方法，通過(guò)批量插入chunk的方式來(lái)插入行(隱式指定選項(xiàng)'--bulk-delete'和'--commit-each')

而不是通過(guò)逐行單獨(dú)插入的方式進(jìn)行，它比單行執(zhí)行INSERT語(yǔ)句插入的速度要快。通過(guò)隱式創(chuàng)建臨時(shí)表來(lái)存儲(chǔ)需要批量插入的行(chunk)，而不是直接進(jìn)行批量插入操作，當(dāng)臨時(shí)表中完成每個(gè)chunk之后再進(jìn)行統(tǒng)一數(shù)據(jù)加載。為了保證數(shù)據(jù)的安全性，該選項(xiàng)會(huì)強(qiáng)制使用選項(xiàng)'--bulk-delete'，這樣能夠有效保證刪除是在插入完全成功之后進(jìn)行的。

--channel

指定當(dāng)主從復(fù)制環(huán)境是多源復(fù)制時(shí)需要進(jìn)行歸檔哪個(gè)主庫(kù)的數(shù)據(jù)，適用于多源復(fù)制中多個(gè)主庫(kù)對(duì)應(yīng)一個(gè)從庫(kù)的情形。

--charset，-A

指定連接字符集。

--[no]check-charset

默認(rèn)值：yes

指定檢查確保數(shù)據(jù)庫(kù)連接時(shí)字符集和表字符集相同。

--[no]check-columns

默認(rèn)值：yes

指定檢查確保選項(xiàng)'--source'指定的源端表和'--dest'指定的目標(biāo)表具有相同的字段。

不檢查字段在表的排序和字段類(lèi)型，只檢查字段是否在源端表和目標(biāo)表當(dāng)中都存在，如果有不相同的字段差異，則工具報(bào)錯(cuò)退出。如果需要禁用該檢查，則指定'--no-check-columns'。

--check-slave-lag

指定主從復(fù)制延遲大于選項(xiàng)'--max-lag'指定的值之后暫停歸檔操作。默認(rèn)情況下，工具會(huì)檢查所有的從庫(kù)，但該選項(xiàng)只作用于指定的從庫(kù)(通過(guò)DSN連接方式)。

--check-interval

默認(rèn)值：1s

如果同時(shí)指定了選項(xiàng)'--check-slave-lag'，則該選項(xiàng)指定的時(shí)間為工具發(fā)現(xiàn)主從復(fù)制延遲時(shí)暫停的時(shí)間。每進(jìn)行操作100行時(shí)進(jìn)行一次檢查。

--columns，-c

指定需要?dú)w檔的表字段，如有多個(gè)則用','(逗號(hào))隔開(kāi)。

--commit-each

指定按每次獲取和歸檔的行數(shù)進(jìn)行提交，該選項(xiàng)會(huì)禁用選項(xiàng)'--txn-size'。

在每次獲取表數(shù)據(jù)并進(jìn)行歸檔之后，在獲取下一次數(shù)據(jù)和選項(xiàng)'--sleep'指定的休眠時(shí)間之前，進(jìn)行事務(wù)提交和刷新選項(xiàng)'--file'指定的文件，通過(guò)選項(xiàng)'--limit'控制事務(wù)的大小。

--host，-h

指定連接的數(shù)據(jù)庫(kù)IP地址。

--port，-P

指定連接的數(shù)據(jù)庫(kù)Port端口。

--user，-u

指定連接的數(shù)據(jù)庫(kù)用戶(hù)。

--password，-p

指定連接的數(shù)據(jù)庫(kù)用戶(hù)密碼。

--socket，-S

指定使用SOCKET文件連接。

--databases，-d

指定連接的數(shù)據(jù)庫(kù)

--source

指定需要進(jìn)行歸檔操作的表，該選項(xiàng)是必須指定的選項(xiàng)，使用DSN方式表示。

--dest

指定要?dú)w檔到的目標(biāo)端表，使用DSN方式表示。

如果該選項(xiàng)沒(méi)有指定的話(huà)，則默認(rèn)與選項(xiàng)'--source'指定源端表為相同表。

--where

指定通過(guò)WHERE條件語(yǔ)句指定需要?dú)w檔的數(shù)據(jù)，該選項(xiàng)是必須指定的選項(xiàng)。不需要加上'WHERE'關(guān)鍵字，如果確實(shí)不需要WHERE條件進(jìn)行限制，則指定'--where 1=1'。

--file

指定表數(shù)據(jù)需要?dú)w檔到的文件。使用類(lèi)似MySQL DATE_FORMAT()格式化命名方式。

文件內(nèi)容與MySQL中SELECT INTO OUTFILE語(yǔ)句使用相同的格式，文件命名選項(xiàng)如下所示：

'

%Y：年，4位數(shù)(Year, numeric, four digits)        

%m：月，2位數(shù)(Month, numeric (01..12))       

%d：日，2位數(shù)(Day of the month, numeric (01..31))    

%H：小時(shí)(Hour (00..23))                

%i：分鐘(Minutes, numeric (00..59))            

%s：秒(Seconds (00..59))             

%D：數(shù)據(jù)庫(kù)名(Database name)            

%t：表名(Table name)                   

例如：--file '/var/log/archive/%Y-%m-%d-%D.%t'

'

--output-format

指定選項(xiàng)'--file'文件內(nèi)容輸出的格式。

默認(rèn)不指定該選項(xiàng)是以制表符進(jìn)行字段的分隔符，如果指定該選項(xiàng)，則使用','(逗號(hào))作為字段分隔符，使用'"'(雙引號(hào))將字段括起。用法示例：'--output-format=dump'。

--for-update

指定為每次歸檔執(zhí)行的SELECT語(yǔ)句添加FOR UPDATE子句。

--share-lock

指定為每次歸檔執(zhí)行的SELECT語(yǔ)句添加LOCK IN SHARE MODE子句。

--header

指定在文件中第一行寫(xiě)入字段名稱(chēng)作為標(biāo)題。

--ignore

指定為INSERT語(yǔ)句添加IGNORE選項(xiàng)。

--limit

默認(rèn)值：1

指定每條語(yǔ)句獲取表和歸檔表的行數(shù)。

--local

指定不將OPTIMIZE和ANALYZE語(yǔ)句寫(xiě)入binlog。

--max-lag

默認(rèn)值：1s

指定允許主從復(fù)制延遲時(shí)長(zhǎng)的最大值，單位秒。如果在每次獲取行數(shù)據(jù)之后主從延遲超過(guò)指定的值，則歸檔操作將暫停執(zhí)行，暫停休眠時(shí)間為選項(xiàng)'--check-interval'指定的值。待休眠時(shí)間結(jié)束之后再次檢查主從延遲時(shí)長(zhǎng)，檢查方法是通過(guò)從庫(kù)查詢(xún)的'Seconds_Behind_Master'值來(lái)確定。如果主從復(fù)制延遲一直大于該參數(shù)指定值或者從庫(kù)停止復(fù)制，則操作將一直等待直到從庫(kù)重新啟動(dòng)并且延遲小于該參數(shù)指定值。

--no-delete

指定不刪除已被歸檔的表數(shù)據(jù)。

--optimize

指定工具完成數(shù)據(jù)歸檔后對(duì)表執(zhí)行'OPTIMIZE TABLE'操作。指定方法如'--analyze=ds'，s代表源端表，d代表目標(biāo)端表，也可以單獨(dú)指定。

--primary-key-only

指定只歸檔主鍵字段，是選項(xiàng)'--columns=主鍵'的簡(jiǎn)寫(xiě)。

如果工具歸檔的操作是進(jìn)行DELETE清除時(shí)最有效，因?yàn)橹恍枳x取主鍵一個(gè)字段而無(wú)需讀取行所有字段。

--progress

指定每多少行打印進(jìn)度信息，打印當(dāng)前時(shí)間，已用時(shí)間以及多少行進(jìn)行歸檔。

--purge

指定執(zhí)行的清除操作而不是歸檔操作。允許忽略選項(xiàng)'--dest'和'--file'進(jìn)行操作，如果只是清除操作可以結(jié)合選項(xiàng)'--primary-key-only'會(huì)更高效。

--quiet，-q

指定工具靜默執(zhí)行，不輸出任何的執(zhí)行信息。

--replace

指定寫(xiě)入選項(xiàng)'--dest'指定目標(biāo)端表時(shí)改寫(xiě)INSERT語(yǔ)句為REPLACE語(yǔ)句。

--retries

默認(rèn)值：1

指定歸檔操作遇到死鎖或超時(shí)的重試次數(shù)。當(dāng)重試次數(shù)超過(guò)該選項(xiàng)指定的值時(shí)，工具將報(bào)錯(cuò)退出。

--run-time

指定工具歸檔操作在退出之前需要運(yùn)行的時(shí)間。允許的時(shí)間后綴名為s=秒，m=分，h=小時(shí)，d=天，如果沒(méi)指定，默認(rèn)為s。

--[no]safe-auto-increment

默認(rèn)值：yes

指定不使用自增列(AUTO_INCREMENT)最大值對(duì)應(yīng)的行進(jìn)行歸檔。

該選項(xiàng)在進(jìn)行歸檔清除時(shí)會(huì)額外添加一條WHERE子句以防止工具刪除單列升序字段具有的具有AUTO_INCREMENT屬性最大值的數(shù)據(jù)行，為了在數(shù)據(jù)庫(kù)重啟之后還能使用到AUTO_INCREMENT對(duì)應(yīng)的值，但這會(huì)引起無(wú)法歸檔或清除字段對(duì)應(yīng)最大值的行。

--set-vars

默認(rèn)：

    wait_timeout=10000

    innodb_lock_wait_timeout=1

    lock_wait_timeout=60

工具歸檔時(shí)指定參數(shù)值，如有多個(gè)用','(逗號(hào))分隔。如'--set-vars=wait_timeout=5000'。

--skip-foreign-key-checks

指定使用語(yǔ)句SET FOREIGN_KEY_CHECKS = 0禁用外鍵檢查。

--sleep

指定工具在通過(guò)SELECT語(yǔ)句獲取歸檔數(shù)據(jù)需要休眠的時(shí)間，默認(rèn)值是不進(jìn)行休眠。在休眠之前事務(wù)并不會(huì)提交，并且選項(xiàng)'--file'指定的文件不會(huì)被刷新。如果指定選項(xiàng)'--commit-each'，則在休眠之前會(huì)進(jìn)行事務(wù)提交和文件刷新。

--statistics

指定工具收集并打印操作的時(shí)間統(tǒng)計(jì)信息。

統(tǒng)計(jì)信息示例如下：

'

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

默認(rèn)：1

指定每個(gè)事務(wù)處理的行數(shù)。如果是0則禁用事務(wù)功能。

--version

顯示工具的版本并退出。

--[no]version-check

默認(rèn)值：yes

檢查Percona Toolkit、MySQL和其他程序的最新版本。

--why-quit

指定工具打印當(dāng)非因完成歸檔行數(shù)退出的原因。

在執(zhí)行一個(gè)自動(dòng)歸檔任務(wù)時(shí)該選項(xiàng)與選項(xiàng)'--run-time'一起使用非常方便，這樣可以確定歸檔任務(wù)是否在指定的時(shí)間內(nèi)完成。如果同時(shí)指定了選項(xiàng)'--statistics'，則會(huì)打印所有退出的原因。

DSN選項(xiàng)(DSN)

可以使用DSN方式來(lái)連接數(shù)據(jù)庫(kù)，DSN選項(xiàng)為key=value方式，在等號(hào)的兩側(cè)不能有空格出現(xiàn)，并且區(qū)分大小寫(xiě)，多個(gè)選項(xiàng)之前以','(逗號(hào))隔開(kāi)，主要選項(xiàng)如下：

a

歸檔操作是在哪個(gè)庫(kù)下進(jìn)行的，相當(dāng)于USE操作。

A

指定默認(rèn)字符集。

b

當(dāng)值為true時(shí)，禁止SQL_LOG_BIN，相當(dāng)于SQL_LOG_BIN = 0。

D

指定包含需要?dú)w檔表的數(shù)據(jù)庫(kù)。

h

指定連接的主機(jī)。

u

指定連接的用戶(hù)。

p

指定連接需要的密碼。

P

指定連接的端口。

S

指定連接的SOCKET文件。

t

指定需要?dú)w檔的表。

i

指定需要使用的索引。

選項(xiàng)用法說(shuō)明

工具至少需指定選項(xiàng)--dest、--file或--purge其中之一；

選項(xiàng)--ignore和--replace是互斥的；

選項(xiàng)--txn-size和--commit-each是互斥的；

選項(xiàng)--share-lock和--for-update是互斥的；

--analyze和--optimize是互斥的。

本次測(cè)試是基于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;

創(chuàng)建存儲(chǔ)過(guò)程i_yoon插入測(cè)試數(shù)據(jù)

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);

在目標(biāo)端創(chuàng)建測(cè)試庫(kù)hank，表結(jié)構(gòu)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;

執(zhí)行歸檔，不刪除源端數(shù)據(jù)：--source 源端，--dest：目標(biāo)端，--no-delete：不刪除源端數(shù)據(jù)   

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

執(zhí)行歸檔，刪除源端數(shù)據(jù)：--source 源端，--dest：目標(biāo)端

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

表歸檔到表(批量進(jìn)行)

批量進(jìn)行歸檔涉及的選項(xiàng)是--limit，批量進(jìn)行插入涉及的選項(xiàng)為--bulk-insert，指定選項(xiàng)--bulk-insert同時(shí)也會(huì)指定選項(xiàng)--bulk-delete，如果不刪除已歸檔數(shù)據(jù)，則需要指定選項(xiàng)--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

表歸檔到文件

表歸檔到文件將選項(xiàng)--dest換成--file，并且根據(jù)需要添加選項(xiàng)--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

表清除數(shù)據(jù):--txn-size 表示200行提交一次失誤，若最后刪除的數(shù)據(jù)低于200行報(bào)錯(cuò)，若改--txn-size值

如果只是進(jìn)行表數(shù)據(jù)清除操作而不做歸檔操作，則可以忽略選項(xiàng)--dest或--file，通過(guò)指定選項(xiàng)--purge，可以先使用選項(xiàng)--dry-run打印查詢(xún)需要清除數(shù)據(jù)的執(zhí)行語(yǔ)句，做好確認(rèn)之后再執(zhí)行。

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