Making a staging database with sed

Quick one – thought was was cute and useful.  I take a copy of live databases once in a while for use in the staging environments, but some apps have references to the live url in the there (WordPress does this and makes all its redirects using it, making it particularly difficult to test in staging).

This is a simple little way to change all the urls in the db as you clone it:

mysqldump -h live_db_host -u user -pmypass live_db | sed -e '{s/www.example.com/staging.example.com/g}' | mysql -h staging_db_host -u user -pmypass staging_db

Though depending on your MySQL table type you might want to dump to disk first, then pipe it through sed as your live tables might be locked (I’m not actually sure if mysqldump will block waiting for the other processes to catch up)