How to import MySQL backup into Amazon Aurora MySQL

Amazon Aurora is a relational database management system (RDBMS) built for the cloud with full MySQL and PostgreSQL compatibility. The managed approach allows AWS to offer high scalability, serverless options, and a 99.99% SLA.

However, the managed approach also means users do not have full access to manage the DB and perform certain actions usually allowed under self-hosted options. One of these limitations is highlighted during the data import stage.

A standard .sql file contains certain SET commands that even the super admin does not have permission to execute on the Amazon Aurora MySQL.

Attempts to import any .sql file raise the below error:

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

Luckily, this error can be fixed easily. We must remove certain SET commands that allow us to import the data to Amazon Aurora MySQL successfully.

Open the .sql file in your preferred editor. At the start of the file, we need to remove the three SET commands below.

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';

Below, I've highlighted the section of the file that you can remove.

Next, scroll to the end of the file, and remove the below line

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

Save the file and attempt to import the data again. This time the import should be successful :)