mySQL Automatic Backup Scheme

If you are on a linux server, put the backup script onto the crontab, and set it to run as frequently as you wish.

http://www.linux-tutorial.info/modules.php?name=MContent&pageid=78

I'm sorry, I didn't include my server specs.  I'm on a Windows 2003 server running the latest versions of both PHP and mySQL.

I don't know if Windows has an equivalent of the crontab, but you see what they have here:

http://cronw.sourceforge.net/

I'm not exactly sure how this helps me.

The crontab is a system feature that runs any specified script at specified times.  Just create your backup script and set it to run daily or as often as you like.

Ok, I'm completely new to mySQL... I'm a MSSQL guy, only started using mySQL a month ago.   I need complete details please.

An excellent tutorial with all the details you will need to understand how to use the crontab:

http://www.linux-tutorial.info/modules.php?name=MContent&pageid=78

For windows users, a windows version of the crontab feature:

http://cronw.sourceforge.net/

This quesion has little to do with mysql.  I use a perl program to do my backups, attached.  If you do not have perl available, write the equivalent in php.

#!/usr/bin/perl -w
#
#
# Using Perl DBI connection to Mysql.
##############################################################################

use DBI;
print "toastbackup started.\n";

$db = "your db name";

# your username
$host = "localhost";
# = "localhost", the server your are on.

$user = "your db connection name";
# your Database name is the same as your account name.

$pwd = "your db password";
# Your account password.

# connect to the database.
$dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pwd) or die "Connecting : $DBI::errstr\n ";
print "DBI->connect is ok.\n";

####  Backup database
#
system("mv /usr/home/yoder/backup/dbbackupTOAST.sql /usr/home/yoder/backup/dbbackupTOAST.sql.old");
system("/usr/local/bin/mysqldump -hlocalhost -u$user -p$pwd --opt yoder_toastmasters > /usr/home/yoder/backup/dbbackupTOAST.sql");
###print "Backing up Toastmasters Magic database is done. The backup file is stored at ~/backup/dbbackupTOAST.sql.\n";
### Now, disconnect from the database
$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";
print "dbbackup completed";
exit;

Select allOpen in new window

I appreciate that but what script would run a backup?  You are not explaining anything you are just throwing links at me.  

I'm looking for a windows based application that will perform a mysql backup.  I'd rather not install multiple applications.  There's got to be something out there that performs this 1 task.  It seems like something that should be common.

Also, a part to my question involved the possibility of doing a backup by sync'ing 2 mysql servers.. like, could i use my office server to replicate the mysql data from my hosting server?  possibily even roll over the data stream if the primary server is available?

Basically, how do people typically secure mysql?  What are common ways this is done?

Thanks.

Ok here's a question that could make a big difference.  Are mysql database files locked?  In other words, is my pre-existing Tivoli backup software backing up the mysql data files?  With MSSQL I know data files are locked as long as the service is active but i'm not sure if that's the same in mysql.  If it's already being backed up, my concern would be eliminated.

If you are using InnoDB tables, you cannot simply backup the "files" on a running MySQL server. You must shut down the server and copy all the files, including the mysql DB itself (which uses MyISAM tables). If Tivoli copies the files while MySQL is running, your back will be inconsistent.

On Windows, the best way is using mysqldump. Some backup solutions have MySQL backup agents, but I have not used them. You can use a batch file to do the backup and then have Tivoli backup to whatever media. Keep in mind that mysqldump will lock the tables as it is dumping, unless you force it not to.

Here's a sample backup.bat

FOR /F "tokens=1-4 DELIMS=/ " %%F IN ('date /T') DO (set v_date=%%F%%G%%H)
FOR /F "tokens=1-4 DELIMS=: " %%F IN ('time /T') DO (set v_time=%%F%%G%%H)
set fname=database_backup_%v_date%_%v_time%.sql
echo %v_time%
echo %fname%
mysqldump.exe --add-drop-table --hex-blob -uusername -ppassword --all-databases > %fname%  

That's fantastic.  I installed the workbench, hit manage databases and connected the database.

What's my next step?  I don't see anything relating to any of the databases in mysql even after adding the connection.  What am I looking for?

MySQL Administrator - while discontinued, has the backup tools.

http://downloads.mysql.com/archives.php?p=MySQLAdministrationSuite

It will not take consistent InnoDB backups from a running server however.

btw, this is a production server so i installed MySQL Workbench 5.1 GA... should i try 5.2 beta?  it seems to be the replacement for "mysql administrator"

I've installed v5.2 and am amazed.  This looks like the piece i've been missing to mysql.   I'm sure I could research this but if anyone wants to point me exactly where to go in here to create an automated backup, I'll assign points.

You can try SQLYog.

http://www.webyog.com/en/sqlyog_feature_matrix.php

The Enterprise and Ultimate versions are commercial, but well worth the cost since they feature scheduled backups. It is by far, the most comprehensive MySQL client I have seen.

Also look at Zamanda Backup for MySQL. http://www.zmanda.com