GA

2015/08/22

#yapcasia でMySQL 5.7の罠についてLTしてきました

YAPC::Asia Tokyo 2015 お疲れ様でした!

2日目のライトニングトークでしゃべらせていただきました。




ネタ的には 発掘するたび書き溜めてきたブログ記事 から 笑いが取れそうなものを 大事そうなもののみをピックアップして紹介した感じです。

知らないと致命傷、でも知ってれば予防できる(はず)
MySQL 5.7で不幸になる人が1人でも少なくなってくれることを願っています。


さて、今年のYAPC::Asiaはメイントラックもトークを応募していたのですが見事に落選したので、1日目は完全にリラックスして過ごしました。LTの採否、当日になるまでわかんないのか大変だなーとか、他人事だったんですが、

1日目のLT見るじゃないですか。

面白いじゃないですか。

俺もしゃべりたくなるじゃないですか。

なったんですよ!!1


が、翌朝になってLTの応募ページをたどってみると






しかしその後復活していたので




申し込み!




その1時間後にはもう締め切られていた! (あるいは、情けで応募させてくれたのかも。。)
とてもありがたいことに採択してもらえましたが、こういう悪ノリが出来るのもYAPCだからなのかなって思います。
(わたしはChiba.pm出身なので、たぶん結構ノリが近くて)

YAPCで「1年ぶりー」って挨拶した人もいっぱいいて、なんかこう同窓会っぽくて楽しいね、なんて話をしてたりもしました。


ありがとうYAPC::Asia 2015!!


【2015/09/07 10:40】
結構前ですが、sakaikさん からツッコミをもらっていたのを思い出しました。。

MySQL を好きな人が、MySQLのちょっと変なところを、少しばかり強調しておもしろおかしく伝えている面はあるので、その変な部分だけが一人歩きして多くの人に理解されてしまうことを危惧はしています
10回目の最後のYAPCに2回目の参加をしてきた - sakaikの日々雑感~(T)編

うぐぅ。。気を付けます。。

2015/08/12

An idea for using MySQL 5.7's generated column like CHECK constraint

This is translation for my early post in Japanese.

As of MySQL 5.7.6, generated column was introduced as 5.7's new feature.
I found that generated column could behave like CHECK constraint.
Let's start :)


mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

First, there's the bad designed table which has DATE-string in its VARCHAR column.


mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Second, add VIRTUAL generated column `check_val` which calculates DATETIME from `val`'s DATE-string.


mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

After that, you can see INSERT statement with incorrect DATE-string fails because of `check_val`'s CAST function raises warning and strict sql_mode can't allow that warning.


mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

This way depends on sql_mode is strict or not.
Then I tried another way using IF function, which returns NULL when check condition is false, with NOT NULL constraint.


mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, 'Tokyo');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, 'Osaka');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, 'Nara');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
+-----+-------+-----------+
2 rows in set (0.00 sec)

I seem this approach is better.
NOT NULL constraint doesn't depend on sql_mode.

This generated column feature can be introduced into tables which have already been filled data.


mysql57> SELECT * FROM t3; -- Invalid data has already been inserted.
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) NOT NULL; -- Add VIRTUAL generated column.
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+-----------+
| num | val   | check_val |
+-----+-------+-----------+
|   1 | Tokyo |         1 |
|   2 | Osaka |         1 |
|   3 | Nara  |         0 |
+-----+-------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, 'Kyoto');
ERROR 1048 (23000): Column 'check_val' cannot be null

With VIRTUAL generated column, the data which is already stored don't be affected by fake CHECK constraint.

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.
http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns

VIRTUAL generated `check_val` doesn't calculate at the time of ALTER TABLE, thus this case doesn't affect stored data but new data are under constraint.


mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | Tokyo |
|   2 | Osaka |
|   3 | Nara  |
+-----+-------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF(val IN ('Tokyo', 'Osaka'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

The other hand, with STORED generated column, all exist data are calculated and checked during ALTER TABLE.
If the table already has invalid (for fake CHECK constraint) data, ALTER TABLE fails.

This is a stage of idea, is not practice in production environment.
But this idea maybe satisfy some case of "OMG, doesn't MySQL have CHECK constraint!?" situation.

2015/08/07

MySQL 5.7のgenerated columnでついにCHECK制約っぽいことを実現できる

generated columnそのものの説明はこのへんに。
日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

というわけでやってみましょう。


mysql57> CREATE TABLE t1 (num int primary key, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (1, '2015-08-06');
Query OK, 1 row affected (0.01 sec)

mysql57> SELECT * FROM t1;
+-----+------------+
| num | val        |
+-----+------------+
|   1 | 2015-08-06 |
+-----+------------+
1 row in set (0.00 sec)

こんなテーブルがあったとするじゃろ?


mysql57> ALTER TABLE t1 ADD check_val datetime AS (CAST(val AS datetime));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

valの中身をdatetimeにキャストする関数を食わせたgenerated columnを作るじゃろ?


mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-31');
ERROR 1292 (22007): Incorrect datetime value: '2015-09-31'

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
+-----+------------+---------------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (2, '2015-09-30');
Query OK, 1 row affected (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
+-----+------------+---------------------+
2 rows in set (0.00 sec)

なんと、日付として不正な値を入れると、キャストに失敗してエラーが返るんじゃよ!


mysql57> SET sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql57> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql57> INSERT INTO t1 (num, val) VALUES (3, '2015-11-31');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql57> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2015-11-31' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql57> SELECT * FROM t1;
+-----+------------+---------------------+
| num | val        | check_val           |
+-----+------------+---------------------+
|   1 | 2015-08-06 | 2015-08-06 00:00:00 |
|   2 | 2015-09-30 | 2015-09-30 00:00:00 |
|   3 | 2015-11-31 | NULL                |
+-----+------------+---------------------+
3 rows in set, 1 warning (0.00 sec)

もちろん、sql_modeがSTRICT_TRANS_TABLESかSTRICT_ALL_TABLESでないと、キャストの失敗を丸めちゃうので台無しになる。まさかもう非strict_modeとか選ばない…よね?
datetime型の場合はNOT NULL制約をつけても0000-00-00 00:00:00で丸めちゃうからアレだけど、IFをかませてtinyintとかにすると


mysql57> CREATE TABLE t2 (num int primary key, val varchar(32), check_val tinyint AS (IF(val IN ('東京', '大阪'), 1, NULL)) NOT NULL) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (1, '東京');
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (2, '大阪');
Query OK, 1 row affected (0.01 sec)

mysql57> INSERT INTO t2 (num, val) VALUES (3, '奈良');
ERROR 1048 (23000): Column 'check_val' cannot be null

mysql57> SELECT * FROM t2;
+-----+--------+-----------+
| num | val    | check_val |
+-----+--------+-----------+
|   1 | 東京   |         1 |
|   2 | 大阪   |         1 |
+-----+--------+-----------+
2 rows in set (0.00 sec)

おおおこれ結構良くない? このIF関数で無理矢理NULLを返す方式だと、NOT NULL制約の問題なので非strict_modeでもちゃんとエラーになってくれる。わーい(死)

ちなみに、既に格納されている値に対して後からこの似非CHECK制約を追加する場合、


mysql57> SELECT * FROM t3;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | 東京   |
|   2 | 大阪   |
|   3 | 奈良   |
+-----+--------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+--------+-----------+
| num | val    | check_val |
+-----+--------+-----------+
|   1 | 東京   |         1 |
|   2 | 大阪   |         1 |
|   3 | 奈良   |         0 |
+-----+--------+-----------+
3 rows in set (0.00 sec)

mysql57> INSERT INTO t3 (num, val) VALUES (4, '京都');
ERROR 1048 (23000): Column 'check_val' cannot be null

generated columnの種類を指定しない場合、暗黙のデフォルトとしてVIRTUALになるので、
「VIRTUALなgenerated columnの場合は、今までのものは無視されて、新たに更新するものに対して有効」(VIRTUALなgenerated columnはALTER TABLEの時には何もせず、SELECTされた時に都度関数が走るから)になり、


mysql57> ALTER TABLE t3 DROP check_val;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> SELECT * FROM t3;
+-----+--------+
| num | val    |
+-----+--------+
|   1 | 東京   |
|   2 | 大阪   |
|   3 | 奈良   |
+-----+--------+
3 rows in set (0.00 sec)

mysql57> ALTER TABLE t3 ADD check_val tinyint AS (IF (val IN ('東京', '大阪'), 1, NULL)) STORED NOT NULL;
ERROR 1048 (23000): Column 'check_val' cannot be null

「STOREDなgenerated columnの場合は、今までのもので似非CHECK制約を通らない行がある場合、そもそもALTER TABLEが通らない」(STOREDなgenerated columnはALTER TABLEの時に関数を通って計算され、それがデータに格納されるから)になる。


おおお面白いんじゃないこれ?


【2015/08/12 10:52】
I translated this post into English, See An idea for using MySQL 5.7's generated column like CHECK constraint

2015/08/04

MySQL 5.7.8以降のrpmパッケージではvalidate_passwordプラグインがデフォルトで有効になっている

というよりは、今までが「デフォルトで入っているつもりが入っていなかった」ので、そのバグをFIX、ということらしい。

The validate_password plugin was not installed by RPM packages for platorms using systemd or SysV-style initialization scripts. (Bug #18438833)
MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (2015-08-03)


やったね! 初心者殺しの罠が一つ増えたよ! orz

validate_passwordプラグインについては5.6で導入された機能なので、日本語マニュアルが既にある。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.1.2.6 パスワード検証プラグイン

これが有効になっているということはどういうことかというと、

$ /etc/init.d/mysqld start
Initializing MySQL database:  2015-08-03T15:33:57.341022Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
                                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

$ grep password /var/log/mysqld.log
2015-08-03T15:33:57.971473Z 1 [Warning] A temporary password is generated for root@localhost: =w?e&SnQf0aK
2015-08-03T15:34:03.509853Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)

$ mysql -p
Enter password:

mysql> SET PASSWORD= '';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

まあ、こういうことですよね。。


mysql> SHOW GLOBAL 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 (0.00 sec)

デフォルトでは8文字以上、英大文字小文字数字記号の4種類を含む必要がある。
この条件を満たしたパスワードに変更するまで、初期セットアップ状態だとパスワードがEXPIREされた状態なので、SHOWコマンドもSETコマンドも効かない。ははは。

そんな都合の良い文字列パッと思い付かねーよ! って場合は、もとのパスワードをそのまま入れてしまえばいい。なんと通る。
それか、"Do_you_love_MySQL57?"もこの条件を満たすので使ってもいいですよ :)


mysql> SET PASSWORD= '=w?e&SnQf0aK';
Query OK, 0 rows affected (0.01 sec)

mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.01 sec)

あとはSET GLOBALで強度を弱めるなり、UNINSTALL PLUGINするなりご自由に…という感じ。

初期のランダムパスワードが/var/log/mysqld.logにあることと合わせて、インストールでくじけさせてくれる新機能でした。

MySQL 5.7.8 透過InnoDBページ圧縮のサイズ計り比べ…の前に、取り敢えず有効化するところまで

今までのInnoDB圧縮(ROW_FORMAT= Compress)は InnoDBテーブル圧縮 、新しいやつは InnoDBページ圧縮 としてドキュメントに載っている。

今までのInnoDBテーブル圧縮はzlibのみの対応で、圧縮後のデータの詰め直しなども全部InnoDB側で対応していた。そのデータの詰め直しをファイルシステム側に任せることで、CPUバウンドだった圧縮処理を軽く/圧縮効率を良くしよう、というアレだと認識している。
詳しくはこちら。 InnoDB Transparent PageIO Compression | MySQL Server Blog


CentOS 6.6(2.6.32-504.30.3.el6.x86_64)のext4ではsupport not availableと言われるのが

$ less /var/log/mysqld.log
..
2015-07-22T09:23:24.769459Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.8-rc-log) starting as process 403 ...
2015-07-22T09:23:24.774797Z 0 [Note] InnoDB: PUNCH HOLE support not available
..


CentOS 7.1(3.10.0-229.el7.x86_64)のxfsだとちゃんと有効になっているぽい。

$ less /var/log/mysqld.log
..
2015-07-22T09:21:44.321951Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.8-rc) starting as process 3355 ...
2015-07-22T09:21:44.326762Z 0 [Note] InnoDB: PUNCH HOLE support available
..


$ mysql -sse "show tables" tpcc | while read table ; do
> mysql -e "ALTER TABLE $table Compression= 'zlib'"
> done

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: customer
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-07-22 18:45:09
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: COMPRESS="zlib"
        Comment:
..

Create_options: COMPRESS="zlib"になった。



20WH突っ込んでみたところ。


無圧縮。

$ ll -h
total 1.8G
-rw-r----- 1 mysql mysql 9.2K Jul 22 19:34 customer.frm
-rw-r----- 1 mysql mysql 392M Jul 22 19:33 customer.ibd
-rw-r----- 1 mysql mysql   65 Jul 22 19:24 db.opt
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:34 district.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:32 district.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:34 history.frm
-rw-r----- 1 mysql mysql  72M Jul 22 19:34 history.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 19:24 item.frm
-rw-r----- 1 mysql mysql  17M Jul 22 19:24 item.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 19:34 new_orders.frm
-rw-r----- 1 mysql mysql  13M Jul 22 19:33 new_orders.ibd
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:34 order_line.frm
-rw-r----- 1 mysql mysql 572M Jul 22 19:34 order_line.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:34 orders.frm
-rw-r----- 1 mysql mysql  48M Jul 22 19:33 orders.ibd
-rw-r----- 1 mysql mysql 9.0K Jul 22 19:34 stock.frm
-rw-r----- 1 mysql mysql 692M Jul 22 19:33 stock.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:24 warehouse.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:32 warehouse.ibd


InnoDBページ圧縮(zlib)。

$ ll -h
total 1.3G
-rw-r----- 1 mysql mysql 9.2K Jul 22 19:15 customer.frm
-rw-r----- 1 mysql mysql 392M Jul 22 19:14 customer.ibd
-rw-r----- 1 mysql mysql   65 Jul 22 18:42 db.opt
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:15 district.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:05 district.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:15 history.frm
-rw-r----- 1 mysql mysql  72M Jul 22 19:15 history.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 18:45 item.frm
-rw-r----- 1 mysql mysql  17M Jul 22 18:56 item.ibd
-rw-r----- 1 mysql mysql 8.5K Jul 22 19:15 new_orders.frm
-rw-r----- 1 mysql mysql  13M Jul 22 19:05 new_orders.ibd
-rw-r----- 1 mysql mysql 8.8K Jul 22 19:15 order_line.frm
-rw-r----- 1 mysql mysql 564M Jul 22 19:15 order_line.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 19:15 orders.frm
-rw-r----- 1 mysql mysql  48M Jul 22 19:14 orders.ibd
-rw-r----- 1 mysql mysql 9.0K Jul 22 19:15 stock.frm
-rw-r----- 1 mysql mysql 692M Jul 22 19:14 stock.ibd
-rw-r----- 1 mysql mysql 8.7K Jul 22 18:45 warehouse.frm
-rw-r----- 1 mysql mysql  96K Jul 22 19:04 warehouse.ibd


すいません性能とか圧縮率とかまだ真面目に調べられてますん><

MySQL 5.7.8のリリースノートななめ読み(変更になったパラメーターとか)

概要だけでベンチマークとかしてないです。あとななめ読みなので俺の興味のないところ(空間インデックスとか)はかっ飛ばしてます。

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (Not yet released)


* innodb_adaptive_hash_index_parts
  * Adaptive Hashのラッチを分割できるらしい。暗黙のデフォルトは8 (以前は1固定)
    * Adaptive Hashが有効だと性能が劣化する書き込みバウンドのケースで効くか?

innodb_log_checksum_algorithm
  * innodb_checksum_algorithm(や、旧innodb_checksums)がInnoDBテーブルスペースファイルのチェックサムだったのに対して、こっちはInnoDBログファイルに対するチェックサム。
    * 今までログのチェックサムは取られていなかったのだとしたら、チェックサムを取るってだけで性能が落ちそう。
  * 暗黙のデフォルトはinnodb(= ソフトウェア計算チェックサム)なので、書き込みバウンドだと性能が劣化するかも。
    * innodb_checksum_algorithmと同じく、crc32がいいと思われる。

* innodb_flush_sync
  * 今まではチェックポイント中もinnodb_io_capacityの値に従ってI/O量を制御していたけれど、チェックポイント中ってことはトランザクション止まってるのでinnodb_io_capacityの値を無視して全力でI/O動かせばいいじゃーん、っていうパラメーターっぽい。
  * 暗黙のデフォルトはON、チェックポイント中は全力でI/Oする。

* innodb_purge_threads, innodb_page_cleaners
  * 1から4に増えてる。

* /etc/init.d/mysqld stopのタイムアウトが600秒に
  * 今までは60秒

* super_read_only
  * Super_privを持ってるユーザーでもread_onlyを強制できるオプション。
  * 日々の覚書: Percona Server 5.6.21にsuper_read_onlyが来ました

* disabled_storage_engines
  * そう! MyISAM利用禁止とかできるんですよ奥さん!
    * ちなみに「今既にあるMyISAMテーブル」には何の影響も及ぼさない(= 読み込みも書き込みも可能。TRUNCATEもできた)

mysql> SELECT @@disabled_storage_engines;
+----------------------------+
| @@disabled_storage_engines |
+----------------------------+
| MyISAM                     |
+----------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE d1.t1 (num int) Engine= MyISAM;
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

* innodb_undo_tablespaces
  * 上限が126 => 95へ
  * とはいえそんなに大きくしなくても、innodb_undo_log_truncate に必要な2で特に困ってない。
    * 日々の覚書: MySQL 5.7.5のInnoDB undo log truncationを試してみた


なんかもうちょっとあったような。。

MySQL 5.7.8をrpmで入れると、初回のservice mysqld startのタイミングでSSL証明書も作ってくれる

日々の覚書: MySQL 5.7.6からSSL周りのセットアップが扱いやすくなった のmysql_ssl_rsa_setup がそのまま/etc/init.d/mysqld の中で呼ばれるようになった…というだけの話なんですが、理屈を知らないとエラーログが矛盾してそうに思えることになっていたので解説がてら。


# rpm -i mysql-community-server-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-common-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-client-5.7.8-0.3.rc.el5.x86_64.rpm mysql-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-libs-5.7.8-0.3.rc.el5.x86_64.rpm mysql-community-libs-compat-5.7.8-0.3.rc.el5.x86_64.rpm
..

# less /var/log/mysqld.log
2015-07-15T07:44:47.363530Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-07-15T07:44:47.504281Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-07-15T07:44:47.582258Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5dd4d949-2ac5-11e5-8a55-0242ac11000a.
2015-07-15T07:44:47.586384Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2015-07-15T07:44:47.586799Z 0 [Warning] Failed to setup SSL
2015-07-15T07:44:47.586825Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2015-07-15T07:44:47.587546Z 1 [Warning] A temporary password is generated for root@localhost: 0J:ra.dZgrmF
150715 16:44:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-07-15T07:44:52.939037Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-07-15T07:44:52.940030Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.8-rc) starting as process 413 ...
..
2015-07-15T07:44:53.156280Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2015-07-15T07:44:53.156620Z 0 [Warning] CA certificate ca.pem is self signed.
2015-07-15T07:44:53.156875Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2015-07-15T07:44:53.156942Z 0 [Note] IPv6 is available.
2015-07-15T07:44:53.156952Z 0 [Note]   - '::' resolves to '::';
2015-07-15T07:44:53.156957Z 0 [Note] Server socket created on IP: '::'.
2015-07-15T07:44:53.158530Z 0 [Note] InnoDB: Buffer pool(s) load completed at 150715 16:44:53
2015-07-15T07:44:53.173689Z 0 [Note] Event Scheduler: Loaded 0 events
2015-07-15T07:44:53.174035Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.8-rc'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

* "[Warning] Failed to setup SSL"と言いながら
* "[Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them." と言って
* "[Warning] CA certificate ca.pem is self signed." とまとめる。


というのは、1つ目の"Failed to setup SSL"はmysqld --initializeのタイミングで呼ばれているので、この時点ではSSL証明書がなくてmysqld起動時のSSL有効化に失敗する。
/etc/init.d/mysqldの中では、 mysqld --initialize => mysql_ssl_rsa_setup => mysqld_safe の順番で呼んでいるので、
2つ目の"Found ca.pem"はmysqld_safeから起動されたmysqldが、その前に走っていたmysql_ssl_rsa_setupで作成されたSSL証明書を拾ってSSLを有効化にして起動している。
3つ目は読んで字のごとく。

5.7.5以降 mysqldの--ssl がデフォルトで有効になっているので、クライアント側でca.pemを使ってSSL接続するようにしさえすればそれだけでOK。

MySQL 5.7.8で追加されたrequire_secure_transportでSSL接続を強要できる

my.cnfの[mysqld]セクションに require_secure_transport と書いておくと、SSL接続を強要できるらしい。

MySQL :: MySQL 5.7 Reference Manual :: 5.1.4 Server System Variables


# service mysqld restart --require_secure_transport
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

$ mysql -uroot

mysql> SELECT @@require_secure_transport;
+----------------------------+
| @@require_secure_transport |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

( ´-`).oO(余談だけど、/etc/init.d/mysqld [re]startの後ろにロングオプションつけると、mysqld_safeにそのまま渡してくれるんですよこれ豆知識。


mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.8-rc, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.8-rc MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 min 28 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.068
--------------

UNIXソケット接続の場合はSSLもへったくれもないのでこれは制限されない。


$ mysql -uroot --protocol=tcp

mysql> STATUS
--------------
mysql  Ver 14.14 Distrib 5.7.8-rc, for Linux (x86_64) using  EditLine wrapper

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.8-rc MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 5 min 54 sec

Threads: 1  Questions: 22  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 100  Queries per second avg: 0.062
--------------

MySQL 5.7から、コマンドラインクライアントもデフォルトで--sslを使おうとするので、特に何も指定しなくても勝手にSSLでつなぎに行く。
これを明示的に無効にしてやる(または、5.6以前のコマンドラインクライアントは--sslをつけない限りSSLを使おうとしないので、そっちで接続しようとすると)

$ mysql -uroot --protocol=tcp --skip-ssl
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.

おお、これは良い感じだ。

CREATE USERGRANTステートメントのREQUIRE SSLがユーザー単位なのに対し、こっちはサーバー全体で一括指定。SET GLOBALによるオンラインの変更も可能。

…まさかこれ暗黙のデフォルトで1にしたりとかしないですよねー? (・∀・)ゞ

MySQL 5.7.8からInnoDBのgenerated columnは実体を取らずにインデックスを作れるようになった

MySQL 5.7.6から入ったgenerated column、STOREDで作るとデータ領域に計算後の結果が格納されてインデックスも貼れるようになるというシロモノだったのが、5.7.8ではVIRTUALで作るとデータ領域には計算後の結果が格納されないけど *インデックスは計算後の値を使って貼れるようになった* 

日々の覚書: MySQL 5.7.6のgenerated columnは関数インデックスの夢を見るか

InnoDB: InnoDB now supports secondary indexes on virtual generated columns. A secondary index on a virtual generated column stores the column's generated values within the records of the index. Such indexes can be scanned and searched more efficiently than virtual generated columns, which are computed “on the fly” when rows are read.

MySQL :: MySQL 5.7 Release Notes :: Changes in MySQL 5.7.8 (Not yet released)


ただしInnoDB限定でMyISAMとかはダメ。まあ、いまさら、MyISAMさんにそんな変更入ってもしょうがないので全然かまわないけども。


まずテスト用のデータを作って入れておく。

$ perl -M"Digest::MD5 qw/md5_hex/" -e 'for (my $n= 1; $n <= 100000; $n++) {printf("%d\t%s\n", $n, md5_hex($n));}' > /var/lib/mysql-files/md5

mysql> CREATE TABLE t1 (num int unsigned PRIMARY KEY, md5 varchar(32) NOT NULL, KEY (md5));
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/md5' INTO TABLE t1;
Query OK, 100000 rows affected (0.89 sec)
Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0

手抜き :)
この状態でinnodb_rubyを使ってテーブルスペースの様子を見てみると、


mysql> SELECT tbl.name, idx.name, idx.index_id FROM INNODB_SYS_INDEXES AS idx INNER JOIN INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';
+-------+---------+----------+
| name  | name    | index_id |
+-------+---------+----------+
| d1/t1 | PRIMARY |       87 |
| d1/t1 | md5     |       88 |
+-------+---------+----------+
2 rows in set (0.00 sec)



もちろんこう、PRIMARY(InnoDBなので行のデータそのものはここに格納されいてる)とmd5というセカンダリーキーが詰められる。

ここに、まずは5.7.7でもできたようにSTOREDな生成列を作ってみる。


mysql> ALTER TABLE t1 ADD stored_sha256 varchar(64) AS (sha2(num, 256)) STORED;
Query OK, 100000 rows affected (1.33 sec)
Records: 100000  Duplicates: 0  Warnings: 0



STOREDな生成列は演算した結果をデータ領域に書き込むので、Index 90(=md5)のページは変わらず406ページに対してIndex 89(=PRIMARY)は367 => 802ページに増えている。
そしてこれにインデックスを貼ると


mysql> ALTER TABLE t1 ADD KEY (stored_sha256);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT tbl.name, idx.name, idx.index_id FROM information_schema.INNODB_SYS_INDEXES AS idx INNER JOIN information_schema.INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';
+-------+---------------+----------+
| name  | name          | index_id |
+-------+---------------+----------+
| d1/t1 | PRIMARY       |       89 |
| d1/t1 | md5           |       90 |
| d1/t1 | stored_sha256 |       91 |
+-------+---------------+----------+
3 rows in set (0.00 sec)



Index 89(=PRIMARY)にはページ数も配置も変化がなく、Index 91(=stored_sha256)が足される。470ページ。

これに対してVIRTUALな生成列は


mysql> ALTER TABLE t1 ADD virtual_sha256 varchar(64) AS (sha2(num, 256)) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0



カラムを追加しただけでは全く変化がなく、


mysql> ALTER TABLE t1 ADD KEY (virtual_sha256);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT tbl.name, idx.name, idx.index_id FROM information_schema.INNODB_SYS_INDEXES AS idx INNER JOIN information_schema.INNODB_SYS_TABLES AS tbl USING(table_id) WHERE tbl.name= 'd1/t1';
+-------+----------------+----------+
| name  | name           | index_id |
+-------+----------------+----------+
| d1/t1 | PRIMARY        |       89 |
| d1/t1 | md5            |       90 |
| d1/t1 | stored_sha256  |       91 |
| d1/t1 | virtual_sha256 |       92 |
+-------+----------------+----------+
4 rows in set (0.00 sec)



ちょっと画面の上切れてる(´・ω・`)
ながら、Index 89(=PRIMARY)はgenerated columnを作る前から変わりがなく(=容量を食わずに) Index 92(=virtual_sha256)が追加されている。


もちろんこれインデックスとして既に固定化されているので、

mysql> EXPLAIN SELECT virtual_sha256 FROM t1 WHERE virtual_sha256 LIKE '8da731305e2397659f7ebc6ef225ddfd3b%';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | virtual_sha256 | virtual_sha256 | 67      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

covering indexが効く。


うわあ、なんか良さそうな気がしてきた。楽しみ。
これで、JSON型と合わせて無理矢理SQL de NoSQLとかできますねわーい。

mysqldumpじゃないよ、mysqlpumpだよ in MySQL 5.7.8 (mysqldumpとの違い編)

TL;DR

MySQL 5.7.8には、mysqlpumpなるmysqldumpの後継バックアップクライアントが同梱されている。インデックスの遅延ロードや進捗の出力、パラレルでのダンプなど魅力的な拡張機能が入っている。

ただし、mysqlpumpの方は「全テーブルがInnoDB」「master_info_repository= TABLE」「relay_log_info_repository= TABLE」「gtid_mode= ONの場合にはMySQL 5.7.5以降であること(OFFの場合はこの制約は入らない)」「パラレルでバックアップする場合は更新を自分で止めておかなくてはならない」であることを前提に作られているため、それを満たさない場合は mysql40dump のようなラッパーを何かしら作る必要があります(レプリケーションの情報はテーブルに保存されていてそれはダンプに含まれるから、SHOW SLAVE STATUSとかSHOW MASTER STATUSの情報は吐いてくれない)


mysqldumpとの機能の違いはこんな感じ。mysqlpumpだけの機能は別の記事で。

mysqldumpにあって、mysqlpumpにないオプション。


* --disable-keys
* --order-by-primary
  * MyISAMはもうおなかいっぱいですねわかります。

* --flush-privileges
  * --usersを使えってことかな。

* --ignore-table
  * --exclude-*があるからね。

* --tab
* --fields-*
* --lines-terminated-by
  * SELECT INTO OUTFILEを使ったダンプ出力はサポートしません、と。

* --force
* --ignore-error
  * エラー握りつぶしてバックアップなんて論外ってことですね。

* --delete-master-logs
* --dump-slave
* --master-data
* --include-master-host-port
* --set-gtid-purged
  * 今まで通りのやり方でレプリケーションスレーブを作るような情報は一切取れなくなっている。
  * MySQL 5.6.2からのmaster_info_repository= TABLE, relay_log_info_repository= TABLE, MySQL 5.7.5からのmysql.gtid_executeあたりからレプリケーション関連の情報は取ってくる。
    * ↑これを満たしていない場合、SHOW SLAVE STATUSとかは自分でラッパー書くなりして取ってこないとダメ。

* --opt
* --secure-auth
* --quote-names
* --dump-date
* --lock-tables
* --no-autocommit
* --no-set-names
* --quick
* --verbose


* --flush-logs
  * これはわざわざ移植する必要はないと思われたのであろう。たぶん。

* --lock-all-tables
  * FLUSH TABLES WITH READ LOCKなんて取る必要ないよ! mysql.slave_master_infoもmysql.slave_relay_log_infoもmysql.gtid_executedも全部InnoDBなんだから、--single-transactionで保護すればロックなんて要らないんだ! …ってことなんだろう、たぶん。
  * ↑この理屈で(だと思う)--single-transactionの場合もFLUSH TABLES WITH READ LOCKは取らない。


mysqldumpにもmysqlpumpにもあるけど、mysqlpumpではデフォルトで有効になっているオプション。

* --events
* --routines
* --triggers
  * ( ´-`).oO(mysqldumpでもデフォルト有効になってくれていいんですよ。。


mysqldumpとmysqlpumpで意味は同じだけど名前が変わっているオプション。

* --log-error => --log-error-file
* --no-data => --skip-dump-rows

mysqldumpじゃないよ、mysqlpumpだよ in MySQL 5.7.8 (新機能編)

TL;DR

MySQL 5.7.8には、mysqlpumpなるmysqldumpの後継バックアップクライアントが同梱されている。インデックスの遅延ロードや進捗の出力、パラレルでのダンプなど魅力的な拡張機能が入っている。

ただし、mysqlpumpの方は「全テーブルがInnoDB」「master_info_repository= TABLE」「relay_log_info_repository= TABLE」「gtid_mode= ONの場合にはMySQL 5.7.5以降であること(OFFの場合はこの制約は入らない)」「パラレルでバックアップする場合は更新を自分で止めておかなくてはならない」であることを前提に作られているため、それを満たさない場合は mysql40dump のようなラッパーを何かしら作る必要があります(レプリケーションの情報はテーブルに保存されていてそれはダンプに含まれるから、SHOW SLAVE STATUSとかSHOW MASTER STATUSの情報は吐いてくれない)


mysqlpumpだけの機能はこんな感じ。mysqldumpとの機能の違いについては別の記事

* --defer-table-indexes
  * MySQL 5.6における大量データロード時の考慮点 - SH2の日記 で語られているように、ダンプファイルのリストアのようなケースでは 「バルクINSERT時はインデックスをつけない」「INSERTが終わってからALTER TABLEでつける」のが最速になる。これをやってくれる。
  * Percona Serverのmysqldumpに入っている --innodb-optimize-keys と同じような動作。

$ mysqlpump --defer-table-indexes d1 t1
-- Dump created by MySQL dump utility, version: 5.7.8-rc, Linux (x86_64)
-- Dump start time: Wed Jul 15 18:35:46 2015
-- Server version: 5.7.8
..
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `d1` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `d1`.`t1` (
`num` int(11) NOT NULL,
`val` varchar(32) DEFAULT NULL,
PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
INSERT INTO `d1`.`t1` VALUES (1,"one"),(2,"two");
USE `d1`;
ALTER TABLE `d1`.`t1` ADD KEY `val` (`val`);
..

↑では明示的につけてるけど、暗黙のデフォルトON。何も考えなくてもmysqlpumpからのリストアはmysqldumpからのリストアに比べて速くなるはず。すてき。


* --compress-output
  * mysqldumpは必ず平文でSQLが出力されるので、ストリームで圧縮したい時はパイプでつなげてリダイレクトするしかなかったが、これを付けるとmysqlpumpの標準出力そのものが既に圧縮された状態で出てくる。
  * 引数は"zlib"もしくは"lz4"で圧縮形式を指定するぽい。

$ mysqlpump d1 t1 > cleartext.sql
Dump progress: 0/1 tables, 2/2 rows
Dump completed in 410 milliseconds

$ mysqlpump --compress-output=lz4 d1 t1 > lz4.sql
Dump progress: 0/1 tables, 2/2 rows
Dump completed in 374 milliseconds

$ mysqlpump --compress-output=zlib d1 t1 > zlib.sql
Dump progress: 0/1 tables, 2/2 rows
Dump completed in 367 milliseconds

$ ll *.sql
-rw-r--r-- 1 root root 1161 Jul 16 11:32 cleartext.sql
-rw-r--r-- 1 root root  705 Jul 16 11:33 lz4.sql
-rw-r--r-- 1 root root  510 Jul 16 11:33 zlib.sql

( ´-`).oO(.sqlってつけたのは間違いだな我ながら。


* --exclude-*
  * databases, events, routines, tables, triggers, usersをそれぞれ除外指定できる。除外対象を複数指定したい時はコンマ区切りで渡す。すてき。
  * --ignore-tableはテーブル単位でしか指定できないし、データベース名からの完全修飾が必要で、複数テーブル指定する時はオプションから複数指定しないといけなかったのに比べると、かなり柔軟になった。


* --include-*
  * --databasesや--tablesと同じようなアレになるんだろうか。コード読みたい。

* --default-parallelism
  * バックアップの並列度を指定できる。暗黙のデフォルトは2で2スレッドで並列にバックアップを取る。ただし、並列でやると *ダンプファイルの中身が交差するよ* って書いてあって、つまり別々に叩いたSELECTをそれぞれ標準出力に独立して書き出す感じ。
  * この値に1より大きい値を指定した時(つまりパラレルでダンプする時)、START TRANSACTIONはそれぞれのスレッドが別々に叩くので、--single-transactionは意味を持たない。
    * よって、更新トラフィックのある状態で--default-parallelism > 1だとバックアップとしてのデータの整合性は保証されない
      5.7.11で修正され、--default-parallelism > 1でも --single-transaction が使えるようになった。ビバ。
      * https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_default-parallelism
    * STOP SLAVEできるバックアップ用のスレーブからパラレルで取るか、STOP SLAVEしたくないなら--default-parallelism = 1 && --single-transactionで取るか(ただしその場合も *今までの* レプリケーション再開に必要そうなSHOW SLAVE STATUSの情報とかは一切取ってくれない、master_info_repository= TABLE && relay_log_info_repository= TABLE && mysql.gtid_executedに異存している。

* --parallel-schemas
  * スキーマ単位でパラレルにダンプを取ってくれる。
  * 数値を渡すとその数でパラレルにダンプ、コンマ区切りのスキーマのリストを渡すと 「そのスキーマだけ特別扱いしてスレッドを起動、リストに記載されてないスキーマは1つのスレッドでダンプ」だそうな。

* --skip-definer
  * VIEWやSTORED PROCEDUREのDEFINER句をかっ飛ばしてくれる。
  * 本番のテーブルからVIEWの定義をダンプしてきたけど、リストア先はmysqlスキーマまでコピーしてないからDEFINERがいないって言われて怒られることがなくなる。
  * "mysqldump definer" とかぐぐると、消すためのワンライナーを紹介してる記事があったりするので、それなりに需要があるんだろうとおもう。

* --users
  * 前にオレオレパッチで対応していた、mysql.userの情報とかをCREATE USER & GRANTのステートメント形式で出力してくれるオプション。--triggersや--eventsと同じ類。
    * MySQLのバージョンアップをmysqldumpでやると面倒なので—grantsをつけてみる | GMOメディア エンジニアブログ

$ mysqlpump --users d1 t1
..
CREATE USER 'yoku0825'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'yoku0825'@'%';
CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
..

おおお、いいね。


* --watch-progress
  * ダンプの進み具合を *標準エラー出力* に吐く。 *暗黙のデフォルトON* 。cronに仕込んで標準エラー出力を拾うようにしていると進捗だけごりごり拾う羽目に遭うので注意。

$ mysqlpump tpcc > test
Dump progress: 0/9 tables, 10/594278 rows
Dump progress: 6/9 tables, 543510/594278 rows
Dump completed in 1525 milliseconds

無効化するには--skip-watch-progressなんだけど、

$ mysqlpump --skip-watch-progress tpcc > test
Dump completed in 1558 milliseconds


:(;゙゚'ω゚'): おいskip-watch-progressしてもDump completedは標準エラー出力に吐くのかよ。。


長くなってきたので取り敢えずここまで。
mysqldumpとの違いについては別のエントリーで。