今までbackupに関しては、第153回 mysqlpumpを使ってバックアップを取ってみるや第15回 mysqldumpを使ってバックアップするなどで紹介をしてきましたが、今回は第9回 pt-query-digestを使って遅いクエリーを発見するや第116回 pt-upgradeを使ってインデックスチューニングの成果を確認するで紹介したPerocona Toolkitの1つ、pt-archiverを使って部分的に取得する方法を紹介します。
検証環境
今回は、第125回 phpMyAdminでDockerで建てたMySQLにアクセスするで記載したdocker-composeを利用して作成します。手元で簡単に試せるように、GitHubのわたしのレポジトリにサンプルコードとして置いてあるので、気軽に試したい方はgit cloneして試してみてください。試すにはdockerとdocker-composeが必要です。
pt-achiverを使用する
pt-achiverは、この連載でも何回か紹介しているPercona Toolkitのうちの1つで、MySQLのテーブルから別のテーブルやファイルとしてテーブルにある行を、文字通りアーカイブするためのツールになります。公式のドキュメントがあります。ぜひ一度よく読んで挙動を理解してから実行を行ってください。
では、pt-achiverを実際に実行してみましょう。今回は郵便番号データベースから北海道の行を取得してみます。
さて、上記のように実行してみようとしたら、以下のようにエラーが発生してしまいます。
こちらのエラー内容ですが、pt-achiverではINDEXや主キーが無いテーブルでは使用することができません。今回北海道の行を取得するので、それに合ったINDEXを以下のように貼りましょう。
これでもう一度実行してみましょう。--sourceにはDSN形式で接続情報を記載します。今回はローカルのファイルにデータを取得するため--file
オプションで指定します。--no-delete
オプションですが、pt-archiverはアーカイブ済みの行をデータベース上から消してしまうため、消えてもらっては困る場合は必ずこのオプションを指定しましょう。とくに本番サーバに実行する場合には注意をして実行しましょう。
--where
オプションでWHERE句に指定したいWHERE句を書くことで取得ができます。
このように、取得できていることがわかります。mysqlコマンドラインクライアントで取得したときと同様に、タブ区切りの文字になっています。ただ、なぜか1件しか取得されていません。このように、想定外の挙動が起きた場合に便利なのが、--dry-run
オプションです。こちらを着けて実行してみます。
上記のように、今回の指定オプションで実行されるSQLが出力されます。先ほど作成したINDEXがFORCE INDEXで利用されていることがわかります。そして最後にLIMIT 1がされているため、今回は1件しか取得されていません。北海道は、以下のようなクエリで8248件あることがわかりました。
北海道の8248件を--limit
オプションで指定をして全部取得をしてみましょう。取得した値は上書きされてしまうので、1回zipcodeファイルを削除してからもう一度実行してみます。
ということで、取得ができていることがわかります。
便利なオプションに関して
その他の便利なオプションとして、--statistics
オプションがあります。このオプションを付けて実行すると、以下のように実行時にどういう処理が行われたのかが確認できるようになってます。
データの取得時に一貫性を保ちたいこともあると思いますが、その場合は--for-update
オプションを付けてみると、SELECT ~ FOR UPDDATE 構文を利用してロックが取られるので指定しましょう。
上記のようにdry-runをしてみると、後ろにFOR UPDATEが付与されていることがわかります。
また、--where
オプションは必須のオプションとなるため、全件の取得をしたい場合はどうしたら良いのか? と悩むこともあると思いますが、その場合は1=1
のような必ずtrueになる条件式を入れてあげれば取得することができます。
zipcodeの行数は124340行になるので、LIMITに指定して試してみましょう。
以上のように取得ができることがわかります。
最後に、--no-delete
オプションを付けないとどうなるかを確認していきたいと思います。
こちらのオプションは付けず実行してしまうと対象のDBから行が削除されてしまうという様に説明をしましたが、実際どうなってしまうか確認してみましょう。実行結果も確認がしたいので--statistics
オプションを付けて実行してみましょう。
deletingが実行されていることがわかります。MySQLに入って確認してみましょう。
ということで、データを取得した後のデータが無くなってしまっていることがわかります。実行する際には--no-delete
オプションが必要かどうか、必ずちゃんと確認してから実行をするようにしましょう。
まとめ
pt-archiverを使用すると、部分的にファイルを抜き出して利用するということがかんたんにできるので、行数がすごく多いテーブルから抽出して分析をしたい場合などに便利です。mysqlクライアントから抜き出して同様の事もできるのですが、NULLの処理など面倒なことが多いです。そのため、このpt-archiverを利用してデータの一部を取得して利用できるのは非常に便利です。
ただし、--no-delete
オプションを着け忘れて実行してしまうと、解説を行ったとおりDB上からデータが無くなってしまうため注意が必要です。本番に直接かけるような事は控えたほうが安全だと思います。
また、取得したファイルのロードには、LOAD DATA INFILE構文を使用する必要があるため、FILE権限が必要な事にも注意が必要です。