教你玩转MySQL8物理备份利器Xtrabackup

教你玩转MySQL8物理备份利器Xtrabackup

原创 我科绝伦 小周的数据库进阶之路
 

热衷于分享各种干货知识，大家有想看或者想学的可以评论区留言，秉承着“开源知识来源于互联网，回归于互联网”的理念，分享一些日常工作中能用到或者频率比较的内容，希望大家能够喜欢，并提出宝贵地意见，我们一起提升，守住自己的饭碗。

小周的数据库进阶之路
致力于dba数据库经验分享、新手入门、原理解读、踩坑指南
171篇原创内容

一、Xtrabackup介绍

xtrabackup是由Percona公司开发的一个用于MySQL数据库物理热备的工具，开源免费，目前官方最新版本是8.0.35，也支持8.0.37版本数据库，本文已经过测试。另外8.1，8.2和8.3也支持。它具有如下优点：

（1）备份速度快，物理备份可靠；

（2）在备份期间不间断地处理事务； 

（3）支持备份压缩，节约磁盘空间和网络带宽； 

（4）自动校验备份。

xtrabackup支持流式输出、压缩、增量备份、加密，是目前各个备份厂商普遍使用的MySQL备份工具。

二、Xtrabackup备份原理

1、xtrabackup开始，记录lsn ，同时启动后台进程监控redo日志的变化，且将变化实时记录到xtrabackup_logfile中；

2、复制ibdata1，.ibd数据文件；

3、全局读锁，执行LOCK INSTANCE FOR BACKUP（8.0取代了 FLUSH TABLES WITH READ LOCK）； 

4、复制非InnoDB的表和文件；

5、获取binlog位置信息； 

6、停止复制redo log，即停止记录xtrabackup_logfile；

7、执行UNLOCK INSTANCE释放锁； 

8、复制buffer pool； 

9、备份完成。

图片

三、下载软件

官网地址：https://www.percona.com/downloads Percona XtraBackup 8.0 只支持MySQL 8.0的版本 

Percona XtraBackup 2.4 支持MySQL 5.11 , 5.5, 5.6 和5.7的版本

大家按需下载对应的版本

图片

当然，也可以从网盘下载

链接: https://pan.baidu.com/s/1cWZ_Vbf8d-eUaAsawA2lUg 

提取码: sfka

图片

四、Xtrabackup安装

上传安装包至/usr/local目录下，并重命名为xtrabackup目录

[root@mysql local]# tar -xvf percona-xtrabackup-8.0.35-31-Linux-x86_64.glibc2.17.tar.gz
[root@mysql local]# mv percona-xtrabackup-8.0.35-31-Linux-x86_64.glibc2.17 xtrabackup
[root@mysql local]# echo "export PATH=\$PATH:/usr/local/xtrabackup/bin" >>/etc/profile
[root@mysql local]# source /etc/profile
[root@mysql local]#which xtrabackup
[root@myoracle local]#xtrabackup --version
[root@myoracle local]#yum install perl-Digest-MD5 -y #安装依赖，要不然备份会报错
图片

五、xtrabackup常用参数

--user：指定连接数据库的用户名。
--password：指定连接数据库的密码。
--backup：执行备份操作。
--prepare：执行备份准备操作。
--copy-back：将备份恢复到原来的数据目录。
--datadir：指定数据目录的路径。
--target-dir：指定备份文件存储目录。
--incremental：执行增量备份操作。
--incremental-lsn：指定增量备份的LSN号。
--tables：仅备份特定的表。
--databases：仅备份特定的数据库。
--compress：压缩备份文件。#开启备份压缩，根据数据内容不同，压缩比可以在50%～60%
--stream=xbstream #开启流式备份，备份出来的数据输出到stdout
--remote-host=user@ip DST_DIR #备份到远程主机

六、备份

新建备份目录

[root@mysql ~]# mkdir -p /data/backup
[root@mysql ~]# chown -R mysql:mysql /data/backup/

新增测试数据

root@localhost:(none)>CREATE DATABASE test;
root@localhost:test>use test;
root@localhost:test>droptableifexists test;
root@localhost:test>CREATETABLE test ( id intNOTNULL AUTO_INCREMENT PRIMARY KEY comment '自增主键',
dept tinyint notnull comment '部门id',
age tinyint notnull comment '年龄',
name varchar(30) comment '用户名称',
create_time datetime notnull comment '注册时间',
last_login_time datetime comment '最后登录时间'
) comment '测试表';
root@localhost:test>insertinto test values(1,1,25,'zhangsan','2024-09-01 00:00:00','2024-09-01 12:00:00');
root@localhost:test>set@i=1;
root@localhost:test>insertinto test(dept, age, name, create_time, last_login_time)selectleft(rand()*10,1)as dept,
FLOOR(20+RAND()*(50-20+1))as age, concat('user_',@i:=@i+1),
date_add(create_time,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(create_time,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)from test;
root@localhost:test>selectcount(1)from test;
root@localhost:test>create index idx_dept on test(dept);create index idx_create_time on test(create_time);
root@localhost:test>create index idx_last_login_time on test(last_login_time);
图片

以上步骤准备继续后，开始全量备份。

（一）全量备份

赋予账号备份权限

root@localhost:(none)>grant BACKUP_ADMIN on *.* to 'root'@'%';
root@localhost:(none)>FLUSH PRIVILEGES;

开始全备

[root@mysql backup]# xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/db_`date +%F`   --user=root --password=123456 --socket=/tmp/mysql.sock  >/data/backup/db_full_`date +%F`.log 2>&1
图片

（二）增量备份

新增数据

root@localhost:test>set@i=1;
root@localhost:test>insertinto test(dept, age, name, create_time, last_login_time)selectleft(rand()*10,1)as dept,
FLOOR(20+RAND()*(50-20+1))as age, concat('user_',@i:=@i+1),
date_add(create_time,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(create_time,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)from test;
root@localhost:test>selectcount(1)from test;
图片

开始增量备份

新建增量备份目录 

[root@mysql ~]# mkdir -p /data/backup/incremental

[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --no-lock --backup --socket=/tmp/mysql.sock --target-dir=/data/backup/incremental --incremental-basedir=/data/backup/db_`date +%F` #先不演示流式备份

图片图片

注：--incremental-basedir 首次增量备份，就指定全量备份路径。如果是第二次增量备份，就指定第一次增量备份路径。 

再次插入数据

root@localhost:test>set@i=1;
root@localhost:test>insertinto test(dept, age, name, create_time, last_login_time)selectleft(rand()*10,1)as dept,
FLOOR(20+RAND()*(50-20+1))as age, concat('user_',@i:=@i+1),
date_add(create_time,interval+@i*cast(rand()*100assigned)SECOND),
date_add(date_add(create_time,interval+@i*cast(rand()*100assigned)SECOND),interval+cast(rand()*1000000assigned)SECOND)from test;
root@localhost:test>selectcount(1)from test;

新建增量备份的目录并进行备份

[root@mysql incremental]# mkdir -p /data/backup/incremental2
[root@mysql incremental]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --no-lock --backup --socket=/tmp/mysql.sock --target-dir=/data/backup/incremental2 --incremental-basedir=/data/backup/incremental
图片图片

六、恢复

（一）全量恢复

这里我们演示删掉数据盘进行恢复。

root@localhost:test>show variables like 'datadir';
[root@mysql ~]# mv /data/mysql/ /data/mysql.bak
[root@mysql incremental2]# xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db_2024-09-22 #备份文件一致性检查
图片 
[root@mysql incremental2]#xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/db_2024-09-22 #将备份文件从restore_dir拷贝到work_dir目录
图片

给恢复的数据目录赋予mysql权限

[root@mysql data]# chown -R mysql.mysql mysql
图片

启动数据库

[root@mysql data]# ps -ef|grep mysql
[root@mysql data]# service mysql start #如果起不来，需要杀掉之前的进程。
图片

验证数据

root@localhost:(none)>use test;
root@localhost:test>select count(1) from test;
图片

（二）使用binlog恢复数据

[root@mysql db_2024-09-22]# cat xtrabackup_binlog_info
mysql-bin.000005 197 5c4a9483-781b-11ef-84a6-000c29f8db3f:1-17
图片 
[root@mysql db_2024-09-22]# mysqlbinlog -vv --start-position=197 /data/mysql.bak/mysql-bin.000005 > recoverdata.sql
root@localhost:(none)>source /data/backup/db_2024-09-22/recoverdata.sql
root@localhost:test>select count(1) from test;
图片

（三）利用增量备份数据恢复

[root@mysql db_2024-09-22]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/db_2024-09-22 #准备数据
图片 
[root@mysql db_2024-09-22]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/db_2024-09-22 --incremental-dir=/data/backup/incremental ## 把增量备份的数据合并到完整备份里面
图片 
[root@mysql db_2024-09-22]#xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --prepare --target-dir=/data/backup/db_2024-09-22 #备份文件一致性检查
[root@mysql db_2024-09-22]#xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123456 --copy-back --target-dir=/data/backup/db_2024-09-22 # 恢复数据
图片

修改权限，启动

[root@mysql data]# chown -R mysql.mysql mysql
[root@mysql data]# service mysql start
图片

验证数据

root@localhost:(none)>use test;
root@localhost:test>select count(1) from test;
图片

七、总结

这篇文章主要讲述如何安装Xtrabackup和备份恢复数据库。后面会分享一键备份和恢复脚本，也会分享基于时间点和各种场景的应急恢复。

大家如果想要测试异机备份，一键安装脚本可以参考以下连接

MySQL数据库一键安装脚本，适合任何版本

全网首例！MySQL8 MGR多主一键搭建脚本分享

实战！MySQL主从复制一键搭建脚本分享

小周的数据库进阶之路
致力于dba数据库经验分享、新手入门、原理解读、踩坑指南
171篇原创内容

 

文中的概念来源于互联网，如有侵权，请联系我删除。

欢迎关注公众号：小周的数据库进阶之路，一起交流数据库、中间件和云计算等技术。如果觉得读完本文有收获，可以转发给其他朋友，大家一起学习进步！感兴趣的朋友可以加我微信，拉您进群与业界的大佬们一起交流学习。

 

 

我科绝伦
MySQL5
备份恢复7
日常运维10
MySQL · 目录
下一篇MySQL8物理一键备份和恢复脚本分享
个人观点，仅供参考
阅读 710
 
​
喜欢此内容的人还喜欢
 
使用OGG 21版本完成Oracle19C到MySQL8的实时同步
 
 
我看过的号
小周的数据库进阶之路
 
MySQL分库分表：如何优雅地解决唯一主键的挑战？
 
 
我看过的号
架构经纬
 
一款轻量级、功能强大的可协作Excel在线表格，适用于Vue、React
 
 
前端学研社
写留言
 
 
 
 
 
 
 
 
写留言
留言
 

暂无留言

posted @ 2024-09-26 09:49  技术颜良  阅读(110)  评论(0)    收藏  举报