MySQL 5.7の「罠」に狙われてもやられないための Advent Calendar 2015

Participant1People

Subscriber70People

Category：Database & DWH

Creator：

「罠」でおなじみMySQL 5.7が2015年10月に晴れてGAとなりました
25日間「罠」について一人寂しくつづるAdvent Calendarです。
大半が @yoku0825 のブログへのリンクになるのは想定された事態です。
1日1つずつ、Advent Calendarのコメント欄(最大127文字らしい)に罠をつづっていきます。(c) @yoku0825
たぶん127文字は超えません。

Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
29	30	@RKajiyama Unix系OSではMySQL 5.7.6からmysql_install_dbが廃止されmysqld --initializeで初期化処理 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama mysqld --initializeでの初期時rootユーザのパスワードが自動設定、password_expiredフラグが立つのでログイン時に変更必要。面倒？mysqld --initialize-insecureならrootパスワード無し Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQLのインストールが最も楽なのはWindows用のZipで、解凍したらすぐ起動できたのが、MySQL 5.7からはUnix系と同じく初期化が必要に。GA直後のリファレンスマニュアルには記載されておらず、GAから一ヶ月後にNoteに小さく追記された罠 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama コミュニティ版のダウンロードページにWindows用インストーラは32bit版しかないけど、インストーラ自体が32bit版なだけでMySQLサーババイナリは64bit版もインストール可能。英語で注意書きあるけど分かりにくすぎる。5.6でも5.5でも同様 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama rpmでインストールする際、互換性の問題を解決するパッケージがMySQL-shared-compatからmysql-community-libs-compatに変更に。これまたGA直後のマニュアルには記載されておらず、GAから一ヶ月後に追記された罠 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use
@RKajiyama ところがこの互換性解決パッケージの名称の問題はrpmをダウンロードしてインストールした場合のみ発生。MySQL YumレポジトリまたはMySQL SLESレポジトリを使うとyumなど各コマンドが互換性の問題を解消してくれる Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama Red Hat Enterprise Linux 7/Oracle Linux 7でMySQL Yumレポジトリを使うと、OSネイティブのレポジトリからのakonadi-mysqlとocsinventoryは互換性の問題で共存できないので注意 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama Mac OS上でDMGでインストールすると、5.7.8以降ではStartup Iteamsではなくlaunchdとして登録される。MySQL側の事情ではなくOS X 10.10 (Yosemite)での変更に伴うもので5.6.26も5.5.45以降も同様 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 5.7.9以降ではソースからのコンパイル時にパフォーマンススキーマが必須に。個別のinstrumentation(性能統計取得ポイント)を含まないようにオプション設定してコンパイルにすることは可能。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama mysql.userテーブルのパスワードハッシュを格納する列名`password`が廃止され、`authentication_string`に。この列の値は各認証プラグインにて認証に利用する値を格納する際にも利用される。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQL 5.7ではデフォルトでvalidate_password(パスワード検証プラグイン)が有効となり各種設定がされているため、8文字以上、大文字小文字を含む、数字および記号が最低一文字が求められる。例えば MySQL 5.7 でもOK Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQL 4.0やそれより古いバージョンではパスワードハッシュに古いアルゴリズムを使用。MySQL 5.6まではサーバ変数のold_passwordsを設定して古いアルゴリズムも利用できたがMySQL 5.7では利用不可に Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use
@RKajiyama SET PASSWORD文が非推奨、将来バージョンで削除の方向。ALTER USER user IDENTIFIED BY 'auth_string';推奨。5.7.6以降はPASSWORD()関数を不使用でも与えられた文字列から自動的にハッシュ演算 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama SHOW VARIABLESとSHOW STATUSの内容はINFORMATION_SCHEMAのVARIABLES,STATUSテーブルからperformance_schemaに移行。I_Sの各テーブルアクセスはエラー、将来バージョンで削除の方向 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 上記に伴い5.6と同様にI_Sのテーブルを利用可能にするオプションshow_compatibility_56が5.7.6で登場、ところが同時に非推奨に。5.7.7でデフォルトオンになったが5.7.8でオフになった。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 罠対策のリンク先の記事が罠になるという斬新な展開に。ブログからリンクされているバグレポの通り、レプリケーションのためにshow_compatibility_56がデフォルトのオフでも問題無くレプリケーション可能に Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama バイナリログのデフォルトのフォーマットがSQL文格納のSTATEMENTから変更後の行イメージ格納のROWに。ログサイズ肥大化注意。主キーが必須などいくつか制約あるが binlog_row_imageをminimalにすることも要検討 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama ディスクフルなどバイナリログが書込不可の場合binlog_error_actionがデフォルトABORT_SERVERなので落ちる。スレーブとの不整合回避等が目的。5.7.5まではbinlogging_impossible_mode Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama ログのタイムスタンプがデフォルトではUTCに。従来通り日本時間などシステム日付の時刻を利用するlog_timestamps=SYSTEMへの変更を推奨 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use
@RKajiyama シャットダウン時にバッファプール内のデータの表領域IDとページIDをダンプするinnodb_buffer_pool_dump_at_shutdownが有効に。 innodb_buffer_pool_dump_pctでダンプする割合を制御可能。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 上記のダンプを起動時にバッファプールにロードするinnodb_buffer_pool_load_at_startupも有効に。上記とあわせ起動停止時の時間や負荷に要注意。ダンプおよびロードの進捗はSHOWコマンドで確認可能 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama innodb_strict_modeが有効になり、sql_modeにもSTRICT_TRANS_TABLESなどがデフォルトで設定されより厳格な仕様となったため、これまでのSQLがエラーになることも Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama LOAD DATAやSELECT ... INTO OUTFILEのアクセス先ディレクトリを指定するsecure-file-privのデフォルトが空文字(制限無し)から、インストール方法やOSによって様々な値に。マニュアル要確認 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 1) JSON関数名：LabsのJSON UDF時代はjson_()、MySQL JSON時代はjsn_()、5.7.8 DMR RC2でjson_*()に戻る 2) mysqlpumpはmysqldumpのタイプミスじゃないよ、でもちょい未完成だよ Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQL 5.7の「罠」といえばこれ！default_password_lifetimeのデフォルト値360。パスワード変更後360日を過ぎると新規接続がエラーになる。はずが、MySQL 5.7.11で再変更されてまさかの無期限に戻った。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	26

@RKajiyama
Unix系OSではMySQL 5.7.6からmysql_install_dbが廃止されmysqld --initializeで初期化処理
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
mysqld --initializeでの初期時rootユーザのパスワードが自動設定、password_expiredフラグが立つのでログイン時に変更必要。面倒？mysqld --initialize-insecureならrootパスワード無し
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQLのインストールが最も楽なのはWindows用のZipで、解凍したらすぐ起動できたのが、MySQL 5.7からはUnix系と同じく初期化が必要に。GA直後のリファレンスマニュアルには記載されておらず、GAから一ヶ月後にNoteに小さく追記された罠
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
コミュニティ版のダウンロードページにWindows用インストーラは32bit版しかないけど、インストーラ自体が32bit版なだけでMySQLサーババイナリは64bit版もインストール可能。英語で注意書きあるけど分かりにくすぎる。5.6でも5.5でも同様
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
rpmでインストールする際、互換性の問題を解決するパッケージがMySQL-shared-compatからmysql-community-libs-compatに変更に。これまたGA直後のマニュアルには記載されておらず、GAから一ヶ月後に追記された罠
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
ところがこの互換性解決パッケージの名称の問題はrpmをダウンロードしてインストールした場合のみ発生。MySQL YumレポジトリまたはMySQL SLESレポジトリを使うとyumなど各コマンドが互換性の問題を解消してくれる
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
Red Hat Enterprise Linux 7/Oracle Linux 7でMySQL Yumレポジトリを使うと、OSネイティブのレポジトリからのakonadi-mysqlとocsinventoryは互換性の問題で共存できないので注意
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
Mac OS上でDMGでインストールすると、5.7.8以降ではStartup Iteamsではなくlaunchdとして登録される。MySQL側の事情ではなくOS X 10.10 (Yosemite)での変更に伴うもので5.6.26も5.5.45以降も同様
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
5.7.9以降ではソースからのコンパイル時にパフォーマンススキーマが必須に。個別のinstrumentation(性能統計取得ポイント)を含まないようにオプション設定してコンパイルにすることは可能。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
mysql.userテーブルのパスワードハッシュを格納する列名`password`が廃止され、`authentication_string`に。この列の値は各認証プラグインにて認証に利用する値を格納する際にも利用される。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQL 5.7ではデフォルトでvalidate_password(パスワード検証プラグイン)が有効となり各種設定がされているため、8文字以上、大文字小文字を含む、数字および記号が最低一文字が求められる。例えば MySQL 5.7 でもOK
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQL 4.0やそれより古いバージョンではパスワードハッシュに古いアルゴリズムを使用。MySQL 5.6まではサーバ変数のold_passwordsを設定して古いアルゴリズムも利用できたがMySQL 5.7では利用不可に
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
SET PASSWORD文が非推奨、将来バージョンで削除の方向。ALTER USER user IDENTIFIED BY 'auth_string';推奨。5.7.6以降はPASSWORD()関数を不使用でも与えられた文字列から自動的にハッシュ演算
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
SHOW VARIABLESとSHOW STATUSの内容はINFORMATION_SCHEMAの*VARIABLES,*STATUSテーブルからperformance_schemaに移行。I_Sの各テーブルアクセスはエラー、将来バージョンで削除の方向
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
上記に伴い5.6と同様にI_Sのテーブルを利用可能にするオプションshow_compatibility_56が5.7.6で登場、ところが同時に非推奨に。5.7.7でデフォルトオンになったが5.7.8でオフになった。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
罠対策のリンク先の記事が罠になるという斬新な展開に。ブログからリンクされているバグレポの通り、レプリケーションのためにshow_compatibility_56がデフォルトのオフでも問題無くレプリケーション可能に
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
バイナリログのデフォルトのフォーマットがSQL文格納のSTATEMENTから変更後の行イメージ格納のROWに。ログサイズ肥大化注意。主キーが必須などいくつか制約あるが binlog_row_imageをminimalにすることも要検討
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
ディスクフルなどバイナリログが書込不可の場合binlog_error_actionがデフォルトABORT_SERVERなので落ちる。スレーブとの不整合回避等が目的。5.7.5まではbinlogging_impossible_mode
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
ログのタイムスタンプがデフォルトではUTCに。従来通り日本時間などシステム日付の時刻を利用するlog_timestamps=SYSTEMへの変更を推奨
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
シャットダウン時にバッファプール内のデータの表領域IDとページIDをダンプするinnodb_buffer_pool_dump_at_shutdownが有効に。 innodb_buffer_pool_dump_pctでダンプする割合を制御可能。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
上記のダンプを起動時にバッファプールにロードするinnodb_buffer_pool_load_at_startupも有効に。上記とあわせ起動停止時の時間や負荷に要注意。ダンプおよびロードの進捗はSHOWコマンドで確認可能
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
innodb_strict_modeが有効になり、sql_modeにもSTRICT_TRANS_TABLESなどがデフォルトで設定されより厳格な仕様となったため、これまでのSQLがエラーになることも
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
LOAD DATAやSELECT ... INTO OUTFILEのアクセス先ディレクトリを指定するsecure-file-privのデフォルトが空文字(制限無し)から、インストール方法やOSによって様々な値に。マニュアル要確認
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
1) JSON関数名：LabsのJSON UDF時代はjson_*()、MySQL JSON時代はjsn_*()、5.7.8 DMR RC2でjson_*()に戻る 2) mysqlpumpはmysqldumpのタイプミスじゃないよ、でもちょい未完成だよ
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQL 5.7の「罠」といえばこれ！default_password_lifetimeのデフォルト値360。パスワード変更後360日を過ぎると新規接続がエラーになる。はずが、MySQL 5.7.11で再変更されてまさかの無期限に戻った。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use

Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
29	30	@RKajiyama Unix系OSではMySQL 5.7.6からmysql_install_dbが廃止されmysqld --initializeで初期化処理 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama mysqld --initializeでの初期時rootユーザのパスワードが自動設定、password_expiredフラグが立つのでログイン時に変更必要。面倒？mysqld --initialize-insecureならrootパスワード無し Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQLのインストールが最も楽なのはWindows用のZipで、解凍したらすぐ起動できたのが、MySQL 5.7からはUnix系と同じく初期化が必要に。GA直後のリファレンスマニュアルには記載されておらず、GAから一ヶ月後にNoteに小さく追記された罠 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama コミュニティ版のダウンロードページにWindows用インストーラは32bit版しかないけど、インストーラ自体が32bit版なだけでMySQLサーババイナリは64bit版もインストール可能。英語で注意書きあるけど分かりにくすぎる。5.6でも5.5でも同様 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama rpmでインストールする際、互換性の問題を解決するパッケージがMySQL-shared-compatからmysql-community-libs-compatに変更に。これまたGA直後のマニュアルには記載されておらず、GAから一ヶ月後に追記された罠 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use
@RKajiyama ところがこの互換性解決パッケージの名称の問題はrpmをダウンロードしてインストールした場合のみ発生。MySQL YumレポジトリまたはMySQL SLESレポジトリを使うとyumなど各コマンドが互換性の問題を解消してくれる Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama Red Hat Enterprise Linux 7/Oracle Linux 7でMySQL Yumレポジトリを使うと、OSネイティブのレポジトリからのakonadi-mysqlとocsinventoryは互換性の問題で共存できないので注意 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama Mac OS上でDMGでインストールすると、5.7.8以降ではStartup Iteamsではなくlaunchdとして登録される。MySQL側の事情ではなくOS X 10.10 (Yosemite)での変更に伴うもので5.6.26も5.5.45以降も同様 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 5.7.9以降ではソースからのコンパイル時にパフォーマンススキーマが必須に。個別のinstrumentation(性能統計取得ポイント)を含まないようにオプション設定してコンパイルにすることは可能。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama mysql.userテーブルのパスワードハッシュを格納する列名`password`が廃止され、`authentication_string`に。この列の値は各認証プラグインにて認証に利用する値を格納する際にも利用される。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQL 5.7ではデフォルトでvalidate_password(パスワード検証プラグイン)が有効となり各種設定がされているため、8文字以上、大文字小文字を含む、数字および記号が最低一文字が求められる。例えば MySQL 5.7 でもOK Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQL 4.0やそれより古いバージョンではパスワードハッシュに古いアルゴリズムを使用。MySQL 5.6まではサーバ変数のold_passwordsを設定して古いアルゴリズムも利用できたがMySQL 5.7では利用不可に Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use
@RKajiyama SET PASSWORD文が非推奨、将来バージョンで削除の方向。ALTER USER user IDENTIFIED BY 'auth_string';推奨。5.7.6以降はPASSWORD()関数を不使用でも与えられた文字列から自動的にハッシュ演算 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama SHOW VARIABLESとSHOW STATUSの内容はINFORMATION_SCHEMAのVARIABLES,STATUSテーブルからperformance_schemaに移行。I_Sの各テーブルアクセスはエラー、将来バージョンで削除の方向 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 上記に伴い5.6と同様にI_Sのテーブルを利用可能にするオプションshow_compatibility_56が5.7.6で登場、ところが同時に非推奨に。5.7.7でデフォルトオンになったが5.7.8でオフになった。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 罠対策のリンク先の記事が罠になるという斬新な展開に。ブログからリンクされているバグレポの通り、レプリケーションのためにshow_compatibility_56がデフォルトのオフでも問題無くレプリケーション可能に Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama バイナリログのデフォルトのフォーマットがSQL文格納のSTATEMENTから変更後の行イメージ格納のROWに。ログサイズ肥大化注意。主キーが必須などいくつか制約あるが binlog_row_imageをminimalにすることも要検討 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama ディスクフルなどバイナリログが書込不可の場合binlog_error_actionがデフォルトABORT_SERVERなので落ちる。スレーブとの不整合回避等が目的。5.7.5まではbinlogging_impossible_mode Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama ログのタイムスタンプがデフォルトではUTCに。従来通り日本時間などシステム日付の時刻を利用するlog_timestamps=SYSTEMへの変更を推奨 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use
@RKajiyama シャットダウン時にバッファプール内のデータの表領域IDとページIDをダンプするinnodb_buffer_pool_dump_at_shutdownが有効に。 innodb_buffer_pool_dump_pctでダンプする割合を制御可能。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 上記のダンプを起動時にバッファプールにロードするinnodb_buffer_pool_load_at_startupも有効に。上記とあわせ起動停止時の時間や負荷に要注意。ダンプおよびロードの進捗はSHOWコマンドで確認可能 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama innodb_strict_modeが有効になり、sql_modeにもSTRICT_TRANS_TABLESなどがデフォルトで設定されより厳格な仕様となったため、これまでのSQLがエラーになることも Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama LOAD DATAやSELECT ... INTO OUTFILEのアクセス先ディレクトリを指定するsecure-file-privのデフォルトが空文字(制限無し)から、インストール方法やOSによって様々な値に。マニュアル要確認 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama 1) JSON関数名：LabsのJSON UDF時代はjson_()、MySQL JSON時代はjsn_()、5.7.8 DMR RC2でjson_*()に戻る 2) mysqlpumpはmysqldumpのタイプミスじゃないよ、でもちょい未完成だよ Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	@RKajiyama MySQL 5.7の「罠」といえばこれ！default_password_lifetimeのデフォルト値360。パスワード変更後360日を過ぎると新規接続がエラーになる。はずが、MySQL 5.7.11で再変更されてまさかの無期限に戻った。 Overwrite Title URL You can post either your article on Qiita or your blog post. About reserved posting If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day. About posting period Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.) When you participate, we will assume that you have agreed to the terms of use	26

@RKajiyama
Unix系OSではMySQL 5.7.6からmysql_install_dbが廃止されmysqld --initializeで初期化処理
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
mysqld --initializeでの初期時rootユーザのパスワードが自動設定、password_expiredフラグが立つのでログイン時に変更必要。面倒？mysqld --initialize-insecureならrootパスワード無し
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQLのインストールが最も楽なのはWindows用のZipで、解凍したらすぐ起動できたのが、MySQL 5.7からはUnix系と同じく初期化が必要に。GA直後のリファレンスマニュアルには記載されておらず、GAから一ヶ月後にNoteに小さく追記された罠
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
コミュニティ版のダウンロードページにWindows用インストーラは32bit版しかないけど、インストーラ自体が32bit版なだけでMySQLサーババイナリは64bit版もインストール可能。英語で注意書きあるけど分かりにくすぎる。5.6でも5.5でも同様
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
rpmでインストールする際、互換性の問題を解決するパッケージがMySQL-shared-compatからmysql-community-libs-compatに変更に。これまたGA直後のマニュアルには記載されておらず、GAから一ヶ月後に追記された罠
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
ところがこの互換性解決パッケージの名称の問題はrpmをダウンロードしてインストールした場合のみ発生。MySQL YumレポジトリまたはMySQL SLESレポジトリを使うとyumなど各コマンドが互換性の問題を解消してくれる
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
Red Hat Enterprise Linux 7/Oracle Linux 7でMySQL Yumレポジトリを使うと、OSネイティブのレポジトリからのakonadi-mysqlとocsinventoryは互換性の問題で共存できないので注意
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
Mac OS上でDMGでインストールすると、5.7.8以降ではStartup Iteamsではなくlaunchdとして登録される。MySQL側の事情ではなくOS X 10.10 (Yosemite)での変更に伴うもので5.6.26も5.5.45以降も同様
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
5.7.9以降ではソースからのコンパイル時にパフォーマンススキーマが必須に。個別のinstrumentation(性能統計取得ポイント)を含まないようにオプション設定してコンパイルにすることは可能。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
mysql.userテーブルのパスワードハッシュを格納する列名`password`が廃止され、`authentication_string`に。この列の値は各認証プラグインにて認証に利用する値を格納する際にも利用される。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQL 5.7ではデフォルトでvalidate_password(パスワード検証プラグイン)が有効となり各種設定がされているため、8文字以上、大文字小文字を含む、数字および記号が最低一文字が求められる。例えば MySQL 5.7 でもOK
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQL 4.0やそれより古いバージョンではパスワードハッシュに古いアルゴリズムを使用。MySQL 5.6まではサーバ変数のold_passwordsを設定して古いアルゴリズムも利用できたがMySQL 5.7では利用不可に
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
SET PASSWORD文が非推奨、将来バージョンで削除の方向。ALTER USER user IDENTIFIED BY 'auth_string';推奨。5.7.6以降はPASSWORD()関数を不使用でも与えられた文字列から自動的にハッシュ演算
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
SHOW VARIABLESとSHOW STATUSの内容はINFORMATION_SCHEMAの*VARIABLES,*STATUSテーブルからperformance_schemaに移行。I_Sの各テーブルアクセスはエラー、将来バージョンで削除の方向
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
上記に伴い5.6と同様にI_Sのテーブルを利用可能にするオプションshow_compatibility_56が5.7.6で登場、ところが同時に非推奨に。5.7.7でデフォルトオンになったが5.7.8でオフになった。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
罠対策のリンク先の記事が罠になるという斬新な展開に。ブログからリンクされているバグレポの通り、レプリケーションのためにshow_compatibility_56がデフォルトのオフでも問題無くレプリケーション可能に
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
バイナリログのデフォルトのフォーマットがSQL文格納のSTATEMENTから変更後の行イメージ格納のROWに。ログサイズ肥大化注意。主キーが必須などいくつか制約あるが binlog_row_imageをminimalにすることも要検討
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
ディスクフルなどバイナリログが書込不可の場合binlog_error_actionがデフォルトABORT_SERVERなので落ちる。スレーブとの不整合回避等が目的。5.7.5まではbinlogging_impossible_mode
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
ログのタイムスタンプがデフォルトではUTCに。従来通り日本時間などシステム日付の時刻を利用するlog_timestamps=SYSTEMへの変更を推奨
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
シャットダウン時にバッファプール内のデータの表領域IDとページIDをダンプするinnodb_buffer_pool_dump_at_shutdownが有効に。 innodb_buffer_pool_dump_pctでダンプする割合を制御可能。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
上記のダンプを起動時にバッファプールにロードするinnodb_buffer_pool_load_at_startupも有効に。上記とあわせ起動停止時の時間や負荷に要注意。ダンプおよびロードの進捗はSHOWコマンドで確認可能
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
innodb_strict_modeが有効になり、sql_modeにもSTRICT_TRANS_TABLESなどがデフォルトで設定されより厳格な仕様となったため、これまでのSQLがエラーになることも
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
LOAD DATAやSELECT ... INTO OUTFILEのアクセス先ディレクトリを指定するsecure-file-privのデフォルトが空文字(制限無し)から、インストール方法やOSによって様々な値に。マニュアル要確認
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
1) JSON関数名：LabsのJSON UDF時代はjson_*()、MySQL JSON時代はjsn_*()、5.7.8 DMR RC2でjson_*()に戻る 2) mysqlpumpはmysqldumpのタイプミスじゃないよ、でもちょい未完成だよ
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use
@RKajiyama
MySQL 5.7の「罠」といえばこれ！default_password_lifetimeのデフォルト値360。パスワード変更後360日を過ぎると新規接続がエラーになる。はずが、MySQL 5.7.11で再変更されてまさかの無期限に戻った。
Overwrite
Title
URL
You can post either your article on Qiita or your blog post.
About reserved posting
If you register a secret article by the day before the same day, it will be automatically published around 7:00 on the same day.
About posting period
Only articles submitted after November 1 of the year can be registered. (Secret articles can be registered anytime articles are posted.)
When you participate, we will assume that you have agreed to the terms of use