はじめに
弊社は長年サポート業務を続けていますが、お客様からよく以下のような質問を頂きます。
「MySQLでトラブルが発生した時、どのような情報を収集すればいいか分からない」
もちろん、発生している事象によって必要な情報は変わってきますが、どんなケースでも最初に取るべき情報が決まっていれば、トラブル時にも慌てず対応することができます。
本記事では、その一つの選択肢として「pt-secure-collect」というツールをご紹介します。
pt-secure-collect とは？
pt-secure-collect は、Percona Toolkit に含まれるツールの一つです。
ちなみに、2018年4月にリリースされた Ver 3.0.9 で追加された比較的新しいツールでもあります。
※ Percona Toolkitについては弊社ホームページでも解説しています
pt-secure-collect を使用すると、以下のような操作を行うことができます。
- pt-summary, pt-mysql-summary, pt-stalk の情報を収集（詳しくは後述）
- 収集したデータの暗号化および複合
- 収集したデータのサニタイズ（≒ マスキング）
- 任意の追加コマンド、ファイルの収集
同ツールは、デフォルトで以下3つのコマンドを内部的に実行しています。これらは全て Percona Toolkit に含まれています。
pt-summary
コマンドを実行したサーバの主要な情報を収集する。具体的には以下のような情報が含まれます。
- CPU情報（製品名、クロック数、コア数）
- メモリ情報（全容量、使用容量、スワップ）
- ディスク情報（ファイルシステム、I/Oスケジューラ、パーティション）
- ネットワーク情報（接続数、アダプタ）
pt-mysql-summary
MySQLに関する主要な情報を収集する。具体的には以下のような情報が含まれます。
- 接続情報（接続数、processlist）
- ステータス情報（status変数、バイナリログ）
- InnoDB情報（バッファプール、トランザクション）
- my.cnf
pt-stalk
サーバおよびMySQLの負荷状況をモニタリングするための情報を収集する。
また、収集するタイミングを任意に指定することができる（1分おき、接続数が一定数を超えた時、等）
使い方
1. Percona Toolkit のインストール
上記の通り、pt-secure-collect コマンドは内部的に他のToolkitのコマンドを実行しています。そのため、Percona Toolkitをまとめてインストールしてしまうのが一番楽な方法です。
※ 個別にコマンドをダウンロード・インストールすることもできます
|
1 2 3 4 |
$ sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm $ sudo yum install -y percona-toolkit $ pt-secure-collect --help |
また、pt-stalkは iostat / mpstat コマンドを使用しているため、もし未インストールであれば sysstat パッケージもインストールします。
|
1 |
$ sudo yum install sysstat |
2. 情報を収集する
情報を収集したい場合は、collect句を付けます。あわせてMySQLのユーザ名なども指定します。
|
1 |
$ pt-secure-collect collect --mysql-user=root --mysql-password=MySQL5.7 --mysql-host=127.0.0.1 --encrypt-password=encpass |
その他にも、以下のようなオプションが用意されています。
| オプション | デフォルト | 説明 |
|---|---|---|
| –bin-dir | $PATH | pt-summaryなどのコマンドが格納されたディレクトリ 各コマンドを個別にダウンロードした場合は配置先を指定します |
| –temp-dir | ${HOME}/data_collection_{timestamp} | 収集したデータを保存するディレクトリ |
| –include-dir | – | サニタイズされた.tarファイルに含めるディレクトリ |
| –config-file | ~/.my.cnf | 設定ファイルのパス |
| –mysql-host | 127.0.0.1 | MySQLのホスト |
| –mysql-port | 3306 | MySQLのポート |
| –mysql-user | – | MySQLのユーザ |
| –mysql-password | – | 上記ユーザのパスワード |
| –ask-mysql-pass | – | MySQLユーザのパスワードを対話式で指定 |
| –extra-cmd | – | データ収集時に合わせて任意のコマンドを実行できる 複数のコマンドを指定することも可能 |
| –encrypt-password | – | 暗号化する時のパスワードを設定 |
| –no-collect | – | データを収集しない（テスト実行などに使用） |
| –no-sanitize | – | サニタイズを実行しない |
| –no-encrypt | – | 収集したデータの暗号化を行わない |
| –no-sanitize-hostnames | – | ホストネームをサニタイズしない |
| –no-sanitize-queries | – | クエリをサニタイズしない |
| –no-remove-temp-files | – | 収集する時の一時ファイルを削除しない |
3. 収集データを復号する
暗号化されたデータを復号したい時は、decrypt句をつけます。
|
1 2 3 |
$ pt-secure-collect decrypt ./data_collection_2020-04-10_14_58_19/data_collection_2020-04-10_14_58_19.tar.gz.aes Encryption password: → --encrypt-passwordオプションで指定したパスワードを入力 |
4. 収集データを確認する
収集データは .tar.gz で圧縮されているため、展開して中身を確認します。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ tar zxvf data_collection_2020-04-10_14_58_19.tar.gz $ ls data_collection_2020-04-10_14_58_19 2020_04_10_14_58_20-df 2020_04_10_14_58_20-slave-status 2020_04_10_14_58_50-netstat_s 2020_04_10_14_58_20-disk-space 2020_04_10_14_58_20-sysctl 2020_04_10_14_58_50-opentables1 2020_04_10_14_58_20-diskstats 2020_04_10_14_58_20-top 2020_04_10_14_58_50-opentables2 ... (省略) ... 2020_04_10_14_58_20-processlist 2020_04_10_14_58_50-mutex-status1 pt-mysql-summary_2020-04-10_14_59_56.out 2020_04_10_14_58_20-procstat 2020_04_10_14_58_50-mutex-status2 pt-stalk_2020-04-10_14_58_19.out 2020_04_10_14_58_20-procvmstat 2020_04_10_14_58_50-mysqladmin pt-summary_2020-04-10_14_59_51.out 2020_04_10_14_58_20-ps 2020_04_10_14_58_50-netstat |
5. 収集データを暗号化/サニタイズする
収集データは後から暗号化、もしくはサニタイズすることもできます。
その場合、encrypt句 or sanitize句を付けます。
動作検証
ケース1：MySQL5.7 / 8.0
まずは dbdeployer でシングル構成のMySQL5.7を構築し、そこに対してpt-secure-collect を実行してみましたが、こちらは問題なくデータ収集できました。
|
1 2 3 4 5 6 7 8 |
$ dbdeployer deploy single 5.7.29 $ pt-secure-collect collect --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=5729 Encryption password: <password>で入力 ※ Re type password: <password>で入力 INFO[2020-04-10 17:12:28] Creating temporary directory: /home/vagrant/data_collection_2020-04-10_17_12_26 ... INFO[2020-04-10 17:14:55] Creating tar file "/home/vagrant/data_collection_2020-04-10_17_12_26/data_collection_2020-04-10_17_12_26.tar.gz" |
※ 暗号化のパスワードを “空文字” にするとデータは取れないようです（バグレポート）
つぎはMySQL8.0に対して実行してみます。こちらも問題なく実行できました。
|
1 2 3 4 5 |
$ dbdeployer deploy single 8.0.19 $ pt-secure-collect collect --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=8019 ... INFO[2020-04-10 17:23:02] Creating tar file "/home/vagrant/data_collection_2020-04-10_17_20_18/data_collection_2020-04-10_17_20_18.tar.gz" |
ケース2：非同期レプリケーション
残念ながら、pt-secure-collect にはレプリケーション向けのオプションなどは用意されていません。ただ、pt-stalkが収集する情報の中には「***-slave-status」のようにスレーブ情報も含まれているので、問題発生時の調査にも有益な情報です。
そこで、マスタ・スレーブに対して同時に pt-secure-collect を実行します。
この時、収集データ名を指定してどれが該当サーバの情報かどうか判別できるようにすると良いです。また、パスワード入力部分をスキップするため、–encrypt-passwordオプションを指定してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
$ dbdeployer deploy replication 8.0.19 $ sandboxes/rsandbox_8_0_19/use_all -e "select @@port" # master @@port 20920 # server: 1 @@port 20921 # server: 2 @@port 20922 $ pt-secure-collect collect --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=20920 --encrypt-password=encpass --temp-dir=${HOME}/data_master & \ pt-secure-collect collect --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=20921 --encrypt-password=encpass --temp-dir=${HOME}/data_slave1 & \ pt-secure-collect collect --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=20922 --encrypt-password=encpass --temp-dir=${HOME}/data_slave2 $ tree ./data_* ./data_master └── data_master.tar.gz.aes ./data_slave1 └── data_slave1.tar.gz.aes ./data_slave2 └── data_slave2.tar.gz.aes 0 directories, 3 files |
ケース3：グループレプリケーション
次に、MySQLにおける新たなレプリケーション形式である Group Replication ですが、残念ながら pt-secure-collect が収集する情報には、同レプリケーション関連の主要な情報は含まれていません（Feature Request）。
そのため、Group Replication の監視情報を、–extra-cmdオプションを追加して収集します。
まずは、Group Replication情報を収集するシェルスクリプトを用意します。
今回は Performance_schema の replication_group_member_stats / replication_group_members テーブルから情報を取得します。
|
1 2 3 4 5 6 7 8 |
$ vi /home/vagrant/collect_gr.sh #!/bin/bash mysql -u $1 -p$2 -P $3 -h $4 -Be "select * from performance_schema.replication_group_member_stats" > $5/group_repl_info.out mysql -u $1 -p$2 -P $3 -h $4 -Be "select * from performance_schema.replication_group_members" >> $5/group_repl_info.out $ chmod +x /home/vagrant/collect_gr.sh |
pt-secure-collect を実行する時に、–extra-cmdオプションでこのシェルを指定すればGroup Replication情報を一緒に収集できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$ dbdeployer deploy --topology=group replication 8.0.19 --single-primary $ pt-secure-collect collect --mysql-port=22920 --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=msandbox \ --extra-cmd='/home/vagrant/collect_gr.sh $mysql-user $mysql-pass $mysql-port $mysql-host $temp-dir' $ pt-secure-collect decrypt data_collection_2020-04-14_10_10_48/data_collection_2020-04-14_10_10_48.tar.gz.aes $ tar zxvf data_collection_2020-04-14_10_10_48.tar.gz $ ls -l data_collection_2020-04-14_10_10_48/group_repl_info.out -rw-rw-r--. 1 vagrant vagrant 1171 Apr 14 10:13 data_collection_2020-04-14_10_10_48/group_repl_info.out $ cat data_collection_2020-04-14_10_10_48/group_repl_info.out CHANNEL_NAME VIEW_ID MEMBER_ID COUNT_TRANSACTIONS_IN_QUEUE COUNT_TRANSACTIONS_CHECKED COUNT_CONFLICTS_DETECTED COUNT_TRANSACTIONS_ROWS_VALIDATING TRANSACTIONS_committed_all_members last_conflict_free_transaction count_transactions_remote_in_applier_queue count_transactions_remote_applied count_transactions_local_proposed count_transactions_local_rollback group_replication_applier 15868250577341501:3 00022920-1111-1111-1111-111111111111 0 0 0 0 00022919-bbbb-cccc-dddd-eeeeeeeeeeee:1-3 0 2 0 0 group_replication_applier 15868250577341501:3 00022921-2222-2222-2222-222222222222 0 0 0 0 00022919-bbbb-cccc-dddd-eeeeeeeeeeee:1-3 0 1 0 0 group_replication_applier 15868250577341501:3 00022922-3333-3333-3333-333333333333 0 0 0 0 00022919-bbbb-cccc-dddd-eeeeeeeeeeee:1-3 0 0 0 0 CHANNEL_NAME MEMBER_ID MEMBER_HOST MEMBER_PORT MEMBER_STATE MEMBER_ROLE MEMBER_VERSION group_replication_applier 00022920-1111-1111-1111-111111111111 hostname 22920 ONLINE PRIMARY 8.0.19 group_replication_applier 00022921-2222-2222-2222-222222222222 hostname 22921 ONLINE SECONDARY 8.0.19 group_replication_applier 00022922-3333-3333-3333-333333333333 hostname 22922 ONLINE SECONDARY 8.0.19 |
ケース4：MariaDB
次に、MySQLフォークであるMariaDBでも動作するか確認します。
最新GAバージョンの 10.4 で試してみます。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ dbdeployer deploy single maria_10.4.12 $ pt-secure-collect collect --mysql-user=root --mysql-password=msandbox --mysql-host=127.0.0.1 --mysql-port=10412 $ pt-secure-collect decrypt data_collection_2020-04-13_17_01_20/data_collection_2020-04-13_17_01_20.tar.gz.aes $ tar zxvf data_collection_2020-04-13_17_01_20.tar.gz $ ls -l data_collection_2020-04-13_17_01_20 total 6764 -rw-rw-r--. 1 vagrant vagrant 15450 Apr 13 17:03 2020_04_13_17_01_27-df -rw-rw-r--. 1 vagrant vagrant 123 Apr 13 17:03 2020_04_13_17_01_27-disk-space ... |
問題なく実行できました。
ケース5：Percona製品
pt-secure-collectはPercona社が開発・サポートを行っています。
そのため、同社の製品である Percona Server for MySQL および Percona XtraDB Cluster に対して問題なく実行できます。
また、Percona XtraDB Cluster（＝ Galera Cluster）のレプリケーション情報は pt-stalk が収集するデータに含まれているため、追加で収集する情報はございません。
補足：pt-stalkで取得する情報の解説
pt-secure-collectが収集する情報の大半は pt-stalk コマンドによって取得されたものです。そこで、どのような情報が含まれているのか簡単に説明します。
※ 詳細はGitHubのページを参考にしています
| ファイル名 | 説明 | コマンド |
|---|---|---|
| df | ディスクの使用状況 | df -k |
| disk-space | ディスクの空き容量 | check_disk_space() |
| diskstats | I/O統計 | cat /proc/diskstats |
| hostname | ホストネーム | hostname |
| innodbstatus1 | InnoDBの統計情報 | mysql -e ‘SHOW ENGINE INNODB STATUS’ |
| innodbstatus2 | 同上 | 同上 |
| interrupts | 割り込み情報 | cat /proc/interrupts |
| iostat | iostat実行結果 | iostat -dx $OPT_SLEEP_COLLECT $cnt |
| iostat-overall | iostat実行結果（累積） | iostat -dx $OPT_RUN_TIME 2 |
| log_error | MySQLエラーログ | tail -f “$mysql_error_log” |
| lsof | mysqlプロセスが開いてるファイル | lsof $mysqld_pid |
| meminfo | メモリ情報 | cat /proc/meminfo |
| mpstat | mpstat実行結果 | mpstat -P ALL $OPT_SLEEP_COLLECT $cnt |
| mpstat-overall | mpstat実行結果（累積） | mpstat -P ALL $OPT_RUN_TIME 1 |
| mysqladmin | MySQLステータス情報 | mysqladmin extended-status |
| netstat | ネットワーク情報 | netstat -antp |
| netstat_s | プロトコルの統計 | netstat -s |
| opentables1 | テーブル統計 | mysql -e ‘SHOW OPEN TABLES’ |
| opentables2 | 同上 | 同上 |
| output | ツール実行中の標準出力 | |
| pmap | mysqlプロセスのpmap | pmap -x $mysqld_pid |
| processlist | プロセスリスト | mysql -e “SHOW FULL PROCESSLIST\G” |
| procstat | システムの統計 | cat /proc/stat |
| procvmstat | vmstatの内容 | cat /proc/vmstat |
| ps | プロセス情報 | ps -eaF |
| ps-locks-transactions | Performance_schemaのロック情報 | ps_locks_transactions() |
| slave-status | スレーブ情報 | slave_status() |
| sysctl | システム変数 | sysctl -a |
| top | topコマンド | top -bn${OPT_RUN_TIME} |
| trigger | pt-stalkのトリガ記録 | |
| variables | MySQL変数情報 | SHOW GLOBAL VARIABLES |
| vmstat | vmstat | vmstat $OPT_SLEEP_COLLECT $cnt |
| vmstat-overall | vmstat（累積） | vmstat $OPT_RUN_TIME 2 |
おわりに
以上、pt-secure-collect について説明しました。
何か問題が発生した時に手軽に取得できる情報として、とても便利なため是非使ってみてください。
