エラーメッセージと発生箇所

MySQL上でdumpファイルを読み込もうとした時に発生。

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

このエラーが起きた背景

MySQLサーバー上に複数のdatabaseが存在し、そのうちの一つのdatabaseがうっかりdropされてしまった。Staging環境だったのでデータについては神経質にならずに一日前に取得していたmysqldumpから復旧しようとしたが、なぜか取得したmysqldumpを流そうとしたら表題のエラーが生じた。

流そうとしたmysqldumpの取得はdatabase別に行われていた。そのため、消えたdatabaseにだけ、dumpファイルからimportを行おうとした。
mysqlのユーザーは、普段このdatabaseの読み書きを行うアプリケーションで使用しているユーザーを使用した。

ちなみに、エラーが出たときには気づいていなかったが、mysqldumpを取得した時には下記の警告メッセージが出ていた。

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

エラーが起きた原因

mysqldumpを実行した時に--set-gtid-purged=OFFオプションをつけていなかった。これによって取得されたdumpファイルの中にGTIDに関するコードが入り込んでおり、部分的にdatabaseを更新する際に表題のエラーが生じた。

暫定対処

何をするかの概要

とりあえずエラーを解消してデータのリストアをするために、mysqldumpからGTIDに関するコードを消してから、インポートを行う。

GTIDに関する記述を消す

dumpファイルの上の方の記述を消す

dumpファイルの上の方にある下記の行を全て消す。コメントは別に消さなくても良い。

-- SETの3行を全部消す。

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

--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='';

dumpファイルの下の方の記述を消す

-- この行を消す
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

dumpファイルを流し込む

エラーが生じなくなるので、データを流し込む。もしここで流し込めなかったら、この記事で説明するのとは別の原因でエラーが発生している可能性がある。

恒久対処

何をするかの概要

部分的にmysqldumpを取得する場合は、--set-gtid-purged=OFFオプションをつけるようにする。

--set-gtid-purged=OFF の効果

--set-gtid-purged=OFFオプションをつけた時とつけない時でdumpファイルの差分を出すと、「暫定対処」で消した部分にだけ差分が出ていることが分かる。

上の方。

下の方。（一番下のはただのコメント）

参考

• https://support.acquia.com/hc/en-us/articles/360056635953-Troubleshooting-ERROR-1227-42000-at-line-18-Access-denied-you-need-at-least-one-of-the-SUPER-privilege-s-for-this-operation-