Myリノベ 8.0.21に参加
先日「MySQL Release note でわいわい言う勉強会 8.0.21」に参加してまいりました。
今回も最新のMySQL8.0.21のリリースノートをみんなで見ながら、思いつくままに語る会で、その中で僕は相変わらずROM専ですが参加しておりました。
流れとしては坂井さんがオープニングトーク、次にまつさんがJSON_VALUE()のLTを語ってくださり、その他の機能についてはリリースノートを見ながら…という感じでした。
いずれyoutubeあたりに動画が上がると思いますので、全体が知りたい方はtwitterやslackでチェックしてるとよいかと。
8.0.21からはCREATE TABLE ... SELECT気軽に使えるぜ！
さて、その会の中でyoku0825さんがつぶやいたCREATE TABLE ... SELECTの以下の話、マイナーかもしれないけど結構驚きの変更だと思うんですよね。
On storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is now logged as one transaction in the binary log when row-based replication is in use. Previously, it was logged as two transactions, one to create the table, and the other to insert data. With this change, CREATE TABLE ... SELECT statements are now safe for row-based replication and permitted for use with GTID-based replication. For more information, see Atomic Data Definition Statement Support.
見たことないですか？
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
GTIDを使う人は頭に叩き込まれてるはずなんです。
- 行ベースで
- GTID使ってる場合
- CREATE TABLE ... SELECTはできないって
だって、ドキュメントにもあるんだから。
みんな5.6以降、GTIDを使う人はCREATE TABLE ... SELECT使わないぞ！と心に誓ってたはずなんです。
でも、
MySQL8.0.21からはフツーにレプリカに反映されるようになったんです。
試してみた
作業概要
環境構築
僕はdbdeployer使ってます。とてもかんたんに環境作れますよ。
dbdeployerの詳しい説明については適当にググってもらうとかすればいいんですが、ざっくり書くと
- MySQLのtarファイルゲットしてきて
- dbdeployerインストールして
- dbdeployer unpack mysql-xxxxxxxx-8.0.xx-linux-x86_64-minimal.tar.xz みたいな感じで解凍して
- 以下のようなコマンドでレプリケーション環境を作れます。
dbdeployer deploy replication 8.0.xx \
--custom-role-name=R_POWERFUL \
--custom-role-privileges='ALL PRIVILEGES' \
--custom-role-target='*.*' \
--custom-role-extra='WITH GRANT OPTION' \
--default-role=R_POWERFUL \
--bind-address=0.0.0.0 \
--remote-access='%' \
--gtid \
--db-user=admin \
--db-password=xxxxx
こんな感じで2つのソースレプリカ形式のDBのペアを用意しました。
$ dbdeployer sandboxes rsandbox_8_0_20 : master-slave 8.0.20 [21021 31021 21022 31022 21023 31023 ] rsandbox_8_0_21 : master-slave 8.0.21 [21122 31122 21123 31123 21124 31124 ]
検証開始
そして、２つのバージョンで比較するんですが、やることはかんたん。
- テーブル（t1）を作って
- テーブルに適当にデータ入れて
- 一応テーブルにデータ入ったこと確認して
- そのテーブルからデータコピーをCREATE TABLE ...SELECT構文でやって
- レプリカ側のほうにあるか確認
というだけの作業。
GTIDの確認
こんな感じでGTIDモードはいずれもONです
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 -e "show variables like 'gtid_mode'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | ON | +---------------+-------+ $ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 -e "show variables like 'gtid_mode'" mysql: [Warning] Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | ON | +---------------+-------+
MySQL8.0.20
ソース（マスター）側
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21021 test
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.20-commercial MySQL Enterprise Server - Commercial
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.
(root@127.0.0.1) [test]> 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)
(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)
(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)
(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.01 sec)
(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
(root@127.0.0.1) [test]> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
ERROR 1786が出ますね。
当然ソース・レプリカいずれのインスタンスにもt2テーブルは作られていません。
これを8.0.21でもやります。
MySQL8.0.21
ソース（マスター）側
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21122 test
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 15
Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial
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.
(root@127.0.0.1) [test]> CREATE TABLE `t1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)
(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.02 sec)
(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)
(root@127.0.0.1) [test]> insert into t1 values(NULL);
Query OK, 1 row affected (0.00 sec)
(root@127.0.0.1) [test]> select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.01 sec)
(root@127.0.0.1) [test]> CREATE TABLE t2 select * from t1;
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
(root@127.0.0.1) [test]> select * from t2;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
エラーが出ない！
んじゃ、レプリカ側はどうなってるかな？
レプリカ（スレーブ）側
$ mysql -uroot -pxxxxx -h127.0.0.1 -P21123 test mysql: [Warning] Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial 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. (root@127.0.0.1) [test]> select * from t1; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) (root@127.0.0.1) [test]> select * from t2; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
できてるぅ！ヒャッホー！
binlogの比較
GITDがONだと比較できないので、OFFにしてmysqlbinlogで覗いてみる。
MySQL8.0.20
# at 9418 #200814 18:50:04 server id 100 end_log_pos 9543 CRC32 0xccceb3e3 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1597398604/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; CREATE TABLE `t2` ( `id` int NOT NULL DEFAULT '0' ) /*!*/; # at 9543 #200814 18:50:04 server id 100 end_log_pos 9622 CRC32 0xf3e5f383 Anonymous_GTID last_committed=38 sequence_number=39 rbr_only=yes original_committed_timestamp=1597398604099299 immediate_commit_timestamp=1597398604099299 transaction_length=283 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST) # immediate_commit_timestamp=1597398604099299 (2020-08-14 18:50:04.099299 JST) /*!80001 SET @@session.original_commit_timestamp=1597398604099299*//*!*/; /*!80014 SET @@session.original_server_version=80020*//*!*/; /*!80014 SET @@session.immediate_server_version=80020*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 9622 #200814 18:50:04 server id 100 end_log_pos 9697 CRC32 0x796b6528 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1597398604/*!*/; BEGIN /*!*/; # at 9697 #200814 18:50:04 server id 100 end_log_pos 9745 CRC32 0x1fde1b11 Table_map: `test`.`t2` mapped to number 112 # at 9745 #200814 18:50:04 server id 100 end_log_pos 9795 CRC32 0xc4bf3987 Write_rows: table id 112 flags: STMT_END_F BINLOG ' TF42XxNkAAAAMAAAABEmAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQARG94f TF42Xx5kAAAAMgAAAEMmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAIc5v8Q= '/*!*/; # at 9795 #200814 18:50:04 server id 100 end_log_pos 9826 CRC32 0xbc8b4b0a Xid = 71 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
MySQL8.0.21
# at 9496 #200814 18:47:28 server id 100 end_log_pos 9639 CRC32 0xe24b1dc8 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1597398448/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; CREATE TABLE `t2` ( `id` int NOT NULL DEFAULT '0' ) START TRANSACTION /*!*/; # at 9639 #200814 18:47:28 server id 100 end_log_pos 9687 CRC32 0x4972ee4a Table_map: `test`.`t2` mapped to number 112 # at 9687 #200814 18:47:28 server id 100 end_log_pos 9737 CRC32 0x56fa2558 Write_rows: table id 112 flags: STMT_END_F BINLOG ' sF02XxNkAAAAMAAAANclAAAAAHAAAAAAAAEABHRlc3QAAnQyAAEDAAABAQBK7nJJ sF02Xx5kAAAAMgAAAAkmAAAAAHAAAAAAAAEAAgAB/wABAAAAAAIAAAAAAwAAAFgl+lY= '/*!*/; # at 9737 #200814 18:47:28 server id 100 end_log_pos 9768 CRC32 0xa09930d6 Xid = 71 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
明らかに8.0.21のほうが短いね。
読み解いたわけじゃないけど、雰囲気は感じる。
所感
リリースノートではサラッと一行で書かれてるけど、結構な変更だと思いません？
GTIDが無効な場合でもクラッシュ時とかにレプリカにはテーブルはあるけどデータが無いとか無くなるわけだし、
GTIDが有効な場合でもエラーにならずにCREATE TABLE ... SELECTが使えるようになったわけです。
リリースノートをみんなで眺めることで、そういう知見が多くの人の目に止まったんです。
素敵な会ですね！
この話以外にも気になる機能について色々語られてました。
非公式の何かがちょろっと配布されたりして、意外と録画されていないところも楽しい勉強会ｗ
みなさんも次回は参加してみては？
（今日はHunter×Hunter アニメ版のようなタイトルの付け方してみました）
