A Critical Warning If You Are Using InnoDB Hot Backup
Feb 12, 2009 / By Sheeri Cabral
If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.
Except for one thing.
The binary log file and position do not appear in their rightful place. Here’s a snippet of the output from the backup:
innobackup: MySQL binlog position: filename 'Warning', position (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead 090210 03:55:04 innobackup: innobackup completed OK!
That’s pretty misleading — looks like the backup completed OK, but it did not show us the binary log position. What about the
[mysql@db3:~] more ibbackup_binlog_info Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead [mysql@db3:~]
Yikes! What happened to the binary log position and file information?
Have no fear, it’s actually in another file.
I was extremely happy to find the information in
[mysql@db3:~] more mysql-stdout innobackup hello 3 innobackup hello 3 innobackup hello 4 innobackup hello 4 innobackup hello 5 innobackup hello 5 Note (Code 1051): Unknown table 'ibbackup_binlog_marker' innobackup hello 6 innobackup hello 6 Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead innobackup hello 7 innobackup hello 7 innobackup hello 8 innobackup hello 8 innobackup hello 9 innobackup hello 9 innobackup hello 10 innobackup hello 10 innobackup hello 11 innobackup hello 11 File Position Binlog_Do_DB Binlog_Ignore_DB db2-binlog.001980 495985099 innobackup hello 12 innobackup hello 12 innobackup hello 13 innobackup hello 13 innobackup hello 14 innobackup hello 14
I filed a bug report here:
The bug is an artifact of the eventual deprecation of TYPE in favor of ENGINE. There’s no reason for ibbackup to continue to use TYPE; while the product works well, it is much more expensive than it warrants.
(While I searched for this bug before creating it, I would like to say that it’s completely possible that this is “well-known” and “well-documented”, and I did not find it. Or, that this client is using an old version of ibbackup. However, there may be many folks in this same position, and this is a very subtle bug — the backup looks fine, it’s a full backup, it will restore properly, etc. but if you want to start replicating the backed up server, you will have a hard time finding the binary log information.