MySQL8.0のエラーログの設定オプションが変わっているので、Fredのブログを確認しながら検証環境で確認しました。

新しいコンポーネントベースのエラーログには次の機能があり、自分の環境にあった設定を選択する事が可能になっています。

1) ログイベントは、フィルタコンポーネントによってフィルタリングして、書き込みに使用できる情報に影響を与えることができます。
2) ログイベントは、sink (writer)コンポーネントによって出力されます。 複数のシンクコンポーネントを有効にして、エラーログの出力を複数の宛先に書き込むことができます。
3) フィルタとライターの組み込みコンポーネントが組み合わされて、デフォルトのエラーログフォーマットが実装されています。
4) ロード可能なライターを使用すると、システムログにロギングできます。
5) ロード可能なライターを使用すると、JSON形式でのログ記録が可能になります。
6) システム変数は、有効にするログコンポーネントとログイベントをフィルタリングするルールを制御します。

log_error_servicesシステム変数は、エラーログに有効にするログコンポーネントを制御します。 その値は、セミコロンで区切られたコンポーネントのリストです。 スペースは重要ではありませんが、リストされた順序でコンポーネントを実行するため、コンポーネントの順序は重要です。

設定変更と動作確認


root@localhost [performance_schema]> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

root@localhost [performance_schema]>  select * from global_variables where VARIABLE_NAME like 'log_error_%';
+---------------------+----------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                         |
+---------------------+----------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal |
| log_error_verbosity | 2                                      |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]> show variables like '%plugin%';
+-------------------------------+------------------------------+
| Variable_name                 | Value                        |
+-------------------------------+------------------------------+
| default_authentication_plugin | mysql_native_password        |
| plugin_dir                    | /usr/local/mysql/lib/plugin/ |
+-------------------------------+------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]> system ls /usr/local/mysql/lib/plugin/component_log*
/usr/local/mysql/lib/plugin/component_log_filter_dragnet.so
/usr/local/mysql/lib/plugin/component_log_sink_json.so
/usr/local/mysql/lib/plugin/component_log_sink_syseventlog.so
/usr/local/mysql/lib/plugin/component_log_sink_test.so
root@localhost [performance_schema]> 

JSONフォーマットのログを利用したいので、JSON用のコンポーネントを追加しています。
メモ:log_errorがstderrの場合、JSONライターはコンソールにログを書き込みます。

root@localhost [performance_schema]> INSTALL COMPONENT 'file://component_log_sink_json';
Query OK, 0 rows affected (0.32 sec)

root@localhost [performance_schema]> SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';
Query OK, 0 rows affected (0.00 sec)

root@localhost [performance_schema]> select * from global_variables where VARIABLE_NAME like 'log_error_%';
+---------------------+-------------------------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                                        |
+---------------------+-------------------------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal; log_sink_json |
| log_error_verbosity | 2                                                     |
+---------------------+-------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]> select * from global_variables where VARIABLE_NAME like 'log_error';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| log_error     | ./GA02.err     |
+---------------+----------------+
1 row in set (0.00 sec)

root@localhost [performance_schema]> restart;
Query OK, 0 rows affected (0.01 sec)


MySQLを再起動して、JSONフォーマットのエラーログが追加されているか確認。
log_sink_internalとlog_sink_jsonの2つが順に列挙されている為、エラーログは通常のエラーとJSONフォーマットのエラーログの2つが出力されています。

エラーログファイルの確認


root@localhost [performance_schema]> system ls /usr/local/mysql/data/GA02*
/usr/local/mysql/data/GA02.err  /usr/local/mysql/data/GA02.err.00.json  /usr/local/mysql/data/GA02.pid
root@localhost [performance_schema]> 

通常のエラーログ

[root@GA02 admin]# head /usr/local/mysql/data/GA02.err
2018-02-23T03:21:22.117236Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc-log) starting as process 3664 ...
2018-02-23T03:21:23.232801Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-23T03:21:23.357209Z 0 [System] [MY-010931] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL).
2018-02-23T03:25:49.322260Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete.
2018-02-23T03:25:55.462916Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc-log) starting as process 3841 ...
2018-02-23T03:25:56.179429Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-23T03:25:56.195172Z 0 [System] [MY-010931] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL).
2018-02-23T04:11:46.558480Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete.
2018-02-23T04:11:47.891177Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc-log) starting as process 4606 ...
2018-02-23T04:11:48.552514Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.

JSONフォーマットのエラーログ

[root@GA02 admin]# head /usr/local/mysql/data/GA02.err.00.json 
{ "prio" : 0, "err_code" : 11086, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "sql_restart_server.cc", "function" : "execute", "msg" : "Received RESTART from user root.  Restarting mysqld (Version: 8.0.4-rc-log).", "time" : "2018-03-14T01:08:33.576059Z", "thread" : 7, "err_symbol" : "ER_RESTART_RECEIVED_INFO", "label" : "System" }
{ "prio" : 2, "err_code" : 10909, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "mysqld.cc", "function" : "operator()", "msg" : "/usr/local/mysql/bin/mysqld: Forcing close of thread 7  user: 'root'.", "time" : "2018-03-14T01:08:35.882770Z", "err_symbol" : "ER_FORCE_CLOSE_THREAD", "label" : "Warning" }
{ "prio" : 0, "err_code" : 10910, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "mysqld.cc", "function" : "clean_up", "msg" : "/usr/local/mysql/bin/mysqld: Shutdown complete.", "time" : "2018-03-14T01:08:36.834869Z", "err_symbol" : "ER_SERVER_SHUTDOWN_COMPLETE", "label" : "System" }
{ "log_type" : 1, "prio" : 0, "err_code" : 10931, "msg" : "/usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL).", "time" : "2018-03-14T01:08:39.000803Z", "err_symbol" : "ER_SERVER_STARTUP_MSG", "SQL_state" : "HY000", "label" : "System" }
[root@GA02 admin]# 

以下、マニュアル抜粋
log_filter_internal:優先度に基づくエラーログのフィルタリング
エラーログの冗長性制御は、エラーイベントの優先度に基づいたログフィルタリングの簡単な形式です。 これは、log_filter_internalログフィルタコンポーネントによって実装されます。
log_filter_internalがエラー・ログを対象とするエラー、警告、およびメモイベントを許可または抑制する方法に影響を与えるには、log_error_verbosityシステム変数を設定します。
log_filter_internalはデフォルトで組み込まれ、有効になっていますが、無効にするとlog_error_verbosityの変更は無効です。
許可されるlog_error_verbosityの値は、1(エラーのみ)、2(エラーと警告)、3(エラー、警告、およびメモ)

log_filter_dragnet:ルールベースのエラーログのフィルタリング
log_filter_dragnetログフィルタコンポーネントは、ユーザ定義のルールに基づいてログフィルタリングを有効にします。 適用可能なルールを定義するにはdragnet.log_error_filter_rulesシステム変数を設定します。
詳細:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_dragnet.log_error_filter_rules

log_filter_dragnetフィルタを有効にするには、まずフィルタコンポーネントをロードしてから、log_error_services値を変更します。

log_filter_internal:優先度に基づくエラーログのフィルタリング
エラーログの冗長性制御は、エラーイベントの優先度に基づいたログフィルタリングの簡単な形式です。 これは、log_filter_internalログフィルタコンポーネントによって実装されます。 log_filter_internalがエラー・ログを対象とするエラー、警告、およびメモイベントを許可または抑制する方法に影響を与えるには、log_error_verbosityシステム変数を設定します。 log_filter_internalはデフォルトで組み込まれ、有効になっていますが、無効にするとlog_error_verbosityの変更は無効です。

log_sink_internal、log_sink_test:これらのライターは、file_nameに書き込みます
log_sink_json:log_error_services値で指定されたこのライターの連続インスタンスは、file_nameという名前のファイルと、番号付きの.NN.json接尾辞:file_name.00.json、file_name.01.jsonなどに書き込みます。
詳細:
https://dev.mysql.com/doc/refman/8.0/en/error-log-json.html

log_sink_syseventlog:このライターは、log_error値に関係なく、システムログに書き込みます。
log_sink_internal、log_sink_json、log_sink_test:これらのライターはコンソールに書き込みます。

参照:5.4.2 The Error Log 
https://dev.mysql.com/doc/refman/8.0/en/error-log.html


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


MySQL8.0.4 RCがリリースされたので、その中でJSON関連の関数JSON_TABLEがリリースされていたので、合わせて挙動を確認してみました。
MySQLにはJSON関数が30弱ありますが、便利なJSON関数が増えてきているので、これまで以上にNOSQL関連データを扱う処理の選択肢として活用出来そうです。

MySQL 8.0.4 Release Candidateの変更点
参照:https://mysqlserverteam.com/the-mysql-8-0-4-release-candidate-is-available/

多くの変更があるので、上記ブログを確認頂いた方が良さそうです。
一部抜粋:
Security:
caching_sha2_password default authentication mechanism
Dynamic Linking of OpenSSL in MySQL Server(yaSSL->OpenSSL)
JSON Functions:
JSON Table Functions
RLIKE/REGEXP:
ICU library to handle RLIKE/REGEXP
SQL DIGEST:
STATEMENT_DIGEST() and STATEMENT_DIGEST_TEXT()
Tablespaces:
ibd and system tablespace files can be moved from one location to another while the server is offline.

and so on ….. Pleases check mysqlserverteam blog.

JSON_TABLEの検証

JSON_TABLE詳細:
WL#8867: Add JSON table functions
https://dev.mysql.com/worklog/task/?id=8867

MySQL8.0.4をDockerにて起動

docker run --name mysql84 -v /docker/docker84:/var/lib/mysql -v /docker/option84:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0



[root@DockerHost docker]# docker run --name mysql84 -v /docker/docker84:/var/lib/mysql -v /docker/option84:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
f314f67d547db50b89be395555fccf2c54dc1d390932a9666c7bfb40da25de1d
[root@DockerHost docker]# 

JSONデータを作成して、JSON_TABLE関数を利用してデータを抽出。
SONデータをそのまま、通常のリレーショナルなテーブルから列を抽出するように扱う事が出来る。JSONデータにWHERE句を利用した抽出も可能。おそらく、JSONデータと生成列を活用してINDEXを利用して参照した方が早いとは思いますが、使いどころは色々とあるかと思います。

[root@DockerHost docker]# docker exec -it mysql84 mysql --default-character-set=utf8mb4 -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
5 rows in set (0.13 sec)

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.01 sec)

mysql> 



mysql> CREATE TABLE `T_JSON` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `body` json DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO T_JSON(body) VALUES ('{"id":1,"name":"washing machine","price":10000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO T_JSON(body) VALUES ('{"id":2,"name":"TV","price":30000,"Conditions":["USED",2013]}');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO T_JSON(body) VALUES ('{"id":3,"name":"refrigerator","price":50000,"Conditions":["NEW",2015]}');
Query OK, 1 row affected (0.02 sec)


mysql> select * from T_JSON;
+----+-----------------------------------------------------------------------------------+
| id | body                                                                              |
+----+-----------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "washing machine", "price": 10000, "Conditions": ["NEW", 2015]} |
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]}             |
|  3 | {"id": 3, "name": "refrigerator", "price": 50000, "Conditions": ["NEW", 2015]}    |
+----+-----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 


mysql> select * from T_JSON, JSON_TABLE(body,"$" COLUMNS
    -> ( 
    ->  product_name varchar(20) PATH "$.name",
    ->  product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+----+-----------------------------------------------------------------------+--------------+---------------+
| id | body                                                                  | product_name | product_price |
+----+-----------------------------------------------------------------------+--------------+---------------+
|  2 | {"id": 2, "name": "TV", "price": 30000, "Conditions": ["USED", 2013]} | TV           | 30000         |
+----+-----------------------------------------------------------------------+--------------+---------------+
1 row in set (0.00 sec)

mysql> 


mysql> select products.* from T_JSON,JSON_TABLE(body,"$" COLUMNS
    -> (
    -> product_name varchar(20) PATH "$.name",
    -> product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+--------------+---------------+
| product_name | product_price |
+--------------+---------------+
| TV           | 30000         |
+--------------+---------------+
1 row in set (0.00 sec)


mysql> select products.* from T_JSON,JSON_TABLE(body,"$" COLUMNS
    -> (
    -> id for ordinality,
    -> product_name varchar(20) PATH "$.name",
    -> product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+------+--------------+---------------+
| id   | product_name | product_price |
+------+--------------+---------------+
|    1 | TV           | 30000         |
+------+--------------+---------------+
1 row in set (0.01 sec)

mysql> 

mysql> explain select products.* from T_JSON,JSON_TABLE(body,"$" COLUMNS
    -> (
    -> product_name varchar(20) PATH "$.name",
    -> product_price varchar(10) PATH "$.price")
    -> ) AS products
    -> where products.product_name in ('TV');
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                       |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | T_JSON   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    2 |   100.00 | NULL                                        |
|  1 | SIMPLE      | products | NULL       | ref  | <auto_key0>   | <auto_key0> | 83      | const |    1 |   100.00 | Table function: json_table; Using temporary |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>  

WORKLOG抜粋:
It’s used to extract data from a JSON document and form a relational table, that could be processed further using SQL. It’s a virtual table,
in this sense it’s like a derived table, with only difference in how result data is obtained. Just like a derived table, it’s specified in the FROM
clause, uses tmp table to store its result, and is able to provide generated indexes for ref access.

JSON_TABLEマニュアル
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

Please check additional details for MySQL8.0
http://mysqlserverteam.com/


MySQL5.7で約20程のJSON関数が追加されていましたが、MySQL8.0においても更に追加でJSON関数が加えられているので、基本的な動作のみを確認しています。JSON_ARRAYAGG(), JSON_OBJECTAGG(),JSON_PRETTY()

MySQL5.7 JSON関数マニュアル
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

MySQL5.7のJSONの概要に関しては、こちらにて資料がダウンロード可能です。https://www.mysql.com/jp/why-mysql/presentations/mysql-json-201701-ja/

まだ、MySQL8.0はDMRなので、これからまだ仕様が変わる部分がある事はご了承ください。

確認バージョン


mysql> select now(),@@version;     
+---------------------+-----------+
| now()               | @@version |
+---------------------+-----------+
| 2017-05-31 19:50:46 | 8.0.1-dmr |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql> 

WL#7987 : JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987

Add aggregation functions to generate JSON arrays and objects. This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.

mysql> select body from T_JSON_DOC where id in (1,2);              
+---------------------------------------------------------------------------------+
| body                                                                            |
+---------------------------------------------------------------------------------+
| {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_ARRAYAGG(body)                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select id,body from T_JSON_DOC where id in(1,2);            
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id,body)                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,body from T_JSON_DOC;                                                
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
|  3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}              |
|  4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}             |
|  5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}   |
+----+---------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price
": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "pri
ce": 25000, "Conditions": ["NEW", 2015, "January"]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
2 rows in set (0.01 sec)

mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
2 rows in set (0.00 sec)

mysql>
 

WL#9191: JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191

User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).

This functionality is available in both PHP and PostgreSQL under the name “pretty”:


mysql> select body from T_JSON_DOC where id = 1\G             
*************************** 1. row ***************************
body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}
1 row in set (0.00 sec)

mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
json_pretty(body): {
  "id": 1,
  "name": "",
  "price": 10000,
  "Conditions": [
    "NEW",
    2015,
    "Excellent"
  ]
}
1 row in set (0.00 sec)

mysql> 

その他のJSON関数(運用向け)

JSON_STORAGE_FREE(json_val)
For a JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET() or JSON_REPLACE(). The argument can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.

※Updating the column without using JSON_SET() (or JSON_REPLACE()) means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0.

JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.


mysql> SELECT 
    ->     jcol, 
    ->     JSON_STORAGE_SIZE(jcol) AS Size, 
    ->     JSON_STORAGE_FREE(jcol) AS Free 
    -> FROM jtable;

JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.


SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C, 
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;