DirectAdmin Knowledge Base and Support

DirectAdmin is a web panel for a simple administration of both virtual and dedicated servers. DirectAdmin is faster, safe and more powerful than any other CP. And we know how to customize and support it.

MySQL: Access denied; you need the SUPER privilege for this operation

| 14:41:43 08.03.2018

Should you get an access denied error when trying to import a dump into MySQL via Directadmin or phpMyAdmin, saying that you need at least one of the SUPER privilege(s) for this operation, here you can learn how to fix it.

The full text of the error is shown below:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Use case

We faced the error on a Directadmin server running on Google Compute Engine (GCE), and MySQL 5.6.x server was running as a separate instance of Cloud SQL (MySQL & PostgreSQL Relational Database Service from Google):

-- MySQL dump 10.13  Distrib 5.6.38, for linux-glibc2.12 (x86_64)
--
-- Host: 35.189.xx.xx    Database: database_name
-- ------------------------------------------------------
-- Server version	5.6.36-google-log

And every time when we created a MySQL dump in Directadmin or phpMyAdmin it contained the following lines:

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

All the attempts to import such a dump later failed due to the lack of permissions. Even when we connected to the remote MySQL server as root user which has all possible permissions.

Bypass to import SQL

If your case is the same with ours, and you need to import dumps you should first clear them, i.e. remove lines:

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

and

SET @@GLOBAL.GTID_PURGED='d2298455-xxxx-xxxx-xxxx-42010a980029:1-3413775';

and

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN

from a SQL file. The import should succeed since then.

Fixing MySQL Dump process

Here we will describe how to fix it and make SQL-dumps to not include those instructions, which break import process. For this you should do at least the following.

Update /etc/my.cnf (or whichever file is that on your Linux box) and populate it with the following instructions:

[mysqldump]
set-gtid-purged=OFF

You don't need to restart anything after it, even if you hold your MySQL server locally. After this update MySQL dumps should be safe for future importing.

Fixing Directadmin and Custombuild

Though the fix mentioned earlier is sufficient, for any cases when you don't have /etc/my.cnf you can update directadmin.conf with the following instruction:

extra_mysqldump_options=--set-gtid-purged=OFF

run this code to update directadmin.conf:

echo "extra_mysqldump_options=--set-gtid-purged=OFF" >> /usr/local/directadmin/conf/directadmin.conf
service directadmin restart

That's it.

Wanna more?

Links

More information on the matter can be found here:

Should you need an assistance with the guide please contact us.
About Us
We are a team of professionals, and specialize in installation, configuring and managing of remote virtual and dedicated servers powered by Linux/Unix-like OS with DirectAdmin. We support various sets of software, including web-servers Apache, Nginx; internet domain name servers Bind, PowerDNS; mail-servers with POP3, IMAP and SMTP, FTP-servers, etc. After years of working through the most complex server challenges our team has gathered valuable expirience and universal solutions suitable for everyday tasks. We are here to lend you a helping hand and take care of your servers in order to let you have enough time to do more of what you love.




All of the information and data on this site is for informational purposes only and is provided for the convenience of the user.
Powered by: Amiro.CMS - Free edition