Disaster Recovery optimize

Automate MySQL backups with the help of MySQLDumper

Learn how to automate backing up MySQL databases with MySQLDumper and configure the web-based tool to email you a report about whether the task succeeds.

After completing the tasks outlined in my TR Dojo post about MySQLDumper, you now have an easy to use, web-based tool that can back up your MySQL databases. If you want to automate the backup process, MySQLDumper can help with this task, though it doesn't have all of the tools built in that are necessary for automated backups. Fortunately, your Windows server has all of the the tools you need.

Requirements

  • Windows Server
  • Working Perl + PHP
  • IIS 6

Automating MySQL backups

The first step is to log in to MySQLDumper and start the process of backing up a database. Once you're on the backup screen (Figure A), click the Backup PERL button. (Be sure to select the database you want to automate first.) Figure A

MySQLDumper's Backup screen (Click the image to enlarge.)
The new window that opens (Figure B) will give you all the information you need to set up the automated backup process for the selected database. Figure B

I already have email reports set up -- more on that later in this post. (Click the image to enlarge.)

At the bottom of the screen you'll see three lines that look like what's below.

Entry in crondump.pl for absolute_path_of_configdir:

/var/www/msd/work/config/

URL for the browser or for external Cron job:

http://localhost/msd/msd_cron/crondump.pl?config=mysqldumper

Commandline in the Shell or for the Crontab:

perl /var/www/msd/msd_cron/crondump.pl -config=mysqldumper -html_output=0

The final line is most important; you need to put that line in a script.

  1. Open a terminal window.
  2. Open your favorite text editor.
  3. Enter #!/bin/bash on the first line.
  4. Paste the contents of the third line (under Commadline in the Shell or for the Crontab) in the second line.
  5. Save the script (name it msd_backup.sh) and close the editor.
  6. Give the script executable permissions with the command chmod u+x msd_backup.sh.

The full contents of the msd_backup.sh script will look similar to this:

#!/bin/bash
perl /var/www/msd/msd_cron/crondump.pl -config=mysqldumper -html_output=0

Here are the steps to create a cron job that will run the backup at midnight every night:

  1. Open a terminal window and issue the command crontab -e.
  2. With the crontab editor open, scroll to the bottom of the crontab file, and add 0 0 * * * /PATH/TO/msd_backup.sh (/PATH/TO/ is the explicit path to the directory containing the backup script).
  3. Save the crontab entry.

If you don't get any errors upon closing, this means the cron job has been entered.

Setting up email report alerts

You can set up email reports to get notifications about whether the backups succeeded. You can also set it up so the alert will attach the backup file to the email. Follow these steps to set up these email alerts.

  1. Log in to MySQL Dumper as the administrative user.
  2. Go to Configuration | Email.
  3. Check Yes for the Send Email Report option.
  4. Enter the details for where you want the report to go, who the report is from, if you want to attach the backup file, and the delivery method: Sendmail or SMTP (Figure C). If Sendmail is not installed on your machine, I recommend going with SMTP (unless you are very familiar with the installation/configuration of Sendmail). If you use an external SMTP server, that server will have to be accessible without authentication. To make sure your backup files are safe, enable the attaching of files to the report.
  5. Click the Save button.

Figure C

MySQLDumper Email Configuration screen (Click the image to enlarge.)

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

0 comments