A Critical Warning If You Are Using InnoDB Hot Backup

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 ibbackup_binlog_info file?

[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-stdout:

[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:
http://bugs.mysql.com/bug.php?id=42812

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.

Comments are closed.