Concerns and What Does Not Work in XtraDB Backup

Jun 30, 2009 / By Sheeri Cabral

Tags: , ,

A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.

The first few points remain the same — the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl.

However, we did figure out why Xtrabackup had to be run as the mysql user:

Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:

xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.

InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.

When the new version is released, we will be sure to test it so that we can run the backup as a read-only user, and report back.]

On the one hand, Xtrabackup is a free tool. On the other hand, modifying InnoDB’s underlying files risks corrupting all the InnoDB tables in the system. Which is a tricky situation when it is your backup tool that might cause corruption that is beyond repair, as you do not know if you can trust your backups.

Regarding the complaint Dan R commented on the previous post that innobackupex could stream the backup to gzip, and helpfully gave the syntax. Shlomi Noach also pointed out streaming as a feature of Xtrabackup that ibbackup does not have. However, Gerry Narvaja, a co-worker noted (and commented):

I’ve been trying to install xtrabackup/innobackupex for a customer and I’m finding a few glitches, especially w/ streaming:

We use innoback(ex) wrapped in our own scripts to allow for rotation and other operations. We also use ZRM for some installations, so this would apply to integration with this tool as well. These are the glitches I found:

1. Using streaming by piping it into ‘gzip’ masks the return code from innobackupex.pl. Since gzip will almost always return 0, you can’t rely on it to determine backup success.

2. The next alternative would be to review the innobackupex.pl’s output for the OK at the end. But since it redirects the output to ‘stderr’ to allow for streaming, you need to add “2> innobackupex.log” before piping and grep for the “OK” at the end.

and noted that there were some limitations:

innobackupex script is limited in the type of options you can specify compared to what the xtrabackup executable supports. I find this annoying since it limits the directories where you can have the backups, data directories and logs.

Xtrabackup doesn’t work for MySQL v4.1. In the Percona forums there was a suggestion that the 5.0 patch should work. This is true, but xtrabackup.c has other dependencies on 5.x definitions and structures I didn’t have time to review. Baron Schwartz correctly suggested that these dependencies might be trivial in a tweet directed to myself. I’ll post my findings to the Percona forums and hopefully we can soon have a patched version.

18 Responses to “Concerns and What Does Not Work in XtraDB Backup”

  • VadimTK says:

    Just to clarify XtraBackup does change destination ibdata1, not source. And it works the same way as ibbackup, so there is no difference.

    As for script issues – we are on Lauchpad and we have source code available and we accept patches.

    As for 4.1 it should be just matter of small compilation fixes, nothing unresolvable. Again – patches are welcome, or if Pythian interested to see it implemented – you have our contacts :)

  • Andrew says:

    What do you mean that it writes to data dictionary? This seems to be a scary, but unqualified statement. You mean “CREATE TABLE mysql.ibbackup_binlog_marker(a INT) TYPE=INNODB;” that innobackupex does? I think you’ll find that just running ‘xtrabackup’ (and not the innobackupex perl wrapper) will leave ibdata1 unmodified.

  • krteQ says:

    I think you can use bash option “pipefail” to solve the problem with return codes.

    $ bash -o pipefail -c “echo aa | grep b | gzip | gzip -d”; echo $?
    1

  • Mikael Fridh says:

    Here’s something for you, and Gerry;

    A friend of mine (Hakan) recently hinted me on a possibly useful bash variable: $PIPESTATUS and I came up with this in one of our backup scripts:

    $_rc = system(‘mysqldump -ujada -pjada -hjada | gzip -3 -c > /tmp/mysql_jada.sql.gz; exit ${PIPESTATUS[0]}’);

    bash(1)

    PIPESTATUS
    An array variable (see Arrays below) containing a list of exit status values from the processes in the most-recently-executed foreground pipeline (which may contain only a single command).

    So, I guess for even more advanced (Longer!) pipes you could walk that array and check for all return codes.

  • Sheeri Cabral says:

    Vadim,

    It works differently from ibbackup, just from the fact that it requires the mysql user, and ibbackup does not.

    Can you please explain why that is?

  • Sheeri Cabral says:

    Andrew,

    It is scary and unqualified, because we do not actually know what it is doing.

    I don’t mean the CREATE TABLE statement; I mean there’s a direct write to the ibdata1 file by the process.

  • Xaprb says:

    Sheeri, the source is there. If Vadim says it doesn’t touch ibdata1, why do you still say it does without pointing to the proof? He might be wrong, but you can check easily.

    If you don’t want to read the source, you can just set file permissions such that it’s allowed to read but not write ibdata1 and see if it still works. So there are two easy ways to check what’s happening.

  • Sheeri Cabral says:

    Baron, we *have* set it that way — We had it with our “pythian” user, which had “r-x” permissions to the ibdata1 file (via being in the mysql group, and group-level r-x permissions) and we got a “permission denied” error for the ibdata1 file.

    We are able to run the InnoDB Hot Backup, on the *same* system, as the “pythian” user. So that’s why we don’t believe Vadim, and the proof is in the previous blog post, which is linked at the beginning of this post.

    And it’s not that I don’t want to read the source, it’s that I haven’t gotten around to it yet. It was more important to correct the impression we’d given that Xtrabackup is not worrisome first, and then we could give the level of worrisome-ness.

    We are still running this on one of our clients who is willing to take this risk. They restore their production backups weekly to their dev environment, and there have been *no* issues thus far, and we’ve been using xtrabackup for 2-3 months there.

    I just want people to be informed — the previous blog post said “it requires the mysql user and we don’t know why”, so I updated why.

  • Xaprb says:

    I see. I had misunderstood the sequence of events and what you were actually seeing. I apologize. I’ll ping Vadim too and make sure we look at this.

  • VadimTK says:

    Ok, as you may know xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.

    InnoDB by default requires O_RDWR option on ibdata1 files at start, and xtrabackup therefore also did that. In the latest push to trunk it was fixed, now xtrabackup opens file with O_RDONLY flag.

    I would ask to refute message that xtrabackup may cause corruption on working database as it is misleading and already confused a lot of users.

  • Sheeri Cabral says:

    Vadim,

    Any time the ibdata file is written to, there is a chance of corruption. I have clarified to note that the only writes come from the fact that this is a restricted instance of InnoDB, so there is nothing different about the write than just plain running InnoDB.

    However, before you explained that, all we knew was that the file was being written to, which *is* scary and vague.

    As for the confusion issue — anyone running Xtrabackup *already knows* that they cannot run it with a user that does not have write privileges to the ibdata file. And if they do not realize that, they should.

  • […] Here on the Pythian Blog, Sheeri Cabral expressed some concerns and what does not work in XtraDB backup. […]

  • Andrew says:

    I think it would be helpful to specify *which* ibdata file is being written to. It’s not the one in the original MySQL data directory. This is easy to demonstrate:

    # md5sum ibdata1
    891d6ec8b4885df30c6dabcec86d1dfc ibdata1
    # xtrabackup –backup –target-dir=/mnt/mysql/backups/
    xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64)

    Copying ./ibdata1
    to /mnt/mysql/backups//ibdata1

    >> log scanned up to (0 2793120394)
    …done
    xtrabackup: The latest check point (for incremental): ‘0:2793120394′
    >> log scanned up to (0 2793120394)
    xtrabackup: Stopping log copying thread.
    xtrabackup: Transaction log of lsn (0 2793120394) to (0 2793120394) was copied.
    # md5sum ibdata1
    891d6ec8b4885df30c6dabcec86d1dfc ibdata1

    Of course, if you use the innobackupex wrapper, it will create a marker innodb table, as previously noted. This is going through the running MySQL instance – not xtrabackup – and will change the ibdata file.

    I think clarity on exactly what tools like these do is critical, but claims about “direct writes to the data dictionary by the process” are a bit alarmist when there’s no evidence of that provided.

  • Sheeri Cabral says:

    Andrew — I can tell you for a fact that I could *not* run innobackupex without being the “mysql” user, even though the non-mysql user I was running it as had read privileges to the ibdata file, and

    My coworker, Gerry, commented that it was writing to ibdata files. He has the exact information, but he’s currently on vacation.

    I hardly think that calling attention to a fact is “alarmist”. After the previous post saying that Xtrabackup was good and there was no real reason not to use it, I received feedback from others, which I then shared.

    At any rate, Vadim explained the reason, and even fixed it. So perhaps if you took the time to read BOTH the edit in the middle of the page, made 5 hours before your comment, as well as the rest of the comments, you’d understand that:

    YES the ibdata file IS written to

    BUT that’s an artifact of using InnoDB as the starting code for Xtrabackup

    AND the write has been taken out of the Xtrabackup trunk, so hopefully it won’t exist in the next version.

  • Andrew says:

    Just to clarify for posterity and no further offense intended :)

    I was not disputing the fact that you can’t run xtrabackup without write access to the ibdata file. This in itself is a (somewhat minor) problem, but nowhere near as scary as an external process directly writing to my ibdata files (which again, is NOT happening).

    I was pointing out that the original, source ibdata file that my production MySQL server is running with is NOT written to, despite what you keep saying. But it is opened with read/write permissions. No actual “write” is taken out in the xtrabackup trunk, the ibdata files themselves will just be opened in read-only mode – read the patches, they’re very simple.

    I don’t mean to split hairs here, but I think its important to portray the underlying issue accurately. Again, the ibdata files are NOT written to by xtrabackup today.

  • Sheeri Cabral says:

    Andrew,

    Before Vadim clarified what the writes actually were, it did indeed *look* like an external process (xtrabackup) was writing to the files. Thus, the concern.

    I had not had time to read the patches, and I have not disputed that the “write” is actually just opened with read/write permissions.

    As for portraying the issue actually, as soon as I got the facts I updated the blog post. What more would you like me to do?

  • Andrew says:

    Sheeri,

    I’m not sure what you mean by “…I have not disputed that the “write” is actually just opened with read/write permissions.” Are you contradicting your earlier statement that “YES the ibdata file IS written to”? Or is “write” being defined in some non-standard way?

    I’m a little curious, given that you were rather acerbic with the comment that:
    “So perhaps if you took the time to read BOTH the edit in the middle of the page, made 5 hours before your comment, as well as the rest of the comments, you’d understand that:

    YES the ibdata file IS written to”

    Which is wildly incorrect – I apologize for being so persistent in stating so. Something like “I can’t use xtrabackup as a non-mysql, non-root user”, seems much more fitting without more interesting evidence to the contrary.

    So, I suppose all I would ask is that the terminology be a bit more accurate so that we would not have necessarily talk past each other.

    Again, Thank you for the otherwise productive discussion. Warm Regards.

  • Gerry says:

    Apparently my observations while implementing ‘innobackupex’ in a complex environment has created some interesting traffic on this blog post by Sheeri, triggered some patches from Percona (thank you for doing it) and great suggestions on how to check for errors in the pipe both in Perl and Bash. We’ll make sure those make it to our scripts.

    Up to the day before my short vacation, I wasn’t been able to install v0.8 since the RPMs weren’t yet posted.

    As posted by Sheeri, the permissions concerns come from the fact that the ‘innobackupex’ script kept failing with a ‘Permission denied’ message. I didn’t think there was a need for the ‘w’ permission meant that something was being written to ‘ibbdata1′, but *nix security is there for a reason, and I didn’t see why then we needed to relax it w/o understanding what was going on. Apparently this has been fixed from what I understand from the comment thread.

    As you know Pythian work is done on customer’s time most of time, so we try the solutions that best work under the circumstances. With that criteria in mind, I usually avoid going into the source code of any tool unless there is a really compelling reason to do it. That’s why I didn’t go ahead and fixed the compilation issues for 4.1 and rather wait for Percona to do it.

    I hope this helps to clarify Sheeri’s original blog.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>