MySQL8.0 SYSスキーマの確認。 
基本的には、MySQL5.7と変わりは無いですが、ロックを取得するVIEW内部のテーブルがInformation_schemaからPerformance_Schemaに変更になったりしている。但し、VIEW内部のスクリプト変更なので基本的に問題無く、これまで通り利用する事が可能です。

オフィシャルマニュアル: Chapter 26 MySQL sys Schema

確認したバージョン


root@localhost [mysql]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

ドキュメント


MySQL5.7からMySQL8.0へのアップグレード対応時のメモ

他のインスタンスも既に、アップグレードしましたが、UTF8MB4以外の文字コードを利用していたり、パーティションエンジンを利用していたので、
少々手間取ってしまいました。こちらの、アップグレード例はもともと、MySQL5.7で初期インストールして利用していたデータベースでUTF8MB4を利用して、
パーティションもInnoDB Native Partitionを利用していたので直ぐにアップグレードする事が出来ました。ただし、いくつか設定を変更しました。

アップグレード手順
1: mysqlsh (Ver 8.0.11) にてアップグレード事前チェックを行う
2: MySQL8.0のバリナリーダウンロード (Generic Tarを利用しました)
3: 既存のサービスを停止して、シンボリックリンクの張り直し
4: 必要なファイルやフォルダーをコピー(直ぐに、ロールバック出来るようにオリジナルフォルダーは残しています)
5: my.cnfに事前に必要な設定を入れて、MySQL8.0に無いパラメータを削除しておく
6: MySQLサービスを開始して、mysql_upgradeを実行
7: 必要に応じて、SchemaやTableのCOLLATEを変更
8: アプリケーションの接続やStrictモードを必要に応じて変更
9: 動作確認して終了

STEP1: mysqlshによるアップグレード対象インスタンスの互換性の確認
色々なアドバイスが出てくるので、必要に応じて適宜対応してください。(例:文字コード変換、パーティションストレージエンジンをInnoDBに変換等)


[root@AP01 bin]# ./mysqlsh root:password@localhost:3306 -e "util.checkForServerUpgrade();"
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.21-log - MySQL Community Server (GPL)

1) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

2) Usage of utf8mb3 charset
  No issues found

3) Usage of use ZEROFILL/display length type attributes
  Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0

  APP.T_Laravel.age - int(4)

4) Issues reported by 'check table x for upgrade' command
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Usage of old temporal type
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Usage of removed functions
  No issues found

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@AP01 bin]# 

■ 8.0のバイナリーをダウンロードして展開

[root@AP01 local]# tar zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz 
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisampack
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_config_editor
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_secure_installation
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_ssl_rsa_setup
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_tzinfo_to_sql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_upgrade
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqladmin
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlbinlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlcheck
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqldump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlimport
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mys

■既存のサービスを停止して、シンボリックリンクの張り直し

[root@AP01 local]# ls -l 
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  2月 19 17:04 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS! 
[root@AP01 local]# rm mysql
rm: シンボリックリンク `mysql' を削除しますか? y
[root@AP01 local]# ln -s mysql-8.0.11-linux-glibc2.12-x86_64 mysql
[root@AP01 local]# ls -l
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  4月 23 14:05 mysql -> mysql-8.0.11-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# 

■必要なファイルやフォルダーをコピー

[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# ls -l
合計 44
-rw-r--r--.  1  7161 31415 17987 12月 28 12:46 COPYING
-rw-r--r--.  1  7161 31415  2478 12月 28 12:46 README
drwxr-xr-x.  2 root  root   4096  2月 19 17:03 bin
drwxr-x---.  6 mysql mysql  4096  4月 23 14:06 data
drwxr-xr-x.  2 root  root     52  2月 19 17:03 docs
drwxr-xr-x.  3 root  root   4096  2月 19 17:03 include
drwxr-xr-x.  5 root  root   4096  2月 19 17:03 lib
drwxr-xr-x.  2 mysql mysql    60  2月 19 17:14 logs
drwxr-xr-x.  4 root  root     28  2月 19 17:03 man
drwxr-x---.  2 mysql mysql     6  2月 19 17:05 mysql-files
drwxr-xr-x. 28 root  root   4096  2月 19 17:03 share
drwxr-xr-x.  2 root  root     86  2月 19 17:03 support-files
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp data /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp mysql-files /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# 

■ my.cnfに以下の設定を入れておく。

default_authentication_plugin=mysql_native_password

また、mysql8.0に無いパラメータは、削除しておかないとエラーになるので削除しておきましょう。
例)2018-04-23T05:39:57.859413Z 0 [ERROR] [MY-011071] [Server] unknown variable ‘query_cache_type=0’

パラメータのチェックに関しては、こちらのページが便利です。
https://tmtm.github.io/mysql-params/?vers=5.7.22,8.0.11&diff=true

■ MySQLの起動とUpgrade


[root@AP01 data]# /etc/init.d/mysql.server start
Starting MySQL... SUCCESS! 
[root@AP01 data]#

[root@AP01 mysql]# mysql_upgrade -u root -p
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
APP.T_ChangeLog                                    OK
APP.T_Laravel                                      OK
APP.T_business                                     OK
APP.T_databases                                    OK
APP.T_others                                       OK
APP.T_scripts                                      OK
APP.cars                                           OK
APP.migrations                                     OK
APP.password_resets                                OK
APP.users                                          OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@AP01 mysql]# 

■ ファイルが自動的にアップグレードされ、メタデータ関連ファイルが無くなっている事が確認できる。

[root@AP01 mysql]# ls -l
合計 10732
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 columns_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 columns_priv.MYI
-rw-r-----. 1 mysql mysql    7763  4月 23 14:36 columns_priv_83.sdi
-rw-r-----. 1 mysql mysql    1464  2月 19 17:28 db.MYD
-rw-r-----. 1 mysql mysql    5120  2月 19 17:28 db.MYI
-rw-r-----. 1 mysql mysql   19285  4月 23 14:36 db_84.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 engine_cost.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 func.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:27 func.MYI
-rw-r-----. 1 mysql mysql    4819  4月 23 14:36 func_87.sdi
-rw-r-----. 1 mysql mysql      35  4月 23 14:31 general_log.CSM
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 general_log.CSV
-rw-r-----. 1 mysql mysql    5520  4月 23 14:36 general_log_88.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 gtid_executed.ibd
-rw-r-----. 1 mysql mysql  131072  4月 23 14:36 help_category.ibd
-rw-r-----. 1 mysql mysql  262144  4月 23 14:36 help_keyword.ibd
-rw-r-----. 1 mysql mysql  163840  4月 23 14:36 help_relation.ibd
-rw-r-----. 1 mysql mysql 8388608  4月 23 14:36 help_topic.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:05 ndb_binlog_index.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:05 ndb_binlog_index.MYI
-rw-r-----. 1 mysql mysql   10729  4月 23 14:36 ndb_binlog_index_96.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 plugin.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 procs_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 procs_priv.MYI
-rw-r-----. 1 mysql mysql    8960  4月 23 14:36 procs_priv_98.sdi
-rw-r-----. 1 mysql mysql     837  2月 19 17:05 proxies_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:05 proxies_priv.MYI
-rw-r-----. 1 mysql mysql    7813  4月 23 14:36 proxies_priv_99.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 server_cost.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 servers.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_master_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_relay_log_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_worker_info.ibd
-rw-r-----. 1 mysql mysql      35  4月 23 14:32 slow_log.CSM
-rw-r-----. 1 mysql mysql  104677  4月 23 14:31 slow_log.CSV
-rw-r-----. 1 mysql mysql   11741  4月 23 14:36 slow_log_105.sdi
-rw-r-----. 1 mysql mysql    1894  2月 19 17:27 tables_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:50 tables_priv.MYI
-rw-r-----. 1 mysql mysql    9379  4月 23 14:36 tables_priv_106.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_leap_second.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_name.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition_type.ibd
-rw-r-----. 1 mysql mysql     504  2月 19 17:27 user.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:50 user.MYI
-rw-r-----. 1 mysql mysql   36754  4月 23 14:36 user_112.sdi
[root@AP01 mysql]# 


[root@AP01 mysql]# ls -l
合計 444
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 general_log.CSM
-rw-r-----. 1 mysql mysql      0  4月 23 14:43 general_log.CSV
-rw-r-----. 1 mysql mysql   5520  4月 23 14:43 general_log_365.sdi
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 slow_log.CSM
-rw-r-----. 1 mysql mysql 109876  4月 23 14:43 slow_log.CSV
-rw-r-----. 1 mysql mysql  11741  4月 23 14:43 slow_log_367.sdi
[root@AP01 mysql]# 

必要に応じて、既存データベースやテーブルのCOLLATION(照合順序)を変更しておく

root@localhost [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */
1 row in set (0.00 sec)

root@localhost [APP]> alter schema APP DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.08 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
1 row in set (0.00 sec)

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `comment` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost [APP]> alter table `T_ChangeLog` convert to character set utf8mb4;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `comment` varchar(2048) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root@localhost [APP]> 

■アプリケーションの接続や必要に応じてStrictモードを変更

            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_0900_ai_ci',
            'prefix' => '',
            /* 'strict' => true, */
            'strict' => false,

NO_AUTO_CREATE_USER等の、SQLモードが削除されているのでフレームワークによっては、StrictモードがTrueのままだと、
アプリケーションがエラーになる場合があります。

参照:アカウント管理に関連する次の機能は削除されました。
GRANTを使用してユーザーを作成する代わりに、CREATE USERを使用するようになっています。 “NO_AUTO_CREATE_USER” SQLモードはGRANT文にとって重要ではない為、削除されました。
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

その他、参考:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html


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


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/