Mysql bin-log日志恢复数据与物理备份-xtrabackup

已于 2023-10-08 09:10:41 修改
阅读量429 收藏
点赞数 1
分类专栏： 云计算-脑力风暴 文章标签： mysql linux 服务器 运维 centos
于 2023-10-07 20:08:10 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiaolinzuoti/article/details/133644820
版权

主打一个数据备份与恢复

binlog与xtarbackup

bin-log日志恢复

bin-log 日志，就记录对数据库进行的操作，什么增删改的操作全都在 bin-log 日志中，首先需要开启bin-log日志文件

开启bin-log配置

在这里插入图片描述

  • 如图：在mysql的配置文件中的 [mysqld] 添加下面的配置
vim /etc/my.cnf   # 不同的操作系统的配置文件不一样

server_id=10   # 不指定，可随意写一个数
log-bin=/var/lib/mysql/mysql-bin.log
  • 添加上面的配置之后需要重启一下
systemctl restart mysqld
  • 重启之后在/var/lib/mysql 目录下面有一个 mysql_bin.00001 文件 在这里插入图片描述
  • 在mysql中可以刷新bin-log日志
mysql> flush logs;
  • 可以使用下面的查看当前使用的是哪一个 bin-log 日志文件
mysql> show master status

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      777 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

bin-log日志恢复

  • 查看bin-log日志
mysql> select * from school.student;
+----+--------+------+
| id | name   | sex  |
+----+--------+------+
|  1 | 小温   | 男   |
|  2 | 小成   | 女   |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> delete from school.student;
Query OK, 2 rows affected (0.00 sec)
#  上面是删除表中的两条记录
# 查看 bin-log 日志文件中对应的操作
mysqlbinlog --base64-output=decode-rows /var/lib/mysql/mysql-bin.000003 -vv
BEGIN

  • 这个图片是课件里面的，只供展示
    这个是课件里面的，只供展示

  • 里面有好多，我只截取上面对应的一部分的日志

/*!*/;
# at 927
#230926  0:48:58 server id 10  end_log_pos 991 CRC32 0x55632c6b         Table_map: `school`.`student` mapped to number 83
# has_generated_invisible_primary_key=0
# at 991
#230926  0:48:58 server id 10  end_log_pos 1052 CRC32 0x67d51427        Delete_rows: table id 83 flags: STMT_END_F
### DELETE FROM `school`.`student`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小温' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
### DELETE FROM `school`.`student`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小成' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
# at 1052
#230926  0:48:58 server id 10  end_log_pos 1083 CRC32 0x6eb05b5b        Xid = 132
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
  • 如果要恢复对应的数据，必须找到对应的添加语句
BEGIN
/*!*/;
# at 634
#230925 19:52:16 server id 10  end_log_pos 698 CRC32 0x26fba24f         Table_map: `school`.`student` mapped to number 83
# has_generated_invisible_primary_key=0
# at 698
#230925 19:52:16 server id 10  end_log_pos 746 CRC32 0x88535582         Write_rows: table id 83 flags: STMT_END_F
### INSERT INTO `school`.`student`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='小温' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0  */
# at 746
#230925 19:52:16 server id 10  end_log_pos 777 CRC32 0xd1f2f433         Xid = 96
COMMIT/*!*/;
# at 777
#230926  0:48:58 server id 10  end_log_pos 856 CRC32 0x842fc928         Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes    original_committed_timestamp=1695660538233698     immediate_commit_timestamp=1695660538233698     transaction_length=306
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1695660538233698 (2023-09-26 00:48:58.233698 CST)
# immediate_commit_timestamp=1695660538233698 (2023-09-26 00:48:58.233698 CST)
/*!80001 SET @@session.original_commit_timestamp=1695660538233698*//*!*/;
/*!80014 SET @@session.original_server_version=80034*//*!*/;
/*!80014 SET @@session.immediate_server_version=80034*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 856
#230926  0:48:58 server id 10  end_log_pos 927 CRC32 0xf9ee8b28         Query   thread_id=18    exec_time=0     error_code=0
SET TIMESTAMP=1695660538/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
BEGIN
  • 上面是添加数据对应的日志
  • 要恢复的话需要使用下面的这条语句
mysqlbinlog --start-position 634 --stop-position 927 /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p1

# 其中 634 是上面日志文件有个 at 634 相当于从这个时刻开始到 927 这个时刻
# 需要注意的是，在这个里面必须将 COMMITTED 事务提交语句也得包含起来
  • 当然，还可以使用时间来恢复数据
mysqlbinlog --start-datetime='2019-8-21 23:55:54' --stop-datetime='2019-8-21 23:56:541' mylog.000001 | mysql -u root -p1

物理备份-xtrabackup

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

三种备份方式

  • 全量备份-----完整备份：

每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较。
特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

  • 增量备份: 每次备份上一次备份到现在产生的新数据

在第一次完整备份之后，第二次开始每次都将添加了存档属性的文件进行备份，并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢？这就是为了下一次备份的时候判断是否有文件变化，因为用户在每次备份以后修改这些被清除存档属性的文件，存档属性就会自动加上，相当于用户告诉系统，这些文件有变化，你下一次就备份这些文件，其他没有存档属性的就不需要备份，这就是增量备份的工作机制。

特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢 复，恢复时间长。

  • 差异备份:只备份跟完整备份不一样的

在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。
特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。

安装xtrabackup

官网：www.percona.com

  • 下载
    在这里插入图片描述
    在这里插入图片描述
    点击这个下载，然后复制到linux系统中解压
  • 安装
# 解压后进入对应文件夹里面
yum install -y *.npm  # 安装

备份

全量备份

  • 备份
# 创建备份目录
mkdir /xtrabackup/full -p
# 全量备份 
innobakeupex --user=root --password='1' /xtrabackup/full
  • 恢复
# 1.关闭数据库
systemctl stop mysqld
# 2.清理数据 
rm -rf /var/lib/mysql/*
# 3.恢复之前验证数据
innobackupex -apply-log /xtrabackup/full/对应的文件
# 4.恢复数据 恢复数据前需要将 bin-log 配置修改 将bin-log的文件目录修改为其他地方，
# 也可以将 bin-log 配置项注释掉
innobackupex --copy-back /xtrabackup/full/文件
# 5.修改权限
chown mysql.mysql  /var/lib/mysql -R

增量备份

原理：每次备份上一次备份到现在产生的新数据

  • 全量备份第一天的
innobackupex --user=root --password='1' /xtrabackup/full
  • 增量备份第二天的
innobackupex --user=root --password='1' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/第一天备份的文件夹
  • 增量备份第三天的
innobackupex --user=root --password='1' --incremental /xtarbackup/ --incremental-basedir=/xtrabackup/第二天备份的文件
  • 恢复文件
# 1.关闭mysql
systemctl stop mysqld
# 2.清除数据
rm -rf /var/lib/mysql/*
# 3.依次重演备份文件
innobackupex --apply-log --redo-only /xtarbackup/第一天备份文件
# 之后的重演相当于将数据放到了第一天备份文件上
innobackupex --apply-log --redo-only /xtrabackup/第一天备份文件 --incremental-dir=/xtrabackup/第二天备份文件
innobackupex --apply-log --redo-only /xtrabackup/第一天备份文件 --incremental-dir=/xtrabackup/第三天备份文件
# 4.恢复数据
innobackupex --copy-back /xtarbackup/第一天备份文件
# 5.修改权限
chown mysql.mysql -R /var/lib/mysql/*
# 6.启动数据库
systemctl start mysqld

差异备份

  • 全量备份第一天的
innobackupex --user=root --password='1' /xtarbackup/
  • 第二到四天全不备份 第五天备份前面全部的数据，除外第一天
innobackupex --user=root --password='1' --incremental /xtarbackup/ --incremental-basedir=/xtarbackup/第一天备份的文件夹
  • 恢复数据
# 1.关闭mysql
systemctl stop mysqld
# 2.清除数据
rm -rf /var/lib/mysql/*
# 3.依次重演备份文件
innobackupex --apply-log --redo-only /xtarbackup/第一天备份文件
innobackupex --apply-log --redo-only /xtarbackup/第一天备份文件 --incremental-dir=/xtarbackup/第五天备份文件
# 4.恢复数据
innobackupex --copy-back /xtarbackup/第一天备份文件
# 5.修改权限
chown mysql.mysql -R /var/lib/mysql/*
# 6.启动数据库
systemctl start mysqld
By Error
关注
专栏目录
Docker环境下MySQL备份恢复工具XtraBackup使用详解 | Spring Cloud 62
gmHappy
08-06 9447
`Percona XtraBackup`是一个开源的`MySQL`和`MariaDB`数据库备份工具，它能够创建高性能、一致性的备份，并且对生产环境的影响很小。`Percona XtraBackup`通过在不停止`MySQL`服务器的情况下，复制`InnoDB`存储引擎的数据文件和事务日志，来实现备份和还原操作。
使用 mysql binlog 恢复数据
magiclight006的博客
09-02 466
【mysqlbinlog】 从binlog里面摘出需要恢复执行的SQL mysqlbinlog   --start-position="$startpos"  --stop-datetime="$stoptime"    --database="$db"    "$binlog_xxxxxx"   >   recover.sql /$homedir/bin/mysql  --defaults
mysql用binlog恢复数据_mysql 利用 binlog 恢复数据
weixin_42545292的博客
01-27 211
mysqlbinlog--start-position=301 ..\log-bin\binlog.000001 |mysql -u root -p 123456这是在从日志的第301行开始恢复。mysqlbinlog 命令的其他一些参数如下：---help，-？显示帮助消息并退出。---database=db_name，-ddb_name只列出该数据库的条目(只用本地日志)。--force-...
MySQL误删除 binlog 还原 恢复已删除数据 实战 超详细
最新发布
id_is_null的博客
09-14 1494
MySQL 通过 binlog 恢复已删除数据 超详细
mysqlbinlog 工具分析binlog日志
z69183787的专栏
06-12 1069
MySQL的binlog 日志对于生产环境非常有用，任何时间对数据库的修改都会记录在binglog中；当数据发生增删改，创建数据库对象都会记录到binlog中，数据库的复制也是基于binlog进行同步数据； 和SQL SERVER 数据库开启完整模式的原理一样，每一次的数据的变动都会记录在案；（对数据库的select,show这些操作不会记录在binlog） 下面介绍mysqlbinlog工具查看 二进制日志内容； 检查工作： 1，查看是否开启binlog show variables like
MYSQL之binlog恢复数据
yeYingXuan的博客
07-31 1898
可以看到有一条新增的sql和一条删除的sql，新增的时间点是 240720 8:06:13，结束的时间点是240720 8:06:13，只要我们恢复的时间段包含了这个时间段即可。对某一条执行了错误的更新/删除操作：这种恢复是最简单的，我们直接在binlog找到这条数据的操作记录，找到原数据的值，然后执行新增/更新操作即可。由上得知，我们的binlog文件名叫binlog.000012，位置在/var/lib/mysql目录下。既然他会记录sql，那我们是不是把需要恢复数据的sql拿出来就好了？
mysql binlog恢复数据
Minus_God的博客
12-23 303
Binlog日志，即binary log，是二进制日志文件，有两个作用，一个是增量备份，另一个是主从复制，即主节点维护一个binlog日志文件，从节点从binlog中同步数据，也可以通过binlog日志来恢复数据 1，登录mysql查看binlog日志的状态，输入show variables like ‘%log_bin%’;查看binlog为off关闭状态 2，开启mysql binlog日...
percona-xtrabackup-8.0.23-16-Linux-x86_64.glibc2.17.tar
01-24
3. 恢复数据：根据需求选择恢复模式（如完全替换现有数据库或合并到现有数据库），使用`innobackupex --copy-back`或`--move-back`命令将备份数据复制到MySQL的数据目录。 4. 重启MySQL服务：完成上述步骤后，重启...
MySQL--基于Xtrabackup+Shell+Crond实现的数据库（全量+增量）热备份方案
热门推荐
JustinQin
08-27 3万+
学习背景 不管你是正在上学，还是已经工作了，想必多多少少都听说过删库跑路、rm -rf的情况，主要指的就是被有意或无意的删除掉了数据库的数据，而数据是整个业务最重要的价值体现，如果你的公司或者项目组没有一个成熟的数据库数据备份方案，一旦发生意外，我只能说年轻人，大意了吧，没有备份数据库！！！ 一、Xtrabackup介绍 官方介绍文档 简单总结下来就是XtraBackup是Percona旗下的一款产品，支持MySQL数据库的热备份（在线不停机），并且是免费、开源，适用所有MySQL版本，非阻塞、紧密
mysql Xb文件--物理备份恢复数据库.docx
10-25
以上就是使用MySQL的Xb文件进行物理备份恢复数据库的详细过程，这个过程涉及到的关键工具包括Percona XtraBackup、`xbstream` 和 `qpress`，以及必要的配置文件修改和权限设置。物理备份的优势在于能提供精确到事务...
Mysql数据库----日志的备份与恢复
Alen686的博客
04-24 682
日志 概述 备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。 而备份、恢复中,目志起到了很重要的作用 日志类型 mysgl日志文件 常用的自志文件(在/etc/my.cnf中[mysqld]客户端配置中修改)认在/usr/ local/ mysql/data/中 错误日志 用于记录Mysql 启动、停止、或运行时产生的错误信息，默认开启可通过以下字段进行修改 log-error=/usr/ local/mysal/data/mysql_erlor.log #指定日志的保存位
mysql.binlog数据恢复
12-14
这是一个eclipse安装插件，在oxygen下测试是可用的。有问题可以是下面反馈^_^ 操作：1、获取binlog文件 2、选中要处理的部分 3、选择菜单“jhh->binlog->”下对应的操作 4、会在相同目录下生成sql文件（文件名相同，加了sql后缀）
mysql通过binlog恢复数据
Lin_XXiang的博客
12-26 651
通过binglog与row模式，进行数据恢复
Mysql使用mysqlbinlog恢复数据
光头强的博客
06-17 1062
一、关于mysqlbinlog mysqlbinlog是数据库的二进制文件，开启mysqlbinlog日志后，平时我们操作的sql语句例如增删改查，都会被记录到日志文件中，如果我们误删某条记录、数据表、数据库，只要我们合理的使用mysqlbinlog，都能对其进行恢复。 二、开启mysqlbinlog 2.1 查看是否开始mysqlbinlog mysql> show variables like 'log_%'; +----------------------------------------
MySQL binlog 数据恢复
Xiang想
09-17 1576
MySQL binlog 数据恢复及备份操作图解
MySQL使用binlog恢复数据
安静的技术宅
07-30 4694
MySQL的二进制日志（Binlog）是一种事务日志，用于记录对数据库的更改操作。复制: 从库通过拉取主库的binlog实现主从数据一致恢复: 通过重放binlog恢复数据丢失或误操作情况MySQL的Binlog是数据库的重要组成部分，它记录了数据库的更改操作，并为数据复制和数据恢复提供了有力支持。通过了解Binlog的原理和使用技巧，我们可以更好地利用这一特性，保障数据库的稳健运行，并在必要时实现高级的数据恢复。然而，进行数据恢复时，请务必谨慎操作，以免造成不可逆的损失。
MySQL通过binlog恢复数据
06-01 1501
部署在dockers中的MySQL通过binlog恢复数据准备1、确认binlog开启2、准备操作的数据库表和数据3、执行误操作恢复1、查看所有binlog日志列表2、查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值3、为了防止干扰，我们flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件4、确认binlog已经更换成新的5、（非docker跳过此步）由于我这边是docker，而mysqlbinlog命令需要在do
mysql从binlog中恢复数据
架构师与哈苏
07-05 511
查看mysql是否开启binlog show variables like '%log_bin%'; 查询文件目录 show variables like '%datadir%'; 查看所有binlog日志文件列表 show master logs; 刷新日志，开启一个新的编号 flush logs 清空所有binlog日志命令 reset master 查看binlog文件内容，使用查看工具 ...
MySQL备份：mysqldump与Xtrabackup解析
"Mysql的备份之mysqldump与Xtrabackup" MySQL数据库在备份方面提供了多种策略和工具，本文主要介绍了两种常用工具——`mysqldump`和`Xtrabackup`，并从基础概念如redo_log、undo_log、多版本并发控制（MVCC）等方面...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值