Remove DEFINER clause from MySQL Dumps

I don't think there is a way to ignore adding DEFINERs to the dump. But there are ways to remove them after the dump file is created.

1. Open the dump file in a text editor and replace all occurrences of DEFINER=root@localhost with an empty string ""

2. Edit the dump (or pipe the output) using perl:

   perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql
   

3. Pipe the output through sed:

   mysqldump ... | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > triggers_backup.sql
   

You can remove using SED

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

In MacOS:

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

Since mysql version 5.7.8 you can use the --skip-definer option with mysqlpump, e.g.:

mysqlpump --skip-definer -h localhost -u user -p yourdatabase

See updated mysql manual at http://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_skip-definer