ぼくらが8.0にいたるまでのみちのり（未到）

ぼくらが8.0にいたるまでのみちのり（未到） The road leading to MySQL 8.0(Not yet arrived)
2018/11/21 yoku0825 Oracle MySQL Innovation Day 2018 秋

Safe Harbor Statement この資料はyoku0825の独断と偏見によるものであり、所属する組織、所属しない組織またはNULLの意見を一切代表する訳がありません != 問題によりひどく酔っぱらっています 1/108

Congratulations for MySQL 8.0 GA Released!! 2/108

Released mysql80 160> SELECT CURDATE() /* JST */; +------------+ |
CURDATE() | +------------+ | 2018-04-20 | +------------+ 1 row in set (0.00 sec) mysql80 160> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.00 sec) 3/108

Today mysql80 160> SELECT CURDATE() /* JST */; +------------+ |
CURDATE() | +------------+ | 2018-11-21 | +------------+ 1 row in set (0.00 sec) mysql80 160> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.13 | +-----------+ 1 row in set (0.00 sec) 4/108

なんと By the way, ナッツシェル（ What is New in MySQL
8.0 の章）には $ curl -L -s https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshe ll.html | perl -nlE 'if ($_ =~ /(8\.0\.\d+)/) { say $1 }' | sort -n | uniq -c 1 8.0.0 2 8.0.12 6 8.0.13 5 8.0.14 3 8.0.2 3 8.0.3 2 8.0.4 5/108

なんか調べようと思ってナッツシェルを見るたびに新しい項目が増えている斬新なRDBMSです Everytime I looked for nutshell, everytime
new features have been added. 6/108

GA #とはなんだったのか What is GA 7/108

そんな愉快盛りだくさんな MySQL 8.0ですが That is MySQL 8.0
8/108

ちょっとアンケート Survey 9/108

自分の検証用に突っ込んだ人？ Is there a person who has introduced 8.0
into your machine? 10/108

テスト環境に突っ込んだ人？（ウチはｲﾏｺｺ） Is there a person who has introduced
8.0 into Test Environment? (I am) 11/108

手が挙がらなかった人はみんな既に本番に突っ込んだってことでいいですか？ :D People who didn’t raise their hand,
has already introduced 8.0 into Production Environment, isn’t it? :D 12/108

言えない人もいると思いますので I knew there are people who are prohibited
to tell 13/108

(俺以外の)全員がMySQL 8.0を既に本番に導入したということにして進めます I assume all of you have introduced
8.0 into Production Environment. 14/108

思い出してください Remember 15/108

8.0にいたるまでのみちのりを the road leading to MySQL 8.0 16/108

＼こんばんわ／ yoku0825＠GM〇メディア株式会社オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter:
@yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 17/108

みちのりに思いを馳せる前にちょっと復讐復習 At first, 18/108

MySQL 8.0に求めることとは？ What you ask to MySQL 8.0? 19/108

とあるDBAがMySQL 8.0に求めたこと A DBA says 新しい performance_schema お気に入りは variables_info と
events_errors_summary_by_account_by_error, data_locks ‐ SELECT .. FOR UPDATE SKIP LOCKED CLONE {LOCAL\|REMOTE} DATA DIRECTORY .. JSON_TABLE 関数 Window関数群 Instant ADD COLUMN（最近気付いた） 20/108

開発エンジニアがMySQL 8.0に求めたこと？ But application developers say SELECT .. FOR UPDATE
SKIP LOCKED （とはいえWEBの世界で排他ロック…？）再帰でないCTE（がすごく速いこと） JSON型（それ、5.7からあるねん…） SQLの互換性接続ライブラリーを入れ替えても入れ替えなくても動く 21/108

ミスマッチ Mismatched opinions （再帰でない）CTEは便利だけど、それだけでびっくりするくらい性能が上がる訳ではない書きやすくなったぶん、JOINの回数を減らせることに気付きやすくなったり、狙うインデックスがわかりやすくなったりってメリットはある ‐ CREATE
TEMPORARY TABLE .. ENGINE= MYISAM AS SELECT .. は相変わらず最強 ‐ 再帰CTEとWindow関数はいいものだけど、そもそも今までなかったのでSQLが書けない・使おうと思われない 22/108

ミスマッチ Mismatched opinions 一部、SQLの互換性が損なわれた（これ結構珍しい） RANK とか ROLE のキーワードのことですよ… ‐ GROUP
BY の暗黙のソートとか ‐ 接続ライブラリー…ｳｯ caching_sha2_password はライブラリーがない以外にもSSLとか有効化しないといけなかったり…？ ‐ 23/108

今までMySQLのバージョンアップでやられたこと？ What were issues in previous versionups? 4.0とそれ以前 ⇒ 4.1とそれ以降
timestamp型の変更、varchar型が文字コードをサポートしたことによる地獄 5.5 ⇒ 5.6 datetime, time, timestamp型の内部の変更、InnoDBのパラメーター変遷、ICPが積極的に選ばれて憤死、良かれと思って設定した explicit_defaults_for_timestamp が略、 mysql_old_password 認証プラグインがデフォルトでOFFられててあぼん 5.6 ⇒ 5.7 Condition Fanout Filterが変に選ばれて略、sql_modeの暗黙のデフォルトを変え忘れて死 24/108

sql_modeを弱い方に倒せば「クエリーが通らない！」で苦労した記憶はなかった There’s no problem if setting sql_mode
empty :D 25/108

(4.0を除く) Expect of 4.0 26/108

が 27/108

8.0へのレプリケーションがガンガン止まる But versionup to 8.0 with replication, stop sql_thread sometimes..
あっ、ハイ、8.0でなくなるやつですよね NO_AUTO_CREATE_USER で5.7上でもエラーにできればいいのに…！ ‐ Last_Errno: 1064 Last_Error: Error 'You have an error in your SQL syntax; check th e manual that corresponds to your MySQL server version for the ri ght syntax to use near 'IDENTIFIED WITH 'mysql_native_password' A S '*4266488C892EA7950486FEC0A1CFFC1BD95' at line 1' on query. Def ault database: ''. Query: 'GRANT USAGE ON *.* TO 'yoku0825'@'%' I DENTIFIED WITH 'mysql_native_password' AS '*4266488C892EA7950486F EC0A1CFFC1BD9543F7B'' 28/108

あっ予約語だ！ binlog_format= ROW にする？（これまでは MIXED ） ‐ 片っ端からオブジェクト名をバッククォートして回る？生SQLでなければこんな心配要らない予感 ‐ Last_SQL_Errno: 1064 Last_SQL_Error: Error 'You have an error in your SQL syntax; chec k the manual that corresponds to your MySQL server version for th e right syntax to use near 'rank) VALUES (4)' at line 1' on query . Default database: 'd1'. Query: 'INSERT INTO t1 (rank) VALUES (4 )' 29/108

止まるかな？と思って打ったら止まった。今は反省している。 FLUSH ステートメントの一部はバイナリーログに記録されるのです ‐ Last_SQL_Errno: 1064 Last_SQL_Error: Error 'You have an error in your SQL syntax; chec k the manual that corresponds to your MySQL server version for th e right syntax to use near 'QUERY CACHE' at line 1' on query. Def ault database: ''. Query: 'FLUSH QUERY CACHE' 30/108

orz 31/108

感想 My impression is あんまり長期間マスター5.7, スレーブ8.0の運用はしたくない気分 8.0マスターならちゃんとエラーになるやつばっかり ‐ binlog_format=
ROW に変えちゃったからもうわからないけど、予約語以外のDMLはフツーに流れていたいい加減（他の8.0関係ないところでも） ROW に変えよう… ‐ 32/108

感想 My impression is MySQL Shellの checkForServerUpgrade はやっておいて損はない MySQL
5.6とそれ以前に対しても実行できる非公式なPerl5実装があるらしいよ yoku0825/p5-mysql-upgrade-checker: Translation of MySQL Shell Upgrade Checker into Perl5 ‐ 33/108

ちょっと痛い目を見たのでちゃんとドキュメントを読んでみる Let’s read document.. 34/108

なくなった機能のチェック Checking removed features. 35/108

なくなった機能 Removed features. Using GRANT to create users ✓ Using
GRANT to modify account properties other than privilege assignments ✓ IDENTIFIED BY PASSWORD ‘hash_string’ syntax ✓ The PASSWORD() function. ✓ The old_passwords system variable. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 36/108

なくなった機能 Removed features. The query cache was removed. ✓ These
deprecated compatibility SQL modes have been removed ✓ The deprecated ASC or DESC qualifiers for GROUP BY clauses have been removed. ✓ The ENCODE() and DECODE() functions. The ENCRYPT() function. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 37/108

なくなった機能 Removed features. the deprecated functions are removed to leave
only the corresponding ST_ and MBR functions ✓ The mysql_install_db program has been removed ✓ The generic partitioning handler was removed ✓ Support for placing table partitions in shared InnoDB tablespaces was removed ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 38/108

なくなった機能 Removed features. The deprecated INFORMATION_SCHEMA INNODB_LOCKS and INNODB_LOCK_WAITS tables
have been removed. ✓ Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13 ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 39/108

なくなった機能 Removed features. Using GRANT to create users ✓ Using
GRANT to modify account properties other than privilege assignments ✓ IDENTIFIED BY PASSWORD ‘hash_string’ syntax ✓ The PASSWORD() function. ✓ The old_passwords system variable. ✓ MySQL :: MySQL 8.0 Reference Manual :: 1.4 What Is New in MySQL 8.0 40/108

GRANT で直接ユーザーを作れなくなった No longer create user by GRANT statement 5.7からワーニングは出てたじゃろ？
GRANT でパスワードを変えたり MAX_USER_CONNECTIONS を変えたりもできなくなった ‐ IDENTIFIED BY PASSWORD は IDENTIFIED WITH .. AS .. に変わる ‐ SET PASSWORD 構文はもうイコールの後ろに PASSWORD() 関数を噛ませられない ‐ mysql> CREATE USER yoku0825@'%' IDENTIFIED BY 'plain_password'; mysql> GRANT DRINK beer.* TO yoku0825@'%'; mysql> ALTER USER yoku0825@'%' IDENTIFIED WITH mysql_native_passw ord AS '*hashed_password'; mysql> SET PASSWORD FOR yoku0825@'%' = 'plain_password'; 41/108

互換SQLモード Compatibility SQL mode まあ知ってる人の方が少ないとは思う最近MariaDBが sql_mode = oracle を使ってOracle互換の構文を～
とかやってた気がする ‐ mysql57 5> SET sql_mode = 'oracle'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql57 5> SELECT @@sql_mode; +------------------------------------------------------------------------------------------------ ----------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------ ----------------------+ | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTION S,NO_AUTO_CREATE_USER | +------------------------------------------------------------------------------------------------ ----------------------+ 1 row in set (0.00 sec) mysql80 15> SET sql_mode = 'oracle'; ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'oracle' 44/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause こんなデータがあるじゃろ？
mysql80 165> SELECT * FROM t1 LIMIT 10; +-----+-----+ | n | m | +-----+-----+ | 2 | 18 | | 4 | 7 | | 6 | 85 | | 1 | 69 | | 4 | 66 | | 7 | 52 | | 3 | 25 | | 2 | 19 | | 1 | 61 | | 5 | 80 | +-----+-----+ 10 rows in set (0.01 sec) 46/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 今まではこうだったんじゃ
mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | | 10 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 47/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause これからはこうなるんじゃ
mysql80 165> SELECT n, COUNT(m) FROM t1 GROUP BY n; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 2 | 100 | | 4 | 100 | | 6 | 100 | | 1 | 100 | | 7 | 100 | | 3 | 100 | | 5 | 100 | | 10 | 100 | | 9 | 100 | | 8 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 48/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 並べ替えのためにこんな構文ができたんじゃ
mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n ASC; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 1 | 100 | | 2 | 100 | | 3 | 100 | | 4 | 100 | | 5 | 100 | | 6 | 100 | | 7 | 100 | | 8 | 100 | | 9 | 100 | | 10 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 49/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 並べ替えのためにこんな構文ができたんじゃ
mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n DESC; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 10 | 100 | | 9 | 100 | | 8 | 100 | | 7 | 100 | | 6 | 100 | | 5 | 100 | | 4 | 100 | | 3 | 100 | | 2 | 100 | | 1 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 50/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause 並べ替えをさせない（ソートのオーバーヘッドをかっ飛ば
す）ために ORDER BY NULL なんてテクニックもあったのじゃ mysql57 11> SELECT n, COUNT(m) FROM t1 GROUP BY n ORDER BY NULL; +-----+----------+ | n | COUNT(m) | +-----+----------+ | 2 | 100 | | 4 | 100 | | 6 | 100 | | 1 | 100 | | 7 | 100 | | 3 | 100 | | 5 | 100 | | 10 | 100 | | 9 | 100 | | 8 | 100 | +-----+----------+ 10 rows in set (0.00 sec) 51/108

GROUP BY による暗黙のソート Implicit sorting by GROUP BY clause SQL的には自然な方になったんだけれども
レポート系で1ファイルにベロっと吐き出してるのとかは「順番がああああ」ってなりそうだけど、スプレッドシート（ｳｯ）とかに詰めてるなら問題ないかしらん…と思っています単に「MySQLでSQLを覚えたのでそういうもんだと思って生きてきた」人が多いんじゃないかなあという気がする仕様だとまでは思ってなかったけど、そうでなくなると違和感をおぼえる、みたいな？ 52/108

mysql_install_dbからmysqld –initializeへこれも5.7からdeprecated warningが出ていたはずこれは5.7時代から結構人口に膾炙していたのではなかろうか ‐ 日々の覚書: MySQL 5.7.6でデータベースの初期化が変わる mysql_install_dbからmysqld
–initialize ‐ $ mysqld --no-defaults --initialize .. 2018-11-07T09:32:45.375558Z 4 [Note] [MY-010454] [Server] A tempo rary password is generated for root@localhost: lP?G,yjh+9eX 2018-11-07T09:32:47.029929Z 0 [System] [MY-013170] [Server] /usr/ mysql/8.0.13/bin/mysqld (mysqld 8.0.13) initializing of server ha s completed 54/108

SET 以外でユーザー変数をセットすること Setting user variables in statements other than SET
計算のタイミングが保障されないからサイレントに事故ることがあったり、ステートメントベースでレプリケーションアンセーフだったりするからだと思うけど。 mysql80 15> SELECT @a := @a + 1; +--------------+ | @a := @a + 1 | +--------------+ | 2 | +--------------+ 1 row in set, 1 warning (0.00 sec) mysql80 15> SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------------------------------------------- --------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------- --------------------------------------+ | Warning | 1287 | Setting user variables within expressions is deprecated and will be removed in a future release. Please set vari ables in separate statements instead. | +---------+------+----------------------------------------------------------------------------------------------------------------- --------------------------------------+ 1 row in set (0.00 sec) 58/108

INTO は良いっぽいまあ INTO は結果セットが複数行だとエラーになるしな ‐ mysql80 15> SELECT @a + 1 INTO @a; Query OK, 1 row affected (0.00 sec) mysql80 15> SELECT @a; +-----+ | @a | +-----+ | 3 | +-----+ 1 row in set (0.00 sec) 59/108

というわけで↓こんなのは今後エラーになる…と便利だったんだけどなぁ(´･ω･｀) 日々の覚書: MySQL 5.7.6のPerformance SchemaでInnoDBのALTER TABLE進捗どうですか ‐ って言ってもアプリケーションコードで使うことはそう無いか ‐ 同じ行の中で計算してるやつはアプリケーションコードに、複数行にまたがってなんかしてるやつはCTEかWindow関数に SELECT thread_id, event_name, sql_text, @progress:= (work_completed / work_estimated) * 100 AS progress, @elapsed:= (timer_current - timer_start) / power(10, 12) AS elapsed, @elapsed * (100 / @progress) - @elapsed AS estimated FROM .. 60/108

それ以外 61/108

話題になった認証プラグイン Changed default_authentication_plugin デフォルトが mysql_native_password から caching_sha2_password に変更 MySQL 5.7.22とそれ以前のクライアントはこのプラグインを持って
いない 5.7.23とそれ以降はクライアントはこのプラグインを持っている（サーバーは持っていない） MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.23 (2018-07-27, General Availability) ‐ $ mysql56 -S /usr/mysql/8.0.13/data/mysql.sock ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/mysql/5.6.42/lib/plugin/caching_sha2_pass word.so: cannot open shared object file: No such file or director y 62/108

caching_sha2_password 「最初の1回」はSSL接続、ソケット接続、RSA鍵でパスワード交換などの「セキュアなコネクション」で接続しないといけない FLUSH PRIVILEGES や mysqld の再起動の都度「最初の1回」がやってくるため、これに切り替えたらもうAPサーバーは
常に「セキュアなコネクション」で接続するようにしないといけない caching_sha2_password vs. mysql_native_password はそんなにコネクション速度劣化はないけど、 SSL vs. 非SSL の間には大きな溝が横たわっている 63/108

caching_sha2_password MYSQL_OPT_GET_SERVER_PUBLIC_KEY は便利だけど実装に波があるコネクターのバージョンが先行するまでは default_authentication_plugin= mysql_native_password かな… 64/108

MySQL 8.0 client libraries language module Using Connecor/ C? caching_sha2_pa
ssword? MYSQL_OPT_GET _SERVER_PUBLI C_KEY? Perl DBD::mysql Yes Yes Yes PHP mysqlnd No Yes No Ruby mysql2 Yes Yes Yes Python PyMySQL No Yes Yes Python MySQLdb Yes Yes No 公式系は割愛した 65/108

utf8mb4 我々日本人にはほとんど影響はないはず吊るしのまま character_set_server= latin1 とかそんなにないじゃろ？ ‐ 3バイトの utf8mb3(utf8)
のままだったのはままありそう 3バイトの utf8mb3 はワーニング出るようになったのでちゃんと拾ってあげてね ‐ $ perror ER_DEPRECATED_UTF8_ALIAS MySQL error code MY-003719 (ER_DEPRECATED_UTF8_ALIAS): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8 MB4 in order to be unambiguous. 66/108

ちなみにutf8mb3とutf8mb4を混ぜると Attention for mixing utf8mb3 and utf8mb4 そのカラムを使ってJOINするとインデックスが使えなくなったりするので慎重に結果は変わらない。だが、実行計画は大きく違う。
これはデータが少ないうちは気づきにくく、データ量が増えてから「あーーー！！！」となるパターン。テーブルやカラムのCollationは揃えよう - 41から始めました 67/108

kamipoの「ハハ=パパ」問題 kamipo’s father = mother issue こっちはデフォルトのままだと影響アリアリ mysql> SELECT *
FROM families; +-----+--------+--------------+ | id | name | relationship | +-----+--------+--------------+ | 1 | ユイ | 本人 | | 2 | キリト | パパ | | 3 | アスナ | ハハ | +-----+--------+--------------+ 68/108

kamipoの「ハハ=パパ」問題 kamipo’s father = mother issue SELECT * FROM families
WHERE relationship = 'パパ'; +-----+--------+--------------+ | id | name | relationship | +-----+--------+--------------+ | 2 | キリト | パパ | | 3 | アスナ | ハハ | +-----+--------+--------------+ 69/108

多くは語るまい That had been already discussed enough collation mysql =
MySQL ハハ != パパびょういん ! = びよういん != MySQL = ＭｙＳＱＬ = != ≠ utf8mb4 _genera l_ci o o o x x o utf8mb4 _0900_ ai_ci o x x o o x utf8mb4 _ja_090 0_as_cs x o o o o o utf8mb4 _bin x o o o x o 70/108

新顔のいこーるいこーるのっといこーる問題 EQUAL-symbol equals NOT_EQUAL-symbol mysql80 32> SELECT '=' = '≠';
+-------------+ | '=' = '≠' | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) MySQL Bugs: #92899: Incorrect uniqueness determination with equality symbols もちろん “Not a bug” ハハ = パパに比べればまだ笑える（のか？） ‐ 71/108

もういちど Once again collation mysql = MySQL ハハ != パパ
びょういん ! = びよういん != MySQL = ＭｙＳＱＬ = != ≠ utf8mb4 _genera l_ci o o o x x o utf8mb4 _0900_ ai_ci o x x o o x utf8mb4 _ja_090 0_as_cs x o o o o o utf8mb4 _bin x o o o x o 72/108

MySQL 8.0を起動すると開かれる謎のポート Unknown listening port..? # ss -ltpn | grep
mysql LISTEN 0 128 :::3306 :::* users:(("mysqld",pid=10363,fd=30)) LISTEN 0 70 :::33060 :::* users:(("mysqld",pid=10363,fd=33)) # mysqld --help --verbose | grep 33060 mysqlx-port 3306 0 73/108

MySQL 8.0ではポート番号が11倍にアップ！（？） MySQL 8.0 increases port- number x11
times! 74/108

MySQLXがデフォルトでLISTENするようになってる mysqlx listens 33060 by default 1つのサーバーに複数のMySQL 8.0を起動しようとするなら OFFにするかポートを変えておかないとバッティングしちゃう
最近ソケットも作るようになった… ‐ ちなみにバッティングしてもエラー吐くだけで起動する 2018-11-15T10:09:35.131783Z 0 [System] [MY-010931] [Server] /usr/ mysql/8.0.13/bin/mysqld: ready for connections. Version: '8.0.13' socket: '/usr/mysql/8.0.13/data/mysql.sock' port: 64080 Sourc e distribution. 2018-11-15T10:09:36.372022Z 0 [ERROR] [MY-011300] [Server] Plugin mysqlx reported: 'Setup of bind-address: '*' port: 33060 failed, `bind()` failed with error: Address already in use (98). Do you already have another mysqld server running with Mysqlx ?' 2018-11-15T10:09:36.372089Z 0 [System] [MY-011323] [Server] X Plu gin ready for connections. Socket: '/tmp/mysqlx.sock' 75/108

LOAD DATA LOCAL INFILE これ結構ググって日々の覚書にたどり着く人が多いようなんですけどみんな意外と使ってるんですね mysql80 125> LOAD DATA
LOCAL INFILE '/tmp/aaa' INTO TABLE t1; ERROR 1148 (42000): The used command is not allowed with this MyS QL version 76/108

LOAD DATA LOCAL INFILE LODA DATA LOCAL INFILE を実行するには2つの条件が必要で、
LOAD DATA LOCAL INFILE を実行するコネクションに CLIENT_LOCAL_FILES ケーパビリティー（オプションだと思って）が設定されていること ‐ サーバー側で opt_local_infile が設定されていること ‐ 日々の覚書: MySQL 8.0でLOAD DATA LOCAL INFILEが “ERROR 1148 (42000): The used command is not allowed with this MySQL version” で失敗する時 77/108

innotop 78/108

innotop run innotop with MySQL 8.0.3 ✓ press any keys
for switching to InnoDB modes,such as D for InnoDB Deadlocks ✓ innotop will crash with error Use of uninitialized value $text in pattern match (m//) at /usr/bin/ innotop line 620. ✓ Switching to InnoDB modes causes to crash with MySQL 8.0.3 · Issue #162 · innotop/innotop 79/108

innotop 前にMySQL 5.7対応ができてないことに気付いてブログ書いた時、結構「使ってる」「困る」みたいな反応をTwitter で見かけた日々の覚書: innotopが最近息してないなーと思ったんだ ‐ 大体にして俺が困る 5.7対応だって俺が困るからパッチ書いたわけだし
‐ 80/108

innotop Innotop for MySQL 8.0 – lefred’s blog: tribulations of
a MySQL Evangelist Support MySQL 8.0 by yoku0825 · Pull Request #167 · innotop/innotop あー、epelへのパッケージングリクエスト出さないとな… 81/108

innotop 82/108

innotop 83/108

mikasafabri略 SHOW GRANTS の結果が変わった件で8.0で動かなかったんだけど直した日々の覚書: MySQL 8.0.4の SHOW GRANTS
の結果が想像したのとちょっと違う ‐ gmo-media/mikasafabric: mikasafabric for MySQL is fork product of MySQL Fabric. ‐ 84/108

ここまで乗り越えて Beyond the problems 85/108

取り敢えず無事動いてます Stability running at now. 86/108

取り敢えず無事に（？）動いてます Stability..? schema1(slave) schema2(slave) schema1(slave) schema2(slave) schema3(master) schema1(master) schema2(master) 56_slave
80_master 56_master 87/108

だいぶやっちまった感… Oops.. 88/108

やっちまったけど（既に）これは便利だったもの Oops but these are useful features I already used.
ROLE JOIN_ORDERヒント INSTANT Add column 89/108

今まで Before 某密林の弾性電算雲とか使ってればセキュリティーグループで制限かけられるんだけど… mysql> CREATE USER apuser@apserver1; mysql> GRANT
ALL ON ap_db.* TO apuser@apserver1; mysql> CREATE USER apuser@apserver2; mysql> GRANT ALL ON ap_db.* TO apuser@apserver2; mysql> CREATE USER apuser@apserver3; mysql> GRANT ALL ON ap_db.* TO apuser@apserver3; .. 90/108

今まで Before 「すいません、新機能はスキーマを分けて開発することになったので、既存のアカウントが新スキーマにもアクセスできるように設定してください」 ( ﾟдﾟ) えっ mysql> GRANT
ALL ON new_db.* TO apuser@apserver1; mysql> GRANT ALL ON new_db.* TO apuser@apserver2; mysql> GRANT ALL ON new_db.* TO apuser@apserver3; .. 91/108

今まで Before 「すいません、新機能はサーバーもわけることになりました。既存のアカウントは今まで通りap_dbだけ、新サーバー用のアカウントはap_dbの読み取りとnew_dbの読み書き権限を」 ( ﾟдﾟ) えっえっ mysql>
REVOKE ALL ON new_db.* FROM apuser@apserver1; mysql> REVOKE ALL ON new_db.* FROM apuser@apserver2; mysql> REVOKE ALL ON new_db.* FROM apuser@apserver3; .. mysql> CREATE USER apuser@apserver11; mysql> GRANT ALL ON new_db.* TO apuser@apserver11; mysql> GRANT SELECT ON ap_db.* TO apuser@apserver11; .. 92/108

今まで Before 「すいませんやっぱりスキーマの名前を」 ( д ) ﾟﾟﾌｧｰ 93/108

ROLEがあると After introducing role mysql> CREATE ROLE ap_rw; mysql> GRANT
ALL ON ap_db.* TO ap_rw; mysql> CREATE USER apuser@apserver1 DEFAULT ROLE ap_rw; mysql> CREATE USER apuser@apserver2 DEFAULT ROLE ap_rw; mysql> CREATE USER apuser@apserver3 DEFAULT ROLE ap_rw; .. 94/108

ROLEがあると After introducing role mysql> GRANT ALL ON new_db.* TO
ap_rw; mysql> REVOKE ALL ON new_db.* FROM ap_rw; mysql> CREATE ROLE ap_ro; mysql> GRANT SELECT ON ap_db.* TO ap_ro; mysql> CREATE ROLE new_rw; mysql> GRANT ALL ON new_db.* TO new_rw; mysql> GRANT ap_ro TO new_rw; mysql> CREATE USER apuser@apserver11 DEFAULT ROLE new_rw; .. 95/108

ROLE IPアドレス単位でアカウントを作成している現場にはとても便利だと思う MySQLアカウントのIPアドレスの制限をせずに、セキュリティーグループとかで制限する環境には特に嬉しくもないかも ‐ ちなみに内部的にユーザーとロールはあんまり区別されてないっぽいので色々試してると面白い GRANT root@localhost
TO yoku0825 とかやっておくと、 SET ROLE root@localhost とかできる ‐ ついさっきレポートした MySQL Bugs: #93263: DROP ROLE username should be rejected ‐ 96/108

JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_ORDER (CountryLanguage, Country) */
Name, Language, Population, Percent age -> FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode -> WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5; +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+---- -------------------------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+---- -------------------------------+------+----------+----------------+ | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NUL L | 984 | 100.00 | Using filesort | | 1 | SIMPLE | Country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | wor ld.CountryLanguage.CountryCode | 1 | 14.29 | Using where | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+---- -------------------------------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) 97/108

JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_ORDER (Country, CountryLanguage) */
Name, Language, Population, Percent age -> FROM CountryLanguage LEFT JOIN Country ON Country.Code= CountryLanguage.CountryCode -> WHERE Country.continent = 'Asia' ORDER BY Percentage LIMIT 5; +-----+-------------+-----------------+------------+------+---------------------+------+---------+------+-- ----+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | r ows | filtered | Extra | +-----+-------------+-----------------+------------+------+---------------------+------+---------+------+-- ----+----------+----------------------------------------------------+ | 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 39 | 14.29 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NULL | 9 84 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +-----+-------------+-----------------+------------+------+---------------------+------+---------+------+-- ----+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) 98/108

FIXED_JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_FIXED_ORDER() */ Name, Language,
Population, Percentage -> FROM Country, CountryLanguage -> WHERE Country.Code= CountryLanguage.CountryCode AND Country.continent = 'Asia' ORDER BY Percenta ge LIMIT 5; +-----+-------------+-----------------+------------+------+---------------------+------+---------+----- -+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+-------------+-----------------+------------+------+---------------------+------+---------+----- -+------+----------+----------------------------------------------------+ | 1 | SIMPLE | Country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 14.29 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NULL | 984 | 100.00 | Using where; Using join buffer (Block Nested Loop) | +-----+-------------+-----------------+------------+------+---------------------+------+---------+----- -+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) 99/108

FIXED_JOIN_ORDERヒント mysql80 18> EXPLAIN SELECT /*+ JOIN_FIXED_ORDER() */ Name, Language,
Population, Percentage -> FROM CountryLanguage, Country -> WHERE Country.Code= CountryLanguage.CountryCode AND Country.continent = 'Asia' ORDER BY Percenta ge LIMIT 5; +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+ -----------------------------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+ -----------------------------------+------+----------+----------------+ | 1 | SIMPLE | CountryLanguage | NULL | ALL | PRIMARY,CountryCode | NULL | NULL | NULL | 984 | 100.00 | Using filesort | | 1 | SIMPLE | Country | NULL | eq_ref | PRIMARY | PRIMARY | 3 | world.CountryLanguage.CountryCode | 1 | 14.29 | Using where | +-----+-------------+-----------------+------------+--------+---------------------+---------+---------+ -----------------------------------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) 100/108

JOIN_ORDERヒント句ついに `OUTER JOIN` でも `ORDER BY狙いのキー` を狙いやすく…！ WHERE
句で外部表のカラム（↑の場合は Country.continent ）を評価してて、実質 INNER JOIN 相当になっている時だけだった…_|￣|◦ 実質 INNER JOIN になってない場合はナチュラルに無視される ‐ USE INDEX と違って存在しない名前を指定しても無視されるだけでエラーにはならない ‐ 101/108

INSTANT ADD COLUMN 100万行, 100MBくらいのテーブルに対して mysql80 20> ALTER TABLE t1
ADD c1 int, ALGORITHM= INSTANT; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql80 20> ALTER TABLE t1 ADD c2 int, ALGORITHM= INPLACE; Query OK, 0 rows affected (3.43 sec) Records: 0 Duplicates: 0 Warnings: 0 102/108

INSTANT ADD COLUMN ALGORITHM = INSTANT ----system---- ----total-cpu-usage---- -dsk/total- ---system--
------mem ory-usage----- --io/total- | time | usr sys idl wai hiq siq | read writ | int csw | used buff cach free | read writ | | 20-11 16:44:56 | 2 1 97 1 0 0 | 0 6409k | 877 996 | 692M 0 172M 127M | 0 222 | | 20-11 16:44:57 | 3 1 96 0 0 0 | 128k 394k | 1545 2509 | 692M 0 172M 127M | 8.00 63.0 | | 20-11 16:44:58 | 2 0 98 0 0 0 | 0 6410k | 901 982 | 692M 0 172M 127M | 0 225 | | 20-11 16:44:59 | 2 1 97 1 0 0 | 3260k 972k | 908 927 | 692M 0 175M 124M | 54.0 41.0 | | 20-11 16:45:00 | 2 0 98 0 0 0 | 0 0 | 641 772 | 692M 0 175M 124M | 0 0 | 17338 buffer_pages_written 2018-11-20 16:44:56 17347 buffer_pages_written 2018-11-20 16:44:57 17547 buffer_pages_written 2018-11-20 16:44:58 17577 buffer_pages_written 2018-11-20 16:44:59 17577 buffer_pages_written 2018-11-20 16:45:00 103/108

INSTANT ADD COLUMN ALGORITHM = INPLACE ----system---- ----total-cpu-usage---- -dsk/total- ---system--
------memory-usage----- --io/total- | time | usr sys idl wai hiq siq | read writ | int csw | used buff ca ch free | read writ | | 20-11 16:45:00 | 2 0 98 0 0 0 | 0 0 | 641 772 | 692M 0 17 5M 124M | 0 0 | | 20-11 16:45:01 | 35 5 49 10 0 0 | 14M 78M | 14k 34k | 692M 0 17 5M 124M | 897 2150 | | 20-11 16:45:02 | 13 5 49 32 0 0 | 37M 51M | 9577 16k | 701M 0 20 5M 85.1M | 1213 3318 | | 20-11 16:45:03 | 19 8 49 23 0 0 | 14M 51M | 12k 18k | 693M 0 20 6M 91.7M | 245 5287 | | 20-11 16:45:04 | 3 2 85 10 0 0 | 6028k 31M | 6985 13k | 693M 0 21 1M 87.7M | 142 3098 | | 20-11 16:45:05 | 2 0 98 0 0 0 | 0 419k | 681 817 | 693M 0 21 1M 87.7M | 0 20.0 | 17577 buffer_pages_written 2018-11-20 16:45:00 18047 buffer_pages_written 2018-11-20 16:45:01 19139 buffer_pages_written 2018-11-20 16:45:02 21147 buffer_pages_written 2018-11-20 16:45:03 21591 buffer_pages_written 2018-11-20 16:45:04 21604 buffer_pages_written 2018-11-20 16:45:05 104/108

INSTANT ADD COLUMN どういう黒魔術使ってるんだってくらい速いちなみに「メタデータだけゴニョって、実際に値がUPDATEされるまでデータはメタデータの中にしかない」状態にする黒魔術 ‐ バッファプールも全然触らなくてすごい楽しくて笑えてくるレベル ‐
ENUM型の定義変更も ALGORITHM = INSTANT でできるらしいですよ！！！１ 105/108

（実務に）試せてないけど使いたい Maybe useful, but I didn’t use yet 式インデックス式デフォルト
リソースグループ JSON_TABLE ヒストグラム 106/108

おしらせ Information まだまだお席に余裕がございます Tomas, Mike and Luis? Kajiyama-san and Yamasaki-san
have a duty. ‐ MySQL Casual Advent Calendar 2018 \- Qiita 107/108

Any Questions and/or Suggestions? 108/108

ぼくらが8.0にいたるまでのみちのり（未到）

ぼくらが8.0にいたるまでのみちのり（未到）

More Decks by yoku0825

Other Decks in Programming

Featured

Transcript