The Chatterbox

Database Backup Script

Just a quick note today to let you know I've just uploaded a modified version of the Database Backup script that I first released some 2 1/2 years ago. It's not a critical update or anything, just a couple of useful tweaks.

The need arose for me to have the ability to make a backup of a database that lives on a separate MySQL Database Server. So rather than back it up on the db server itself via a shell script I chose to use the php database dumper script to create the backups on the main server that runs Apache, mail and everything by the MySQL server.

The additions allow one to specific either localhost (for local dbs), and IP number of a remote server or a hostname. It also allows one to set the port to use in case you're using something other than the standard MySQL port 3306.

While I was tweaking the script I added a --lock-tables option to the mix, which will cause the script to lock a table before starting its dump. This was done to fend off the rare cases of potential data corruption if the db was changing at the exact moment a table was being updated.

If you need to make any other changes to how mysqldump works in the script you can simply edit the three appropriate lines to add them. For instance, if you wanted to add a --add-drop-table so that the dumps would automagically drop existing tables when performing a restore it's a trivial edit. Just remember there are 3 places in the script to make such edits (they're easy to find) and also be careful about leading spaces with the concactenation. In other words, to insert --add-drop-table it should look like " --add-drop-table" as opposed to "--add-drop-table".

Cheers!

You can ping this entry by using http://www.randycullom.com/chatterbox/mt-tb.cgi/35 .