How to Remove DEFINER Statements From MySQL Dumps

If you encounter an error like the following while doing a MySQL import, there are DEFINER statements in the database dump file. The easy way to get past this problem is to remove the DEFINER statements from the MySQL dump file.

The error message you’ll see is:

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

The easiest way to remove them is to run the following command:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i dump_file.sql

After that, re-run the database import.

Why does this happen?

During exports, stored procedures, and triggers are exported with references to the current username. This happens independent of how you back up or export your database. The username at the time of export is custom to your hosting setup. When you then try to import this dump on another server, where you most often use another username to connect to the database, the username in the database dump file does not match the currently active user.

Do I need SUPER privileges?

You do not need to have SUPER privileges to fix this, removing the definer statements will create these triggers and stored procedures with the current, and correct username. On most hosting setups you will and should in general never use the SUPER user (root) to run database imports, because it is dangerous to execute code as the root user.