Welcome to this article on MySQL technology shared by the GreatSQL community. If you have any questions or questions you would like to learn, please leave them in the comments section below

• 1. Percona XtraBackup feature Description
• 2. Percona Xtrabackup Backup and restore permission restriction
• 3. Create backup users, configure parameters, and prepare data
• 4. Full backup and restoration
• 5. Incremental backup
• 6. Compression backup
• 7. Flow backup

1. Percona XtraBackup feature Description

1)Percona Xtrabackup 8.0.26 added support for MyRocks, not TokuDB

2)Percona Xtrabackup 8.0.26 does not support backups lower than MySQL 8.0 (MySQL 8.0 is incompatible with data dictionary, redo log and previous versions)

Percona Xtrabackup 8.0.26 Xtrabackup 8.0.26 Xtrabackup 8.0.26

4) The backup file must be empty without any files

2. Percona Xtrabackup backup and restore permission restrictions

1) The backup path must have read and write permissions

Reload and Lock Tables (except for the –no-locak option) because FLUSH Tables WITH READ Lock and FLUSH ENGINE LOGS need to be executed before backup

Performance_schema. log_status; 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 You need to create a table to restore it

6) Process. To back up data, run the show engine Innodb status command

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

8) Create permissions. To create the perconA_schema.xtrBackup_history table

9) the Alter permissions. To update the perconA_schema.xtrBackup_HISTORY table

To Insert 10). To insert the history into the perconA_schema.xtrBackup_HISTORY table

To Select 11). To query historical data

3. Create backup users, configure parameters, and prepare data

Mysql > CREATE USER 'bkpuser' @ 'localhost' IDENTIFIED BY 's3cr%T'; 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 ;Copy the code

[mysqld] : [xtrabackup] : [mysqld] : [xtrabackup] So we can set the backup directory [xtrabackup] in the configuration file,target_dir = /data/backups/mysql

Note: If there is –set-variable== in the my.cnf configuration file, xtrabackup does not identify it. Use –veriable=value instead of unavailable version check. The –no-server-version-check parameter is added to Percona Xtrabackup 8.0.21, which compares the backup source version with that of Percona Xtrabackup

The source system is the same as the PXB version. The backup continues. The source system is earlier than the PXB version and parameters are not overwritten

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

When overwriting parameters, a backup failure, a corrupted backup, and a successful backup may occur

3.1 Sysbench generates data

mysql> create database sbtest; 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 // check  MySQL [sbtest]> select count(*) from sbtest.sbtest1; + -- -- -- -- -- -- -- -- -- -- + | count (*) | + -- -- -- -- -- -- -- -- -- -- + | 5000 | + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

4. Full backup and restoration

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/  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!Copy the code

4.2 Full Recovery

Preparation, because the –backup data files are not synchronized at any point in time (the backup incremental redo log thread listens for incremental data in real time), because programs are copied at different points in time and may be changed during re-copying, if InnoDB directly tries to start these data files, Innodb will detect corruption and stop running, so use –prepare to crash restore the backup files to use these copied files

Shell>xtrabackup --prepare --target-dir=/data1/xtrabackup/full
Copy the code

// Copy data Note: The source directory must be backed up and the recovery directory must be empty

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

// Change directory properties to start the database

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

// Start the database

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

/ / testing

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

5. Incremental backup

Incremental backup needs to be done on a full backup basis, with each InnoDB page containing a log sequence number LSN. The LSN is the version number of the entire database system. The LSN for each page shows the time of the last change. During incremental backup, the LSN of the last full backup or incremental backup is compared with the LSN of the current page, and the search continues using the two algorithms.

The first method is to read all the data pages and check the LSN directly (for all versions of MySQL). The second method is to enable the “change page tracking” function on the server, which records the changes of the page and saves the information. Xtrbackup does incremental backup to read the information. Save resources (this lookup method works for Percona Server for MySQL)

• Full amount of 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/
Copy the code

• Application Trunk Logs

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

• Output:

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

• Incremental backup: Query xtrBackup_checkpoints of the full backup directory

Shell> cat xtrabackup_checkpoints backup_type = full-prepared // Backup type from_lsn = 0 // Start LSN to_lsn = 22254648// End backup LSN Last_lsn = 22254648// Flushed_lsn = 22254648// LSN number of flushCopy the code

• 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
Copy the code

• 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! Shell> cat xtrabackup_checkpoints backup_type = incremental from_lsn = 22254648 to_lsn = 33288485 last_lsn = 33299549 flushed_lsn = 33288485Copy the code

From_lsn is the starting LSN of the backup and must be the same as the previous backup checkpoint to_lsn for deltas

• Application Trunk Logs

– Prepare Incremental backup Is different from full backup and is executed during full backup. – Prepare To ensure database consistency, it plays back committed transactions from log files for data files and rolls back uncommitted transactions. In increments, the rollback of uncommitted transactions must be skipped because uncommitted transactions may be in progress at backup time and will most likely be committed in the next incremental backup, so the –apply-log-only option is required to prevent the rollback phase.

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

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

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!Copy the code

Note: The to_LSN used in full backup is the same as the to_LSN used in incremental backup that I/O change. If you use the –prepare –apply-log-only command to merge incremental backup data into a full backup, an LSN conflict is 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.
Copy the code

5.1 Incremental Recovery

Shell> xtrabackup --prepare --target-dir=/data1/xtrabackup/full /  Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf --copy-back --target-dir=/data1/xtrabackup/full // Modify data directory permissions and properties: 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 > select count(*) from sbtest.sbtest1;Copy the code

Full backup + Add recovery is complete

6. Compress the backup

We can compress backup files during backup using 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
Copy the code

Since — 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
Copy the code

Adding a 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
Copy the code

The following information is displayed:

. 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!Copy the code

6.1 Decompressing Backup and Restoration

Unzip files:

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!Copy the code

Backup and restore:

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

7. Flow backup

Use a stream backup to transport to the name backup.xbStream

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

Encrypted backups

Shell > xtrabackup - backup - stream = xbstream. / > backup. Xbstream gzip - | openssl des3 - salt - k "password" > backup.xbstream.gz.des3Copy the code

Pressurized backup to current directory

Shell> xbstream -x <  backup.xbstream
Copy the code

Use stream backup to other servers

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

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"Copy the code

Enjoy GreatSQL 🙂

Article recommendation:

Technology sharing | MGR Best practices (MGR Best Practice) mp.weixin.qq.com/s/66u5K7a9u…

Share | wanli database MGR Bug fixes mp.weixin.qq.com/s/IavpeP93h road…

Macos system compile percona and some functions on Macos systems operation difference mp.weixin.qq.com/s/jAbwicbRc…

Technology sharing | use systemd manage MySQL standalone multi-instance mp.weixin.qq.com/s/iJjXwd0z1…

Products | GreatSQL, create a better ecological mp.weixin.qq.com/s/ByAjPOwHI MGR…

Products | GreatSQL MGR optimization reference mp.weixin.qq.com/s/5mL_ERRIj…

About GreatSQL

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

Gitee: gitee.com/GreatSQL/Gr…

Making: github.com/GreatSQL/Gr…

Wechat &QQ Group:

You can search to add GreatSQL Community Assistant wechat friends, and send the verification message “Add group” to join the GreatSQL/MGR communication wechat group

QQ group: 533341697 wechat assistant: WanliDBC

This article is published by OpenWrite!