The following guide will show you how to create and schedule a Bash server script in order to backup MySQL databases on your VPS or dedicated server.

Please note that different server configurations may require slightly different commands to those shown, and please BACKUP EVERYTHING before proceeding. You use the following information at your own risk!

I recently purchased a decent VPS for hosting a handful of demanding Magento sites (my shared hosting is just not up to scratch unfortunately).

I like to be able to offer comprehensive backup solutions to clients for complete peace-of-mind – relying on your host to do this is a big no-no – they will only make sure their backups cover themselves in the event of a hardware failure, so will be of little use if you or your client makes a mistake and breaks the site, or if, God forbid, your site gets hacked or maliciously attacked.

Joomla has a few options for auto-backing up your database and sending an email – so I started hunting around for Bash Scripts that I could run via CRON to do this for multiple databases on my VPS.

After visiting a few dozen pages, I finally managed to put everything together in my own Bash script and deployed it to the VPS, scheduled to run every day – and it works perfectly! So, without further ado, here's the process and script you need to implement this on your own server.

My VPS runs the Plesk control panel, so the process may be slightly different for your server, but the method should be the same. You will need root access via SSH in order to test the script.

STEP ONE: Setup your Server Folders

I set my folders up on the server root. You will need two folders:

• scripts
• mysqlbackups

The names are just suggestions; you can use whatever you want for these.

You can create these folders via FTP (use the root account for the server) or via the control panel file manager.

STEP TWO: Create your Script

Using a plain text editor (I used Coda for this), create the following script:

#!/bin/sh
mysqldump -udatabase_user_name -pdatabase_password --opt database_name >
/mysqlbackups/mysql_backup.sql
cd /mysqlbackups/
tar -zcvf mysql_backup.tgz mysql_backup.sql
uuencode mysql_backup.tgz mysql_backup.tgz > mysql_backup.uuc
mail -s "MySQL Backup" myemail@domain.com < mysql_backup.uuc
rm -f mysql_backup.uuc
rm -f mysql_backup.tgz
rm -f mysql_backup.sql

…and save it as mysqlbackup.sh.

NOTE: The following code should be all on one line, without the line-break:

mysqldump -udatabase_user_name -pdatabase_password --opt database_name >
/mysqlbackups/mysql_backup.sql

You can easily add more MySQL databases to the backup list by duplicating the above and changing the filenames (so that you can tell them apart, of course!).

STEP THREE: Upload and Test

You should now upload the saved script to your server. Upload it to the "scripts" directory you created in Step One.

To test, logon to the server via an SSH terminal, and go to the scripts directory:

cd /scripts

Now run the script – this command may vary, so if it does not work for you, you may have to investigate the correct command for your server environment.

bash -x mysqlbackup.sh

The terminal window should output the results (no errors with any luck), and if you check your email inbox you should have been emailed the .tgz backup file!

The original files created (the .sql, .tgz and .uuc files) are all deleted from the server via the script after it has run successfully.

STEP FOUR: Schedule Via CRON

Now that you've confirmed the script works correctly, you can schedule it to run whenever you like. I set mine to run every day at 1pm:

And that's all there is to it – you now have an auto-backup schedule for your MySQL database!

(Thanks to Paul Bradley for the original starting point for the Bash script)

There are no comments yet.

Have something to say? Join in the conversation!

Leave a comment: