Server based MySQL backup

I'm looking for a server-based backup tool for MySQL. This will be used to backup multiple databases serving multiple sites all based on a Windows 2003 server. The backups need to be automatic and destined for the local hard drive.

Note that CPanel or PHPMyAdmin backups aren't appropriate solutions in this case and what I'm really looking for is in an independent backup tool that runs from the Start menu with scheduling/restore simplicity. There seem to be a few out there but has anyone used one in anger and can report on the best or ones to avoid?

Jon

 

I currently use Navicat for all of my mysql backups. I have it backing up to a network file server, but the program itself is sitting on a Windows Server 2003 box. You can specify any local hard drive, network drive, and just about any other accessable location to backup a database to. It runs completely on the local machine, so it isn't subject to a lot of the restrictions that something would have running on the same machine as the DB itself. It is currently backing up about 100 databases daily, but you can set the schedule to whatever you want. Navicat also supports table level restores which is nearly impossible to find elsewhere, but extremely helpful if you lose a single table and don't want to restore an entire DB. It also compresses the backup file, so a 500GB database can get down to 50Gb or less in some cases.

The only drawback to doing it with this program is that each backup creates its own file, and it does not auto remove old backups. You may be able to create a task schedule or do a monthly audit or something similar to remove old backups.

Anyway, they offer a free trial. I highly recommend it in place of phpmyadmin and any other mysql GUI. The only difference between the enterprise and the standard version is the level of reporting. There is also a non-commercial version, but I think that it is missing the backup feature.
http://www.navicat.com/

 

You can create a simple php script to this.

<?php
 $username = "root";
 $password = "pass";
 $backupdir= "/backup/

 $link = mysql_connect($s, $username, $password);
 $db_list = mysql_list_dbs($link);
 while ($row = mysql_fetch_object($db_list))
 {
 $dbname = $row->Database;

 # Mysql Dump
 #$script = "mysqlhotcopy $dbname -u $username --password=\"$password\" --allowold $backupdir";
 $script  = "mysqldump --user $username --password=$password --create-options -B --hex-blob --order-by-primary -q -Q $dbname > $backupdir/$dbname.sql";
 $results = system($script,$retval);
 echo "RESULTS: $results\n RETURN VALUE: $retval\n";
?>

PHP:

You then setup a scheduled task to run that backup.php

You could also add copy or ftp functions to transfer the some place as well.

 

Thanks Jestep, I'll have a look at Navicat, that sounds just the ticket, especially the ability to restore a single table, which would be a likely restore situation in our case.

Little Jon, script based solutions aren't really appropriate for our purposes as the backups need to be administered - including restores if necessary - by non-PHP/MySQL bods. We also need a more robust method of checking that backups have been carried out successfully, partial restores, etc. etc. Our existing solution is script based running on CRON jobs and it's this we're eager to get away from to a more 'enterprise' level solution.

Thanks for the post anyway.

Jon