Not the answer you need?
Register and ask your own question!

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

diwakardiwakar EntrantInactive User Role Beginner
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.

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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?
  • diwakardiwakar Entrant Inactive User Role Beginner
    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.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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
    
  • IMPIMP Percona Percona Staff Role
    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
  • diwakardiwakar Entrant Inactive User Role Beginner
    jrivera wrote: »
    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&#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 :

    [email protected]:/home/database# ssh [email protected] "xtrabackup --backup --stream=xbstream --target-dir=/var/lib/mysql" > backup.xbstream 2> backup.log
    [email protected]'s password:
    [email protected]'s password:
    [email protected]'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.
  • diwakardiwakar Entrant Inactive User Role Beginner
    IMP wrote: »
    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:

    [email protected]:/home/database# ssh [email protected] "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
    [email protected]:/home/database#

    What am i doing wrong? please help.
  • jriverajrivera Percona Support Engineer Percona Staff Role
    Please replace <user> 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 <user> and <host_IP>, make sure you type the correct password of your SSH user.
  • diwakardiwakar Entrant Inactive User Role Beginner
    sorry i used root instead of user. This is what i did:

    ssh [email protected] "xtrabackup --backup --stream=xbstream --target-dir=./" > backup.xbstream 2> backup.log

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

    [email protected]:/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 [email protected]% |
    +
    +
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
    +
    +
    1 row in set (0.01 sec)
  • jriverajrivera Percona Support Engineer Percona Staff Role
    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 <backupuser> and <backup_password> to the correct mysql user credentials.
  • diwakardiwakar Entrant Inactive User Role Beginner
    Thanks a lot. It really worked like magic for me this time. I was trying this since yesterday.
    i really get till this point:

    [email protected] ~]# ssh [email protected] "xtrabackup --backup --stream=xbstream --target-dir=backup/" > backup.xbstream 2> backup.log [email protected]'s password: [[email protected] ~]# 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! [[email protected] ~]# [[email protected] ~]# cd backup [[email protected] backup]# xbstream -x < ../backup.xbstream [[email protected] backup]# ls backup-my.cnf foo ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile [[email protected] backup]# cd ..


    but why do we use this part of command:

    [email protected] ~]# 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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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?
  • diwakardiwakar Entrant Inactive User Role Beginner
    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 :

    [email protected]:/home/diwakar/ubuntu14# ssh [email protected] "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 ?
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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. ;)
  • diwakardiwakar Entrant Inactive User Role Beginner
    Finally got the solution i was looking for. Thank you everyone.
    I did this and it worked:

    ssh [email protected] "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.

    [[email protected] ~]# cd backup [[email protected] backup]# xbstream -x < ../backup.xbstream [[email protected] backup]# ls backup-my.cnf foo ib_buffer_pool ibdata1 mysql performance_schema sys xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile [[email protected] backup]# cd .. [[email protected] ~]# 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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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 :)
  • timurehtimureh Entrant Current User Role Novice
    how this scheme will look like with incremental backup or using apply logs?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.