Mysql backup of remote server in local machine using percona xtrabackup

percona

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 ?
Thank you for the response.

Best Answer

You need to install Percona Xtrabackup on the remote server and then SSH from your local server to the remote server. From this position, you can initiate the backup and stream to your local server.

Xtrabackup requires 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 alternative solution) and you can stream the backup to the local server.

For example:

ssh serverA "innobackupex --stream=xbstream /var/lib/mysql" > backup.xbstream

Related Solutions

MySQL: Headers and client library minor version mismatch

Changed type of database connection in vbulletin config from mysql to mysqli and problem solved.

Mysql – Percona Xtrabackup incremental backup based on prepared full backup

No because the above mentioned --apply-log won't update LSN in xtrabackup_checkpoints, thus every next incremental backup will copy pages modified since the last full backup. That's not what you want to achieve

UPD

To implement your scenario you need:

Take full backup
```
innobackupex --no-timestamp /path/full
```

Save the last LSN

# cat /path/full/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1887987291
last_lsn = 1887987291
compact = 0

to_lsn=`grep to_lsn /path/full/xtrabackup_checkpoints | awk '{ print $3 }'`

Apply xtrabackup REDO log

innobackupex --apply-log --redo-only /path/full/

Take incremental backup

innobackupex --no-timestamp --incremental /path/inc/ --incremental-lsn=$to_lsn

Save last LSN

# cat /path/inc/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1887987291
to_lsn = 1887987291
last_lsn = 1887987291
compact = 0

to_lsn=`grep to_lsn /path/inc/xtrabackup_checkpoints | awk '{ print $3 }'`

Apply incremental changes and REDO log

innobackupex --apply-log --redo-only --incremental-dir=/path/inc /path/full/

Remove directory with incremental backup
```
rm -r  /path/inc
```

Repeat 4-7 as many as you need. /path/full will contain the last version of your database.

When you want to restore the database

Finish applying logs(= create REDO log):
```
innobackupex --apply-log /path/full
```
Copy the backup copy to datadir
```
mv /path/full/* /var/lib/mysql
```
Fix permissions (Check that options in /path/full/backup-my.cnf are the same as in /etc/my.cnf (/etc/mysql/my.cnf for Debian))
```
chown -R mysql /var/lib/mysql
```
Start MySQL
```
/etc/init.d/mysql start
```

Best Answer

Related Solutions

MySQL: Headers and client library minor version mismatch

Mysql – Percona Xtrabackup incremental backup based on prepared full backup

Related Topic