mySQL Automatic Backup Scheme

I've been looking for a way to automatically backup my mysql database.  For now I'm using MyPHPAdmin to export the databases but I'd love to be able to set up either a failover server or an automatic backup.

Does anyone have any experience with hosting and protecting a mysql database?  Thank you!
LVL 6
Shaun RiemanPresident / CEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
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
0
Shaun RiemanPresident / CEOAuthor Commented:
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.
0
Cornelia YoderArtistCommented:
I don't know if Windows has an equivalent of the crontab, but you see what they have here:

http://cronw.sourceforge.net/
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Shaun RiemanPresident / CEOAuthor Commented:
I'm not exactly sure how this helps me.
0
Cornelia YoderArtistCommented:
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.
0
Shaun RiemanPresident / CEOAuthor Commented:
Ok, I'm completely new to mySQL... I'm a MSSQL guy, only started using mySQL a month ago.   I need complete details please.
0
Cornelia YoderArtistCommented:
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/

0
Cornelia YoderArtistCommented:
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;

Open in new window

0
Shaun RiemanPresident / CEOAuthor Commented:
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.
0
Shaun RiemanPresident / CEOAuthor Commented:
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.
0
gmckeown99Commented:
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%  

0
pareshprajapatiCommented:
Simply use Mysql GUI Tool available on mysql site.
http://www.mysql.com/downloads/workbench/

This will allow you to do scheduled backups and many other operations.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shaun RiemanPresident / CEOAuthor Commented:
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?
0
gmckeown99Commented:
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.
0
Shaun RiemanPresident / CEOAuthor Commented:
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"
0
Shaun RiemanPresident / CEOAuthor Commented:
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.
0
gmckeown99Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.