MySQL bills itself as the world’s most popular open source database. It turns up all over, including most installations of WordPress. Packages for multiple platforms make installation easy and online resources are plentiful. Web-based admin tools like phpMyAdmin are very popular and there are many stand-alone options for managing MySQL databases as well.
When it comes to back-up, though, are you prepared? Backup plug-ins for WordPress databases are fairly common, but what other techniques can be used? Scripting to the rescue!
On Unix-type systems, it’s easy to find one of the many example scripts online, customize them to your needs, then add the script to a nightly cron job (or launchd on Mac OS X systems). Most of these scripts use the mysqldump command to create a text file that contains the structure and data from your database. More advanced scripts can loop through multiple databases on the same server, compress the output and email you copies.
Here is an example we found online a long time ago and modified (thanks to the unknown author):
#!/bin/sh
# List all of the MySQL databases that you want to backup in here,
# each seperated by a space
databases="database1 database2 database3"
# Directory where you want the backup files to be placed
backupdir=/mydatabasebackups
# MySQL dump command, use the full path name here
mysqldumpcmd=/usr/local/mysql/bin/mysqldump
# MySQL Username and password
userpassword=" --user=myusername --password=mypassword"
# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"
# Unix Commands
gzip=/usr/bin/gzip
uuencode=/usr/bin/uuencode
# Create our backup directory if not already there
mkdir -p ${backupdir}
if [ ! -d ${backupdir} ]
then
echo "Not a directory: ${backupdir}"
exit 1
fi
# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql
done
# Compress all of our backup files
echo "Compressing Dump Files"
for database in $databases
do
rm -f ${backupdir}/${database}.sql.gz
$gzip ${backupdir}/${database}.sql
done
# And we're done
ls -l ${backupdir}
echo "Dump Complete!"
exit
Once you verify that your backup script is giving you valid backup files, these should be added to your other backup routines, such as CrashPlan, Mozy, Retrospect, Time Machine, Backup Exec, PresSTORE, etc. It never hurts to have too many copies of your critical data files.
To make sure your organization is prepared, contact your 318 account manager today, or email sales@318.com for assistance.
