Remove DEFINER clauses from MySQL dumps

If you have triggers or views in a MySQL database, a dump of that database will have clauses like:

/*!50017 DEFINER=`root`@`localhost` */

To import this dump into a new MySQL server (e.g., going from a production environment to local development), you’ll run into problems if you don’t have the same users defined on both systems.

As far as I can tell, there’s not an easy way to exclude these clauses from the dump, but with just a bit of post-processing, your SQL file can become portable. You can replace the user in the DEFINER clause with CURRENT_USER and it will use the current user when you import the SQL.

$ mysqldump > non_portable_dump.sql
$ sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' non_portable_dump.sql > portable_dump.sql

Note: Depending on your version of sed, you might need to use the flag -r (for GNU sed) instead of -E (for BSD sed).

2 Responses to Remove DEFINER clauses from MySQL dumps

  1. Prasad says:

    Buddy, very useful post.

    After spending an hour for searching on Internet finally I got my answer here.

    Its worked perfectly fine for me.

    Thanks a lot !

  2. Jan Novotný says:

    If you already have view with bad definer created in your database – you can get rid off it by this one-liner:

    mysql -uuser -ppwd -A –skip-column-names -e”SELECT CONCAT(‘SHOW CREATE VIEW ‘,table_schema,’.’,table_name,’;’) FROM information_schema.tables WHERE engine IS NULL and table_schema like ‘mydb%'” | mysql -uuser -ppwd -A –skip-column-names | sed -rn ‘s/.*?VIEW ([^\s]+?) (AS .*?)\s([^\s]+?)\s([^\s]+?)/DROP VIEW \1;\nCREATE VIEW \1 \2;/p’ | mysql -uuser -ppwd -A –skip-column-names
    You have only to replace strings in bold with your DB user credentials and database name / like pattern.

    More info here: http://blog.novoj.net/2014/05/16/recreate-mysql-views-without-definer-one-liner-solution-linux/