RDSやAuroraなどMySQLの監査ログをCloudWatch Logs Insightsで整形する

AWS DB MySQL CloudWatch RDS/Aurora

RDSやAuroraなどのMySQLの監査ログ(Auditログ)はCSV形式なのでうまく扱いづらいため、整形してCloudWatchダッシュボードに載せたいなというときや、手軽にログをフィルタリングしたいなというときに便利なCloudWatch Logs Insightsクエリを作ってみました。


概要

RDSやAurora、はたまたEC2内のMySQLの監査ログ(Audit Log)を、CloudWatch Logs Insightsでうまくパースして、見やすく・フィルタリングしやすくします。


前提

  • AWS環境でMySQLを構築していること
    • オンプレや他クラウドのサーバにMySQLを立て、CloudWatchエージェントでAWSにログを転送すればAWS以外での環境にも対応できますが、今回は触れません
  • AuditログがCloudWatch Logsに吐き出されるように設定されていること
    • RDSやAuroraを使用しているときは簡単に設定できます

aws.amazon.com


MySQLの監査ログ(Audit Log)

MariaDB Audit Plugin

RDSやAuroraで提供されているMySQLの監査ログは、MariaDB Audit PluginというMariaDBのプラグインが使用されています。

MariaDBのプラグインなのですがMySQLとも互換性があり、自前でサーバにMySQLを入れて運用する場合は、MariaDBからプラグインを抜いてきてMySQLにインストールして使うのが一般的です。


またこちらの監査ログは、CSV形式となります。

docs.aws.amazon.com

mariadb.com


MySQLの監査プラグインの小ネタ

mysql-audit

以前自分もEC2でMySQLを運用していたときにMariaDB Audit Pluginと、mcafee/mysql-auditというもう一つ別のプラグインを使ったりしていました。

こちらはJSONでログを扱えたり、すごく便利な機能もあるのにメンテナンスされなくなってしまい、結局MariaDB Audit Pluginをメインで使うようになったりしました。

しかしmysql-auditも、最近また少し手が入るようになったのをこの記事書きながら知って驚きました。

github.com


MariaDB Audit PluginのMySQL8.0対応

ただMariaDB Audit Pluginの方も、やはりMySQLではなくあくまでMariaDBのプラグインというだけありMySQLの新バージョンである8.0への対応がされず、どうしても監査ログが欲しければ現状でプラグインに対応しているMySQL5.7で運用する(またはエンタープライズ版を使用する)しかないという状況でモヤモヤしながら運用していました。

そのせいもあり、RDSでもMySQL8.0では監査ログ機能に対応していないという状況が生まれていました。


ところがなんとAWSで、RDSのMySQL8.0でもMariaDB Audit Pluginが対応するという出来事が今年あり、非常に驚いたことを覚えています。


バグレポなど出してました

すごくニッチな界隈なのですが、以前自分はどちらのプロダクトにもバグレポやフィーチャーリクエストなどを出していました。



CloudWatch Logs Insightsで整形する方法

長くなりましたが、ここからが本題です。


MariaDB Audit PluginのログはCSV形式であるため、少々ログとしては扱いづらい点があります。

ここで、このCSV形式のログをうまくパースして、見やすくしたりフィルタリングなどがしやすいように整形してみます。


CloudWatch Logs Insights

  1. CloudWatch Logs Insightsでロググループを指定し、検索したい期間を指定する

    • /aws/rds/cluster/(cluster名)/auditのように、auditというログになります

  2. 以下クエリを入力して実行する

fields @timestamp, @message
| parse @message /^(?<dateTimestamp>[0-9]*),(?<cluster>[^,]*),(?<mysqlUser>[^,]*),(?<ip>[^,]*),(?<threadId>[0-9]*),(?<queryId>[0-9]*),(?<logType>[^,]*),(?<dbName>[^,]*),('(?<query>.*)'|(?<table>.*)|),(?<statusCode>[0-9]*)$/
| filter (logType = 'QUERY' OR logType = 'CONNECT' OR logType = 'DISCONNECT')
| filter mysqlUser != 'rdsadmin'
| filter query != 'SELECT USER()'
| filter query != 'SELECT @@session.tx_read_only'
#| display @timestamp, statusCode, threadId, queryId, mysqlUser, dbName, logType, table, query
| display @timestamp, statusCode, threadId, queryId, mysqlUser, dbName, logType, query
| sort @timestamp asc
| limit 1000


結果イメージ


解説

全体の流れ

  • fields
    • ログから取得するカラムを指定する
  • parse
    • CSV形式をパースして各変数に割り当て
  • filter
    • WHERE文のようなもの
    • 絞りたい条件を指定
  • diplay
    • 表示したいカラムを指定
  • sort
    • どのようにソートするか
  • limit
    • 何件表示するか


fields

CloudWatch Logsは@timestamp、@messageから構成されているため、とりあえず指定します。

@timestampにはログが転送された時刻、@messageにはログ本文が入ります。

fields @timestamp, @message


parse

一番大事なところです。

CSV形式（カンマ区切り）のものをパースして、各変数に値を割り当てます。

| parse @message /^(?<dateTimestamp>[0-9]*),(?<cluster>[^,]*),(?<mysqlUser>[^,]*),(?<ip>[^,]*),(?<threadId>[0-9]*),(?<queryId>[0-9]*),(?<logType>[^,]*),(?<dbName>[^,]*),('(?<query>.*)'|(?<table>.*)|),(?<statusCode>[0-9]*)$/

MariaDB Audit Pluginのログ形式に従って、以下の変数を用意しました。

変数 説明
dateTimestamp CloudWatch Logsのタイムスタンプではなく、DBのAuditログ上で記録された正確な時刻
cluster MySQLがホストされているサーバ名
mysqlUser MySQLユーザ名
ip MySQLユーザのホスト
threadId スレッドID（コネクションID）
queryId クエリID
logType ログの種類
dbName カレントDB名
query クエリ文
table クエリがアクセスするテーブル（logTypeが'READ', 'WRITE'のときに出力）
statusCode クエリ結果のステータスコード


(?<columnName>文字パターン)というように書くことで、正規表現にマッチする文字列が、新たなカラム（変数）に格納されます。


だいたいどれも、数値のものは[0-9]*、そうでないものは[^,]*としていますが、少し違うのがqueryとtableです。


queryとtableは、auditログのCSVフォーマットで言うカンマ区切りで右から2番目に当たる情報から抽出しているカラムなのですが、以下のようにlogTypeによって入る値の意味が変わります。

  • logTypeがCONNECT, DISCONNECT(などSQLを実行しないもの)のとき
    • 空
  • logTypeがQUERYのとき
    • SQLクエリ文
      • シングルクォーテーションで囲まれる
  • logTypeがREAD, WRITEのとき
    • アクセスしたテーブル名
      • シングルクォーテーションで囲まれない


そのため、('(?<query>.*)'|(?<table>.*)|)というようにORパターンで変数展開を行なうことで、1つの情報から複数のカラムへの展開を実現しています。


filter

ここでは絞りたい条件を書きます。

| filter (logType = 'QUERY' OR logType = 'CONNECT' OR logType = 'DISCONNECT')
| filter mysqlUser != 'rdsadmin'
| filter query != 'SELECT USER()'
| filter query != 'SELECT @@session.tx_read_only'



まずlogTypeですが、これは大事で、今回は「SQL文」「接続」「切断」に絞っています。

他にも「READ(SELECTでアクセス)」「WRITE(INSERT, UPDATE, DELETEでアクセス)」などといった種類があり、もしそれらに絞った場合、SQL文の代わりに各クエリがアクセスしたテーブルが出力されます。

そのため、細かいクエリ文の情報は必要ないが、アクセスするテーブル情報のみが知りたい場合はREAD, WRITEで絞ると有用です。

今回はSQLに焦点を当てるため選択しません。

| filter (logType = 'QUERY' OR logType = 'CONNECT' OR logType = 'DISCONNECT')


また、次のmysqlUserはrdsadminというRDS内部で走るrootユーザのようなものです。自分たちの実行したSQLのログとは関係ないので除外します。

| filter mysqlUser != 'rdsadmin'


以下は、MySQL接続する際に、コネクタ内部で勝手に投げられるようなクエリを除外しています。

各コネクタなどに合わせて、またアプリケーション側でinit時に投げているクエリなどを削りたい場合は、ご自由に追加してください。

| filter query != 'SELECT USER()'
| filter query != 'SELECT @@session.tx_read_only'


display

表示させたいカラムのリストを指定します。

今回Aurora Serverlessで運用しているDBでのログを例としたため、cluster（サーバのホスト）やip（MySQLユーザのホスト）は省略しました。

| display @timestamp, statusCode, threadId, queryId, mysqlUser, dbName, logType, query


logTypeでREAD, WRITEも入れる場合、以下のようにtableを追加する必要があります。（記事最初に載せたサンプルではコメントアウト）

| display @timestamp, statusCode, threadId, queryId, mysqlUser, dbName, logType, table, query


sort

どのカラムでソートするか、また昇順・降順（asc | desc）にするかを指定します。

| sort @timestamp asc


limit

表示する件数を指定します。

| limit 1000


使い方

特定のクエリの検索

上記をもとにして様々な検索ができるようになると思います。

  • filter query LIKE 'INSERT'などで絞りたいクエリをLIKE文で指定して特定のクエリログを検索したり
  • filter logType = 'WRITE'などで更新系クエリでアクセスしたテーブルを検索したり
  • filter ( logType = 'CONNECT' OR logType = 'DISCONNECT')などで接続だけに絞ったり
  • filter statusCode != 0でステータスコードを0以外に指定して、失敗したSQLを取得したり


ステータスコードが0以外のとき、何のエラーが起きたのか気になる場合があります。

その場合は、自分のPCやサーバ上で、perror (ステータスコード)と打つと、エラーメッセージが表示されエラーの内容がわかります。

$ perror 1136
MySQL error code MY-001136 (ER_WRONG_VALUE_COUNT_ON_ROW): Column count doesn't match value count at row %ld


ダッシュボードに登録

また、上記のままCloudWatchダッシュボードに登録して、そのままクエリログとして眺めるっていうのもありかなと思います。


最後に

MySQLの監査ログをCloudWatch Logs Insightsで整形する方法をご紹介しました。

さらに色々と工夫することで様々な用途にも使えるかと思いますので、よかったらぜひ参考にしてみてください。