mysql 8系备份学习总结
一、背景:1、 Percona Xtrabackup 8.0.25 不支持低于MySQL 8.0的备份(因为MySQL 8.0在数据字典、redo log中和之前版本不兼容),因此2.4版本的也不支持8系的备份
2、mysql8系,除了克隆方式,也可以采用 Xtrabackup进行数据恢复。全备份的好处是当数据库出现无法启动或人为的drop,truncate等导致的误操作,也可以通过全备份和binlog日志全量恢复数据
二、8系备份工具见附件,依赖包qpress是压缩解压时需要的命令
三、准备主备数据库,备库数据停止,删除data目录数据,从主库备份数据进行恢复
3.1 命令 参数 解释:
--compress 压缩备份
--compress-threads=4 压缩备份线程数
--decompress 压缩解压 xtrabackup --decompress --target-dir=/data/compressed/
--prepare 恢复数据预操作
--apply-log 回滚日志
--redo-only 对增量备份进行合并
3.2全备份命令:
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxxx --socket=/dbdata/mysql3308/my3308.sock --backup --no-server-version-check --compress --compress-threads=4 --target-dir=/home/e3base/test/mysql_backup/base-`date +%Y%m%d%H%M%S`
效果见图:
3.3增量备份1:
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxx --socket=/dbdata/mysql3308/my3308.sock --backup --no-server-version-check --compress --compress-threads=4 --target-dir=/home/e3base/test/mysql_backup/inc1-`date +%Y%m%d%H%M%S` --incremental-basedir=/home/e3base/test/mysql_backup/base-20221017145605
3.3增量备份2(不一一展示图片):
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxx --socket=/dbdata/mysql3308/my3308.sock --backup --no-server-version-check --compress --compress-threads=4 --target-dir=/home/e3base/test/mysql_backup/inc2-`date +%Y%m%d%H%M%S` --incremental-basedir=/home/e3base/test/mysql_backup/inc1-20221017150134
3.4 数据恢复
解压:
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --decompress --target-dir=/home/e3base/test/mysql_backup/base-20221017145605
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --decompress --target-dir=/home/e3base/test/mysql_backup/inc1-20221017150134
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --decompress --target-dir=/home/e3base/test/mysql_backup/inc2-20221017151342
恢复数据
先重放日志和数据文件,并避免回滚全量备份过程中未提交的事务
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --prepare --apply-log-only --target-dir=/home/e3base/test/mysql_backup/base-20221017145605
再重放第一次增量备份文件中的日志和数据文件,并避免此次备份过程中未提交事务的回滚
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --prepare --apply-log-only --target-dir=/home/e3base/test/mysql_backup/base-20221017145605 --incremental-dir=/home/e3base/test/mysql_backup/inc1-20221017150134
再重放第二次增量备份文件中的日志和数据文件,并避免此次备份过程中未提交事务的回滚
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --prepare --apply-log-only --target-dir=/home/e3base/test/mysql_backup/base-20221017145605 --incremental-dir=/home/e3base/test/mysql_backup/inc2-20221017151342
应用数据文件
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --copy-back --target-dir=/home/e3base/test/mysql_backup/base-20221017145605
四、远程备份数据:
4.1全量备份流的形式备份到远程主机:
压缩,流方式备份
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxx --socket=/dbdata/mysql3308/my3308.sock --backup --parallel=4 --slave-info --no-timestamp --compress --compress-threads=4 --stream=xbstream --target-dir=/dbdata/backup --tmpdir=/dbdata/backup/tmp 2> /dbdata/backup_3308.log | ssh e3base@192.xx.xx.xx "cat - > /dbdata/backup/backup_3308_`date '+%Y%m%d%H'`.xbstream" &
远程主机文件:
流解压:
/home/e3base/xtrabackup-8.0.25/bin/xbstream -x </dbdata/backup/backup_3308_2022101715.xbstream -C backup4
compress压缩解压,解压成功会有ok字样:
远程恢复全备份:
重放日志和数据文件,并避免回滚全量备份过程中未提交的事务
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --prepare --target-dir=/dbdata/backup/backup4
应用数据文件
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --copy-back --target-dir=/dbdata/backup/backup4
备份数据恢复数据一致性校验(恢复数据e3base1与备份实例e3base2数据表数据一致,全备份成功):![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/2dc86abd-16f1-4d73-9006-9b4f75110d9e.png)
2、mysql8系,除了克隆方式,也可以采用 Xtrabackup进行数据恢复。全备份的好处是当数据库出现无法启动或人为的drop,truncate等导致的误操作,也可以通过全备份和binlog日志全量恢复数据
二、8系备份工具见附件,依赖包qpress是压缩解压时需要的命令
三、准备主备数据库,备库数据停止,删除data目录数据,从主库备份数据进行恢复
3.1 命令 参数 解释:
--compress 压缩备份
--compress-threads=4 压缩备份线程数
--decompress 压缩解压 xtrabackup --decompress --target-dir=/data/compressed/
--prepare 恢复数据预操作
--apply-log 回滚日志
--redo-only 对增量备份进行合并
3.2全备份命令:
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxxx --socket=/dbdata/mysql3308/my3308.sock --backup --no-server-version-check --compress --compress-threads=4 --target-dir=/home/e3base/test/mysql_backup/base-`date +%Y%m%d%H%M%S`
效果见图:
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/c029ad24-efe8-4217-9650-b90e4b176dd8.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/28ccf295-e20b-46c0-8584-809e93b46e0d.png)
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxx --socket=/dbdata/mysql3308/my3308.sock --backup --no-server-version-check --compress --compress-threads=4 --target-dir=/home/e3base/test/mysql_backup/inc1-`date +%Y%m%d%H%M%S` --incremental-basedir=/home/e3base/test/mysql_backup/base-20221017145605
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/7f1116f0-ff48-440f-99d9-6287a46d9799.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/cd8236ba-de31-40a9-93a0-2c8bc724a685.png)
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxx --socket=/dbdata/mysql3308/my3308.sock --backup --no-server-version-check --compress --compress-threads=4 --target-dir=/home/e3base/test/mysql_backup/inc2-`date +%Y%m%d%H%M%S` --incremental-basedir=/home/e3base/test/mysql_backup/inc1-20221017150134
3.4 数据恢复
解压:
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --decompress --target-dir=/home/e3base/test/mysql_backup/base-20221017145605
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --decompress --target-dir=/home/e3base/test/mysql_backup/inc1-20221017150134
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --decompress --target-dir=/home/e3base/test/mysql_backup/inc2-20221017151342
恢复数据
先重放日志和数据文件,并避免回滚全量备份过程中未提交的事务
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --prepare --apply-log-only --target-dir=/home/e3base/test/mysql_backup/base-20221017145605
再重放第一次增量备份文件中的日志和数据文件,并避免此次备份过程中未提交事务的回滚
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --prepare --apply-log-only --target-dir=/home/e3base/test/mysql_backup/base-20221017145605 --incremental-dir=/home/e3base/test/mysql_backup/inc1-20221017150134
再重放第二次增量备份文件中的日志和数据文件,并避免此次备份过程中未提交事务的回滚
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --prepare --apply-log-only --target-dir=/home/e3base/test/mysql_backup/base-20221017145605 --incremental-dir=/home/e3base/test/mysql_backup/inc2-20221017151342
应用数据文件
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --no-server-version-check --copy-back --target-dir=/home/e3base/test/mysql_backup/base-20221017145605
四、远程备份数据:
4.1全量备份流的形式备份到远程主机:
压缩,流方式备份
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --user=xxx --password=xxx --socket=/dbdata/mysql3308/my3308.sock --backup --parallel=4 --slave-info --no-timestamp --compress --compress-threads=4 --stream=xbstream --target-dir=/dbdata/backup --tmpdir=/dbdata/backup/tmp 2> /dbdata/backup_3308.log | ssh e3base@192.xx.xx.xx "cat - > /dbdata/backup/backup_3308_`date '+%Y%m%d%H'`.xbstream" &
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/6d2649d3-d203-4b1f-ace2-85caee193936.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/e7da2491-46a7-4088-a834-8eb22b7d7a6c.png)
远程主机文件:
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/7c31ac5a-4177-475c-95a9-bc420bb350d2.png)
/home/e3base/xtrabackup-8.0.25/bin/xbstream -x </dbdata/backup/backup_3308_2022101715.xbstream -C backup4
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/03f41f39-ff38-4cea-bcef-b7802776cc63.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/fdd80833-ed35-4993-b8e0-33883b40571c.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/b29dc8eb-93a4-47cd-9b18-7ba0b24f04c0.png)
重放日志和数据文件,并避免回滚全量备份过程中未提交的事务
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --prepare --target-dir=/dbdata/backup/backup4
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/b097d177-775a-4400-9870-3f6c4c0418ac.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/3da8351a-35b3-41b9-9a75-8e06fc0537d0.png)
/home/e3base/xtrabackup-8.0.25/bin/xtrabackup --defaults-file=/dbdata/mysql3308/my3308.cnf --copy-back --target-dir=/dbdata/backup/backup4
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/cb857f06-95c7-4157-8ac8-56cf43a7cd37.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/c2500807-1ab9-4aea-8b35-7644a34597dc.png)
备份数据恢复数据一致性校验(恢复数据e3base1与备份实例e3base2数据表数据一致,全备份成功):
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/2dc86abd-16f1-4d73-9006-9b4f75110d9e.png)
![](http://media.teamshub.com/10000/tm/2022/10/17/a2bb433f-7970-4ea1-9ff2-bc17ce9d2145/db29619f-6a18-40f9-9ee4-9c28c3f5ca32.png)