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).