Cliff Hirsch wrote:
How do you back up your MySQL InnoDB database?
My DB is on a dedicated server in a hosted environment. From what I
can gather, I have two choices:
1. Stop the DB and copy the files at say 2am to reduce user disruption
2. Use the InnoDB hot backup program (with InnoBackup to capture
the MyISAM files in sync)
A third choice, which would be my preference if I didn’t have budget
constraints, would be to use a replication server for backup and then
stop that to do files copies. As I understand it, it would catch up
with the log files once the DB starts up again.
mysqldump is viable for large backups. We use a combination of
replication, logical volume copying and mysqldump. We stop the
replicated database, create a logical copy (Almost instant), restart the
database. We start a second instance of MySQL and do a mysqldump from
that instance. This avoids any issues with table/row locking ect ect
ect. At the end we delete the logical copy. Rinse and repeat. We have a
mix of InnoDB and MyIsam tables. The only issue here is that loading the
SQL back in from the dump takes a long time. If you have a lot of
indexes it will take even longer. We have found this solution to be very
reliable and it is "free". This solution could be implemented without
the replication and would be far faster they copying the files or
dumping directly.
I would be wary of copying innodb files. If for some reason you have to
restore the database to a different directory or different machine you
results may vary. Plus the backups will be much larger because all the
index information is included int he copy. The dumps do not include the
populated indexes so they can be smaller and more portable. dump files
gzip down to very reasonable files sizes.
I have to thank Partha and Hans Z for turning us on to this solution.
Hans Kaspersetz
Cyber X Designs a NJ Web Development Company.
http://www.cyberxdesigns.com
_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
