Hello,
On Tue, 25 Oct 2011 07:34:06 -0400, Phil Stracchino wrote:
>
> MySQLdump is a valid technique, and the simplest working technique.
> You
> should not, as a rule, back up the MySQL data directory at filesystem
> level. It is extremely unlikely to yield a consistent backup. If
> you're going to attempt this, issue a FLUSH TABLES WITH READ LOCK,
> snapshot the MySQL directory, release the lock, then mount the
> snapshot
> and back up the snapshot. Thanks to InnoDB's write-ahead logs and
> crash
> recovery features, this technique is generally safe *IF ALL YOUR
> DATABASES ARE IN INNODB TABLES*.[1]
With mylvmbackup, you can backup any type of engine which writes on
disk (don't expect to recover memory table...) as long as you flush
buffers to disk.
MyLVMBackup sends a flush tables with read lock, creates a snapshot,
releases the lock and then archives the snapshot.
That means that during the snapshot's creation, nothing will change
into any database.
That works with MyISAM too (because tables have been flushed to disk).
(I tested it in real situations)
> The big problem with MySQLdump is that while the logical dump is
> portable, it is slow to dump and slower still to restore. You might
> want to look into a third party tool such as Percona XtraBackup to
> make
> a consistent copy of your MySQL data directory, then back up that
> copy.
> In some very large servers I have tested it on, XtraBackup runs in
> about an hour where MySQLdump takes many hours. Unlike MySQLdump, it
> can perform incremental backups of the DB.
>
> You might also investigate MySQL Enterprise Backup. It is similar to
> XtraBackup, but more finished.
I performed some tests with a 150 GB database (mostly InnoDB + MyISAM).
mysqldump : to be coherent, we needed to stop the service during 15
minutes
mylvmbackup : the service was blocked during 3-5 seconds.
I tried to restore with both methods :
mysqldump : 20hours
mylvmbackup : 2 hours (including recovering 23h of binlogs ! I was
really unlucky this day)
There is some cons :
- A gzipped snapshot is much bigger than a gzipped plaintext file.
That means you have to reserve enough space to store the final archive
file.
- You can't split your datadir accross several mountpoints.
HTH.
Jérôme Blion
------------------------------------------------------------------------------
The demand for IT networking professionals continues to grow, and the
demand for specialized networking skills is growing even more rapidly.
Take a complimentary Learning@Cisco Self-Assessment and learn
about Cisco certifications, training, and career opportunities.
http://p.sf.net/sfu/cisco-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
