Mysql backup of remote server in my local machine using percona xtrabackup

I have percona xtrabackup installed in my ubuntu client machine. My local machine IP address is 192.168.0.100. The database that i need to backup is in 192.168.0.200. Is it possible to do the backup of remote server in local server using percona xtrabackup? I tried doing the same using mysqldump in the past but i need to use percona xtrabackup in particular ?
I found this solution in your site
https://www.percona.com/forums/questions-discussions/percona-xtrabackup/8816-can-i-backup-remote-databases-from-my-local-server
but the solutions didn’t work for me and it is really complicated.
Could anyone please help me with this one.
Thank you for the response.

1 Like

Hello diwakar

Welcome to our forums!
Here’s a link to some information that might help you out https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/recipes_ibkx_stream.html

See how you get on?

2 Likes

Hi there,

Thank you for your response. I can see the help there but it didn’t work for me. i want database backup of remote server in my local machine. From the post there i can see the backup being sent to the remote server. But i want to stay in my local machine and take the database backup of remote server.
Please help me with that.

1 Like

Diwakar, you need to install Percona Xtrabackup on the remote node 192.168.0.200. Then connect to that node from 192.168.0.100 for example in my case I have nodes 192.168.0.106 and 192.168.0.107:
I execute everything in node1 (192.168.0.106):

[root@node1 ~]# ssh root@192.168.0.107 "xtrabackup --backup --stream=xbstream --target-dir=backup/" > backup.xbstream 2> backup.log
root@192.168.0.107's password:
[root@node1 ~]# tail -n5 backup.log
180620 09:39:34 [00] ...done
180620 09:39:34 [00] Streaming <STDOUT>
180620 09:39:34 [00] ...done
xtrabackup: Transaction log of lsn (2636335) to (2636351) was copied.
180620 09:39:34 completed OK!
[root&#64;node1 ~]#
[root&#64;node1 ~]# cd backup
[root&#64;node1 backup]# xbstream -x < ../backup.xbstream
[root&#64;node1 backup]# ls
backup-my.cnf foo ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile
[root&#64;node1 backup]# cd ..
[root&#64;node1 ~]# xtrabackup --prepare --target-dir=backup/
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
xtrabackup: cd to /root/backup/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
1 Like

Hi,

Xtrabackup requires an access to MySQL’s data directory (and MySQL instance as well), so it can’t be run on another server. However, it can be triggered by another server with SSH (or other solutions as well). For example, the following command can be used:
serverB# ssh serverA “innobackupex --stream=xbstream /var/lib/mysql” > backup.xbstream

1 Like

Hello Jrivera, i tried your given solution. But it is not working for me.
I am getting this :

root@taskwise:/home/database# ssh root@192.168.0.109 “xtrabackup --backup --stream=xbstream --target-dir=/var/lib/mysql” > backup.xbstream 2> backup.log
root@192.168.0.109’s password:
root@192.168.0.109’s password:
root@192.168.0.109’s password:

my ip has changed a bit. i am trying to take the backup of 192.168.0.109 in the server 192.168.0.146.
This is the output of my local machine of 192.168.0.146.
I have set the grant on my 192.168.0.109 to allow 192.168.0.146.

What could be the problem? target-dir=/var/lib/mysql. Should the target directory be something else? I am confused as this is the first time i am using percona xtrabackup.
Please suggest me.

1 Like

Hello IMP. Thank you for your response as well. When i tried as per your suggestion i got this:

root@taskwise:/home/database# ssh diwakar@192.168.0.109 “innobackupex --stream=xbstream /var/lib/mysql” > backup.xbstream
encryption: using gcrypt 1.6.5
Can not open directory /var/lib/mysql: No such file or directory
root@taskwise:/home/database#

What am i doing wrong? please help.

1 Like

Please replace and <host_ip> to your ssh user and remote server’s IP address:


ssh <user>&#64;<host_IP> "xtrabackup --backup --stream=xbstream --target-dir=./" > backup.xbstream 2> backup.log

Please use the command above as it is and just replace the and <host_IP>, make sure you type the correct password of your SSH user.

1 Like

sorry i used root instead of user. This is what i did:

ssh diwakar@192.168.0.109 “xtrabackup --backup --stream=xbstream --target-dir=./” > backup.xbstream 2> backup.log

Then i typed the password , worked fine and got these files:

root@taskwise:/home/diwakar/mysql# ls
backup.log backup.xbstream

then nano backup.log gives :

encryption: using gcrypt 1.6.5
180620 17:17:23 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;$
Failed to connect to MySQL server: DBI connect(’;mysql_read_default_group=xtrab$
180620 17:17:23 Connecting to MySQL server host: localhost, user: not set, pass$
Failed to connect to MySQL server: Access denied for user ‘diwakar’@‘localhost’$

The mysql grant permission on 192.168.0.109 is as follow:

mysql> show grants for ‘root’@’%’;
±------------------------------------------+
| Grants for root@% |
±------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ |
±------------------------------------------+
1 row in set (0.01 sec)

1 Like

Now try this:

ssh diwakar&#64;192.168.0.109 "xtrabackup --user=<backupuser> --password=<backup_password> --backup --stream=xbstream --target-dir=./" > backup.xbstream 2> backup.log

This time replace and <backup_password> to the correct mysql user credentials.

1 Like

Thanks a lot. It really worked like magic for me this time. I was trying this since yesterday.
i really get till this point:

root@node1 ~]# ssh root@192.168.0.107 “xtrabackup --backup --stream=xbstream --target-dir=backup/” > backup.xbstream 2> backup.log root@192.168.0.107’s password: [root@node1 ~]# tail -n5 backup.log 180620 09:39:34 [00] …done 180620 09:39:34 [00] Streaming 180620 09:39:34 [00] …done xtrabackup: Transaction log of lsn (2636335) to (2636351) was copied. 180620 09:39:34 completed OK! [root@node1 ~]# [root@node1 ~]# cd backup [root@node1 backup]# xbstream -x < …/backup.xbstream [root@node1 backup]# ls backup-my.cnf foo ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile [root@node1 backup]# cd …

but why do we use this part of command:

root@node1 ~]# xtrabackup --prepare --target-dir=backup/ xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5) xtrabackup: cd to /root/backup/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1

Could you please clearify me.

1 Like

This is fully explained in the Percona Xtrabackup documentation, here is the link to the online page https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/preparing_the_backup.html or you can download a pdf version of the document from here https://learn.percona.com/download-percona-xtrabackup-2-4-manual

That manual has a lot of pages that you probably need to be familiar with to operate dependable and useful backups. See how you get on?

1 Like

Just another query related to same problem. What if i need to backup just one database namely test? As per your forum

https://www.percona.com/forums/questions-discussions/percona-xtrabackup/10131-backing-up-and-restoring-a-single-database

it had this solution:
innobackupex --include="^test." /tmp/

so i tried :

root@taskwise:/home/diwakar/ubuntu14# ssh diwakar@192.168.0.109 “xtrabackup --user=root --password=root --include=”^test1." --backup --stream=xbstream --target-dir=./" > backup.xbstream 2> backup.log

i got the backup of every databases. Is there a way to backup just single database ?

1 Like

Here you are, that is covered under partial backups. https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/partial_backups_innobackupex.html

Please be sure to read that carefully, especially the caveats with regard to how the back up is restored. It’s all there in the doc. :wink:

1 Like

Finally got the solution i was looking for. Thank you everyone.
I did this and it worked:

ssh diwakar@192.168.0.154 “xtrabackup --user=root --password=root --backup --stream=xbstream --databases=“uniquedb” --target-dir=./” > backup.xbstream 2>backup.log

This creates the backup of database named uniquedb from 192.168.0.154 to 192.168.0.109 (this is my local machine for now).

and then rest can be followed as per Jrivera.

[root@node1 ~]# cd backup [root@node1 backup]# xbstream -x < …/backup.xbstream [root@node1 backup]# ls backup-my.cnf foo ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile [root@node1 backup]# cd … [root@node1 ~]# xtrabackup --prepare --target-dir=backup/ xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5) xtrabackup: cd to /root/backup/ xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 Hope this helps to some other member too.

1 Like

Hi there, thank you for the update and also for providing the solution of what worked for you. As you mention, it could well help someone in the future so it’s much appreciated.

Please don’t hesitate to visit the Forum again if you have any issues going forward :slight_smile:

1 Like

how this scheme will look like with incremental backup or using apply logs?

1 Like

As long as you have ssh connections between the two You can also save a step and also have it extracted.

ssh REMOTEHOST 'sudo xtrabackup --defaults-file=/etc/my.cnf --login-path=local --backup --stream=xbstream --target-dir=/tmp/' | xbstream -x -C /var/lib/data/

1 Like