Remove DEFINER statements from mysql dumps

If you encounter an error like this while doing mysql imports, there are DEFINER statements in the dump that should be removed.

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

A simple way to remove them is to run the following command:

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

After that, rerun your import and that’s it.

Why does this happen?

During exports, stored procedures and triggers are exported with the current username. When you are importing the dump again, with another user, the username does not match your current user.

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.

Give us your feedback on this article