How to Fix ERROR 1227 (42000) in MySQL for Database Restoration

If you are trying to restore a MySQL backup which was downloaded from a managed service like DigitalOcean, you probably came across this error.

ERROR 1227 (42000) at line 18: Access denied;
you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN
or SESSION_VARIABLES ADMIN privileges) for this operation

From the error, it may seem like we're lacking some access privileges to execute our restorations, but actually it has nothing to do with that.

Here, let me share 2 ways on how you can solve this.

EASY: If you can do a new backup

Ideally, your database is still online and intact and you can perform a new backup.

In this case, it's as simple as adding a new flag --set-gtid-purged=OFF while doing your new backup to disable the global transaction identifier which was causing the error.

A sample command would look like this:

mysqldump -h your-db-host-here.db.ondigitalocean.com -P 25060 -u doadmin -p \
--ssl-ca=./certs/ca.crt --set-gtid-purged=OFF source-db > dump.sql

NOT-SO-BAD: If you are stuck with your current backup

In this case, there is no choice but to edit your backup dump to remove some lines that are causing the problems. Remember to do a backup of your backup before you proceed!

Search for these lines and remove them.

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

Now try to restore again. Did it work?

Both methods didn't work

Maybe your issue is different from mine. So if both didn't work for you, check out this thread on Stackoverflow and hopefully you will find other useful answers there.