How is everyone backing up large MySQl database from a cpanel server?
I do have 2 very large forums databases that probably won't restore from a WHM/cpanel restore. For now I am running a scheduled MySQL dump via cron to the backup drive and from there either download a copy to my home office.
I now started using DiskSync and wanted to see if there is more elegant way of backing up large Mysql databases.
Thanks for any feedback.
Chris
Full Version: DiskSync and MySQL databases
Greetings,
As with any backup solution, you will need to make use of "mysqldump" to make routine dumps of your databases for the backup software to pick up.
If you create your scripts such that the same file is overwritten each day (i.e. not deleted and recreated fresh), DiskSync should pick up the incremental changes to the database each day.
As with any backup solution, you will need to make use of "mysqldump" to make routine dumps of your databases for the backup software to pick up.
If you create your scripts such that the same file is overwritten each day (i.e. not deleted and recreated fresh), DiskSync should pick up the incremental changes to the database each day.
You can dump all databii with "mysqldump --all-databases", however, you might find it more convenient to have a separate mysqldump file for each database. You can script that manually, however, it's a bit nicer if you use a utility to discover all databases, and rotate the mysqldump files so that you always have a previous mysqldump file that is static while creating a new set of dump files on a rotating basis (though proper selection of backup windows will reduce the chances of the mysqldump and disksync overlapping).
This is one area where MySQL shows a weakness compared to higher end database systems such as Oracle. Oracle has a "hot backup mode" which provides for virtual quiescence of the data, capturing transactions during the backup window to the log files, and automatically replaying the log files at the conclusion of the backup window. I haven't read the specs on the DiskSync Oracle agent, however, my guess is that it uses this capability. I wouold expect that MySQL will eventually have something similar to Oracle online backup.
I use the following perl script to drive a MySQL dump for each database on my system, and rotate a new copy each day fo the week.
This is one area where MySQL shows a weakness compared to higher end database systems such as Oracle. Oracle has a "hot backup mode" which provides for virtual quiescence of the data, capturing transactions during the backup window to the log files, and automatically replaying the log files at the conclusion of the backup window. I haven't read the specs on the DiskSync Oracle agent, however, my guess is that it uses this capability. I wouold expect that MySQL will eventually have something similar to Oracle online backup.
I use the following perl script to drive a MySQL dump for each database on my system, and rotate a new copy each day fo the week.
CODE
#!/usr/bin/perl
use DBI;
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime();
print "Backup start: $hour:$min:$sec $mon/$mday/$year\n";
$dir="/drv3/sql_backup/day_$wday";
eval
{
$dbh=DBI->connect("DBI:mysql:mysql:localhost","root","rootpassword",{ RaiseError=>1});
$sth=$dbh->prepare("SHOW databases");
$sth->execute;
$sth->bind_columns(\($database));
while($sth->fetch)
{
$cmd="mysqldump $database > $dir/$database.sql";
print "DIR=$dir CMD=$cmd\n";
if ( ! -f $dir )
{
mkdir($dir);
}
system($cmd);
}
$dbh->disconnect();
};
if($@)
{
print "Database errro $@\n";
}
print "Backup end: $hour:$min:$sec $mon/$mday/$year\n";
use DBI;
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime();
print "Backup start: $hour:$min:$sec $mon/$mday/$year\n";
$dir="/drv3/sql_backup/day_$wday";
eval
{
$dbh=DBI->connect("DBI:mysql:mysql:localhost","root","rootpassword",{ RaiseError=>1});
$sth=$dbh->prepare("SHOW databases");
$sth->execute;
$sth->bind_columns(\($database));
while($sth->fetch)
{
$cmd="mysqldump $database > $dir/$database.sql";
print "DIR=$dir CMD=$cmd\n";
if ( ! -f $dir )
{
mkdir($dir);
}
system($cmd);
}
$dbh->disconnect();
};
if($@)
{
print "Database errro $@\n";
}
print "Backup end: $hour:$min:$sec $mon/$mday/$year\n";
QUOTE (nsusa @ Apr 26 2006, 03:23 PM) 
How is everyone backing up large MySQl database from a cpanel server?
I do have 2 very large forums databases that probably won't restore from a WHM/cpanel restore. For now I am running a scheduled MySQL dump via cron to the backup drive and from there either download a copy to my home office.
I now started using DiskSync and wanted to see if there is more elegant way of backing up large Mysql databases.
Thanks for any feedback.
Chris
I do have 2 very large forums databases that probably won't restore from a WHM/cpanel restore. For now I am running a scheduled MySQL dump via cron to the backup drive and from there either download a copy to my home office.
I now started using DiskSync and wanted to see if there is more elegant way of backing up large Mysql databases.
Thanks for any feedback.
Chris
If you install MySQL on your home office system you can do this directly. You should be able to use Windows MySQL dump to dump a Linux database and vice-versa, however, be sure the MySQL versions match as MySqldump does not always work cross-rev.
mysqldump databasename -uusername -ppassword -hservername > dumpfile.sql
QUOTE (Rob Boudrie @ Apr 14 2007, 09:29 PM)
This is one area where MySQL shows a weakness compared to higher end database systems such as Oracle. Oracle has a "hot backup mode" which provides for virtual quiescence of the data, capturing transactions during the backup window to the log files, and automatically replaying the log files at the conclusion of the backup window.
What about mysqlhotcopy? I've not used it, but I thought it worked similar. Granted, I'm not a MySQL genius, either.
QUOTE
I haven't read the specs on the DiskSync Oracle agent, however, my guess is that it uses this capability. I wouold expect that MySQL will eventually have something similar to Oracle online backup.
We do not offer any Oracle Agent Plug-In at this time. Until recently, the framework to support it existed within the DiskSync Agent for each architecture that would support Oracle, but the Oracle Agent Plug-In was only available for Solaris on SPARC architecture.
QUOTE (jscott @ Apr 16 2007, 09:27 PM)
What about mysqlhotcopy? I've not used it, but I thought it worked similar. Granted, I'm not a MySQL genius, either.
We do not offer any Oracle Agent Plug-In at this time. Until recently, the framework to support it existed within the DiskSync Agent for each architecture that would support Oracle, but the Oracle Agent Plug-In was only available for Solaris on SPARC architecture.
We do not offer any Oracle Agent Plug-In at this time. Until recently, the framework to support it existed within the DiskSync Agent for each architecture that would support Oracle, but the Oracle Agent Plug-In was only available for Solaris on SPARC architecture.
There are a couple of issues with MySQL Hocopy. First, it only works on certain table types (and does not include the "innodb" table type - critical if you are into automatic maintenance of referential integrity). Secondly, it actually makes on on-disk copy of the tables rather than quiescing the underlying table in the style of Oracle hot backup mode. That Oracle feature allows you to copy the actual database table to backup media without first making a copy.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.