MySQL Backup Warning Running MySQL Instance Detected. MySQL Data Will Be Inconsistent
Symptom
Backups succeed with a WARNING:
Cause
A running MySQL instance (mysqld) was detected on your server and you have no MySQL Instance configured in the CDP Server for it. Backing up MySQL database files without a MySQL instance added to the CDP Server causes no synchronization of the databases during backup. Database files restored without a properly configured MySQL instance in the CDP Server may be corrupt or unusable. MySQL Backups are licensed as a add-on product to CDP Agents.
Why the MySQL Add-On Is Important
MySQL is very good at recovering from unclean shutdowns so it will detect on startup that its an unclean shutdown when you recover from an unclean backup and automatically repair your corrupt MyISAM tables.
Without our MySQL Add-on you will loose whatever changes were being made to the database at the time of the backup.
Note that the R1Soft MySQL backup process is different from a normal hot backup.
MySQL Hot Backup Process (mysqlhotcopy)
1) Lock All tables.
2) Flush all table changes to disk.
3) Copy all raw database files (could take a long time.)
4) Unlock tables.
R1Soft MySQL Hot Backup Process
1) Lock All tables.
2) Flush All tables changes to disk.
3) Snapshot the raw partition using the r1soft near-CDP device driver /dev/backupdriver.
Note: This takes less than a second.
4) Unlock tables.
5) If seed copy all raw MySQL files from point-in-time snapshot of volume/partition. If Not a seed copy only changed blocks from point-in-time snapshot of volume/partition.
Note this step can take a long time if you are doing a seed or have a lot of changes to your database like importing a bunch of data. Thats ok since we aren't holding table locks anymore. We also are getting the benefits of a totally consistent copy of MySQL raw table files by reading from the block level point-in-time copy-on-write snapshot that /dev/backupdriver maintains for us.
This is significant because your application and queries can continue while the backup is taking place (step 5).
With typical hot backup queries will timeout, web pages will fail to load and systems will be down for the duration of the backup operation.
It is not safe to backup MySQL without lock and flush while its running. Here is a helpful article that tells you how to get hot backups of MySQL using LVM snapshot. (Note: R1Soft does Not use or Require LVM for MySQL Hot Backups.) The article emphasizes the importance of a lock and flush during a hot backup.
Except for the fact that its not near-continuous the process described in this article is very similar concept to the backup process that R1 uses. R1 takes a snapshot of your volume. LVM takes a snapshot of your volume too... you just have to be running LVM where R1 can do it for any storage type... not just LVM.
http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
Note the author of this informative article has in bold: FLUSH TABLES WITH READ LOCK as the first step before creating the snapshot.
Here is another text about it in the MySQL 5 certification study guide about the importance of lock and flush during hot backup of MySQL:
http://book.itzero.com/read/others/0508/MySQL.Press.MySQL.5.Certification.Study.Guide.2nd. Edition.Aug.2005.eBook-DDU_html/0672328127/ch32lev1sec3.html
Resolution
If you have licensed the MySQL Add-On Module, configure a MySQL instance for each running MySQL daemon on this Agent.