MySQL5.7.21からMySQL Enterprise Auditの機能が拡張されています。
主な変更点は、以下の4つになります。

1) 監査ログの圧縮
2) 監査ログの暗号化
3) JSONフォーマットの追加
4) audit_log_read関数による監査ログの確認

まだ、監査を実施してなくて、個人情報、機密情報、売上利益に関わるデータに監査が必用な場合は、MySQLのオプション機能として利用するを検討しても良いかもしれません。

MEMO: MySQL5.7.21から監査設定用のインストールスクリプトもMyISAMからInnoDBに変更されています。

[root@GA01 data]# cat /usr/local/mysql/share/audit_log_filter_linux_install.sql | grep engine
CREATE TABLE IF NOT EXISTS audit_log_filter(NAME VARCHAR(64) BINARY NOT NULL PRIMARY KEY, FILTER JSON NOT NULL) engine= InnoDB;
CREATE TABLE IF NOT EXISTS audit_log_user(USER VARCHAR(16) BINARY NOT NULL, HOST VARCHAR(60) BINARY NOT NULL, FILTERNAME VARCHAR(64) BINARY NOT NULL, PRIMARY KEY (USER, HOST), FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME)) engine= InnoDB;
[root@GA01 data]# 

※ MySQL Enterprise FirewallのスクリプトもEngineがInnoDBに変更されていました。

5.7.13~5.7.20でEnterprise Auditを利用されている方は、監査定義で利用するテーブルを以下のようにマニュアルでInnoDBに変更する事を推奨となっています。

ALTER TABLE mysql.audit_log_user ENGINE=InnoDB;
ALTER TABLE mysql.audit_log_filter ENGINE=InnoDB;

■ ログフィルター設定(ファイングレインオプション)
ユーザー、テーブル、クエリータイプ(SELECT, INSERT, UPDATE, DELETE等)

root@localhost [mysql]> select * from audit_log_user;
+---------+-----------+------------------+
| USER    | HOST      | FILTERNAME       |
+---------+-----------+------------------+
| admin   | %         | log_confidential |
| admin   | localhost | log_confidential |
| fw_user | localhost | log_confidential |
+---------+-----------+------------------+
3 rows in set (0.00 sec)

root@localhost [mysql]> select * from audit_log_filter;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| NAME             | FILTER                                                                                                                                         |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| log_confidential | {"filter": {"class": {"name": "table_access", "event": {"log": {"field": {"name": "table_name.str", "value": "card_info"}}, "name": "read"}}}} |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

■ MySQL Enterprise Auditのファンクションを利用してJSONのログを読み取る。

audit_log_read_bookmark()
最後に書き込まれた監査ログイベントのブックマークを表すJSON文字列を返します。
このブックマークはaudit_log_read()に渡して、読み込みを開始する場所を指定するのに適しています。

audit_log_read()
監査ログからイベントを読み取り、監査イベントの配列を含むJSON文字列を返します。

■ Audit用ファンクションのインストール


root@localhost [OU]> CREATE FUNCTION audit_log_read_bookmark RETURNS STRING SONAME 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)

root@localhost [OU]> CREATE FUNCTION audit_log_read RETURNS STRING SONAME 'audit_log.so';
Query OK, 0 rows affected (0.00 sec)

/*** Edit my.cnf for adding audit_log_format=JSON ***/

root@localhost [(none)]> SELECT @@global.audit_log_format;
+---------------------------+
| @@global.audit_log_format |
+---------------------------+
| JSON                      |
+---------------------------+
1 row in set (0.00 sec)

root@localhost [(none)]> SELECT * FROM mysql.func WHERE name IN ('audit_log_read_bookmark', 'audit_log_read');
+-------------------------+-----+--------------+----------+
| name                    | ret | dl           | type     |
+-------------------------+-----+--------------+----------+
| audit_log_read          |   0 | audit_log.so | function |
| audit_log_read_bookmark |   0 | audit_log.so | function |
+-------------------------+-----+--------------+----------+
2 rows in set (0.00 sec)

■ 直近のAuditログを読み込む場合

root@localhost [(mysql)]> SET @bookmark = (SELECT audit_log_read_bookmark());
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> select @bookmark;
+-------------------------------------------------+
| @bookmark                                       |
+-------------------------------------------------+
| { "timestamp": "2018-02-13 06:47:23", "id": 0 } |
+-------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(mysql)]> SELECT audit_log_read(@bookmark)\G
*************************** 1. row ***************************
audit_log_read(@bookmark): [ {"timestamp":"2018-02-13 06:47:23","id":0,"class":"table_access","event":"read","connection_id":14,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 5","sql_command":"select"}}, null ]
1 row in set (0.01 sec)

■ 特定のポイント以降のログを読み込む場合

root@localhost [(mysql)]> set @bookmark2 = JSON_OBJECT('timestamp','2018-02-13 06:33:33','id',0);
Query OK, 0 rows affected (0.01 sec)

root@localhost [(mysql)]> SELECT audit_log_read(@bookmark2)\G
*************************** 1. row ***************************
audit_log_read(@bookmark2): [ {"timestamp":"2018-02-13 06:33:33","id":0,"class":"table_access","event":"read","connection_id":8,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 5","sql_command":"select"}}, {"timestamp":"2018-02-13 06:47:09","id":0,"class":"table_access","event":"read","connection_id":9,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 1","sql_command":"select"}}, {"timestamp":"2018-02-13 06:47:12","id":0,"class":"table_access","event":"read","connection_id":10,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 2","sql_command":"select"}}, {"timestamp":"2018-02-13 06:47:15","id":0,"class":"table_access","event":"read","connection_id":11,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 3","sql_command":"select"}}, {"timestamp":"2018-02-13 06:47:17","id":0,"class":"table_access","event":"read","connection_id":12,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 4","sql_command":"select"}}, {"timestamp":"2018-02-13 06:47:23","id":0,"class":"table_access","event":"read","connection_id":14,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 5","sql_command":"select"}}, null ]
1 row in set (0.01 sec)

root@localhost [(none)]> 

■ ログフォーマットの追加

XMLフォーマット(従来からのフォーマット)

[root@GA01 data]# head -n 20 audit.20180124T012252.log
<?xml version="1.0" encoding="UTF-8"?>
<AUDIT>
 <AUDIT_RECORD>
  <TIMESTAMP>2018-01-19T03:39:20 UTC</TIMESTAMP>
  <RECORD_ID>1_2018-01-19T03:39:20</RECORD_ID>
  <NAME>Audit</NAME>
  <SERVER_ID>101</SERVER_ID>
  <VERSION>1</VERSION>
  <STARTUP_OPTIONS>/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=GA01.err --open-files-limit=8192 --pid-file=/usr/local/mysql/data/GA01.pid --socket=/tmp/mysql.sock --port=3306</STARTUP_OPTIONS>
  <OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION>
  <MYSQL_VERSION>5.7.20-enterprise-commercial-advanced-log</MYSQL_VERSION>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2018-01-19T04:48:42 UTC</TIMESTAMP>
  <RECORD_ID>2_2018-01-19T03:39:20</RECORD_ID>
  <NAME>NoAudit</NAME>
  <SERVER_ID>101</SERVER_ID>
 </AUDIT_RECORD>
 <AUDIT_RECORD>
  <TIMESTAMP>2018-01-23T04:17:19 UTC</TIMESTAMP>
[root@GA01 data]# 

JSONフォーマット(MySQL5.7.21以降で追加されたフォーマットオプション)

[root@GA01 data]# head -n 20 audit.log 
[
{ "timestamp": "2018-02-13 05:20:48", "id": 0, "class": "audit", "event": "startup", "connection_id": 0, "startup_data": { "server_id": 101, "os_version": "x86_64-linux-glibc2.12", "mysql_version": "5.7.21-enterprise-commercial-advanced-log", "args": ["/usr/local/mysql/bin/mysqld", "--basedir=/usr/local/mysql", "--datadir=/usr/local/mysql/data", "--plugin-dir=/usr/local/mysql/lib/plugin", "--user=mysql", "--log-error=GA01.err", "--open-files-limit=8192", "--pid-file=/usr/local/mysql/data/GA01.pid", "--socket=/tmp/mysql.sock", "--port=3306" ] } },
{ "timestamp": "2018-02-13 05:26:12", "id": 0, "class": "table_access", "event": "read", "connection_id": 4, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 1", "sql_command": "select" } }[root@GA01 data]# 

■ ログの圧縮


root@localhost [(none)]> show variables like 'audit_log_compression';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| audit_log_compression | NONE  |
+-----------------------+-------+
1 row in set (0.02 sec)

root@localhost [(none)]> 

my.cnfに”audit_log_compression=GZIP”を追加

root@localhost [(none)]> show variables like 'audit_log_compression';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| audit_log_compression | GZIP  |
+-----------------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> 

ログファイルがGZIPされている事が確認出来る

[root@GA01 data]# ls -l audit.*
-rw-r-----. 1 mysql mysql  702  2月  7 20:42 audit.20180207T114235.log
-rw-r-----. 1 mysql mysql  702  2月 13 14:20 audit.20180213T052043.log
-rw-r-----. 1 mysql mysql 1099  2月 13 14:47 audit.20180213T054746.log
-rw-r-----. 1 mysql mysql  624  2月 13 14:58 audit.20180213T055818.log.gz
-rw-r-----. 1 mysql mysql   20  2月 13 14:58 audit.log.gz
[root@GA01 data]# 



[root@GA01 data]# gzip -dc audit.20180213T055818.log.gz | tail
{ "timestamp": "2018-02-13 05:53:28", "id": 0, "class": "table_access", "event": "read", "connection_id": 7, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 10", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:53:39", "id": 0, "class": "table_access", "event": "read", "connection_id": 9, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 5", "sql_command": "select" } },{ "timestamp": "2018-02-13 05:54:37", "id": 0, "class": "table_access", "event": "read", "connection_id": 10, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 5", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:54:38", "id": 0, "class": "table_access", "event": "read", "connection_id": 11, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 5", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:55:36", "id": 0, "class": "table_access", "event": "read", "connection_id": 12, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 5", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:55:37", "id": 0, "class": "table_access", "event": "read", "connection_id": 13, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 5", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:57:28", "id": 0, "class": "table_access", "event": "read", "connection_id": 14, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 12", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:57:31", "id": 0, "class": "table_access", "event": "read", "connection_id": 15, "account": { "user": "fw_user", "host": "localhost" }, "login": { "user": "fw_user", "os": "", "ip": "127.0.0.1", "proxy": "" }, "table_access_data": { "db": "test", "table": "card_info", "query": "select id, fname,lname,cnumber from card_info where id = 2", "sql_command": "select" } },
{ "timestamp": "2018-02-13 05:58:18", "id": 0, "class": "audit", "event": "shutdown", "connection_id": 0, "shutdown_data": { "server_id": 101 } }
][root@GA01 data]# 

■ GZIPされていても、audit_log_readでログファイルを確認する事が可能なようだ。

root@localhost [mysql]> select @bookmark;
+-------------------------------------------------+
| @bookmark                                       |
+-------------------------------------------------+
| { "timestamp": "2018-02-13 07:17:12", "id": 0 } |
+-------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_read(@bookmark)\G
*************************** 1. row ***************************
audit_log_read(@bookmark): [ {"timestamp":"2018-02-13 07:17:12","id":0,"class":"audit","event":"startup","connection_id":0,"startup_data":{"server_id":101,"os_version":"x86_64-linux-glibc2.12","mysql_version":"5.7.21-enterprise-commercial-advanced-log","args":["/usr/local/mysql/bin/mysqld","--basedir=/usr/local/mysql","--datadir=/usr/local/mysql/data","--plugin-dir=/usr/local/mysql/lib/plugin","--user=mysql","--log-error=GA01.err","--open-files-limit=8192","--pid-file=/usr/local/mysql/data/GA01.pid","--socket=/tmp/mysql.sock","--port=3306"]}}, null ]
1 row in set (0.00 sec)

root@localhost [mysql]> SELECT audit_log_read(@bookmark)\G
*************************** 1. row ***************************
audit_log_read(@bookmark): [ {"timestamp":"2018-02-13 07:17:12","id":0,"class":"audit","event":"startup","connection_id":0,"startup_data":{"server_id":101,"os_version":"x86_64-linux-glibc2.12","mysql_version":"5.7.21-enterprise-commercial-advanced-log","args":["/usr/local/mysql/bin/mysqld","--basedir=/usr/local/mysql","--datadir=/usr/local/mysql/data","--plugin-dir=/usr/local/mysql/lib/plugin","--user=mysql","--log-error=GA01.err","--open-files-limit=8192","--pid-file=/usr/local/mysql/data/GA01.pid","--socket=/tmp/mysql.sock","--port=3306"]}}, {"timestamp":"2018-02-13 07:18:33","id":0,"class":"table_access","event":"read","connection_id":5,"account":{"user":"fw_user","host":"localhost"},"login":{"user":"fw_user","os":"","ip":"127.0.0.1","proxy":""},"table_access_data":{"db":"test","table":"card_info","query":"select id, fname,lname,cnumber from card_info where id = 3","sql_command":"select"}}, null ]
1 row in set (0.00 sec)

root@localhost [mysql]> 

■ Auditログの暗号化

AESでAuditログを暗号化する機能が追加された。

root@localhost [(mysql)]> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM 
INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

root@localhost [(none)]> show variables like 'audit_log_encryption';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| audit_log_encryption | AES   |
+----------------------+-------+
1 row in set (0.02 sec)

root@localhost [(none)]> 

Auditログは暗号化されているため、先に複合化しないと読み込めない。

[root@GA01 data]# ls -l audit*.enc
-rw-r-----. 1 mysql mysql 32  2月 13 17:34 audit.log.gz.enc
[root@GA01 data]# 


[root@GA01 data]# gzip -dc audit.20180213T084836.log.gz.enc 

gzip: audit.20180213T084836.log.gz.enc: not in gzip format
[root@GA01 data]# 

参照:
7.5.5 Audit Log Logging Control


MySQL Enterprise Auditのログローテーションについての追加の手法。
合わせて、NFS上に監査ログを設定していて、万が一NFSが一時的に切れてしまった場合の挙動の確認。

1) ログサイズによる自動ローテーション
audit_log_rotate_on_size

2) mysqlutilitiesを利用したローテーション
mysqlauditadmin.exe

上記は、前回のブログ記事で紹介。
MySQL Audit Logのローテーション

今回は、CRON等で定期的に実行して、自分のカスタマイズしたいようにログをシェルでローテーションする方法です。

3) 自分でSHELLを用意して運用する方法

概要はこちらのマニュアルページで紹介しています。
https://dev.mysql.com/doc/refman/5.7/en/audit-log-logging-control.html

By default, audit_log_rotate_on_size=0 and there is no log rotation. In this case, the audit log plugin closes and reopens the log file when the audit_log_flush value changes from disabled to enabled.
If audit_log_rotate_on_size is greater than 0, setting audit_log_flush has no effect. In this case, the audit log plugin closes and reopens its log file whenever a write to the file causes its size to exceed the audit_log_rotate_on_size value.

MySQLのaudit関連設定

parameter

実際に、以下の様にSHELLを作成してログのローテーション確認してみました。
audit.logがリネームされて、新しいaudit.logファイルが再作成されています。


[root@misc01 mysql]# ls -l
合計 396
-rw-r-----. 1 mysql mysql   2813  9月 21 23:19 audit.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# ./audit_log_rotate.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@misc01 mysql]# ls -l
合計 400
-rw-r-----. 1 mysql mysql     47  9月 21 23:19 audit.log
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd

簡易版なので、適宜用途によって加工して利用して下さい。


[root@misc01 mysql]# cat audit_log_rotate.sh 
#!/bin/sh

#######################################
#
#    MySQL Aduit Log Rotate Shell
#
#######################################

TODAY=`date -d 'today' '+%Y-%m-%d'`
AUDIT_LOG=/home/mysql/audit_log_${TODAY}.log

# Archive and rotate audit.log
mv /home/mysql/audit.log ${AUDIT_LOG}

# Flush Audit Log for creating new log file.
mysql -u root -ppassword -e "SET GLOBAL audit_log_flush = ON;"

[root@misc01 mysql]# 


【その他、確認事項】
■ NFS上にaudit.logを配置して、運用中にNFSが切れてしまった場合の挙動の確認。

ログのローテションを確認する為に、一時的にaudit_log_rotate_on_sizeに最小限のサイズを設定しています。


root@localhost [(none)]> show variables like 'audit%';
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| audit_log_buffer_size       | 1048576               |
| audit_log_connection_policy | ALL                   |
| audit_log_current_session   | OFF                   |
| audit_log_exclude_accounts  |                       |
| audit_log_file              | /home/mysql/audit.log |
| audit_log_filter_id         | 0                     |
| audit_log_flush             | OFF                   |
| audit_log_format            | NEW                   |
| audit_log_include_accounts  |                       |
| audit_log_policy            | ALL                   |
| audit_log_rotate_on_size    | 0                     |
| audit_log_statement_policy  | ALL                   |
| audit_log_strategy          | ASYNCHRONOUS          |
+-----------------------------+-----------------------+
13 rows in set (0.00 sec)

root@localhost [(none)]> set global audit_log_rotate_on_size=4096;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]> show variables like 'audit%';
+-----------------------------+-----------------------+
| Variable_name               | Value                 |
+-----------------------------+-----------------------+
| audit_log_buffer_size       | 1048576               |
| audit_log_connection_policy | ALL                   |
| audit_log_current_session   | OFF                   |
| audit_log_exclude_accounts  |                       |
| audit_log_file              | /home/mysql/audit.log |
| audit_log_filter_id         | 0                     |
| audit_log_flush             | OFF                   |
| audit_log_format            | NEW                   |
| audit_log_include_accounts  |                       |
| audit_log_policy            | ALL                   |
| audit_log_rotate_on_size    | 4096                  |
| audit_log_statement_policy  | ALL                   |
| audit_log_strategy          | ASYNCHRONOUS          |
+-----------------------------+-----------------------+
13 rows in set (0.01 sec)

root@localhost [(none)]> 


ログをリネームして一時的にMySQLからファイルを見えなくしています。この間にAudit対象のQueryを実行して、ローテーションサイズを超えてもadit.logが無いので監査ログは書き込まれません。


[root@misc01 mysql]# ls -l
合計 424
-rw-r-----. 1 mysql mysql    504  9月 22 21:52 audit.log
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd

[root@misc01 mysql]# mv audit.log on_purpose_rename_audit.log
[root@misc01 mysql]# ls -l
合計 424
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql    504  9月 22 21:52 on_purpose_rename_audit.log
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# ls -l
合計 424
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql    961  9月 22 21:53 on_purpose_rename_audit.log
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# ls -l
合計 428
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql   6902  9月 22 21:53 on_purpose_rename_audit.log
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd

NFSの接続が戻ったと仮定して、ログを元の名前に戻す。
21:56の段階でaudit.logが無かった時のデータがaudit.log.14745489750144595.xmlに書き込まれている。
ログローテーションのサイズ(4096)をオーバーしているがデータがロストしていない事が確認出来る。
また、新規でaudit.logが作成されている。ローテーションも設定サイズできちんと行われている。
audit.logに書き込めない間は、audit_log_bufferに蓄積されているようです。


[root@misc01 mysql]# mv on_purpose_rename_audit.log audit.log
[root@misc01 mysql]# ls -l
合計 440
-rw-r-----. 1 mysql mysql   1418  9月 22 21:56 audit.log
-rw-r-----. 1 mysql mysql   4466  9月 22 21:52 audit.log.14745487509667319.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487651271064.xml
-rw-r-----. 1 mysql mysql   4169  9月 22 21:52 audit.log.14745487704909257.xml
-rw-r-----. 1 mysql mysql  15594  9月 22 21:56 audit.log.14745489750144595.xml
-rw-r-----. 1 mysql mysql   2822  9月 21 23:19 audit_log_2016-09-21.log
-rwxr-xr-x. 1 root  root     391  9月 21 23:19 audit_log_rotate.sh
-rw-r-----. 1 mysql mysql  98304 11月 18  2015 osc_tablespace01.ibd
drwxrwxr-x. 2 mysql mysql      6  4月 29  2015 perl5
drwxrwxr-x. 2 mysql mysql   4096  4月 29  2015 ssl
-rw-r-----. 1 mysql mysql 163840  9月 19 21:43 user_tablespace01.ibd
-rw-r-----. 1 mysql mysql  65536  4月 22  2015 user_tablespace02.ibd
-rw-r-----. 1 mysql mysql  65536  4月 20  2015 user_tablespace02_8k.ibd
[root@misc01 mysql]# 

nfs


補足:ログに書けない間も、MySQLのステータス変数はカウントアップされている事がaudit_log_current_sizeから確認する事が出来ます。
ファイルを戻したタイミングで、ログがファイルに書き込まれ、audit_log_current_sizeは値が小さくなっている事が確認出来ます。

root@localhost [(none)]> show status like 'audit%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| audit_log_current_size        | 15585 |
| audit_log_event_max_drop_size | 0     |
| audit_log_events              | 1     |
| audit_log_events_buffered     | 0     |
| audit_log_events_filtered     | 0     |
| audit_log_events_lost         | 0     |
| audit_log_events_written      | 59    |
| audit_log_total_size          | 28389 |
| audit_log_write_waits         | 0     |
+-------------------------------+-------+
9 rows in set (0.00 sec)

root@localhost [(none)]> show status like 'audit%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| audit_log_current_size        | 1418  |
| audit_log_event_max_drop_size | 0     |
| audit_log_events              | 1     |
| audit_log_events_buffered     | 0     |
| audit_log_events_filtered     | 0     |
| audit_log_events_lost         | 0     |
| audit_log_events_written      | 62    |
| audit_log_total_size          | 29816 |
| audit_log_write_waits         | 0     |
+-------------------------------+-------+
9 rows in set (0.00 sec)

root@localhost [(none)]>