Hi everyone,

So in my last post, I showed how we can install and use Percona Xtrabackup on FreeBSD 8.2 to take and restore database backups.

But what if you want files to be saved directly to a compressed tarball ?

Read On..

Prerequisites:

Before we go any further, special notes on tar:

Make sure you are using GNU tar instead of BSD tar. The reason is BSD tar doesn't have the -i (-i, --ignore-zeros ignore zeroed blocks in archive (means EOF) )

Have a look at the manual pages if you are interested in the details:

BSD Tar: http://www.freebsd.org/cgi/man.cgi?query=tar&sektion=1

GNU Tar: http://linux.die.net/man/1/tar

1.With that being said, installing GNU tar is super easy:

pkg_add -r gtar

2.Check that it works:

gtar –-version

You should see something like this:

tar (GNU tar) 1.25
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Written by John Gilmore and Jay Fenlason.

3.To create a backup directly to a compressed tar do this:

innobackupex --defaults-file=/usr/local/etc/my.cnf --stream=tar ./ | gzip - > /root/backup.tar.gz

You should see something like upon success:

innobackupex: Backup created in directory '/tmp/xtrabackup-1.6.3'
innobackupex: MySQL binlog position: filename '', position
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream.
111227 15:25:01  innobackupex: completed OK!

Note the warning above about using the -i option!

4.Now, let's restore this backup. First, stop the database:

/usr/local/etc/rc.d/mysql-server stop

5.Move the existing files somewhere else:

mv /var/db/mysql/ /root/oldmysqlfiles

6.Re-create the /var/db/mysql directory:

mkdir /var/db/mysql

7.Extract files – REMEMBER TO USE THE i : (note the directory must already exist)

mkdir /root/restore;gtar -xizf /root/backup.tar.gz -C /root/restore

8.Prepare the backup:

innobackupex --defaults-file=/usr/local/etc/my.cnf --apply-log /root/restore

9.Restore the backup:

innobackupex --defaults-file=/usr/local/etc/my.cnf --copy-back /root/restore

10.Change ownership of the files back to the MySQL user:

chown -R mysql:mysql /var/db/mysql

11.Start MySQL again:

/usr/local/etc/rc.d/mysql-server start

aaand you're done!