September 30, 2014

Btw xtrabackup is not only backup..

It is obvious thing for me, but I just figured out it may be not common understanding. Xtrabackup is also can be used (not only can, but we actually use it this way) to clone one slave to another, or just setup new slave from the master. And it is done in almost non-blocking way ( true for InnoDB setups) for cloned server. Here is command

When it finished on destination server you run

And you have ready database directory, just copy my.cnf from original server and start mysqld.

About Vadim Tkachenko

Vadim leads Percona's development group, which produces Percona Clould Tools, the Percona Server, Percona XraDB Cluster and Percona XtraBackup. He is an expert in solid-state storage, and has helped many hardware and software providers succeed in the MySQL market.

Comments

  1. Bruce Bristol says:

    Remember to change the slave’s ‘server_id’ variable if it’s in your my.cnf copied from the original server.

  2. anonymous says:

    wow

  3. John Sheppard says:

    I understand Xtrabackup is InnoDB only. Since 2001 I’ve been using
    Dave Brown’s perl script mysql backup. I
    should very much appreciate knowing what better options are available
    now ?

  4. Todd says:

    Hi, I just tried the above I had to add a few things like user and password. Now I get this error:

    100213 09:52:19 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup –backup –suspend-at-end –log-stream –target-dir=./
    innobackupex-1.5.1: Waiting for ibbackup (pid=7630) to suspend
    innobackupex-1.5.1: Suspend file ‘/var/lib/mysql/xtrabackup_suspended’

    xtrabackup: suspend-at-end is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: Target instance is assumed as followings.
    xtrabackup: innodb_data_home_dir = ./
    xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
    xtrabackup: innodb_log_group_home_dir = ./
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 5242880
    xtrabackup: use O_DIRECT
    100213 9:52:19 InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.
    InnoDB: File name ./ibdata1
    InnoDB: File operation call: ‘open’.
    InnoDB: Error in opening ./ibdata1
    100213 9:52:19 InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.
    xtrabackup: Could not open or create data files.
    xtrabackup: If you tried to add new data files, and it failed here,
    xtrabackup: you should now edit innodb_data_file_path in my.cnf back
    xtrabackup: to what it was, and remove the new ibdata files InnoDB created
    xtrabackup: in this failed attempt. InnoDB only wrote those files full of
    xtrabackup: zeros, but did not yet use them in any way. But be careful: do not
    xtrabackup: remove old data files which contain your precious data!
    innobackupex-1.5.1: Error: ibbackup child process has died at /usr/bin/innobackupex-1.5.1 line 464.

    I don’t quiet understand – what files do I not have permission to write to and has my database been destroyed?

  5. Kenny says:

    I have the same question as Todd (from Feb 2010). Why does nobody ever answer this question with a straight answer??? Not everybody that uses this tool is a Unix/Linux guru who delves into the mysteries of permissions (and people say Windows drives you nuts … ).
    WTF kind of permissions does this thing want, and for what user????

  6. Kenny,

    I can give you straight answer: RTFM, where F is the same F as in your WTF.
    http://www.percona.com/doc/percona-xtrabackup/innobackupex/privileges.html

  7. Kenny says:

    Vadim, I’m logged on as root. I have permissions to write and read from all the folders mentioned in the documentation. I also have ALL privileges on the database. My “WTF directory” is directed toward the fact that the message names at least four directories in the stream, then fails to tell me which one I don’t have permissions on, in spite of the fact that the user I’m logged on as has permissions to them all. It’s also directed toward the fact that many people have asked this same question in forums across the web and nobody ever gets a straight answer on which directory it’s complaining about. Todd, above, posted his whole message stream and no one bothered to even give him a tip about what directory. Also, there are several documented bugs in the Percona docs that have been filed on this subject and they keep getting re-assigned and transferred around to people. So I have no idea if it’s been fixed or is in the process of being fixed or if it’s simply a problem with documentation. What I DO know is that I’m here to design relational database systems for software and I don’t have time to deal with nonsensical, uninformative messages like this. Is this a ploy for me to log a ticket with Consulting so they can make a few bucks?

  8. Kenny, it sounds like MySQL Enterprise Backup might be a better solution for you. It isn’t free, but on the other hand, at least it has no bugs and it is perfectly documented. It’ll probably enable you to design your relational database systems with no wasted time at all. Give Oracle Sales a call and see if they can help you!

  9. Kenny says:

    Extremely funny — “no bugs, perfectly documented”. But I’ll check it out. By the way, I’ve had a side business for 7 years and I’ve never lost a customer. Possibly because I try my best to help them when they have a problem with my software. They may ask stupid questions or have invalid complaints but I never tell them that — I just do what I can to help them get past it. For similar references, see the forum at myclientbase.com. But I understand that not everyone prefers to operate like that.
    Cheers.

  10. Kenny says:

    Extremely funny — “no bugs, perfectly documented”. But I’ll check it out. In reference to the suggestion to RTFM, I **was** reading the manual AND the Percona XtraBackup Documentation as well. That’s why I asked the question because I followed the instructions and still got these errors. I’ve since gotten around them, but that’s a moot point now.

    By the way, I’ve had a side business for 7 years and I’ve never lost a customer. Possibly because I try my best to help them when they have a problem with my software. They may ask stupid questions or have invalid complaints but I never tell them that — I just do what I can to help them get past it. For similar references, see the forum at myclientbase.com. But I understand that not everyone prefers to operate like that.
    Cheers.

  11. Kenny, nobody is opposed to helping. You just catch more flies with honey than with vinegar. It’s free software that we spend literally hours developing. A pretty thankless job, all told, and when you start throwing F-Bombs around it just doesn’t endear you to people you’re asking to help you for free.

    BTW, I spend at least a few hours a week helping people for free on forum.percona.com, too. Try developing some open-source software and balancing that with making a living, and then compare myclientbase.com to Percona after a few years — you’ll find that the more popular your free work becomes, the harder it is to balance unpaid and paid work.

    Sign up for a support contract and you’ll get white-glove service like all of Percona’s clients. Curse us and we’ll refer you to someone else instead.

  12. s/hours/thousands of hours/

  13. pinoy says:

    selinux is the culprit. turn it OFF and you’ll be fine

    /etc/selinux/config
    SELINUX=disabled

  14. Justin R says:

    We use Percona software, xtrabackup as well as xtradb . I can honestly say Percona has some of the best consulting services/ support you can find in todays world. Very helpful. I do seriously recommend a support contract. Both Jay and Marco have been great.

  15. Chester says:

    I was also stuck on the error mentioned in the logs. I’m no sysadmin/devops, but it seems this is the key:

    100213 9:52:19 InnoDB: Operating system error number 13 in a file operation.
    InnoDB: The error means mysqld does not have the access rights to
    InnoDB: the directory.

    What happens is that the mysqld *process* needs to access the directory (the script will direct it into writing what needs to be written). One can solve that both the user that will run the backup and the user that runs MySQL itself are either the same (maybe not a great idea), or belong to a group that is also shared by the backup directory (and also ensure sub-directories created under it will have such group, by means of chmod g+s or the like).

    Keep in mind that group allocations are not recognized upon login, which means you may need to restart MySQL. Simply running the script as root may not cut it, because the script will create the sub-directories with root:root, but then mysql may not be able to write to them. It’s a bit frustrating, but manageable. Hope it helps.

    Cheers.

Speak Your Mind

*