今まで、あまり気にしていなかったのですが、MySQLって監査（audit）の機能ってないのです。例えば、アプリケーション上のトラブルでデータが消えてしまった。原因がわからないなんていった場合の「いつ？」「だれか？」「何をした？」ってのを追跡したい場合ってどう対応しています？
私的には、24時間365日で、クエリーログとっておけばいいんじゃない？とか思いつつ、馬鹿でかいファイルサイズになっちゃうから結局やらない。・・で結局、なんにもしてません状態だったのですが、MySQLでそういう機能があるのであれば使うつもりでした。。。つまり、MySQLにはそういう機能はありません。oracleとか、MS-SQLserverとかはあるのにね・・・ と思ってたら、McAfeeから、MySQLの監査ツールを無償公開しておりました。知らなかった・・・
というわけで、 McAfeeのMySQL監査ツールを導入してみたいと思います。
特に説明するまでもないのですが、インストールから動作確認まで記載しております。その後、監査ログをどう利用するかについては、ここでは触れていません。お任せします。
セットアップ
プラグインファイルのダウンロード
github上に公開されております。そこからダウンロードします。 https://github.com/mcafee/mysql-audit/downloads
<h1>wget https://github.com/downloads/mcafee/mysql-audit/audit-plugin-mysql-5.5-1.0.3-371-linux-x86_64.zip</h1>
MySQLの事前調査
McAfeeのMySQL監査ツールは、MySQL5.1、5.5対応です。なので、ご自身のMySQLが対応しているかどうか事前に確認しておきます。併せて、インストール時にプラグインがローディングできているのかもこちらでは確認しております。
- バージョンの確認
mysql> select @@version; +------------+ | @@version | +------------+ | 5.5.28-log | +------------+ 1 row in set (0.00 sec)
- オフセット値を求める
一部、対応しないバージョンがあります。私の場合は、mysqlの5.5.31で、pluginのインストールに失敗しました。
オフセット値は、下記shellで求めることができます。
# cd /usr/local/src # wget https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh # chmod 775 offset-extract.sh /usr/sbin/mysqld # # オフセット値を求めて、my.cnfにセットします。セットする値は、下記の例ですと、6064, 6112, 3816, 4240, 88, 2568 です。 # ./offset-extract.sh /usr/sbin/mysqld {"5.5.31","d37e7c0891712fab8fdee7189515b9a5", 6064, 6112, 3816, 4240, 88, 2568}, - プラグインディレクトリの確認
mysql> show variables like 'plugin_dir'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | plugin_dir | /usr/lib64/mysql/plugin/ | +---------------+--------------------------+ 1 row in set (0.00 sec)
pluginディレクトリにファイルを格納
解凍したファイルを所定のMySQLプラグインフォルダにコピーしてあげるだけです。
<h1>unzip audit-plugin-mysql-5.5-1.0.3-371-linux-x86_64.zip</h1> Archive: audit-plugin-mysql-5.5-1.0.3-371-linux-x86_64.zip creating: audit-plugin-mysql-5.5/ creating: audit-plugin-mysql-5.5/lib/ inflating: audit-plugin-mysql-5.5/lib/libaudit_plugin.so inflating: audit-plugin-mysql-5.5/COPYING inflating: audit-plugin-mysql-5.5/THIRDPARTY.txt inflating: audit-plugin-mysql-5.5/README.txt <h1>cd audit-plugin-mysql-5.5/lib/</h1> <h1>ls -l</h1> -rw-r--r-- 1 root root 737422 11月 14 17:52 libaudit_plugin.so <h1>cp libaudit_plugin.so /usr/lib64/mysql/plugin/</h1>
プラグインのインストール
後述でmy.cnfに設定するので、ここでインストールする必要はないかもしれませんが・・
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so'; Query OK, 0 rows affected (0.42 sec)
プラグインインストール後の確認
- プラグインがローデイングされているか確認
```
mysql> show plugins like 'AUDIT';
+--------------------------+--------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+--------------------+---------+
| AUDIT | ACTIVE | DAEMON | libaudit_plugin.so | GPL |
+--------------------------+--------+--------------------+--------------------+---------+
26 rows in set (0.00 sec)
<li>auditのパラメータが増えているか確認
mysql> show global variables like 'audit%'; +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | audit_checksum | | | audit_delay_cmds | | | audit_delay_ms | 0 | | audit_json_file | OFF | | audit_json_file_flush | OFF | | audit_json_file_sync | 0 | | audit_json_log_file | mysql-audit.json | | audit_json_socket | OFF | | audit_json_socket_name | /tmp/mysql-audit.json.sock | | audit_offsets | | | audit_offsets_by_version | ON | | audit_record_cmds | | | audit_record_objs | | | audit_uninstall_plugin | OFF | | audit_validate_checksum | ON | | audit_validate_offsets_extended | ON | +---------------------------------+----------------------------+ 16 rows in set (0.00 sec)</ul> <h2>設定</h2> <h3>パラメータ設定</h3> 上記のパラメータを設定します。尚、設定内容はこちらを参照しました。 今回は検証の為、ざっくり設定しております <a href="https://github.com/mcafee/mysql-audit/wiki/Configuration" target="_blank">https://github.com/mcafee/mysql-audit/wiki/Configuration</a> <ul> <li>my.cnfの編集 今回は、動作検証のでざっくりとした設定になっているのですが、実運用ではこのままの設定ですと結構なログの量になってしまいますので、audit_record_cmdsでコマンドは色々とフィルタはかけたほうが良いかと思います。監査としては、個人的には更新だけで良いのではないかなあ・・・とおもいます# vi /etc/my.cnf # ****************************************************** # McAfee Audit-Plugin # ****************************************************** plugin-load=AUDIT=libaudit_plugin.so# 監査ログの出力パスを指定 audit_json_log_file=/var/log/mysql/mysqld-audit-log.json # 監査 ソケットファイルの使用(1:ON 0:OFF) audit_json_socket=0 # 監査 ソケットファイルのパス指定 audit_json_socket_name='/var/lib/mysql/mysql-audit.sock'# ログファイルに出力（1:出力 0:出力しない） audit_json_file=1# ログの同期タイミング(0:OSに従い、0以外: 秒？？？） audit_json_file_sync=0# 記録対象のコマンドを指定 # show global status like 'Com_%'; にて、表示されるものが対象のコマンドだと思ってくれて良いです audit_record_cmds=insert,select,update,delete# 記録対象のスキーマまたは、テーブルを指定する # "{"の部分は、SET、FLUSH等の監査対象外をログに取りたい場合に指定 # audit_record_objs=mydb.*,{}<li>設定を反映させます MySQLの再起動をします
# /etc/init.d/mysql restart<li>設定後のパラメータの確認 当たり前だけど、設定が変更されているとおもいます
mysql> show global variables like '%audit%'; +---------------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------------+ | audit_checksum | | | audit_delay_cmds | | | audit_delay_ms | 0 | | audit_json_file | ON | | audit_json_file_flush | OFF | | audit_json_file_sync | 0 | | audit_json_log_file | /var/log/mysql/mysqld_audit_log.json | | audit_json_socket | OFF | | audit_json_socket_name | /var/lib/mysql/mysql-audit.sock | | audit_offsets | | | audit_offsets_by_version | ON | | audit_record_cmds | | | audit_record_objs | | | audit_uninstall_plugin | OFF | | audit_validate_checksum | ON | | audit_validate_offsets_extended | ON | +---------------------------------+--------------------------------------+ 16 rows in set (0.00 sec)<li>補足：コマンドで直接実行の場合 実際、リアルタイムで変更されるのかされないのかはわからないのです。(^_^;)でもエラーは出なかったので載せておきます。
mysql> set global audit_json_file=1 ; -- ログをJSON形式で出力する Query OK, 0 rows affected (0.00 sec)set global audit_json_file_flush=1 ; -- ログローテートの設定、1を指定した時にローテートする Query OK, 0 rows affected (0.00 sec)set global audit_json_file_sync=0 ; -- ログファイルの同期（0:OSに従い 0以外:秒）mysql> set global audit_json_socket_name='/var/lib/mysql/mysql-audit.sock' ; -- socketファイルの指定 Query OK, 0 rows affected (0.01 sec)```
動作確認
実際どのように出力されるか確認してみます。
▼ MySQLでコマンドを実行 ここは、あれです。単純に、auditログが書かれているかどうか確認したかっただけなので、好きなコマンドを打ってくださいませ。
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec) mysql> select * from plugin limit 10; +----------------------+--------------------+ | name | dl | +----------------------+--------------------+ | rpl_semi_sync_master | semisync_master.so | | rpl_semi_sync_slave | semisync_slave.so | | AUDIT | libaudit_plugin.so | +----------------------+--------------------+ 3 rows in set (0.00 sec)
以下の通り、監査ログが出力されております。JSON形式なので、このデータを、fluentdで転送して、ログを一つのサーバに転送しちゃって監理するのに便利かもしれませんね。
▼ 監査ログの確認 上記ｊの監査ログが出力されているのがわかるかと思います。
<h1>cat /var/log/mysql/mysqld-audit-log.json</h1>
{"msg-type":"activity","date":"1367401014808","thread-id":"1","query-id":"1","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1367401068086","thread-id":"2","query-id":"3","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1367401092429","thread-id":"2","query-id":"5","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1367401145285","thread-id":"3","query-id":"88","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1367401266863","thread-id":"1","query-id":"1","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1367401350586","thread-id":"1","query-id":"1","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"select @@version_comment limit 1"}
{"msg-type":"activity","date":"1367401354071","thread-id":"1","query-id":"2","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1367401385511","thread-id":"1","query-id":"31","user":"myuser","priv_user":"myuser","host":"localhost","cmd":"select","objects":[{"db":"mysql","name":"plugin","obj_type":"TABLE"}],"query":"select * from plugin limit 10"}
とまあ、例として適切だったかどうかはわかりませんが、いかがでしょうか？これであれば、DBサーバに過剰な負荷を欠けずに、監査することが可能かと思います。便利！！
