MySQL5.7までのSHOW SLAVE STATUSだけでは分からない事が多かったけど、MySQL8.0のSHOW SLAVE STATUSは少し改善されていた。
マスター側で負荷をかけて、スレーブの状態を確認した時にスレーブ側で”Systetm lock”という状態になっていて、詳細を確認する為にPerformance Schemaを確認してみた。
MySQL8.0からはPerformance_Schemaを確認しなくても”Slave_SQL_Running_State”で状態が確認出来るようになっている。
以下、MySQL8.0で確認したログですが、MySQL5.7では”System lock”だった状態が、MySQL8.0では”Applying batch of row changes (update)”になっています。

[admin@misc02 ~]$ cat repli_log | grep Slave_SQL_Running_State
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Applying batch of row changes (update)
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[admin@misc02 ~]$

詳細: WL#7364: RBR: Enhanced Applier Thread Progress Details
https://dev.mysql.com/worklog/task/?id=7364

以下、MySQL5.7のケース
マスター側でmysqlslapで負荷をかけると、スレーブ側がsystem lockになっている。
スレーブ側でトランザクション実行して、UPLOCKかけて参照しても、同じsystem lockになる。
これだと、I/O Threadに遅延が無いことは分かるが、Applier Threadの状態が良く分からない。

[ON MASTER] – MySQL5.7

root@localhost [REPLI]> show create table T_SLAP01\G
*************************** 1. row ***************************
       Table: T_SLAP01
Create Table: CREATE TABLE `T_SLAP01` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `n_time` varchar(30) DEFAULT NULL,
  `s_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [REPLI]> 

[root@misc01 admin]# cat mysqlslap_exsisting_table.sh 
/usr/local/mysql/bin/mysqlslap -u root -p -S /tmp/mysql.sock --create-schema=REPLI --no-drop -c 10 -i 1000 -q 'INSERT INTO REPLI.T_SLAP01(n_time,s_time) values(now(3),sysdate(6))' 
[root@misc01 admin]# 

[root@misc01 admin]# ./mysqlslap_exsisting_table.sh 
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.046 seconds
        Minimum number of seconds to run all queries: 0.018 seconds
        Maximum number of seconds to run all queries: 0.135 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

[root@misc01 admin]# 

[ON SLAVE]
上記マスターで負荷をかけた場合の、スレーブ側の状態。 “System lock”という状態になっている。ただし、この状態はApplier Threadが止まっている訳では無く、何らかの処理を行っている状態の場合もある。


root@localhost [REPLI]> show processlist; show engine innodb status\G show slave status\G
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
| Id  | User            | Host         | db    | Command | Time | State                            | Info             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
|   1 | system user     |              | NULL  | Connect |  888 | Waiting for master to send event | NULL             |
|   2 | system user     |              | NULL  | Connect |   -1 | System lock                      | NULL             |
|   3 | event_scheduler | localhost    | NULL  | Daemon  |  887 | Waiting on empty queue           | NULL             |
|  13 | root            | localhost    | REPLI | Query   |    0 | starting                         | show processlist |
|  29 | admin           | Labs01:44555 | mysql | Sleep   |    3 |                                  | NULL             |
| 763 | admin           | Labs01:47755 | mysql | Sleep   |   55 |                                  | NULL             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
6 rows in set (0.00 sec)

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-03-23 15:32:58 0x7fcdb65f0700 INNODB MONITOR OUTPUT
=====================================
<SNIP>
------------
TRANSACTIONS
------------
Trx id counter 756832
Purge done for trx's n:o < 756831 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421997346109728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346107904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 756831, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 140521468139264, query id 69415 System lock

<SNIP>

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=8340, Main thread ID=140522077779712, state: sleeping
Number of rows inserted 31953, updated 30756, deleted 0, read 32044
72.99 inserts/s, 72.99 updates/s, 0.00 deletes/s, 72.99 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002848
          Read_Master_Log_Pos: 59249068
               Relay_Log_File: misc02-relay-bin.000766
                Relay_Log_Pos: 12937379
        Relay_Master_Log_File: mysql-bin.002848
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
<SNIP>
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
<SNIP>
1 row in set (0.00 sec)

root@localhost [REPLI]> 

SlaveのPerfomance_Schemaの状態。
Replication SlaveのIDで確認してみると、statement/sql/beginという状態である事が分かる。

root@localhost [REPLI]> SELECT  event_id, event_name, timer_wait, lock_time FROM performance_schema.events_statements_history_long WHERE thread_id =  @sql_thread;
+----------+---------------------+------------+-----------+
| event_id | event_name          | timer_wait | lock_time |
+----------+---------------------+------------+-----------+
|    10003 | statement/sql/begin |  175076000 |         0 |
+----------+---------------------+------------+-----------+
1 row in set (0.01 sec)


root@localhost [REPLI]> SELECT event_name, count_star, sum_timer_wait FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE thread_id =  @sql_thread AND sum_timer_wait > 0 ORDER BY sum_timer_wait DESC;
+--------------------------------------+------------+----------------+
| event_name                           | count_star | sum_timer_wait |
+--------------------------------------+------------+----------------+
| wait/io/table/sql/handler            |      10002 | 52235951612404 |
| wait/io/file/innodb/innodb_log_file  |      20012 | 24423316510586 |
| wait/io/file/sql/binlog              |      10014 |   372515866246 |
| wait/io/file/innodb/innodb_data_file |         29 |   158702658238 |
| wait/io/file/sql/relaylog            |      12641 |   144454610798 |
| wait/io/file/sql/binlog_index        |         30 |    62816598186 |
| wait/lock/table/sql/handler          |      10003 |    51676533966 |
| wait/io/file/sql/misc                |          3 |      116404090 |
+--------------------------------------+------------+----------------+

SLAVE側のSystem Lockの状態はRelay Logの適用から発生するトランザクションでした。

備考:以下のように、スレーブ側で参照ロックをかけた場合の状況もSystem Lock。
補足:トランザクションでロックをかけたセッションで、SYSスキーマを利用した為、sys.innodb_lock_waitsの値がおかしい。

違うセッションであれば、sys.innodb_lock_waitsも問題無い。

上記はバグでは無いですが、分かり難いのでスクリプト変更しました。(MySQL8.0では利用出来ません)
https://bugs.mysql.com/file.php?id=26694&bug_id=72131

Please Enjoy Replication with MySQL8.0 soon…..


MySQL8.0における文字セットの変更:
デフォルトの文字セットがlatin1からutf8mb4に変更されます。 これらのシステム変数は次のような影響を受けます。character_set_serverおよびcharacter_set_databaseシステム変数のデフォルト値は、latin1からutf8mb4に変更。collation_serverおよびcollation_databaseシステム変数のデフォルト値は、latin1_swedish_ciからutf8mb4_0900_ai_ciに変更。
MySQL8.0では、Unicode9までアップグレードされています、またutf8mb4(4byte)では絵文字を含む文字も処理する事が出来る為、バージョンアップと共に文字コードや照合順序を変更される事もあるかと思います。簡単ですが、手順を再確認して見ました。

MySQL8.0にアップグレードする前に,mysqlsh8.xのアップグレード確認ユーティリティでチェックするのもお勧めです。文字セットも確認してくれます。

MySQL8.0における、基本的な機能の変更点
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

■ スキーマの文字セット変換

mysql -uroot -p information_schema -e "select SCHEMA_NAME from SCHEMATA where DEFAULT_CHARACTER_SET_NAME <> 'utf8mb4' and SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')" --batch --skip-column-names | xargs -I{} echo 'alter SCHEMA `'{}'` DEFAULT CHARSET utf8mb4;' > alter_schema_default_change.sql

■ テーブル文字セット若しくは、テーブルと列の文字セット変換
※ 状況に応じて使い分けて下さい。

mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND character_set_name <> 'utf8mb4' AND T.table_schema = '<DATABASE>'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` DEFAULT CHARSET utf8mb4;' > alter_tables_default_change.sql
    or 
mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND character_set_name <> 'utf8mb4' AND T.table_schema = '<DATABASE>'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;' > alter_tables_convert.sql

■ 変換後の確認


select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.COLUMNS where CHARACTER_SET_NAME <> 'utf8mb4';

■ Schemaの文字セット(変更前)


[information_schema]> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA where SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys');
+--------------------+----------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| AdventureWorks2012 | utf8                       |
| BENCHMARK          | utf8                       |
| GIS                | utf8mb4                    |
| IOT                | utf8mb4                    |
| MYDB               | utf8mb4                    |
| OU                 | utf8mb4                    |
| PrivDB             | utf8                       |
| RewriteA           | utf8                       |
| audit_information  | utf8mb4                    |
| info               | utf8mb4                    |
| jposm              | utf8mb4                    |
| myosm              | utf8mb4                    |
| partitioning       | utf8mb4                    |
| test               | latin1                     |
| world              | utf8                       |
+--------------------+----------------------------+
15 rows in set (0.00 sec)


[root@GA01 convert]# mysql -uroot -p information_schema -e "select SCHEMA_NAME from SCHEMATA where DEFAULT_CHARACTER_SET_NAME <> 'utf8mb4' and SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')" --batch --skip-column-names | xargs -I{} echo 'alter SCHEMA `'{}'` DEFAULT CHARSET utf8mb4;' > alter_schema_default_change.sql
Enter password: 
[root@GA01 convert]# cat alter_schema_default_change.sql 
alter SCHEMA `AdventureWorks2012` DEFAULT CHARSET utf8mb4;
alter SCHEMA `BENCHMARK` DEFAULT CHARSET utf8mb4;
alter SCHEMA `PrivDB` DEFAULT CHARSET utf8mb4;
alter SCHEMA `RewriteA` DEFAULT CHARSET utf8mb4;
alter SCHEMA `test` DEFAULT CHARSET utf8mb4;
alter SCHEMA `world` DEFAULT CHARSET utf8mb4;

[root@GA01 convert]# mysql -u root -p < alter_schema_default_change.sql 
Enter password: 
[root@GA01 convert]# 

スキーマの文字セットが変換された事を確認


[information_schema]> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA where SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys');
+--------------------+----------------------------+
| SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| AdventureWorks2012 | utf8mb4                    |
| BENCHMARK          | utf8mb4                    |
| GIS                | utf8mb4                    |
| IOT                | utf8mb4                    |
| MYDB               | utf8mb4                    |
| OU                 | utf8mb4                    |
| PrivDB             | utf8mb4                    |
| RewriteA           | utf8mb4                    |
| audit_information  | utf8mb4                    |
| info               | utf8mb4                    |
| jposm              | utf8mb4                    |
| myosm              | utf8mb4                    |
| partitioning       | utf8mb4                    |
| test               | utf8mb4                    |
| world              | utf8mb4                    |
+--------------------+----------------------------+
15 rows in set (0.00 sec)

utf8mb4以外のスキーマ(データベース)の変換スクリプト作成と実行

■ Tableの変換(テーブルのDefault文字セットのみ)

SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name 
FROM information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C
WHERE C.collation_name = T.table_collation AND T.table_schema = "<DATABASE>";

[information_schema]> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` C WHERE C.collation_name = T.table_collation   AND T.table_schema = "AdventureWorks2012" limit 5;
+--------------------+-----------------+-----------------+--------------------+
| TABLE_SCHEMA       | TABLE_NAME      | TABLE_COLLATION | character_set_name |
+--------------------+-----------------+-----------------+--------------------+
| AdventureWorks2012 | AWBuildVersion  | utf8_general_ci | utf8               |
| AdventureWorks2012 | Address         | utf8_general_ci | utf8               |
| AdventureWorks2012 | AddressType     | utf8_general_ci | utf8               |
| AdventureWorks2012 | BillOfMaterials | utf8_general_ci | utf8               |
| AdventureWorks2012 | BusinessEntity  | utf8_general_ci | utf8               |
+--------------------+-----------------+-----------------+--------------------+
5 rows in set (0.01 sec)

テーブルの文字セット変換スクリプト作成と実行

[root@GA01 convert]# mysql -uroot -p AdventureWorks2012 -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` DEFAULT CHARSET utf8mb4;' > alter_tables_default_change.sql
Enter password: 
[root@GA01 convert]# cat alter_tables_default_change.sql 
alter table `AWBuildVersion` DEFAULT CHARSET utf8mb4;
<SNIP>
alter table `WorkOrderRouting` DEFAULT CHARSET utf8mb4;
[root@GA01 convert]# mysql -u root -p  AdventureWorks2012 < alter_tables_default_change.sql 
Enter password: 
[root@GA01 convert]# 

変換結果としては、問題無くテーブルのDefault文字セットはutf8mb4に変換されている。

[information_schema]> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` C WHERE C.collation_name = T.table_collation   AND T.table_schema = "AdventureWorks2012" limit 5;
+--------------------+-----------------+--------------------+--------------------+
| TABLE_SCHEMA       | TABLE_NAME      | TABLE_COLLATION    | character_set_name |
+--------------------+-----------------+--------------------+--------------------+
| AdventureWorks2012 | AWBuildVersion  | utf8mb4_general_ci | utf8mb4            |
| AdventureWorks2012 | Address         | utf8mb4_general_ci | utf8mb4            |
| AdventureWorks2012 | AddressType     | utf8mb4_general_ci | utf8mb4            |
| AdventureWorks2012 | BillOfMaterials | utf8mb4_general_ci | utf8mb4            |
| AdventureWorks2012 | BusinessEntity  | utf8mb4_general_ci | utf8mb4            |
+--------------------+-----------------+--------------------+--------------------+
5 rows in set (0.01 sec)

但し、上記のようにテーブルのDefault文字セットを変換しただけの場合、以下の様にテーブルのカラムなどの文字コードは変換されていない。


[AdventureWorks2012]> show create table Store\G
*************************** 1. row ***************************
       Table: Store
Create Table: CREATE TABLE `Store` (
  `BusinessEntityID` int(11) NOT NULL COMMENT 'Primary key. Foreign key to Customer.BusinessEntityID.',
  `Name` varchar(100) NOT NULL COMMENT 'Name of the store.',
  `SalesPersonID` int(11) DEFAULT NULL COMMENT 'ID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID.',
  `Demographics` text CHARACTER SET utf8 COMMENT 'Demographic informationg about the store such as the number of employees, annual sales and store type.',
  `rowguid` varchar(64) CHARACTER SET utf8 NOT NULL COMMENT 'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.',
  `ModifiedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the record was last updated.',
  PRIMARY KEY (`BusinessEntityID`),
  UNIQUE KEY `rowguid` (`rowguid`),
  UNIQUE KEY `AK_Store_rowguid` (`rowguid`),
  KEY `IX_Store_SalesPersonID` (`SalesPersonID`),
  KEY `PXML_Store_Demographics` (`Demographics`(255)),
  CONSTRAINT `FK_Store_BusinessEntity_BusinessEntityID` FOREIGN KEY (`BusinessEntityID`) REFERENCES `BusinessEntity` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Store_SalesPerson_SalesPersonID` FOREIGN KEY (`SalesPersonID`) REFERENCES `SalesPerson` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customers (resellers) of Adventure Works products.'
1 row in set (0.00 sec)

■ テーブルのデフォルトの文字セットおよびすべての文字カラム (CHAR、VARCHAR、TEXT) を新しい文字セットに変更するには、次のようなステートメントを使用。
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
注意:このステートメントでは、すべての文字カラムの照合順序も変更されます。使用する照合順序を示す COLLATE 句を指定しない場合、このステートメントは、その文字セットのデフォルトの照合順序を使用します。この照合順序が目的とするテーブル使用に適していない (たとえば、大文字と小文字が区別される照合順序から大文字と小文字が区別されない照合順序に変更されてしまう) 場合は、照合順序を明示的に指定します。

[root@GA01 convert]# mysql -uroot -p AdventureWorks2012 -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;' > alter_tables_convert.sql
Enter password: 
[root@GA01 convert]# cat alter_tables_convert.sql 
alter table `AWBuildVersion` convert to character set utf8mb4;
alter table `Address` convert to character set utf8mb4;
<SNIP>
alter table `WorkOrderRouting` convert to character set utf8mb4;
[root@GA01 convert]# 

以下のテーブルは、文字セットと照合順序変更に伴いFK制約に引っかかってしまったので、マニュアルで対応する。

[root@GA01 convert]# mysql -u root -p AdventureWorks2012 < alter_tables_convert.sql
Enter password:
ERROR 1832 (HY000) at line 34: Cannot change column ‘DocumentNode’: used in a foreign key constraint ‘FK_ProductDocument_Document_DocumentNode’
[root@GA01 convert]#

FKを一次的にOFFにして、対象テーブルをマニュアル変換


[AdventureWorks2012]> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

[AdventureWorks2012]> alter table `ProductDocument` convert to character set utf8mb4;alter table `Product` convert to character set utf8mb4;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

[AdventureWorks2012]> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

該当テーブルを変換後は問題無く、スクリプトでまとめて変更する事が出来た。全て、UTF8MB4に統一された事も確認


[root@GA01 convert]# mysql -u root -p AdventureWorks2012 < alter_tables_convert.sql 
Enter password: 
[root@GA01 convert]# 


root@localhost [AdventureWorks2012]> show create table Store\G
*************************** 1. row ***************************
       Table: Store
Create Table: CREATE TABLE `Store` (
  `BusinessEntityID` int(11) NOT NULL COMMENT 'Primary key. Foreign key to Customer.BusinessEntityID.',
  `Name` varchar(100) NOT NULL COMMENT 'Name of the store.',
  `SalesPersonID` int(11) DEFAULT NULL COMMENT 'ID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID.',
  `Demographics` mediumtext COMMENT 'Demographic informationg about the store such as the number of employees, annual sales and store type.',
  `rowguid` varchar(64) NOT NULL COMMENT 'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.',
  `ModifiedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the record was last updated.',
  PRIMARY KEY (`BusinessEntityID`),
  UNIQUE KEY `rowguid` (`rowguid`),
  UNIQUE KEY `AK_Store_rowguid` (`rowguid`),
  KEY `IX_Store_SalesPersonID` (`SalesPersonID`),
  KEY `PXML_Store_Demographics` (`Demographics`(255)),
  CONSTRAINT `FK_Store_BusinessEntity_BusinessEntityID` FOREIGN KEY (`BusinessEntityID`) REFERENCES `BusinessEntity` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_Store_SalesPerson_SalesPersonID` FOREIGN KEY (`SalesPersonID`) REFERENCES `SalesPerson` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customers (resellers) of Adventure Works products.'
1 row in set (0.00 sec)

■■■ 変換前と後のデータ確認 ■■■

変換前

 

[world]> select name,CountryCode,hex(weight_string(name)),hex(weight_string(CountryCode)) from City3 where CountryCode = '日本' limit 3;
+--------+-------------+--------------------------+---------------------------------+
| name   | CountryCode | hex(weight_string(name)) | hex(weight_string(CountryCode)) |
+--------+-------------+--------------------------+---------------------------------+
| Nagoya | 日本        | 004E00410047004F00590041 | 65E5672C                        |
| Nagoya | 日本        | 004E00410047004F00590041 | 65E5672C                        |
| Nagoya | 日本        | 004E00410047004F00590041 | 65E5672C                        |
+--------+-------------+--------------------------+---------------------------------+
3 rows in set (0.00 sec)

テーブルの文字コード変換スクリプト作成と実行、TABLEの照合順序がCity2だけutf8mb4_binのまま残っている。


[root@GA01 admin]# mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND character_set_name <> 'utf8mb4' AND T.table_schema = 'world'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` DEFAULT CHARSET utf8mb4;' > alter_tables_default_change.sql
Enter password: 
[root@GA01 admin]# cat alter_tables_default_change.sql 
alter table `City` DEFAULT CHARSET utf8mb4;
alter table `Country` DEFAULT CHARSET utf8mb4;
alter table `CountryLanguage` DEFAULT CHARSET utf8mb4;
alter table `Demo_City` DEFAULT CHARSET utf8mb4;
alter table `Demo_Country` DEFAULT CHARSET utf8mb4;
alter table `Demo_CountryLanguage` DEFAULT CHARSET utf8mb4;
alter table `Wingarc` DEFAULT CHARSET utf8mb4;
alter table `成績表` DEFAULT CHARSET utf8mb4;
alter table `City3` DEFAULT CHARSET utf8mb4;
alter table `City4` DEFAULT CHARSET utf8mb4;
alter table `filler` DEFAULT CHARSET utf8mb4;
[root@GA01 admin]# mysql -u root -p world < alter_tables_default_change.sql
Enter password: 

root@localhost [world]>SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND T.table_schema = 'world';
+--------------+----------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME           | TABLE_COLLATION    | character_set_name |
+--------------+----------------------+--------------------+--------------------+
| world        | 成績表               | utf8mb4_general_ci | utf8mb4            |
| world        | City                 | utf8mb4_general_ci | utf8mb4            |
| world        | City3                | utf8mb4_general_ci | utf8mb4            |
| world        | City4                | utf8mb4_general_ci | utf8mb4            |
| world        | Country              | utf8mb4_general_ci | utf8mb4            |
| world        | CountryLanguage      | utf8mb4_general_ci | utf8mb4            |
| world        | Demo_City            | utf8mb4_general_ci | utf8mb4            |
| world        | Demo_Country         | utf8mb4_general_ci | utf8mb4            |
| world        | Demo_CountryLanguage | utf8mb4_general_ci | utf8mb4            |
| world        | Wingarc              | utf8mb4_general_ci | utf8mb4            |
| world        | filler               | utf8mb4_general_ci | utf8mb4            |
| world        | City2                | utf8mb4_bin        | utf8mb4            |
+--------------+----------------------+--------------------+--------------------+
12 rows in set (0.01 sec)

一度、テーブルのcharacter_setを上記のステップでutf8mb4に変換しているのでここでは外して、テーブルと列の文字セット変換スクリプトを実行したが、FKのエラーになったので、FKを無効にして関連テーブルをマニュアルで変換しておく。
※テーブルだけでは無く、列の文字セットや照合順序も変わるので大文字小文字の区別などが変わると困るテーブルなどは個別にマニュアル対応して下さい。


[root@GA01 admin]# mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND T.table_schema = 'world'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;' > alter_tables_convert.sql
Enter password: 
[root@GA01 admin]# cat alter_tables_convert.sql 
alter table `成績表` convert to character set utf8mb4;
alter table `City` convert to character set utf8mb4;
alter table `City3` convert to character set utf8mb4;
alter table `City4` convert to character set utf8mb4;
alter table `Country` convert to character set utf8mb4;
alter table `CountryLanguage` convert to character set utf8mb4;
alter table `Demo_City` convert to character set utf8mb4;
alter table `Demo_Country` convert to character set utf8mb4;
alter table `Demo_CountryLanguage` convert to character set utf8mb4;
alter table `Wingarc` convert to character set utf8mb4;
alter table `filler` convert to character set utf8mb4;
alter table `City2` convert to character set utf8mb4;
[root@GA01 admin]# mysql -u root -p world < alter_tables_convert.sql 
Enter password: 
ERROR 1832 (HY000) at line 2: Cannot change column 'CountryCode': used in a foreign key constraint 'city_ibfk_1'
[root@GA01 admin]# 


制約でエラーになったテーブルは、個別に対応してエラーを回避

root@localhost [world]> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> alter table `City` convert to character set utf8mb4;
Query OK, 4079 rows affected (0.27 sec)
Records: 4079  Duplicates: 0  Warnings: 0

root@localhost [world]> alter table `Country` convert to character set utf8mb4;
Query OK, 239 rows affected (0.15 sec)
Records: 239  Duplicates: 0  Warnings: 0

root@localhost [world]> alter table `CountryLanguage` convert to character set utf8mb4;
Query OK, 984 rows affected (0.21 sec)
Records: 984  Duplicates: 0  Warnings: 0

root@localhost [world]> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

対象テーブルの制約は対応済みなので、問題無く変換が終了。


[root@GA01 admin]# mysql -u root -p world < alter_tables_convert.sql 
Enter password: 
[root@GA01 admin]# 

全てのテーブルの文字コードも照合順序も適切に変換された事を確認。

root@localhost [world]> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND T.table_schema = 'world';
+--------------+----------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME           | TABLE_COLLATION    | character_set_name |
+--------------+----------------------+--------------------+--------------------+
| world        | 成績表               | utf8mb4_general_ci | utf8mb4            |
| world        | City                 | utf8mb4_general_ci | utf8mb4            |
| world        | City2                | utf8mb4_general_ci | utf8mb4            |
| world        | City3                | utf8mb4_general_ci | utf8mb4            |
| world        | City4                | utf8mb4_general_ci | utf8mb4            |
| world        | Country              | utf8mb4_general_ci | utf8mb4            |
| world        | CountryLanguage      | utf8mb4_general_ci | utf8mb4            |
| world        | Demo_City            | utf8mb4_general_ci | utf8mb4            |
| world        | Demo_Country         | utf8mb4_general_ci | utf8mb4            |
| world        | Demo_CountryLanguage | utf8mb4_general_ci | utf8mb4            |
| world        | Wingarc              | utf8mb4_general_ci | utf8mb4            |
| world        | filler               | utf8mb4_general_ci | utf8mb4            |
+--------------+----------------------+--------------------+--------------------+
12 rows in set (0.01 sec)

変換後もutf8からutf8mb4への変換なので特にデータに問題は無い。絵文字もutf8mb4なので問題無くINSERT出来る事を確認。


root@localhost [world]> select name,CountryCode,hex(weight_string(name)),hex(weight_string(CountryCode)) from City3 where CountryCode = '日本' limit 3;
+--------+-------------+--------------------------+---------------------------------+
| name   | CountryCode | hex(weight_string(name)) | hex(weight_string(CountryCode)) |
+--------+-------------+--------------------------+---------------------------------+
| Nagoya | 日本        | 004E00410047004F00590041 | 65E5672C                        |
| Nagoya | 日本        | 004E00410047004F00590041 | 65E5672C                        |
| Nagoya | 日本        | 004E00410047004F00590041 | 65E5672C                        |
+--------+-------------+--------------------------+---------------------------------+
3 rows in set (0.00 sec)

root@localhost [world]> INSERT INTO City3(Name,CountryCode,District,Population) VALUES ('Emoji','日本','\U+1F363',1780000);
Query OK, 1 row affected (0.01 sec)
root@localhost [world]> 

文字関連のデータ型に関しては、キャラクターセットも照合順序も問題無い事が確認出来る。

root@localhost [world]> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.COLUMNS WHERE TABLE_SCHEMA = 'world' and TABLE_NAME = 'City3';
+--------------+------------+-------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------+------------+-------------+--------------------+--------------------+
| world        | City3      | ID          | NULL               | NULL               |
| world        | City3      | Name        | utf8mb4            | utf8mb4_general_ci |
| world        | City3      | CountryCode | utf8mb4            | utf8mb4_general_ci |
| world        | City3      | District    | utf8mb4            | utf8mb4_general_ci |
| world        | City3      | Population  | NULL               | NULL               |
+--------------+------------+-------------+--------------------+--------------------+
5 rows in set (0.00 sec)

root@localhost [world]> show create table City3\G
*************************** 1. row ***************************
       Table: City3
Create Table: CREATE TABLE `City3` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `idx_City3` (`CountryCode`,`District`)
) ENGINE=InnoDB AUTO_INCREMENT=225200 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

詳細:

Collation変更にあたり、色々と考慮しなければいけない事がまとめられていて参考になります。
https://mysqlserverteam.com/mysql-8-0-collations-migrating-from-older-collations/

特定の文字セットを使用するように、バイナリ文字列または非バイナリ文字列カラムを変換する為の注意点が書いてあります。

抜粋:正しく変換が行われるには、次の条件のいずれかを適用する必要があります。
1)カラムにバイナリデータ型 (BINARY、VARBINARY、BLOB) がある場合、含まれるすべての値は、単一の文字セット (カラムの変換後の文字セット) を使用してエンコードされる必要があります。
バイナリカラムを使用して複数の文字セットで情報を格納する場合、MySQL はどの値がどの文字セットを使用するかを認識できず、データを正確に変換できません。
2)カラムに非バイナリデータ型 (CHAR、VARCHAR、TEXT) がある場合、その内容は、カラムの文字セットでエンコードする必要があり、ほかの文字セットは使用できません。
内容が別の文字セットでエンコードされている場合、最初にバイナリデータ型を使用するようにカラムを変換してから、使用する文字セットで非バイナリカラムに変換できます。
https://dev.mysql.com/doc/refman/5.6/ja/charset-conversion.html

ALTER TABLE を使用して、ある文字セットから別の文字セットにカラムを変換する場合、MySQL はデータ値をマップしようとしますが、文字セットに互換性がない場合、データの損失が生じる可能性があります。
https://dev.mysql.com/doc/refman/5.6/ja/charset-column.html

CONVERT TO CHARACTER SETを利用する場合の注意に関しては、此方も合わせて確認して下さい。
https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

■■■■ 文字セットや照合順の互換性確認 ■■■■

■ 照合順の互換性

root@localhost [information_Schema]> SELECT _utf8 'x' COLLATE utf8_general_ci;
+-----------------------------------+
| _utf8 'x' COLLATE utf8_general_ci |
+-----------------------------------+
| x                                 |
+-----------------------------------+
1 row in set (0.00 sec)

root@localhost [information_Schema]> SELECT _utf8 'x' COLLATE utf8mb4_general_ci;
ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'

root@localhost [information_Schema]> SELECT _utf8mb4 'x' COLLATE utf8mb4_general_ci;
+-----------------------------------------+
| _utf8mb4 'x' COLLATE utf8mb4_general_ci |
+-----------------------------------------+
| x                                       |
+-----------------------------------------+
1 row in set (0.00 sec)

root@localhost [information_Schema]> SELECT _utf8mb4 'x' COLLATE utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'
root@localhost [information_Schema]> 

参照:10.8.3 Character Set and Collation Compatibility
https://dev.mysql.com/doc/refman/5.7/en/charset-collation-compatibility.html

■ 文字セットの互換性の確認

root@localhost [OU]> CREATE TABLE `T_Character_Compare` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `c_utf8` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci  DEFAULT NULL,
    ->   `c_utf8mb4` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  DEFAULT NULL,
    ->   `c_latin2` char(1) CHARACTER SET latin2 COLLATE latin2_general_ci  DEFAULT NULL,
    ->   `c_cp932` char(1) CHARACTER SET cp932 COLLATE cp932_japanese_ci  DEFAULT NULL,
    ->   `c_eucjpms` char(1) CHARACTER SET eucjpms COLLATE eucjpms_japanese_ci  DEFAULT NULL,
    ->   `c_utf8_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_utf8`))) VIRTUAL,
    ->   `c_utf8mb4_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_utf8mb4`))) VIRTUAL,
    ->   `c_latin2_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_latin2`))) VIRTUAL,
    ->   `c_cp932_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_cp932`))) VIRTUAL,
    ->   `c_eucjpms_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_eucjpms`))) VIRTUAL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.10 sec)

root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('A','A','A','A','A');
Query OK, 1 row affected (0.01 sec)

root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
|  1 | A      | A         | A        | A       | A         | 0041          | 0041             | 41              | 41             | 41               |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
1 row in set (0.00 sec)

root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('a','a','a','a','a');
Query OK, 1 row affected (0.02 sec)

root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
|  1 | A      | A         | A        | A       | A         | 0041          | 0041             | 41              | 41             | 41               |
|  2 | a      | a         | a        | a       | a         | 0041          | 0041             | 41              | 41             | 41               |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
2 rows in set (0.00 sec)

root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('あ','あ','あ','あ','あ');
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82' for column 'c_latin2' at row 1

root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('あ','あ','-','あ','あ');
Query OK, 1 row affected (0.01 sec)

root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
|  1 | A      | A         | A        | A       | A         | 0041          | 0041             | 41              | 41             | 41               |
|  2 | a      | a         | a        | a       | a         | 0041          | 0041             | 41              | 41             | 41               |
|  3 | あ     | あ        | -        | あ      | あ        | 3042          | 3042             | 2D              | 82A0           | A4A2             |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
3 rows in set (0.01 sec)

root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('ア','ア','-','ア','ア');
Query OK, 1 row affected (0.00 sec)

root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
|  1 | A      | A         | A        | A       | A         | 0041          | 0041             | 41              | 41             | 41               |
|  2 | a      | a         | a        | a       | a         | 0041          | 0041             | 41              | 41             | 41               |
|  3 | あ     | あ        | -        | あ      | あ        | 3042          | 3042             | 2D              | 82A0           | A4A2             |
|  4 | ア     | ア        | -        | ア      | ア        | 30A2          | 30A2             | 2D              | 8341           | A5A2             |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
4 rows in set (0.00 sec)

root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('1','1','1','1','1');
Query OK, 1 row affected (0.00 sec)

root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
|  1 | A      | A         | A        | A       | A         | 0041          | 0041             | 41              | 41             | 41               |
|  2 | a      | a         | a        | a       | a         | 0041          | 0041             | 41              | 41             | 41               |
|  3 | あ     | あ        | -        | あ      | あ        | 3042          | 3042             | 2D              | 82A0           | A4A2             |
|  4 | ア     | ア        | -        | ア      | ア        | 30A2          | 30A2             | 2D              | 8341           | A5A2             |
|  5 | 1      | 1         | 1        | 1       | 1         | 0031          | 0031             | 31              | 31             | 31               |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
5 rows in set (0.00 sec)

root@localhost [OU]> 

その他:パラメータに関しての留意
character_set_server
default_character_set
innodb_large_prefix
innodb_file_format
innodb_file_format_max

※ MySQL8.0にした場合は特に指定しなければDefaultで基本的には問題無い設定が適用されるので、マニュアルで指定している場合やカスタマイズしている場合は確認して下さい。


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


SYSスキーマの説明をする機会があったので、改めてMySQL5.7.21でSYSスキーマに関しての概要をまとめたのでご紹介。
Performance_Schema, Information_Schemaを直接確認しないと取得出来無い情報もまだあるけれど、SYSスキーマを利用すれば簡単にMySQLの状態を確認出来、複雑なクエリーを使わないでもロックの状態、メモリーの状態、未使用のインデックス、起動してからの累積値だけれども遅いクエリー等が確認可能です。まだまだ使われていないユーザーも多いけど、便利なので是非活用下さい。

MySQL5.7.21の時点では以下のオブジェクトが存在します。


root@localhost [sys]> select * from schema_object_overview where db = 'sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    22 |
| sys | PROCEDURE     |    26 |
| sys | VIEW          |   100 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
+-----+---------------+-------+
6 rows in set (0.32 sec)

以下のスライドにまとめたので、詳細をご確認下さい。


MySQLでGeoHashを試してみた。以前、Webサイトを構築したときは、Google Map APIを利用して、住所から緯度経度を逆引きしてvarcharで作った列に情報を格納していましたが、あの時にgeohashを知っていたら色々とサービスで使えたかもしれない。。と思い、少し確認してみた。

MEMO:
Geohashは、任意の精度の緯度と経度座標をテキスト文字列にエンコード
Geohash値は、”0123456789bcdefghjkmnpqrstuvwxyz”から選択された文字のみを含む文字列

Geohash

桁数の誤差による差異

変換テーブル

Wiki参照: https://en.wikipedia.org/wiki/Geohash

変換例: xn76g = 11101 10100 00111 00110 01111
左から数え始め、経度は偶数ビット(1110001101011)、緯度は奇数ビット(101100101011)として表現される。二進数は1ビットずつ、左のビットから順に評価されるて、緯度については、-90から+90が2つの区間に分割され、経度は-180から+180の区間に分割される。

確認で利用したMySQLのバージョン

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

MySQL8.0のgeohash関連関数
ST_から始まるものが、最新のGEO関数です。

ST_GeoHash(経度,緯度,最大長)、ST_GeoHash(point,max_length)
接続文字セットと照合順序でgeohash文字列を返します。

ST_LatFromGeoHash(geohash_str)
geohash文字列値から[-90、90]の範囲の倍精度数として緯度を返します。

ST_LongFromGeoHash(geohash_str)
geohash文字列値から[-180,180]の範囲の倍精度数値として経度を返します。

ST_PointFromGeoHash(geohash_str、srid)
geohash文字列値を指定して、解読されたgeohash値を含むPOINT値を返します。

root@localhost [GIS]> SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
+----------------------+-------------------------+
| ST_GeoHash(180,0,10) | ST_GeoHash(-180,-90,15) |
+----------------------+-------------------------+
| xbpbpbpbpb           | 000000000000000         |
+----------------------+-------------------------+
1 row in set (0.01 sec)

root@localhost [GIS]>  SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
+------------------------------------------+
| ST_LatFromGeoHash(ST_GeoHash(45,-20,10)) |
+------------------------------------------+
|                                      -20 |
+------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GIS]> SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));
+-------------------------------------------+
| ST_LongFromGeoHash(ST_GeoHash(45,-20,10)) |
+-------------------------------------------+
|                                        45 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GIS]> SET @gh = ST_GeoHash(45,-20,10);
Query OK, 0 rows affected (0.00 sec)

root@localhost [GIS]> SELECT ST_AsText(ST_PointFromGeoHash(@gh,0));
+---------------------------------------+
| ST_AsText(ST_PointFromGeoHash(@gh,0)) |
+---------------------------------------+
| POINT(45 -20)                         |
+---------------------------------------+
1 row in set (0.00 sec)

root@localhost [GIS]> 

実際にGoogle Mapを利用して確認
オラクル青山オフィスのgeohash(5桁:xn76g)を利用してレストランを検索。
データをImport後に、緯度-経度を読み込んで確認してみる。

検証用のデータはこちらからダウンロード可能です。
Download OpenStreetMap data for this region:Japan
http://download.geofabrik.de/asia/japan.html
Download OpenStreetMap data for this region:Asia
http://download.geofabrik.de/asia.html

インポート方法はこちらが参考になります。
MySQL 5.7 and GIS, an Example
https://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

検証データと結果の確認

root@localhost [GIS]> select nodetags.v as 'name',st_latfromgeohash(st_geohash((nodes.geom),8)) as 'lat', st_longfromgeohash(st_geohash((nodes.geom),8)) as 'lng','restaurant' as 'type'  FROM nodes,nodetags WHERE nodes.id = nodetags.id and match(tags)  against ('+restaurant' IN BOOLEAN MODE) and nodetags.k='name' and nodes.GeoHash5 = 'xn76g' limit 10;
+--------------------------------------------+---------+----------+------------+
| name                                       | lat     | lng      | type       |
+--------------------------------------------+---------+----------+------------+
| 雅山 (Gazan)                               | 35.6603 | 139.7397 | restaurant |
| とんかつ まい泉                            |  35.668 | 139.7116 | restaurant |
| 粥 「喜喜」                                | 35.6727 | 139.7126 | restaurant |
| ヘンドリックス・カリー・バー               |  35.675 | 139.7126 | restaurant |
| えさき (Esaki)                             | 35.6717 | 139.7133 | restaurant |
| Cardenas/charcoal grill                    | 35.6478 | 139.7075 | restaurant |
| ROYAL PALACE                               | 35.6475 |  139.707 | restaurant |
| 長崎ちゃんぽんリンガーハット               | 35.6478 |  139.746 | restaurant |
| 増田屋 (Masudaya)                          | 35.6705 | 139.7136 | restaurant |
| Las Chicas                                 | 35.6634 |  139.708 | restaurant |
+--------------------------------------------+---------+----------+------------+
10 rows in set (0.04 sec)

PHPにSQLをコピーしてGoogle Map APIに渡して、地図を確認してみると、問題なく、同じGeohashにあるレストランが表示されているようです。
インデックスを併用すれば、結構レスポンスの良い空間データベースになりそうです。

上記処理を実行した時の、MySQLで実行プランは以下のような感じです

以上で確認は終了ですが、ついでにSRIDを指定してテーブルを作成する事が出来るようになっていたので確認してみた。
緯度-経度の順番でINSERTする場合は、列にSRIDを指定して作成した方が良いようです。
反対に経度-緯度の場合はDefaultの0のままで良い。データINSERT時に緯度と経度の順番のバリデーションが無いのであった方がよさそうです。

root@localhost [GIS]> CREATE TABLE geom (
    ->     p POINT NOT NULL SRID 0,
    ->     g GEOMETRY NOT NULL SRID 4326,
    ->     GeoHash5 varchar(5) GENERATED ALWAYS AS (st_geohash(`g`,5)) VIRTUAL,
    ->     GeoHash8 varchar(8) GENERATED ALWAYS AS (st_geohash(`g`,8)) VIRTUAL
    -> );

root@localhost [GIS]> INSERT INTO geom(p,g) VALUES(ST_GeomFromText('POINT(139.718695 35.671482)'),ST_GeomFromText('POINT(35.671482 139.718695)',4326));
Query OK, 1 row affected (0.00 sec)

root@localhost [GIS]> select * from geom;
+---------------------------+---------------------------+----------+----------+
| p                         | g                         | GeoHash5 | GeoHash8 |
+-------- @$----------------+ @$------------------------+----------+----------+
|           A@        |         A@         | xn76g    | xn76gmu1 |
+---------------------------+---------------------------+----------+----------+
1 row in set (0.00 sec)

root@localhost [GIS]> select st_geohash(p,8),st_geohash(g,8),GeoHash5,GeoHash8 from geom;
+-----------------+-----------------+----------+----------+
| st_geohash(p,8) | st_geohash(g,8) | GeoHash5 | GeoHash8 |
+-----------------+-----------------+----------+----------+
| xn76gmu1        | xn76gmu1        | xn76g    | xn76gmu1 |
+-----------------+-----------------+----------+----------+
1 row in set (0.00 sec)

root@localhost [GIS]> select 'xn76g' as 'geohash',st_latfromgeohash('xn76g') as '緯度',st_longfromgeohash('xn76g') as '経度';
+---------+--------+--------+
| geohash | 緯度   | 経度   |
+---------+--------+--------+
| xn76g   |  35.66 | 139.72 |
+---------+--------+--------+
1 row in set (0.01 sec)


参照:
12.15.10 Spatial Geohash Functions
https://dev.mysql.com/doc/refman/8.0/en/spatial-geohash-functions.html

geohash.org
http://geohash.org/


デモの準備をしていて、MySQL5.7で動いていたページが以下のようにエラーになったので、今後の為にメモ。既にマニュアルやブログで確認してはいて、情報としては認識していたのですがMySQL8.0.4以降の変更点なので忘れてました。

■ 認証プラグインの変更について
MySQL 8.0では、mysql_native_passwordではなくcaching_sha2_passwordがデフォルトの認証プラグインです。
https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

■ 変更理由はセキュリティ強化とパフォーマンス
caching_sha2_passwordおよびsha256_password認証プラグインは、mysql_native_passwordプラグインよりも安全なパスワード暗号化を提供し、caching_sha2_passwordはsha256_passwordよりも優れたパフォーマンスを提供します。
caching_sha2_passwordのこれらの優れたセキュリティとパフォーマンスの特性のため、これはMySQL 8.0.4以降の認証プラグインであり、mysql_native_passwordではなくデフォルトの認証プラグインでもあります。
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

現状では、コネクターが対応していない為、アカウント認証をMySQL8.0.4以前の認証方法に戻します。


[root@GA02 mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
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> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> 


root@localhost [mysql]> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> alter user 'admin'@'%' identified WITH mysql_native_password by 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | mysql_native_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> 

mysql_native_password認証に戻し、ページが問題無く表示される事を確認

caching_sha2_password互換のクライアントおよびコネクタ
caching_sha2_passwordについて知るように更新されたクライアントまたはコネクターが使用可能な場合は、それを使用すると、MySQL 8.0サーバに接続する際に互換性を保証する最良の方法です、それらはデフォルトの認証プラグインとしてcaching_sha2_passwordを使用して構成されています。これらのクライアントとコネクタは、caching_sha2_passwordをサポートするようにアップグレードされました:

MySQL 8.0.4以降のlibmysqlclientクライアントライブラリ。
mysqlやmysqladminなどの標準的なMySQLクライアントはlibmysqlclientベースである為、互換性があります。

MySQL Connector/J 8.0.9 or higher.
MySQL Connector/Net 8.0.10 or higher (through the classic MySQL protocol).
MySQL Connector/Node.js 8.0.9 or higher.

注意:2018年2月現在、caching_sha2_password互換クライアントはGAになってない為、
   MySQL8.0がGAになるまでしばらく進捗を確認する必要があります。

MySQL8.0.4以前に作成されたアカウントをcaching_sha2_passwordに切り替える必要がある場合
ユーザーはALTER USERステートメントを使用して変更できます。


ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';

重要
MySQL 8.0.4より前のバージョンのクライアントに対応する必要があり、MySQL 8.0.4以降にアップグレードした後で互換性の問題が発生した場合、これらの問題に対処して8.0以前の互換性を復元する最も簡単な方法は、サーバを再設定しプラグイン(mysql_native_password)を利用します。
必要に応じて、オプションファイルに次の行を設定してください。


[mysqld]
default_authentication_plugin=mysql_native_password

MySQL8.0をアプリケーションで利用される場合はこちらを確認ください。
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password


先日, MySQL Enterprise Auditの機能追加に関して確認したので、大きな変更は無いですが、Enterprise Firewallも再度機能確認してみました。データベースFirewallなので、XSSは防ぐ事は出来ませんが、SQL Injectionは防ぐ事が出来るのでEnterprise AuditとEnterprise Firewallの組み合わせで、重要な情報を扱うデータベースに追加する事で、セキュリティを更に強固にすることが可能です。

【利用例】

1) Webで公開しているアカウントにFirewallを設定して,外部からの入力フィルターをアプリケーションのフレームワークのEscape処理のみに依存しない。


2) 社内で利用しているアカウントにWHERE句を利用しない参照処理をさせないように制限する。(補足:MySQLでは列レベルの権限設定は可能)


3) IDSの代わりにFirewallを検知モードにしておいて、不正アクセスを検知したらEnterprise Monitorと連携してセキュリティ担当者にメールやSNMPで知らせる。

オフィシャルマニュアル
6.5.6 MySQL Enterprise Firewall

3年前のブログポスト
http://variable.jp/2015/04/13/mysql-enterprise-firewall/

Enterprise Firewallのデモ
1) Firewall OFFの状態 → 2) Firewall ONに設定

サイバーセキュリティ経営ガイドライン@経済産業省“に「指示5サイバーセキュリティリスクに対応するための仕組みの構築」に、新たに「攻撃の検知」を含めたリスク対応体制についての記載”が改定されていました。

MySQL Enterprise Firewallでブロックされる事が懸念としてある場合は、検知モードで設定する事も可能ですので検知する仕組みとして利用してみては如何でしょうか?


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/


5.6.1で既に実装されていてDefaultでONになっているので,5.6や5.7では普段殆ど気にしてませんでしたが、質問頂いたのでindex_condition_pushdownの条件を再確認。
DefaultはONになっています。あえて、OFFにするメリットはあまり無いかと思います。

Index Condition Pushdown(ICP): ストレージエンジンからフェッチしたレコードをMySQLが評価してWHERE区の条件による絞り込みを行っていたが、
インデックスが貼られたカラムを用いた評価については、ストレージエンジンへ条件式を渡し(プッシュダウン)、ストレージエンジン側で評価を行わせることによってオーバーヘッドの低減させる。

ICPの目標は、完全なレコードの読み取りの回数を減らし、それによって I/O 操作を減らすことです。InnoDB のクラスタ化されたインデックスの場合、完全なレコードはすでに InnoDB バッファーに読み込まれています。この場合に ICP を使用しても IO は削減されません。 その為、ICP はセカンダリインデックスにのみ使用されます。

インデックス条件をプッシュダウンしようとするのは以下の条件の場合:
0. テーブルには選択条件がある。
1. ストレージエンジンがICPをサポートする。
2. index_condition_pushdownスイッチがオンで、ICPの使用がNO_ICPヒントによって無効にされていない。
3. クエリーは、複数表の更新または削除ステートメントでは無い。この要件の理由は、選択/結合と更新の両方に同じハンドラが使用されるためです。プッシュされたインデックス条件は、更新部分を実行するときにストレージエンジンによって適用され、誤ったレコードを更新または更新するためのレコードを見つけられない結果になることがあります。
4. JOIN_TABは、”NULLキーをフル・スキャン”実行中にオンまたはオフにできる条件を保護しているサブクエリの一部ではありません。

       @see Item_in_optimizer :: val_int()
       @see subselect_single_select_engine :: exec()
       @see TABLE_REF :: cond_guards
       @see setup_join_buffering

5.結合タイプはCONSTまたはSYSTEMではありません。これらの結合タイプを除外する理由は、これらの結合タイプが、ストレージエンジンから一度だけレコードを読み取り、後でそれを再利用するように最適化されているためです。プッシュされたインデックス条件が結合シーケンスの前のテーブルからのフィールドを評価する結合では、プッシュされた条件は、レコード値が最初に必要とされたときにのみ評価されます。
6.インデックスはクラスタ化インデックスではありません。クラスタ化されたキーでインデックス条件をプッシュすると、クラスタ化されていないキーよりも大幅に低くなります。WL#6061が実装されている場合、この制限は再評価する必要があります。
7.仮想生成列の索引は、ICPではサポートされていません。

Ref Source:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_select.cc#L1723

index_condition_pushdown=offを意図的にOFFにした場合

root@localhost [world]> SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> explain select * from City3 where CountryCode ='JPN' and District like '%o%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | City3 | NULL       | ref  | idx_City3     | idx_City3 | 9       | const |  605 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 605   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

root@localhost [world]> 

Defaultの挙動 (index_condition_pushdown=on)

root@localhost [world]> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> explain select * from City3 where CountryCode ='JPN' and District like '%o%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | City3 | NULL       | ref  | idx_City3     | idx_City3 | 9       | const |  605 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 440   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

参照:
http://nippondanji.blogspot.jp/2011/04/mysql-56.html
https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/