WordPress backup on demand to OS X

I wanted a script which would download the SQL database for this site in one go. So I wrote one. This script works on OS X, and should be fine anywhere 'Bash' is spoken. This script operates only the local machine, but requires ssh access to the remote machine (where commands are executed). The database is compressed on the server and decompressed on the local machine. This is to minimise download time.

Of course, anything you do with this is at your own risk, but it works for me.

Here it is: [sourcecode language="bash"] #!/bin/bash

cd /Users/USERNAME/Documents/Backups/website/sql

echo "Going to remote server to get the SQL Database";

ssh serverusername@servername 'mysqldump -usqlusername -psqlpassword --opt sqldatabase > sqlbackup/latest.sql ; cd sqlbackup ; tar -cvzf latest.sql.tar.gz latest.sql ; rm latest.sql'

# serverusername is the name used to log in via ssh to the server # servername is that server # sqlusername, sqlpassword and sqldatabase are as in wp-config.php # note, no space between -u and the sqlusername etc # note - this script has your sql database credentials, don't put it somewhere where it is exposed to malicious types.

echo ""; echo "Downloading the SQL database" scp userforservername@servername:~/sqlbackup/latest.sql.tar.gz .

echo ""; echo "Unpacking the SQL database into ~/Documents/Backups/website/sql"; gunzip latest.sql.tar.gz tar xvf latest.sql.tar rm latest.sql.tar [/sourcecode]

This relies upon the following:

  1. There existing a directory called sqlbackup in the home directory on the server
  2. Already having setup password free ssh access to the server
  3. Knowing sqlusername and password etc (available in wp-config.php)
  4. Having the correct path to a folder on the local machine (you will want to modify the above script)

I called my script dbbackup, and it was placed in ~/Documents/scripts - which is in my $PATH.

Don't forget, the script must be chmod'ed with permissions 755, and if the script is not in a location listed in the $PATH variable, you'll need to run it with ./scriptname instead of just scriptname - of course, you'd use a 'terminal' command line prompt.

Next, I want to modify the downloaded file to change the table prefixes, and automatically upload it to MAMP. I want to find/replace for local links in posts (so that my mirror copy doesn't continually redirect me to the server copy) - and I want to change the WP settings so that the blog calls itself something to remind me I'm on a local version.