MySQL 5.7にやられないためにおぼえておいてほしいこと

MySQL 5.7にやられないためにおぼ
えておいてほしいこと
安⼼してください。履いてますよ。
2015/10/03
yoku0825
phpcon 2015

免責事項
MySQLの話をします
PHPの話は⼀切出てきません
個⼈の⾒解であり、所属する組織または所属しない組織の意
⾒を代表するわけがありません
アップデートはありますが、過去のスライドと内容カブって
ます。
今から備えるMySQL最新バージョン5.7-
MySQL 5.7の罠があなたを狙っている-
光のMySQL 5.7-
資料は公開しますので、となりのセッションに移動する
なら今です
-
1/172

MySQL 5.7にどんなイメージを持っていますか︖
光︖
闇︖
まっさら︖
2/172

なお、MySQL 5.7の闇の正体はこんな感じです
https://twitter.com/tmtms/status/636104524164296708
3/172

なお、MySQL 5.7の闇の正体はこんな感じです
MySQL を好きな⼈が、MySQLのちょっと変なところ
を、少しばかり強調しておもしろおかしく伝えている⾯
はあるので、その変な部分だけが⼀⼈歩きして多くの⼈
に理解されてしまうことを危惧はしていますが
http://sakaik.hateblo.jp/entry/20150823/yapc2015
4/172

お世辞抜きでMySQL
5.7楽しいですよ :)
お世辞抜きで地雷も多いけど
5/172

ところで皆様
お気付きでし
ょうか
6/172

このスライドなん
と表紙を抜いて
171枚あります
7/172

資料はslideshareで公開
します
ってか既にしてます http://
www.slideshare.net/yoku0825/
mysql-57-53449734
9/172

MySQL 5.7とは
2年半ぶりのメジャーバージョンアップ
PHPさんは5.6の次を7にしましたが、MySQLは5.6 =>
5.7 => 次は5.8っぽいです
-
新しい機能を使わないぶんには、 “まあまあ良いMySQL”
バギーってほどバギーではない( バグの流量を⾒る限
り、今のところは )
-
罠オプションがいっぱい
5.5やそれ以前の情報をアテにして本番に突っ込むと痛い
目を⾒る
-
それでもなお、アップグレードしたいバージョン
新しい機能がてんこもり-
10/172

＼こんにちは／
yoku0825＠とある企業のDBA
オラクれない-
ポスグれない-
マイエスキューエる-
家に帰ると
妻の夫-
せがれの⽗-
ムスメの⽗-
Twitter: @yoku0825
Blog: ⽇々の覚書
MyNA ML: ⽇本MySQLユーザ会
11/172

MySQLの系譜
2008/11 MySQL 5.1
2010/12 MySQL 5.5
2013/02 MySQL 5.6
2015/xx MySQL 5.7
2015年8⽉に5.7.8-rc2(Realease Candidate, リリース
候補版)
-
5.6のGA時点で「Oracleとしては18〜24か⽉で次のメジ
ャーバージョンをリリースする予定」と⾔っていたの
で、ズレこんでいるのは確か
-
12/172

本編に⼊る前に、過去のスライドを⾒てくれた⽅へのお知
らせです
show_compatibility_56= OFF問題
performance_schema.{session|global}_{status|
variables}はSEELCT権限なくてもSELECTできるようにな
りました。
-
performance_schema= OFFの時には
show_compatibility_56= OFFでもinformation̲schema
を⾒に⾏くようになりました。
-
13/172

updated at 2016/01/13
MySQL 5.7.11でdefault̲password̲lifetimeの暗黙のデフ
ォルトは0になりました。それ以降のバージョンであれば
360⽇におびえる必要はありません。
⽇々の覚書: MySQL 5.7.11で
default̲password̲lifetimeのデフォルトが0になるらし
い︕
-
14/172

最低限これだけは知ってほしい5.7
16桁ハッシュのパスワードの廃⽌
default̲password̲lifetime
sql̲modeのデフォルト値変更
log̲error̲verbosity vs. log̲warnings
テンポラリーテーブルがInnoDBに
15/172

⼀応知っておいてほしい5.7
mysql.user.passwordカラムの廃⽌
認証周りの構⽂の変更
secure̲file̲priv
log̲timestamps
mysql̲install̲db
mysqld –initialize
InnoDBバッファプールの暖気がデフォルトでONに
binlog̲formatのデフォルト変更
rpmパッケージではvalidate̲passwordプラグインがデフォ
ルトで有効
innodb̲default̲row̲formatの注意
16/172

これを知っているとちょっと得する5.7
sysスキーマ
GTIDのオンライン有効化がサポート
MySQLネイティブの全⽂検索が⽇本語対応
InnoDB UNDO logのシュリンク
サーバーサイドステートメントタイムアウト
generated columnで関数インデックス
SSL関連いろいろ
JSONデータ型
マルチスレッドスレーブの機能向上
17/172

知っておいても損はない5.7
innodb̲buffer̲pool̲sizeのオンライン変更がサポート
sync̲binlogのデフォルト変更
マルチソースレプリケーション
ダイナミックレプリケーションフィルター
InnoDBの新しい圧縮
1テーブルに複数トリガー設定可能
mysql p ump
log-syslog
オフラインモード
GET̲LOCK関数で複数のロックを取れる
Query Rewrite Plugin
18/172

参考URL
MySQL :: MySQL 5.7 Reference Manual :: 1.4 What Is
New in MySQL 5.7
MySQL :: MySQL 5.7 Release Notes
Complete list of new features in MySQL 5.7
中の⼈が作った「新機能完全リスト」-
MySQL 5.7の新機能完全リスト | Yakst
↑をがんばって⽇本語に訳したヤーツ-
昨⽇アップデートした-
⽇々の覚書: 5.7
19/172

20/172

old̲passwords, skip̲secure̲authというオプションに⼼
当たりはありませんか︖
16桁ハッシュのパスワードでもログインできるようにす
るオプション
-
MySQL 4.1(10年前)に非推奨
MySQL 5.6でもデフォルトではログインできないなど風当
たりが強くなり(16桁ハッシュのユーザーが存在する状態で
アップグレードした場合のみ16桁ハッシュのユーザーが存
在)
MySQL 5.7からは全く使えなくなります
21/172

MySQL 5.6へのアップグレード
ワーニングは出たけど、アップグレードはできた。
ユーザー情報としては残ってるけど、実際にログインするに
はskip̲secure̲authを駆使しないといけない。
$ /usr/mysql/5.6.23/bin/mysql_upgrade
..
Warning 1642 Pre-4.1 password hash found. It is deprecated and will be re
moved in a future release. Please upgrade it to a new format.
..
mysql56> SELECT user, host, password FROM user WHERE user <> 'root';
+--------------+------+-------------------------------------------+
| user | host | password |
+--------------+------+-------------------------------------------+
| new_password | % | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| old_password | % | 378b243e220ca493 |
+--------------+------+-------------------------------------------+
23/172

MySQL 5.7へのアップグレード
ワーニングなのは相変わらずだけど、ちゃんと移⾏されてな
い。
$ /usr/mysql/5.7.7/bin/mysql_upgrade -S /usr/mysql/5.7.7/data/mysql.sock -uroot
..
mysql_upgrade: [Warning] 1642: Pre-4.1 password hash found. It is deprecated an
d will be removed in a future release. Please upgrade it to a new format.
..
mysql57> SELECT user, host, authentication_string FROM user WHERE user <> 'root
';
+--------------+------+-------------------------------------------+
| user | host | authentication_string |
+--------------+------+-------------------------------------------+
| new_password | % | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
| old_password | % | NULL |
+--------------+------+-------------------------------------------+
$ mysql57 -uold_password -ptest
ERROR 1524 (HY000): Plugin 'mysql_old_password' is not loaded
24/172

暗黙のデフォルトで360に設定されており、
password̲last̲changedから360⽇以上経過するとそのア
カウントでログインできなくなる。
吊るしのまま使うとMySQL 5.7へのバージョンアップから
360⽇後に突然アプリケーションからつなげなくなるよ。
MySQL 5.7.11から先はデフォルトが0になっているのでこ
の罠はない、それ以前の場合は注意。
25/172

mysql57> SELECT user, host, password_expired, password_last_changed, pas
sword_lifetime, account_locked FROM mysql.user WHERE user= 'yoku0825'G
*************************** 1. row ***************************
user: yoku0825
host: %
password_expired: N
password_last_changed: 1999-07-31 00:00:00
password_lifetime: NULL
account_locked: N
1 row in set (0.00 sec)
mysql57> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)
$ mysql57 -uyoku0825 -p -e "SELECT current_user()"
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must chang
e it using a client that supports expired passwords.
26/172

回避⽅法
my.cnfの[mysqld]セクションに
default_password_lifetime= 0
SET GLOBALでオンライン変更も可能。-
0を指定することで、default̲password̲lifetimeを利⽤し
なくなる。
全くEXPIREされなくなるのが嫌なら、⼤きな値にしても
良い。
-
0より⼤きな値を指定する場合、単位は”⽇”-
27/172

回避⽅法
mysql57> SET GLOBAL default_password_lifetime= 0;
$ vim /etc/my.cnf
[mysqld]
default_password_lifetime= 0
28/172

5.5まで ““(指定なし)
5.6 “NO̲ENGINE̲SUBSTITUTION”(存在しないストレー
ジエンジンを指定してCREATE TABLEするとエラーになる)
場合によっては/usr/my.cnfの罠
で”STRICT̲TRANS̲TABLES,NO̲ENGINE̲SUBSTITU
TION”
-
29/172

5.7のデフォルトのsql̲mode
ONLY̲FULL̲GROUP̲BY
STRICT̲TRANS̲TABLES
NO̲ZERO̲IN̲DATE
NO̲ZERO̲DATE
ERROR̲FOR̲DIVISION̲BY̲ZERO
NO̲AUTO̲CREATE̲USER
NO̲ENGINE̲SUBSTITUTION
sql̲modeの変更は5.6でもエミュレートできるので、デフ
ォルトのまま⾏くなら5.6でも確認しておける
30/172

圧倒的で
はないか
31/172

何故MySQLは今までこんなクエリーを許していたのかよく
わからない感じのものが(valとnumが⼀意に紐づくかどうか
がDBMSには判断できないはず)
mysql56> SELECT val, num, COUNT(*) AS c FROM t1 GROUP BY val ORDE
R BY c DESC LIMIT 5;
+--------------------------------------+-----+---+
| val | num | c |
+--------------------------------------+-----+---+
| 人類はストラトス | 16 | 8 |
| 僕は友達が巨人 | 333 | 7 |
| アウトブレイクストラトス | 46 | 7 |
| さんをつけろよマスター | 39 | 7 |
| 電波女と衰退しました | 140 | 7 |
+--------------------------------------+-----+---+
32/172

ちゃんとエラーになる。
mysql56> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'ONLY_FULL_GROU
P_BY');
mysql56> SELECT val, num, COUNT(*) AS c FROM t1 GROUP BY val ORDE
R BY c DESC LIMIT 5;
ERROR 1055 (42000): 'd1.t1.num' isn't in GROUP BY
33/172

今までと同じ、「何が返ってくるかわからなくてもいいよ」
を表現するためのANY̲VALUE関数が追加された。
mysql57> SELECT val, ANY_VALUE(num), COUNT(*) AS c FROM t1 GROUP BY val
ORDER BY c DESC LIMIT 5;
+--------------------------------------+----------------+---+
| val | ANY_VALUE(num) | c |
+--------------------------------------+----------------+---+
| 人類はストラトス | 16 | 8 |
| 僕は友達が巨人 | 333 | 7 |
| アウトブレイクストラトス | 46 | 7 |
| さんをつけろよマスター | 39 | 7 |
| 電波女と衰退しました | 140 | 7 |
+--------------------------------------+----------------+---+
34/172

⼀部のワーニングをワーニング（警告だけど成功）ではなく
エラーにする。
⼀般的には「勝⼿に桁を切り詰める」「勝⼿に⽂字列型を整
数型にキャストする」がエラーになるやつ。
35/172

mysql56> INSERT INTO t1 VALUES (1, REPEAT('a', 33));
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql56> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 1 |
+---------+------+------------------------------------------+
);
mysql56> INSERT INTO t1 VALUES (1, REPEAT('a', 33));
ERROR 1406 (22001): Data too long for column 'val' at row 1
36/172

NO̲ZERO̲IN̲DATE
sql_mode= NO_ZERO_DATEとは別物。
DATE, DATETIME型の「⽉」「⽇」に0を⼊れようとする
と、ワーニングを発して ʻ0000-00-00 00:00:00ʼに切り
詰める。
37/172

NO̲ZERO̲IN̲DATE
mysql56> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'NO_ZERO_IN_DATE');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql56> INSERT INTO t1 VALUES ('2015-00-31');
+---------+------+---------------------------------------------+
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'dt' at row 1 |
+---------+------+---------------------------------------------+
mysql56> SELECT * FROM t1;
+---------------------+
| dt |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
38/172

NO̲ZERO̲IN̲DATE + strict̲mode
STRICT̲TRANS̲TABLESまたはSTRICT̲ALL̲TABLESと
⼀緒に使うためのもの。
mysql56> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'STRICT_TRANS_T
ABLES');
mysql56> INSERT INTO t1 VALUES ('2015-00-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-00-31' for co
lumn 'dt' at row 1
39/172

NO̲ZERO̲DATE
DATE, DATETIME型にʼ0000-00-00ʼを⼊れようとするとワ
ーニングにしてくれるもの。
NO̲ZERO̲IN̲DATEと⼀緒で直接エラーではなくワーニン
グなので、strict̲modeと⼀緒に使う。
40/172

未指定時は0で除算するとNULLになる
mysql56> INSERT INTO t2 VALUES (2/0);
Query OK, 1 row affected (0.00 sec)
+------+
| num |
+------+
| NULL |
+------+
41/172

指定時はワーニングになってNULLが⼊る。
mysql56> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'ERROR_FOR_DIVISION_BY_ZERO');
+---------+------+---------------+
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
+------+
| num |
+------+
| NULL |
| NULL |
+------+
42/172

ERROR̲FOR̲DIVISION̲BY̲ZERO + strict̲mode
strict̲modeと合わせて初めてエラーになる。
mysql56> SELECT @@sql_mode;
+------------------------------------------------+
| @@sql_mode |
+------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO |
+------------------------------------------------+
ERROR 1365 (22012): Division by 0
43/172

NO̲AUTO̲CREATE̲USER
パスワードなしのユーザーをCREATE USERせずに直接GRANTが
ワーニングになるのはコイツのせい。
mysql56> GRANT replication slave ON *.* TO u1;
mysql56> SET sql_mode= CONCAT_WS(',', @@sql_mode, 'NO_AUTO_CREATE_USER'
);
ERROR 1133 (42000): Can't find any matching row in the user table
mysql56> CREATE USER u2;
44/172

sql̲modeが空でも多少の違いがあるっぽい
mysql56> CREATE TABLE t1 (num int default null, PRIMARY KEY(num)
);
mysql57> CREATE TABLE t1 (num int default null, PRIMARY KEY(num)
);
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL;
if you need NULL in a key, use UNIQUE instead
MySQL 5.7.3 m13 does not allow DEFAULT NULL for
primary key ・ Issue #13203 ・ rails/rails
45/172

今までのlog̲warningsはワーニングも出⼒するイメージ
5.6までの動作はこんな
sql̲print̲warning()の外側で判定されてるんだぜコイ
ツ。。
-
value notes some warnings other warnings errors
log-
warnings =
0
YES No Yes Yes
log-warnings
>= 1
YES Yes Yes Yes
46/172

5.7のlog̲error̲verbosityはいわゆる普通のログレベルの制
御。
下⼿にlog̲warningsと⼲渉するため、意図しない値が設
定されることがある。
log_warnings + 1の値が勝⼿にlog_error_verbosityに
セットされる。
log_warnings= 1が記述してあると、
log_error_verbosity= 2(Noteを出⼒しない)になって
しまう
log_warningsとlog_error_verbosityが混在していた場
合、通常のオプションと同じく後勝ちで設定される。
-
47/172

value notes warnings errors
log-warnings = 0
log-error-
verbosity = 1
No No Yes
log-warnings = 1
log-error-
verbosity = 2
No Yes Yes
log-warnings >=
2
log-error-
verbosity >= 3
Yes Yes Yes
MySQL Bugs: #74963: changes in 5.7.5 logging
verbosity seem surprising and unexpected
48/172

CREATE TEMPORARY TABLE時のデフォルトは
default̲tmp̲storage̲engine変数で制御(MySQL 5.6〜)
暗黙のテンポラリーテーブル(Using temporary)は
internal̲tmp̲disk̲storage̲engine 変数の新設, 暗黙のデ
フォルトはInnoDB
テンポラリーテーブルとして利⽤する場合、REDOログや
排他ロックは必要ない(他のスレッドからそのテーブルは
⾒えない, クラッシュ時にはクリアされる)
その辺の処理をかっ⾶ばす専⽤のibtmp1ファイルを
作成する
暗黙のデフォルトは innodb_temp_data_file_path=
ibtmp1:12M:autoextend
-
49/172

ibdata1と同じく、データとインデックス, UNDOログが書
き込まれる
mysqldの再起動時に毎回再作成される, mysqldが再起動さ
れるまでは再作成されない
⼀度容量を確保してしまうと、mysqldを再起動するまで
ibtmp1は⼤きくなったまま
MyISAMなテンポラリーテーブルはDiskがあふれた場合
にクエリーがエラーになってテンポラリーテーブル領域
がクリアされた
-
50/172

MyISAMを使⽤した暗黙のテンポラリーテーブルがあふれた
場合
mysql57> SELECT * FROM (SELECT * FROM t1 WHERE val LIKE 'abc%') A
S t1, (SELECT * FROM t1 WHERE val LIKE 'def%') AS t2 ORDER BY t1.
num;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_2974_
0.MYI'; try to repair it
$ tail error.log
..
2015-04-30T14:59:45.558360+09:00 87 [ERROR] /usr/mysql/5.7.7/bin/
mysqld: Incorrect key file for table '/tmp/#sql_2974_0.MYI'; try
to repair it
2015-04-30T14:59:45.573411+09:00 87 [ERROR] Got an error from unk
nown thread, /home/yoku0825/mysql-5.7.7-rc/storage/myisam/mi_writ
e.c:223
51/172

InnoDBを使⽤した暗黙のテンポラリーテーブルがあふれた
場合
mysql57> SELECT * FROM (SELECT * FROM t1 WHERE val LIKE 'abc%') AS t
1, (SELECT * FROM t1 WHERE val LIKE 'def%') AS t2 ORDER BY t1.num;
ERROR 1114 (HY000): The table '/tmp/#sql_2974_0' is full
$ tail error.log
..
2015-04-30T15:04:22.549484+09:00 87 [ERROR] InnoDB: posix_fallocate
(): Failed to preallocate data for file ./ibtmp1, desired size 671088
64 bytes. Operating system error number 28. Check that the disk is no
t full or a disk quota exceeded. Make sure the file system supports t
his function. Some operating system error numbers are described at ht
tp://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.htm
l
2015-04-30T15:04:22.590070+09:00 87 [Warning] InnoDB: 1048576 bytes s
hould have been written. Only 794624 bytes written. Retrying again t
o write the rem
52/172

⼀度利⽤したibtmp1上の領域は再起動するまで解放されな
い。
$ ll /usr/mysql/5.7.7/data/ibtmp1
-rw-r----- 1 yoku0825 yoku0825 485236736 4月 30 15:04 2015 /us
r/mysql/5.7.7/data/ibtmp1
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root
42G 40G 17M 100% /
53/172

Disk Fullは簡単
にデータがぶっ壊
れます
（特にbinlog）
54/172

対策
internal_tmp_disk_storage_engineはオンラインでSET
GLOBALでも変更できる
$ vim my.cnf
[mysqld]
innodb_temp_data_file_path= ibtmp1:256M ## autoextendしなけれ
ば、このサイズで打ちどまる
## or
internal_tmp_disk_storage_engine= MyISAM ## 今までどおりMyISAM
を使わせる
mysql57> SET GLOBAL internal_tmp_disk_storage_engine= MyISAM;
55/172

56/172

57/172

MySQL 5.6とそれ以前は、パスワードハッシュは
mysql.user.passwordに格納されていた。
今まで存在はしたものの何もデータが⼊らなかった
mysql.user.authentication̲stringというカラムに⼊ってい
る。
58/172

MySQL 5.7では、こう
mysql57> SELECT user, host, password, authentication_string FROM
user WHERE user <> 'root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql57> SELECT user, host, authentication_string FROM user WHER
E user <> 'root';
+--------------+------+------------------------------------------
-+
| user | host | authentication_strin
g |
+--------------+------+------------------------------------------
-+
9 |
| old_password | % | NUL
L |
+--------------+------+------------------------------------------
-+
60/172

SET PASSWORD= PASSWORD('..')が非推奨、SET PASSWORD=
'..'の構⽂に変更
この構⽂は5.6までは逆にエラー”ERROR 1372
(HY000): Password hash should be a 41-digit
hexadecimal number”になる。
-
61/172

SET PASSWORD= PASSWORD('..')が非推奨
mysql57> SET PASSWORD = PASSWORD('test');
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'SET PASSWORD = PASSWORD('<plaintext_password>')' is dep
recated and will be removed in a future release. Please use SET P
ASSWORD = '<plaintext_password>' instead
mysql57> SET PASSWORD = 'root';
62/172

SHOW GRANTSの出⼒結果からパスワードハッシュが取り除か
れた。
最近の5.5でもSUPERでないユーザーだとʼ<secret>ʻに
マスクされるようになった。
-
63/172

SHOW GRANTSの出⼒結果からパスワードハッシュが取り除
かれた
mysql56> SHOW GRANTS FOR yoku0825G
*************************** 1. row ***************************
Grants for yoku0825@%: GRANT USAGE ON *.* TO 'yoku0825'@'%' IDENT
IFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
mysql57> SHOW GRANTS FOR yoku0825G
*************************** 1. row ***************************
Grants for yoku0825@%: GRANT USAGE ON *.* TO 'yoku0825'@'%'
64/172

CREATE USERせずにいきなりGRANTを叩くとワーニングまたは
エラー。
パスワードなしでいきなりGRANTはエラー。-
sql_mode= NO_AUTO_CREATE_USERがデフォルトに設定され
ている。
-
65/172

CREATE USERせずにいきなりGRANTを叩くとワーニングまた
はエラー
mysql57> GRANT USAGE ON *.* TO new_user;
ERROR 1133 (42000): Can't find any matching row in the user table
mysql57> GRANT USAGE ON *.* TO new_user IDENTIFIED BY 'test';
mysql57> SHOW WARNINGSG
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: Using GRANT statement to modify existing user's properties othe
r than privileges is deprecated and will be removed in future release. U
se ALTER USER statement for this operation.
66/172

IDENTIFIED BY PASSWORD '..'構⽂の非推奨
IDENTIFIED WITH mysql_native_password AS '..'が代替
構⽂
-
67/172

IDENTIFIED BY PASSWORD '..'構⽂の非推奨
mysql57> CREATE USER new_user IDENTIFIED BY PASSWORD '*94BDCEBE19
083CE2A1F959FD02F964C7AF4CFC29';
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'IDENTIFIED BY PASSWORD' is deprecated and will be remov
ed in a future release. Please use IDENTIFIED WITH <plugin> AS <h
ash> instead
mysql57> CREATE USER new_user IDENTIFIED WITH mysql_native_passwo
rd AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
68/172

ALTER USERステートメント
今まではGRANTステートメントで上書きしていたアカウン
トに紐付く属性の上書き
PASSWORD, REQUIRE SSL,
MAX̲QUERIES̲PER̲HOUR, ..
-
5.6ではEXPIRE PASSWORDしかできなかった⼦が今後の標
準になるっぽい
-
69/172

ALTER USERステートメント
mysql57> GRANT USAGE ON *.* TO yoku0826 WITH MAX_USER_CONNECTION
S 10;
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: Using GRANT statement to modify existing user's properti
es other than privileges is deprecated and will be removed in fut
ure release. Use ALTER USER statement for this operation.
mysql57> ALTER USER yoku0827 WITH MAX_USER_CONNECTIONS 10;
70/172

secure̲file̲priv
File̲priv持ちのユーザーのSELECT .. INTO OUTFILE ..ステ
ートメントやLOAD DATA INFILE ..ステートメント、
LOAD_FILE関数の動作を制限。
指定されていない場合、mysqldの実効ユーザー権限で全
てのファイルにアクセス可能
-
相変わらずオンラインで変更はできない
71/172

secure̲file̲privの動作⾃体は変わっていない
mysql56> SELECT @@global.secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/ |
+--------------------+
mysql56> SELECT 1 INTO OUTFILE '/home/mysql/test.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-p
riv option so it cannot execute this statement
mysql56> SELECT LOAD_FILE('/etc/hosts');
+-------------------------+
| LOAD_FILE('/etc/hosts') |
+-------------------------+
| NULL |
+-------------------------+
72/172

secure̲file̲priv変更点
rpm, debなどのパッケージものの場合、5.7から暗黙のデフ
ォルトが /var/lib/mysql-files に設定される
secure_file_priv= NULLが指定されている場合、FILE権限を
利⽤する操作は全て不可能。
secure̲file̲privが設定されていない場合はワーニングを出
⼒するようになった。
73/172

secure̲file̲priv未設定のワーニング
2015-02-17T07:09:49.446585Z 0 [Warning] Insecure configuration fo
r --secure-file-priv: Current value does not restrict location o
f generated files. Consider setting it to a valid, non-empty pat
h.
MySQL :: MySQL 5.7 Reference Manual :: 5.1.3 Server
Command Options
74/172

log̲timestamps
デフォルトではスローログ, エラーログ, ジェネラルログの
タイムスタンプがUTC になってしまう。
暗黙のデフォルトはUTC。”UTC”または”SYSTEM”のどちら
かしか取れない。
SET GLOBAL log_timestamps= SYSTEM; でオンライン変更
も可能。
-
my.cnfの[mysqld]セクションにlog_timestamps= SYSTEM
推奨。
-
バイナリーログは影響を受けない。
MySQL :: MySQL 5.7 Reference Manual :: 5.1.4 Server
System Variables
75/172

log̲timestamps
たとえばスローログ
$ tail data/slow.log
..
# Time: 2015-03-09T05:01:02.714121Z
# User@Host: root[root] @ localhost [] Id: 14
# Query_time: 0.000633 Lock_time: 0.000215 Rows_sent: 0 Rows_ex
amined: 0
SET timestamp=1425877262;
INSERT INTO t1 SELECT * FROM t1 ORDER BY RAND() LIMIT 1;
76/172

log̲timestamps
log_timestamps= SYSTEMすると+09:00がオフセットとして
追加される。
$ tail data/slow.log
..
# Time: 2015-03-09T14:03:16.607072+09:00
# User@Host: root[root] @ localhost [] Id: 14
# Query_time: 0.000671 Lock_time: 0.000226 Rows_sent: 0 Rows_ex
amined: 0
SET timestamp=1425877396;
INSERT INTO t1 SELECT * FROM t1 ORDER BY RAND() LIMIT 1;
77/172

fluent-plugin-
mysqlslowquery
さんは⼤丈夫だろ
うか
78/172

mysql̲install̲db
パスがscripts/mysql̲install̲dbからbin/mysql̲install̲db
に変わっている
バイナリー.tar.gz, ソースビルド派のみ
scriptsからbinに移動されている
5.5までshell script
5.6ではPerl script
5.7でCになってscriptじゃなくなったからだろうか。
-
rpmは相変わらず/usr/bin/mysql_install_dbにあるので
パスの変更は気にしなくていい。
-
/usr/my.cnf作らなくなりました :)
79/172

mysql̲install̲db
せっかくバイナリーになったのに、あっという間に
deprecated になった
5.7.6以降ではmysqld --initializeを使う。-
まだしばらくはmysql_install_dbも使えそうだけど、い
ずれ置き換えられる。
-
80/172

mysql̲install̲db
$ mysql_install_db --datadir=./
2015-09-29 20:10:13 [WARNING] mysql_install_db is deprecated. Ple
ase consider switching to mysqld --initialize
2015-09-29 20:10:17 [WARNING] The bootstrap log isn't empty:
2015-09-29 20:10:17 [WARNING] mysqld: [Warning] --bootstrap is de
precated. Please consider using --initialize instead
OpenSSL 1.0.1e-fips 11 Feb 2013
server-cert.pem: OK
client-cert.pem: OK
81/172

mysql̲install̲dbの作成するユーザー
5.6までは root@localhost, root@127.0.0.1, root@::1,
root@hostnameが黙って作成された。
5.7では(デフォルトは) root@localhostのみ。
--skip-name-resolveな状態で
は”root@localhost”と”root@127.0.0.1”は別⼈なの
で、 Windows環境では注意
MySQL Bugs: #75656: 5.7.5 skip̲name̲resolve
stops all connection attempts
-
mysql.sock.lockの登場によりソケットの上書きリスクは
減ったものの、root@127.0.0.1は⼿で作っておいてもい
いかも。
-
82/172

mysql̲install̲dbを置き換えるものとして
–datadirは必ず指定してやらないといけない
(mysql̲install̲dbには暗黙のデフォルトがあった)
-
初期パスワードは標準エラー出⼒に吐く
--log-errorが設定されてるなら標準エラー出⼒がそっ
ちに向く
rpmで突っ込んだ場合は/var/log/mysqld.logから
grepして取り出すことになるかと。
~/.mysql̲secretは作らない
-
83/172

$ bin/mysqld --initialize
..
2015-02-17T02:07:18.782831Z 1 [Note] Creating the system database
2015-02-17T02:07:18.782984Z 1 [Warning] A temporary password is g
enerated for root@localhost: wokeG8n=Joia
2015-02-17T02:07:18.783207Z 1 [Note] Creating the system tables
..
84/172

mysqld再起動直後にI/O読み込みがガリガリ発⽣するけどあ
わてないで。
この処理が終わるまでの間にトラフィックが突っ込んで
くるとかなり悲惨。
-
暗黙のデフォルトでは正常終了時にバッファプール全体の
25%のページ番号をテキストファイルに書き出し、起動時
にそのページ番号をバッファプールに読み込む。
パーセンテージはinnodb̲buffer̲pool̲dump̲pct変数
で指定可能、5.6では100%固定だったものが5.7ではデ
フォルト25%に。
-
個⼈的にはinnodb_buffer_pool_dump_at_shutdown= ON,
innodb_buffer_pool_load_at_startup= OFFがいい。 85/172

今までのSTATEMENT(5.1の⼀時期だけMIXED)からROW
に
バイナリーログ容量の増加
ここに1クエリーで100万⾏更新しているバッチがあるじ
ゃろ︖ （AA略）
-
スレーブ側ではTRIGGERが実⾏されない(マスターとスレー
ブで違うトリガーを設定している場合に問題化)
mysqlbinlog -vv はちょっと読みにくいなぁ。。
86/172

binlog̲format= STATEMENT
$ mysqlbinlog bin.000002
# at 294
#150929 20:12:51 server id 1 end_log_pos 396 CRC32 0x3fcab02a
Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1443525171/*!*/;
INSERT INTO t1 VALUES (1, 'one')
/*!*/;
..
# at 567
#150929 20:12:56 server id 1 end_log_pos 665 CRC32 0x5d3a9895
Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1443525176/*!*/;
DELETE FROM t1 WHERE num = 1
/*!*/;
87/172

binlog̲format= ROW
$ mysqlbinlog -vv bin.000003
# at 289
p: `d1`.`t1` mapped to number 108
# at 335
ws: table id 108 flags: STMT_END_F
BINLOG '
QnIKVhMBAAAALgAAAE8BAAAAAGwAAAAAAAEAAmQxAAJ0MQACCA8CIAACPJfzhg==
QnIKVh4BAAAAMAAAAH8BAAAAAGwAAAAAAAEAAgAC//wBAAAAAAAAAANvbmVriXRQ
'/*!*/;
### INSERT INTO `d1`.`t1`
### SET
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='one' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
# at 549
p: `d1`.`t1` mapped to number 108
# at 595
ows: table id 108 flags: STMT_END_F
BINLOG '
RHIKVhMBAAAALgAAAFMCAAAAAGwAAAAAAAEAAmQxAAJ0MQACCA8CIAACi6wgiw==
RHIKViABAAAAMAAAAIMCAAAAAGwAAAAAAAEAAgAC//wBAAAAAAAAAANvbmVo4g21
'/*!*/;
### DELETE FROM `d1`.`t1`
### WHERE
### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='one' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
88/172

rpmパッケージではvalidate̲passwordプラグインがデ
フォルトで有効
デフォルトでは8⽂字以上、英⼤⽂字⼩⽂字数字記号の4種
類
これを満たしていないとSET PASSWORDが転けるしSHOW
VARIABLESさえ⾒られない。
Do̲you̲love̲MySQL57? でこれを満たせるのでどうぞ
ご活⽤ください（ Do̲you̲love̲PHP7? でもいいです
よ :-P
89/172

validate̲passwordプラグイン
mysql> SHOW VARIABLES LIKE 'validate%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement befo
re executing this statement.
mysql> SET PASSWORD= 'unsafe_password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requiremen
ts
mysql> SET PASSWORD= 'Do_you_love_MySQL57?';
mysql> SHOW VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
6 rows in set, 1 warning (0.00 sec)
90/172

innodb_file_format= Barracuda, innodb_large_prefix= ON
と合わせて、innodb_default_row_format= Dynamicがデフォ
ルトに。
InnoDBのセカンダリーキー767bytes制限、1⾏のデータ
8000bytes制限を⾶び越えるための設定（5.5から使えた）
がデフォルト化。
91/172

ただし、オンラインALTER TABLEにちょっとだけ影響を及
ぼす。
ADD INDEXには影響ないです。-
ADD COLUMNの場合、オンラインはオンラインなんだけど
同時にROW̲FORMATの暗黙の変換が（場合によって）
⾏われるので、テーブルを再構築し始める（オンライン
はオンラインでできる）
-
既にROW̲FORMATが明⽰されている場合は暗黙の変換は
起こらない。
92/172

secure̲file̲priv
log̲timestamps
mysql̲install̲db
rpmパッケージではvalidate̲passwordプラグインがデフォ
ルトで有効
93/172

94/172

sysスキーマ
performance̲schemaの情報を⾒やすくするためのビ
ュー, ストアドプロシージャの集合
ストアドプロシージャの⽅はあまり本家で語られること
がないけれど、ps̲setup̲saveとか便利。
-
SHOW DATABASESするとsysがいますが、気にしないでくださ
い。
95/172

sys.metrics
監視に良さそうな項目の詰め合わせ。
Type= InnoDB Metricsも詰め合わせてある。SHOW ENGINE
INNODB STATUSをパースしなくてもそのままfluentdに⾷わせ
てKibanaに送ったらいいんじゃないか疑惑 in 俺の中。
96/172

sys.metrics
mysql> SELECT * FROM metrics LIMIT 10;
+----------------------------+----------------+---------------+---------+
| Variable_name | Variable_value | Type | Enabled |
+----------------------------+----------------+---------------+---------+
| aborted_clients | 4963 | Global Status | YES |
| aborted_connects | 1 | Global Status | YES |
| binlog_cache_disk_use | 0 | Global Status | YES |
| binlog_cache_use | 14779017 | Global Status | YES |
| binlog_stmt_cache_disk_use | 0 | Global Status | YES |
| binlog_stmt_cache_use | 502 | Global Status | YES |
| bytes_received | 21737114595 | Global Status | YES |
| bytes_sent | 33296720984 | Global Status | YES |
| compression | OFF | Global Status | YES |
| com_admin_commands | 5827 | Global Status | YES |
+----------------------------+----------------+---------------+---------+
97/172

sys.schema̲index̲statistics
どのインデックスを使ってどれだけハンドラーが呼ばれた
か、そのレイテンシーの合計は…というのが⾒られる。
あんまり使われてないインデックスをあぶりだすのは後述の
sys.schema_unused_indexesビューでもいける。
98/172

sys.schema̲index̲statistics
mysql> SELECT * FROM schema_index_statistics LIMIT 1G
*************************** 1. row ***************************
table_schema: xxxxxxxx
table_name: table_1
index_name: PRIMARY
rows_selected: 14113184
select_latency: 5.21 m
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 13327613
update_latency: 39.57 m
rows_deleted: 0
delete_latency: 0 ps
99/172

sys.schema̲unused̲indexes
名前そのものズバリ、使われてない（統計開始から⼀度も使
われていない）インデックスを⼀覧してくれる。
ただし、performance_schema.setup_actors,
performance_schema.setup_objects,
performance_schema.setup_instruments= 'wait/io/table/
sql/handler'あたりの設定に依存する。吊るしで使ってるな
らフツーのテーブルではmysqldの起動時から全部統計が有効
になっている。
100/172

sys.schema̲unused̲indexes
mysql> SELECT * FROM sys.schema_unused_indexes LIMIT 3G
*************************** 1. row ***************************
object_schema: xxxxxxx
object_name: table_1
index_name: idx_number_1
*************************** 2. row ***************************
*************************** 3. row ***************************
101/172

sys.statement̲analysis
sys.format_statementというストアドファンクションを噛ん
でいるせいで、queryカラムが切り詰められる。
performance_schema.events_statements_summary_by_diges
t.digest_textに切り詰められていないステートメントがあ
るので、USING (digest)でJOINしてやるとpt-query-
digestっぽい出⼒が得られる。
102/172

sys.statement̲analysis
mysql> SELECT * FROM sys.statement_analysis LIMIT 1G
*************************** 1. row ***************************
query: UPDATE `table_21` SET `bo ... RE `table_21` . `column_1` = ?
db: xxxxxxx
full_scan:
exec_count: 13263309
err_count: 0
warn_count: 0
total_latency: 1.40 h
max_latency: 18.38 ms
avg_latency: 378.83 us
lock_latency: 20.10 m
rows_sent: 0
rows_sent_avg: 0
rows_examined: 13263309
rows_examined_avg: 1
rows_affected: 13263227
rows_affected_avg: 1
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: a73db02641a1215f4e54093763bde706
first_seen: 2015-06-24 17:46:51
last_seen: 2015-09-28 11:10:24
103/172

sys.statements̲with̲errors̲or̲warnings
カジュアルに桁切り詰めワーニングを無視してるステートメ
ントとか⼀網打尽にできる。
--gtid-enforce-consistency= WARNと組み合わせて使うと夢
が広がる。
DBAからの攻めの⼀⼿にどうぞ。
104/172

sys.statements̲with̲errors̲or̲warnings
mysql> SELECT * FROM sys.statements_with_errors_or_warnings LIMI
T 1G
*************************** 1. row ***************************
query: SELECT IF ( ( `locate` ( ? , ` ... . `COMPRESSED_SI
ZE` ) ) DESC
db: sys
exec_count: 1
errors: 1
error_pct: 100.0000
warnings: 0
warning_pct: 0.0000
first_seen: 2015-09-28 10:36:27
last_seen: 2015-09-28 10:36:27
digest: 97b93c349004ed5d8774479aecc83eda
105/172

危険なヤツら
sys.innodb̲buffer̲stats̲by̲schema
sys.innodb̲buffer̲stats̲by̲table
sys.schema̲table̲statistics̲with̲buffer
これ注意。迂闊に触ると死ぬことがある。
内部的にinformaition̲schema.innodb̲buffer̲pageを⾒
ているので、でかいバッファプール（経験則だと20GB超）
でやるとたまに突き刺さる。
特にCtrl + Cでクエリーを殺すと突き刺さる率が俺の中
では⾼い
-
106/172

名前で判りそうなやつら
sys.statements̲with̲temp̲tables
sys.statements̲with̲full̲table̲scans
sys.statements̲with̲runtimes̲in̲95th̲percentile
sys.statements̲with̲sorting
sys.statements̲with̲temp̲tables
107/172

5.6ではマスターとスレーブで同じgtid-modeを持たなけれ
ばならず、gtid̲modeはオンライン変更不可能だった。
masterslave gtid-mode= OFF gtid-mode= ON
gtid-mode= OFF ○ ×
gtid-mode= ON × ○
109/172

5.7ではOFF̲PERMISSIVEとON̲PERMISSIVEが追加さ
れ、かつ、オンラインでgtid̲modeを変更できるようにな
った。
masterslave OFF OFF̲PERMISSIVE ON̲PERMISSIVE ON
OFF ○ ○ ○ ×
OFF̲PERMIS
SIVE
○ ○ ○ ×
ON̲PERMISS
IVE
× ○ ○ ○
ON × ○ ○ ○
110/172

GTIDのオンライン有効化
slave> SET GLOBAL gtid_mode= OFF_PERMISSIVE;
master> SET GLOBAL gtid_mode= OFF_PERMISSIVE;
slave> SET GLOBAL gtid_mode= ON_PERMISSIVE;
master> SET GLOBAL gtid_mode= ON_PERMISSIVE;
slave> SET GLOBAL enforce_gtid_consistency= ON;
master> SET GLOBAL enforce_gtid_consistency= ON;
slave> SET GLOBAL gtid_mode= ON;
master> SET GLOBAL gtid_mode= ON;
111/172

enforce_gtid_consistencyの取りうる値がON, OFF, WARN
の3種類に。
enforce_gtid_consistency= WARNだと、ON̲PERMISSIVE
以上のgtid̲modeではエラーになるステートメントがワー
ニングで出⼒される。
sys.statements_with_errors_or_warningsで拾っていく
スタイル。
-
112/172

enforce̲gtid̲consistency= WARN
mysql57> SELECT @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| WARN |
+----------------------------+
mysql57> CREATE TABLE t2 AS SELECT * FROM t1;
Records: 1 Duplicates: 0 Warnings: 1
+---------+------+--------------------------------------------------------------
-+
| Level | Code | Messag
e |
+---------+------+--------------------------------------------------------------
-+
| Warning | 1786 | Statement violates GTID consistency: CREATE TABLE ... SELEC
T. |
+---------+------+--------------------------------------------------------------
-+
113/172

今まではレプリケーションの系全体を⼀度シャットダウンし
ないといけなかった。
mysqlfailoverやmysqlfabricはGTIDの有効化が条件になっ
ている。
MHA for MySQLがrelay_log_info_repository= TABLE
（クラッシュセーフスレーブの条件）で上⼿く動かなか
ったので、どうにかしたかった。。
-
これから流⾏る…といいな。
114/172

Not Only InnoDB.
WITH PARSER句で利⽤するフルテキストパーサーを指定でき
る（指定⾃体は5.5からできる）
Ngram（デフォルトで有効）とMeCab（バンドルされてい
るが、⾃分でmecabrcを編集してINSTALL PLUGIN）
ざっとベンチした感じ、「MeCab」かつ「ファイルソート
が気にならないレベルの⾏数」なら使えるかと。
115/172

MeCab FTパーサーの有効化
$ vim /etc/my.cnf
[mysqld]
loose-mecab-rc-file= /usr/lib64/mysql/mecab/etc/mecabrc
mysql57> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
116/172

MySQLの全⽂検索で検索
mysql57> CREATE TABLE t1 (num serial, val varchar(32), FULLTEXT K
EY(val) WITH PARSER mecab) CHARSET utf8;
mysql57> SELECT * FROM t1 WHERE match(val) against ('がんばらな
い' IN BOOLEAN MODE);
+-----+--------------------------------------+
| num | val |
+-----+--------------------------------------+
| 4 | 魔法少女＠がんばらない |
| 25 | ささみさん＠がんばらない |
| 85 | テラ＠がんばらない |
+-----+--------------------------------------+
117/172

前提としてinnodb-undo-tablespaces >= 2が必要（UNDOロ
グがibdata1ではなくてundo001とかに書かれている必要があ
る）
ibdata1が作成される時点で指定されていなければならな
いので、mysql_install_db(or mysqld --initialize)の時
点でmy.cnfに書き込んでおかないといけない。
-
SET GLOBAL innodb_undo_log_truncation= 1で有効化。
innodb_max_undo_log_sizeを超えているundoログがある場
合、デフォルトではパージ処理が128回（これが最⼤値）呼
ばれるたびにundo001とかのシュリンクが⾛ってしまうの
で、⼩さくなったのが確認できたら無効に戻しておく⽅が良
い。
118/172

5.7.7までの書き⽅と変わっている
セッション変数(と暗黙のデフォルトとしてのグローバル変
数)でも指定可能。
単位はミリ秒。
SELECTステートメントでだけ有効。安⼼してください。
119/172

mysql57> SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1;
ERROR 3024 (HY000): Query execution was interrupted, maximum stat
ement execution time exceeded
mysql57> SET SESSION max_execution_time= 1000;
ERROR 3024 (HY000): Query execution was interrupted, maximum stat
ement execution time exceeded
120/172

「実カラムの値を計算した結果」を格納する専⽤のカラムを
作成できる
STOREDタイプはALTER TABLE時に値が計算されてDisk
に固定化
-
VIRTUALタイプはSELECT時に都度計算-
どちらのタイプもセカンダリーインデックスを作成でき
る（セカンダリーインデックスはどちらのタイプでも
Diskに固定化される）
-
121/172

generated column
mysql> SELECT * FROM t1;
+------+------------+
| num | val |
+------+------------+
| 1 | one |
| 99 | ninty-nine |
+------+------------+
mysql> ALTER TABLE t1 ADD is_over_20 TINYINT AS (num > 20) NOT NU
LL;
122/172

generated column
mysql> SELECT * FROM t1;
+------+------------+------------+
| num | val | is_over_20 |
+------+------------+------------+
| 1 | one | 0 |
| 99 | ninty-nine | 1 |
+------+------------+------------+
mysql> ALTER TABLE t1 ADD KEY (is_over_20);
123/172

⽣成された列にNOT NULL制約、UNIQUE制約もかけられる
（疑似チェック制約が作れる）
直接値を投⼊することはできないが、「カラム」としては定
義されるのでSELECT *とかINSERT INTO .. VALUESに注意。
124/172

オレオレ証明書を⼀発で作ってくれるmysql_ssl_rsa_setup
コマンド
どこで実⾏してもdatadirに出⼒されるので、出⼒先を変
える時は--datadirオプションを渡す
-
$ mysql_ssl_rsa_setup
..
writing RSA key
$ ll *.pem
-rw------- 1 mysql mysql 1675 Sep 29 20:22 ca-key.pem
-rw-r--r-- 1 mysql mysql 1078 Sep 29 20:22 ca.pem
-rw-r--r-- 1 mysql mysql 1086 Sep 29 20:22 client-cert.pem
-rw------- 1 mysql mysql 1679 Sep 29 20:22 client-key.pem
-rw------- 1 mysql mysql 1679 Sep 29 20:22 private_key.pem
-rw-r--r-- 1 mysql mysql 451 Sep 29 20:22 public_key.pem
-rw-r--r-- 1 mysql mysql 1086 Sep 29 20:22 server-cert.pem
-rw------- 1 mysql mysql 1679 Sep 29 20:22 server-key.pem
125/172

クライアントにSSL接続を強要する
require_secure_transportオプション
mysql57> SET GLOBAL require_secure_transport= 1;
$ mysql57 --protocol=tcp --skip-ssl
ERROR 3159 (HY000): Connections using insecure transport are proh
ibited while --require_secure_transport=ON.
126/172

JSONデータ型
データ型としてのJSONであり、基本的にはデータのバリデ
ーションのみ⾏ってくれる（バイナリーパックとかしている
らしいけど、クライアントから⾒るとバリデーションの有無
だけがTEXT/BLOBに突っ込む時との違い）
JSONの検索はgenerated columnによる関数インデックス
で多少何とかできる。
基本的に、今まで（トランザクションの保護が欲しいとかの
理由で）TEXT/BLOBにJSONを突っ込んでいた⼈たちがち
ょっと幸せになるための機能。
127/172

JSONデータ型
バリデーションに失敗した時のエラーが3000番台なのが新
しい。
mysql57> CREATE TABLE t2 (num serial, j json);
mysql57> INSERT INTO t2 VALUES (1, 'aaa');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at positi
on 0 in value (or column) 'aaa'.
128/172

JSON型のための関数
mysql> SELECT val FROM t1 LIMIT 1G
*************************** 1. row ***************************
val: {"dt": 1443603809, "id": 1850147, "cod": 200, "sys": {"id": 7619, "
type": 1, "sunset": 1443601626, "country": "JP", "message": 0.0181, "sun
rise": 1443558902}, "base": "stations", "main": {"temp": 294.52, "humidi
ty": 49, "pressure": 1020, "temp_max": 295.37, "temp_min": 292.04}, "nam
e": "Tokyo", "wind": {"deg": 200, "speed": 6.7}, "coord": {"lat": 35.6
9, "lon": 139.69}, "clouds": {"all": 75}, "weather": [{"id": 803, "icon
": "04n", "main": "Clouds", "description": "broken clouds"}], "visibilit
y": 10000}
mysql> SELECT json_extract(val, '$.dt') AS dt, json_extract(val, '$.sys.
country') AS country FROM t1 LIMIT 1;
+------------+---------+
| dt | country |
+------------+---------+
| 1443603809 | "JP" |
+------------+---------+
129/172

5.6まではスキーマが分かれていないとパラレルに動けなか
った
5.7ではslave_parallel_type= {DATABASE|LOGICAL_CLOCK}で
スキーマ単位のMTSと、マスターで記録されたロジカルタ
イムスタンプを利⽤したMTSが選べる。
マスターはどのトランザクションとどのトランザクショ
ンが競合せずにCOMMITできたかを知っている
-
競合せずにCOMMITできたトランザクション同⼠は、ス
レーブでパラレルに実⾏されても競合することはない
-
gitでファイルAを更新したコミットとファイルBを更新し
たコミットはコンフリクトしないから、どっちを先にマ
ージしても構わない…というのと⼀緒（かな︖）
-
130/172

sysスキーマ
JSONデータ型
131/172

132/172

バッファプールを⼤きくするとき
innodb̲buffer̲pool̲chunk̲size ごとに新しいページを
確保しながらゴニョゴニョやる
-
この処理中はバッファプールへの全てのアクセスがブロ
ックされる
-
133/172

バッファプールを⼩さくするとき
innodb̲buffer̲pool̲chunk̲size ごとにページを追い出
しながらゴニョゴニョやる
-
常にバッファプールミスヒットが起こり続けるイメージ
でだいたい合ってる。
-
⽌めてバッファプールの暖気をしなおすよりは速くて便利だ
けど、本当にオンラインでやるべきではない。
134/172

innodb̲buffer̲pool̲sizeのオンライン変更
非同期で裏でやるのでSET GLOBALはすぐ戻ってくる
mysql57> SET GLOBAL innodb_buffer_pool_size= 2 * 1024 * 1024 * 1024;
$ less error.log
2015-09-29T11:24:49.099855Z 0 [Note] InnoDB: Resizing buffer pool from 1
34217728 to 2147483648. (unit=134217728)
2015-09-29T11:24:49.100508Z 0 [Note] InnoDB: disabled adaptive hash inde
x.
2015-09-29T11:24:49.274486Z 0 [Note] InnoDB: buffer pool 0 : 15 chunks
(122880 blocks) were added.
2015-09-29T11:24:49.277227Z 0 [Note] InnoDB: buffer pool 0 : hash table
s were resized.
2015-09-29T11:24:49.330346Z 0 [Note] InnoDB: Resized hash tables at lock
_sys, adaptive hash index, dictionary.
2015-09-29T11:24:49.330397Z 0 [Note] InnoDB: Completed to resize buffer
pool from 134217728 to 2147483648.
2015-09-29T11:24:49.333206Z 0 [Note] InnoDB: Re-enabled adaptive hash in
dex.
135/172

InnoDBのクラッシュリカバリーの中でバイナリーログを使
う箇所がある
より安全なsync_binlog= 1に変更…というか-
sync_binlog= 0は危険な設定（クラッシュリカバリーが
失敗することがある）
-
binlogへの書き出しが失敗した際に、⼀貫性を保つため
mysqldをダウンさせるオプションが有効になっている
MySQL :: MySQL 5.7 Reference Manual ::
17.1.6.4 Binary Logging Options and Variables
-
実は5.6から既にsync̲binlog= 0は危険なんだけど。-
136/172

マルチスレッドスレーブ(MTS)とは別物。
1つのI/Oスレッドは相変わらず1つのマスターにしか接続し
ない。
今のところMTSと併⽤できない。
複数のMySQLから集約スレーブを作ってJOINJOIN。
⽔平シャーディングしたサーバーの集約。
137/172

シングルソース、シングルスレッドレプリケーション
138/172

シングルソース、マルチスレッドレプリケーション
139/172

マルチソース、シングルスレッドレプリケーション
140/172

今までのレプリケーション関連コマンドの末尾にFOR
CHANNEL 'channel_name'をつけるだけでOK
mysql57> STOP SLAVE FOR CHANNEL 'node2';
mysql57> SHOW SLAVE STATUS FOR CHANNEL 'node2'G
mysql57> START SLAVE FOR CHANNEL 'node2';
141/172

--replicate-ignore-*オプションとか--replicate-do-*オプ
ション相当の操作がSQLインターフェースで可能になった
ただしSQLスレッドは停⽌する必要がある
142/172

mysql57> CHANGE REPLICATION FILTER replicate_ignore_db= (mysql, p
erformance_schema);
ERROR 3017 (HY000): This operation cannot be performed with a run
ning slave sql thread; run STOP SLAVE SQL_THREAD first
mysql57> STOP SLAVE sql_thread;
mysql57> CHANGE REPLICATION FILTER replicate_ignore_db= (mysql, p
erformance_schema);
mysql57> START SLAVE sql_thread;
143/172

Transparent Page Compression
今までの圧縮はソフトウェア(InnoDB内)でページまるごと
zlib圧縮
Transparent Page Compressionはページの中のデータ部
分だけ圧縮し、ページ内の空き領域の圧縮はファイルシステ
ムとデバイスに任せる
zlibとlz4が選べる
144/172

対応ディストリビューションが限定されている、Intel SSD
でxfsだとデバイスに任せた⽅がむしろ遅かった、とか、⾃
分でベンチした⽅が良さげ
Fusion-ioとなら相性良いはず（今までとほぼ同じ圧縮率、
今までの無圧縮に数%のオーバーヘッドだけで済むとか⾔っ
てる）
Third day with InnoDB transparent page compression
InnoDB Transparent Page Compression | MySQL
Server Blog
145/172

{BEFORE|AFTER} {INSERT|UPDATE|DELETE}トリガー
があったが、同じアクションに対するトリガーは1つしか定
義できなかった。
MySQL 5.7からはBEFORE INSERTトリガーを3つとか作れ
る。
複数設定できるということは順番の概念が発⽣するので気を
付けて。
146/172

mysql57> CREATE TRIGGER before_insert_1 BEFORE INSERT ON t1 FOR
EACH ROW INSERT INTO t2 SET num = NEW.num, val = NEW.val;
mysql57> CREATE TRIGGER before_insert_2 BEFORE INSERT ON t1 FOR
EACH ROW UPDATE t2 SET val = '残念だったな' WHERE num = NEW.nu
m;
mysql57> INSERT INTO t1 VALUES (1, 'one');
Query OK, 1 row affected (0.00 sec)
+-----+--------------------+
| num | val |
+-----+--------------------+
| 1 | 残念だったな |
+-----+--------------------+
147/172

mysql p ump
mysql d umpの後継を狙っているらしい。論理バックアッ
プのパラレル版。
mysqlpumpの中だけで圧縮(zlib, lz4)
--userオプションでGRANTステートメントを出⼒してくれ
たり
--watch-progressオプションがデフォルト有効
インデックスの遅延ロード(--defer-table-indexes)がデフ
ォルトで有効
まだ発展途上。これからに期待
148/172

log-syslog
エラーログをloggerで出⼒する
今までもmysqld_safeには--syslogオプションがあったけ
ど、アレはmysqld_safeのログだけ
log_syslog_facility, log_syslog_tagオプションで読みやす
くできる
ちなみにこっちはlog_timestampsの影響を受けないので、エ
ラーログは今後こっちが⾒やすいかも
149/172

log-syslog
$ less /var/log/messages
..
Sep 29 20:27:54 e23cb0f9dde9 mysqld[1683]: /usr/sbin/mysqld (mysq
ld 5.7.8-rc-log) starting as process 1683 ...
..
Sep 29 20:27:54 e23cb0f9dde9 mysqld[1683]: Event Scheduler: Loade
d 0 events
Sep 29 20:27:54 e23cb0f9dde9 mysqld[1683]: /usr/sbin/mysqld: read
y for connections.#012Version: '5.7.8-rc-log' socket: '/var/lib/
mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
150/172

クエリーが終わったものから順番に切断(server has gone
awayが返る)
SUPER権限以外はそれ以降接続できなくなる
エラーコードが3032でそれ⽤のものになっているので、こ
のあたり上⼿くハンドルすればgraceful shutdownの夢を⾒
るか
151/172

mysql57-root> SET GLOBAL offline_mode= 1;
mysql57-yoku0825> show databases;
ERROR 2006 (HY000): MySQL server has gone away
$ mysql57 -uyoku0825
ERROR 3032 (HY000): The server is currently in offline mode
152/172

複数取れるってことは、デッドロックの可能性があるってこ
とだ
デッドロックしてもInnoDBのように他のロックをリリース
はしてくれないから、コネクションを切断するか⾃分で
release̲lockかrelease̲all̲locksしてやらないとダメ
153/172

GET̲LOCK関数でデッドロックさせてみる
mysql57> SELECT GET_LOCK('yoku0825', 1);
+-------------------------+
| GET_LOCK('yoku0825', 1) |
+-------------------------+
| 1 |
+-------------------------+
mysql57> SELECT GET_LOCK('yoku0826', 1);
ERROR 3058 (HY000): Deadlock found when trying to get user-level
lock; try rolling back transaction/releasing locks and restartin
g lock acquisition.
mysql57> SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 1 |
+---------------------+
154/172

SQLをパースする前とパースした後にプラグインAPIの⼝が
⽤意されている
MySQL Proxyがmysqldの中に⼊っちゃってる感じ
パース前ならクエリー書き換え放題
パース後のAPIに上⼿く差し込むようにすれば監査ログを吐
き出せるようにできるかな
155/172

mysql p ump
log-syslog
157/172

変更された暗黙のデフォルト
5.6 defaultに合わせるようなmy.cnfを書くと、5.6コンパ
チに近付けられる。
my.cnfだけじゃどうしようもないものもある
(mysql.user.passwordカラムがなくなったやつとか)
158/172

variable̲name 5.6 default 5.7 default
binlog̲error̲action IGNORE̲ERROR ABORT̲SERVER
binlog̲format STATEMENT ROW
binlog̲group̲commit̲sy
nc̲delay
N/A 0
binlog̲group̲commit̲sy
nc̲no̲delay̲count
N/A 0
binlog̲gtid̲simple̲recov
ery
OFF ON
check̲proxy̲users N/A OFF
default̲authentication̲pl
ugin
N/A
(mysql̲native̲password
)
mysql̲native̲password
default̲password̲lifeti
me
N/A(0) (<= 5.7.10は360) 0
159/172

disabled̲storage̲engine
s
N/A ”“(empty)
eq̲range̲index̲dive̲lim
it
10 200
gtid̲executed̲compressi
on̲period
N/A 1000
have̲statement̲timeout N/A YES
innodb̲adaptive̲hash̲in
dex̲parts
N/A(1) 8
innodb̲additional̲me
m̲pool̲size
8M N/A
innodb̲buffer̲pool̲chun
k̲size
N/A 128M
160/172

innodb̲buffer̲pool̲dum
p̲at̲shutdown
OFF ON
innodb̲buffer̲pool̲dum
p̲pct
N/A(100) 25
innodb̲buffer̲pool̲insta
nces
8 1
innodb̲buffer̲pool̲load
̲at̲startup
OFF ON
innodb̲checksum̲algorit
hm
innodb CRC32
innodb̲default̲row̲for
mat
N/A(compact) dynamic
innodb̲file̲format Antelope Barracuda
innodb̲file̲format̲max Antelope Barracuda
161/172

innodb̲fill̲factor N/A(100) 100
innodb̲flush̲sync N/A ON
innodb̲large̲prefix OFF ON
innodb̲log̲buffer̲size 8M 16M
innodb̲log̲checksum̲al
gorithm
N/A(none) CRC32
innodb̲log̲checksums N/A(OFF) ON
innodb̲log̲write̲ahead
̲size
N/A(0) 8k
innodb̲mirrored̲log̲gro
ups
1(N/A) N/A
innodb̲max̲undo̲log̲si
ze
N/A 1G
162/172

innodb̲numa̲interleave N/A(OFF) OFF
innodb̲page̲cleaners N/A(1) 1
innodb̲purge̲rseg̲trun
cate̲frequency
N/A 128
innodb̲purge̲threads 1 4
innodb̲strict̲mode OFF ON
innodb̲temp̲data̲file̲p
ath
N/A ibtmp1:12M:autoextend
innodb̲undo̲log̲truncat
e
N/A OFF
innodb̲use̲sys̲malloc ON N/A(ON)
internal̲tmp̲disk̲stor
age̲engine
N/A(MyISAM) InnoDB
163/172

log̲backward̲compatibl
e̲user̲definitions
N/A OFF
log̲builtin̲as̲identified̲
by̲password
N/A OFF
log̲error̲verbosity N/A 3
log̲syslog N/A OFF
log̲syslog̲facility N/A daemon
log̲syslog̲include̲pid N/A ON
log̲syslog̲tag N/A ”“(empty)
log̲timestamps N/A(SYSTEM) UTC
log̲warnings 1 2
max̲execution̲time N/A 0
max̲points̲in̲geometry N/A 65536
164/172

mysql̲native̲password̲
proxy̲users
N/A OFF
ngram̲token̲size N/A 2
offline̲mode N/A OFF
optimizer̲switch snip snip
performance̲schema̲* snip snip
require̲secure̲transport N/A OFF
secure̲file̲priv ”“(empty) /var/lib/mysql-files(rpm,
dpkg only)
show̲compatibility̲56 N/A OFF
slave̲net̲timeout 3600 60
slave̲parallel̲type N/A DATABASE
sql̲mode snip snip
165/172

super̲read̲only N/A OFF
sync̲binlog 0 1
table̲open̲cache̲instan
ces
1 16
thread̲concurrency 10 N/A
timed̲mutexes OFF N/A
transaction̲write̲set̲ex
traction
N/A OFF
166/172

⼼得
向こう1年くらいは新機能には⼿を出さない
新しいパラメーター、変更になったパラメーターは調べてお
いた⽅がいい
5.5 => 5.6は⼀部を除いてそんなに影響はなかった
（し、オンラインで変更できるものが多かった）
-
5.6の時と同じペースなら、GAから1年後くらいにアーリー
マジョリティーが出てくるかなってところ。
既存のものを置き換えるよりは、まっさらなサービスを作る
タイミングで積極的に地雷を踏み抜く運⽤上の問題を洗い
出していく
踏み抜いた罠運⽤上の問題を共有してもらえれば、次の
⼈はうまくいく
-
167/172

罠 #とは
知らないところに置かれているといきなり⾷らって致命傷に
なることがある
置いてある場所がわかって、どんな罠だかがわかって、解除
の⽅法を知っていれば致命傷にはならない
みんなで知れば怖くない
<censored>
168/172

発⾒報告はたとえばこんなところに
⽇本MySQLユーザ会メーリングリスト
メーリングリストに登録するだけで⽇本MySQLユーザ会
員なんですよ知ってました︖
-
中の⼈も⾒てたり、真⾯目成分多め-
MySQL Casual Slack
ユーザー成分多め-
Qiita、ブログその他⾃⾝の情報発信場所
169/172

よろしくお
願いします
170/172

それでは、楽しい
PHP 7 + MySQL
5.7ライフを :)
171/172

Questions
and/or
Suggestions?
172/172

MySQL 5.7にやられないためにおぼえておいてほしいこと

More Related Content

MySQL 5.7にやられないためにおぼえておいてほしいこと