CDP for MySQL – Why MySQL Backups are Not Safe Without It

Posted by R1Soft on Nov 19, 2009 11:47:00 AM

If you are using CDP Server and MySQL and you are not using the MySQL add-on module you better start.

I often hear CDP users telling me that they don’t need the add-on because it’s safe to perform an online backup of MySQL without it. Usually it goes like this….

“I’m not paying for your MySQL add-on. It is not necessary. Besides MyISAM is the only thing that can become corrupted during an online backup and it has a repair utility. So even if something does get corrupted I can repair it. And I have had that repair utility save my butt before after a power failure, it works.”

This is not totally accurate. Yes, MyISAM has a repair.  Yes, it can some times save your butt after a crash.  There is more to it.

After seeing people lose their data that could have completely prevented it with the MySQL add-on you are going to start hearing R1Soft scream louder… YOU NEED THE ADD-ON FOR MYSQL.

Here is why…

If you use MySQL you must use the R1Soft CDP for MySQL Add-on

IF YOU USE MyISAM AND DO NOT USE THE Linux MySQL ADD-ON YOU WILL GET CORRUPTED BACKUPS FOR SURE IF THERE ARE ANY WRITES TO THE DATABASE DURING THE SNAPSHOT PROCESS!

The MySQL MyISAM database makes changes to the MyISAM files and it does NOT use an on-disk rollback journal file. This means MyISAM files will be corrupted if they are copied while they are being written to and a special query “FLUSH TABLES WITH READ LOCK” is not precisely coordinated with the file system snapshot performed by CDP.

The MySQL add-on requires you to specify a MySQL account with administrative privileges that is used by the Linux Agent to FLUSH TABLES WITH READ LOCK before the file system snapshot is taken. And the lock must be held until the snapshot completes. If we do not do this your MyISAM tables will be corrupted.

InnoDB Tables Need The MySQL Add-on Too

The InnoDB table space uses an on-disk write journal and is mostly ACID compliant when it writes to the database. This means InnoDB is mostly safe from crashes and unclean shutdowns. It also means writes to the InnoDB table space are always consistent even when performing an R1Soft CDP backup WITHOUT the MySQL Add-on. Mostly is mostly. And you still need the Add-on.

Here is why: even the MySQL InnoDB storage engine which implements ACID compliant transactions and data integrity is not truly ACID compliant. If MySQL crashes or loses power while a Database Definition Language (DDL) statement is issued like ALTER TABLE even InnoDB can be corrupted. For more on MySQL journaling details we highly recommend the book “High Performance MySQL”. You may also like the MySQL Performance Blog www.mysqlperformanceblog.com

Locking tables during the snapshot on Linux also locks tables from ALTER TABLE queries which is important to InnoDB. You even need the MySQL Add-on to correctly protect InnoDB without corruption.

Wait But My Corrupt MyISAM Tables Can be Repaired After Restore Right?

Corruption can go unnoticed until the corrupted portion of the database is needed. Corrupted MyISAM tables can cause MySQL to crash or hang cascading into further database corruption. MySQL provides the myisam_recover option which checks MyISAM tables that were not cleanly closed and checks may trigger a repair. The MyISAM repair process at a high level is similar to a file system being meta-data consistent. The table structure is sound but row content may still be corrupt.

It is true that often a MyISAM repair can make a corrupt table work perfectly. You can not always count on the MyISAM repair / check to correctly recover your data. It never makes any promises it can and it is a fact there is a chance it will not be able to recover / repair your data.

What about other Linux backup applications that do NO Snapshot like tar and rsync are they safe?

No! They are just worse off. tar and rsync and just about every other Linux backup application besides R1Soft CDP are reading from files while other applications are writing to them. They are actually WORSE OFF. There is no point-in-time snapshot so you get a mix match of changes to the file system in your backup and there is no protection at all to backing up open files.

Learn More

KB Article on MySQL Backups – http://wiki.r1soft.com/x/shJE

Topics: Continuous Data Protection, mysql, linux

Recent Posts

Posts by Topic

see all