MySQL Error: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

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 CURRENT_USER.

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:

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.

Node35 Editorial Team

We are a bunch of people who are still continue to learn Linux servers. Only high passion keeps pushing us to learn everything.