pt-archiverとは
- 大きなテーブルから他のテーブルあるいはファイルにレコードをアーカイブするのにツール
- where句が使え、抽出条件が設定できる
- 出力ファイル名にDBスキーマ名、テーブル名、年月日時分秒を入れられる
- 抽出対象データを自動的に削除する(デフォルト)
これを使う目的としては以下だろう。
OLTPクエリに影響を与えることなく古いデータをアーカイブし、そのデータを同じサーバあるいは違うサーバの他のテーブルに保存する、あるいはLOAD DATA INFILE文に適したフォーマットでファイルに保存することだ。
yakst.com より
うちの会社でも「不要なデータをどうする?」という議題が過去にあって、 テーブル単位で 取りたければこういう方法もあるなー、と思って調べてみた。
使い方
pt-archiver [オプション] --source [DSN] --file "ファイル名" --where "条件文"
必須系オプション
--source [DSN]
(h=ホスト名 後述の設定ファイルに書くなら不要) ,D=DBスキーマ名,t=テーブル名
--file "ファイル名"
アーカイブした内容を保存するファイル名。
ファイル名にDBスキーマ名、テーブル名、年月日時分秒を入れられる
%d Day of the month, numeric (01..31) %H Hour (00..23) %i Minutes, numeric (00..59) %m Month, numeric (01..12) %s Seconds (00..59) %Y Year, numeric, four digits %D Database name %t Table name
こんな感じで書ける
--file '/var/log/archive/%Y-%m-%d-%D_%t.txt'
この例だと/var/log/archive/2019-01-29-testdb_testtable.txt
みたいなファイルができるイメージ。
ちなみにオプションで接続先ホスト、ポート、ユーザ、パスワードを設定できるが、設定ファイルに書き込むことも可能だ。
pt-archiver.confの編集
# touch /etc/percona-toolkit/pt-archiver.conf # vim /etc/percona-toolkit/pt-archiver.conf
pt-archiver.conf
user=xxxxx password=xxxxxxx host=xxxxxxx port=xxxx
※対象のMySQLデータベースに接続するのに必要な任意の値を入れてね。
--where "条件文"
アーカイブするレコードの条件を指定する。 条件が無い場合でもwhere句は必須なのでその時は1=1とか書けばいい。
その他の主なオプション
--no-delete
デフォルトでは抽出対象データを削除するので、削除したくない場合はつける
--no-check-charset
デフォルトではレコードとアーカイブファイルの文字コードチェックが走るので、このオプションでDB側の文字コードに併せる形にする
--charset=文字コード
--charset=utf8というように設定してファイルの文字コードを指定する --no-check-charset の場合、抽出元テーブルがutf8mb4だとエラーになるため使う。
--statistics
統計情報を表示する。
--dest
--file "ファイル名" でファイルにデータを抽出できるが、--destを使うと他のテーブルに対してそのデータを直接入れることができる イメージは以下の通り
--dest h=ホスト名,P=ポート番号,D=DBスキーマ名,t=テーブル名,u=ユーザ,p=パスワード
使用時の注意
- PKが張ってないテーブルには使えない
- utf8mb4にうまく対応できていないので使えない(--no-check-charsetだと2バイト文字が文字化けし、--charset=utf8だとテーブルはutf8mb4だ!と怒る→詰んでる)
- 単純なデータ抽出には向いてない(--no-deleteを付ける必要がある、デフォルトが削除するというのは危険)
個人的な感想
- --source [DSN] でDBスキーマ名とかテーブル名がLIKEとか指定無しとかあるといいなあ・・・。
- MySQL8はutf8mb4がデフォルトだから、直ってほしい。
- --no-deleteじゃなくてdeleteがオプションになってほしい。(ちゃんと知らないやつが使うと事故の元)
触り始めは「面白そう!」と思ってたんだが、かなりニッチなツールだわコレ・・・。