Error When Migration MySQL Database to Amazon RDS
Overview
¶
When migrating a MySQL database I received the following error: "Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation". This article provides the resolution to this issue.
It is important to note the approach I was using to migrate the database. The source database was hosted on Host Gator, and I used their Export Database feature (found in the "phpMyAdmin" section) to generate a SQL script file. When I ran the SQL script file against the target database, which was hosted on Amazon RDS, I received the error noted above. Using Amazon's Database Migration Service was not an option, as I wanted to migrate the database without taking down the website that depended on it. This requires turning on binary logging for the source database, which was not possible in this case because of the hosting arrangement with Host Gator.
Solution
¶
The problem lies in the fact that the
CREATE
statements included a
DEFINER
clause (see below) for a user which didn't exist in the target database.
CREATE DEFINER=`myuser`@`localhost` PROCEDURE ...
The solution was to remove all the
DEFINER
clauses. This is easily accomplished via a global search and replace of "
CREATE DEFINER=`myuser`@`localhost`
" with "
CREATE
".