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
- Percona XtraBackup feature description
- Percona Xtrabackup backup recovery permission restrictions
- Create backup users, configuration parameters and data preparation
- Full backup and recovery
- Incremental backup
- Compressed backup
- 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 are -- set variable = < variable > = < value > in the CNF configuration file. Xtrabackup is not recognized. You need to use -- variable = value instead of 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 = 33288485From_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/66...
Technology sharing | the way to repair MGR Bug in Wanli database
https://mp.weixin.qq.com/s/Ia...
Macos system compiler percona and the difference of some functions on Macos system
https://mp.weixin.qq.com/s/jA...
Technology sharing | using systemd to manage single machine and multiple instances of MySQL
https://mp.weixin.qq.com/s/iJ...
Product | GreatSQL to create a better MGR ecology
https://mp.weixin.qq.com/s/By...
Product | GreatSQL MGR optimization reference
https://mp.weixin.qq.com/s/5m...
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/Gr...
GitHub:
https://github.com/GreatSQL/G...
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!