Percona Xtrabackup for MySQL

MySQL & MariaDB Percona XtraBackup
,
1

I want to take backup from a remote server and need to restore the backup data to another server from my local machine, and I want to perform it with a single command, as take backup from the server-A and immediately restore it to server-B without storing it in a dump file. And I need to perform this using the xtrabackup tool for mysql.

Kindly anyone help me in this task

Thanks
Gousik Rao N

2

Hello @Wwe_Star,

You cannot do this with Xtrabackup. Xtrabackup is a physical backup tool which requires local disk access. You cannot use Xtrabackup to take remote backups.

If you do not have local disk access, you will need to take a logical backup using mydumper.

3

So whether it is not possible by using XtraDB right ?

4

XtraDB is a storage engine created by Percona which is an enhanced InnoDB.

Percona Xtrabackup (PXB) is a physical backup tool, meaning it copies files on the disk from location A to location B. You need disk-level access on both servers to use PXB.

If you do not have disk access, you must take a logical backup using mysqldump or mydumper.

5

Okay sir I understand that, If I can have disk level access then it is possible to take the backup and restore it in another server in a single command,

Note:I saw a post that it can by done using specific tools like that ?

6

Hello @Wwe_Star
If you have disk access, it is possible to restore to another server using two commands,
one to take the backup, you can use streaming backup to avoid saving it on the server, and directly save on server B. These blog posts will help you in detailed steps-

But you will have to prepare the backup using the second command - Prepare a full backup - Percona XtraBackup

If it has to be done in one command, you can use mysqldump and feed the output directly to MySQL which will have a higher chance of failure.

7

Okay let me try the solution you have gave !!

Any way thanks for your time !!!

8

Yes, you can use a single command to take the backup and stream it to another server, but once the backup is on server 2, you must still prepare it, fix permissions, and then start MySQL.

9

Yeah that’s very very helpful sir can you please give me more info on this regard like how to perform this operation and can you provide with the command or resource for this

Thanks

10

ssh user@remote-server “xtrabackup --backup --user=username --password=password --stream=xbstream --parallel=4” | ssh username@remote-server “xtrabackup --parallel=4 --prepare --target-dir=/path/to/restored_data”

Whether this command is correct to execute the above mentioned operation

11

No, your command will not work. You cannot prepare the stream of data. You must fully copy the backup from remote to local, and then prepare it.

Here is how to backup and stream in 1 command. After this, you need to prepare the backup on the local, machine, then fix permissions, then start MySQL. You cannot do all of this in “1 command”.

    remote-server$ xtrabackup --backup --compress --stream=xbstream --target-dir=./ | ssh user@otherhost "xbstream -x"
    local-server$ xtrabackup --prepare --target-dir=./
    local-server$ chown -R mysql:mysql ./
    local-server$ systemctl start mysql
12

Thanks sir for your valuable reply, but I want to take backup from a remote server and I want to restore it to another remote server in a single command can you help me for this ?

13

You cannot do this with a single command. It will require multiple commands as shown above. You can wrap all those commands into a single shell script and execute that one script as an alternative.

14

Ahh yes sir, whether its possible to take the backup from the remote server and then stream it directly to the another remote server in one command, and in the destination remote server we can prepare the backup separately to backup ?

15

Sir didn’t get the concept and my task is unfinished can you please help me with your valuable time

16

Hello @Wwe_Star,
The documentation and examples provided above should be enough to get a script written. Again, you cannot do all this in 1 command. You can use 1 command to stream the backup from A to B, but you will need local access to prepare the backup and then start mysql.

17

ssh username@remote_host1 “xtrabackup --backup -u root -p --stream=xbstream --compress --datadir=/path/to/data/dir” | ssh username@remote_host2 “xbstream -x -C /path/to/backup”

whether this command is right to take a backup from the remote server A and then stream it to the another remote server B