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
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.
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.
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.
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?
More information on the matter can be found here: