MySQL キャラクタセット（文字コード）の変換方法おさらい

ALTER TABLE xxx MODIFY 〜 でキャラクタセットを変換するのと、ALTER TABLE xxx CONVERT TO〜 で変換するのは、どう違うんだっけ？ ってなったので、おさらい。

データベースのデフォルトキャラクタセット

MySQL はカラム単位でキャラクタセット（文字コード）を指定することが出来ます。

データベース（スキーマ）のキャラクタセットは、「テーブル作成時にテーブルのキャラクタセットを指定しなかったときに採用されるキャラクタセット」の指定です。 CREATE DATABASE db1 CHARACTER SET ujis と DEFAULT を省略して書くことが出来ますが、省略せずに書くと、DEFAULT CHARACTER SET です。 デフォルトのキャラクタセットの指定であることが、SHOW CREATE TABLEの結果からもわかります。

# データベースのデフォルトキャラクタセットを ujis にする
mysql> CREATE DATABASE db1 CHARACTER SET ujis;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET ujis */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

テーブルの DEFAULT CHARACTER SET を指定しない場合）

DEFAULT CHARSET=ujis と、データベースのデフォルトキャラクタセットを引き継いでいることがわかる。

mysql> CREATE TABLE tbl_no_char (pk SERIAL, c1 VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_no_char \G
*************************** 1. row ***************************
       Table: tbl_no_char
Create Table: CREATE TABLE `tbl_no_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=ujis
1 row in set (0.00 sec)

テーブルの DEFAULT CHARACTER SET を明示的に utf8mb4 に指定）

mysql> CREATE TABLE tbl_with_char (pk SERIAL c1 VARCHAR(255) NOT NULL) CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_with_char \G
*************************** 1. row ***************************
       Table: tbl_with_char
Create Table: CREATE TABLE `tbl_with_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

テーブルのデフォルトキャラクタセット

テーブルのデフォルトキャラクタセットも同様です。CREATE TABLE時にカラムのキャラクタセットを明示的に指定しなかった場合、テーブルのデフォルトキャラクタセットが採用されます。

mysql> CREATE TABLE tbl_mix_char (
    pk SERIAL,
    ujis_col VARCHAR(255) CHARACTER SET ujis NOT NULL,
    utf8mb4_col VARCHAR(255) NOT NULL) 
    CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_mix_char \G
*************************** 1. row ***************************
       Table: tbl_mix_char
Create Table: CREATE TABLE `tbl_mix_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ujis_col` varchar(255) CHARACTER SET ujis NOT NULL,
  `utf8mb4_col` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

念の為、ujisには存在しない絵文字🍣をINSERTして、utb8mb4_col が utf8mb4 であることを確認してみます。

mysql> INSERT INTO tbl_mix_char (ujis_col, utf8mb4_col) VALUES('', '🍣たべたい');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tbl_mix_char (ujis_col, utf8mb4_col) VALUES('🍣たべたい' '');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'ujis_col' at row 1

テーブルのデフォルトキャラクタセットを ujis にしてみます。

すると、SHOW CRETE TABLEの結果のujis_col varchar(255) CHARACTER SET ujis NOT NULL が ujis_col varchar(255) NOT NULL に変わりました。 テーブルのデフォルトキャラクタセットとキャラクタセットが一致するカラムは CHARACTER SET 〜 の表示が省略される作りのようです。

mysql> ALTER TABLE tbl_mix_char CHARACTER SET ujis;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tbl_mix_char \G
*************************** 1. row ***************************
       Table: tbl_mix_char
Create Table: CREATE TABLE `tbl_mix_char` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ujis_col` varchar(255) NOT NULL,
  `utf8mb4_col` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=ujis
1 row in set (0.00 sec)

データを変換する

データベースとテーブルのデフォルトキャラクタセットを変更しても、実際に保存されているデータは変換されません。 これらはCREATE時に明示的にキャラクタセットを指定しなかったときに採用されるキャラクタセットを示しているだけですから。

実際にデータのキャラクタセットを変換方法は２つ。

1. カラムごとに変更する

utf8 → utf8mb4 に ALTER TABLE xxx MODIFY 〜 で変換します。この方法では、テーブルのデフォルトキャラクタセットは変更されません（別途、必要に応じて変更）。

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text,
  `c1` varchar(255) NOT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 MODIFY t1 TEXT CHARACTER SET utf8mb4, MODIFY c1 VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text CHARACTER SET utf8mb4,
  `c1` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2. テーブル全体を変換する

カラム単位での変換は、カラム数が多いと面倒です。ALTER TABLE xxx CONVERT TO CHARACTER SET 〜でテーブルをまるごと変換できます。

TEXT型の扱いに注意が必要です。 １文字あたりのバイト数が増える場合、TEXT型が自動的にMEDIUMTEXT型に変更されます。

データ型が VARCHAR か、またはいずれかの TEXT 型であるカラムに対して、CONVERT TO CHARACTER SET は、新しいカラムが確実に元のカラムと同じ数の文字を格納できる十分な長さになるように、必要に応じてデータ型を変更します。 たとえば、TEXT カラムには、そのカラム内の値のバイト長 (最大 65,535) を格納するための 2 バイト長があります。 latin1 TEXT カラムの場合は、各文字に 1 バイトが必要なため、このカラムには最大 65,535 文字を格納できます。このカラムが utf8 に変換された場合は、各文字に最大 3 バイトが必要になる可能性があるため、可能性のある最大の長さは 3 × 65,535 = 196,605 バイトになります。 その長さは TEXT カラムのバイト長には収まらないため、MySQL はそのデータ型を、バイト長に 196,605 の値を記録できる最小の文字列型である MEDIUMTEXT に変換します。同様に、VARCHAR カラムは MEDIUMTEXT に変換される可能性があります。

https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` text,
  `c1` varchar(100) DEFAULT NULL,
<snip>
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `t1` mediumtext,
  `c1` varchar(100) DEFAULT NULL,
<snip>
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

変換できない文字があるときは？

変換できない文字があればエラーになります。

# utf8mb4 から ujis に変換する、絵文字はujisにないのでエラーに
mysql> ALTER TABLE tbl_mix_char MODIFY ujis_col VARCHAR(255) CHARACTER SET ujis NOT NULL;
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3\xE3\x81...' for column 'ujis_col' at row 2

デフォルトキャラクタセットの変更も忘れずに

特別な事情がない限りは、データベースとテーブルのデフォルトキャラクタセットと、カラムのキャラクタセットはすべて揃える運用でしょう。 データを変更したあとは、忘れずに、データベースとテーブルのデフォルトキャラクタセットも変換が必要です。

mysql> ALTER TABLE t1 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER  DATABASE db1 CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL Group Replication vs Percona XtraDB Cluster 〜DDLのKILL 編〜

MySQL のマルチマスターのソリューションである、PXC と Group Replication の比較記事が続きます・・・

PXC では DDL は KILL できない

Percona XtraDB Cluster (Galera) では DDL実行中は、すべてのノードで更新が止まると、以前のエントリーで書きました。

mita2db.hateblo.jp

うっかりDDLを流してしまって、更新が止まってしまい、DDLをKILLしようとしても、PXCでは、このように、KILLさせてくれません。

mysql> KILL 18;
ERROR 1095 (HY000): You are not owner of thread 18

PXCでは更新を一旦ブロックし、全てのノードで同時にDDLを実行します（この仕組みを TOI (Total Order Isolation) と呼びます）。ここで、あるノードだけDDLがKILLされてしまうと、ノード間でテーブル定義が一致しない状態になってしまいます。不一致を回避するため「一度流したDDLは途中ではキャンセルできない」仕様になっていると推測しています。

これは、MySQLにはない制約です。PXCは、（Group Replicationの Primary/Secondaryのような）ノードに役割を持たせない設計となっている一方で、その代償として、MySQLにはない制約があります。

Group Replication は大丈夫

Group Replication (Single Primary 運用) では、普通にKILLできます。 Group Replication は、 Single Primary 運用を前提とすることで、特に追加の制約が少なく運用できるように工夫されているように感じました。

> ALTER TABLE sbtest2  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
^C-- query aborted
Query OK, 0 rows affected (2.6369 sec)

※ Single Primary を off にすると、どうなるかは検証してない

MySQL Group Replication vs Percona XtraDB Cluster 〜フルデータコピー中のDDLブロック 編〜

フルデータコピーが行われるタイミング

PXC や MySQL Group Replication では、新規にノードを追加したタイミングや、障害発生後、既存ノードからデータをコピーし、復旧する機能があります。

データコピー中のDDLの挙動の比較

フルデータコピーには非常に時間がかかることが予想されます。 DDLはあまり日常的に流れるものではないと思いますが、万が一、コピー中にDDLが走ってしまったときにどうなるか確認してみます。

検証内容：

1. CREATE / DROP PROCEDURE を流しつつ、フルデータコピーを走らせる
2. 並行して、DDLを流しているDBへ更新、参照を行う

PXC は キツイ挙動をする

Percona XtraDB Cluster では、このフルデータコピープロセスは State Snapshot Transfers (SST) と呼ばれています。 なんだかカッコイイ名前がついていますが、実態は、xtrabackup によるリカバリです。

コピー元となるノードで、xtrabackup による物理バックアップを取得し、コピー先に転送します。

PXC では、フルデータコピー中（xtrabackup 実行中）はDDLがブロックされます。DDL実行が待機させられます。

DDLが単品で待機させられるだけなら、まぁ、良いのですが、、、「DDL実行中は、クラスタ全体の更新がブロックされる」という仕様に基づき、すべてのINSERTやUPDATEが巻き込まれて止まります😱

mita2db.hateblo.jp

xtrabackup でバックアップ中にDDLが実行されるとバックアップが失敗するため、DDLをブロックしているのでしょう。SSTを行うスクリプトで、xtrabackup の引数に明示的に --block-ddl が指定されていることが、確認できます。

/usr/bin/wsrep_sst_xtrabackup-v2

INNOBACKUP="${pxb_bin_path} --defaults-file=${WSREP_SST_OPT_CONF} \
 --defaults-group=mysqld${WSREP_SST_OPT_CONF_SUFFIX} $disver $iopts \
 \$INNOEXTRA \$keyringbackupopt --lock-ddl --backup --galera-info \
 --binlog-info=ON \$encrypt_backup_options --stream=\$sfmt \
 --xtrabackup-plugin-dir="$pxb_plugin_dir" \
 --target-dir=\$itmpdir 2>\${DATA}/innobackup.backup.log"

定期的にバッチでDDLを流しているようなケース（パーティション追加など）では、バッチとフルデータコピーが重ならないように運用しなければなりません。つらい。。。

Group Replication は好ましい挙動

Group Replication のノードリカバリは Clone プラグインを使って実装されています。 データファイルを物理的にコピー先のノードへコピーします。

Group Replication でも、フルデータコピー中はDDLの実行が待機させられています。addInstance でノードを追加したタイミングで、CREATE PROCEDURE がストップしていることがわかります。

Clone プラグインのマニュアルにもドナー（コピー元）で、DDLが ブロックされると明記されています。

On the donor, the clone user requires the BACKUP_ADMIN privilege for accessing and transferring data from the donor, and for blocking DDL during the cloning operation. https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-remote.html

ただし、Group Replication は PXCとは異なり、DDL実行中に更新クエリをブロックすることはありません。したがって、フルデータコピー中であっても、INSERTやUPDATEが影響を受けることはありません。 Groiup Replication のほうが好ましい挙動です。

余談ですが、Clone プラグインには、データコピーに利用するネットワーク帯域幅に制限をかける clone_max_network_bandwidth パラメータがあります。 このパラメータを活用することで、意図的にフルデータコピーに時間のかかる状況を簡単に作り出せました。検証がしやすかったです。

まとめ

• Percona XtraDB Cluster
  • コピー中、DDLがブロックされ、それに巻き込まれて、更新クエリもブロックされる
• Group Replication
  • コピー中、DDLだけがブロックされる

MySQL Group Replication vs Percona XtraDB Cluster 〜DDLのロック編〜

Gelera Cluster / Percona XtraDB Cluster

Galera Cluster for MySQL は フィンランドの会社が開発している、MySQL の fork （派生製品）の１つです。wsrep API と呼ばれる独自のレプリケーション機構を使った、マルチマスター型のHA構成を組むことができます。Galera Cluster は 2007年にリリースされ、MySQL の高可用性構成を実現するための手段として一定の支持を得てきました。

galeracluster.com

wsrep API ベースのマルチマスター機能を MySQL の forkである、Percona Server に実装したものが、Percona XtraDB Cluster。 同じく、MariaDB に実装したものが、MariaDB Galera Cluster になります。

MySQL Group Replication

2016 年に登場した、Vanilla MySQL (forkでなくオリジナル）のマルチマスター機能です。実装は全く異なりますが、目的としているところは、Galera Cluster と同じです。

MySQL 5.7 の終盤でいきなり登場したのですが、機能が充実し、十分使えるようになったのは、MySQL 8.0 になってからです。 MySQL 8.0の新機能と捉えても良いでしょう。

MySQL Group Replication は、HA機能のみで、アプリケーションにデータベースの構成情報を伝える機能は持ちません（例えば、PRIMARYサーバが変わったときに、アプリケーションの接続先を切り替えるなど）。 アプリケーションをデータベースの構成変更に追従させるためのツールである MySQL Router と組み合わせて、InnoDB Cluster いうソリューション名が付けられています。

相互レプリケーションと何が違うのか

Galera Cluster や Group Replication を使わなくても、従来、相互にマスターサーバ同士をレプリケーションさせることで、「マルチマスター」構成とすることが出来ました。しかし、単純な相互レプリケーションでは、スプリットブレインのリスクを排除できません。運が悪いと障害時にデータロストやデータ不整合が発生してしまう可能性があります。

（Paxos などの）分散合意アルゴリズムを利用して、堅牢なマルチマスター構成を実現したものが、Galera Cluster や Group Replication になります。

PXC vs MySQL Group Replication

Group Replication は Galera Cluster や Percona XtraDB Cluster (PXC) と同じようなHAを実現するための機能ですが、実装は全く異なります。 PXCと Group Replicaion でどのような違いがあるのか比較していきたいと思います。

バージョンは、PXC 8.0 と MySQL 8.0 で比較します。

※ 比較相手に Galera Cluster for MySQL や MariaDB Galera Cluster でなく、PXCを選択したのは、単なる自分の趣味です。

DDLのロックの挙動

以下の３点に注目して比較してみます。いずれの検証も、書き込み先のノード（サーバ）は１台に固定して行います（Single Primary 運用）

1. ALTER 中に対象テーブルに対して書き込みができるか
2. ALTER 中に対象テーブル以外に対して書き込みができるか
3. ALTER がレプリケーション遅延（ノード間のデータ同期の遅延）を引き起こすかどうか

Percona XtraDB Cluster 8.0

PXC 8.0 では、例えオンラインDDLであっても、DDL実行中はすべての更新がブロックされます。 ALTER TABLE の対象外のテーブルであってもです。

すべてのノードで同じタイミングで、DDLの実行が開始され、ほぼ同時に終わります。

これはこれで、シンプルでわかりやすいですし、予期しないトラブルを避けるのに有効な挙動だと思います。

MySQL 8.0 - Group Replication

MySQL Group Replication では、ALTER TABLE中もテーブルに対して書き込みを行えます（オンラインDDLに限る）。 プライマリノードで実行が終わったら、DDLはセカンダリノードで実行されます。セカンダリノードでのDDL実行中、セカンダリノードへの更新の反映が待機します（レプリケーション遅延が発生）。

つまり、普通のマスター・スレーブ構成と同じです。これまでマスター・スレーブで運用してきた人であれば、違和感なく運用できますね。

まとめ

製品	対象テーブルへの更新	それ以外のテーブルの更新	レプリ遅延
PXC 8.0	できない	できない	N/A
MySQL GR 8.0	できる	できる	発生する

Percona Playback で 本番 MySQLに流れているクエリを試験環境でリプレイする

データベースのバージョンアップの際、アプリケーションの網羅的なテストが可能であれば良いのですが、どうしても難しいケースがあります。 そのような場合、リプレイツールで本番環境に流れているクエリを、試験環境でリプレイ（再現）し、動作確認を取る方法もあります。

リプレイツールを探す

MySQL の クエリ リプレイができるツールを探してみました。

Percona Tookit に pt-log-player というツールが含まれていたのですが、いつのまにか、なくなってました。。。 2013年にリリースされた、percona tookit 2.2 で削除されてしまったようです。

We removed pt-query-advisor, pt-tcp-model, pt-trend, and pt-log-player. Granted, no tool is ever really gone: if you need one of these tools, get it from 2.1. pt-log-player is now superseded by Percona Playback (http://www.percona.com/doc/percona-playback/). pt-query-advisor was removed so that we can focus our efforts on its online counterpart instead: https://tools.percona.com/query-advisor. The other tools were special projects that were not widely used.

https://www.percona.com/doc/percona-toolkit/LATEST/release_notes.html

pt-log-player is now superseded by Percona Playback とあるように Percona Playback が代替のツールとなるようです。 このツールも、最後のコミットが3年前と、メンテされてない雰囲気が漂っていますが・・・試してみます。

github.com

Percona Playback を インストールする

MySQL 8.0 環境でも問題なくビルドできました。セーフ。

$ git clone https://github.com/Percona-Lab/query-playback.git

$ sudo yum -y install tbb tbb-devel cmake boost boost-devel gcc gcc-c++ doxygen mysql-community-devel mysql-community-libs-compat

$ cd query-playback
$ mkdir build_dir
$ cd build_dir
$ cmake -DMYSQL_LIB=/usr/lib64/mysql/libmysqlclient.so -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
$ make
$ make test
$ make install

$ ls /usr/local/bin/percona-playback
/usr/local/bin/percona-playback

動かしてみる

Percona Playback はスロークエリログか一般クエリログをもとにリプレイします。 とりあえず、クエリを１つだけ含んだ、スロークエリログを食わせてみます。

$ sudo cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 8.0.21 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2020-08-30T08:15:17.706789Z
# User@Host: root[root] @ localhost []  Id:    16
# Query_time: 0.002868  Lock_time: 0.000708 Rows_sent: 1  Rows_examined: 559
SET timestamp=1598775317;
SELECT COUNT(*) FROM information_schema.tables;
# Time: 2020-08-30T08:15:18.795306Z
# User@Host: root[root] @ localhost []  Id:    16
# Query_time: 0.000009  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 559
SET timestamp=1598775318;
# administrator command: Quit;

リプレイ先とユーザ、パスワード等をシンプルに指定するだけです。

$ sudo /usr/local/bin/percona-playback --query-log-file /var/lib/mysql/localhost-slow.log --mysql-host 192.168.123.12 --mysql-user appuser --mysql-password Pass@123 --mysql-schema mysql --mysql-max-retries=0

Database Plugin: libmysqlclient
 Running...
 Finished reading log entries
 Start sorting log entries
 Finished sorting log entries
 Finished preprocessing - starting playback...
thread 16 slower query was run in  35623 microseconds instead of 2868
  <--
SELECT COUNT(*) FROM information_schema.tables;  -->
Error Report finished


Detailed Report
----------------
SELECTs  : 1 queries (0 faster, 1 slower)
INSERTs  : 0 queries (0 faster, 0 slower)
UPDATEs  : 0 queries (0 faster, 0 slower)
DELETEs  : 0 queries (0 faster, 0 slower)
REPLACEs : 0 queries (0 faster, 0 slower)
DROPs    : 0 queries (0 faster, 0 slower)


Report
------
Executed 1 queries
Spent 00:00:00.035623 executing queries versus an expected 00:00:00.002868 time.
0 queries were quicker than expected, 1 were slower
A total of 0 queries had errors.
Expected 1 rows, got 1 (a difference of 0)
Number of queries where number of rows differed: 0.

Average of 1.00 queries per connection (1 connections).

SELECTs : 1 queries (0 faster, 1 slower) とリプレイ先で遅くなったクエリの数をレポートしてくれます。 その他、Expected 1 rows, got 1 (a difference of 0) 結果セットの件数比較もあります。

わざとエラーにしてみる

リプレイ先でわざとエラーになるようなクエリを投げて、エラーが検知できることを確認してみます。 リプレイ先では sql_mode に ONLY_FULL_GROUP_BY を設定して、SQLがエラーになるようにしています。

$ sudo /usr/local/bin/percona-playback --query-log-file /var/lib/mysql/localhost-slow.log --mysql-host 192.168.123.12 --mysql-user appuser --mysql-password Pass@123 --mysql-schema mysql --mysql-max-retries=0
Database Plugin: libmysqlclient
 Running...
 Finished reading log entries
 Start sorting log entries
 Finished sorting log entries
 Finished preprocessing - starting playback...
Error during query: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tables.TABLE_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, number of tries 1 of 1
thread 24 slower query was run in  60973 microseconds instead of 13559
  <--
SELECT COUNT(*), table_name FROM information_schema.tables GROUP BY table_schema;  -->
Error query: SELECT COUNT(*), table_name FROM information_schema.tables GROUP BY table_schema;
Connection 24 Rows Sent: 0 != expected 5 for query: SELECT COUNT(*), table_name FROM information_schema.tables GROUP BY table_schema;
Error Report finished


Detailed Report
----------------
SELECTs  : 1 queries (0 faster, 1 slower)
INSERTs  : 0 queries (0 faster, 0 slower)
UPDATEs  : 0 queries (0 faster, 0 slower)
DELETEs  : 0 queries (0 faster, 0 slower)
REPLACEs : 0 queries (0 faster, 0 slower)
DROPs    : 0 queries (0 faster, 0 slower)


Report
------
Executed 1 queries
Spent 00:00:00.060973 executing queries versus an expected 00:00:00.013559 time.
0 queries were quicker than expected, 1 were slower
A total of 1 queries had errors.
Expected 5 rows, got 0 (a difference of 5)
Number of queries where number of rows differed: 1.

Average of 1.00 queries per connection (1 connections).

A total of 1 queries had errors. としっかりエラーが補足できています。 デフォルトでは、10回リトライする設定になっています。エラーがたくさん出て、出力が見にくいので、--mysql-max-retries=0 で無効化したほうが良いです。

実行タイミングも考慮したリプレイ

相互に関係するクエリのロックの挙動やタイミング依存の問題も含めて、精度の高いリプレイをしたいと考えると、クエリの実行タイミングも可能な限り再現できるとベターです。

--query-log-accurate-mode を指定すれば、クエリの実行タイミングも含めて再現してくれそうです。 5秒間隔で INSERT INTO t (d) VALUES(NOW()) を実行し、それがリプレイ先で再現されるか確認してみます。タイミングも含めたリプレイが成功すれば、d の値の差が5秒あるレコードが再現されるはずです。

mysql> select * from test.t;
+----+---------------------+
| pk | d                   |
+----+---------------------+
|  1 | 2020-08-30 08:30:15 |
|  2 | 2020-08-30 08:30:20 |
+----+---------------------+
2 rows in set (0.01 sec)

$ sudo /usr/local/bin/percona-playback --query-log-file /var/lib/mysql/localhost-slow.log --mysql-host 192.168.123.12 --mysql-user appuser --mysql-password Pass@123 --mysql-schema mysql --mysql-max-retries=0 --query-log-accurate-mode
Database Plugin: libmysqlclient
 Running...
WARNING: did not find a timestamp for the first query. Disabling accurate mode.

WARNING: did not find a timestamp for the first query. Disabling accurate mode. とタイムスタンプがログに見つからないと言われてしまいました・・・ぐぬぬぬ。ログにはしっかりタイムスタンプが出力されています。

ソースコードを見ると、MySQL 8.0 のタイムスタンプの形式（# Time: 2020-08-30T08:29:48.563737Z） に対応できていない状態でした。

query_log/query_log.cc

static bool parse_time(boost::string_ref s, QueryLogData::TimePoint& start_time) {
  // Time can look like this
  //   # Time: 090402 9:23:36
  // or like this if microseconds granularity is configured.
  //   # Time: 090402 9:23:36.123456
  long long msecs = 0;
  std::tm td;
  memset(&td, 0, sizeof(td));
  std::string line(s.begin(), s.end());
  int num_read = sscanf(line.c_str(), "# Time: %02d%02d%02d %2d:%02d:%02d.%06lld",
                        &td.tm_year, &td.tm_mon, &td.tm_mday, &td.tm_hour, &td.tm_min, &td.tm_sec, &msecs);

MySQL 8.0のスロークエリログのタイムスタンプ形式にあわせて書き換えます。

  int num_read = sscanf(line.c_str(), "# Time: %04d-%02d-%02dT%2d:%02d:%02d.%06lld",
                        &td.tm_year, &td.tm_mon, &td.tm_mday, &td.tm_hour, &td.tm_min, &td.tm_sec, &msecs);

ソースを微修正すれば動きました。

リプレイ先に作られたレコード）

mysql> select * from test.t;
+----+---------------------+
| pk | d                   |
+----+---------------------+
|  1 | 2020-08-30 08:46:21 |
|  2 | 2020-08-30 08:46:26 |
+----+---------------------+
2 rows in set (0.00 sec)

当たり前ですが、このオプションを使うと、実際の時間と同じぐらいリプレイに時間がかかります。１時間分のスロークエリログを食わせたら、リプレイに１時間かかります。

Percona Playback まとめ

• リプレイ先でのエラーの有無、性能劣化の有無のチェックが出来る
• スロークエリログに記録されている結果セットの件数とリプレイ先での結果セットの件数に差異がないかチェック出来る
• クエリの実行タイミングも再現したリプレイが可能

tombo さんの mysql-query-replayer も試したい

tombo さんが、リプレイツールを作っています。 大規模なリプレイが出来そうです。こちらも、いつか試してみたいと思います。

github.com

MySQL Shell で検証環境を作る

日々、検証のためDBサーバを作っては捨て、作っては捨てる・・を繰り返しています。 そうすると、検証環境を作る手間を省きたくなってきます。

dbdeployer、docker-compose やchef など、いろいろなツールを試してきましたが、結局、白紙の仮装マシン（VM）を立てて、一から構築することが多いです。 それが一番慣れてますからね。。。

今回は、MySQL Shell の サンドボックス機能を試してみました。

MySQL Shell をインストールする

$ yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$ sudo yum install -y mysql-shell

MySQL Shell の Sandbox 関連のファンクション

検証環境（Sandbox）を作るファンクションが用意されています。ファンクション名を見れば、だいたい使い方の予想が付きます。 〜 on localhost という記述があり、（dbdeployer のような）ローカルに複数の mysqld を立ち上げる仕組みであることが想像できます。

 MySQL  127.0.0.1:3307 ssl  JS > dba.help()

NAME
      dba - InnoDB cluster and replicaset management functions.
<snip>

FUNCTIONS
<snip>

      deleteSandboxInstance(port[, options])
            Deletes an existing MySQL Server instance on localhost.

      deploySandboxInstance(port[, options])
            Creates a new MySQL Server instance on localhost.

<snip>

      killSandboxInstance(port[, options])
            Kills a running MySQL Server instance on localhost.

<snip>


      startSandboxInstance(port[, options])
            Starts an existing MySQL Server instance on localhost.

      stopSandboxInstance(port[, options])
            Stops a running MySQL Server instance on localhost.


<snip>

サンドボックスインスタンスを作成する

deploySandboxInstance でサンドボックスインスタンスを作成＋起動します。作成だけでなく、起動まで一緒にやってくれます。 引数にはポート番号を指定します。

 MySQL  localhost:33060+ ssl  JS > dba.deploySandboxInstance(3307)
A new MySQL sandbox instance will be created on this host in
/home/vagrant/mysql-sandboxes/3307

Warning: Sandbox instances are only suitable for deploying and
running on your local machine for testing purposes and are not
accessible from external networks.

Please enter a MySQL root password for the new instance: ********

Deploying new MySQL instance...

Instance localhost:3307 successfully deployed and started.
Use shell.connect('root@localhost:3307') to connect to the instance.

mysql-sandboxes ディレクトリ以下にポート番号でディレクトリが切られ、その中に設定ファイルやデータファイルが配置されていました。

$ ps au | grep mysqld
vagrant   2739  5.1 52.8 1252440 263732 pts/0  Sl   06:07   0:03 /home/vagrant/mysql-sandboxes/3307/bin/mysqld --defaults-file=/home/vagrant/mysql-sandboxes/3307/my.cnf
vagrant   2799  0.0  0.1  12500   688 pts/0    S+   06:08   0:00 grep --color=auto mysqld

my.cnf には最初からレプリケーションに必要な設定があらかじめ追記されています。

$ cat /home/vagrant/mysql-sandboxes/3307/my.cnf
[mysqld]
port = 3307
loose_mysqlx_port = 33070
server_id = 3057363809
socket = mysqld.sock
loose_mysqlx_socket = mysqlx.sock
basedir = /usr
datadir = /home/vagrant/mysql-sandboxes/3307/sandboxdata
loose_log_syslog = OFF
report_port = 3307
report_host = 127.0.0.1
log_error = /home/vagrant/mysql-sandboxes/3307/sandboxdata/error.log
relay_log_info_repository = TABLE
master_info_repository = TABLE
gtid_mode = ON
log_slave_updates = ON
transaction_write_set_extraction = XXHASH64
binlog_format = ROW
log_bin
enforce_gtid_consistency = ON
pid_file = /home/vagrant/mysql-sandboxes/3307/3307.pid
secure_file_priv = /home/vagrant/mysql-sandboxes/3307/mysql-files
[client]
port = 3307
user = root
protocol = TCP

レプリケーションを組んでみる

レプリカ用のインスタンスを追加します。

 MySQL  localhost:33060+ ssl  JS > dba.deploySandboxInstance(3308)
A new MySQL sandbox instance will be created on this host in
/home/vagrant/mysql-sandboxes/3307

createReplicaSet で ReplicaSet を作成します。createReplicaSet の引数には名前を指定します。今回は適当に、myCluster という名前を付けました。

 MySQL  localhost:33060+ ssl  JS > \connect root@localhost:3307
Creating a session to 'root@localhost:3307'
Please provide the password for 'root@localhost:3307': ********
Save password for 'root@localhost:3307'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 14
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.

 MySQL  localhost:3307 ssl  JS > dba.createReplicaSet('myCluster')
A new replicaset with instance '127.0.0.1:3307' will be created.

* Checking MySQL instance at 127.0.0.1:3307

This instance reports its own address as 127.0.0.1:3307
127.0.0.1:3307: Instance configuration is suitable.

* Updating metadata...

ReplicaSet object successfully created for 127.0.0.1:3307.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.

topology を見ると、まだ、127.0.0.1:3307 の１台構成であることがわかります。

 MySQL  localhost:3307 ssl  JS > rs = dba.getReplicaSet()
You are connected to a member of replicaset 'myCluster'.
<ReplicaSet:myCluster>

 MySQL  localhost:3307 ssl  JS > rs.status()
{
    "replicaSet": {
        "name": "myCluster",
        "primary": "127.0.0.1:3307",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "127.0.0.1:3307": {
                "address": "127.0.0.1:3307",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

addInstance で ReplicaSet にレプリカを追加します。

 MySQL  127.0.0.1:3307 ssl  JS > rs.addInstance('root@127.0.0.1:3308');
Adding instance to the replicaset...

* Performing validation checks

This instance reports its own address as 127.0.0.1:3308
127.0.0.1:3308: Instance configuration is suitable.

* Checking async replication topology...

* Checking transaction state of the instance...

NOTE: The target instance '127.0.0.1:3308' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether replication can completely recover its state.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '127.0.0.1:3308' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

このとき、レプリカのリカバリーをClone か Incremental recovery 、どちらの方法で行うか選択する必要があります。今回は、Cloneを選択し、ソース（マスター）をまるっとコピーしました。

WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.


Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C
* Updating topology
Waiting for clone process of the new member to complete. Press ^C to abort the operation.
* Waiting for clone to finish...
NOTE: 127.0.0.1:3308 is being cloned from 127.0.0.1:3307
** Stage DROP DATA: Completed
** Clone Transfer
    FILE COPY  ############################################################  100%  Completed
    PAGE COPY  ############################################################  100%  Completed
    REDO COPY  ############################################################  100%  Completed

NOTE: 127.0.0.1:3308 is shutting down...

* Waiting for server restart... ready
* 127.0.0.1:3308 has restarted, waiting for clone to finish...
* Clone process has finished: 59.62 MB transferred in about 1 second (~59.62 MB/s)

** Configuring 127.0.0.1:3308 to replicate from 127.0.0.1:3307
** Waiting for new instance to synchronize with PRIMARY...

The instance '127.0.0.1:3308' was added to the replicaset and is replicating from 127.0.0.1:3307.

127.0.0.1:3308 がレプリカ（SECCONDARY）として追加されていることが確認できます。

 MySQL  127.0.0.1:3307 ssl  JS >  rs.status()
{
    "replicaSet": {
        "name": "myCluster",
        "primary": "127.0.0.1:3307",
        "status": "AVAILABLE",
        "statusText": "All instances available.",
        "topology": {
            "127.0.0.1:3307": {
                "address": "127.0.0.1:3307",
                "instanceRole": "PRIMARY",
                "mode": "R/W",
                "status": "ONLINE"
            },
            "127.0.0.1:3308": {
                "address": "127.0.0.1:3308",
                "instanceRole": "SECONDARY",
                "mode": "R/O",
                "replication": {
                    "applierStatus": "APPLIED_ALL",
                    "applierThreadState": "Slave has read all relay log; waiting for more updates",
                    "receiverStatus": "ON",
                    "receiverThreadState": "Waiting for master to send event",
                    "replicationLag": null
                },
                "status": "ONLINE"
            }
        },
        "type": "ASYNC"
    }
}

念のため、SHOW SLAVE STATUS を直接みてチェックしてみます。 3307 ポートのMySQLをソース（マスター）として参照していることが確認できました。

$ mysql -uroot -p -S mysql-sandboxes/3308/sandboxdata/mysqld.sock
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: mysql_innodb_rs_637839038
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: localhost-bin.000002
          Read_Master_Log_Pos: 24939
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 2093
        Relay_Master_Log_File: localhost-bin.000002
             Slave_IO_Running: Yes
<snip>
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3057363809
                  Master_UUID: d89032f1-e9bd-11ea-81a5-5254004d77d3
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: d89032f1-e9bd-11ea-81a5-5254004d77d3:37-38
            Executed_Gtid_Set: d89032f1-e9bd-11ea-81a5-5254004d77d3:1-38
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace:
1 row in set (0.01 sec)

所感

MySQL Shell に同梱されているのは良いですね。MySQL Shell を普段使いするようになれば、出番があるかも。

MySQL スロークエリログからやたら遅いクエリだけ取り出す

日常的に、long_query_time をちょっと超えているクエリが流れていて、スロークエリログが多く出力されているDBってあったりしますよね？

そんなログからノイズとなってしまっている、ちょっと遅いクエリは除いて、非常に重症のクエリのログだけに絞って見たいときどうするか。

今まで、grep や awk でゴニョゴニョしてがんばってたんですが、pt-query-digest を使えば簡単にできました・・・

pt-query-digest でフィルタだけする

pt-query-digest はスロークエリログを集計し、「遅いクエリランキング」等のレポートを生成できるツールです。SQLチューニングする時によく使っています。

www.percona.com

pt-query-digest のレポート機能を無効にすることで、単なるログのフィルタツールとしても使えました（知らなかった！）

やたら遅いクエリだけ取り出す

--no-report でレポートを無効化し、出力形式を --output slowlog でスローログ形式にします。--filer に条件を指定します。

# 5秒以上のクエリだけを取り出す
$ pt-query-digest --type slowlog --output slowlog --no-report --filter '$event->{Query_time} > 5 '  localhost-slow.log

出力例）

$ pt-query-digest --type slowlog --output slowlog --no-report --filter '$event->{Query_time} > 5 '  localhost-slow.log
# Time: 2020-08-23T07:26:29
# User@Host: root[root] @ localhost []
# Thread_id: 13
# Query_time: 10.002385  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
SELECT SLEEP(10);
# Time: 2020-08-23T07:30:25
# User@Host: root[root] @ localhost []
# Thread_id: 14
# Query_time: 5.004318  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
SELECT SLEEP(5);
# Time: 2020-08-23T07:44:26
# User@Host: root[root] @ localhost []
# Thread_id: 15
# Query_time: 5.000435  Lock_time: 0.000163  Rows_sent: 1  Rows_examined: 4
SELECT *, SLEEP(5) FROM mysql.user WHERE User = 'root';

--output slowlog-secure とすると、バンド変数を ? に置き換えてマスキングしてくれます。

$ pt-query-digest --type slowlog --output secure-slowlog --no-report --filter ' $event->{Query_time} > 5 ' localhost-slow.log
<snip>
# Time: 2020-08-23T07:44:26
# User@Host: root[root] @ localhost []
# Thread_id: 15
# Query_time: 5.000435  Lock_time: 0.000163  Rows_sent: 1  Rows_examined: 4
select *, sleep(?) from mysql.user where user = ?;

特定のユーザのクエリだけ見る

pt-query-digest は perl で書かれてます。--filer には任意の perl のプログラムを書くことができます。

Userを正規表現でフィルタして、appuser で実行されたクエリだけ取り出す。

# pt-query-digest --type slowlog --output secure-slowlog --no-report --filter ' $event->{user} =~ /appuser/ ' /var/lib/mysql/localhost-slow.log
# Time: 2020-08-23T07:02:53
# User@Host: appuser[appuser] @ localhost []
# Thread_id: 8
# Query_time: 0.000066  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
select @@version_comment limit ?;