読者です 読者をやめる 読者になる 読者になる

なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

RDS MySQL5.6にSYSスキーマを組み込もうとして失敗する件

日本語情報、ぜんぜんないのね。

ていうか、MySQLのSYSスキーマ自体がそんなに普及していないのかな。


SYSスキーマは、パフォーマンスを見る上で重要なPerformance_Schemaの情報を見やすくしてくれるViewのセット。


ソースとセットアップ手順は、以下のURLの通り。
GitHub - mysql/mysql-sys: The MySQL sys schema


オンプレ環境ならセットアップでまったくトラブることもないし、MySQL Workbenchから1クリックでセットアップできちゃうのに、対象がRDSになった途端にひっかかる、というお話です。


なお、Oracleの最強ユーザ「SYS」とは違います。


どこでひっかかるのか。

スクリプトを覗き込んでみて、1件ずつ処理してみると、かなり冒頭にあるCREATE TRIGGERで引っかかってる。


エラーメッセージは、以下のとおり。

Error Code: 1419. You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

超訳)「SUPER権限が足りねえよ。それと、バイナリロギングが有効になってんよ」


はい。RDSではSUPER権限は取得できません。詰んだ?


メッセージの後半、カッコの中に注目。

MySQL「you *might* want to use the less safe log_bin_trust_function_creators variable」
超訳)「log_bin_trust_function_creatorsが使えるよ(ぼそっ)」


「使える」ってなんだよ!


このオプションは対応する log_bin_trust_function_creators システム変数を設定します。引数が指定されない場合、このオプションは変数を 1 に設定します。log_bin_trust_function_creators は、ストアドファンクションおよびトリガー作成に対して MySQL がどのように制限を適用するかに影響します。セクション20.7「ストアドプログラムのバイナリロギング」を参照してください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.4.4 バイナリログのオプションと変数


で、素直に参照すると

MySQL 5.6 でストアドファンクションを使用するための現在の条件は、次のように要約できます。これらの条件は、ストアドプロシージャーまたはイベントスケジューラのイベントには適用されず、バイナリロギングが有効でないかぎり適用されません。


ストアドファンクションを生成または変更するには、ユーザーは、通常必要になる CREATE ROUTINE 権限または ALTER ROUTINE 権限以外に、SUPER 権限が必要です。(関数定義の DEFINER 値によっては、バイナリロギングが有効かどうかにかかわらず SUPER が必要になる場合があります。セクション13.1.15「CREATE PROCEDURE および CREATE FUNCTION 構文」を参照してください。)


(中略)


関数作成に関する前述の条件 (SUPER 権限を持つ必要があることと、関数が決定的であるか、データを変更しないと宣言する必要があること) を緩和するには、log_bin_trust_function_creators グローバルシステム変数を 1 に設定します。デフォルトでこの変数には 0 の値が設定されていますが、次のように変更できます。
 mysql> SET GLOBAL log_bin_trust_function_creators = 1;
サーバーの起動時に --log-bin-trust-function-creators=1 オプションを使用することによって、この変数を設定することもできます。


バイナリロギングが有効でない場合、log_bin_trust_function_creators は適用されません。前述のように、関数定義の DEFINER 値が必要としないかぎり、関数の作成に SUPER は必要ありません。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.7 ストアドプログラムのバイナリロギング


キタコレ。


バイナリロギングを有効にしている場合、安全性を考慮して、ファンクションやトリガーの生成にSUPER権限も必要とするようにデフォルト設定されてます。


SUPER権限なしでも対応できるように緩和することができます。そのへんの制御は、以下のグローバルパラメータ(動的変更可)が絡んでます。


log_bin_trust_function_creators=0(デフォルト:無効) -> 1(許可)


しかし、GLOBALのサーバーパラメータを変更するには、SUPER権限が必要です!やっぱりSUPERなんです!



なので、ストアドファンクションやトリガーを作りたい人が勝手に「log_bin_trust_function_creators」を変更して作りこむことはできません。
グローバルのサーバーパラメータは、マネジメントコンソールまたはCLIから変更します。


ということで、SYSスキーマにかぎらず、ストアドファンクションやトリガーはすべてこの辺のお話が絡んできます。


ですので、このあたりはしっかり読み込んでおきましょう。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.6 ストアドプログラムおよびビューのアクセスコントロール
MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.7 ストアドプログラムのバイナリロギング


まとめ:RDSのMySQL5.6ににSYSスキーマを組み込むには

1.マネジメントコンソール or CLIで「log_bin_trust_function_creators=1(TRUE)」に修正
2.githubから取得したmysql-sysの中にある、「generate_sql_file.sh」を実行し、RDS用のsqlファイル「sys___inline.sql」を作る。

Generate a MySQL 5.6 SQL file for RDS:
 ./generate_sql_file.sh -v 56 -b -u CURRENT_USER

3.RDSに接続して、「sys_<sys_version>_<mysql_version_identifier>_inline.sql」ファイルを読み込ませて実行する。
mysql>SOURCE sys_<sys_version>_<mysql_version_identifier>_inline.sql

こんな感じで。


なお、手順1、2をやらずに3だけ実行すると、不完全なSYSスキーマが出来上がります。
トリガーとストアドファンクションが生成できず、それを使ったVIEWが作れず、全体として半分くらいしか生成されません。
無いよりはマシ、MySQL WorkbenchのPerformance Reportsも多少は動くようにはなりますが。。。


もっと簡単にSYSスキーマを使う方法。

MySQL5.7からデフォルトで組み込まれてます!

New! SysスキーマMySQLの詳細な稼働状況を簡単に確認可能
MySQL :: MySQL 5.7: 3倍高速


これからRDS-MySQLで構築する場合、5.7にする可能性が高いと思います。地雷(初期パスワード有効期限問題)も取り除かれたしねっ。


実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版