If your database is large enough then any sort of hot backup
will lock the tables for too long. The method I use is to
use LVM to take a snapshot of the MySQL partition while
MySQL is stopped. All my attempts to snapshot a live MySQL
database resulted in inconsistent results...
The following is being used in an on-line production environment
with an 11GB database, and a bunch of smaller ones. This method
works on both Solaris 2.6 and Linux 2.4. Although the example
uses the Solairs mount command this is all encapsulated into a
perl script that works on both Solaris and Linux.
mysqladmin shutdown
/usr/sbin/mount -Fvxfs -o snapof=$snap_volume $snap_device $snap_mount
nohup $mysql_bin_dir/safe_mysqld < /dev/null &
cp -p -r $snap_mount/$dbname $snapshot/$dbname
/usr/sbin/umount $snap_mount
Using this the database is down for approximately 15 seconds.
The actual database copy takes 30 minutes, but because it is
copying from a LVM snapshot the MySQL server can be
running at the same time. There is no impact to the web site
because our queueing system automatically retries queries
when the server goes away; most read queries don't even
wait the 15 seconds because they are redirected to a read-only
copy of the database.
--- Matthew Costello
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
