MySQL数据库备份与恢复 Part 3：Xtrabackup

最新推荐文章于 2022-11-14 16:29:05 发布
最新推荐文章于 2022-11-14 16:29:05 发布
阅读量195 收藏
点赞数 1
分类专栏： MySQL 5.7 文章标签： MySQL backup&restore
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sandy9919/article/details/89068209
版权

1 Xtrabackup介绍

          Xtrabackup是Percona公司针对MySQL数据库开发的一款开源物理备份工具，可以对InnoDB和XtraDB等事务引擎的数据库实现非阻塞备份，也可以对MyISAM等非事务引擎实现锁表方式备份。Xtrabackup特点如下

  • 复制物理文件，备份和恢复数据的速度非常快，安全可靠
  • 备份期间执行的事务不中断，备份InnoDB数据不影响业务连续性
  • 备份期间不会增加数据库性能压力
  • 自动校验备份数据
  • 全量、增量、压缩及流备份
  • 在线迁移数据表、快速创建从库
  • 版本兼容性好，支持全系列的MySQL和MariaDB

2  Xtrabackup安装步骤

2.1 系统环境说明

--- 查看系统版本

cat /etc/redhat-release

--- 查看内核版本

uname -r

--- 查看系统位数

uname -m

 2.2 安装Xtrabackup

--- 安装基础环境包

yum install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

--- 下载Xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

--- 安装Xtrabackup

yum install percona-xtrabackup-24-2.4.13-1.el7.x86_64.rpm

--- 查看安装结果

ls -l `which xtrabackup innobackupex`

3 Xtrabackup备份与恢复

3.1 Xtrabackup命令说明

  • Xtrabackup用于InnoDB和XtraDB等事务引擎的数据库的热备份工具，不能用于备份MyISAM引擎数据
  • Innobackupex是Xtrabackup命令基于perl语言进行二次封装的工具，支持Xtrabackup功能，还可以备份MyISAM及多种引擎混合使用的场景

3.2 创建数据库用户

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup123';

GRANT RELOAD,LOCK TABLES,PROCESS,REPLICATION CLIENT ON *.* TO 'backup'@'localhost';

GRANT BACKUP_ADMIN,SELECT ON *.* TO 'backup'@'localhost';

FLUSH PRIVILEGES;

3.3 Innobackupex介绍 

        语法如下

innobackupex [--user=username] [--host=hostname] [--password=WORD] [--port=PORT] [--socket=SOCKET] [--defaults-file=my.cnf] PATH

        Innobackupex参数说明

参数说明
--user=USERNAME用于备份数据的用户
--password=PASSWORD用于备份数据的用户密码
--port=PORT数据库服务端口
--host=HOSTNAME备份数据库的主机名
--defaults-fileMySQL的配置文件备份
--defaults-group=GROUP-NAME多实例环境
--databases备份的数据库，多数据库使用'空格"分隔
--incremental增量备份，后跟增量备份路径
--incremental-basedir=DIRECTORY增量备份使用，增量备份的路径
--incremental-dir=DIRECTORY增量备份恢复时，合并增量备份到全备份，指定增量备份路径
--no-timestamp生成的备份文件不以时间戳为目录
--slave-info记录主库binlog位置点，保存在xtrabackup_slave_info文件，用于主从复制
--safe-slave-backup暂停slave库的SQL线程，备份结束后自动开启SQL线程，确保数据一致性，类似锁表
--stream=tar备份结果以tar的文件输出
--include=ocean备份包含的库表
--throttle=500限定I/O
--apply-log回滚未提交数据，应用redo日志
--use-memory恢复时使用内存量（需与--apply-log一起使用）
--copy-back备份数据复制回原始位置
--redo-only合并多份增量备份，只应用redo日志数据，而不应用undo日志回滚数据
--rsync加速本地文件传输，适用于non-innodb引擎
--parallel=N增加多线程备份

3.4 全量备份与恢复

--- 创建备份目录

mkdir -p /bak/ocean

--- 全量备份

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp /bak/ocean/full

--- 查看binlog信息

cat /bak/ocean/full/xtrabackup_binlog_info

--- 查看备份检查点

cat /bak/ocean/full/xtrabackup_checkpoints

--- 应用日志，回滚未提交事务，确保数据一致性

innobackupex --apply-log --use-memory=64m /bak/ocean/full

--- 恢复数据

/etc/init.d/mysqld stop

ss -lnt | grep 3306

mv /app/mysql5.7/data /app/mysql5.7/data_ss

mkdir /app/mysql5.7/data

innobackupex --defaults-file=/etc/my.cnf --copy-backup --rsync /bak/ocean/full

chown -R mysql.mysql /app/mysql5.7/data

/etc/init.d/mysqld start

3.5 增量备份与恢复

        增量备份是以一次全量备份做基准进行增量备份的，之后的每次增量备份都是基于前一次增量备份的数据来实现

--- 全量备份

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp /bak/ocean/base_full

--- 第一次增量备份

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp --incremental-basedir=/bak/ocean/base_full --incremental /bak/ocean/1st_incr

--- 第二次增量备份

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp --parallel=4 --incremental-basedir=/bak/ocean/1st_incr --incremental /bak/ocean/2nd_incr

--- 应用日志，回滚未提交事务，确保数据一致性

innobackupex --apply-log --use-memory=64m --redo-only /bak/ocean/base_full

innobackupex --apply-log --use-memory=64m --redo-only --incremental-dir=/bak/ocean/1st_incr /bak/ocean/base_full

innobackupex --apply-log --use-memory=64m --redo-only --incremental-dir=/bak/ocean/2nd_incr /bak/ocean/base_full

innobackupex --apply-log --use-memory=64m /bak/ocean/base_full

--- 恢复数据

/etc/init.d/mysqld stop

ss -lnt | grep 3306

mv /app/mysql5.7/data /app/mysql5.7/data_ss

mkdir /app/mysql5.7/data

innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /bak/ocean/base_full

chown -R mysql.mysql /app/mysql5.7/data

/etc/init.d/mysqld start

注：增量备份合并过程中，--redo-only参数是只应用redo日志恢复数据，不执行undo未提交数据，待最后增量数据合并完成一起应用undo日志回滚数据。

4 Xtrabackup分库、分表备份与恢复

4.1 分库、分表备份

        Xtrabackup分库、分表备份数据，需开启独立表空间模式，innodb_file_per_table=ON

--- 备份单库ocean

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp --databases="ocean" /bak/ocean/single_db

--- 备份多库

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp --databases="ocean bob" /bak/ocean/multi_db

--- 备份单表

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp --databases="ocean.sales" /bak/ocean/single_tab

--- 备份多表

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup123 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp --databases="ocean.sales ocean.depts mysql.user" /bak/ocean/multi_tab

4.2 分库、分表恢复

        分库、分表恢复数据时，使用"--export"参数，需初始化一个纯净的mysql数据库

--- 在纯净的mysql环境执行

innobackupex --apply-log --export /bak/ocean/single_tab

--- 初始化数据库

/app/mysql/bin/mysqld --no-defaults --initialize --basedir=/app/mysql --datadir=/app/mysql/data --user=mysql

--- 恢复数据

cp /bak/ocean/ocean_sales/* /app/mysql5.7/data

chown -R mysql.mysql /app/mysql5.7/data

--- 启动数据库，查看表数据

/etc/init.d/mysqld start

select * from ocean.sales;

--- mysqldump工具导出表数据，恢复到生产数据库

mysqldump -uroot -p'root1234' ocean sales > single_tab.sql

mysql ocean < single_tab.sql

--- source 恢复数据

mysqldump -uroot -p'root1234' ocean sales > single_tab.sql

mysql -uroot -p'root1234' -S /app/mysql5.7/tmp/mysql.sock

use ocean

source single_tab.sql

--- SQL语句导出、导入数据

mysql -uroot -p'root1234' -S /app/mysql5.7/tmp/mysql.sock

use ocean

select * from sales into outfile /bak/ocean/single_tab.txt" character set utf8;

mysql -uroot -p'root1234' -S /app/mysql5.7/tmp/mysql.sock

use ocean

delete from sales;

load data infile '/bak/ocean/single_tab.txt' into table sales;

5 企业案例

        系统开发工程师测试功能脚本时，误操作执行在生产环境数据库中，导致生产数据异常。幸运的是专业的DBA团队配置了日常数据备份策略，通过备份恢复了受损的数据，最大限度的降低了企业的损失。下面我们来看一下恢复过程

--- 周日凌晨1:00的全量备份

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup1234 --socket=/app/mysql5.7/tmp/mysql.sock --no-timestamp /bak/ocean/base_full

--- 周一凌晨1:00的增量备份

innobackupex --defaults-file=/etc/my.cnf --user=backup --password=backup1234 --sock=/app/mysql5.7/tmp/mysql.sock --no-timestamp --incremental-basedir=/bak/ocean/base_full --incremental /bak/ocean/1st_incr

--- 周一上午10:00，开发顾问误操作

drop database ocean

--- 周一上午10:03，前端应用无法访问，业务中断。DBA查看日志，发现业务数据库不存在，查看系统审计日志发现数据库丢失原因

--- 备份binlog

cp -a /app/mysql5.7/log /bak/ocean/binlog

--- 应用日志，回滚未提交事务，确保数据一致性

innobackupex --apply-log --use-memory=64m --redo-only /bak/ocean/base_full

innobackupex --apply-log --use-memory=64m --redo-only --incremental-dir=/bak/ocean/1st_incr /bak/ocean/base_full

innobackupex --apply-log --use-memory=64m /bak/ocean/base_full

--- 恢复binlog日志数据

cat /bak/ocean/1st_incr/xtrabackup_binlog_info

ocean-bin.000008 668

mysqlbinlog -d ocean ocean-bin.000008 ocean-bin.000009 --start-position=668 -r bin.sql

grep -w drop bin.sql

sed -i '/drop database ocean/d' bin.sql

--- 确保恢复过程中数据一致性，关闭数据库服务端口

iptables -I INPUT -p tcp --dport 3306 ! -s 10.6.0.243 -j DROP

--- 恢复数据

/etc/init.d/mysqld stop

ss -lnt | grep 3306

mv /app/mysql5.7/data /app/mysql5.7/data_ss

mkdir /app/mysql5.7/data

#innobackupex --defaults-file=/etc/my.cnf --copy-back --rsync /bak/ocean/base_full

mv /bak/ocean/base_full/* /app/mysql5.7/data

chown -R mysql.mysql /app/mysql5.7/data

/etc/init.d/mysqld start

--- 应用binlog恢复数据

mysql ocean < bin.sql

 

 

 

相关链接

MySQL数据库备份与恢复 Part 1 : 基础篇

MySQL数据库备份与恢复 Part 2 : 进阶篇

吥輕誩放棄
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL -- 数据库备份与恢复
weixin_48693408的博客
08-26 568
mysqldump实现数据库的备份与恢复
Mysql数据库备份恢复测试报告-xtrabackup.docx
最新发布
03-29
3. **恢复验证**：恢复后，通过检查数据库中的数据更新时间（如`mysql.innodb_table_stats`表的`last_update`字段），验证了恢复的时间点与备份时间一致，确保数据的准确性。测试结果显示，恢复到了2022年6月24日02:...
MySQL基础（十三）：备份与恢复
一点一滴铺就人生
07-02 387
文章目录一、备份类型二、备份方式及工具介绍1、备份工具2、逻辑备份和物理备份的比较（1）mysqldump (MDP)（2）xtrabackup(XBK)3、备份策略三、mysqldump的使用1、连接参数2、备份专用参数（1）-A 全备参数（2）-B 备份多个单库（3）备份一个库下多个表3、高级参数应用（1）--master-data=2（2）--single-transaction（3）-R、-E、--triggers（4）--max-allowed-packet（5）--set-gtid-purged
保持业务数据连续性的灾难恢复
weixin_34306446的博客
07-03 131
杰斯普瑞特·辛格是数据保护和治理厂商Druva公司的创始人和首席执行官，他于日前阐述了公共云在提高数据恢复、备份和归档的重要性… 保持业务运行是企业IT团队和业务团队二者面临的最大挑战之一。然而，围绕IT变化的速度为每个团队带来的问题也不相同。对于首席执行官和商界领袖来说，此举更多的数字化业务活动，而不是传统渠道正在开拓新的类型和企业面临的风险水平，根据...
主从断开恢复流程
u011105165的博客
10-29 709
#从库 show slave status; #从结果中取出Relay_Master_Log_File 与 Exec_Master_Log_Pos; #前往主库 SHOW BINLOG EVENTS in '【Relay_Master_Log_File】 ' from 【Exec_Master_Log_Pos】 #从上述结果列表中检查是否慢sql卡住 #因慢sql卡住 到从库执行以下...
MySQL数据库备份与恢复 Part 4：Xtrabackup 企业级备份实战
木苒的博客
08-29 210
企业级备份策略 周日、周三进行全库备份，其余进行增量备份。 计划任务 # database Backup strategy --- create by ocean * 2 * * * /app/bak/scripts/XtraBackup.sh 数据库备份脚本 #!/bin/bash # filename : XtraBackup.sh # Author : ...
MySQL数据库备份与恢复 Part 5：Xtrabackup 企业级备份实战 --- MySQL 8
木苒的博客
09-12 1084
1关于MySQL8.0新的密码认证方式 The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password prov...
MySQL数据库备份与恢复 Part 1 : 基础篇
木苒的博客
05-01 197
1 MySQL数据库备份 1.1 备份的意义 大数据时代，数据对于企业越来越重要。失去数据就相当于失去商机、失去产品、失去客户，甚至会导致企业倒闭。而数据中的核心数据都存储在数据库中，那么数据库备份与恢复的重要性就不言而喻了。 1.2 mysqldump介绍 mysqldump是MySQL自带的备份工具 mysqldump是把数据从MySQ...
MySQL数据库备份与恢复 Part 2 : 进阶篇
木苒的博客
05-01 192
1 全量备份与增量备份 1.1 全量备份 全量数据是数据库中所有数据，全量备份是把所有数据进行备份 --- 备份所有数据库的所有数据 mysqldump -B --master-data=2 --single-transaction -A | gzip > /bak/all.sql.gz --- 备份ocean数据库的所有数据 mysqldump -B --ma...
mysql数据库备份与恢复
程序三两行
11-14 2603
物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比 较大，MySQL中可以用 xtrabackup 工具来进行物理备份 逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空 间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的 时候执行备份的sql语句实现数据库数据的重现。
基于xtrabackup的MySQL数据库备份及还原Shell脚本.zip
08-27
了解这些基本概念后，你可以查看压缩包内的`基于xtrabackup的MySQL数据库备份及还原Shell脚本`，这个脚本将详细展示如何结合`xtrabackup`和Shell来自动化MySQL数据库的备份与还原流程。在实际使用时，你需要根据自己...
MySQL备份与恢复实战mysqldump xtrabackup等
06-14
本套课程从2个方面讲解了MySQL的备份与恢复:逻辑备份和物理备份，其中逻辑备份工具分别介绍了mysqldump、mysqlpump、mydumper的详细使用，...通过本套课程的学习能够达到快速高效的掌握MySQL数据库的备份恢复及原理。
MySQL数据库的备份与恢复之xtrabackup工具的使用1
08-03
MySQL数据库的备份与恢复是确保数据安全的重要环节。在本文中，我们将重点讨论使用xtrabackup工具进行物理备份，特别是在InnoDB和MyISAM表中的应用。 首先，了解备份的基本类型至关重要。物理备份是直接复制数据库...
MySQL数据库备份恢复实现代码
09-08
MySQL数据库是世界上最受欢迎的开源关系型数据库之一，其备份与恢复功能对于确保数据安全和业务连续性至关重要。本文将深入探讨MySQL数据库的备份和恢复...希望这篇文章能帮助你更好地理解和应用MySQL的备份恢复技术。
ERROR 1123 (HY000): Can't initialize function 'group_replication'; Plugin initialization function fa
木苒的博客
05-23 4673
MySQL Group Replication安装group_replication.so报错 mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; ERROR 1123 (HY000): Can't initialize function 'group_replication'; Plugin in...
MySQL数据库分类与版本升级
木苒的博客
03-25 1823
MySQL数据库的官方网站 http://www.mysql.com，发布的MySQL版本采用双授权政策，与大多数开源产品路线一致，MySQL也有社区版和企业版之分，且这两个版本又各自分为了四个版本依次发布，分别是：Alpha版、Beta版、RC版和GA版。 1MySQL数据库企业版与社区版的区别 1.1MySQL数据库企业版介绍 MySQL企业版由MySQL AB...
MySQL数据库复制技术 Part 1 : 复制技术介绍
木苒的博客
05-13 762
1 异步复制（async replication） 异步复制发送数据至目的端，目的端无论收到数据与否，都不会返回信息。默认情况，MySQL的复制是异步的，Master上将所有更新操作写入binlog，但不能确保所有更新是否复制到slave的relaylog中，更不能确定被应用到slave数据库。 异步复制明显的优势是复制效率很高，但缺点也很明显，即数据一致性无法...
MySQL数据库复制技术 Part 3 : 级联复制
木苒的博客
05-13 562
MySQL 级联复制 MySQL级联复制的特点是从库作为从库的同时也会作为下联从库的主库。形式：A >>> B >>> C 1.1 级联复制实现关键点：B库开启binlog B库是A库的从库，也是C库的主库，这种情况下需要开启B库的binlog --- 配置my.cnf log_bin = /app/mysq5.7/l...
MySQL备份策略：xtrabackup与常规备份解析
MySQL的备份恢复与xtrabackup备份技术是保障数据安全的重要手段，通过理解不同类型的备份和利用高效工具，可以有效地降低数据丢失的风险，确保系统的稳定运行。在新的一年里，不断学习和掌握这些知识，对于运维...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值