In almost all of my Rails projects, I write a Rake task to take a dump of the remote mysql database and copy it locally. I decided to do this for my WordPress installation as well, as I tend to test things on my local copy first.
Wordpress isn’t too keen on doing this, as it stores the hostname of the running WordPress instance in several places in the database. To get around this, I substitute the remote hostname in the dumped SQL for my local one.
An extra trick I thought I’d try here is to pass the processed SQL dump directly into a local mysql client process, removing the need for a temporary dump file.
NB: due to firewall restrictions I can only access my remote database server by SSHing through my Amazon EC2 instance. You might not need to do this. In you don’t, you can change the dump assignment (lines 20 and 21) to a more straightforward mysqldump command from your local machine.
Run the thing with rake db:import_production
Get the Gist: