xtrabackup backup and restore mysql database

xtrabackup backup and restore mysql database installation

Official website address:
xtrabackup centos (see the link above for apt and tar decompression installation methods)

https://www.percona.com/doc/perconaxtrabackup/LATEST/installation/yum_repo.html
$yum install http://www.percona.com/downloads/percona-release/redhat/0.1–4/percona-release-0.1–4.noarch.rpm

Backup
Full backup

$ xtraback — backup — target-dir=/data/backups
--Backup backup 
--target-dir backup file directory

Incremental backup

First full backup

$ xtrabackup — backup — target-dir=/data/backups/base 

First increment

$ xtrabackup xtrabackup --backup --target-dir=/data/backups/base/
 --incremental-basedir=/data/backups/inc1

Second increment

$ xtrabackup — backup — target-dir=/data/backups/inc2 /
 — incremental-basedir=/data/backups/inc1


 — Incremental-basedir incremental backup directory

Prepare (backed up data can be used directly after prepare)
Full backup and restore preparation

$ xtrabackup — prepare — target-dir=/data/backups/
 — Prepare prepare to adjust the data content so that it can be directly copied and used

Incremental backup and restore preparation

Three backup directories, base is fully prepared, inc1, inc2 incremental
/data/backups/base
/data/backups/inc1
/data/backups/inc2
The first (not the last) restore point
$ xtrabackup — prepare — apply-log-only — target-dir=/data/backups/base \
 — incremental-dir=/data/backups/inc1

Last restore point

$ xtrabackup — prepare — target-dir=/data/backups/base \
 — incremental-dir=/data/backups/inc2

apply-log-only before the last increment used in combination, so inc2 no use
if the last increment also use the apply-log-only may cause rollback data, that is, unable to complete the restoration

Reduction
Restore data
After prepare, the data obtained by the backup can be directly copied to the mysql data directory to go to the default (/var/lib/mysql/)

There are three ways to complete the restoration

First close mysql-server
$ systemctl stop mysql

copy-back
$ xtrabackup — copy-back — target-dir=/data/backups/

move-back
$ xtrabackup — move-back — target-dir=/data/backups/

cp,rsync
 $ rsync -avrP /data/backup/ /var/lib/mysql/

Note:
1. To use the move that comes with xtrabackup, the copy command must clear the /var/lib/mysql directory,
otherwise an error will be reported
2. Close mysql-server, and change chown -R mysql.mysql /var/lib/mysql
3 . Restart systemctl start mysql restore is complete

Configuration

For detailed configuration information, see : more options
Some information used:
xtrabackup will read the /etc/mysql/my.cnf configuration by default, and the parameters of xtrabackup can be configured in my.cnf
[xtrabackup]
target_dir = /data/backups/mysql/

Note: Compress can be compressed during backup, compressed in quicklz mode, compression software (qpress, if no download is required), 1.7G compressed to 360M, can decrease maximum backup space, xtrabackup -decompress -remove- original unzip and delete the compressed file
Finally, only the contents of the base directory are concerned as the incremental backup prepares to be restored.

The recovery speed is very high, which is equivalent to the disk speed for reading and writing. This software can be used for full daily backup and hourly intervals at the same time as logical backup to ensure data protection.

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.