WordPress and automated MySQL backups to a different host

You can use plugins such as BackupBuddy, but I prefer to script this and use wp-cli, which reads wp-config.php and means you don’t have to worry about mysql credentials.

wp-cli allows you to:

• export the db: wp db export <filename>
• import the db: wp db import <filename>
• safe search and replace (including serialised data): wp search-replace <search-string> <replace-string>

So, I have a script that

• executes wp export on a remote server via ssh
• copies the sql export file via scp to my local machine
• executes wp import locally
• executes wp search-replace

You could then add it to cron, but I prefer to run it manually – takes less than a minute to sync db from production to my local environment.