トップ	コンポーネント数	説明
error	1	インストゥルメント化されたエラーイベント
idle	1	インストゥルメント化されたアイドルイベント
memory	489	インストゥルメント化されたメモリイベント
stage	121	インストゥルメント化されたステージイベント
statement	212	インストゥルメント化されたステートメントイベント
transaction	1	インストルメント化されたトランザクションイベント
wait	381	インストゥルメント化された待機イベント

この中で言葉からイメージが湧きづらいのはstageだと思います。

でも、名前空間を見るとなんとなくわかります。

例）

stage/innodb/alter table (end)
stage/innodb/alter table (flush)
stage/innodb/alter table (insert)
stage/innodb/alter table (log apply index)
…
stage/mysys/Waiting for table level lock
stage/sql/After create
stage/sql/altering table
stage/sql/Applying batch of row changes (delete)
stage/sql/Applying batch of row changes (update)
stage/sql/Applying batch of row changes (write)
stage/sql/Changing master
stage/sql/Checking master version
stage/sql/checking permissions
stage/sql/cleaning up
stage/sql/closing tables
stage/sql/committing alter table to storage engine
stage/sql/Compressing gtid_executed table
stage/sql/Compressing transaction changes.
stage/sql/Connecting to master
stage/sql/converting HEAP to ondisk
stage/sql/copy to tmp table
stage/sql/creating table
stage/sql/Creating tmp table
…
stage/sql/Waiting for acl cache lock
stage/sql/Waiting for an event from Coordinator
stage/sql/Waiting for backup lock
stage/sql/Waiting for check constraint metadata lock
stage/sql/Waiting for column statistics lock
stage/sql/Waiting for commit lock
stage/sql/Waiting for dependent transaction to commit

これを見るとドキュメントの言ってることもなんとなくわかります。（ドキュメントだけだと僕は最初なんのこっちゃでしたｗ）

ステージインストゥルメントは、形式 stage/code_area/stage_name の名前を持ちます。ここで code_area は sql や myisam などの名前で、stage_name は、Sorting result や Sending data などのステートメント処理のステージを示します。ステージは SHOW PROCESSLIST によって表示されるか、または INFORMATION_SCHEMA.PROCESSLIST テーブルに表示されるスレッドの状態に対応します。

Stage instruments have names of the form stage/code_area/stage_name, where code_area is a value such as sql or myisam, and stage_name indicates the stage of statement processing, such as Sorting result or Sending data. Stages correspond to the thread states displayed by SHOW PROCESSLIST or that are visible in the INFORMATION_SCHEMA.PROCESSLIST table.

https://dev.mysql.com/doc/refman/5.6/ja/performance-schema-instrument-naming.html

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-instrument-naming.html

パフォーマンススキーマ関連のシステムパラメータ

mysql> SHOW VARIABLES LIKE 'perf%';

で確認できますが、各パラメータの詳細についてはドキュメントを参照してください。

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html

ご存じだと思いますが、パフォーマンススキーマを有効にするにはperformance_schemaをONにするんですが、MySQL5.7以降はデフォルトONです。

最大登録数について

そしてここからが本題

performance_schema_max_xxxx_classesは計器の種類がxxxxのものの最大登録数を設定しています。

例えば、performance_schema_max_file_classesであれば ****/file/xxx といった計器が登録できる最大数という意味です。

パフォーマンススキーマの計器はバージョンを重ねるごとに増えており、計器の最大登録数もそれに併せて増えているようです。

また、計器自体は既存のもの以外にサードパーティプラグインを追加することもできるようになっており、そういったことから追加したい場合にその数に併せて最大登録数を調整できるようにしています。

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-status-monitoring.html

実際にどう増えていってるか確認してみる

とみたさんの例のパラメータ比較するやつをお借りします。

https://mysql-params.tmtms.net/mysqld/?vers=5.6.47,5.7.24,5.7.25,8.0.11,8.0.12,8.0.13&diff=true

パラメータ名	MySQL 5.6.47	MySQL 5.7.24	MySQL 5.7.25	MySQL 8.0.11	MySQL 8.0.12	MySQL 8.0.13	MySQL 8.0.19
performance-schema-max-cond-classes	80	80	80	80	80	100	100
performance-schema-max-file-classes	50	80	80	80	80	80	80
performance-schema-max-memory-classes	-	320	320	450	450	450	450
performance-schema-max-mutex-classes	200	210	210	250	300	300	300
performance-schema-max-rwlock-classes	40	40	50	60	60	60	60
performance-schema-max-stage-classes	150	150	150	150	150	175	175
performance-schema-max-statement-classes	168	193	193	212	212	212	218
performance-schema-max-thread-classes	50	50	50	100	100	100	100

ところどころマイナーバージョンアップ時に上がってます。

ところが・・・

MySQL8.0のドキュメントを見ると・・・

パラメータ名	ドキュメントのデフォルト値	8.0.19のデフォルト値	URL
performance-schema-max-cond-classes	80	100	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_cond_classes
performance-schema-max-file-classes	80	80	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_file_classes
performance-schema-max-memory-classes	450	450	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_memory_classes
performance-schema-max-mutex-classes	300	300	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_mutex_classes
performance-schema-max-rwlock-classes	40	60	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_rwlock_classes
performance-schema-max-stage-classes	150	175	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_stage_classes
performance-schema-max-statement-classes	(auto-size)	(auto-size)	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_statement_classes
performance-schema-max-thread-classes	100	100	https://dev.mysql.com/doc/refman/8.0/en/performance-schema-system-variables.html#sysvar_performance_schema_max_thread_classes

あれ？一部のデフォルト値がおかしい。

マイナーバージョンで数値が上がっているのであれば、そのマイナーバージョンごとの記載があるべきなんですが・・・。

ソースも見てみる

#ifndef PFS_MAX_MUTEX_CLASS
#define PFS_MAX_MUTEX_CLASS 300
#endif
#ifndef PFS_MAX_RWLOCK_CLASS
#define PFS_MAX_RWLOCK_CLASS 60
#endif
#ifndef PFS_MAX_COND_CLASS
#define PFS_MAX_COND_CLASS 100
#endif
#ifndef PFS_MAX_THREAD_CLASS
#define PFS_MAX_THREAD_CLASS 100
#endif
#ifndef PFS_MAX_FILE_CLASS
#define PFS_MAX_FILE_CLASS 80
#endif
・・・
#endif
#ifndef PFS_MAX_STAGE_CLASS
#define PFS_MAX_STAGE_CLASS 175
#endif
・・・
#ifndef PFS_MAX_MEMORY_CLASS
#define PFS_MAX_MEMORY_CLASS 450
#endif

https://github.com/mysql/mysql-server/blob/8.0/storage/perfschema/pfs_server.h#L56-L85

あー、やっぱりドキュメントが未修正っぽい・・・。

いずれ修正されると思いますが、気になるパラメータについてはデフォルト値についてはソースや実際にインストールしたDBの初期値で確認するのが確実ですね。

追記

修正されました！

Default Value (≥ 8.0.13)という欄が追加されてます！やったね！

2020-03-27

ようこそ…『男の世界』へ…（AUTO_INCREMENTが巻き戻るお話）

MySQL

AUTO_INCREMENTが巻き戻る

今の会社に入るまで知らなかったんですが、結構有名なバグっぽいですね。

AUTO_INCREMENTで採番された番号が、再起動するとMySQL5.7以前は巻き戻る現象が起きる話です。

再現してみる

MySQL5.7と8.0にそれぞれ同じテーブルを作ってデータを入れ、最新の行を削除します。

共通

mysql> use test;
Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> delete from t1 where id = 3;
Query OK, 1 row affected (0.00 sec)

MySQL5.7.29の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AUTO_INCREMENTは4ですね。

MySQL8.0.19の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

同じくAUTO_INCREMENTは4ですね。

MySQLを再起動すると・・・

MySQL5.7.29の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AUTO_INCREMENT=3 になってる！（4から3に巻き戻ってる）

でも、information_schema.tablesで確認すると4のまま…。

MySQL8.0.19の場合

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

AUTO_INCREMENT=4 のままですね。

ドキュメントやソース

この変更についてはこの8.0のドキュメントに記載されています。

Google先生等を使ってそれっぽく訳したものをあげておきます。

MySQL 5.7 以前のバージョンでは、オートインクリメントカウンタはメインメモリにのみ保存され、ディスクには保存されません。サーバの再起動後にオートインクリメントカウンタを初期化するために、InnoDB は AUTO_INCREMENT カラムを含むテーブルへの最初の挿入時に以下のステートメントと同等のものを実行します。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

MySQL 8.0では、この動作が変更されています。現在のオートインクリメントカウンタの最大値は、変更されるたびに REDO ログに書き込まれ、チェックポイントごとにエンジン・プライベート・システム・テーブルに保存されます。これらの変更により、現在のオートインクリメントカウンタの最大値は、サーバーの再起動時にも持続します。

通常のシャットダウン後のサーバー再起動時に、InnoDB は、データ辞書システムテーブルに保存されている現在の最大オートインクリメント値を使用して、メモリ内オートインクリメントカウンタを初期化します。

REDOログに書き込んでいると言っているのがソース上だとこの辺

そのdict_table_autoinc_log()のドキュメントはここ

バグだったのが8で直ったという扱い

2003年に見つかって、約20年経って直ったんですねえ…。

5.7以前でも巻き戻らないようにしたいという人は

(5.6以降ならオンラインDDL扱いなので)ストアドプロシージャ作って、そこから値を取ればシーケンスのような挙動はできますね。

作るときはinformation_schema.tablesのauto_incrementカラムの値を取得して、
alter table 文をでauto_incrementの値を変更

5.5以前だとauto increment使わずにプロシージャと専用テーブルかな？（さすがにやりすぎか）

ところでタイトルの意味は？

ようこそ…『男の世界』へ…

ジョジョの奇妙な冒険 Part7 スティール・ボール・ランのキャラクターの一人リンゴォ・ロードアゲインのセリフから。

このキャラがきっかり時を6秒『巻き戻す』能力を持っていることから引用。

ただそれだけｗ

ぶっちゃけ

ソースの場所とかyoku0825さんに教えてもらったんですよね。

本当にいつもありがとうございます。

参考

2020-02-28

MySQL8でCHAR関数がドキュメントどおりになってない

MySQL

MySQL8でCHAR関数がドキュメントどおりになってない

https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_char

を読むと、こう書いてある。

CHAR(N,... [USING charset_name])
CHAR() は各 N 引数を整数として解釈し、それらの整数のコード値で指定された文字を構成している文字列を返します。NULL 値はスキップされます。

mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM'

MySQL5.7.29の場合

ドキュメントはこちら

mysql [localhost:5729] {msandbox} ((none)) > SELECT CHAR(77,121,83,81,'76');
+-------------------------+
| CHAR(77,121,83,81,'76') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

ドキュメント通りですね。

MySQL8.0.19の場合

ドキュメントはこちら

mysql [localhost:8019] {msandbox} ((none)) > SELECT CHAR(77,121,83,81,'76');
+--------------------------------------------------+
| CHAR(77,121,83,81,'76')                          |
+--------------------------------------------------+
| 0x4D7953514C                                     |
+--------------------------------------------------+
1 row in set (0.01 sec)

あるぇ？おかしい。。。

これ、バグっぽいな

バグレポを見る。

これはわりと関係しそうだけど直接的ではないんだよなー。

ただ、ドキュメントと違うというだけで、雰囲気的には8のそれも最初からそれならアリな感じはするんだよな。

そして

atsuizoさんからコメントを頂く。 f:id:next4us-ti:20200301103852p:plain

なるほど、8.0.14で検証したら大丈夫。ということはどこかのバージョンか、環境のせいでおかしいのかも？

MySQL8.0.19の場合 by docker

mysql> SELECT CHAR(77,121,83,81,'76');
+--------------------------------------------------+
| CHAR(77,121,83,81,'76')                          |
+--------------------------------------------------+
| 0x4D7953514C                                     |
+--------------------------------------------------+
1 row in set (0.00 sec)

同じだ。

MySQL8.0.18の場合 by docker

mysql> SELECT CHAR(77,121,83,81,'76');
+-------------------------+
| CHAR(77,121,83,81,'76') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

あ！今までの見え方になった・・・。

おそらくバージョンのせいっぽいぞ。

バグレポ

既に木村さんの方で出してもらってたようです。ありがたや。 f:id:next4us-ti:20200301110201p:plain

MySQL Bugs: Access denied

解決方法

やはり木村さんに教えてもらいました。

f:id:next4us-ti:20200302194733p:plain

MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options

試してみます

# mysql -uroot -p --skip-binary-as-hex
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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 CHAR(77,121,83,81,'76');
+-------------------------+
| CHAR(77,121,83,81,'76') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

ほんとだ、元の見え方になりました！

リリースノートを見る

8.0.19のリリースノートはこちら

When the mysql client operates in interactive mode, the --binary-as-hex option now is enabled by default.

mysqlクライアントが対話モードで動作するとき、--binary-as-hexオプションはデフォルトで有効になりました。

To disable hexadecimal notation, use --skip-binary-as-hex (Bug #24432545)

16進表記を無効にするには、--skip-binary-as-hex （バグ＃24432545）を使用します

ここのことか！（分かりづらい…）

バグ対応で正しくしたのならドキュメントの方直すべきだべ…。

感想

今回は木村さんに全てやってもらって、僕は記事を挙げただけですｗ（木村さんありがとうございます）

そして、atsuizoさんに指摘してもらって8.0.19からの変更だと気づけましたし、そのあとのコメントの通り、これ誰が嬉しいのかよくわからない変更ですよね（苦笑） f:id:next4us-ti:20200302195245p:plain

マイナーながらもなかなかナニコレ的なものなので、こいつがいつか誰かの役に立てれば幸いです。

2020-01-25

MySQL Technology Cafe #7 で『MySQL Shellを使ってみよう』を聞いてからのLinux(Ubuntu)での導入部分

MySQL

前回Windowsだったので、今回はUbuntuで引き続き初心者向けにライトな感じで導入部分を書いてみました。

1. MySQL APT Repositoryの取得

ここからダウンロード(インストール済の人はスキップ)

2020/1/25現在の最新版はmysql-apt-config_0.8.14-1_all.deb

2. インストール＆アップデート

$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb
$ sudo apt-get update

実行例

$ sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb
以前に未選択のパッケージ mysql-apt-config を選択しています。
(データベースを読み込んでいます ... 現在 200765 個のファイルとディレクトリがインストールされています。)
mysql-apt-config_0.8.14-1_all.deb を展開する準備をしています ...
mysql-apt-config (0.8.14-1) を展開しています...
mysql-apt-config (0.8.14-1) を設定しています ...
Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)
OK
$ sudo apt-get update
取得:1 http://repo.mysql.com/apt/ubuntu bionic InRelease [19.4 kB]
ヒット:2 http://archive.ubuntulinux.jp/ubuntu bionic InRelease
ヒット:3 http://jp.archive.ubuntu.com/ubuntu bionic InRelease 
ヒット:4 http://archive.ubuntulinux.jp/ubuntu-ja-non-free bionic InRelease
取得:5 https://download.docker.com/linux/ubuntu bionic InRelease [64.4 kB]
取得:6 http://jp.archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]
取得:7 http://jp.archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]  
無視:8 http://dl.google.com/linux/chrome/deb stable InRelease 
ヒット:9 http://packages.microsoft.com/repos/vscode stable InRelease
ヒット:11 http://dl.google.com/linux/chrome/deb stable Release
取得:12 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
ヒット:10 https://packages.cloud.google.com/apt kubernetes-xenial InRelease
ヒット:13 https://packagecloud.io/slacktechnologies/slack/debian jessie InRelease
ヒット:14 http://apt.postgresql.org/pub/repos/apt bionic-pgdg InRelease
取得:16 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 Sources [928 B]
取得:17 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config i386 Packages [566 B]
取得:18 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config amd64 Packages [566 B]
取得:19 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 amd64 Packages [5,667 B]
取得:20 http://repo.mysql.com/apt/ubuntu bionic/mysql-5.7 i386 Packages [5,657 B]
取得:21 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 Packages [4,294 B]
取得:22 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools i386 Packages [3,694 B]
357 kB を 6秒 で取得しました (61.4 kB/s)
パッケージリストを読み込んでいます... 完了

3. MySQL Shellのインストール

$ sudo apt-get install mysql-shell

実行例

$ sudo apt-get install mysql-shell
パッケージリストを読み込んでいます... 完了
依存関係ツリーを作成しています  
状態情報を読み取っています... 完了
以下のパッケージが自動でインストールされましたが、もう必要とされていません:
  libllvm8 postgresql-client-11 python3-flask-htmlmin python3-htmlmin
これを削除するには 'sudo apt autoremove' を利用してください。
以下のパッケージが新たにインストールされます:
  mysql-shell
アップグレード: 0 個、新規インストール: 1 個、削除: 0 個、保留: 14 個。
13.0 MB のアーカイブを取得する必要があります。
この操作後に追加で 72.3 MB のディスク容量が消費されます。
取得:1 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 mysql-shell amd64 8.0.19-1ubuntu18.04 [13.0 MB]
13.0 MB を 2秒 で取得しました (7,678 kB/s)
以前に未選択のパッケージ mysql-shell:amd64 を選択しています。
(データベースを読み込んでいます ... 現在 200770 個のファイルとディレクトリがインストールされています。)
.../mysql-shell_8.0.19-1ubuntu18.04_amd64.deb を展開する準備をしています ...
mysql-shell:amd64 (8.0.19-1ubuntu18.04) を展開しています...
mysql-shell:amd64 (8.0.19-1ubuntu18.04) を設定しています ...

4. 確認

mysqlshのHELP（オプション確認）

$ mysqlsh
MySQL Shell 8.0.19

Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.

 MySQL  JS >  \help
The Shell Help is organized in categories and topics. To get help for a
specific category or topic use: \? <pattern>

The <pattern> argument should be the name of a category or a topic.

The pattern is a filter to identify topics for which help is required, it can
use the following wildcards:

- ? matches any single character.
- * matches any character sequence.

The following are the main help categories:

 - AdminAPI       The AdminAPI is an API that enables configuring and managing
                  InnoDB clusters and replicasets, among other things.
 - Shell Commands Provides details about the available built-in shell commands.
 - ShellAPI       Contains information about the shell and util global objects
                  as well as the mysql module that enables executing SQL on
                  MySQL Servers.
 - SQL Syntax     Entry point to retrieve syntax help on SQL statements.
 - X DevAPI       Details the mysqlx module as well as the capabilities of the
                  X DevAPI which enable working with MySQL as a Document Store

The available topics include:

- The dba global object and the classes available at the AdminAPI.
- The mysqlx module and the classes available at the X DevAPI.
- The mysql module and the global objects and classes available at the
  ShellAPI.
- The functions and properties of the classes exposed by the APIs.
- The available shell commands.
- Any word that is part of an SQL statement.
- Command Line - invoking built-in shell functions without entering interactive
  mode.

SHELL COMMANDS

The shell commands allow executing specific operations including updating the
shell configuration.

The following shell commands are available:

 - \                   Start multi-line input when in SQL mode.
 - \connect    (\c)    Connects the shell to a MySQL server and assigns the
                       global session.
 - \edit       (\e)    Launch a system editor to edit a command to be executed.
 - \exit               Exits the MySQL Shell, same as \quit.
 - \help       (\?,\h) Prints help information about a specific topic.
 - \history            View and edit command line history.
 - \js                 Switches to JavaScript processing mode.
 - \nopager            Disables the current pager.
 - \nowarnings (\w)    Don't show warnings after every statement.
 - \option             Allows working with the available shell options.
 - \pager      (\P)    Sets the current pager.
 - \py                 Switches to Python processing mode.
 - \quit       (\q)    Exits the MySQL Shell.
 - \reconnect          Reconnects the global session.
 - \rehash             Refresh the autocompletion cache.
 - \show               Executes the given report with provided options and
                       arguments.
 - \source     (\.)    Loads and executes a script from a file.
 - \sql                Executes SQL statement or switches to SQL processing
                       mode when no statement is given.
 - \status     (\s)    Print information about the current global session.
 - \system     (\!)    Execute a system shell command.
 - \use        (\u)    Sets the active schema.
 - \warnings   (\W)    Show warnings after every statement.
 - \watch              Executes the given report with provided options and
                       arguments in a loop.

GLOBAL OBJECTS

The following modules and objects are ready for use when the shell starts:

 - dba    Used for InnoDB cluster administration.
 - mysql  Support for connecting to MySQL servers using the classic MySQL
          protocol.
 - mysqlx Used to work with X Protocol sessions using the MySQL X DevAPI.
 - os     Gives access to functions which allow to interact with the operating
          system.
 - shell  Gives access to general purpose functions and properties.
 - sys    Gives access to system specific parameters.
 - util   Global object that groups miscellaneous tools like upgrade checker
          and JSON import.

For additional information on these global objects use: <object>.help()

EXAMPLES
\? AdminAPI
      Displays information about the AdminAPI.

\? \connect
      Displays usage details for the \connect command.

\? checkInstanceConfiguration
      Displays usage details for the dba.checkInstanceConfiguration function.

\? sql syntax
      Displays the main SQL help categories.

\?または\helpを最初に書いて、その後オプションを書く。

 MySQL  SQL > \? \connect
NAME
      \connect - Connects the shell to a MySQL server and assigns the global
      session.

SYNTAX
      \connect [<TYPE>] <URI>
      \c [<TYPE>] <URI>

DESCRIPTION
      TYPE is an optional parameter to specify the session type. Accepts the
      following values:

      - --mc, --mysql: create a classic MySQL protocol session (default port
        3306)
      - --mx, --mysqlx: create an X protocol session (default port 33060)

      If TYPE is omitted, automatic protocol detection is done, unless the
      protocol is given in the URI.

      URI format is: [user[:password]@]hostname[:port]

EXAMPLE
      \connect --mx root@localhost
            Creates a global session using the X protocol to the indicated URI.

DB接続

mysqlのクライアントとしてDBに接続するときはこんな感じで書くということらしい。

 MySQL  SQL > \connect --mc user:password@127.0.0.1:3306

 MySQL  SQL > \connect --mysql user:password@127.0.0.1:3306

実行例

 MySQL  SQL > \connect --mysql root:xxxxxx@127.0.0.1:3306
Creating a Classic session to 'root@127.0.0.1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 4410
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  127.0.0.1:3306 ssl  SQL >

スキーマ変更は`\use <schema>`

 MySQL  127.0.0.1:3306 ssl  performance_schema  SQL > \use performance_schema
Default schema set to `performance_schema`.
Fetching table and column names from `performance_schema` for auto-completion... Press ^C to stop.
 MySQL  127.0.0.1:3306 ssl  performance_schema  SQL >

セッションの再接続

セッション切れたっぽいぞ、と思ったら\reconnect

 MySQL  127.0.0.1:3306 ssl  performance_schema  SQL > \reconnect 
Attempting to reconnect to 'mysql://root@127.0.0.1:3306/performance_schema'..
The global session was successfully reconnected.

接続先DBサーバの状態確認

繋いでいるDBサーバの状態を見るのは\status

 MySQL  127.0.0.1:3306 ssl  performance_schema  SQL > \status
MySQL Shell version 8.0.19

Session type:                 Classic
Connection Id:                4430
Current schema:               performance_schema
Current user:                 root@172.xxx.xxx.1
SSL:                          Cipher in use: ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2
Using delimiter:              ;
Server version:               8.0.19 MySQL Community Server - GPL
Protocol version:             classic 10
Client library:               8.0.19
Connection:                   127.0.0.1 via TCP/IP
TCP port:                     3306
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Compression:                  Disabled
Uptime:                       23 hours 35 min 19.0000 sec

Threads: 6  Questions: 391129  Slow queries: 0  Opens: 542  Flush tables: 3  Open tables: 462  Queries per second avg: 4.605

別のMySQLサーバ（5.7）の環境に繋いで\status実行してみる

 MySQL  127.0.0.1:3306 ssl  performance_schema  SQL > \connect --mc root:xxxxx@127.0.0.1:3307
Creating a Classic session to 'root@127.0.0.1:3307'
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 4440
Server version: 5.7.26 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
 MySQL  127.0.0.1:3307 ssl  SQL > \use information_schema
Default schema set to `information_schema`.
Fetching table and column names from `information_schema` for auto-completion... Press ^C to stop.
 MySQL  127.0.0.1:3307 ssl  information_schema  SQL > \status
MySQL Shell version 8.0.19

Session type:                 Classic
Connection Id:                4440
Current schema:               information_schema
Current user:                 root@172.xxx.xxx.1
SSL:                          Cipher in use: DHE-RSA-AES256-SHA TLSv1.1
Using delimiter:              ;
Server version:               5.7.26 MySQL Community Server (GPL)
Protocol version:             classic 10
Client library:               8.0.19
Connection:                   127.0.0.1 via TCP/IP
TCP port:                     3307
Server characterset:          latin1
Schema characterset:          utf8
Client characterset:          latin1
Conn. characterset:           latin1
Compression:                  Disabled
Uptime:                       23 hours 36 min 19.0000 sec

Threads: 5  Questions: 388499  Slow queries: 0  Opens: 204626  Flush tables: 1  Open tables: 2000  Queries per second avg: 4.571

コマンド履歴の確認

今のセッションのコマンド履歴を見たいときは\history

 MySQL  SQL > \history
    1  \history
    2  \status

但し、その番号のコマンドが実行できるわけではなさそう

 MySQL  SQL > \? \history
NAME
      \history - View and edit command line history.

SYNTAX
      \history [options].

DESCRIPTION
      The operation done by this command depends on the given options. Valid
      options are:

      - del range         Deletes entry/entries from history.
      - clear             Clear history.
      - save              Save history to file.

      If no options are given the command will display the history entries.

      Range in the delete operation can be given in one of the following forms:

      - num single number identifying entry to delete.
      - num-num numbers specifying lower and upper bounds of the range.
      - num- range from num till the end of history.
      - -num last num entries.

      NOTE: The history.autoSave shell option must be set to true to
      automatically save the contents of the command history when MySQL Shell
      exits.

EXAMPLES
      \history
            Displays the entire history.

      \history del 123
            Deletes entry number 123 from the history.

      \history del 10-20
            Deletes range of entries from number 10 to 20 from the history.

      \history del 10-
            Deletes entries from number 10 and ahead from the history.

      \history del -5
            Deletes last 5 entries from the history.

ページャーの設定と解除

先頭１０行で区切る

\pager more -10

うう、改行されちゃったものも一行とみなすのね・・・。

 MySQL  127.0.0.1:3306 ssl  information_schema  SQL > select * from TABLES;
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+---------
--------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+---------------------------------------+------------------------------
--------------------+
| TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME                                           | TABLE_TYPE  | ENGINE             | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA
_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION    | CHECKSUM | CREATE_OPTIONS                        | TABLE_COMMENT                
                    |
+---------------+--------------------+------------------------------------------------------+-------------+--------------------+---------+------------+------------+----------------+-------------+---------
--------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+---------------------------------------+------------------------------
--------------------+
| def           | mysql              | innodb_table_stats                                   | BASE TABLE  | InnoDB             |      10 | Dynamic    |         67 |            244 |       16384 |
--More--

ページャーの解除は\nopager

 \nopager

抜けるときは\qか\quitか\exitで

 MySQL  127.0.0.1:3306 ssl  information_schema  SQL > \q
Bye!
$

最後に

\js や\pyでJavaScriptやPythonに切り替えられますし、Replica SetやMySQL InnoDB Clusterを使うならそっちなので、今度はそっちの話を書こうかと思います。

2020-01-19

MySQL Technology Cafe #7 で『MySQL Shellを使ってみよう』を聞いてからのWindowsでの導入部分

MySQL

MySQL Shell をWindowsに入れてみる

先日参加してきたMySQL Technology Cafe #7でMySQL Shellって何？どんなことできるの？といった初心者向けの話がされるというので聞いて来ました。

今回はそこから学んだこと、およびハマってしまったWindowsでの設定について軽く触れておきます。

（普段LinuxなのでWindowsはすっかりわからんちんになってた…）

セッションで使われた資料

MySQL Shellを使ってみよう！

資料と当日の講義から感じた思いとしては

MySQL ShellってInnoDB Clusterの管理をするのに必要なものってイメージで、それを使わないなら必要ないと思われてる
でも、そもそものコンセプトとしてはMySQL Utilitiesから派生してできたもので、色々なことができるもの
- それなのにMySQL大好きな人たちにすら前述の認知しかされてないのでもったいない！

もっと知ってもらいたい！

JavaScript,Python,SQLどんな形でも簡単にDBにつないで作業できる
MySQLのバージョンアップグレードチェッカー機能がある
XdevAPIでスキーマレスJSONドキュメントの操作ができる
パラレルデータインポートができる
JSONデータのインポートができる
データの出力形式を色々選べる
バッチとして使える
pluginを追加すればここにない機能が追加できる！
最新機能のReplica Setの管理にも使える（構築・手動HAとか）
イメージとしてはこちらを参照

f:id:next4us-ti:20200119092236j:plain

今回の講義では、来ているメンバーが濃い目（笑）と思ったことからプラグイン部分とRepluca Setの部分に焦点をあてたらしい。

（実際はその濃い目の人たちもMySQL Shellって普段全然触らないから何それおいしいの？って感じで温度差があったのですが）

インストール前のおススメ

https://insidemysql.com/mysql-shell-8-0-19/ https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-configuring-options.html

実行履歴を保存しよう！

shell.options.setPersist　を使って構成ファイルへ設定を保存することができる。

実行履歴を保存するのであれば、

MySQL JS > shell.options.set('history.maxSize', 5000)
MySQL JS > shell.options.setPersist('useWizards', 'true')
MySQL JS > shell.options.setPersist('history.autoSave', 1)

MySQL Shellコード履歴に保存するエントリの最大数を5000にセットし、
ウィザードモードを有効にして、構成ファイルへ設定を保存
MySQL Shellを終了するときに、MySQL Shellコード履歴のエントリを保存（true）するように構成ファイルへ設定を保存

とやるらしい。

デフォルトのモードを設定しよう（defaultMode）

（後述のMySQL Shellのセットアップ終わってから戻ってきて試してみてください）

まず、現状を確認してみよう。

mysqlshと打ってMySQL Shellを起動。

shell.と打って、そのあとTabを二回たたくと候補が表示される

 MySQL  JS > shell.
addExtensionObjectMember()  options
connect()                   parseUri()
createExtensionObject()     prompt()
deleteAllCredentials()      reconnect()
deleteCredential()          registerGlobal()
disablePager()              registerReport()
dumpRows()                  reports
enablePager()               setCurrentSchema()
getSession()                setSession()
help()                      status()
listCredentialHelpers()     storeCredential()
listCredentials()           unparseUri()
log()

この中からとりあえずオプション(option)を選択して同様に二回Tabをタタンとたたく。

 MySQL  JS > shell.options.
autocomplete.nameCache          interactive
batchContinueOnError            logLevel
credentialStore.excludeFilters  oci.configFile
credentialStore.helper          oci.profile
credentialStore.savePasswords   outputFormat
dba.gtidWaitTimeout             pager
dba.logSql                      passwordsFromStdin
defaultCompress                 resultFormat
defaultMode                     sandboxDir
devapi.dbObjectHandles          showColumnTypeInfo
history.autoSave                showWarnings
history.maxSize                 useWizards
history.sql.ignorePattern       verbose

じゃあ、defaultModeを見てみよう。

 MySQL  JS > shell.options.defaultMode
none

今は何も設定されていないらしいのでSQLモードにしてみる。

 MySQL  JS > shell.options.setPersist('defaultMode', 'SQL')
 MySQL  JS > shell.options.defaultMode
sql

一回抜けて入りなおしてみる。

 MySQL  JS > \q
Bye!

C:\Users\takayuki>mysqlsh
MySQL Shell 8.0.19

Copyright (c) 2016, 2019, 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 '\?' for help; '\quit' to exit.
 MySQL  SQL >

確かにSQLモードになった。

こんな感じで設定変更が色々できそうだ。

MySQLシェルオプション

他にも色々な設定変更ができるので以下参照。

optionName	DefaultValue	Type	Effect
autocomplete.nameCache	TRUE	ブール値	自動補完のデータベース名のキャッシュを有効にします。
batchContinueOnError	FALSE	ブール値（読み取り専用）	SQLバッチモードでは、エラーが見つかった場合、処理を強制的に続行します。
dba.gtidWaitTimeout	60	0より大きい整数	AdminAPI操作で必要な場合に、GTIDトランザクションが適用されるのを待機する秒単位のタイムアウト（InnoDBクラスターの操作を参照）
dba.logSql	0	0から2の範囲の整数	AdminAPI操作によって実行されるSQL ステートメントをログに記録します（第8章「 MySQLシェルのロギングとデバッグ」を参照）。
defaultCompress	FALSE	ブール値	すべてのグローバルセッションで可能であれば、クライアントとサーバー間で送信される情報の圧縮を有効にします（クラシックMySQL プロトコル接続のみ）。
defaultMode	None	文字列（sql、js、またはpy）	MySQLシェルの起動時に使用するモード（SQL、Javascript、またはPython）。
devapi.dbObjectHandles	TRUE	ブール値	DevAPI dbオブジェクトのテーブル名とコレクション名のハンドルを有効にします。
history.autoSave	FALSE	ブール値	MySQL Shellを終了するときに、MySQL Shellコード履歴のエントリを保存（true）またはクリア（false）します（セクション5.5「コード履歴」を参照）。
history.maxSize	1000	整数	MySQL Shellコード履歴に保存するエントリの最大数。
history.sql.ignorePattern	IDENTIFIED : PASSWORD	文字列	これらのパターンに一致する文字列は、MySQL Shellコード履歴に追加されません。
interactive	TRUE	ブール値（読み取り専用）	対話モードを有効にします。
logLevel	Requires a value	1から8の範囲の整数	アプリケーションログのログレベルを設定します（第8章「 MySQLシェルのログとデバッグ」を参照）。
pager	None	文字列	指定された外部ポケットベルツールを使用して、テキストと結果を表示します。ツールのコマンドライン引数を追加できます（セクション4.6「ページャーの使用」を参照）。
passwordsFromStdin	FALSE	ブール値	stdin端末の代わりにパスワードを読み取ります。
resultFormat	table	文字列（table, 水平タブ区切り, 垂直タブ区切り, json	json/pretty, ndjson \| json/raw, json/array）\|結果セットを印刷するためのデフォルトの出力形式（セクション5.7「出力形式」を参照）。
sandboxDir	プラットフォームに依存	文字列	サンドボックスディレクトリ。デフォルトはWindowsでは `C:\Users\MyUser\MySQL\mysql-sandboxes` であり、Unixシステムでは `$HOME/mysql-sandboxes` です。
showColumnTypeInfo	FALSE	ブール値	SQLモードでは、結果セットの列メタデータを表示します。
showWarnings	TRUE	ブール値	SQLモードでは、SQL警告があれば自動的に表示します。
useWizards	TRUE	ブール値	ウィザードモードを有効にします。
verbose	1	0から4の範囲の整数	コンソールへの詳細出力を有効にし、詳細レベルを設定します（第8章「 MySQLシェルのロギングとデバッグ」を参照）。

参照：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-configuring-options.html

Windows でのMySQL Shellのセットアップ

さて、現場でちょっとハマったセットアップの話。

自分が使ったのはWindowsマシンだったのでその方法を以下記載。

1. インストーラをダウンロード

MySQL Community Downloads　MySQL Shellからダウンロードします。

f:id:next4us-ti:20200119092319j:plain

mysql-shell-8.0.xx-windows-x86-64bit.zipファイルを解凍します。
mysql-shell-8.0.xx-windows-x86-64bit\binフォルダ下のmysqlsh.exeファイルを環境変数PATHに追加します。
- 環境変数にはお好みでMySQL Shellのホームディレクトリを登録できます（MYSQLSH_HOME）
  - 指定しない場合はmysql-shell-8.0.xx-windows-x86-64bitフォルダがホームディレクトリになります。
- 同様にMySQL Shellのユーザー設定ファイル用ホームディレクトリも登録可能です（MYSQLSH_USER_CONFIG_HOME）

ハマったのは以下のポイント

プラグインを試したい場合、%AppData%\MySQL\mysqlsh\pluginsフォルダを作ってそこに入れる
- 資料のほうが%AppData% とMySQLの間の\が抜けててそれに最初気づけなかった
あ、俺のPC、今Git入れてないわ…。（ZIP方式に切り替え）

2. 確認

f:id:next4us-ti:20200119092411j:plain

3. 起動について（講義にはない。補足）

起動スクリプトというものを用意することでJavaScriptとPythonモードで起動した場合は実行環境をカスタマイズできる（9.1 Working With Startup Scripts）
起動スクリプトは以下のどこかに、Pythonならmysqlshrc.py、JavaScriptならmysqlshrc.jsという名前で置く（以下Windowsでの話にしているが、Unix/Linuxも同様）
- 標準のグローバル構成パス：Windowsの場合： %PROGRAMDATA%\MySQL\mysqlsh\mysqlshrc.[js|py]
- MYSQLSH_HOMEを作った場合：Windowsの場合： %MYSQLSH_HOME%\share\mysqlsh\mysqlshrc.[js|py]
- mysqlshバイナリを含むフォルダー内：Windowsの場合：（mysqlsh binary path）\mysqlshrc.[js|py]
- MYSQLSH_USER_CONFIG_HOMEを作った場合：Windowsの場合： %MYSQLSH_USER_CONFIG_HOME%\mysqlshrc.[js|py]
- 標準ユーザー設定パス：Windowsの場合： %APPDATA%\MySQL\mysqlsh\mysqlshrc.[js|py]
- 但し、あとで読み込まれたほうが優先されるので置き場所は注意！
例えば、以下のようにして起動スクリプトのモジュール検索パスを追加できる

Python（mysqlshrc.py）

# Import the sys module
import sys

# Append the additional module paths
sys.path.append('~/custom/python')
sys.path.append('~/other/custom/modules')

JavaScript（mysqlshrc.js）

// Append the additional module paths
  sys.path = [...sys.path, '~/custom/js'];
  sys.path = [...sys.path, '~/other/custom/modules'];

9.2.2 Module Search Path Variable in Startup Scripts参照

次の段階

下準備はできたので、ここから以下を少しずつ進めてみたい。

公式ドキュメントを一通り読んで触ってみる
講義でやってたpluginを試す（起動スクリプトが置かれているフォルダの下にpluginsというフォルダを作ってそこに置く）
同様に、Replica Setも触ってみる
書籍を読む⇒Charles BellのIntroducing MySQL Shell
（余裕があれば）pluginを作る

2020-01-15

Percona Serverのマスキング試してみた

MySQL

MySQLのマスキングをPerconaで使ってみる

MySQLのマスキングはEnterpriseEditionでしか使えないが、PerconaServerはそれを無償で使えるように！

やるじゃん、ぺるこな！

でも使えるのは8.0.17-18バージョンからなので、ごめんね5.7…。

とりあえず、この記事をなぞりつつ、MySQLの公式ドキュメントを見ながら試してみた。

マスキング関数

まず、MySQLのマスキングについて公式ドキュメントを読む

ビューでマスキング済の結果を返す
ビューにマスキング関数を利用する
- 今のところは関数を見る限りあまり日本向けではない

マスキング関数の種類

マスキング関数の種類以下の3パターンに分かれている。

(ネーミングは公式の英語からこんな感じで勝手につけたので正しくないかも)

データマスキング関数
ランダムデータ生成関数
ランダムデータディクショナリベース関数

１．データマスキング関数（Data Masking Functions＝DMF）

文字列引数に対してマスキング操作を実行し、マスクされた結果を返します。

２．ランダムデータ生成関数（Random Data Generation Functions＝RDGF）

さまざまなタイプのデータに対してランダムな値を生成します。
- 生成された値には、可能であれば、正当なデータと間違われることを避けるために、デモンストレーションまたはテスト値用に予約された特性があります。

３．ランダムデータディクショナリベース関数（Random Data Dictionary-Based Functions＝RDDBF）

用語の辞書を操作し、それらに基づいて生成およびマスキング操作を実行します。

マスキング関数の一覧(MySQL8.0.19時点)

関数名	関数タイプ	説明	構文	引数
mask_inner	DMF	文字列の内部部分をマスクし、両端をそのままにして、結果を返します。オプションのマスキング文字を指定できます。マスキング文字はシングルバイト文字でなければなりません。マルチバイト文字を使用しようとすると、エラーが発生します	mask_inner(str, margin1, margin2 [, mask_char])	str：マスクする文字列。 margin1：マスクされないままにする文字列の左端の文字数を指定する負でない整数。値が0の場合、マスクされていない左端の文字はありません。 margin2：マスクされないままにする文字列の右端の文字数を指定する負でない整数。値が0の場合、右端の文字はマスクされません。 mask_char：（オプション）マスキングに使用する単一の文字。もし mask_charが指定されていない場合デフォルトは 'X'です。
mask_outer	DMF	文字列の左端と右端をマスクし、内部をマスクせずに、結果を返します。オプションのマスキング文字を指定できます。マスキング文字はシングルバイト文字でなければなりません。マルチバイト文字を使用しようとすると、エラーが発生します	mask_outer(str, margin1, margin2 [, mask_char])	str：マスクする文字列。 margin1：マスクする文字列の左端の文字数を指定する非負の整数。値が0の場合、左端の文字はマスクされません。 margin2：マスクする文字列の右端の文字数を指定する非負の整数。値が0の場合、右端の文字はマスクされません。 mask_char：（オプション）マスキングに使用する単一の文字。もし mask_charが指定されていない場合デフォルトは 'X'です。
mask_pan	DMF	支払いカードのプライマリアカウント番号をマスクし、最後の4桁以外をすべて'X'文字で置き換えた番号を返します。	mask_pan(str)	str：マスクする文字列。文字列はプライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。
mask_pan_relaxed	DMF	支払いカードのプライマリアカウント番号をマスクし、最初の6桁と最後の4桁を除くすべての'X'文字を文字に置き換えた番号を返します。最初の6桁は、支払いカード発行者を示します。	mask_pan_relaxed(str)	str：マスクする文字列。文字列は、プライマリアカウント番号に適した長さでなければなりませんが、それ以外の場合はチェックされません。
mask_ssn	DMF	米国の社会保障番号をマスクし、最後の4桁以外のすべてを'X'文字に置き換えた番号を返します。	mask_ssn(str)
gen_range	RDGF	指定された範囲から選択された乱数を生成します。	gen_range(lower, upper)	lower：範囲の下限を指定する整数。 upper：範囲の上限を指定する整数。下限より小さくてはいけません。
gen_rnd_email	RDGF	example.com ドメイン内のランダムな電子メールアドレスを生成します。	gen_rnd_email()	なし
gen_rnd_pan	RDGF	ランダムな支払いカードのプライマリアカウント番号を生成します。 (※割り当てられない可能性が0じゃないので公開向きではないと公式には記載されている)	gen_rnd_pan(size)	size：（オプション）結果のサイズを指定する整数。指定しない場合デフォルトは16です。指定する場合12〜19の範囲の整数である必要があります。
gen_rnd_ssn	RDGF	ランダムな米国社会保障番号をAAA-BB-CCCCAAABB形式で生成します。	gen_rnd_ssn()	なし
gen_rnd_us_phone	RDGF	ランダムな米国の電話番号を1-555-AAA-BBBB形式で生成します。	gen_rnd_us_phone()	なし
gen_blacklist	RDDBF	1つの辞書に存在する用語を2番目の辞書の用語に置き換え、置換する用語を返します。これにより、置換によって元の用語がマスクされます	gen_blacklist(str, dictionary_name, replacement_dictionary_name)	str：置換する用語を示す文字列。 dictionary_name：置換する用語を含む辞書に名前を付ける文字列。 replacement_dictionary_name：置換語を選択する辞書を指定する文字列。
gen_dictionary	RDDBF	辞書からランダムな用語を返します。	gen_dictionary(dictionary_name)	dictionary_name：用語を選択する辞書を指定する文字列。
gen_dictionary_drop	RDDBF	辞書を辞書レジストリから削除します。	gen_dictionary_drop(dictionary_name)	dictionary_name：辞書レジストリから削除する辞書に名前を付ける文字列。
gen_dictionary_load	RDDBF	ファイルを辞書レジストリにロードし、辞書名の引数を必要とする他の関数で使用される名前を辞書に割り当てます。この機能にはSUPER特権が必要です。辞書をリロードするには、まずgen_dictionary_drop()で辞書をドロップし、次にgen_dictionary_load()で再度ロードします。	gen_dictionary_load(dictionary_path, dictionary_name)	dictionary_path：辞書ファイルのパス名を指定する文字列。 dictionary_name：辞書の名前を提供する文字列。

環境構築

では、遊んで見るために環境を構築してみるか！

Percona8 インストール in AWS EC2

Installing Percona Server for MySQL on Red Hat Enterprise Linux and CentOS参照

今回は作業時のPercona Serverの最新Verである8.0.18-9 を入れました。

[ec2-user@ip-xxx ~]$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
percona-release-latest.noarch.rpm                                                                                                                                                    |  17 kB  00:00:00     
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm を調べています: percona-release-1.0-13.noarch
/var/tmp/yum-root-HSvVL4/percona-release-latest.noarch.rpm をインストール済みとして設定しています
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:1.0-13 を インストール
--> 依存性解決を終了しました。
amzn2-core/2/x86_64                                                                                                                                                                  | 2.4 kB  00:00:00     
amzn2extra-docker/2/x86_64                                                                                                                                                           | 1.3 kB  00:00:00     

依存性を解決しました

============================================================================================================================================================================================================
 Package                                           アーキテクチャー                         バージョン                               リポジトリー                                                      容量
============================================================================================================================================================================================================
インストール中:
 percona-release                                   noarch                                   1.0-13                                   /percona-release-latest.noarch                                    20 k

トランザクションの要約
============================================================================================================================================================================================================
インストール  1 パッケージ

合計容量: 20 k
インストール容量: 20 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-release-1.0-13.noarch                                                                                                                                               1/1 
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.

For example, to enable the Percona Server 8.0 repository use:

  percona-release setup ps80

Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.

For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html

  検証中                  : percona-release-1.0-13.noarch                                                                                                                                               1/1 

インストール:
  percona-release.noarch 0:1.0-13                                                                                                                                                                           

完了しました!
[ec2-user@ip-xxx ~]$ sudo percona-release setup ps80
* Disabling all Percona Repositories
* Enabling the Percona Server 8.0 repository
* Enabling the Percona Tools repository
<*> All done!
[ec2-user@ip-xxx ~]$  sudo yum install percona-server-server
読み込んだプラグイン:extras_suggestions, langpacks, priorities, update-motd
ps-80-release-noarch                                                                                                                                                                 | 2.9 kB  00:00:00     
ps-80-release-x86_64                                                                                                                                                                 | 2.9 kB  00:00:00     
tools-release-noarch                                                                                                                                                                 | 2.9 kB  00:00:00     
tools-release-x86_64                                                                                                                                                                 | 2.9 kB  00:00:00     
(1/4): tools-release-noarch/2/primary_db                                                                                                                                             | 1.1 kB  00:00:00     
(2/4): ps-80-release-noarch/2/primary_db                                                                                                                                             | 1.1 kB  00:00:00     
(3/4): ps-80-release-x86_64/2/primary_db                                                                                                                                             |  63 kB  00:00:00     
(4/4): tools-release-x86_64/2/primary_db                                                                                                                                             |  54 kB  00:00:00     
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-server.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: percona-server-client のパッケージ: percona-server-server-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ percona-server-client.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: libncurses.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
--> 依存性の処理をしています: libtinfo.so.5()(64bit) のパッケージ: percona-server-client-8.0.18-9.1.el7.x86_64
---> パッケージ percona-server-shared.x86_64 0:8.0.18-9.1.el7 を インストール
--> 依存性の処理をしています: percona-server-shared-compat のパッケージ: percona-server-shared-8.0.18-9.1.el7.x86_64
--> トランザクションの確認を実行しています。
---> パッケージ mariadb-libs.x86_64 1:5.5.64-1.amzn2 を 不要
---> パッケージ ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3 を インストール
---> パッケージ percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7 を 非推奨
--> 依存性解決を終了しました。

依存性を解決しました

============================================================================================================================================================================================================
 Package                                                  アーキテクチャー                   バージョン                                              リポジトリー                                      容量
============================================================================================================================================================================================================
インストール中:
 percona-server-server                                    x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                              53 M
 percona-server-shared-compat                             x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                             1.2 M
     mariadb-libs.x86_64 1:5.5.64-1.amzn2 を入れ替えます
依存性関連でのインストールをします:
 ncurses-compat-libs                                      x86_64                             6.0-8.20170212.amzn2.1.3                                amzn2-core                                       308 k
 percona-server-client                                    x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                              12 M
 percona-server-shared                                    x86_64                             8.0.18-9.1.el7                                          ps-80-release-x86_64                             1.3 M

トランザクションの要約
============================================================================================================================================================================================================
インストール  2 パッケージ (+3 個の依存関係のパッケージ)

総ダウンロード容量: 68 M
Is this ok [y/d/N]: y
Downloading packages:
(1/5): ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64.rpm                                                                                                                       | 308 kB  00:00:00     
warning: /var/cache/yum/x86_64/2/ps-80-release-x86_64/packages/percona-server-client-8.0.18-9.1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY    ] 3.6 MB/s |  24 MB  00:00:12 ETA 
percona-server-client-8.0.18-9.1.el7.x86_64.rpm の公開鍵がインストールされていません
(2/5): percona-server-client-8.0.18-9.1.el7.x86_64.rpm                                                                                                                               |  12 MB  00:00:04     
(3/5): percona-server-shared-8.0.18-9.1.el7.x86_64.rpm                                                                                                                               | 1.3 MB  00:00:00     
(4/5): percona-server-shared-compat-8.0.18-9.1.el7.x86_64.rpm                                                                                                                        | 1.2 MB  00:00:01     
(5/5): percona-server-server-8.0.18-9.1.el7.x86_64.rpm                                                                                                                               |  53 MB  00:00:18     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
合計                                                                                                                                                                        3.7 MB/s |  68 MB  00:00:18     
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
 Userid     : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
 Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
 Package    : percona-release-1.0-13.noarch (installed)
 From       : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか？ [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-server-shared-compat-8.0.18-9.1.el7.x86_64                                                                                                                          1/6 
  インストール中          : percona-server-shared-8.0.18-9.1.el7.x86_64                                                                                                                                 2/6 
  インストール中          : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64                                                                                                                         3/6 
  インストール中          : percona-server-client-8.0.18-9.1.el7.x86_64                                                                                                                                 4/6 
  インストール中          : percona-server-server-8.0.18-9.1.el7.x86_64                                                                                                                                 5/6 
Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/8.0/management/udf_percona_toolkit.html for more details
  削除中                  : 1:mariadb-libs-5.5.64-1.amzn2.x86_64                                                                                                                                        6/6 
  検証中                  : percona-server-client-8.0.18-9.1.el7.x86_64                                                                                                                                 1/6 
  検証中                  : ncurses-compat-libs-6.0-8.20170212.amzn2.1.3.x86_64                                                                                                                         2/6 
  検証中                  : percona-server-server-8.0.18-9.1.el7.x86_64                                                                                                                                 3/6 
  検証中                  : percona-server-shared-8.0.18-9.1.el7.x86_64                                                                                                                                 4/6 
  検証中                  : percona-server-shared-compat-8.0.18-9.1.el7.x86_64                                                                                                                          5/6 
  検証中                  : 1:mariadb-libs-5.5.64-1.amzn2.x86_64                                                                                                                                        6/6 

インストール:
  percona-server-server.x86_64 0:8.0.18-9.1.el7                                                     percona-server-shared-compat.x86_64 0:8.0.18-9.1.el7                                                    

依存性関連をインストールしました:
  ncurses-compat-libs.x86_64 0:6.0-8.20170212.amzn2.1.3                    percona-server-client.x86_64 0:8.0.18-9.1.el7                    percona-server-shared.x86_64 0:8.0.18-9.1.el7                   

置換:
  mariadb-libs.x86_64 1:5.5.64-1.amzn2                                                                                                                                                                      

完了しました!

Percona Server起動

[ec2-user@ip-xxx ~]$ sudo service mysql start
Redirecting to /bin/systemctl start mysql.service
[ec2-user@ip-xxx ~]$ sudo service mysql status
Redirecting to /bin/systemctl status mysql.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 水 2020-01-15 02:26:06 UTC; 4s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3294 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3376 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─3376 /usr/sbin/mysqld

 1月 15 02:26:00 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Starting MySQL Server...
 1月 15 02:26:06 ip-xxx.ap-northeast-1.compute.internal systemd[1]: Started MySQL Server.

Percona Server ログイン

[ec2-user@ip-xxx ~]$ sudo cat /var/log/mysqld.log |grep generated
2020-01-15T02:26:03.644352Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: xxxxxx
[ec2-user@ip-xxx ~]$ mysql -uroot -pxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18-9

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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>

root ユーザのパスワード変更

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

が出るのでパスワードを変更する

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'passxxxxxx';
Query OK, 0 rows affected (0.00 sec)

マスキング設定

mysql>  INSTALL PLUGIN data_masking SONAME 'data_masking.so';
Query OK, 0 rows affected (0.00 sec)

辞書登録

gen_dictionaryで行う。

辞書と呼ばれるものにマスキング用文字列登録します。(再起動すると読み直しが必要)

というわけで、お試しファイルを落としてくる

[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
--2020-01-15 02:50:00--  https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/first_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1289773 (1.2M) [text/plain]
`first_names.all.txt' に保存中

100%[==================================================================================================================================================================>] 1,289,773   --.-K/s 時間 0.03s   

2020-01-15 02:50:01 (39.1 MB/s) - `first_names.all.txt' へ保存完了 [1289773/1289773]
[ec2-user@ip-xxx ~]$ wget https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
--2020-01-15 02:50:45--  https://raw.githubusercontent.com/philipperemy/name-dataset/master/names_dataset/last_names.all.txt
raw.githubusercontent.com (raw.githubusercontent.com) をDNSに問いあわせています... 151.101.108.133
raw.githubusercontent.com (raw.githubusercontent.com)|151.101.108.133|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 786535 (768K) [text/plain]
`last_names.all.txt' に保存中

100%[==================================================================================================================================================================>] 786,535     --.-K/s 時間 0.04s   

2020-01-15 02:50:46 (18.1 MB/s) - `last_names.all.txt' へ保存完了 [786535/786535]

[ec2-user@ip-xxx ~]$ ll
合計 2032
-rw-rw-r-- 1 ec2-user ec2-user 1289773  1月 15 02:50 first_names.all.txt
-rw-rw-r-- 1 ec2-user ec2-user  786535  1月 15 02:50 last_names.all.txt
[ec2-user@ip-xxx ~]$ mv *.txt /tmp/
[ec2-user@ip-xxx ~]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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 gen_dictionary_load('/tmp/first_names.all.txt', 'first_names');
+----------------------------------------------------------------+
| gen_dictionary_load('/tmp/first_names.all.txt', 'first_names') |
+----------------------------------------------------------------+
| Dictionary load success                                        |
+----------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select gen_dictionary_load('/tmp/last_names.all.txt', 'last_names');
+--------------------------------------------------------------+
| gen_dictionary_load('/tmp/last_names.all.txt', 'last_names') |
+--------------------------------------------------------------+
| Dictionary load success                                      |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

テストテーブルの用意

[ec2-user@ip-xxx ~]$ wget https://github.com/datacharmer/test_db/archive/master.zip
--2020-01-15 03:00:43--  https://github.com/datacharmer/test_db/archive/master.zip
github.com (github.com) をDNSに問いあわせています... 52.69.186.44
github.com (github.com)|52.69.186.44|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Found
場所: https://codeload.github.com/datacharmer/test_db/zip/master [続く]
--2020-01-15 03:00:44--  https://codeload.github.com/datacharmer/test_db/zip/master
codeload.github.com (codeload.github.com) をDNSに問いあわせています... 52.68.31.213
codeload.github.com (codeload.github.com)|52.68.31.213|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 特定できません [application/zip]
`master.zip' に保存中

    [          <=>                                                                                                                                                      ] 36,687,757  3.15MB/s 時間 11s    

2020-01-15 03:00:55 (3.15 MB/s) - `master.zip' へ保存終了 [36687757]

[ec2-user@ip-xxx ~]$ ll
合計 35828
-rw-rw-r-- 1 ec2-user ec2-user 36687757  1月 15 03:00 master.zip
[ec2-user@ip-xxx ~]$ unzip master.zip
Archive:  master.zip
0b66c2338736779e3b150c7d125b1012d95a961f
   creating: test_db-master/
  inflating: test_db-master/Changelog  
  inflating: test_db-master/README.md  
  inflating: test_db-master/employees.sql  
  inflating: test_db-master/employees_partitioned.sql  
  inflating: test_db-master/employees_partitioned_5.1.sql  
   creating: test_db-master/images/
  inflating: test_db-master/images/employees.gif  
  inflating: test_db-master/images/employees.jpg  
  inflating: test_db-master/images/employees.png  
  inflating: test_db-master/load_departments.dump  
  inflating: test_db-master/load_dept_emp.dump  
  inflating: test_db-master/load_dept_manager.dump  
  inflating: test_db-master/load_employees.dump  
  inflating: test_db-master/load_salaries1.dump  
  inflating: test_db-master/load_salaries2.dump  
  inflating: test_db-master/load_salaries3.dump  
  inflating: test_db-master/load_titles.dump  
  inflating: test_db-master/objects.sql  
   creating: test_db-master/sakila/
  inflating: test_db-master/sakila/README.md  
  inflating: test_db-master/sakila/sakila-mv-data.sql  
  inflating: test_db-master/sakila/sakila-mv-schema.sql  
  inflating: test_db-master/show_elapsed.sql  
  inflating: test_db-master/sql_test.sh  
  inflating: test_db-master/test_employees_md5.sql  
  inflating: test_db-master/test_employees_sha.sql  
[ec2-user@ip-xxx ~]$ ll
合計 35832
-rw-rw-r-- 1 ec2-user ec2-user 36687757  1月 15 03:00 master.zip
drwxrwxr-x 4 ec2-user ec2-user     4096  4月  9  2019 test_db-master
[ec2-user@ip-xxx ~]$ cd test_db-master/
[ec2-user@ip-xxx test_db-master]$ ll
合計 168336
-rw-rw-r-- 1 ec2-user ec2-user      964  4月  9  2019 Changelog
-rw-rw-r-- 1 ec2-user ec2-user     4325  4月  9  2019 README.md
-rw-rw-r-- 1 ec2-user ec2-user     4193  4月  9  2019 employees.sql
-rw-rw-r-- 1 ec2-user ec2-user     6276  4月  9  2019 employees_partitioned.sql
-rw-rw-r-- 1 ec2-user ec2-user     7948  4月  9  2019 employees_partitioned_5.1.sql
drwxrwxr-x 2 ec2-user ec2-user       69  4月  9  2019 images
-rw-rw-r-- 1 ec2-user ec2-user      250  4月  9  2019 load_departments.dump
-rw-rw-r-- 1 ec2-user ec2-user 14159880  4月  9  2019 load_dept_emp.dump
-rw-rw-r-- 1 ec2-user ec2-user     1090  4月  9  2019 load_dept_manager.dump
-rw-rw-r-- 1 ec2-user ec2-user 17722832  4月  9  2019 load_employees.dump
-rw-rw-r-- 1 ec2-user ec2-user 39806034  4月  9  2019 load_salaries1.dump
-rw-rw-r-- 1 ec2-user ec2-user 39805981  4月  9  2019 load_salaries2.dump
-rw-rw-r-- 1 ec2-user ec2-user 39080916  4月  9  2019 load_salaries3.dump
-rw-rw-r-- 1 ec2-user ec2-user 21708736  4月  9  2019 load_titles.dump
-rw-rw-r-- 1 ec2-user ec2-user     4568  4月  9  2019 objects.sql
drwxrwxr-x 2 ec2-user ec2-user       77  4月  9  2019 sakila
-rw-rw-r-- 1 ec2-user ec2-user      272  4月  9  2019 show_elapsed.sql
-rwxr-xr-x 1 ec2-user ec2-user     1800  4月  9  2019 sql_test.sh
-rw-rw-r-- 1 ec2-user ec2-user     4878  4月  9  2019 test_employees_md5.sql
-rw-rw-r-- 1 ec2-user ec2-user     4882  4月  9  2019 test_employees_sha.sql
[ec2-user@ip-xxx test_db-master]$ view employees.sql
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:51
[ec2-user@ip-xxx test_db-master]$ mysql -uroot -ppassxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.18-9 Percona Server (GPL), Release 9, Revision 53e606f

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql>  show columns from employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

マスキングビュー作成(GEN_RANGE,GEN_DICTIONARY)

mysql> CREATE VIEW deidentified_employees
    -> AS
    -> SELECT
    ->   gen_range(900000000, 999999999) as emp_no,
    ->   makedate(year(birth_date), 1) as birth_date,
    ->   gen_dictionary('first_names') as first_name,
    ->   gen_dictionary('last_names') as last_name,
    ->   gender,
    ->   makedate(year(hire_date), 1) as hire_date
    -> FROM employees;
ERROR 1305 (42000): FUNCTION employees.  gen_range does not exist

どうやらPerconaの記事のそれをそのまま使うとエラーになるみたい。(どっかに変な文字コードでも入ってるのかな？)

書き換える

mysql> CREATE VIEW deidentified_employees
    -> AS
    -> SELECT 
    ->     GEN_RANGE(900000000, 999999999) AS emp_no,
    ->     MAKEDATE(YEAR(birth_date), 1) AS birth_date,
    ->     GEN_DICTIONARY('first_names') AS first_name,
    ->     GEN_DICTIONARY('last_names') AS last_name,
    ->     gender,
    ->     MAKEDATE(YEAR(hire_date), 1) AS hire_date
    -> FROM
    ->     employees;
Query OK, 0 rows affected (0.01 sec)

マスキング結果確認

ここでマスキング関数でマスキングされるのはemp_no,first_name,last_name、 birth_dateとhire_dateはMAKEDATE関数で置換表示してます。

元テーブル

mysql> SELECT * FROM employees LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

マスキングビュー

mysql> SELECT * FROM deidentified_employees LIMIT 10;
+-----------+------------+------------+-------------+--------+------------+
| emp_no    | birth_date | first_name | last_name   | gender | hire_date  |
+-----------+------------+------------+-------------+--------+------------+
| 912474868 | 1953-01-01 | sumio      | greff       | M      | 1986-01-01 |
| 998170886 | 1964-01-01 | haralambie | rauhuff     | F      | 1985-01-01 |
| 958103369 | 1959-01-01 | filippina  | angilletta  | M      | 1986-01-01 |
| 967871226 | 1954-01-01 | margarine  | munoz       | M      | 1986-01-01 |
| 907286604 | 1955-01-01 | ajahnay    | agel        | M      | 1989-01-01 |
| 914673574 | 1953-01-01 | kalibo     | manganiello | F      | 1989-01-01 |
| 906652311 | 1957-01-01 | shantai    | mitchael    | F      | 1989-01-01 |
| 998921306 | 1958-01-01 | rachela    | selbe       | M      | 1994-01-01 |
| 918607185 | 1952-01-01 | bradlay    | ehlke       | F      | 1985-01-01 |
| 904967645 | 1963-01-01 | shadiyah   | partyka     | F      | 1989-01-01 |
+-----------+------------+------------+-------------+--------+------------+
10 rows in set (0.00 sec)

マスキングビュー作成(MASK_INNER/MASK_OUTER)

文字列の中もしくは外側をマスキングする関数ですね

mysql> CREATE VIEW deidentified_salaries
    -> AS
    -> SELECT
    -> gen_range(900000000, 999999999) as emp_no,
    -> gen_range(40000, 80000) as salary,
    -> mask_inner(date_format(from_date, '%Y-%m-%d'), 4, 0) as from_date,
    -> mask_outer(date_format(to_date, '%Y-%m-%d'), 4, 2, '0') as to_date
    -> FROM salaries;
ERROR 1123 (HY000): Can't initialize function 'mask_outer'; Wrong argument type: MASK_OUTER(string, int, int, [char])

えー、こっちもそのままだと動かん…。(4つ目の引数を外してみる)

mysql> CREATE VIEW deidentified_salaries AS
    ->     SELECT 
    ->         GEN_RANGE(900000000, 999999999) AS emp_no,
    ->         GEN_RANGE(40000, 80000) AS salary,
    ->         MASK_INNER(DATE_FORMAT(from_date,'%Y-%m-%d'),4,0) AS from_date,
    ->         MASK_OUTER(DATE_FORMAT(to_date,  '%Y-%m-%d'),4,2) AS to_date
    ->     FROM
    ->         salaries;
Query OK, 0 rows affected (0.01 sec)

通った。

マスキング結果確認

from_dateでは前から4文字を除いてマスキング、to_dateでは前から4文字目の次の文字から2文字(-は含まない模様)を除きマスキング

元テーブル

mysql> SELECT * FROM salaries LIMIT 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
+--------+--------+------------+------------+
10 rows in set (0.00 sec)

マスキングビュー

mysql> SELECT * FROM deidentified_salaries LIMIT 10;
+-----------+--------+------------+------------+
| emp_no    | salary | from_date  | to_date    |
+-----------+--------+------------+------------+
| 950823834 | 70949  | 1986XXXXXX | XXXX-06-XX |
| 987110465 | 62877  | 1987XXXXXX | XXXX-06-XX |
| 964944005 | 43451  | 1988XXXXXX | XXXX-06-XX |
| 990093942 | 65625  | 1989XXXXXX | XXXX-06-XX |
| 997508813 | 53442  | 1990XXXXXX | XXXX-06-XX |
| 935495362 | 65082  | 1991XXXXXX | XXXX-06-XX |
| 991618027 | 65280  | 1992XXXXXX | XXXX-06-XX |
| 961487920 | 60056  | 1993XXXXXX | XXXX-06-XX |
| 947195247 | 45183  | 1994XXXXXX | XXXX-06-XX |
| 976597378 | 62853  | 1995XXXXXX | XXXX-06-XX |
+-----------+--------+------------+------------+
10 rows in set (0.00 sec)

ちなみに

MySQL 8.0.19の時点で戻り値がlatin1になっている(それ以前はbinary)
- 確認方法は SELECT CHARSET(gen_rnd_email());
- 文字セットを特定のものにしたい場合はCONVERT関数を使う（CONVERT(gen_rnd_email() USING utf8mb4);）

バグもしくはMySQL EEとは違ってるところ

mask_outer(a*****,a#####ってなるんじゃないの？)

mysql> SELECT mask_outer('abcdef', 0, 1, '*'), mask_outer('abcdef',0, 1, "#");
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 0, 1, '*') | mask_outer('abcdef',0, 1, "#") |
+---------------------------------+--------------------------------+
| abcde*                          | abcde#                         |
+---------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT mask_outer('abcdef', 0, 2, '*'), mask_outer('abcdef',0, 2, "#");
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 0, 2, '*') | mask_outer('abcdef',0, 2, "#") |
+---------------------------------+--------------------------------+
| abcd**                          | abcd##                         |
+---------------------------------+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT mask_outer('abcdef', 0, 5, '*'), mask_outer('abcdef',0, 5, "#");
+---------------------------------+--------------------------------+
| mask_outer('abcdef', 0, 5, '*') | mask_outer('abcdef',0, 5, "#") |
+---------------------------------+--------------------------------+
| abcdef                          | abcdef                         |
+---------------------------------+--------------------------------+
1 row in set (0.00 sec)

gen_rnd_panに数字が入れられない

mysql> SELECT mask_pan(gen_rnd_pan(19));
ERROR 1123 (HY000): Can't initialize function 'gen_rnd_pan'; Wrong argument list: gen_rnd_pan()

gen_rangeがマイナス値入れるとバグる

mysql> SELECT gen_range(100, 200), gen_range(-1000, -800);
+---------------------+------------------------+
| gen_range(100, 200) | gen_range(-1000, -800) |
+---------------------+------------------------+
| 159                 | 4294966311             |
+---------------------+------------------------+
1 row in set (0.00 sec)

感想

マスキング便利(バグっぽいところあるけど、無料でこれらが気軽に使えるのは良い)
- でも、2バイト文字は使えない…。
- 早く2バイト対応版欲しい！

mysql> SELECT mask_inner('41からはじめたんですよね?', 1, 2);
+----------------------------------------------------------+
| mask_inner('41からはじめたんですよね?', 1, 2)            |
+----------------------------------------------------------+
| 4XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX�?                      |
+----------------------------------------------------------+
1 row in set (0.00 sec)

ビューで同じデータを加工した形で見せることができるので色々な使い方ができそう
一方でPerconaServer　AND　8.0なのでそうじゃない環境の場合どうするか？
- 既存のDBがMySQLだったり、Percona8.0.16以前ならレプリケーションしてスレーブにPercona8環境を用意するのか？
- Aurora使ってる場合はbinlog有効にして、Aurora→RDS MySQL→Percona on EC2みたいな構成で使えるかな？
- 移行する予定があるなら、マスキング使う予定あればこの際Perconaにしちゃうとか？

MySQLも含めてマスキングがまだまだ使い勝手が微妙なところはあるものの、気軽に元データに手を入れずにマスキングできるのは本当に便利。無いよりはあったほうが絶対に良いので、なんとか使う方法を模索してみるのもありでは。

2020-01-04

【番外編】北海道（函館近辺）のおすすめポイントを語ってみる

今回は技術的なことはもちろん、MySQLのMの字も出ない番外編です

北海道（函館近辺）のおすすめポイントを語ってみる

嫁の実家があるので年末年始で行ってきた
今までにも何度も行ってるんで、函館近辺で良かったとことか雑に紹介してみる

遊

函館の街並み

懐かしいレトロな感じが残っている
明治～大正くらいの建物が普通に残ってる
外人墓地なんかもあって長崎とかと似ている
いつも「るろうに剣心っぽい」なと感じる街並みである（行ってみたらわかると思う）

夜景

実際に行ってみて見たほうがいい。写真では伝わらないものがある
嫁は地元民・学校が函館山にあったので何も思わないらしいが、自分は感動した
カップルなりたてなら行け！

https://www.hakobura.jp/nightview/

雪遊び

パウダースノーなので転んでも痛くない
手につかむとすぐ溶ける
雪の結晶が裸眼で見えるくらい細やか
自分でウェアとか用意すればかかるのはリフト代くらいなので安くつく

温泉

北海道はあちこちに温泉が湧いているが、函館近辺も多い
大沼だとスキーしたあとそのままホテルのお風呂が使えるので良い
寒い中で温泉はいると幸せな気分になる
大衆浴場的なお風呂屋さんも函館市内に多く点在しており、どれも綺麗で大きい

蔦屋書店

超巨大なTSUTAYA
無い本はほとんどないのでは？と思うような書籍量
休むところも多く、地元民は図書館のように使っている
もちろんレンタルやゲームやカード等の販売もやってる
駐車場もバカでかい

食

ラッキーピエロ

函館近辺でやってるファーストフード（ご当地なんちゃら）
主にハンバーガーを扱っているが、オムライスやカレーライス、カツ丼なんかも食べられる
価格はハンバーガーはさほど安くないがオリジナリティはある
オムライスやカレー、カツ丼あたりは量のわりには安い
ソフトクリーム、パフェはコスパ高い
最近は認知度が上がったため、外国人観光客も多く、激混みで店によっては40分～1h待ちもありうる
15歳から75歳までバイトを募集しているらしい

http://luckypierrot.jp/

寿司🍣（魚介類）

いうまでもなく函館に行ったら食べるべきものの一つ
スーパーの寿司ですら東京のそれと比べ物にならないが、ぜひ回転寿司に行くべき
一人3000円出せば「暫く寿司は食べなくてもいいや」という満足した気持ちになれる
東京で同じ味を同じ量食べようと思うと諭吉が必要
海岸通り沿いにあるところは混んでいるのでお昼過ぎくらいまでに行かないとネタが夜にはかなり無くなっている

大沼だんご🍡

保存料とか使ってないので、24h以内に食べないとどんどん固くなるだんご
買ってすぐに食べるのが吉。柔らかくてうまい
だんごといっても串にささっているわけではない
結構な量が入っている
水曜どうでしょうでも甘いものが苦手なミスターが苦しめられていたｗ

http://www.hakonavi.ne.jp/oonuma/numanoya.html

セイコーマート

函館にはセブンイレブンが結構あるが、それ以上にあるのがセイコーマートというコンビニ
コンビニ業界ではかなりホワイト企業として知られている
品物は他のコンビニで見ないようなオリジナル商品が多いが、特にドリンクとHOT CHEFと呼ばれる店内で作られるお弁当やおにぎり、フライドポテトなんかがかなり美味いのでおすすめ

https://www.seicomart.co.jp/instore/lineup.html

交通

飛行機

函館空港があり、羽田から約1hで着くんだから驚き
羽田まで行くのがそこまで大変じゃなければおすすめ
子供が小さければ膝に乗せられる年齢なら子供分が無料
本数が少ないJALとANN足して1日8便程度なのは注意（まあ、地方としては多いほうだけど）

新幹線

4～5hかかるけどずっと座って行けるのは良いし、何より連休シーズンは飛行機より安い
普段できない動画やラジオをまとめて聞ける
途中下車も可能だし
ただし、東北新幹線は基本自由席という概念がない（一部あるけど）ので注意

路面電車

市内はバスも走っているがそれ以上に路面電車が幅を利かせている
市外に行くにはバスだが、市内は路面電車でたいていどこでも行けるので1日乗車券を使って回るといい

車

車の運転に自信があるならレンタカーが吉
車があればどこでも行ける（函館はそんなに大きくないが、車がないといけないところは多い）
効率よく楽しむのであれば車はサイコー

ロードヒーティング

高速道路的なところは大体雪が溶けるように道路が温かい
大雪の日でも安心して走れる
さすがに住宅街とか近辺の道路には無い

その他

水曜どうでしょう最新作が最初に見られる（これは北海道だから）

ネガティブな話も少し書いてみる

函館市内と新函館北斗駅（新幹線の駅）は結構離れてて、電車で約30分ほどかかる
函館市内と函館空港も同様
嫁の実家にはインターネット環境がない
テレビも山に近いので映らなくなること多い
嫁の実家には犬と猫がいるのだが、子供がアレルギー持ちで全身湿疹だらけになる（でも動物は好き）
病院が遠い（車なしでは行けない）

まとめ

函館だけでも十分に北海道を堪能できる
夏も冬も北海道は楽しいし、おいしい
函館をスタート地点にして北海道旅行始めてみません？

※追記しました。後述のドキュメントの話は修正されました！(^▽^)

計器（インストゥルメント）とは

トップレベルインストゥルメントコンポーネント

パフォーマンススキーマ関連のシステムパラメータ

最大登録数について

実際にどう増えていってるか確認してみる

ところが・・・

ソースも見てみる

追記

AUTO_INCREMENTが巻き戻る

再現してみる

共通

MySQL5.7.29の場合

MySQL8.0.19の場合

MySQLを再起動すると・・・

MySQL5.7.29の場合

MySQL8.0.19の場合

ドキュメントやソース

バグだったのが8で直ったという扱い

5.7以前でも巻き戻らないようにしたいという人は

ところでタイトルの意味は？

ぶっちゃけ

参考

MySQL8でCHAR関数がドキュメントどおりになってない

MySQL5.7.29の場合

MySQL8.0.19の場合

これ、バグっぽいな

バグレポを見る。

そして

MySQL8.0.19の場合 by docker

MySQL8.0.18の場合 by docker

バグレポ

解決方法

試してみます

リリースノートを見る

感想

1. MySQL APT Repositoryの取得

2. インストール＆アップデート

実行例

3. MySQL Shellのインストール

実行例

4. 確認

mysqlshのHELP（オプション確認）

DB接続

実行例

スキーマ変更は\use <schema>

セッションの再接続

接続先DBサーバの状態確認

コマンド履歴の確認

ページャーの設定と解除

抜けるときは\qか\quitか\exitで

最後に

MySQL Shell をWindowsに入れてみる

セッションで使われた資料

もっと知ってもらいたい！

インストール前のおススメ

最新のMySQL Shellを使う！

実行履歴を保存しよう！

デフォルトのモードを設定しよう（defaultMode）

MySQLシェルオプション

Windows でのMySQL Shellのセットアップ

1. インストーラをダウンロード

ハマったのは以下のポイント

2. 確認

3. 起動について（講義にはない。補足）

Python（mysqlshrc.py）

JavaScript（mysqlshrc.js）

次の段階

MySQLのマスキングをPerconaで使ってみる

やるじゃん、ぺるこな！

マスキング関数

マスキング関数の種類

１．データマスキング関数（Data Masking Functions＝DMF）

２．ランダムデータ生成関数（Random Data Generation Functions＝RDGF）

３．ランダムデータディクショナリベース関数（Random Data Dictionary-Based Functions＝RDDBF）

マスキング関数の一覧(MySQL8.0.19時点)

環境構築

Percona8 インストール in AWS EC2

Percona Server起動

Percona Server ログイン

スキーマ変更は`\use <schema>`