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

**URL:** <https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415>\
**Category:** Percona XtraBackup\
**Created:** [June 19, 2018, 10:52pm UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415 "2018-06-19T22:52:34Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 19, 2018, 10:52pm UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/1 "2018-06-19T22:52:34Z")

</div>

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  
[url][https://www.percona.com/forums/questions-discussions/percona-xtrabackup/8816-can-i-backup-remote-databases-from-my-local-server[/url]](https://www.percona.com/forums/questions-discussions/percona-xtrabackup/8816-can-i-backup-remote-databases-from-my-local-server%5B/url%5D)  
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.

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [June 20, 2018, 2:36am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/2 "2018-06-20T02:36:41Z")

</div>

Hello [diwakar](https://www.percona.com/forums/member/42099-diwakar)

Welcome to our forums!  
Here’s a link to some information that might help you out [url][Percona XtraBackup](https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/recipes_ibkx_stream.html%5B/url%5D)

See how you get on?

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 20, 2018, 3:48am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/3 "2018-06-20T03:48:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jrivera](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jrivera/32/13_2.png) [@jrivera](https://forums.percona.com/u/jrivera)\
**Post date:** [June 20, 2018, 4:13am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/4 "2018-06-20T04:13:03Z")

</div>

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):

```auto
[root&#64;node1 ~]# ssh root&#64;192.168.0.107 "xtrabackup --backup --stream=xbstream --target-dir=backup/" > backup.xbstream 2> backup.log
root&#64;192.168.0.107's password:
[root&#64;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

```

---

<div class="post-metadata">

**Author:** ![IMP](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/imp/32/20213_2.png) [@IMP](https://forums.percona.com/u/IMP)\
**Post date:** [June 20, 2018, 4:17am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/5 "2018-06-20T04:17:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 20, 2018, 5:15am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/6 "2018-06-20T05:15:09Z")

</div>

> [@jrivera;51712](#):
>
> 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):
> 
> ```auto
> [root&#64;node1 ~]# ssh root&#64;192.168.0.107 "xtrabackup --backup --stream=xbstream --target-dir=backup/" > backup.xbstream 2> backup.log
> root&#64;192.168.0.107's password:
> [root&#64;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
> 
> ```

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.

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 20, 2018, 5:18am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/7 "2018-06-20T05:18:39Z")

</div>

> [@IMP;51713](#):
>
> 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

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.

---

<div class="post-metadata">

**Author:** ![jrivera](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jrivera/32/13_2.png) [@jrivera](https://forums.percona.com/u/jrivera)\
**Post date:** [June 20, 2018, 5:40am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/8 "2018-06-20T05:40:22Z")

</div>

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

```auto

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.

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 20, 2018, 6:07am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/9 "2018-06-20T06:07:15Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![jrivera](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jrivera/32/13_2.png) [@jrivera](https://forums.percona.com/u/jrivera)\
**Post date:** [June 20, 2018, 6:21am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/10 "2018-06-20T06:21:22Z")

</div>

Now try this:

```auto
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.

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 20, 2018, 6:39am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/11 "2018-06-20T06:39:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [June 21, 2018, 1:57am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/12 "2018-06-21T01:57:01Z")

</div>

This is fully explained in the Percona Xtrabackup documentation, here is the link to the online page [url][https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup\_bin/preparing\_the\_backup.html[/url]](https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/preparing_the_backup.html%5B/url%5D) or you can download a pdf version of the document from here [url][https://learn.percona.com/download-percona-xtrabackup-2-4-manual[/url]](https://learn.percona.com/download-percona-xtrabackup-2-4-manual%5B/url%5D)

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?

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 21, 2018, 6:05am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/13 "2018-06-21T06:05:19Z")

</div>

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

[url][https://www.percona.com/forums/questions-discussions/percona-xtrabackup/10131-backing-up-and-restoring-a-single-database[/url]](https://www.percona.com/forums/questions-discussions/percona-xtrabackup/10131-backing-up-and-restoring-a-single-database%5B/url%5D)

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 ?

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [June 21, 2018, 9:19am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/14 "2018-06-21T09:19:32Z")

</div>

Here you are, that is covered under partial backups. [url][https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/partial\_backups\_innobackupex.html[/url]](https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/partial_backups_innobackupex.html%5B/url%5D)

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. 😉

---

<div class="post-metadata">

**Author:** ![diwakar](https://avatars.discourse-cdn.com/v4/letter/d/2acd7d/32.png) [@diwakar](https://forums.percona.com/u/diwakar)\
**Post date:** [June 22, 2018, 12:50am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/15 "2018-06-22T00:50:41Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [June 22, 2018, 2:44am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/16 "2018-06-22T02:44:25Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![timureh](https://avatars.discourse-cdn.com/v4/letter/t/c2a13f/32.png) [@timureh](https://forums.percona.com/u/timureh)\
**Post date:** [July 31, 2019, 2:15am UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/17 "2019-07-31T02:15:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Keith\_Larson](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/keith_larson/32/6761_2.png) [@Keith\_Larson](https://forums.percona.com/u/Keith_Larson)\
**Post date:** [June 16, 2022, 9:55pm UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/18 "2022-06-16T21:55:07Z")

</div>

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/`

---

<div class="post-metadata">

**Author:** ![Wwe\_Star](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/wwe_star/32/15673_2.png) [@Wwe\_Star](https://forums.percona.com/u/Wwe_Star)\
**Post date:** [April 3, 2024, 12:01pm UTC](https://forums.percona.com/t/mysql-backup-of-remote-server-in-my-local-machine-using-percona-xtrabackup/6415/19 "2024-04-03T12:01:46Z")

</div>

Yes bro it is useful for me as you anticipated, how ever my task is little different, I want to take backup from a remote server and need to restore it to another remote server from my local machine, in a single command either using ’ | ’ or other operators, so anyone can help me please