Backup databases from the command line

Daily backup is one of the most important things that a WordPress website owner should do. There are plenty powerful plugins that can help you to get the job done quickly and efficiently. However, if you are using a VPS, you can create a simple script to backup your databases daily and this post will show you how to do that.

Backup script

This is the script I'm using to backup my website on Digital Ocean VPS: (scroll down to full script that backup all databases and upload to Dropbox)

#!/bin/bash

DIR=~/backup/domain.com/
DATE=`date +"%Y%m%d-%H%M"`
[email protected]
USER=your_db_username
PASSWORD=your_db_password
DB=your_db_name

cd $DIR

mysqldump -u $USER -p$PASSWORD $DB | gzip -9 > $DB-$DATE.sql.gz
echo "Backup for database $DB on $DATE completed." | mail $MAIL -s "Daily backup $DB on $DATE"
find ~/backup/domain.com/* -mtime +30 -exec rm {} \;

This script does 3 things:

1. Backup a database to a specific folder
2. Send an email notification to your email when it's done
3. Delete old backup files (older than 30 days)

You need to change the parameters at the beginning of the script to make it work in your case. After done, save it as backup.sh.

Then run the following command to make it executable:

chmod +x backup.sh

Add cronjob

The last step to ensure your database is backup daily is adding a cronjob. Run the following command to add a cronjob:

crontab -e

Then simply add the following line

0 0 * * * ~/backup.sh

This line ensures your backup script run once daily at 00:00.

That's all. Now you can deactivate and uninstall your WordPress backup plugins. That reduces the server load and thus, increases the website performance and makes your server run faster. You still have your database backup daily, which is great!

Send backup files to Dropbox

After working a while with the script above, I tried to upload the backed up files to Dropbox. Thanks to this script, everything is easy now. Here is my final script that I'm using:

https://github.com/elightup/dotfiles/blob/master/backup.sh

If you're using Amazon S3 for storage, you can use s3cmd to send the backup file to Amazon S3.