欢送来到 GreatSQL社区分享的MySQL技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答

1. Percona XtraBackup个性阐明
1. Percona Xtrabackup 备份复原权限限度
1. 创立备份用户、配置参数及数据筹备
1. 全量备份与复原
1. 增量备份
1. 压缩备份
1. 流备份

1. Percona XtraBackup 个性阐明

1)Percona Xtrabackup 8.0.26新增反对MyRocks存储引擎，不反对TokuDB引擎

2)Percona Xtrabackup 8.0.26 不反对低于MySQL 8.0的备份（因为MySQL 8.0在数据字典、redo log中和之前版本不兼容）

3)Percona Xtrabackup 8.0.26 目前X86版本能够从官网下载，ARM版本须要手动编译

4)备份文件必须是空的，没有任何文件

2. Percona Xtrabackup 备份复原权限限度

1)备份门路须要有可读写权限

2)reload和Lock Tables（指定--no-locak选项除外），因为备份前须要执行FLUSH TABLES WITH READ LOCK和FLUSH ENGINE LOGS

3)Backup_admin权限,因为备份时须要查问performance_schema.log_status表并运行LOCK INSTANCE FOR BACKUP, LOCK BINLOG FOR BACKUP, or LOCK TABLES FOR BACKUP

4)Replication client权限。备份时为了读取二进制日志文件

5)Create tablespace权限。复原表时须要创立表

6)Process权限。备份时须要运行show engine innodb status命令

7)Super权限。为了在复制环境中启动/进行复制线程

8)Create权限。为了创立percona_schema.xtrbackup_history表

9)Alter权限。为了更新percona_schema.xtrbackup_history表

10)Insert权限。为了将历史记录插入到percona_schema.xtrbackup_history表

11)Select权限。为了查问历史数据

3. 创立备份用户、配置参数及数据筹备

    //创立用户    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 ;

配置参数，Xtrbackup在备份时会读取MySQL的my.cnf配置文件中[mysqld]和[xtrabackup]局部，所以咱们能够在配置文件中设置备份的目录[xtrabackup],target_dir = /data/backups/mysql

留神：如果my.cnf配置文件中有--set-variable=<variable>=<value>，xtrabackup不辨认，须要应用--veriable=value代替版本检测,Percona xtrabackup 8.0.21增加了--no-server-version-check参数，会将备份源版本和Percona Xtrabackup版本比照

源零碎和PXB版本一样，备份持续源零碎低于PXB版本，备份持续源零碎大于PXB版本，且参数未被笼罩，进行备份并返回谬误源零碎大于PXB版本，参数被笼罩，备份持续

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

笼罩参数时，可能会产生备份失败、创立损坏的备份、备份胜利

3.1 Sysbench造数据

mysql> create database sbtest;//应用sysbench造数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//查看MySQL [sbtest]> select count(*) from sbtest.sbtest1;+----------+| count(*) |+----------+| 5000 |+----------+1 row in set (0.00 sec)

4. 全量备份与复原

4.1 全量备份

    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!

4.2 全量复原

筹备工作,因为应用--backup备份数据文件时工夫点时不统一的（备份增量redo日志线程会实时监听增量数据），因为程序在不同的工夫点复制，并且再复制过程中可能会被更改，如果应用innodb间接尝试启动这些数据文件，innodb将检测损坏并进行运行，所以须要应用--prepare将将备份文件进行解体复原，来应用这些复制文件

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

// 拷贝数据留神：须要将源目录进行备份，且复原目录要为空

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

// 批改目录属性启动数据库

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

// 启动数据库

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

// 检测

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

5. 增量备份

增量备份须要在全量备份的根底上进行，每个innodb页面都蕴含一个日志序列号LSN。该LSN是整个数据库系统的版本号。每个页面的LSN显示最近更改的工夫。增量备份时会将上一次全量备份的LSN或上一次增量备份的LSN和以后页面的LSN进行比照，通过两种算法持续查找。

第一种就是读取所有的数据页面间接查看页面LSN（实用所有版本MySQL），第二种就是在服务器上启用”更改页面跟踪“性能，该性能会在页面更改时进行记录，将信息保留下来，xtrbackup做增量备份时回去读取该信息，从而节俭资源（该查找办法实用于percona server for MySQL）

全量备份

    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/

利用中继日志

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

输入：

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

增量备份:查看全量备份目录的xtrbackup_checkpoints

    Shell>  cat xtrabackup_checkpoints     backup_type = full-prepared //备份类型    from_lsn = 0 //开始LSN    to_lsn = 22254648//备份完结时LSN    last_lsn = 22254648   //上一个LSN号    flushed_lsn = 22254648//以后flush的LSN号

增量备份命令

    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

输入信息：

 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!    //查看以后checkpoints点    Shell>  cat xtrabackup_checkpoints     backup_type = incremental    from_lsn = 22254648    to_lsn = 33288485    last_lsn = 33299549    flushed_lsn = 33288485

From_lsn是备份的起始LSN，对于增量，他必须与前一个备份检查点to_lsn雷同

利用中继日志

--prepare增量备份复原时与全量备份不同，在全量备份时执行--prepare为了保障数据库统一，针对数据文件从日志文件重放已提交的事务，以及回滚未提交的事务。在增量时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行中，很可能在下一次增量备份中提交，所以须要应用--apply-log-only选项来避免回滚阶段。

将第一个增量备份利用到全量备份中（增量文件将会利用到/data1/xtrbackup/full中）

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

输入信息：

incremental backup from 1626007 is enabled.xtrabackup: cd to /data/backups/basextrabackup: 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 bytesApplying /tmp/backups/inc1/ibdata1.delta to ./ibdata1......161011 12:45:56 completed OK!

留神：全量备份中的to_LSN号和增量备份中的checkpoints的to_lsn号雷同如果做屡次增量备份时，须要在每次做完增量备份后应用--prepare --apply-log-only将增量局部合并到全量备份中，否则会报lsn抵触

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 增量复原

Shell> xtrabackup --prepare --target-dir=/data1/xtrabackup/full // 拷回数据：Shell> xtrabackup --defaults-file=/data1/mysqldata/my.cnf  --copy-back --target-dir=/data1/xtrabackup/full// 批改数据目录的权限和属性：Shell> chown -R mysql:mysql /data1/mysqldata1 Shell> chmod -R 755 /data1/mysqldata1 启动数据库Shell>/data1/mysql/bin/mysqld --defaults-file=/data1/mysqldata/my.cnf --user=mysql &// 检测MySQL> select count(*) from sbtest.sbtest1;

全备+增备复原实现

6. 压缩备份

咱们在备份时能够对备份文件进行压缩备份，应用--compress选项

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

因为--compress应用到了qpress，所以须要装置percona-release包

Shell> suudo percona-release enable toolsShell> sudo apt-get updateShell> sudo apt-get install qpress

减少备份线程

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

输入如下信息：

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

6.1 解压备份及复原

解压文件：

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

备份复原：

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

7. 流备份

应用流备份传输到名为 backup.xbstream

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

加密备份

Shell> xtrabackup –backup –stream=xbstream ./ > backup.xbstream gzip - | openssl des3 -salt -k “明码” > backup.xbstream.gz.des3

加压备份到当前目录

Shell> xbstream -x <  backup.xbstream

应用流备份形式到其余服务器上

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

限度备份工具的吞吐量为10MB/秒

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

Enjoy GreatSQL :)

文章举荐：

技术分享 | MGR最佳实际(MGR Best Practice)
https://mp.weixin.qq.com/s/66...

技术分享 | 万里数据库MGR Bug修复之路
https://mp.weixin.qq.com/s/Ia...

Macos零碎编译percona及局部函数在Macos零碎上运算差别
https://mp.weixin.qq.com/s/jA...

技术分享 | 利用systemd治理MySQL单机多实例
https://mp.weixin.qq.com/s/iJ...

产品 | GreatSQL，打造更好的MGR生态
https://mp.weixin.qq.com/s/By...

产品 | GreatSQL MGR优化参考
https://mp.weixin.qq.com/s/5m...

对于 GreatSQL

GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。

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

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

微信&QQ群：

可搜寻增加GreatSQL社区助手微信好友，发送验证信息“加群”退出GreatSQL/MGR交换微信群

QQ群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！