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).
Tags: mysql, sed
When I develop WordPress sites, I find that I end up repeating many identical tasks for each site. The phrase “repeating identical tasks” should (and does) set off alarms: This should be automated! To that end, I’m trying to learn some more command line tools for doing tasks that I currently use a GUI for.
One such task is migrating my development database to the staging/production server. Basically a
mysqldump from my local database that I can then import on another server. A key thing to watch out for with WordPress, though, is the base URL for your site. If you’re serving a site from
localhost while you develop it, you’ll need to change every occurrence of
localhost in your database to the base URL for the new server.
sed is a command line tool for doing just that job. It runs a regular expression search on the input and outputs the replacement. Example:
$ echo "localhost" | sed "s/localhost/www.example.com/g"
sed to have an SQL file ready for your new server.
$ mysqldump -uusername -ppassword database_name | sed "s/localhost/www.example.com/g" > database.sql
This way I avoid having to open the SQL file in a text editor, doing a global search and replace, and re-saving.