PerconaToolkitのpt-archiverを使ってみたんだが・・・。

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がオプションになってほしい。(ちゃんと知らないやつが使うと事故の元)

触り始めは「面白そう！」と思ってたんだが、かなりニッチなツールだわコレ・・・。

参考

www.percona.com

pt-archiver | MySQLチューニング/保守サポート/コンサルティングのスマートスタイル

yakst.comyakst.com