【mysql】mysql之物理备份Xtrabackup

2024-09-05 12:28

本文主要是介绍【mysql】mysql之物理备份Xtrabackup,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

  本站以分享各种运维经验和运维所需要的技能为主

《python零基础入门》:python零基础入门学习

《python运维脚本》: python运维脚本实践

《shell》:shell学习

《terraform》持续更新中:terraform_Aws学习零基础入门到最佳实战

《k8》从问题中去学习k8s

《docker学习》暂未更新

《ceph学习》ceph日常问题解决分享

《日志收集》ELK+各种中间件

《运维日常》运维日常

《linux》运维面试100问

《DBA》db的介绍使用(mysql、redis、mongodb...)

一、物理备份Xtrabackup

1.安装

#上传文件包
[root@db03 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm#下载epel源
wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm#安装
[root@db03 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#安装好后的命令
[root@db03 ~]# xtrabackup
[root@db03 ~]# innobackupex

2.Xtrabackup备份

1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。
2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf(如果使用Xtrabackup备份,必须要配置datadir)

3.Xtrabackup全量备份

1)准备备份目录
[root@db03 ~]# mkdir /backup
2)备份(全备)
[root@db03 ~]# innobackupex --user=root --password=123 /backup/full#去掉时间戳进行备份
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full
3)查看全备内容
[root@db03 ~]# ll /backup/full/
总用量 129052
-rw-r----- 1 root root      434 7月  23 08:51 backup-my.cnf
drwxr-x--- 2 root root       68 7月  23 08:51 dump
-rw-r----- 1 root root 79691776 7月  23 08:51 ibdata1
-rw-r----- 1 root root 52428800 7月  23 08:51 ibdata2
drwxr-x--- 2 root root     4096 7月  23 08:51 mysql
drwxr-x--- 2 root root     4096 7月  23 08:51 performance_schema
drwxr-x--- 2 root root       68 7月  23 08:51 row
drwxr-x--- 2 root root       20 7月  23 08:51 test
-rw-r----- 1 root root       21 7月  23 08:51 xtrabackup_binlog_info			#记录binlog的信息
-rw-r----- 1 root root      113 7月  23 08:51 xtrabackup_checkpoints			
-rw-r----- 1 root root      483 7月  23 08:51 xtrabackup_info				#工具或数据的信息
-rw-r----- 1 root root     2560 7月  23 08:51 xtrabackup_logfile				#redo-log[root@db03 full]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 8417759

4.Xtrabackup全备恢复数据

1)删除所有数据库
mysql> drop database dump;mysql> drop database performance_schema;mysql> drop database row;mysql> drop database test;
2)停止数据库
[root@db03 ~]# systemctl stop mysqld.service
3)手动模拟CSR的过程
#将redo进行重做,已提交的写到数据文件,未提交的使用undo回滚,模拟CSR的过程
[root@db03 ~]# innobackupex --user=root --password=123 --apply-log /backup/full
4)恢复数据
1>方法一:
#移走原数据目录
[root@db03 mysql]# mv data data.back#将全备的数据目录迁移回来
[root@db03 mysql]# cp -r /backup/full ./data[root@db03 mysql]# chown -R mysql.mysql data
2>方法二(推荐):
#使用innobackupex恢复数据
[root@db03 mysql]# innobackupex ---back /backup/full/
[root@db03 mysql]# chown -R mysql.mysql data
5)启动数据库查看数据
#启动数据库
[root@db03 data]# systemctl start mysqld[root@db03 data]# mysql -uroot -p123
mysql> show databases;

5.Xtrabackup增量备份

1.基于上一次备份进行增量
2.增量备份无法单独恢复,必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
1)先全备
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full_$(date +%F)
2)写入新数据
[root@db03 ~]# mysql -uroot -p123
mysql> use dump
mysql> insert dump values(10000),(20000),(30000);
3)第一次增备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full_2020-07-23 /backup/inc1参数说明:
--incremental:开启增量备份功能
--incremental-basedir:上一次备份的路径#验证
[root@db03 ~]# cat /backup/full/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 8417759
[root@db03 ~]# cat /backup/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 8417759
to_lsn = 8419281
4)再次写入数据
[root@db03 ~]# mysql -uroot -p123
mysql> use dump
mysql> insert dump values(100000),(200000),(300000);
5)第二次增备
[root@db03 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1/ /backup/inc2#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1636167[root@db03 backup]# cat /backup/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1636167
to_lsn = 1640828[root@db03 backup]# cat /backup/inc2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1640828
to_lsn = 1645877
6)再次写入数据
7)第三次增量备份
[root@db03 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/inc2 /backup/inc3

6.Xtrabackup增量恢复数据

1)将全备执行redo
[root@db03 backup]# innobackupex --apply-log --redo-only /backup/full_2020-07-23
2)将第一次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1/ /backup/full_2020-07-23#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1640828			#该值本来是inc1的位置点
3)将第二次增备只执行redo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2/ /backup/full_2020-07-23#验证
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1645877			#该值本来是inc2的位置点
4)将最后一次增备执行redo和undo并合并到第一次全备
[root@db03 backup]# innobackupex --apply-log --incremental-dir=/backup/inc3/ /backup/full_2020-07-23#验证
[root@db03 backup]# 
[root@db03 backup]# cat /backup/full_2020-07-23/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1649869
5)将整体数据进行一次CSR
[root@db03 backup]# innobackupex --apply-log /backup/full_2020-07-23/
6)恢复数据
[root@db03 mysql]# mv data data.bak
[root@db03 mysql]# innobackupex ---back /backup/full_2020-07-23/
[root@db03 mysql]# chown -R mysql.mysql data
[root@db03 mysql]# systemctl start mysqld

7.总结

1.增备:优点:占用磁盘空间小,没有重复数据缺点:恢复麻烦2.全备:优点:恢复只需一次缺点:占用磁盘空间,每次全备都有重复数据

8.企业及增量恢复实战

要求

企业级增量恢复实战背景:
某大型网站,mysql数据库,数据量500G,每日更新量100M-200M备份策略:
xtrabackup,每周六0:00进行全备,周一到周五及周日00:00进行增量备份。故障场景:
周三下午2点出现数据库意外删除表操作。

思路

# 数据恢复流程
1. 将周六全备执行redo	
2. 将周日增备只执行redo并合并到周六全备
3. 将周一增备只执行redo并合并到周六全备
4. 将周二增备执行redo和undo并合并到周六全备
5. 将合并到周六的数据整体执行一次CSR
6. 通过binlog日志将周三0:00-14:00数据导出
7. 通过xtrabackup将数据恢复
8. 将binlog日志导入数据库

环境模拟

主机名ip角色
db01172.16.1.51数据库服务器

环境准备

# 0.前期工具准备:
[root@db01 ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
注:(下载方式)
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm# 1. 模拟创建大型网站500G数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| test               |
| world              |
+--------------------+# 2.准备备份目录
[root@db01 ~]# mkdir /backup# 3. 通过xtrabackup工具执行全量备份
[root@db01 ~]# innobackupex --user=root --no-timestamp /backup/full# 4. 查看备份全量文件
[root@db01 backup]# ll /backup/full/
total 12316
-rw-r----- 1 root root      418 Jul 23 15:00 backup-my.cnf
-rw-r----- 1 root root 12582912 Jul 23 15:00 ibdata1
drwxr-x--- 2 root root     4096 Jul 23 15:00 mysql
drwxr-x--- 2 root root     4096 Jul 23 15:00 performance_schema
drwxr-x--- 2 root root       58 Jul 23 15:00 student
drwxr-x--- 2 root root       20 Jul 23 15:00 test
drwxr-x--- 2 root root      144 Jul 23 15:00 world
-rw-r----- 1 root root       24 Jul 23 15:00 xtrabackup_binlog_info
-rw-r----- 1 root root      113 Jul 23 15:00 xtrabackup_checkpoints
-rw-r----- 1 root root      471 Jul 23 15:00 xtrabackup_info
-rw-r----- 1 root root     2560 Jul 23 15:00 xtrabackup_logfile# 5. 查看备份类型及备份点
[root@db01 full]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2588601
last_lsn = 2588601
compact = 0
recover_binlog_info = 0# 6. 模拟周日写入数据
mysql> select * from tcy;
+------+--------------+
| id   | name         |
+------+--------------+
|   77 | 周日数据     |
+------+--------------+# 7. 对周日数据执行增量备份
[root@db01 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/full /backup/sunday# 8. 查看备份数据类型及数据点
[root@db01 backup]# cat /backup/sunday/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2588601
to_lsn = 2595794
last_lsn = 2595794
compact = 0
recover_binlog_info = 0# 9. 模拟周一数据写入
mysql> select * from tcy;
+------+--------------+
| id   | name         |
+------+--------------+
|   77 | 周日数据     |
|   11 | 周一数据     |
+------+--------------+
2 rows in set (0.00 sec)# 10.模拟对周一数据执行增量备份
[root@db01 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/sunday /backup/monday# 11.查看备份数据数据类型及数据点
[root@db01 backup]# cat /backup/monday/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2595794
to_lsn = 2596831
last_lsn = 2596831
compact = 0
recover_binlog_info = 0# 12.模拟周二数据写入
mysql> select * from tcy;
+------+--------------+
| id   | name         |
+------+--------------+
|   77 | 周日数据     |
|   11 | 周一数据     |
|   22 | 周二数据     |
+------+--------------+
3 rows in set (0.00 sec)# 13. 对周二数据执行增量备份
[root@db01 backup]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/monday /backup/tuesday# 14. 查看备份数据类型及数据点
[root@db01 backup]# cat /backup/tuesday/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2596831
to_lsn = 2597868
last_lsn = 2597868
compact = 0
recover_binlog_info = 0# 15. 模拟周三数据写入
mysql> select * from tcy;
+------+--------------+
| id   | name         |
+------+--------------+
|   77 | 周日数据     |
|   11 | 周一数据     |
|   22 | 周二数据     |
|   33 | 周三数据     |
+------+--------------+# 16. 模拟故障(对表和库进行删除)
mysql> drop table tcy;
mysql> drop database student;

备份数据找回

# 1.全备数据redo找回(只执行redo)
[root@db01 backup]# innobackupex --apply-log --redo-only /backup/full# 2. 将周日增备份放入到周六全备中(只执行redo)
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/sunday /backup/full# 3. 将周一增备放入周六全备中(只执行redo)
[root@db01 backup]# innobackupex --apply-log --redo-only --incremental-dir=/backup/monday /backup/full# 4. 将周二增倍放入周六全备中(执行undo和redo)
[root@db01 backup]# innobackupex --apply-log --incremental-dir=/backup/tuesday /backup/full# 5.将整体数据进行一次CSR
[root@db01 data]# innobackupex --apply-log /backup/full# 6. 将binlog中00:00-14:00数据导出
[root@db01 data]#  mysqlbinlog -uroot --start-position=790602 --stop-position=790754 mysql-bin.000001 > /tmp/tuesday.sql# 7.将原本/usr/mysql/data目录进行移动备份
[root@db01 mysql]# mv data data.bak# 8. 通过xtrabackup将数据恢复
[root@db01 mysql]# innobackupex ---back /backup/full# 9. 服务启动
[root@db01 mysql]# chown -R mysql.mysql data
[root@db01 mysql]# systemctl restart mysqld# 10.查看通过增量和差异导入数据
mysql> select * from tcy;
+------+--------------+
| id   | name         |
+------+--------------+
|   77 | 周日数据     |
|   11 | 周一数据     |
|   22 | 周二数据     |
+------+--------------+
3 rows in set (0.00 sec)# 11.通过binlog将周三数据找回
mysql> source /tmp/tuesday.sql# 12.查看数据是否完整
mysql> select * from tcy;
+------+--------------+
| id   | name         |
+------+--------------+
|   77 | 周日数据     |
|   11 | 周一数据     |
|   22 | 周二数据     |
|   33 | 周三数据     |
+------+--------------+
4 rows in set (0.00 sec)

 

这篇关于【mysql】mysql之物理备份Xtrabackup的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1138953

相关文章

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》:本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁(一)原理(二)应用场景(三)示例代码三、悲观锁(一)原理(二)应用场景(三)示例

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一:桌面图标进入方法二:运行窗口进入方法三:查找文件路径方法四:检查 SQL Server 安

MySQL 中的 LIMIT 语句及基本用法

《MySQL中的LIMIT语句及基本用法》LIMIT语句用于限制查询返回的行数,常用于分页查询或取部分数据,提高查询效率,:本文主要介绍MySQL中的LIMIT语句,需要的朋友可以参考下... 目录mysql 中的 LIMIT 语句1. LIMIT 语法2. LIMIT 基本用法(1) 获取前 N 行数据(