Fix ERROR 1227: Access denied; you need the SUPER privilege When Importing MySQL Dump

The Error

You're importing a .sql dump file and MySQL stops cold:

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The import dies mid-way. Your database is either partially restored or completely empty. What makes this error annoying is that it's not obvious which statement inside the dump file pulled the trigger.

Why This Happens

The dump file contains statements that MySQL won't run without SUPER privilege — and your user doesn't have it. Three usual suspects:

• DEFINER clauses — stored procedures, triggers, and views exported with DEFINER=original_user@%`` baked in
• SET statements — things like SET @@SESSION.SQL_LOG_BIN = 0 or any SET GLOBAL variable
• GTID-related statements — SET @@GLOBAL.GTID_PURGED shows up whenever the source server had GTID replication enabled

AWS RDS, Google Cloud SQL, and most shared hosting providers deliberately block SUPER — even for admin-level users. But this isn't just a cloud problem. It bites on local setups too whenever you're importing as a restricted user.

Find the Problem Line First

Don't guess. Pipe the import errors to a file and read exactly what MySQL is choking on:

mysql -u youruser -p yourdatabase < dump.sql 2> import_errors.log
cat import_errors.log

Or scan the dump directly for the known offenders:

grep -n 'DEFINER' dump.sql | head -20
grep -n 'SQL_LOG_BIN' dump.sql
grep -n 'GTID_PURGED' dump.sql

The line numbers from grep -n tell you exactly where to look in a 50,000-line dump instead of scrolling blindly.

Fix 1: Strip DEFINER from the Dump (Most Common Fix)

This resolves roughly 90% of cases. Use sed to scrub all DEFINER clauses before importing:

sed -i 's/DEFINER=[^*]*\*/\*/g' dump.sql

Prefer keeping the original untouched? Write to a clean copy instead:

sed 's/DEFINER=[^*]*\*/\*/g' dump.sql > dump_clean.sql
mysql -u youruser -p yourdatabase < dump_clean.sql

macOS ships with BSD sed, which needs an extension argument for in-place edits:

sed -i '' 's/DEFINER=[^*]*\*/\*/g' dump.sql

Once the DEFINER is stripped, MySQL simply uses the importing user's identity for those objects — no SUPER needed.

Fix 2: Remove the GTID_PURGED Statement

Check if the error points to a line containing SET @@GLOBAL.GTID_PURGED:

grep -n 'GTID_PURGED' dump.sql

It looks something like this:

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '4b8e2c1a-1234-5678-abcd-ef0123456789:1-100';

Delete that line, then retry the import. If you control how the dump was created, the cleaner fix is to exclude GTID statements at export time:

mysqldump --set-gtid-purged=OFF -u root -p yourdatabase > dump.sql

Fix 3: Disable the SQL_LOG_BIN Line

Some dumps include SET @@SESSION.SQL_LOG_BIN= 0; near the top — this also trips the SUPER check. Comment it out with sed:

sed -i 's/SET @@SESSION.SQL_LOG_BIN= 0/-- SET @@SESSION.SQL_LOG_BIN= 0/g' dump.sql

Or just open the dump in a text editor and delete the line manually. For small files that's often faster.

Fix 4: Export More Carefully (If You Control the Source)

If you're generating the dump yourself, use flags that skip the privilege-heavy statements from the start:

mysqldump \
  --set-gtid-purged=OFF \
  --no-tablespaces \
  --skip-definer \
  -u root -p yourdatabase > dump_clean.sql

--skip-definer is available from MySQL 8.0.32 and MariaDB 10.x onward. On anything older, fall back to the sed approach in Fix 1.

Fix 5: Grant SUPER Temporarily (Local MySQL Only)

Running MySQL on your own machine with root access? Grant SUPER to the importing user, do the import, then pull it back:

mysql -u root -p

GRANT SUPER ON *.* TO 'youruser'@'localhost';
FLUSH PRIVILEGES;

Run the import:

mysql -u youruser -p yourdatabase < dump.sql

Then revoke it immediately:

REVOKE SUPER ON *.* FROM 'youruser'@'localhost';
FLUSH PRIVILEGES;

Skip this on AWS RDS or any managed database. Those platforms block SUPER at the platform level — there's no way around it from inside MySQL.

Verify the Import Completed

Once the import finishes without errors, run a quick sanity check:

-- Check tables
SHOW TABLES;

-- Check triggers
SHOW TRIGGERS;

-- Check stored procedures
SHOW PROCEDURE STATUS WHERE Db = 'yourdatabase';

-- Check views
SHOW FULL TABLES WHERE Table_type = 'VIEW';

For extra confidence, compare row counts against the source:

SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'yourdatabase'
ORDER BY table_name;

Note that table_rows is an estimate for InnoDB — differences under 5% are normal. For exact counts, run SELECT COUNT(*) on tables where precision matters.

Quick Reference: Which Fix to Use

• DEFINER error in triggers/views → Fix 1 (strip DEFINER with sed)
• GTID_PURGED error → Fix 2 (remove the line or dump with --set-gtid-purged=OFF)
• SQL_LOG_BIN error → Fix 3 (comment out the line)
• You control the source server → Fix 4 (cleaner dump flags)
• Local MySQL, have root → Fix 5 (grant SUPER temporarily)

Prevention

The fix is always easier when the dump was clean to begin with. Add these flags to your backup script so you never have to deal with this during a stressful restore:

mysqldump \
  --set-gtid-purged=OFF \
  --no-tablespaces \
  --single-transaction \
  -u root -p yourdatabase > backup_$(date +%Y%m%d).sql

One more thing worth documenting: which MySQL user handles imports in each environment. A user with too many privileges is a security risk; too few, and you hit this error at the worst possible time — during a production restore. Write it down somewhere your team can find it at 2am.

If your team shares database credentials, keep passwords rotated. ToolCraft's password generator works entirely in the browser — nothing gets sent to a server.