Posted by CANbike on Tue, 13 Aug 2013

[Hostgator, WolfCMS] Automatic MySQL Backup

Website backups are important, but they can be a chore. However, an automated daily database backup sent to my email inbox everyday would be really convenient!

So I did just that with one line of code!

The Short Version

Add the following command to a cron job

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip | uuencode <FILENAME>.gz | mail <EMAIL>

where

<USERNAME> - A username for an account with database access rights
<PASSWORD> - Password for the username
<DATABASENAME> - Name of the database to backup
<FILENAME>.gz - Name of the file to save the MySQL database dump to
<EMAIL> - An email address to send the file to

Use uudecode to convert the uuencoded email message to a binary file.


The Long Version

Details

A website, based on WolfCMS, is located on a Hostgator server.

Server Details:

  • Hosting is from Hostgator
  • The server is running CentOS Linux x86 with the latest cPanel
  • Cron jobs are accessed through cPanel
  • Cron jobs can be run at most every 15 minutes

Website Details:

  • Based on WolfCMS, a lightweight PHP based content management system
  • Has a single user
  • Contains no personal or private data
  • Uses one MySQL database
  • Contains hashed account information

Old Backup Routine:

  1. Login to Hostgator cPanel
  2. Access web-based tool phpMyAdmin
  3. Export the database and download it
  4. Logout of Hostgator cPanel
  5. Access Hostgator server via FTP
  6. Download all files

WolfCMS Website Backup Discussed:

Given the lightweight PHP nature of WolfCMS, file backups are less important. It is the database that changes as content is edited or posted.

WolfCMS files do not change, unless a update or plugin is manually installed. The only other exception is when changes are made to the html/CSS template.

Images, however, are regularly uploaded by FTP to a single directory. They serve to supplement the articles. However backups are not critical as they are resized and reduce versions of locally stored and backed up photos.

Description Changes Notes
MySQL Database Articles are posted, content is edited Possibly no original local copy
WolfCMS Files Version update, html/CSS recoded Rarely changes
Images Downsized version uploaded by FTP Originals are locally backed up

File backups (all files or image directory) can be done by FTP, after uploading new images.

MySQL database backups, on the other hand, are not part of a regular routine. The username and password have to be located to log into the Hostgator cPanel. Then phpMyAdmin is used to export the database to a downloadable file.

Unfortunately, it is the MySQL database that is most important as the website cannot be restored without it!

Luckliy due to the lightweight PHP based design of WolfCMS, the database is very small (~1 MB) and easily compressible. Hence, the idea to have database backups automatically delivered to my email inbox! It would be a low resource but useful task that elimantes having to login to the server cPanel.

Automatic MySQL Backups Delivered to Email Address

Cron can be used for the automatic scheduling of tasks.

The Database Backup Command

The command to backup a MySQL database and send it to an email address is

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip | uuencode <FILENAME>.gz | mail <EMAIL>

  • First, the program mysqldump is used to make a backup of the MySQL database:

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME>

where according to the man page, the -e option enables the

Use of multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

the --user option specifies a

MySQL user name to use when connecting to server

and the --password option specifies

The password to use when connecting to the server

  • The database dump is then piped to gzip for compression:

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip

  • The compressed file is then piped to uuencode for uuencoding (an old robust binary-to-text encoding method for sending binary files by email):

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip | uuencode <FILENAME>.gz |

  • Finally, the uuencoded binary file is then piped to mail and sent to an email address:

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip | uuencode <FILENAME>.gz | mail <EMAIL>

Automatic Scheduling (Cron)

A cron job set for every day of the week at 3:00 a.m. would then look like

0 3 * * * mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip | uuencode <FILENAME>.gz | mail <EMAIL>

where

<USERNAME> - A username for an account with database access rights
<PASSWORD> - Password for the username
<DATABASENAME> - Name of the database to backup
<FILENAME>.gz - Name of the file to save the MySQL database dump to
<EMAIL> - An email address to send the file to

Hostgator Cron Jobs

Hostgator cron jobs are set by accessing them through cPanel’s advanced section.

Hostgator Automatic MySQL Backup-01

The scheduling is set by a graphical user interface consisting of text fields and drop-down lists.

Hostgator Automatic MySQL Backup-02

The following is entered in the command text field.

mysqldump -e --user='<USERNAME>' --password='<PASSWORD>' <DATABASENAME> | gzip | uuencode <FILENAME>.gz | mail <EMAIL>

where a hostgator database <USERNAME> is of the form cpanelUsername_databaseUsername and a hostgator <DATABASENAME> is of the form cpanelUsername_databaseName.*

Hostgator Automatic MySQL Backup-01-thumb.jpgHostgator Automatic MySQL Backup-02-thumb.jpgHostgator Automatic MySQL Backup-03-thumb.jpgHostgator Automatic MySQL Backup-04-thumb.jpg

Why are the Emails Delivered All Text?

The emails sent are all text. This is a result of uuencoding the compressed MySQL database backup file. More specifically, the file is converted from binary to text, and then sent as the message body in the email.

Unfortunately, not all email programs can detect a uuencoded message and automatically convert the text back to a binary program. However, the command uudecode can be used to recreate the original binary file!

Decode Instructions:

  1. Open the email message
  2. Copy or save the email message to a text file
  3. Open a terminal and run the command uudecode on the text file

Uudecode Email-01-thumb.jpgUudecode Email-02-thumb.jpgUudecode Email-03-thumb.jpgUudecode Email-04-thumb.jpg