MySQL8.0.20で追加・変更されたパラメータを眺める

はじめに

MySQL8.0.20で追加・変更されたパラメータをなんとなく眺めてみました。

パラメータの説明については基本的にはドキュメントからの引用です。

変更されたパラメータ一覧

例によってとみたさんのパラメータ比較ツールをお借りしました。

Parameter	8.0.20	説明
binlog-transaction-compression	FALSE	バイナリログファイルに書き込まれるトランザクションの圧縮を有効にする
binlog-transaction_compression_level-zstd	3	バイナリログファイルの圧縮レベルを設定
innodb_doublewrite_batch_size	0	バッチで書き込むダブルライトバッファページの数
innodb_doublewrite_dir	(No default value)	ダブルライトバッファのファイルディレクトリ
innodb_doublewrite_files	0	ダブルライトファイルの数
innodb_doublewrite_pages	0	バッチ書き込みのスレッドあたりの二重書き込みページの最大数
mysqlx_deflate_default_compression_level	3	サーバが Xプロトコル接続で Deflate アルゴリズムに使用する既定の圧縮レベル
mysqlx_deflate_max_client_compression_level	5	サーバが X プロトコル接続上の Deflate アルゴリズムに対して許可する最大の圧縮レベル
mysqlx_lz4_default_compression_level	2	サーバが X プロトコル接続で LZ4 アルゴリズムに使用する既定の圧縮レベル
mysqlx_lz4_max_client_compression_level	8	サーバが X プロトコル接続上の LZ4 アルゴリズムに対して許可する最大の圧縮レベル
mysqlx_zstd_default_compression_level	3	X プロトコル接続でサーバーが zstd アルゴリズムに使用するデフォルトの圧縮レベル
mysqlx_zstd_max_client_compression_level	11	サーバが X プロトコル接続上の zstd アルゴリズムに対して許可する最大の圧縮レベル
performance_schema_error_size	(number of server error codes)	インスツルメント化されたサーバーエラーコードの数

バイナリログトランザクション圧縮

MySQL8.0.20からbinlogが圧縮できます。

これについてはけんつさんが検証してくれてますので、非常に参考になります。

パラメータについては以下の通り

binlog_transaction_compression

• このサーバーのバイナリ ログ ファイルに書き込まれるトランザクションの圧縮を有効にします。
• デフォルト値は OFF 。
• 圧縮に使用する zstd アルゴリズムのレベルを設定するには、binlog_transaction_compression_level_zstd システム変数を使用します。
• バイナリ・ログ・トランザクション圧縮が有効な場合、トランザクション・ペイロードは圧縮され、単一のイベント (Transaction_payload_event) としてバイナリ・ログ・ファイルに書き込まれます。
• 圧縮されたトランザクション ペイロードは、レプリケーション スレーブ、他のグループ レプリケーション グループ メンバ、または mysqlbinlog のようなクライアントにレプリケーション ストリームで送信されている間、圧縮された状態のままでリレー ログに書き込まれます。
• したがって、バイナリログのトランザクション圧縮は、トランザクションの発信者と受信者の両方（およびそれらのバックアップのための）ストレージスペースを節約し、トランザクションがサーバインスタンス間で送信されるときにネットワーク帯域幅を節約します。
• binlog_transaction_compression=ON が直接効果を発揮するためには、サーバー上でバイナリロギングが有効になっている必要があります。
• MySQLサーバインスタンスがバイナリログを持たない場合、MySQL 8.0.20からのリリースであれば、binlog_transaction_compressionの値に関係なく、圧縮されたトランザクションペイロードを受信、処理、表示することができます。
• そのようなサーバインスタンスによって受信された圧縮されたトランザクションペイロードは、その圧縮された状態でリレーログに書き込まれるので、レプリケーショントポロジ内の他のサーバによって実行された圧縮から間接的に恩恵を受けます。
• このシステム変数は、トランザクションのコンテキスト内では変更できない。
• このシステム変数のセッション値の設定は制限された操作です。セッションユーザーは制限されたセッション変数を設定するのに十分な権限を持っていなければならない。
• セクション5.1.9.1「システム変数の特権」を参照してください。
• 圧縮されるイベントと圧縮されないイベントの詳細や、トランザクション圧縮が使用されているときの動作の変化など、バイナリログトランザクション圧縮の詳細については、5.4.4.5項 「バイナリログトランザクション圧縮」を参照してください。

もしCPUはヒマしてるけど、大量にデータ更新してて、ネットワークがネックなMySQL環境があればONにして、次の圧縮レベルを調整すると良いかもしれませんね。

binlog_transaction_compression_level_zstd

• このサーバのバイナリログトランザクション圧縮の圧縮レベルを設定します。
• 値は、圧縮努力を決定する整数で、1 (最も低い) から 22 (最も高い) までの値を指定します。
• このシステム変数を指定しない場合、圧縮レベルは3に設定されます。
• 圧縮レベルが高くなると、データ圧縮率が高くなり、トランザクション・ペイロードに必要なストレージ・スペースとネットワーク帯域幅が削減されます。
• しかし、データ圧縮に必要な労力も増加し、時間と発信元サーバのCPUおよびメモリリソースが必要となる。圧縮努力の増加は、データ圧縮率の増加と直線的な関係はありません。
• このシステム変数は、トランザクションのコンテキスト内では変更できません。
• このシステム変数のセッション値の設定は制限された操作です。
• セッションユーザは、制限されたセッション変数を設定するのに十分な権限を持っていなければならない。
• セクション5.1.9.1 「システム変数の特権」を参照してください。

けんつさんの記事から読むに、デフォルトの3でも半分くらいになるみたいなので、22とかどうなっちゃうの？とｗ

ダブルライトバッファの改良

• 8.0.20以降、doublewriteバッファストレージ領域をInnoDBシステムテーブルスペースではなく、別途専用ファイルに分けることができるようになりました。
• これにより書き込みレイテンシが減少するため、スループットを向上させることができ、ダブルライトバッファページの配置に関して柔軟性が提供されます。
• 8.0.20では、ダブルライトバッファの構成用にパラメータが追加されました。

ドキュメントを読む限りはinnodb_doublewrite_dir以外はあまり変更する必要は無さそうですが、書き込み量やファイルサイズからデフォルト値だと…という場合に触ってみる可能性があるかも、でしょうか。

ダブルライトバッファについて

念のため、ダブルライトバッファについて軽く触れておくと、

• InnoDBではデータファイル更新前にダブルライトバッファと呼ばれる領域（ファイル）にデータを書き込む。
• マシンクラッシュでデータページが壊れていても、ダブルライトバッファからリカバリされる。
• ダブルライトバッファが壊れていた場合は、そのデータは破棄。
• ダブルライトバッファは連続した領域なので、書き込みのオーバーヘッドはデータファイルより少ない
  • とはいえ、書き込み量、利用領域が増えるので不要な場合は無効にできる

innodb_doublewrite_batch_size

• バッチで書き込むダブルライトバッファページの数を定義します。
• 基本的にはデフォルト値のままで問題ないはずですが、パフォーマンスチューニングしたい場合には調整するパラメータ。

innodb_doublewrite_dir

• ダブルライトバッファのファイルディレクトリを定義します。
• ディレクトリが指定されていない場合、ディレクトリに二重書き込みファイルが作成されます。
• 指定されていない場合は、innodb_data_home_dirのデフォルトでデータディレクトリが作成されます。
• スキーマ名との競合を避けるために、指定されたディレクトリ名の前にハッシュ記号「#」が自動的に付加されます。
  • ただし、ディレクトリ名で「.」、「#」または「/」がプレフィックスとして明示的に指定されている場合、ハッシュ記号「#」はディレクトリ名のプレフィックスではありません。
• 理想的には、doublewriteディレクトリは、利用可能な最速のストレージメディアに配置する必要があります。

innodb_doublewrite_files

• ダブルライトファイルの数を定義します。
• デフォルトでは、バッファープールインスタンスごとに2つの二重書き込みファイルが作成されます。
• 少なくとも、2つの二重書き込みファイルがあります。
• doublewriteファイルの最大数は、バッファープールインスタンスの数の2倍です。（バッファー・プール・インスタンスの数はinnodb_buffer_pool_instances 変数によって制御されます。）
• 二重書き込みファイル名の形式は次のとおりです 。
  • たとえば、次のdoublewriteファイルは、ページサイズが16KBで単一のバッファープールを持つMySQLインスタンス用に作成されたものです。

  • ib_page_size_file_number.dblwrInnoDB

#ib_16384_0.dblwr
#ib_16384_1.dblwr

• 基本的にはデフォルト値のままで問題ないはずですが、パフォーマンスチューニングしたい場合には調整するパラメータ。

innodb_doublewrite_pages

• バッチ書き込みのスレッドあたりの二重書き込みページの最大数を定義します。
• 値が指定されていない場合は、innodb_doublewrite_pagesはinnodb_write_io_threadsの値に設定されます。
• 基本的にはデフォルト値のままで問題ないはずですが、パフォーマンスチューニングしたい場合には調整するパラメータ。

Xプロトコル接続を介して送信されるメッセージの圧縮

MySQL 8.0.19以降、Xプロトコル接続を介して送信されるメッセージの圧縮がサポートされました。

サーバーとクライアントが使用する圧縮アルゴリズムに同意した場合、接続を圧縮でき、デフォルトでは、Deflate、LZ4、およびzstd圧縮アルゴリズムを許可します。

ところが、MySQL 8.0.19では、サーバー側で圧縮レベルがデフォルト設定化されていて、クライアントはこれを調整することができません。

それがMySQL 8.0.20以降、クライアントはXプロトコル接続のケイパビリティ ネゴシエーション（※）中に特定の圧縮レベルを要求できるようになったとのこと。

一方でアルゴリズムごとに最大圧縮レベルを設定できるようになっていて、サーバーが忙しい時に、クライアントが高圧縮レベルを要求しても、最大圧縮レベル以上の負荷をかけることを防ぎます。

ちなみに、この圧縮レベルの要求はMySQL Shell経由でのみ行われるため、現時点ではMySQLクライアントやその他のConnector系はサポートされていないようです。（20.5.5項 「X プラグインによる接続圧縮」より）

ちなみに、ケイパビリティ ネゴシエーション(capability negotiation)っていうのがよくわからんな、と思って調べたらドキュメントにありました。

ケイパビリティ ネゴシエーション(capability negotiation)

14.2.1.3 Capability Negotiation

要するに、サーバーとクライアントでやり取りするのに色んなもの使うし、使えますよ、と。（例えば、サーバーのバージョンや認証情報（認証方式）とか）

mysqlx_deflate_default_compression_level

• サーバが X プロトコル接続で Deflate アルゴリズムに使用する既定の圧縮レベル。
• レベルは、1 (最も低い圧縮率) から 9 (最も高い圧縮率) までの整数で指定。
• このレベルは、クライアントがケイパビリティ ネゴシエーション中に圧縮レベルを要求しない場合に使用されます。
• このシステム変数を指定しない場合、サーバーはレベル3を既定値として使用します。
• 詳細については、20.5.5項 「X プラグインによる接続圧縮」を参照してください。

mysqlx_deflate_max_client_compression_level

• サーバが X プロトコル接続上の Deflate アルゴリズムに対して許可する最大の圧縮レベル。
• 範囲は、このアルゴリズムの既定の圧縮レベルと同じ(1から9)です。
• クライアントがこれより高い圧縮レベルを要求した場合、サーバはここで設定したレベルを使用します。
• このシステム変数を指定しない場合、サーバは最大圧縮レベルを 5 に設定します。

Deflateとは

Deflate（デフレート）とはLZ77とハフマン符号化を組み合わせた可逆データ圧縮アルゴリズム。フィル・カッツが開発した圧縮ツールPKZIPのバージョン2で使われていた。ZIPやgzipなどで使われている。1996年5月に RFC 1951 としてドキュメント化された。ヘッダーやフッターをつけた zlib (RFC 1950) 形式や gzip (RFC 1952) 形式とともに使われる事が多い。 Deflateアルゴリズムが利用されているソフトウェアの一例を挙げる。

zlib ZIP GZIP 7z Portable Network Graphics (PNG) また、ほとんどのプログラミング言語で利用できる。以下はその一例。

Java - Deflater クラスで nowrap を有効にすることにより素の deflate が扱え、別途 zlib 形式や gzip 形式のヘッターやフッターの付いた物も扱える。 Perl PHP Python Ruby C#、VB.NET等の.NET Framework 2.0以降対応言語 - DeflateStream クラスで素の deflate もしくは GZipStream クラスで gzip 形式。 Apache HTTP ServerなどのWebサーバでも圧縮通信を zlib 形式で Deflate を使って実装している。 (Wikipediaより)

というわけで普段からよくお世話になってる可逆圧縮方式のあいつらはDeflateだったわけでした。

mysqlx_lz4_default_compression_level

• サーバが X プロトコル接続で LZ4 アルゴリズムに使用する既定の圧縮レベル。
• レベルは、0 (最も低い圧縮率) から 16 (最も高い圧縮率) までの整数で指定します。
• このレベルは、クライアントがケイパビリティ ネゴシエーション中に圧縮レベルを要求しない場合に使用されます。
• このシステム変数を指定しない場合、サーバーはレベル2を既定値として使用します。
• 詳細については、20.5.5項 「X プラグインによる接続圧縮」を参照してください。

LZ4とは

LZ4 は圧縮と展開の速さに焦点を当てた可逆圧縮アルゴリズムである。バイト指向の圧縮方法であるLZ77ファミリーに属する。 (Wikipediaより)

mysqlx_lz4_max_client_compression_level

• サーバが X プロトコル接続上の LZ4 アルゴリズムに対して許可する最大の圧縮レベル。
• 範囲は、このアルゴリズムの既定の圧縮レベルと同じです。
• クライアントがこれより高い圧縮レベルを要求した場合、サーバはここで設定したレベルを使用します。
• このシステム変数を指定しない場合、サーバは最大圧縮レベルを 8 に設定します。

mysqlx_zstd_default_compression_level

• Xプロトコル接続でサーバーが zstd アルゴリズムに使用するデフォルトの圧縮レベル。
• 1.4.0 以降の zstd ライブラリのバージョンでは、1 から 22 までの正の値 (最高の圧縮率)、または負の値 (圧縮率が徐々に低くなることを表す) を設定できます。
• 0 の値は 1 の値に変換されます。
• zstd ライブラリの以前のバージョンでは、3 の値しか指定できません。
• このレベルは、ケイパビリティ・ネゴシエーション中にクライアントが圧縮レベルを要求しない場合に使用されます。
• このシステム変数を指定しない場合、サーバーはレベル3をデフォルトとして使用します。
• 詳細については、20.5.5項 「X プラグインによる接続圧縮」を参照してください。

Zstandard (Zstd)

Zstandard (Zstd) は、2015年からFacebookに所属しているYann Colletによって開発された可逆圧縮アルゴリズムである。またCで書かれた前述のアルゴリズムのリファレンス実装の名前でもある。 Zstandardは現在主流であるDeflate (ZIP, gzip) アルゴリズムによるものと遜色ない圧縮を、より高速に行えるように設計されている。 Zstandardは大きな検索窓の辞書式圧縮アルゴリズム (LZ77) とエントロピー符号化を併用しており、エントロピー符号化ステージで有限状態エントロピー(FSE)のtANS（英語版） あるいはハフマン符号化を使用している。Zstandardの実装で特徴的なのはエントロピー復号化時に逆方向から読み取ることである。 (Wikipediaより)

mysqlx_zstd_max_client_compression_level

• サーバが X プロトコル接続上の zstd アルゴリズムに対して許可する最大の圧縮レベル。
• 範囲は、このアルゴリズムの既定の圧縮レベルと同じです。
• クライアントがこれより高い圧縮レベルを要求した場合、サーバはここで設定したレベルを使用します。
• このシステム変数を指定しない場合、サーバは最大圧縮レベルを 11 に設定します。

performance_schema_error_size

• インスツルメント化されたサーバーエラーコードの数。
• デフォルト値はサーバーエラーコードの実際の数なので、サーバーエラーコードが増えたことで変更となりました。
• 値は0から最大値まで設定できますが、使用目的は、デフォルト（すべてのエラーを計測する）または0（エラーなしを計測する）に設定することです。
• エラー情報は要約テーブルに集約されます。セクション26.12.18.11「エラーサマリーテーブル」を参照してください。

まとめ

• MySQL8.0.20では圧縮形の機能追加が二つあったことがパラメータから見て取れました。
  • binlogの圧縮はZstdのみ。
  • Xプロトコルの圧縮はDeflate、LZ4、Zstdの三つの圧縮レベルのデフォルト値と最大値がパラメータとして追加
    • クライアントが要求した圧縮レベル＞サーバーのmax値だったときはサーバのmax値のほうが使われる
    • Xプロトコルの圧縮はMySQL Shell専用なので注意
  • 圧縮形はCPU使用率が上がり、CPU使用率＋圧縮時間とネットワーク転送時間とのトレードオフとなります。
• ダブルライトバッファ系パラメータはinnodb_doublewrite_dir以外はあまり変更する必要は無さそうです。
  • innodb_doublewrite_dirを高速なディスクに設定すると良さそうです。

さいごに

• 和訳部分についてはドキュメントをDeepLやGoogle翻訳を使って訳したものを一部引用してます。
• 日本語がおかしい部分は自分で訳しましたが、合ってなかったらごめんなさい。

MySQLでのテーブルコピー（InnoDBとMyISAM）

MySQLでのテーブルコピーを考える

MySQLでテーブルコピー（同一インスタンス内とか別インスタンスとかあるけど）って意外とテストとかちょっとしたリカバリ作業用にしたくなりますよね。

というわけで、InnoDBとMyISAM関連だけですが書き出してみました。

コピーパターン

僕がパッと思いつく限りだとこの辺ですが、後から追記する可能性あり〼。

• Mysql Enterprise Backup（MEB）
• mysqldump/mysqlpump
• INSERT...SELECT
• CREATE TABLE...SELECT
• OSファイルコピー
• トランスポータブルテーブルスペース(TTS)
• MEB + TTS
• IMPORT TABLE
• その他

MEB（InnoDB,MyISAM）

MySQL Enterprise Backup と呼ばれる商用の製品です。

以下、ドキュメントからの転記です

• 実行中の MySQL データベース (InnoDB および MyISAM テーブルを含む) を、データベースの整合性のあるスナップショットを生成しながら、操作の中断を最小限に抑えてバックアップできます。
• MySQL Enterprise Backup が InnoDB テーブルをコピーしている間は、InnoDB テーブルと MyISAM テーブルの両方に対する読み取りと書き込みを続行できます。
• MyISAM およびその他の InnoDB 以外のテーブルのコピー中は、これらのテーブルに対する (書き込みではなく) 読み取りが許可されます。
• さらに、MySQL Enterprise Backup では、圧縮バックアップファイルを作成したり、InnoDB テーブルのサブセットをバックアップしたりすることもできます。
• MySQL のバイナリログと組み合わせると、ポイントインタイムリカバリを実行できます。

ユーザーガイドはこちらですが、EE版を契約した人には使わない手は無い代物だと思います。

mysqldump/mysqlpump（InnoDB,MyISAM）

皆さんご存じmysqldumpとあまりご存じじゃないmysqlpumpですｗ

mysqlpumpだけの機能だとか、mysqldumpとの違いなんかは既に僕ら大好きyoku0825さんがまとめてくださってるので多くは語らず。

あえて言うなら、mysqlpumpはMySQL5.7以降で使いましょう。

そして、mysqldumpと比べると新しい機能なのでまだまだ枯れてない部分もありそうです。

こういう話もありますので、最新を使いましょうね！

INSERT...SELECT（InnoDB,MyISAM）

create tableして、INSERT...SELECTする、こちらもおなじみのやり方ですね。

create tableして、csv,tsv形式のファイルを作ってload dataコマンドやmysqlimportコマンドという方法なら並列化できていいかもしれません。

CREATE TABLE...SELECT（InnoDB,MyISAM）

CREATE TABLE...SELECTもちょっとしたコピーテーブル作るのには便利ですよね。

でも、インデックスや制約を自動で作らないので、以下のような書き方でインデックスや制約は追加してあげましょう。

mysql> CREATE TABLE t2 (PRIMARY KEY (a),INDEX (b),foreign key f1 (a) references t1(a)) SELECT * FROM t1;

AUTO_INCREMENTあたりも保持しないので気を付けましょう。

OSファイルコピー（InnoDB,MyISAM）

これは2パターンある。MySQLを停止するパターンと停止しないで行うパターンです。

停止パターン

ドキュメントに例があります。

Not停止パターン

停止パターンとほぼ同じですがshutdownをしないので、メモリやらバイナリログにデータが残ってる可能性があるのでフラッシュやsync作業が必要かも。

https://dev.mysql.com/doc/refman/8.0/en/flush.html

トランスポータブルテーブルスペース（InnoDB）

InnoDBだとTTSが便利かつ高速。

ドキュメントの手順だと別のサーバーへのコピーとなっているが、同じインスタンス内の別スキーマでもOK。

注意点としては外部キーは持てないので、必要なら後から適用する必要があるところ。

MEB + TTS（InnoDB）

MEBのオプションとして先ほど挙げたTTSがバックアップとして使えるというものです。

詳しく知りたい方はドキュメントを参照してください。

IMPORT TABLE（MyISAM）

ここまではMySQL5.6以降であれば使えました。（え？5.5？もう僕は知らない子ですねぇ…）

そして、MySQL8.0では"IMPORT TABLE"というのができました。

まあ、MySQL8.0ではMyISAM自体非推奨であり、Perconaのブログでは

”MySQL 8.0: The end of MyISAM”

なんてタイトルの話があったりして、今更じゃね？という気もしないではないんですが…。

IMPORT TABLEはドキュメントを読むと以下の通りです。

• MyISAMのテーブルをシリアル化された辞書情報（.sdi）メタデータファイルに含まれる情報に基づいてインポートします。
• .sdiを読むためのIMPORT TABLEコマンドにはFILE権限とテーブルを作成するためのCREATE権限が必要です。
• mysqldumpでエクスポートして、mysqlでインポートする方法よりも高速です。

参照：13.2.5 IMPORT TABLE Statement

作業手順としてはOSファイルコピーと似たような感じですが、んーーーー、使うメリットが今のところわかってません（苦笑）

その他

サードパーティ製のツールを使う方法です。

GUI、CUI色々ありますが、有名なのはPercona社のPercona XtraBackupですね。

xtrabackupのオプションの--tablesオプションを使えば対象テーブルのみをコピーしてくれるようです。

Percona XtraBackupの使い方については北川さんの記事がとても分かりやすいですが、他にも色々記事があるので使う場合はググって読んでみるといいです。

まとめ

MySQLでテーブルコピーに使えそうなものを雑に挙げてみました。

速度で行くとOSファイルコピーは最強でしょうね。ただ、権限的なものが強くなりますし、基本的には同一データベース名にコピーするものなので、開発者が気軽にできないところが難点でしょうか。

開発者が使うなら時間はかかりますが、mysqldumpやSQLでできるようなものがいいでしょう。

あとはTTSはそこまで有名じゃないですが、非常に便利だと思います。気になった方は試してみてはいかがでしょうか。

あ、そういえばロックの話全然書いてなかった…(;^_^A

パフォーマンススキーマの計器の最大登録数について

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

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

計器（インストゥルメント）と呼ばれるコードを通じてMySQL（NDB Clusterを含む）はパフォーマンスに関するデータを取得しています。

取得されたデータはバッファ上に格納されます。

それがperformance_schemaデータベースのテーブル達です。

ユーザーはperformance_schemaデータベースのテーブルやsysビューを参照することでこれらの計器が収集した結果を取得することができます。

命名規則

計器（インストゥルメント）の名前は '/' 文字で区切られた一連のコンポーネントから構成されます。

この命名規則はどのような情報を取得しているかのおおよその目安になります。

名前空間がツリー上の構造を持っており、インストゥルメント名のコンポーネントは左から右に、より一般的からより具体的になります。

例えば、

wait/io/file

といった場合、ファイル I/O 操作の待機系情報を取得するコンポーネントが含まれています。

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

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

計器（インストゥルメント）の一番大枠となる部分です。

以下、各トップレベルインストゥルメントコンポーネント下にあるものの数を8.0.20版でざっと書き出します。（performance_schema.setup_instrumentsより）

トップ	コンポーネント数	説明
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)という欄が追加されてます！やったね！

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

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さんに教えてもらったんですよね。

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

参考

• https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
• https://lefred.be/content/bye-bye-bug-199/
• https://bugs.mysql.com/bug.php?id=199
• https://www.s-style.co.jp/blog/2018/08/2284/

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

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さんからコメントを頂く。

なるほど、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)

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

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

バグレポ

既に木村さんの方で出してもらってたようです。ありがたや。

MySQL Bugs: Access denied

解決方法

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

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からの変更だと気づけましたし、そのあとのコメントの通り、これ誰が嬉しいのかよくわからない変更ですよね（苦笑）

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

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

前回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

or

 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を使うならそっちなので、今度はそっちの話を書こうかと思います。

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

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とか）
• イメージとしてはこちらを参照

今回の講義では、来ているメンバーが濃い目（笑）と思ったことからプラグイン部分と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

最新のMySQL Shellを使う！

最新を使う理由としては新しいほうがより便利になっていっていることから。

実行履歴を保存しよう！

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)

1. MySQL Shellコード履歴に保存するエントリの最大数を5000にセットし、
2. ウィザードモードを有効にして、構成ファイルへ設定を保存
3. 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からダウンロードします。

• 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. 確認

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を作る