Xtrabackup stream backup and recovery, xtrabackup backup

Xtrabackup stream backup and recovery, xtrabackup backup

Xtrabackup is one of the most common tools for MySQL database backup. Provides full backup, Incremental backup, database-level, table-level backup, and so on. There is also a backup that is not flushed into the disk, that is, the stream backup mode. If the server space is insufficient or the Master/Slave node is set up, the direct use of streaming backup greatly simplifies the overhead caused by compression and replication after backup. Xtrabackup supports stream backup in tar and xbstream formats. This article describes the details.

 

1. tar-based backup
A. Back up data locally
# Innobackupex -- stream = tar/tmp>/backup/bak.tar ### non-compression mode
# Innobackupex -- stream = tar/tmp | gzip>/backup/bakz.tar.gz ### compression mode
# Ls-hltr
Total 42 M
-Rw-r -- 1 root 39 M Apr 15 :23 bak.tar
-Rw-r -- 1 root 3.3 M Apr 15 17:24 bakz.tar.gz

### Decompress the backup
# Mkdir bak bakz
# Tar-xivf bak.tar-C/backup/bak
# Tar-xizvf bakz.tar.gz-C/backup/bakz
# Du-sh *
38 M bak
39 M bak.tar
38 M bakz
3.3 M bakz.tar.gz

B. Back up data to a remote device
# Innobackupex -- stream = tar/tmp | ssh root@192.168.1.7 \ "cat->/backup/bak.tar" ### non-compression mode
# Innobackupex -- stream = tar/tmp | ssh root@192.168.1.7 \ "gzip>/backup/bak.tar.gz" ### compression mode

 

2. Back up data in xbstream format
A. Back up data locally
# Innobackupex -- stream = xbstream/tmp>/backup/bak. xbstream ### non-compression mode
# Innobackupex -- stream = xbstream -- compress/tmp>/backup/bak_compress.xbstream ### compression mode
# Ls-hltr
Total 43 M
-Rw-r -- 1 root 37 M Apr 15 :41 bak. xbstream
-Rw-r -- 1 root 6.0 M Apr 15 :41 bak_compress.xbstream

### Decompress the backup
# Mkdir bk bk_compress
# Xbstream-x <bak. xbstream-C/backup/bk ### decompress the xbstream format

### Decompress the xbstream format and compress parameter backup
# Xbstream-x <bak_compress.xbstream-C/backup/bk_compress ### decompress xbstream first
# For bf in 'Find. -iname "*\. qp "'; do qpress-d $ bf $ (dirname $ bf) & rm $ bf; done ### decompress the qp compression format
# Innobackupex -- decompress/backup/bk_compress ### if the xtrabackup version is later than 2.1.4, decompress the package directly.

B. Back up data to a remote device
### Use compressed backup to remotely decompress
# Innobackupex -- stream = xbstream -- compress/tmp | ssh root@192.168.1.7 "xbstream-x-C/backup/stream"

 

3. Full backup and Incremental backup of stream backup
### Full backup database, use the -- extra-lsndir parameter to generate the checkpoints File
# Innobackupex -- stream = xbstream -- compress -- extra-lsndir =/backup/chkpoint/tmp>/backup/bak_compress.xbstream
# More/backup/chkpoint/xtrabackup_checkpoints
Backup_type = full-backuped
From_lsn = 0
To_lsns = 8408290
Last_lsns = 8408290
Compact = 0

### Author: Leshami
### Blog: http://blog.csdn.net/leshami
### Add a backup database. If you need to add a backup again later, you can specify -- extra-lsndir again. If it is the same location as the previous backup, the file will be overwritten.
# Innobackupex -- compress -- incremental -- extra-lsndir =/backup/chkpoint -- incremental-basedir =/backup/chkpoint \
> -- Stream = xbstream/tmp>/backup/bak_compress_inc.xbstream

 

4. Stream Backup Recovery from different machines
### Back up data to a different machine
# Innobackupex -- stream = xbstream -- extra-lsndir =/backup/chkpoint/tmp | ssh root@192.168.1.7 "xbstream-x-C/backup/stream"
# Innobackupex -- incremental -- extra-lsndir =/backup/chkpoint -- incremental-basedir =/backup/chkpoint -- stream = xbstream \
>/Tmp | ssh root@192.168.1.7 "xbstream-x-C/backup/stream_inc"

### Recovery from different machines, copy-back, and subsequent steps omitted
# Innobackupex -- apply-log -- redo-only/backup/stream
# Innobackupex -- apply-log/backup/stream -- incremental-dir =/backup/stream_inc

 

5. Other considerations
A. If xbstream is used for backup on a different machine, the following error message is displayed if xbstream is not installed on the same machine (encapsulated in xtrabackup.
Bash: xbstream: command not found
Xtrabackup: Error writing file 'unopened' (Errcode: 32-Broken pipe)
Xb_stream_write_data () failed.
Compress: write to the destination stream failed.
Xtrabackup: Error writing file 'unopened' (Errcode: 32-Broken pipe)
Xb_stream_write_data () failed.
Xtrabackup: Error writing file 'unopened' (Errcode: 32-Broken pipe)
[01] xtrabackup: Error: xtrabackup_copy_datafile () failed.
[01] xtrabackup: Error: failed to copy datafile.
Innobackupex: Error: The xtrabackup child process has died at/usr/bin/innobackupex line 2681.

B. The equivalence must be set up during Backup of different hosts, as shown in the following example:
# Ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/. ssh/id_rsa ):
Enter passphrase (empty for no passphrase ):
Enter same passphrase again:
Your identification has been saved in/root/. ssh/id_rsa.
Your public key has been saved in/root/. ssh/id_rsa.pub.
The key fingerprint is:
29: 45: ee: 8d: b3: 55: f1: 5f: 2f: da: 2a: 88: 0c: 0d: 37: 9f root @ vdbsrv1

### Copy the public key to the remote host
# Ssh-copy-id-I ~ /. Ssh/id_rsa.pub root@192.168.1.7
21
The authenticity of host' 192. 168.1.7 (192.168.1.7) 'can't be established.
RSA key fingerprint is 1d: 7c: 40: 98: ef: de: 6f: b8: 8c: b2: 87: 72: 0e: 79: db: 0a.
Are you sure you want to continue connecting (yes/no )? Yes
Warning: Permanently added '192. 168.1.7 '(RSA) to the list of known hosts.
Root@192.168.1.7's password:
Now try logging into the machine, with "ssh 'root @ 192.168.1.7'", and check in:

. Ssh/authorized_keys

To make sure we haven't added extra keys that you weren't expecting.

### Verify whether the equivalence is successful
# Ssh 192.168.1.7 date;
Wed Apr 15 17:55:31 CST 2015

C. When using the tar format for remote Incremental backup, the following prompt is received, that is, only xbstream is supported.
Xtrabackup: error: streaming incremental backups are incompatible with
'Tar 'streaming format. Use -- stream = xbstream instead.

D. Use stream backup. The default temporary directory is the system's/tmp directory. Make sure that the directory has sufficient space or specify the -- tmpdir option.

E. Stream backup log output
Innobackupex -- stream = xbstream/tmp 2> "$ backupLog" | gzip> "$ backup_file" 2> "$ backupLog"

 

6. More references
Procedure for Incomplete recovery across instances based on innobakcupex
MySQL backup script based on Innobackupex
Incomplete Restoration Based on Innobackupex
Innobackupex-based full recovery
Innobackupex-Based Incremental backup and recovery
Innobackupex-based full Backup Recovery
Innobackupex full backup database