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]
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.
Hello diwakar
Welcome to our forums!
Hereâs a link to some information that might help you out [url]Percona XtraBackup
See how you get on?
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.
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@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 ..
[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
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 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.
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.
Please replace and <host_ip> to your ssh user and remote serverâs IP address:
ssh <user>@<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.
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)
Now try this:
ssh diwakar@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.
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.
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] or you can download a pdf version of the document from here [url]https://learn.percona.com/download-percona-xtrabackup-2-4-manual[/url]
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?
Just another query related to same problem. What if i need to backup just one database namely test? As per your forum
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 ?
Here you are, that is covered under partial backups. [url]https://www.percona.com/doc/percona-xtrabackup/LATEST/innobackupex/partial_backups_innobackupex.html[/url]
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.
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.
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
how this scheme will look like with incremental backup or using apply logs?
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/
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