xtrabackup backup/restore strategy

[Blake06]

Hello,

I am attempting to set up xtrabackup as our backup/restore solution
for a single msyql server. We're only using InnoDB tables in our
databases. Using mysql 5.0.67 on CentOS 5.2.

I have a shell script that does the following for backup:

1. Run xtrabackup (backup folder named based on current date and time)
2. Tar/gzip the directory that was created
3. Send the tar file to Rackspace Cloud Files using jungledisk

This seems to work fine.

Now for recovery. Here is what I tested:

1. Moved the tar file to a separate server and un-tar the files into a
directory on that server (CentOS 5.3 running mysql 5.0.45).
2. This server has no tables in mysql except for hte default ones that
are created when you install mysql
3. I used --prepare twice on the directory.
4. I stopped mysql.
5. Then I copied all of the files into the /var/lib/mysql directory.
6. I restarted mysql.
7. I logged into mysql and it had my databases listed, but I couldn't
select from any of the tables. It gave me an error saying that it
could not find the .frm files.

So, are .frm files required for restoring InnoDB tables, and if so,
why does xtrabackup not back them up?

Or maybe I can't restore to a different server?

Or maybe I was not supposed to copy the files into /var/lib/mysql? If
not, would that mean that I should restart mysql using a different
my.cnf that has the datadir pointing to the restore directory?

Any help would be appreciated. Thanks!!!

[Vadim Tkachenko]

Blake06,

xtrabackup by itself can copy only .ibd files.

To copy .frm and myisam files you should use wrapper innobackupex

--
Vadim Tkachenko, CTO
Percona Inc.
ICQ: 369-510-335, Skype: vadimtk153, Phone +1-888-401-3403
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.percona.com/

[Blake06]

But doesn't using innobackupex mean that I'd no longer be getting a
hot backup? That wrapper is going to result in a read lock, correct?

Thanks for your help,
blake

[Vadim Tkachenko]

Blake06,

Yes, it will acquire read lock for time needed to copy .frm and myisam
files, but it is usually very short time if you have no myisam tables.

We are going to implement --no-lock option which will not obtain this
lock, but there is risk that files will be unsynced with binary log
position in this case.

[Blake06]

Okay, thanks for the explanation.

Is there no way, though, to use xtrabackup by itself to backup/restore
without using innobackupex? Can mysql regenerate the .frm files
somehow?

Thanks again!
blake

[Vadim Tkachenko]

Blake06,

That may be possible to implement... but that's very hard piece of work.
I do not expect we will have it anytime soon.

Best,
Vadim

[Blake06]

Okay, one last question. Is there a way to choose the databases that
get backed up, or do you have to backup everything?

I see the --tables option for xtrabackup, can you use that with
innobackupex? Does the regular expression match the database name
also, or just the table names? If you use this and only back up a
portion of the databases on the server, will the restore process still
work correctly, or is there going to be info in the binlog that is
referring to databases/tables that weren't backed up, and cause it to
have problems?

Thanks for your help Vadim.

blake

[Blake06]

Oh yeah, I have one feature request also. It would be great if you
could pass a parameter to innobackupex to specify the actual name of
the backup directory, instead of innobackupex naming it automatically
using a date/time string.

blake

[Vadim Tkachenko]

Blake,

This functionality is fully identical to
http://www.innodb.com/doc/hot_backup/manual.html#innobackup

6.Making a partial backup

you can get all info there.

However I would not recommend to do partial backup, only if you really
understand how it works and how to restore it.

[Vadim Tkachenko]

Blake,

This is valid request, can you please fill bug report for this so it is
not lost ?

Thanks,
Vadim

[Blake06]

Thanks for all your help Vadim!

I'll file a bug report for the naming request.

blake

[Blake06]

Vadim,

I think I have successfully set up our backup/restore plan. I'm
including the details here so you'll know ways that people are using
your software, and as a reference in case other people are thinking
about a similar strategy.

Background:
- We have a CentOS 5.2 x86_64 server
- We are running MySql version 5.0.67-community (no replication or any
other complexities) with the majority of the tables being InnoDB (the
only ones that are not InnoDB are the system tables created by our
operating system, apache, mail, etc.).
- We need to do hot backups (or at least semi-hot) instead of using
mysqldump so that we have less read-lock time, and so that recovery
will be faster in case of a crash
- We would still like the convenience (and piece-of-mind) of having
full sql/logical backups in case we ever need them (but we want to do
these backups on a different server)
- We have enabled the binlog on our server
- We are using innodb file per table
- We want to store our backups on our remote Rackspace Cloud Files
space using JungleDisk

Our Approach:

Raw Backups using innobackupex:
- I created a shell script that runs innobackupex to back up our
entire mysql server. The script does the following:
- Delete any previous backups in the backup directory that are over
5 days old
- Back up server using innobackupex
- Parse the innobackupex output to find out the name of the backup
folder that was created
- Gzip the backup folder
- Transfer the gzip file using JungleDisk to our remote Rackspace
Cloud Files space
- The shell script is run every 6 hours using cron

Sql/Logical backups using mysqldump on a separate server:
- I have an iMac with Parallels 4.0. I installed a CentOS 5.3 x86_64
virtual machine and it is running MySql 5.0.45-community.
- I created a shell script that does the following inside the CentOS
VM:
- SSH into our dedicated server and find the latest updated .tar.gz
file in the backups directory
- Copy that file to the CentOS VM and untar
- Stop mysqld
- Delete all data from the /var/lib/mysql directory
- Use innobackupex --apply-logs to prepare the files
- Use innobackupex --copy-back to move the files into /var/lib/
mysql
- Start mysqld
- Run mysqldump to dump the databases into a gzip file
- Copy that dump file back to the dedicated server
- The script is run each night using cron
- I have another shell script on my Mac that starts the CentOS VM 5
minutes before the mysqldump script it scheduled to run on the VM, and
then it suspends the VM 30 minutes later

It took me a while, but I finally have this all up and running.

Thanks for your great backup software!

Blake

[chopeta]

Hi blake,

Currently I am using mysql-zrm to backup my servers and I would love
to switch over to xtrabackup, but the purge functionality its a must
for me.

Could you share your script to automate innobackupex execution with
us?

Regards,
chopeta