Innobackupex restore from SSH

Question

I made regularly MySQL backups using Percona's xtrabackup, using a command I found on the net (I am not deep into databases nor in their tools):

innobackupex --user=XXXX --password=XXXX --stream=tar ./ | sshpass -p XXXX ssh root@192.168.XX.XX "cat - > /var/lib/mysql_backups/name.tar"

Now, the backup file is 85GB and it has been made daily as FULL backup. I need to restore the DB after a crash and it doesn't work.

I watched inside the tar file (with tar -tif name.tar) and I read all the list of file names.

1) how is the normal way to restore the DB from local TAR using --move-back to decompress everything in place and not use extra space on the disk?

2) Or is there the possibility (if option 1 is not available) to restore the backup from a remote TAR, reversing the procedure described in Taking backup remotely using innobackupex? I tried multiple times but I cannot find the correct options.

I found an option --remote-host used in a post from 2012, but in my version 1.5.1 this option is missing...

3) being streamed=tar, how do I prepare (if necessary) the tar before restore?

Thank you in advance.

Answer 1

Regarding your questions:

1) how is the normal way to restore the DB from local TAR using --move-back to decompress everything in place and not use extra space on the disk?

You can extract the file and pipe the output to the destination server using the command line below:

# Execute the command where the backup is located ssh <user>@<mysql_server> "cd <datadir> && tar -xvv" < backup.tar

Using this method you will avoid the extra space needed to uncompress and copy. Next, you need to prepare the backup, which is your question number 3:

3) being streamed=tar, how do I prepare (if necessary) the tar before restore?

$ xtrabackup --prepare --target-dir=./<datadir>

Or with innobackupex:

$ innobackupex --apply-log ./<datadir>

Note that in the end, if the operation completes with success, you will see the message:

InnoDB: Shutdown completed; log sequence number 9059880 180618 11:05:22 completed OK!

Then MySQL will be ready to start.

More information can be found on these links:

Preparing the backup with xtrabackup

Streaming and compressing backups

Preparing the backup with innobackupex