概要

AWS EC2サーバでmysqlコマンドでリストアをしようとしたら以下のエラーメッセージが表示されました。

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

本記事ではその原因と解決方法を記載します。

原因

このエラーは、ダンプしたユーザーとリストアを実行したユーザーが異なるために起きるエラーとのこと。アクセス権限が不足しているエラーです。
DEFINERを削除してどのユーザーでもリストアできるようにしたらOK、という記事があったのですが、DEFINERは見つからず。ちなみにDUMPはDBeaverというDBクライアントツールを使って取得しました。それをSCPコマンドでEC2サーバにコピーし、リストアを試みたところ、上述のエラーが出ました。

解決方法

DEFINERの記述がない場合は、ファイルの冒頭にある以下SET3行をコメントアウトしてあげたら良いとのこと。
セッション変数@@SESSION.SQL_LOG_BINの値を変更しようとしていますが（=バイナリログを無効にする操作らしい）、一般的にSUPER特権を持つユーザーにしか許可されていません。そのため、上述のエラーにSUPER privilege(s)何ちゃらと出ているんですね。

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

以下のように対応したら無事に解決しました。

-- SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
-- SET @@SESSION.SQL_LOG_BIN= 0;

ただし、MySQLではDUMP作る時から設定することもできます。それが以下のオプション。

 mysqldump -h XXX -u root -p mysql57-db > mysql57-db-dump.sql --set-gtid-purged=OFF

--set-gtid-purged=OFFにより、GTID（Global Transaction ID。トランザクションの一意性を識別するために使用される機能）情報の削除または無効化が行われます。
これにより、GTID情報が保持されるため、データベースを別のサーバーにリストアするときも簡単になります。