Automatic MySQL Backup to Email

If you have a website running WordPress that relies on a MySQL database, you probably should be backing up your MySQL database on a daily basis. Here are the steps to create an automatic solution to do a MySQL backup and sends the results to your Email on a daily basis. I assume that you are using a web host that has the control panel cpanel.

Setup Backup Directory

Login to your hosting control panel and create a new folder directory named backup in your home folder directory using the File Manager application.

Create MySQL Backup to Email Script

Create a file named mysql_backup in your /home/USERNAME/backup folder directory created above.

In this file insert the following code:

#!/bin/bash

# CLEAN UP SENT FOLDER
rm /home/USERNAME/sent

# MySQL BACKUP
mysqldump DATABASE --user=MYSQL_USERNAME --password=MYSQL_PASSWORD | 
gzip > /home/USERNAME/backup/mysql_backup.sql.gz ; 
mutt -s "MySQL Backup" -a /home/USERNAME/backup/mysql_backup.sql.gz 
-- EMAIL_ADDRESS < /dev/null 

In the code above you need to substitute your username, database name, MySQL username, MySQL password, and Email Address.  The code that needs to be substituted are all listed in UPPERCASE.

Please note that part of the code above deletes your sent email. If you don’t want to do this, please remove this line. I do this because the sent email gets quite large every time a backup runs and it is wasting storage space.

Lastly you need to change permissions of this file so that is executable. In the File Manager:

  1. click on the mysql_backup file you just created
  2. click the Change Permissions button
  3. click the 3 boxes next to Execute
  4. click Change Permissions

Schedule the Daily MySQL Backup

MySQL BackupTo schedule the daily MySQL backup you need to go to the home of your control panel and click on Cron jobs in the advanced section.

On this screen you need to schedule the job using the template listed to the right. This will run your mysql_backup script every day at midnight and email you with the MySQL backup file gzip.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.