This annoying errors happened to our team few days ago when we were trying to migrate a site from one Linux server to another.
So on the current server we compressed all the website files into tar.gz archive and the dump the MySQL database using
mysqldump command. Ran
wget on the new server to download those to files (website archive and the database backup). Everything ran smoothly until we tried to import the SQL database from the command line, we were getting “Access denied; you need (at least one of) the SUPER privilege(s) for this operation”.
Why is this error happening?
If you ever experience this error as well, don’t worry too much. The error is just trying to tell us that when we tried to import the database the system notice that you don’t have enough permission to perform that operation.
Ways to solve
There are several ways that you can try to solve “Access denied; you need (at least one of) the SUPER privilege(s) for this operation” error when importing a database.
1. Remove DEFINER
Open your SQL dump file and find any statement that start with
DEFINER. You can try to either remove the
DEFINER = .. statement from the SQL dump file, or you can replace the user in the
DEFINER statement values with
2. Remove statements
If your SQL dump file doesn’t contains
DEFINER at all, you can try removing these lines from your dump file.
At the start:
-- SET @@SESSION.SQL_LOG_BIN= 0; -- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
At the end:
-- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
You actually can prevent those lines being printed to the output SQL file, simply add
--set-gtid-purged=OFF when you run the
mysqldump command. See example below:
mysqldump -u [username] -p [yourdatabasename] --set-gtid-purged=OFF > [outputfile].sql
3. Check username used in DEFINER
The issue we had were not solved using the two methods above. The issue was because the username used in the DEFINER command was different that the one we were using in the new server. DUH!
Our DEFINER statement looked like this:
/*!50013 DEFINER=`mynode`@`%` SQL SECURITY DEFINER */
We simply replace the username “mynode” with the correct MySQL username in the new server, which was “mynodes”.
Hope this tutorial help you in fixing the access denied issue when you’re trying to import a SQL file into MySQL.