Mysql Database Backup

[wkg]

The good folks at TCH keep reminding us we are responsible for our own data, even though they carefully back it up regularly. One of the things I keep meaning to do is set up an automatic backup of my MySQL databases. The manual method via phpMyAdmin and cPanel works great, but that gets tedious quickly.

 

I wanted a method to back up all my databases, I currently have 7 databases. I also realized it was probably smart not to leave the backup files on my TCH site in case of disc crash or other disaster.

 

This post covers my discoveries and what worked for me. One caveat, I haven't actually backed up using the files generated, but I did unzip them and visually examined them and in their essentials contain text as I'd seen in the files I'd manually created using phpMyAdmin, so I expect them to work fine. (Although there were some lines in the save files I didn't understand, maybe it's a version compatibility thing that someone can explain, such as

>/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;

 

One of our forum moderators recommended dbsender [by Eric Rosebrock, http://www.phpfreaks.com] to another user. I looked that over. It is a fairly simple php script which will email your backup to you (TCH doesn't support the FTP that it can also use,) but it will only backup one database.

 

I googled for scripts and visited the php script sites for a suitable program. MySQL provides powerful commands to generate backup, but though I've done some simple php scripting, I thought it would be better to find one written by a more experienced person.

 

I looked at WipeOut's - Automatic MySQL Backup - which was reviewed favorably, but as it was a shell script, I didn't feel up to trying it.

 

I found another php script that had lots of interesting features. It was backupDB() by James Heinrich at http://www.silisoftware.com. Unfortunately, I was unable to get it working in either the interactive or cron job mode. The author provided his email for questions or comments, but after several days receiving no response and no joy in figuring out what was going wrong, I abandoned working with it.

 

Then I found the script at Dagon Designs. It will backup multiple databases, compress them, and either leave them on the server or put them in an archive and email them to you. I did get this to work, and will show you what I needed to do to get it to work on TCH.

 

First I want to pass along a good piece of advice I ran across while searching for a script. I forgot who wrote it, so can't pass along credit. He was offering a script, but the link to it was no good. He recommended you create a special user to access your database(s) with the backup script. Only give this user SELECT and LOCK TABLES privileges. This provides both security and protection. This user cannot change the database and a misbehaving script cannot damage the database. Most scripts (and good practice) lock the tables while doing backup.

 

Dagon Design's utility has only two files, a configuration you change, and the script which you don't edit. The basic instructions are provided on the site. Additionally, the config script is well documented.

 

HOWEVER, you do need to change the defaults for the backup and temporary directory destinations! Leaving the defaults will generate errors as the script tries to create the directories on the system root. You need to change these to the full path to where you want them within your space. For example, this is what I used:

 

>// Backup destination (will be created if not already existing)
$BACKUP_DEST = '/home/cpanelname/public_html/cgi-bin/db_backups';

// Temporary location (will be created if not already existing)
$BACKUP_TEMP = '/home/cpanelname/public_html/cgi-bin/db_backups/backup_temp';

 

Now I don't know if the script will actually create those directories itself if they don't exist, because by the time I figured out I needed to change the path, I'd already manually created them.

 

My only other changes to the default was user and password, naturally; I changed the compression to gzip, email to true & delete after email to true, and of course the address to send the archive file and error msg.

 

The error email only tells you that there was an error, not what it was. You will have to consult the error log for that. When I was getting it up and running I provided my email address when filling out the cron job page in cPanel (I used the standard, non advanced version.) Cron emailed a comprehensive list of the job with all the errors and/or successes. This provided enough explanation to get the script to work, so then I removed my email address from the cron job page (though I expect more experienced users can tell us how to append something to the command to keep cron from emailling its job log.)

 

The cron command I entered was:

>php -q /home/cpanelname/public_html/cgi-bin/backup_dbs.php

 

As I undestand it you need the "php" to tell it what kind of script and "-q" means quiet, though I'm unsure of the specifics.

 

I hope this will help you and encourage you to back up your databases.

[TCH-Thomas]

Dbsender can be used for several database backups. All you need to do is to upload it to different folders and configure each php file before uploading it to respective folder and configure one cron job per backup.

 

Not sure this is the best way to do this, but I use it for 2 db backups and it works prefectly.

[wkg]

Dbsender can be used for several database backups. All you need to do is to upload it to different folders and configure each php file before uploading it to respective folder and configure one cron job per backup.

True, but the beauty of a script such as Dagon's is that it automatically searches for all your databases (you can set exceptions, though I haven't tried that) and it won't need reconfiguration if you add a new database. With dbsender you'd need to add new files & directories, configure files, etc. I'd get busy with the db and forget to do the housekeeping

[TCH-Bruce]

To each their own. Glad you found a solution you can live with.

 

I prefer to use dbsender myself. Everytime I add a database I add a new script and cron job. They get sent to a special email address I created just for backups nightly.

[ALS]

wkg I just know you can never have enough backups.....great I'll look into this one.....I've always worried about my databases.