What does MySQL Agent add-on actually do? Do I really need it? Won't CDP get a good backup of my MySQL databases without it?
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!
R1Soft CDP for Linux does perform point-in-time snapshot (A.K.A. open file backups) for Linux. The snapshot means we take a flush all uncommitted changes to the file system still in memory to disk and snapshot the state of the file system. It is not possible for us to know what Linux or Windows applications have open in memory that has never been written to disk or may be partially written. Its all application specific.
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.
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 looses 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.
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.
These steps are the same for both MyISAM and InnoDB. If a database contains only InnoDB tables then the FLUSH TABLES and READ LOCK queries are only needed to synchronize the MySQL binary log if it is enabled..
- Continuous Data Protection Server (CDP Server) connects to CDP Agent on target MySQL server to request backup operation (synchronization).
- Agent Repeats the Following Steps Three Times:
- execute FLUSH TABLES
- sync disk containing MySQL data directory (uses proprietary Linux IOCTL call on R1Soft CDP device driver to sync only MySQL data directory disk)
- execute FLUSH TABLES
- Execute FLUSH TABLES WITH READ LOCK After the previous step this typically takes less than 1 second
(Note: unusually long running queries on your MySQL server can cause this step to take longer)
- Execute FLUSH LOGS to start a new binary log file that corresponds with the CDP backup for granular transaction level recovery.
- Create a Disk/Volume Snapshot using the proprietary R1Soft Linux CDP Device Driver. This typically takes less than 1 second.
- UNLOCK TABLES After this point MySQL queries can proceed again.
- Read Block Level Deltas from the Snapshot and Send them over the network to the CDP Server Disk Safe Repository
- Remove the Snapshot
MySQL is only locked for steps 3) and 4). These steps combined typically take less than 1 second even on write intensive MySQL servers.
Step 6) is where the Continuous Data Protection comes in. Using a proprietary low level Linux device driver the R1Soft Linux Agent knows in advance what block level changes where made to the MySQL data files for MyISAM and InnoDB. The backup window only consists of reading changed blocks from disk. Delta computation meaning determining what files or blocks have changed is done real-time as the system is running.