A, description,

This document describes how to use the MySQL database backup tool Xtrabackup in a Linux environment.

Second, the environment

System: CentOS 7

Database: MySQL 5.7

Software version: Percona-Xtrabackup -24-2.4.9-1.el7.x86_64

Three, description,

Percona-xtrabackup is abackup tool used for physical hot backup of MySQL database. It has two tools, xtrabakup and Innobakupe, and can only backup tables of innoDB and xtraDB data engines, not MyISAM data engines.

Four advantages,

(1) Fast backup speed and reliable physical backup

(2) The backup process does not interrupt ongoing transactions (no need to lock tables)

(3) It can save disk space and traffic based on compression

(4) Automatic backup verification

(5) Rapid reduction speed

(6) Can be circulated to transfer the backup to another machine

(7) Back up data without increasing server load

5. Backup Principle

(1) Innobackupex will fork a process to start xtrabackup and wait for xtrabackup to backup ibD data files.

(2) Xtrabackup has two types of thread when backing up innoDB data: redo copy thread and IBD data copy thread. When the xtrabackup process starts, a redo copy thread is started to copy redo.log from the latest checkpoint. Restart the IBD data copy thread to copy IBD data. The redo copy thread is started first. At this stage innobackupex is in the wait state (waiting for the file to be created)

(4) Xtrabackup will notify InnoBackupex when the ibD data file is copied (by creating the file), and xtrabackup will wait (the redo thread is still copying redo.log).

(5) When Innobackupex receives xtrabackup notification, run FLUSH TABLES WITH READ LOCK (FTWRL) to obtain consistency points. Then start to back up non-InnoDB files (such as FRM, MYD, MYI, CSV, OPT, PAR, etc.). During the process of copying non-InnoDB files, the database will be in the global read-only state.

(6) When InnoBackup has copied all the non-InnoDB files, it will notify Xtrabackup, and then enter the wait state;

(7) When xtrabackup receives notification that innoBackupex backup is complete, it will stop the redo copy thread and notify Innobackupex that the redo file is complete.

(8) Innobackupex receives redo. Log backup and unlocks it. Innobackupex runs: UNLOCK TABLES;

(9) Finally innBackupex and xtrabackup process to release resources, write backup metadata information, innobackupex and other xtrabackup child process after the end of exit.

Six, installation,

# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9 - 1. El7. X86_64. Rpm# yum install - y percona xtrabackup - 24-2.4.9-1. El7. X86_64. Rpm# RPM - qa | grep Xtrabackuppercona - xtrabackup - 24-2.4.9-1. El7. X86_64Copy the code

Xtrabackup consists of two tools:

Xtrabackup: this tool is used for hot spare InnoDB, xtradb table data. It cannot backup other types of tables, nor can it backup data table structures.

Innobackupex: A Perl script that wraps Xtrabackup and provides the ability to backup MyISAM tables. - user= # Specify the database backup user - password= # Specify the database backup user password - port= # Specify the database port - host= # Specify the backup host - socket= # Specify the path of the socket file - Databases = # Specify the database backup path. - databases= "dbname1 dbname2", do not backup all databases - defaults-file= # specify my.cnf configuration file - apply-log # log rollback - incremental= # incremental backup, Incremental-basedir = # incremental backup, which is the last incremental backup path -- redo-only # Merge full backup and incremental backup data files -- copy-back # Copy backup data to database, The database directory should be empty - no-timestamp # Generates backup files without timestamp as directory name - stream= # Specifies the format of the backup stream, - stream=tar, archive the backup file - remote-host=user@ip DST_DIR # Backs up to the remote hostCopy the code

Create a MySQL user with minimum permissions

Mysql > CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456'; mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456'; Mysql > GRANT ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; Mysql > GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; Mysql > FLUSH PRIVILEGES; # Refresh the authorization tableCopy the code

Note: MySQL should be started for backup, and MySQL should be shut down for recovery. MySQL data directory should be cleared and cannot be reinitialized. A full backup should be performed immediately after data restoration

7. Perform full backup and restoration

Backup:

# innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf /path/to/BACKUP-DIR/
Copy the code

Common backup:

1. Specifying database backup

# innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf --databases="db01" /path/to/BACKUP-DIR/
Copy the code

2. Specify table backup

# innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf --databases="db01 table01" /path/to/BACKUP-DIR/
Copy the code

3. Compression backup

innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf --stream= xbstream /path/to/BACKUP-DIR/
Copy the code

4. Decompressing backup media

# xbstream -x < full. Xbstream -c specifies the recovery directoryCopy the code

5. Install qpress (qP decompression package)

Qpress-11-linux-x64.tar # tar -xvf qpress-11-linux-x64.tar# cp qpress /usr/binCopy the code

6. Decompress the QP package

# Innobackupex -- Decompress --parallel=6 -- Compress-threads =6 qpCopy the code

7. Clear QP packets in batches

# for f in `find /home/whf/full/ -iname "*\.qp"`; do echo $f && rm -f $f; done
Copy the code

Recovery:

# innobackupex --apply-log /backups/2018-07-30_11-04-55/# innobackupex --copy-back --defaults-file=/etc/my.cnf /backups/2018-07-30_11-04-55/
Copy the code

(1) Prepare a full backup file

Generally, after a full backup is made, the backup set cannot be used immediately because the backup set contains uncommitted transactions or committed transactions that are not written to the data file. Therefore, the data file is in the inconsistent state. The apply log synchronizes the consistency of uncommitted and unwritten transactions.

# innobackupex --apply-log /path/to/BACKUP-DIR
Copy the code

If executed correctly, the last few lines of output usually look like this:

120407 09:01:04 innobackupex: completed OK!
Copy the code

(2) Copy back a full backup

The innobackupex command physically copies the information in the backup file to the target repository. The related directory information is obtained from my.cnf.

You do not need to start the database during the recovery

# innobackupex --copy-back /path/to/BACKUP-DIR
Copy the code

(3) Modify the owner of the restored file

After the recovery is complete, check whether the owner and owner group of the restored file are correct, for example, mysql. Otherwise, change the owner to the correct one.

chown -R mysql.mysql /mydata/data/
Copy the code

Start the database to check whether data is restored

8. Incremental backup and restoration

When using InnoBackupex for incremental backup, each page of InnoDB contains LSN information. When data changes, the LSN of the page will increase accordingly. This is the basis for incremental backup of InnoBackupex. Before an incremental backup, you need to perform a full backup. The first incremental backup is based on the last full backup, and subsequent incremental backups are based on the last incremental backup.

Incremental backup and recovery based on full backup

Make an incremental backup (based on the latest full backup)

# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
Copy the code

1. Prepare based on full volume

# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37
Copy the code

2. Prepare for increments

# innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37 --incremental-dir=/backups/2018-07-30_13-51-47/
Copy the code

3. Restore

# innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2017-01-05_11-04-55/
Copy the code

Explanation:

1. 2018-07-30_11-01-37 indicates the directory where the full backup resides.

2. 2018-07-30_13-51-47 specifies the directory for the first incremental backup based on 2018-07-30_11-01-37, and so on if there are multiple incremental backups. Do the above each time.

Incremental backup can only be applied to InnoDB or XtraDB tables. For MyISAM tables, when incremental backup is performed, it is actually a full backup.

Preparing an incremental backup is different from preparing a full backup:

1. On each backup (full and incremental), “replay” committed transactions. After “replay”, all backup data will be merged into the full backup.

2. Roll back uncommitted transactions based on all backups.

Nine,

Incremental backup: –incremental specifies the directory to be backed up, and incremental-dir specifies the full backup directory.

2. When data is backed up, you need to use the parameter –apply-log redot-only to merge the directory data of the full standby data to ensure the consistency of the directory data of the full standby data.

3. Add incremental backup data to full backup data using the –incremental-dir parameter.

4. Restore data using the final full backup data. Note that if there are multiple incremental backups, they need to be merged into the full backup data one by one before restoration.

5, InnoBackupex full backup, and specify the backup directory path;

6. Before recovery, use the –apply-log parameter to merge data files to ensure data consistency.

In my.cnf, you need to specify the path of the data file directory.