MySQL 之【数据库备份（全量、增量）含xtrabackup方式】

Aiir�

已于 2023-10-19 15:10:18 修改

阅读量497

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库运维

于 2023-10-18 18:26:13 首次发布

本文链接：https://blog.csdn.net/Aiirer/article/details/133904681

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

MySQL数据备份的原因

数据安全性：在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果。备份是防止数据丢失的重要手段，当数据库因为某些原因造成部分或者全部数据丢失后，备份文件可以帮助我们找回丢失的数据。
数据完整性：备份不仅可以防止数据丢失，还可以保证数据的完整性。当发现数据库中的数据被误操作或损坏时，可以使用备份文件进行恢复，以保持数据的完整性和一致性。

MySQL数据备份的策略

全量备份（Complete Backup）：每次对数据进行完整的备份。这种备份方式优点是简单直观，缺点是备份时间较长，占用存储空间较大。
增量备份（Incremental Backup）：只有那些在上次全量备份或者增量备份后被修改的文件才会被备份。这种备份方式优点是可以减少备份时间和存储空间的使用，缺点是在需要恢复数据时可能需要额外的时间来还原增量备份。

MySQL数据备份的方式

热备 : 不关闭mysql服务的情况下，请求可以继续操作数据库，实现备份
温备 : 不关闭mysql服务的情况下，支持读，不支持写，实现备份
冷备 : 关闭mysql服务的情况下，实现备份

MySQL数据备份的类型

完全备份（Full Backup）：这种备份类型备份全部字符集。它是最全面、最完整的备份类型，包含了所有需要备份的数据。完全备份的恢复策略通常为“完全+增量+二进制日志”或者“完全+差异+二进制日志”。
增量备份（Incremental Backup）：这种备份类型备份的是自上次完全备份或增量备份以来改变了的数据，不能单独使用，需要借助完全备份。增量备份的频率取决于数据的更新频率。
差异备份（Differential Backup）：这种备份类型也是备份自上次完全备份以来改变了的数据，但与增量备份不同的是，差异备份可以单独使用。这也意味着差异备份的效率要比增量备份高。
物理备份（Physical Backup）：这种备份方式直接备份数据文件，优点是备份和恢复操作都比较简单，能够按文件系统级别恢复。但需要注意的是，不要假设备份一定可用，要测试备份。
逻辑备份（Logical Backup）：这种备份方式备份的是表中的数据和代码，优点是恢复简单，备份的结果为ASCII文件，可以编辑，与存储引擎无关，可以通过网络备份和恢复。缺点是备份或恢复都需要MySQL服务器进程参与，备份结果占据更多的空间，浮点数可能会丢失精度，还原之后缩影需要重建。
冷备（Cold Backup）：在数据库停止的情况下进行备份，需要关MySQL服务，读写请求均不允许状态下进行。
温备（Warm Backup）：服务在线，但仅支持读请求，不允许写请求。
热备（Hot Backup）：在数据库运行中直接备份，对正在运行的数据库没有任何影响。

一、全量备份

1.mysqldump

mysqldump是MySQL数据库的一个命令行工具，它主要用于备份和转储MySQL数据库。这个工具可以生成一个SQL脚本文件，其中包含了重建数据库及恢复数据所需要的一系列SQL命令。当使用mysqldump时，你可以指定许多不同的选项来定制备份的行为。例如，你可以选择只备份特定的数据库，或者包括数据和表的定义语句（CREATE TABLE, CREATE INDEX等）。你还可以在备份中使用gzip等工具来压缩输出文件，以节省存储空间。

#使用Demo
#全备数据库(innodb Engine)
mysqldump -uroot -pmysql123 --single-transaction --master-data=2  --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
#全备(Myisam Engine)
mysqldump -uroot -pmysql123 --lock-all-tables    --master-data=2  --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
#只备份数据库db1和db2
mysqldump -uroot -pmysql123 --single-transaction --master-data=2  --flush-logs --routines --events --databases db1 db2 > /backup/db_bak01.sql

参数解释
mysqldump #这是用于从MySQL数据库导出数据的命令
-uroot #这表示使用’root’用户登录
-123456 #密码,但通常mysqldump命令不直接使用此格式的参数来指定密码
–single-transaction #这表示在备份过程中使用单一的事务处理，有助于保持数据的一致性
–master-data=2 #这记录了备份的二进制日志位置，以便在需要时进行恢复
–flush-logs #这会刷新MySQL的日志系统，但通常在备份之后进行，以保持备份的完整性和安全性
–flush-privileges #这会刷新MySQL用户权限，这有助于确保备份期间授予的权限不会影响后续的数据
–routines #这会包含存储过程的定义在备份文件中
–events #这会包含事件的定义在备份文件中
–all-databases #这表示要备份所有数据库
–lock-all-tables #这会锁定所有表，使得在备份过程中其他对数据库的更改被阻止
/backup/db_bak01.sql #这表示将导出的数据存储到指定的SQL文件中

1.1数据导出

mysqldump -uroot -p --single-transaction --master-data=2 --all-databases |gzip > /backup/db_bak01.sql.gz

在这里插入图片描述

1.2数据恢复

gzip -d /backup/db_bak01.sql.gz  # 解压备份文件
# 恢复1
mysql -uroot -p < /backup/db_bak01.sql
# 恢复2
mysql> source /backup/db_bak01.sql

恢复1和恢复2任选一种方式：方式1是mysql外部，方式2是登录mysql方式

2.xtrabackup工具

xtrabackup简介
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份（对于MyISAM的备份同样需要加表锁）。XtraBackup支持所有的Percona Server、MySQL、MariaDB和Drizzle。xtrabackup有两个主要的工具：xtrabackup、innobackupex
Xtrabackup主要具有以下功能：
数据备份：Xtrabackup最基本的功能是进行数据备份。它能够备份InnoDB和XtraDB两种数据引擎的表，但无法备份MyISAM数据表。
在线备份：Xtrabackup支持在线热备份，也就是说在备份过程中不会影响数据的读写，不会阻塞数据库操作。
增量备份：Xtrabackup可以创建增量备份，每次备份只包含自上次备份以来发生更改的部分数据。
并行备份：Xtrabackup可以使用多线程进行并行备份，以提高备份速度。
数据恢复：如果数据出现损坏或丢失，Xtrabackup可以用来恢复备份的数据。
日志压缩：Xtrabackup可以对备份的日志进行压缩，以减小备份和恢复时间。

Xtrabackup和Innobackupex都是用于MySQL数据库备份的工具，但二者之间存在一些明显的区别。
可备份的数据表类型：Xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。相反，Innobackupex则是一个封装了xtrabackup的Perl脚本，它可以同时备份InnoDB和MyISAM引擎的表。
对MyISAM表的备份方法：当处理MyISAM表时，Xtrabackup无法进行增量备份，而Innobackupex虽然可以备份MyISAM表，但需要加一个全局的读锁，且MyISAM不支持增量备份。
总的来说，Xtrabackup更适合于备份InnoDB和XtraDB数据表，而Innobackupex则提供了更多功能和灵活性，可以同时备份InnoDB和MyISAM数据表，但处理MyISAM表时需要的额外操作可能会带来一些不便。

2.1备份准备

XtraBackup下载安装 
官网地址：https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup
# Mysql5.7以下和Mysql8.0以上所需要的版本不同
下载命令
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.33-28/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.33-28.1.el7.x86_64.rpm  //下载
yum -y localinstall percona-xtrabackup-80-8.0.33-28.1.el7.x86_64.rpm //安装 
xtrabackup  --version //查看

不同的MySQL版本对应的XtraBackup版本不同具体看官网版本对应,这里mysql对应版本8.0.19 XtraBackup版本8.0.33

#创建备份时所需要的用户
#备份权限【这里直接使用root,但是要给root备份权限】
#CREATE USER 'backup'@'%' IDENTIFIED BY 'qwe123456';
#grant reload,lock tables,replication client,super,process,BACKUP_ADMIN on *.*  to 'backup'@'%';
***注意 如果使用非root用户进行备份的话 可能会出现权限不足的错误***
grant BACKUP_ADMIN on *.* to 'root'@'%';   //给权限
FLUSH PRIVILEGES;

2.2数据导出

本地保存

# 创建目录
mkdir  -p /data/backup/
# 执行导出命令(本地保存)
/usr/bin/xtrabackup --no-defaults --backup --slave-info --host=xx.xx.51.242 -uroot -p --datadir=/data/mysql/database  --stream=xbstream --target-dir=/data/backup/ --port=3306 2> /data/backup/log/xtrabackup.log  | gzip - > /data/backup/backupdata.gz

-uroot -p：这是登录MySQL的用户名和密码。  
--no-defaults：这表示不使用任何默认的配置文件，而是使用命令行参数。  
--backup：这是告诉Xtrabackup要进行备份的选项。  
--slave-info：这个选项用于从备份中获取复制信息。  
--host=xx.xx.51.242：这是备份的主机名或IP地址。  
--datadir=/data/mysql/database：这是MySQL数据文件的路径。  
--stream=xbstream --target-dir=/data/backup/ --port=3306   
  这部分是关于备份流的选项。  
--stream=xbstream：这表示使用xbstream格式进行流式备份。  
--target-dir=/data/backup/：这是备份流的目标目录。  
--port=3306：这是MySQL服务器的端口号。  

2> /data/backup/log/xtrabackup.log | gzip - > /data/backup/backupdata.gz  
  
这部分是将备份过程中的错误输出重定向到日志文件，并将备份流压缩为gz格式。  
  
2> /data/backup/log/xtrabackup.log：这表示将标准错误输出（2）重定向到指定的日志文件。  
| gzip - > /data/backup/backupdata.gz：这表示将之前的命令的输出通过管道传递给gzip命令进行压缩，并将压缩后的结果保存到指定的路径。

直接把压缩文件放到新库的database下解压,可省略拷贝过程
xtrabackup支持流式备份，备份文件本地不落盘，直接备份到远程主机，避免单个主机故障导致数据库和备份文件同时丢失。
本地主机跟远程主机提前做好SSH免密，命令如下

/usr/bin/xtrabackup --backup --slave-info -uroot -p -S /opt/data/mysql.sock  --datadir=/data/mysql/database --stream=xbstream --target-dir=/data/backup/ --port=3306 2> /data/backup/log/xtrabackup.log  | ssh root@x.x.x.x "cat -  > /data/backup/backupdata.s1"

这个命令使用Xtrabackup工具来备份一个MySQL数据库。以下是命令各部分的解释：

/usr/bin/xtrabackup --backup --slave-info -uroot -p -S /opt/data/mysql.sock：这是调用Xtrabackup工具进行备份的命令，指定了备份的用户名、密码和socket文件路径。

--slave-info：这个选项用于从备份中获取复制信息。

-uroot -p：这是登录MySQL的用户名和密码。

-S /opt/data/mysql.sock：这是指定MySQL服务的socket文件路径。

--datadir=/data/mysql/database：这是MySQL数据文件的路径。

--stream=xbstream --target-dir=/data/backup/ --port=3306：这部分是关于备份流的选项。指定使用xbstream格式进行流式备份，并将备份流保存到指定的目录。

2> /data/backup/log/xtrabackup.log：这部分将备份过程中的错误输出重定向到指定的日志文件。

| ssh root@x.x.x.x \"cat - > /data/backup/backupdata.s1\"：这部分使用SSH协议将备份流通过管道传输到远程服务器上。它通过SSH连接到指定的IP地址和端口号，并使用root用户运行一个cat命令来接收备份流，并将备份流重定向到一个指定的文件。

备份到云端(OSS)
使用ossutil工具将备份文件上传到OSS,具体使用方法略

# 查看是否导出成功,如下
tail -f xtrabackup.log
# 可以观察到xtrabackup备份过程中的重要操作

在这里插入图片描述

2.3数据恢复

# 解压还原
cd /data/backup/
gunzip backupdata.gz 
xbstream -x -v < backupdata
#gunzip: 这是用于解压缩文件的命令。
#backupdata.gz: 这是要解压缩的文件名。在这里，backupdata.gz 是待解压缩的备份文件。
#xbstream -x -v < backupdata: 这是使用 xbstream 工具从备份文件中还原数据的命令。
#xbstream: 这是 Percona XtraBackup 工具提供的命令，用于从 xbstream 格式的流数据中恢复备份文件。
#-x: 这是 xbstream 命令的选项，表示要从流数据中还原（解压）备份文件。
#-v: 这也是 xbstream 命令的选项，表示在解压备份数据时输出详细信息（verbose mode）。
#< backupdata: 这是输入重定向符号 <，将备份文件 backupdata的内容作为输入传递给 xbstream 命令。这样，xbstream 命令就可以从 backupdata文件中获取数据并进行解压缩。
# 查看解压还原出来的文件和是否成功
cat xtrabackup_checkpoints

在这里插入图片描述

backup-my.cnf: 备份期间使用的 MySQL 配置文件。
ibdata1: InnoDB 存储引擎的系统表空间文件。它包含了表结构、数据和索引等信息。
mysql-bin.000010: 二进制日志文件，用于记录数据库的更改操作，以支持数据复制和恢复。
performance_schema: MySQL 的性能架构表，用于存储性能监控数据。
slave-bin.index: 从服务器的二进制日志索引文件，记录了从服务器的复制日志文件名。
undo_001: InnoDB 存储引擎的回滚段文件，用于支持事务的回滚操作。
xtrabackup_binlog_info: 备份期间的二进制日志信息，包括文件名和位置等。
xtrabackup_info: 备份的元数据信息，包括备份开始和结束时间、备份类型等。
xtrabackup_slave_info: 备份期间的主从复制信息，记录了主服务器的位置等信息。
ib_buffer_pool: InnoDB 存储引擎的缓冲池文件，用于缓存数据页。
mysql: MySQL 数据库的目录，其中可能包含其他表和数据文件。
mysql.ibd: MySQL 系统表的表空间文件。
test: 数据库中名为 "test" 的数据库的目录，其中可能包含其他表和数据文件。
sys: MySQL 的系统库，包含了系统视图和存储过程等。
undo_002: InnoDB 存储引擎的回滚段文件，用于支持事务的回滚操作。
xtrabackup_checkpoints: 备份期间生成的检查点信息，用于增量备份。
xtrabackup_logfile: 备份期间生成的日志文件。
xtrabackup_tablespaces: 备份期间生成的表空间信息，用于支持 InnoDB 表的恢复。

在这里插入图片描述

#xtrabackup_checkpoints 文件是 Percona XtraBackup 工具在备份过程中生成的检查点文件，其中包含了备份过程的一些状态信息。这些信息的含义：
backup_type = full-backuped: 这是备份类型，指示该备份是全量备份。
from_lsn = 0: 这是备份的起始日志序列号（LSN），表示备份过程从哪个LSN开始。
to_lsn = 31415217: 这是备份的结束日志序列号（LSN），表示备份过程到哪个LSN结束。
last_lsn = 31415217: 这是备份过程完成时数据库的最后一个日志序列号（LSN），表示备份过程结束时数据库中最后一个已经完成的日志序列号。
flushed_lsn = 31415217: 这是备份过程完成时数据库的刷新日志序列号（LSN），表示备份完成时刷新到磁盘上的最后一个日志序列号。
redo_memory = 0: 这是备份过程中使用的重做日志内存大小。
redo_frames = 0: 这是备份过程中使用的重做日志帧数。
这些信息对于备份的恢复非常重要。备份过程中记录的 LSN 信息用于确定备份的范围和备份点。在恢复时，这些信息可以帮助确定从哪个备份点开始恢复数据。通过这些信息，Percona XtraBackup 工具可以在恢复时识别已经备份的数据和未备份的数据，确保数据的一致性。
在正常情况下，xtrabackup_checkpoints 文件应该被 Percona XtraBackup 工具自动创建和管理，无需手动修改或操作。它只是备份过程的一个辅助文件，供工具在需要时参考。

#执行prepare命令
命令要在刚才解压的路径下执行
xtrabackup --prepare --target-dir . > prepare.log 2>&1
#xtrabackup启动的mysql临时实例buffer pool默认为100M，可以通过参数–use-memory适当增加内存，加快恢复的速度。官方文档建议将内存设置为1-2G。
#xtrabackup --prepare --use-memory=2G --target-dir . > prepare.log 2>&1
# 查看日志是否执行成功
tail -f  prepare.log
#将数据文件复制到数据目录（backupdata这个文件是否需要复制过去需要考虑）

在这里插入图片描述
将数据文件复制到新数据库的数据目录 xtrabackup prepare完成后的数据库，可以用来直接启动。启动实例之前，需要将文件复制（或移动）到目标实例的数据目录中。目录需要依据目标实例的参数来确定。

# 例如
cp -r  /data/buckup/*   /新数据库的数据目录

最后启动数据库实例进行数据验证

二、增量备份

1.MySQL的二进制日志（binlog）

1.1备份准备

#查看binlog是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.01 sec)

开启binlog的可跳过此步骤

# 开启binlog
vim /etc/my.cnf
# 添加如下
[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8

## 设置server_id,注意要唯一
server-id=101
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-bin
#开启binlog日志,指定其存放位置
log-bin=/var/lib/mysql/mysql-bin
#开启binlog自动过期
expire_logs_days=3

default_authentication_plugin=mysql_native_password

# 重启数据库
service mysqld restart

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

1.2数据备份

# 查看日志索引和事件位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 查询一下binlog
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       178 | No        |
| mysql-bin.000002 |   3101251 | No        |
| mysql-bin.000003 |      1490 | No        |
| mysql-bin.000004 |       178 | No        |
| mysql-bin.000005 |       178 | No        |
| mysql-bin.000006 |       178 | No        |
| mysql-bin.000007 |      3729 | No        |
| mysql-bin.000008 |       709 | No        |
| mysql-bin.000009 |       648 | No        |
| mysql-bin.000010 |      4922 | No        |
| mysql-bin.000011 |       155 | No        |
+------------------+-----------+-----------+

#全量备份test库,并刷新binlog
mysqldump -u root -p --master-data=2 --single-transaction  -F -B    test > /data/mysqlbackup/test.sql;

模拟CRUD操作

mysql>INSERT INTO user ( id, age,name ) VALUES  ( 11, 88, '大白' );
mysql>INSERT INTO user ( id, age,name ) VALUES  ( 12, 88, '小白' );
mysql>drop database test；

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 |      9981|              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 经过一些列的CRUD操作后再次查询binlog
mysql> flush logs; #接着新的日志就会输出到mysql-bin.000012上,以便我们分析mysql-bin.000011的数据
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       178 | No        |
| mysql-bin.000002 |   3101251 | No        |
| mysql-bin.000003 |      1490 | No        |
| mysql-bin.000004 |       178 | No        |
| mysql-bin.000005 |       178 | No        |
| mysql-bin.000006 |       178 | No        |
| mysql-bin.000007 |      3729 | No        |
| mysql-bin.000008 |       709 | No        |
| mysql-bin.000009 |       648 | No        |
| mysql-bin.000010 |      4922 | No        |
| mysql-bin.000011 |      10105| No   	   |
| mysql-bin.000012 |       155 | No        |
+------------------+-----------+-----------+

查看mysql-bin.0000011

方式1：

mysqlbinlog -vvv --base64-output=decode-rows   mysql-bin.000011

方式2：

mysql>show binlog events in 'mysql-bin.000011'\G;
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000011 |    4 | Format_desc    |         1 |         123 | Server ver: 8.0.19-log, Binlog ver: 4 |
| mysql-bin.000011 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000011 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000011 |  219 | Query          |         1 |         291 | BEGIN                                 |
| mysql-bin.000011 |  291 | Table_map      |         1 |         342 | table_id: 156 (test.user)             |
| mysql-bin.000011 |  342 | Write_rows     |         1 |         389 | table_id: 156 flags: STMT_END_F       |
| mysql-bin.000011 |  389 | Xid            |         1 |         420 | COMMIT /* xid=7033 */                 |
| mysql-bin.000011 |  420 | Anonymous_Gtid |         1 |         485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000011 |  485 | Query          |         1 |         557 | BEGIN                                 |
| mysql-bin.000011 |  557 | Table_map      |         1 |         608 | table_id: 156 (test.user)             |
| mysql-bin.000011 |  608 | Write_rows     |         1 |         655 | table_id: 156 flags: STMT_END_F       |
| mysql-bin.000011 |  655 | Xid            |         1 |         686 | COMMIT /* xid=7043 */                 |
| mysql-bin.000011 |  686 | Anonymous_Gtid |         1 |         751 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000011 |  751 | Query          |         1 |         823 | BEGIN                                 |
| mysql-bin.000011 |  823 | Table_map      |         1 |         874 | table_id: 156 (test.user)             |
| mysql-bin.000011 |  874 | Write_rows     |         1 |         921 | table_id: 156 flags: STMT_END_F       |
| mysql-bin.000011 |  921 | Xid            |         1 |         952 | COMMIT /* xid=7046 */                 |
| mysql-bin.000011 |  952 | Anonymous_Gtid |         1 |        1017 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000011 | 8643 | Query          |         1 |        1109 | drop database test                    |
| mysql-bin.000011 | 9981 | Rotate         |         1 |        1156 | mysql-bin.000011;pos=4  				 |

在这里插入图片描述
误操作pos在8643~9981之间，所以将数据恢复到8643之前就可以了

1.3数据恢复

#全量备份恢复
mysql -uroot -p -v  <test.sql
#恢复删库之前的数据(方法一)
mysqlbinlog --stop-position=8643 -d test  mysql-bin.000011 | mysql -uroot -p

#恢复删库之前的数据(方法二)
mysqlbinlog --stop-position=8643 -d test  mysql-bin.000011 >testback.sql
mysql -uroot -p < /data/mysqlbackup/testback.sql

验证数据（略）

2.xtrabackup工具

2.1先进行一次全量备份

恢复增量备份时，需要先对基础全量备份进行恢复，然后再依次按增量备份的时间进行恢复。
略（请看全量备份部分）

2.2测试写入新的数据

略

2.3发起增量备份

/usr/bin/xtrabackup --backup --slave-info -uroot -p -S /opt/data/mysql.sock --datadir=/opt/data/dabase --stream=xbstream --target-dir /data/backup/inc --port=3306 --extra-lsndir=/data/backup/inc --incremental-basedir=/data/backup 2>/data/backup/inc/backup_inc.log | gzip -  > /data/backup/inc/backupdata2.gz

/usr/bin/xtrabackup: 这是实际执行的备份工具的路径。
--backup: 这是 Percona XtraBackup 工具的选项之一，表示进行备份操作。
--slave-info: 这是 Percona XtraBackup 工具的选项之一，表示在备份的时候，也要获取复制信息。通常在配置了 MySQL 主从复制时使用，以确保备份数据的一致性。
-uroot: 这是连接 MySQL 数据库的用户名，这里使用了 "root" 用户。
-p: 这是一个选项，表示在连接数据库时需要输入密码。由于没有指定密码，系统会提示输入密码后才能继续执行。
-S /opt/data/mysql.sock: 这是指定 MySQL 连接使用的套接字文件路径，用于连接到特定的 MySQL 实例。这里是连接到位于 /opt/data/dabase 目录下的 MySQL 实例。
--datadir=/opt/data/dabase: 这是指定 MySQL 数据目录的路径，即数据库文件所在的位置。
--stream=xbstream: 这是一个选项，指定备份数据以 xbstream 格式进行流式输出。xbstream 格式支持并行处理和压缩，适用于备份大型数据库。
--target-dir /data/backup/inc: 这是指定备份文件输出的目录路径。在这里，增量备份文件将会输出到 /data/backup/inc 目录下。
--port=3306: 这是指定要连接的 MySQL 实例的端口号，这里指定为 3306。
--extra-lsndir=/data/backup/inc: 这是指定备份过程中产生的日志文件（LSN 文件）的存放目录。LSN 文件包含备份信息和复制信息。
--incremental-basedir=/data/backup: 这是指定增量备份的基准目录，即之前已经完成的完整备份（full backup）的目录路径。这里是指定增量备份基于 /data/backup 目录下的完整备份。
2>/data/backup/inc/backup_inc.log: 这是将标准错误输出（stderr）重定向到 /data/backup/inc/backup_inc.log 文件中，用于记录备份过程中的错误信息。
|: 这是管道符号，用于将前面命令的输出传递给后面的命令。
gzip - > /data/backup/inc1/backupdata2.gz: 这是将增量备份数据通过管道传送到 gzip 命令中，并将压缩后的数据输出到 /data/backup/inc1/backupdata2.gz 文件中。

2.4在上一次增量备份的基础上进行增量备份【这里为二次增量备份】

--incremental-basedir=/data/backup/inc 2>/data/backup/inc2/backup_inc2.log | gzip -  > /data/backup/inc2/backupdata2.gz

2.5恢复增量备份

先恢复全量备份(略)

2.6恢复第一个增量备份

cd /data/backup/inc
gunzip backupdata1.gz
rm -rf  xtrabackup_checkpoints xtrabackup_info    ## 需要先删除这两个文件，否则xbstream提取文件时有冲突
xbstream -x -v < backupdata1
#恢复增量备份时，切换到全量备份的目录执行
cd /data/backup
xtrabackup --prepare --apply-log-only --incremental-dir=/data/backup/inc --target-dir=. > prepare_inc.log 2>&1
#恢复增量备份时，加上apply-log-only参数，参数–incremental-dir设置为增量备份文件所做目录。

各个步骤查看log是否成功,最后将数据文件复制到数据目录下启动MySQL实例验证数据

总结

1.恢复全量备份大致可以分成以下几步：解压备份文件、prepare备份文件、将数据copy到目标实例相关目录、启动数据库实例
2.MySQL增量恢复常用的增量恢复方法有三种：一般恢复，基于位置的恢复，基于时间点的恢复
一般恢复:
将所有备份的二进制日志内容全部恢复，格式命令如下
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u root -p
基于位置的恢复:
数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，
命令格式如下所示。
格式一：恢复数据到指定位置
mysqlbinlog --stop-position=‘8643’ 二进制日志 | mysql -u root -p ******
格式二：从指定的位置开始恢复数据
mysqlbinlog --start-position=‘8643’ 二进制日志 | mysql -u root -p ******
基于时间点的恢复:
跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。
格式1：从日志开头截止到某个时间点恢复
mysqlbinlog [–no-defaults] --stop-datetime=‘年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户 -p 密码
格式2：从某个时间点到日志结尾恢复
mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户 -p 密码
格式3：从某个时间点到某个时间点恢复
mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日小时:分钟:秒’ --stop-datetime=‘年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户 -p 密码

注意

如果使用远程数据导出可以直接备份到新数据库的数据目录下,执行解压等一些列操作后可直接启动MySQL实例进行数据验证
如果备份完成后需要重做主从的话，可以使用cat xtrabackup_binlog_info命令查看当前备份的binlog文件和POS位点，重新拉起主从
增量备份的恢复是建立在有备份增量数据之前的全量数据之上做的增量恢复