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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
