In memory of WordPress Backup Week four years ago, I thought I’d knock up a little script to backup my WordPress database and file content.
You might be wondering why I’d want to script it, seeing as there’s plenty of Wordpress plugins to backup your Wordpress content from the safe confines of WordPress administration.
Well, my thinking was that anything that is activated by a plugin in WordPress is fine if WordPress is working. If it is completely out-of-action, you’re going to have to recover your backup from the command line. I’m comfortable with the command line and a bit of (low quality) shell scripting, so why not roll my own simple backup that grabs all the file and database content and makes a backup file out of it? Besides, while the Wordpress Mobile Pack gives you the administration essentials, it’s not great at handling plugins, and I quite like the idea of being able to perform an emergency restore with nothing but a Smartphone or PDA with a copy of Pocket PuTTY installed on it.
There’s advice on WordPress backups on the Codex, which makes a good starting point. I also found some old script examples here. Time to do a bit of modifying! I liked the original idea of having a 7-day rolling backup set to prevent disk space being overrun, but I also liked the idea of backups stamped with the date they were taken, to ease any restore. So, I decided that it would be simple to adjust the script to still create 7 day-of-week folders and just store the backed-up content in a compressed tar archive using the date taken as the filename.
There were a few more changes I made to the code. While I appreciate the ‘clean’ approach of putting usernames and passwords in a separate file, anyone finding the script would soon see where to look to find those details, so it’s not adding much in the way of security. I also made a few tweaks to the MySQL commands used to get the table listing.
Here’s the code I got to:
#!/bin/bash # MYSQL Backup Script # Contains portions of code and ideas from http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/ # and http://ocaoimh.ie/simple-mysql-backup/ # Get day of week to keep 7 rolling backups sequence, and date to name tgz files for ease of identification export d=$(date +%Y-%m-%d) export s=$(date +%u) # Set up the paths that we're going to use for source and backup export wppath=/var/www/html export savepath=/var/www/backup export tmppath=/tmp/wpback-$s-$d # The wp database to backup export db=wp_myblog # Username and password for the wp database - SUGGEST A READ-ONLY USER IS USED! export wpuser=wp_backupuser export wppass=MyP4sswordG0esH3re # Tell syslog and any interative user that we're running logger "Wordpress backup script backing up $wppath to $savepath/$s/$d.tgz" echo "Wordpress backup script backing up $wppath to $savepath/$s/$d.tgz" # Make the temporary backup path mkdir -p $tmppath echo "Dumping database: $db..." for tab in `mysql --user=$wpuser --password=$wppass -s -e "show tables;" $db`; do echo "Dumping table: $tab" mysqldump --user=$wpuser --password=$wppass --add-drop-table --allow-keywords -q -a -c $db $tab > $tmppath/$tab.sql done echo "Archiving Wordpress file content..." tar -cpf $tmppath/wp-files.tar $wppath/ # Clear and remake the backup path rm -rf $savepath/$s mkdir -p $savepath/$s echo "Compressing database and files into $savepath/$s/$d.tgz" tar -czf $savepath/$s/$d.tgz $tmppath/* echo "Deleting temporary files" rm -rf $tmppath echo "Wordpress backup complete"
So, what does the code do? Well, first the date tool is used to obtain the day of week and date string, then a few user-modifiable strings define where to back up Wordpress content from, where to make temporary files and where to save the backup archive. The script quickly informs any interative user, and the system log, that it is running then we get to the important bit.
The MySQL command-line client is called, specifying the username and password, using the -s parameter to tell the client to output a simple listing with one result per line, and the -e parameter with the command to execute, in this case “show tables” and finally the database to execute the command against. This fills the tab script variable with a list of tables to back up.
A temporary location is created to store the database tables and files until the final backup file is created.
Next, the MySQL dump client is called once for each table, again supplying username and password, and with parameters indicating that the dump should create a file that deletes any existing table before inserting data, and ignores any reserved words it sees, and stores the results for each table in a text file in the temporary location.
Then, the Wordpress content directory is archived using the tar utility, specifying to preserve premissions attributes. This backup is not compressed, as the script then goes on to clear any existing day-of-week directory, and creates the final backup archive in there as a compressed tar archive containing all the SQL table dumps and the single WordPress file archive just created.
Finally, the script clears out the temporary location it created.
To use the script, it needs to be downloaded to a place outside of your WordPress content area (so it can’t be viewed by visitors to your web site), and given suitable permissions to run, with a command such as such as “chmod 744 wp_backup.sh”.
The lines at the top of the script that define wppath (WordPress files location), savepath (place to put the day-of-week directories), tmppath (place to put temporary files), db (MySQL database to backup), wpuser (MySQL user account for backing up) and finally wppass (MySQL user password) will need to be edited to suit the local WordPress and MySQL installations.
Next the MySQL user account needs to be created. For a little bit of extra security, I recommend using a dedicated user account with read-only access to the database. This is easy to create. Simply start the MySQL client with the -p parameter (type mysql -p from a shell prompt) and enter you MySQL root password when prompted. Next create a user account and grant read rights with the command GRANT SELECT, LOCK TABLES on wp_myblog.* TO ‘wp_backupuser’@'localhost’ IDENTIFIED BY ”MyP4sswordG0esH3re”;
The script will execute from the shell, and will display some progress as it goes.
To automate the process with cron, simply type crontab -e to edit the cron table, and add a line like this:
1 0 * * * /var/www/domains/thatchrisbrown.com/www/backup/wp_backup.sh
This tells cron to call the script at one minute past midnight every day. You can verify the script has run my looking in the /var/log/messages file for the script announcing itself when run.
Disclaimer: This was written on a CentOS 5.3 box using fairly standard installs of Apache, PHP, MySQL and Wordpress. Your environment may vary, so some elements may need adjusting to suit. This script and its author makes no claims to fitness, usability, suitability for any specific purpose, safety or neatness, ingenuity, tidiness or prettiness of code. You use it at your own risk.
Recent Comments