Percona XtraBackup 8.0.26 instructions for use

Welcome to the MySQL technical articles shared by the great SQL community. If you have any questions or want to learn, you can leave a message in the comment area below. After seeing it, you will answer it

1. Percona XtraBackup feature description
1. Percona Xtrabackup backup recovery permission restrictions
1. Create backup users, configuration parameters and data preparation
1. Full backup and recovery
1. Incremental backup
1. Compressed backup
1. Stream backup

1. Description of percona xtrabackup feature

1)Percona Xtrabackup 8.0.26. The new MyRocks storage engine is supported, but TokuDB engine is not supported

2)Percona Xtrabackup 8.0.26 backups lower than MySQL 8.0 are not supported (because MySQL 8.0 is incompatible with previous versions in data dictionary and redo log)

3)Percona Xtrabackup 8.0.26 at present, the X86 version can be downloaded from the official, and the ARM version needs to be compiled manually

4) The backup file must be empty without any files

2. Percona Xtrabackup backup and recovery permission restrictions

1) The backup path needs read-write permission

2)reload and Lock Tables (except for specifying the -- no lock option), because FLUSH TABLES WITH READ LOCK and FLUSH ENGINE LOGS need to be executed before backup

3)Backup_admin permission, because performance needs to be queried during backup_ schema. log_ Status table and run LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, or LOCK TABLES FOR BACKUP

4)Replication client permissions. To read binary log files during backup

5)Create tablespace permission. A table needs to be created when restoring a table

6)Process permissions. The show engine innodb status command needs to be run during backup

7)Super permissions. To start / stop replication threads in a replication environment

8)Create permission. To create percona_schema.xtrbackup_history table

9)Alter permissions. To update percona_schema.xtrbackup_history table

10)Insert permission. To insert history into percona_schema.xtrbackup_history table

11)Select permission. To query historical data

3. Create backup users, configuration parameters and data preparation

    //Create user
    mysql > CREATE  USER  'bkpuser' @ 'localhost'  IDENTIFIED  BY  's3cr%T' ; 
    mysql > GRANT BACKUP_ADMIN,PROCESS,RELOAD,LOCK  TABLES,REPLICATION  CLIENT ON *.*  TO  'bkpuser' @ 'localhost' ; 
    mysql > GRANT  SELECT  ON  performance_schema.log_status  TO  'bkpuser' @ 'localhost' ;
    Mysql > GRANT  SELECT  ON  performance_schema.keyring_component_status  TO  bkpuser @ 'localhost' 
    mysql > FLUSH  PRIVILEGES ;

Configuration parameters, Xtrbackup will read MySQL my. During backup CNF configuration file contains [mysqld] and [xtrabackup], so we can set the backup directory [xtrabackup] and target in the configuration file_ dir = /data/backups/mysql

Note: if my There is -- set variable in CNF configuration file= = , xtrabackup is not recognized, and -- veriable=value is required to replace version detection. Percona Xtrabackup 8.0 21 -- no server version check parameter is added to compare the backup source version with the Percona Xtrabackup version

The source system is the same as the PXB version. The backup continues. The source system is lower than the PXB version, the backup continues, the source system is greater than the PXB version, and the parameters are not overwritten. Stop the backup and return an error. The source system is greater than the PXB version, the parameters are overwritten, and the backup continues

Shell> xtrabackup --backup --no-server-version-check --target-dir = $mysql /backup1

When overwriting parameters, backup failure, creation of damaged backup and successful backup may occur

3.1 Sysbench manufacturing data

mysql> create database sbtest;
//Use sysbench to create numbers
Shell> sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=172.16.50.161 --mysql-port=3306 --mysql-user='root' --mysql-password='123456' --mysql-db='sbtest' --tables=1 --table-size=5000 --threads=20 prepare
//inspect
MySQL [sbtest]> select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 5000 |
+----------+
1 row in set (0.00 sec)

4. Full backup and recovery

4.1 full backup

    Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf --socket=/data1/mysqldata/data/mysql.sock --user=root --password=123456 --port=3306 --backup --target-dir=/data1/xtrabackup/full/
    Output information:
    210913 14:07:01 Finished backing up non-InnoDB tables and files
    210913 14:07:01 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
    210913 14:07:01 Selecting LSN and binary log position from p_s.log_status
    210913 14:07:01 [00] Copying /data1/mysqldata/log/binlog.000004 to /data1/xtrabackup/full/binlog.000004 up to position 196
    210913 14:07:01 [00]...done
    210913 14:07:01 [00] Writing /data1/xtrabackup/full/binlog.index
    210913 14:07:01 [00]...done
    210913 14:07:01 [00] Writing /data1/xtrabackup/full/xtrabackup_binlog_info
    210913 14:07:01 [00]...done
    210913 14:07:01 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '19442549'
    xtrabackup: Stopping log copying thread at LSN 19442549.
    Starting to parse redo log at lsn = 19442226

    210913 14:07:01 Executing UNLOCK INSTANCE
    210913 14:07:01 All tables unlocked
    210913 14:07:01 [00] Copying ib_buffer_pool to /data1/xtrabackup/full/ib_buffer_pool
    210913 14:07:01 [00]...done
    210913 14:07:01 Backup created in directory '/data1/xtrabackup/full/'
    MySQL binlog position: filename 'binlog.000004', position '196', GTID of the last change '8950f324-1441-11ec-b9b5-080027193a00:1-15'
    210913 14:07:01 [00] Writing /data1/xtrabackup/full/backup-my.cnf
    210913 14:07:01 [00]...done
    210913 14:07:01 [00] Writing /data1/xtrabackup/full/xtrabackup_info
    210913 14:07:01 [00]...done
    xtrabackup: Transaction log of lsn (19442549) to (19442559) was copied.
    210913 14:07:02 completed OK!

4.2 full recovery

preparation, Because the time point is inconsistent when using -- backup to back up data files (the backup incremental redo log thread will listen to the incremental data in real time). Because the program is copied at different time points and may be changed during the re replication process, if innodb directly tries to start these data files, innodb will detect damage and stop running. Therefore, you need to use -- prepare to crash and recover the backup files to use these copied files

Shell>xtrabackup --prepare --target-dir=/data1/xtrabackup/full

//Note: the source directory needs to be backed up and the recovery directory should be empty

Shell> xtrabackup --copy-back --target-dir=/data1/xtrabackup/full

//Modify directory properties to start the database

 Shell> chown -R mysql:mysql /data/mysqldata/data 
Shell> chmod -R 755 /data/mysqldata/data

//Start database

Shell> /data1/mysql/bin/mysqld --defaults-file=/data1/mysqldata/my.cnf --user=mysql &

//Detection

    MySQL> select count(*) from sbtest.sbtest1;

5. Incremental backup

Incremental backup needs to be carried out on the basis of full backup. Each innodb page contains a log serial number LSN. The LSN is the version number of the entire database system. The LSN of each page displays the time of the most recent change. During incremental backup, the LSN of the last full backup or the LSN of the last incremental backup will be compared with the LSN of the current page, and the search will continue through two algorithms.

The first is to read all data pages and directly check the page LSN (applicable to all versions of MySQL). The second is to enable the "change page tracking" function on the server. This function will record the page changes and save the information. When xtrbackup makes incremental backup, it will go back to read the information, so as to save resources (this lookup method is applicable to percona server for MySQL)

Full backup

    Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf --socket=/data1/mysqldata/data/mysql.sock --user=root --password=123456 --port=3306 --backup --target-dir=/data1/xtrabackup/full/

Apply relay log

    Shell> xtrabackup --prepare --apply-log-only --target-dir=/data1/xtrabackup/full

Output:

 InnoDB: Shutdown completed; log sequence number 1626007
    161011 12:41:04 completed OK!

Incremental backup: view the xtrbackup of the full backup directory_ checkpoints

    Shell>  cat xtrabackup_checkpoints 
    backup_type = full-prepared //Backup type
    from_lsn = 0 //Start LSN
    to_lsn = 22254648//LSN at end of backup
    last_lsn = 22254648   //Last LSN number
    flushed_lsn = 22254648//LSN number of current flush

Incremental backup command

    Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf --socket=/data1/mysqldata/data/mysql.sock --user=root --password=123456 --port=3306 --backup --target-dir=/data1/xtrabackup/inc1 --incremental-basedir=/data1/xtrabackup/full

Output information:

 210913 14:18:07 Finished backing up non-InnoDB tables and files
    210913 14:18:07 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
    210913 14:18:07 Selecting LSN and binary log position from p_s.log_status
    210913 14:18:07 [00] Copying /data1/mysqldata/log/binlog.000005 to /data1/xtrabackup/inc1/binlog.000005 up to position 196
    210913 14:18:07 [00]...done
    210913 14:18:07 [00] Writing /data1/xtrabackup/inc1/binlog.index
    210913 14:18:07 [00]...done
    210913 14:18:07 [00] Writing /data1/xtrabackup/inc1/xtrabackup_binlog_info
    210913 14:18:07 [00]...done
    210913 14:18:07 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '20799610'
    xtrabackup: Stopping log copying thread at LSN 20804588.
    Starting to parse redo log at lsn = 20799534
    210913 14:18:07 >> log scanned up to (20804598)

    210913 14:18:08 Executing UNLOCK INSTANCE
    210913 14:18:08 All tables unlocked
    210913 14:18:08 [00] Copying ib_buffer_pool to /data1/xtrabackup/inc1/ib_buffer_pool
    210913 14:18:08 [00]...done
    210913 14:18:08 Backup created in directory '/data1/xtrabackup/inc1/'
    MySQL binlog position: filename 'binlog.000005', position '196', GTID of the last change '8950f324-1441-11ec-b9b5-080027193a00:1-20'
    210913 14:18:08 [00] Writing /data1/xtrabackup/inc1/backup-my.cnf
    210913 14:18:08 [00]...done
    210913 14:18:08 [00] Writing /data1/xtrabackup/inc1/xtrabackup_info
    210913 14:18:08 [00]...done
    xtrabackup: Transaction log of lsn (20799610) to (20804624) was copied.
    210913 14:18:09 completed OK!
    //Check current checkpoints
    Shell>  cat xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 22254648
    to_lsn = 33288485
    last_lsn = 33299549
    flushed_lsn = 33288485

From_lsn is the starting LSN of backup. For increment, it must be the same as the previous backup checkpoint_ LSN same

Apply relay log

--Prepare incremental backup recovery is different from full backup. It is executed during full backup -- prepare to ensure database consistency, replay committed transactions from log files for data files, and roll back uncommitted transactions. During incremental backup, you must skip the rollback of uncommitted transactions. Because uncommitted transactions during backup may be in progress and may be committed in the next incremental backup, you need to use the -- apply log only option to prevent the rollback phase.

Apply the first incremental backup to the full backup (the incremental file will be applied to / data1/xtrbackup/full)

    Shell> xtrabackup --prepare --apply-log-only --target-dir=/data1/xtrabackup/full  --incremental-dir=/data1/xtrabackup/inc1

Output information:

incremental backup from 1626007 is enabled.
xtrabackup: cd to /data/backups/base
xtrabackup: This target seems to be already prepared with --apply-log-only.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(4124244)
...
xtrabackup: page size for /tmp/backups/inc1/ibdata1.delta is 16384 bytes
Applying /tmp/backups/inc1/ibdata1.delta to ./ibdata1...
...
161011 12:45:56 completed OK!

Note: to in full backup_ lsn number and to of checkpoints in incremental backup_ If the lsn numbers are the same for multiple incremental backups, you need to use -- prepare -- apply log only to merge the incremental parts into the full backup after each incremental backup. Otherwise, an lsn conflict will be reported

xtrabackup: This target seems to be already prepared with --apply-log-only.
xtrabackup: error: This incremental backup seems not to be proper for the target.
xtrabackup:  Check 'to_lsn' of the target and 'from_lsn' of the incremental.

5.1 incremental recovery

Shell> xtrabackup --prepare --target-dir=/data1/xtrabackup/full 
// Copy back data:
Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf  --copy-back --target-dir=/data1/xtrabackup/full
// Modify the permissions and properties of the data directory:
Shell> chown -R mysql:mysql /data1/mysqldata1 
Shell> chmod -R 755 /data1/mysqldata1 Start database
Shell>/data1/mysql/bin/mysqld --defaults-file=/data1/mysqldata/my.cnf --user=mysql &
// testing
MySQL> select count(*) from sbtest.sbtest1;

Full backup + additional backup recovery completed

6. Compressed backup

We can compress the backup files during backup, and use the -- compress option

Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf --socket=/data1/mysqldata/data/mysql.sock --user=root --password=123456 --port=3306 --backup --compress --target-dir=/data1/xtrabackup/full

Because -- compress uses qpress, you need to install the percona release package

Shell> suudo percona-release enable tools
Shell> sudo apt-get update
Shell> sudo apt-get install qpress

Add backup thread

Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf --socket=/data1/mysqldata/data/mysql.sock --user=root --password=123456 --port=3306 --backup --compress --compress-threads=4 --target-dir=/data1/xtrabackup/full

Output the following information:

...
170223 13:00:38 [01] Compressing ./test/sbtest1.frm to /tmp/compressed/test/sbtest1.frm.qp
170223 13:00:38 [01]...done
170223 13:00:38 [01] Compressing ./test/sbtest2.frm to /tmp/compressed/test/sbtest2.frm.qp
170223 13:00:38 [01]...done
...
170223 13:00:39 [00] Compressing xtrabackup_info
170223 13:00:39 [00]...done
xtrabackup: Transaction log of lsn (9291934) to (9291934) was copied.
170223 13:00:39 completed OK!

6.1 decompression, backup and recovery

Unzip file:

Shell> xtrabackup --decompress --target-dir=/data/compressed/
Shell> xtrabackup --prepare --target-dir=/data/compressed/
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 9293846
170223 13:39:31 completed OK!

Backup recovery:

Shell> xtrabackup --copy-back --target-dir=/data/backups/

7. Streaming backup

Use streaming backup to transfer to a file named backup xbstream

Shell> xtrabackup --backup --stream=xbstream --target-dir=./ > backup.xbstream

Encrypted backup

Shell> xtrabackup –backup –stream=xbstream ./ > backup.xbstream gzip - | openssl des3 -salt -k ""Password" > backup.xbstream.gz.des3

Backup to current directory

Shell> xbstream -x <  backup.xbstream

Use streaming backup to other servers

Shell> xtrbackup --backup --stream=xbstream --target-dir=./ | ssh user@otherhost "xbstream -x "

Limit the throughput of the backup tool to 10MB / s

Shell> xtrabackup –backup –stream=xbstream ./ | pv -q -L10m ssh user@desthost "cat - > /data/backups/backup.xbstream"

Enjoy GreatSQL :)

Article recommendation:

Technology sharing | MGR best practice https://mp.weixin.qq.com/s/66u5K7a9u8GcE2KPn4kCaA

Technology sharing | the way to repair MGR Bug in Wanli database https://mp.weixin.qq.com/s/IavpeP93haOKVBt7eO8luQ

Macos system compiler percona and the difference of some functions on Macos system https://mp.weixin.qq.com/s/jAbwicbRc1nQ0f2cIa_2nQ

Technology sharing | using systemd to manage single machine and multiple instances of MySQL https://mp.weixin.qq.com/s/iJjXwd0z1a6isUJtuAAHtQ

Product | GreatSQL to create a better MGR ecology https://mp.weixin.qq.com/s/ByAjPOwHIwEPFtwC5jA28Q

Product | GreatSQL MGR optimization reference https://mp.weixin.qq.com/s/5mL_ERRIjpdOuONian8_Ow

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli database. It focuses on improving the reliability and performance of MGR and supports the parallel query feature of InnoDB. It is a branch version of MySQL suitable for financial applications.

Gitee: https://gitee.com/GreatSQL/GreatSQL

GitHub: https://github.com/GreatSQL/GreatSQL

Wechat & QQ group:

You can search and add GreatSQL community assistant wechat friends, send verification information "add group" to join GreatSQL/MGR communication wechat group

QQ group: 533341697 wechat assistant: wanlidbc

This article is composed of blog one article multi posting platform OpenWrite release!

Keywords: MySQL

Added by hezll on Wed, 15 Dec 2021 15:14:04 +0200

Programming VIP