Novell Home

Using MySqlHotCopy on Open Enterprise Server

Novell Cool Solutions: Tip
By Chris Beckett

Digg This - Slashdot This

Posted: 1 Aug 2006
 

Problem:

We are running a mission critical payments service on MySQL on OES NetWare SP2. The problem we have is that we need to be able to back up the tables without mangling them. This isn't easy and nor can it be cheap. Some backup vendors will supply a MySQL backup agent, but that costs serious money!

Luckily, with an installation of Perl on your OES NetWare server and a script already included in MySQL called "MySqlHotCopy", you can take a dump of the tables outside peak hours, put them somewhere away from your live data and let your backup software do it's stuff on unlocked tables. Risk free!

Solution:

You should have Perl installed either at the same time as MySQL or before, this way you can be sure that the MySQLHotCopy.pl file is there (it actually lives in SYS:\MySQL\Scripts). What we also need to be able to do is quit the script once it's complete, so we can automate the process with Cron.

I would also recommend dumping your tables to a different volume than the main MySQL data store (I have only tested volumes on the same server, I'd be interested to know if it works cross servers).

MySQLHotCopy basically provides freeze/thaw functionality for your tables so that they aren't modified as you are backing them up, giving you a clean snapshot.

Example:

Edit SYS:\ETC\CRONTAB to contain the following line :-

# Run MySQL Hot Backup to take a snapshot of MySQL database for tape backup

30 0 * * * perl --autodestroy sys:\mysql\scripts\mysqlhotcopy.pl -u root -p <root's password> <db name> /mysqlbackup --addtodest (should all be on one line)

This runs MySQLHotCopy each night at 1230am. It calls the script, logging into MySQL (not eDirectory!) as the root user with the corresponding password. We then specify which database we wish to backup (and therefore all tables within) and to where, in this case it is SYS:\MySQLBackup. We also want to "freshen" the files in backup location, so --addtodest will basically perform a file overwrite.

Oh, one last thing, --autodestroy will end the script and quit, once it has finished processing. This is VERY important otherwise the script will have problems after the first time it runs.

Enjoy!

Environment:

This solution works with OES NetWare SP2 (that's SP5 in "old" money!). You need MySQL and Perl, the script comes in the box already. If you want to automate the process, you'll need CRON.NLM too!


Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

© 2014 Novell