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
Did this help you?
Want to speed up your site, easily?
Set up your site on Servebolt, free of charge for 60 days, and see for yourself how fast it can be on our extremely fast hosting.
A faster site converts better, ranks better on Google, and is better for the climate.