Re: [Bacula-users] Backup MySQL databases

[Phil S. <al...@me...>]

On 10/25/11 05:13, Dimitri Gourdon wrote:
> Hi all,
> 
> I have some servers with MySQL databases. I want to know what is the 
> best way to backup these databases ?
> 
> For now, i think i will do mysqldump on each server to local disk and 
> after that, backup that files. Is there a better way ? MySQL module for 
> bacula, backup mysql files (.myd and .myi) directly,...? How are you 
> doing it ?

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]

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.


[1]  Which they *SHOULD* be; at this point in time, unless you are still
using one of the few specific features supported only by MyISAM such as
merge tables[2] or full-text indices, there is no realistic real-world
use case in which the correct answer to "Which MySQL primary storage
engine should I use?" is not "InnoDB."[3]

[2]  Merge tables are frankly a horrible hack which, in 90% of cases
where people typically use them, can be better replaced by table
partitioning and yield better performance.

[3]  Or Percona XtraDB, which they claim is as fast in Percona 5.1 as
InnoDB in MySQL 5.5.  However, I rather believe they're somewhat
comparing apples to oranges.


-- 
  Phil Stracchino, CDK#2     DoD#299792458     ICBM: 43.5607, -71.355
  al...@ca...   al...@me...   ph...@co...
  Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
                 It's not the years, it's the mileage.