Remove DEFINER clause from MySQL Dumps

Question

I have a MySQL dump of one of my databases. In it, there are DEFINER clauses which look like,

"DEFINER=`root`@`localhost`" 

Namely, these DEFINER clauses are on my CREATE VIEW and CREATE PROCEDURE statements. Is there a way to remove these DEFINER clauses from my dump file?

Answer 1

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