0

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.

Alex Poca
  • 2,406
  • 4
  • 25
  • 47

1 Answers1

2

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

Vinicius Grippa
  • 56
  • 4
  • Thank you very much! After more than 12 hours of failed attempt any news is good news. I try and will upvote when it works. – Alex Poca Jun 18 '18 at 15:36
  • About the untar, I see that I need `tar -xiv` as the `tar` content has been streamed. Without the `-i` the binary is lost. But it is decompressing. – Alex Poca Jun 18 '18 at 15:43
  • Using `--i ` will ignore the zeroed block in the archive. This option helps `tar` handle a damaged archive, or any other oddly-formed archive with blocks of zeros in its contents. So I do not see any problem in using this way. – Vinicius Grippa Jun 18 '18 at 16:54
  • As I used `stream=tar` during backup, decompressing without the `-i` will produce only `backup-my.cnf`. I assume the rest is binary and at the first zero byte the extraction ends. With `-i` I get the correct files back. Thank you once more – Alex Poca Jun 19 '18 at 08:43