We frequently run into issues when restoring databases that were created using the mysqldump command. One of them is shown below.
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
ERROR 1227 (42000) at line 14573: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
Due to the issues listed above, the command below that assists in restoring the databases will not succeed.
mysql -h RDS.EndPoint.rds.amazonaws.com -P 3306 -u myUser -p MyDB < MyDumpFile.sql
The lines in the dump files that are responsible for this problem are listed below.
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 ‘+’*/ ”;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
The SQL_LOG_BIN variable controls whether logging to the binary log is enabled for the current session. And the global value of the GTID_PURGED system variable ( @@GLOBAL. GTID_PURGED) is a GTID set consisting of the GTIDs of all the transactions that have been committed on the server but do not exist in any binary log file on the server. gtid_purged is a subset of GTID_EXECUTED.
You’ll require SUPER, SYSTEM VARIABLES ADMIN, or SESSION VARIABLES ADMIN privileges to prevent this issue. You can still restore your database because the force command will disregard these errors and continue with the subsequent stages.
mysql -f -h RDS.EndPoint.rds.amazonaws.com -P 3306 -u myUser -p MyDB < MyDumpFile.sql
Hope you find this article helpful.
Big Data and SQL 
One comment