innobackupex: restoring data without server restart

[Sergey]

Hello Perconians, 

I was a little bit surprised when I noticed that innobackupex copy-back requires an empty datatir.

Original data directory '/var/lib/mysql0' is not empty! at /usr/bin/innobackupex line 580.

As a workaround I need to move datadir to other directory, do copy-back, set up right permissions. 

So, in fact it means that server will be stopped during this process.

But I want to make restoring without stopping mysql server. 

Is there any possibilities to do that? 

I thought xtrabackup is 100% HOT backup tool, is this right or not? 

Thanks in advance. 

[Bill Karwin]

No, you can't restore a full physical backup to a running MySQL or Percona Server instance without stopping the instance.

XtraBackup is a hot backup tool -- you can create the backup in a hot manner, without interrupting the operation of a running instance.

But restoring a full backup is not "hot."  Copy-back is not an atomic operation, it could take several minutes or longer, depending on the size of the backup.  If you try to do this in a datadir while a running instance is in progress of flushing its dirty pages from the buffer pool to the disk, it's likely that the instance will overwrite pages you just restored, resulting in a mangled restore.  That's why you can't "copy-back" except to an empty directory.  Innobackupex deliberately checks that the copy-back target directory is empty since version 1.6.4.

You can, however, import a partial backup -- individual databases or tables -- to a running instance.

See:

http://www.percona.com/doc/percona-xtrabackup/innobackupex/partial_backups_innobackupex.html

--

Bill Karwin
Principal Instructor, Percona

Join us in Santa Clara for the annual Percona Live MySQL Conference & Expo 2013
http://www.percona.com/live/mysql-conference-2013/

[Justin Swanhart]

Hi,

Backup up hot does not imply that you can restore without shutting down the database.  Xtrabackup takes a physical backup up the database.  It is not possible to restore a physical backup without taking down the server.  

If you want to keep the database running for the most time possible then don't use the copy back option.  Manually copy the data to /var/lib/mysql_restore.  Once that completes, fixup the permissions on /var/lib/mysql_restore.  Then run "sudo service mysqld stop && sudo mv mysql1 mysql1_old && sudo mv mysql_restore mysql && service mysql start".  This will allow MySQL to be down for the minimum time possible, though you must have space in the filesystem holding /var/lib for both copies of the data.

--Justin

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Sergey Rudenko]

Thanks for the explanation.