View Full Version : Innodb tables causing raw LVM snapshot backups to switch to logical mysqldump backup

May 24th, 2011, 02:56 PM
When I try to perform a raw LVM snapshot backup, I get messages from mysql-zrm-backup that say "The database(s) foo bar foobar will be backed up in logical mode since they contain tables that use a transactional engine". This is defeating the purpose of taking a raw snapshot backup.

The documentation seems to indicate that a raw LVM snapshot is valid to use when all the files are on the same logical volume even when using InnoDB based tables.

All these databases have Innodb based tables. I specify to use a seperate Innodb file per table option in my.cnf (innnodb_file_per_table). These are all on an LVM volume that is replicated using DRBD to a failover system. I am able to manually create an LVM snapshot of the logical volume that houses my MySQL database.

I have some databases that have use an InnoDB engine that do not appear in the list of databases causing the backup to use a logical backup.

I am running ZRM 2.2 community edition.

Is the backup command working correctly?

Why would some databases using the same transactional engine, InnoDB, appear not to cause its database to require a logical backup while others do?

Since these databases, logs, etc., are all on the same logical volume, should I expect an LVM snapshot to be a valid and reliable backup method? If not, why not?

Is there a way to force it to take a raw backup assuming a raw backup is valid for my databases?

May 24th, 2011, 04:43 PM
Can you check if the LVM snapshot backup failed? If the LVM snapshot backup fails or is incorrectly configured, it will switch to logical backup.


May 24th, 2011, 04:56 PM
The process decided to run both a raw and logical backup before either backup ran. The raw backup only specified the databases not listed for the logical backup. Therefore the decision to perform a raw and logical backup had already been made before either of them ran. Thanks for the idea though.

UPDATE - after much tracing of the mysql-zrm-backup process, I see a call to the lvm-snapshot.pl plugin to "checkIfDirIsOnLogicalVol" (--action get-vm-device-details). It looks like the logic is expecting the filesystem name for the MySQL directory as shown by the /bin/df command to appear as /dev/mapper/VOLUMEGROUP-LOGICALVOLUME if the MySQL data is on a logical volume. However, in my case the filesystem name returned shows /dev/drbd1 since my logical volume is mounted through the DRBD block device /dev/drbd1 I am thinking that lvm-snapshot.pl GetSnapshotDeviceDetails() routine is unable to determine that the MySQL data directory is on a logical volume (it is) and determine the volume group and logical volume names. While I have not finished tracing this, I suspect that it is returning indicating the InnoDB shared content is not on LVM, thus causing the backup method to revert to logical rather than raw. I think it is only doing this when there are InnoDB tables, but also think I have examples where this does not happen (still have more to understand).

I still have more tracing to perform to actually confirm my suspicion but this looks like a promising cause. I will have to see if I can figure out a means for this routine to understand DRBD mounted volumes. Using the DRBD volume does not impact my ability to create a LVM snapshot of the logical volume containing the MySQL data.

A possible solution could be to create and set options to specify these values and bypass the check and discovery of the LVM values if unable to discover them when using DRBD block devices.

June 7th, 2011, 04:12 PM
I've got a similar issue, but in my case, there is no indication even with verbose logging that there's even a failure.

The snapshot backup log entries look like this.
A check of the directory /u01/zmanda/backup shows that the raw backup directory is definitely the right size for the "bigdatabase" database, but zrm proceeds with a logical backup anyway.

Tue Jun 07 22:55:45 2011: DAILY_FULL:backup:INFO: PHASE START: Creating raw backup
Tue Jun 07 22:55:45 2011: DAILY_FULL:backup:INFO: Command used for raw backup is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy=/usr/bin --user="secureuser" --password="*****" --host="OBFUSCATED" --port="3306" --quiet mysql "/u01/zmanda/backups/DAILY_FULL/20110607215334" > /u01/zmanda/tmp/VDxSbkUmkq 2>&1
Tue Jun 07 22:56:10 2011: DAILY_FULL:backup:INFO: raw-databases=mysql
Tue Jun 07 22:56:10 2011: DAILY_FULL:backup:INFO: PHASE END: Creating raw backup
Tue Jun 07 22:56:10 2011: DAILY_FULL:backup:INFO: PHASE START: Creating logical backup
Tue Jun 07 22:56:10 2011: DAILY_FULL:backup:WARNING: The database(s) bigdatabase will be backed up in logical mode since they contain tables that use a transactional engine.