Posted on Jan 07, 2011 at 22:15
Backups - MySQL Dump/Email Mash-Up
This backup process dumps specified MySQL databases to individual files and then creates a tarball of them all on a seperate drive. It also emails out a results report upon completion. To do this I called upon several technologies to mash-up a practical solution. It's a little involved but worked out rather well.
The required software involved is as follows:
PHP PEAR: Mail package
PHP PEAR: Mail_Mime package
PyODConverter: OpenDocument Converter
Some things need to be in place before running the script. All of the above software needs to be installed. Install them where you wish and make the necessary path adjustments in the scripts. You should be able to find those rather easily.
The download package includes 4 files total. I will explain the process flow. As you look at the scripts keep in mind that it was setup to run on a Windows box under Cygwin. The pathings should give that away but those can be changed to suit your needs.
This is the main executable that is executed directly. It basically just performs a dump of the specified MySQL databases, gzips them, creates a tarball of them all and copies them to another drive. Then it initiates the summary report it sends out via email. You could just use thise script for the backups and be done with it. The rest is required if you want to email a summary report.
This is a PHP script that actually creates the email body and emails out the report using SMTP. Here we need the two PHP PEAR packages mentioned above to perform the emailing of the report.
This is another PHP script that holds a function that creates the actual email body from an OpenOffice odt template file. It takes the template file (mentioned next) and converts it to an HTML file that ends up being the email body. It does this conversion by using the Python PyODConverter tool. After doing this it does various variable subsitutions to fill in the blanks with the correct information about the backup.
This is the OpenOffice Writer file that contains the body of the summary report email that gets sent out. This is nice because you can easily format how you want the email body to look using an OpenOffice document.
[steved@blackbox]$ ./backup_mysql_dbs.sh Backing up to directory /cygdrive/c/Storage/BACKUPS/ ... Generating db backup file 20110107000657_blackbox_mybiz-1.sql.gz ... Generating db backup file 20110107000657_blackbox_yourbiz_205.sql.gz ... Generating db backup file 20110107000657_blackbox_yourbiz_999.sql.gz ... Generating db backup file 20110107000657_blackbox_hisbiz_33.sql.gz ... Generating db backup file 20110107000657_blackbox_hisbiz_7.sql.gz ... Generating tarball backup file 20110107000657_blackbox_backup_dbs.tar ... Email successfully sent to the following recipients: To: <firstname.lastname@example.org> CC: <email@example.com>
Here the script is setup to put the backups files in /cygdrive/c/Storage/BACKUPS/. Using Cygwin this is the equivalent of saying C:\Storage\BACKUPS on the Windows system. It generates the five individual database backup files first. Then it tars them all together and puts the file in the aforementioned directory. It finally emails out the summary report to the people specified in the script.