Typically we have recommended using MySQL Administrator to backup the MySQL databases we use for our Denika, Logalot, and WebNM applications. This is because typical backup processes tend to cause database corruption to MySQL database tables when there is contention between the data backup application and MySQL Server locking the files.

However, Scrutinizer, our NetFlow analyzer, is very data intensive and therefore can have an extremely large database. Database sizes easily exceed 50GB for some customers. MySQL Administrator does not handle these large databases very well. The backup process using MySQL Administrator can take days, even up to or more than a week, to complete. That is not an acceptable database backup solution. By the time the database backup has completed, the data is obsolete. And restoring a database that size is not possible using MySQL Administrator, as it tries to load the extremely large database backup file into memory and fails.

So, for Scrutinizer database backups, we recommend using Windows xcopy command. The user would exclude the Scrutinizer database (x:\scrutinizer\mysql\data\) from their regularly scheduled disk backup and then, using xcopy, copy the Scrutinizer database to a location that is backed up by their disk backup routine.

Use the windows command xcopy to hotcopy the MySQL data files to the backup directory.

C:\>xcopy /D /E /I /Y /Z “\SCRUTINIZER\mysql\data” “\SCRUTINIZER_backup”

The above command will only copy files that have changed. The initial copy will need to duplicate the entire DB. Subsequent runs will only copy files that have been modified since the previous run.

*** Where \SCRUTINIZER_backup is a directory included in the regularly scheduled disk backup.

For full backups on a weekly or monthly basis, use the following command:

C:\>xcopy /E /I /Y /Z “\SCRUTINIZER\mysql\data” “\SCRUTINIZER_backup”

This will copy all database files regardless of last date updated.

Note: The incremental backup will also have a full backup of the database in the \SCRUTINIZER_backup directory, as it initially backs up the complete database, then adds/replaces only files that have been updated since last backup.

You can then create a batch file with the xcopy command(s) and schedule it to run at a time when network traffic is minimal (ie. 1:00am or 2:00am) using Windows Scheduler or Cron (if installed).

Joanne Ghidoni author pic

Joanne Ghidoni

Joanne is a Software Quality Assurance Engineer at Plixer. She has also held positions as Technical Support Engineer and Sales Engineer since joining Plixer in 2005. Prior to joining Plixer, Joanne has had numerous positions in the IT field, including data entry, computer operator, PC coordinator and support, mainframe programmer, and also Technical Support and web programmer at Cabletron Systems. In her spare time, Joanne enjoys traveling, always seeking out new and interesting places to visit.

Related

Leave a Reply