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.

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

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

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

Related