MySQL备份介绍
文章目录
1.DBA备份与恢复职责
1.备份、恢复策略的设计。
备份周期、备份工具、备份方式、恢复方式全部流程化
2.日常备份检查
日志、备份内容
3.定期的恢复演练
4.数据故障时，利用现有的资源，快速恢复
5.数据迁移、升级。
2.MySQL备份工具分类
逻辑备份 100G 以内 30分钟
mysqldump
mysqlbinlog
物理备份 100G 以上
Xtrabackup
MySQL备份工具的分类及其适用场景：逻辑备份和物理备份。逻辑备份适用于数据量在100GB以内的场景，备份时间约为30分钟，常用工具包括
mysqldump（用于逻辑数据导出）和mysqlbinlog（用于读取和备份二进制日志）。物理备份适用于数据量超过100GB的情况，推荐使用Xtrabackup工具，这是一种高效的物理备份工具，可以快速备份大型数据库并支持无锁备份。逻辑备份和物理备份各有侧重，选择时需根据数据规模和业务需求决定。
mysqldump逻辑备份
1.介绍
数据逻辑备份工具
MySQL 自带的客户端命令
可以实现远程和本地备份
mysqldump是 MySQL 自带的一款数据逻辑备份工具，通过客户端命令实现数据的逻辑导出。它的主要特点包括支持远程和本地备份，适用于结构和数据的逻辑备份，能够生成SQL脚本文件，用于重建表结构及插入数据。在备份小规模数据库或需要跨平台传输数据时，mysqldump是一种高效、便捷的选择。
2.重要参数
连接参数
-u 用户名
-p 密码
-S 本地socket文件路径
mysqldump的连接参数用于指定数据库连接信息，确保正确连接到目标数据库实例：-u指定用户名，用于认证访问权限；-p指定密码，用于验证用户身份（可以省略密码直接输入，但会提示输入）；-S指定本地 socket 文件路径，当连接本地 MySQL 实例时可使用此参数代替网络连接。通过这些参数，mysqldump能灵活支持本地和远程数据库的备份操作。
备份参数
-A 导出所有的库
-B 导出单库或多库
--master-data=2 标记全备的时候位于binlog哪个位置,=2这一行是个注释,固定为22行
--single-transaction 对于InnoDB表,通过快照备份表数据,不锁表备份,可以理解为热备
-R -E --triggers 备份特殊对象使用
这些是
mysqldump中常用的备份参数，用于满足不同的备份需求：
-A：导出所有数据库，包括其表结构和数据，适用于全库备份。-B：导出单个或多个数据库，支持指定数据库名称，适用于部分数据库备份。--master-data=2：在全量备份时标记当前二进制日志的位置，方便用于主从复制恢复，=2表示将该标记作为注释（位于生成的SQL文件的第22行）。--single-transaction：对于InnoDB表，利用事务隔离的快照技术备份数据，无需锁表，实现热备，适用于在线业务系统。-R -E --triggers：分别用于备份存储过程、事件和触发器等特殊对象，确保备份文件包含这些重要数据库对象的定义。这些参数结合使用，可以提高备份的灵活性和完整性，满足多样化场景需求。
3.备份命令
全备命令
mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers > /backup/full_$(date +%F).sql
这是一条用于 MySQL 全量备份的命令，具体解释如下：
mysqldump：调用 MySQL 的逻辑备份工具。-uroot：以root用户身份执行备份操作。-p123：指定用户密码为123（也可以省略密码，在运行时手动输入）。-A：表示导出所有数据库。--master-data=2：标记当前二进制日志位置，便于之后的主从同步配置，生成的标记作为注释存储在备份文件的第22行。--single-transaction：启用事务隔离，利用快照技术备份InnoDB表数据，无需锁表，实现热备。-R -E --triggers：将存储过程、事件和触发器等特殊对象一起备份。> /backup/full_$(date +%F).sql：将备份结果输出到指定目录/backup中，文件名为full_YYYY-MM-DD.sql，其中$(date +%F)动态生成当前日期（格式为年-月-日）。这条命令适合在需要全库备份的场景下使用，生成的备份文件完整性高，便于恢复和后续操作。
备份单个库或多个库
mysqldump -uroot -p123 -B 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/luffy_$(date +%F).sql
mysqldump -uroot -p123 -B 库名 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/luffy_$(date +%F).sql
这两条命令用于备份单个数据库或多个数据库，具体说明如下：
备份单个数据库
mysqldump -uroot -p123 -B 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/luffy_$(date +%F).sql
-uroot和-p123：使用root用户和密码123连接数据库。-B 库名：指定需要备份的单个数据库名，并确保备份文件中包含CREATE DATABASE语句。- 其他参数（如
--master-data=2、--single-transaction、-R、-E和--triggers）与全备命令相同，确保备份文件包含二进制日志位置、事务快照以及存储过程、事件和触发器等特殊对象。> /backup/luffy_$(date +%F).sql：将备份输出至/backup目录下，文件名以日期命名。备份多个数据库
mysqldump -uroot -p123 -B 库名 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/luffy_$(date +%F).sql
-B 库名 库名：指定多个数据库名，用空格分隔，实现同时备份多个数据库，其余参数与备份单个数据库的命令一致。这两条命令的区别在于
-B后面的数据库名数量，可根据需求灵活调整，适用于部分数据库的备份需求。
备份单个表或多个表
mysqldump -uroot -p123 库名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
mysqldump -uroot -p123 库名 表名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
这两条命令用于备份单个表或多个表，具体说明如下：
备份单个表
mysqldump -uroot -p123 库名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
-uroot和-p123：使用root用户和密码123连接数据库。库名 表名：指定需要备份的数据库名和表名，仅备份该表的结构和数据。- 其他参数：
--master-data=2：记录当前二进制日志位置，便于恢复或主从同步配置。--single-transaction：利用事务隔离的快照技术，避免锁表。-R -E --triggers：备份存储过程、事件和触发器，尽管在表备份中它们通常不常用，但可以保留。> /backup/linux7_$(date +%F).sql：将备份文件保存到/backup目录中，文件名动态包含当前日期。备份多个表
mysqldump -uroot -p123 库名 表名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
库名 表名 表名：在指定数据库下列出多个表名，用空格分隔。- 其余参数和功能与单表备份命令相同，备份多个表时会将其结构和数据存储到同一个文件中。
这两条命令适用于需要备份数据库中特定表的场景，可以根据需求精确选择单表或多表。
远程备份
mysqldump -uroot -p123 -h10.0.0.51 -A --master-data=2 --single-transaction -R -E --triggers > /backup/full_$(date +%F).sql
这条命令用于进行远程 MySQL 数据库的备份，具体说明如下：
mysqldump：调用 MySQL 的逻辑备份工具。-uroot和-p123：使用root用户和密码123进行数据库连接。-h10.0.0.51：指定远程数据库的主机地址（IP 地址为10.0.0.51）。-A：表示备份所有数据库。--master-data=2：记录当前二进制日志位置，便于恢复或配置主从复制。--single-transaction：使用事务隔离，避免锁表，适用于在线备份。-R -E --triggers：备份存储过程、事件和触发器等特殊对象。> /backup/full_$(date +%F).sql：将备份结果保存到本地/backup目录中，文件名包含当前日期（格式为年-月-日）。此命令适用于需要从远程 MySQL 数据库进行全库备份的场景，生成的 SQL 文件可以用于恢复或迁移数据库。
4.分库分表备份
备份思路
双层for循环
第一层for循环所有的库
第二层for循环库下所有的表
这段内容介绍了如何通过分库分表的方式进行备份，并提供了相应的备份脚本。首先，备份思路是使用双层
for循环，第一层遍历所有的数据库，第二层遍历每个数据库中的所有表。
备份脚本
#!/bin/bash
for ku in $(mysql -N -e 'show databases;'|egrep -v 'mysql|schema|sys')
do
mkdir /backup/$(date +%F)/$ku/ -p
for biao in $(mysql -N -e "show tables from $ku;")
do
mysqldump -h127.0.0.1 $ku $biao --master-data=2 --single-transaction -R -E --triggers > /backup/$(date +%F)/$ku/$biao.sql
done
done
具体的备份脚本如下：
#!/bin/bash：声明这是一个 Bash 脚本。for ku in $(mysql -N -e 'show databases;'|egrep -v 'mysql|schema|sys')：查询并遍历所有数据库，排除mysql、schema和sys系统数据库。mkdir /backup/$(date +%F)/$ku/ -p：为每个数据库创建一个以当前日期命名的文件夹，用于存储该数据库的备份。for biao in $(mysql -N -e "show tables from $ku;")：遍历每个数据库中的所有表。mysqldump -h127.0.0.1 $ku $biao --master-data=2 --single-transaction -R -E --triggers > /backup/$(date +%F)/$ku/$biao.sql：对每个表执行备份操作，并将结果保存到相应的目录中。done：结束内外两层循环。
技巧: 可以把账号密码写进配置文件里,这样就不会出现命令警告了
vim /etc/my.cnf
[client]
user=root
password=123
此外，提供了一个技巧：可以将 MySQL 的账号密码写入配置文件
/etc/my.cnf中，以避免每次执行命令时输入密码或出现警告。具体做法是编辑配置文件并在[client]部分加入user=root和password=123。通过这种方式，脚本执行时无需再次输入密码，避免了警告信息的显示。
5.故障恢复演练
5.1 模拟环境
create database luffy charset utf8mb4;
use luffy
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
这段内容描述了如何在 MySQL 中创建一个模拟的环境并插入一些数据，具体操作如下：
create database luffy charset utf8mb4;：创建一个名为luffy的数据库，并指定字符集为utf8mb4，该字符集支持更广泛的字符，适用于多语言环境。use luffy;：切换到luffy数据库，后续的操作将在该数据库内进行。create table t1(id int);：在luffy数据库中创建一个名为t1的表，表中包含一个id字段，类型为整数。insert into t1 values(1),(2),(3);：向t1表中插入三条记录，分别是1、2和3。commit;：提交事务，将插入的数据持久化到数据库中。这些操作是为演示或测试准备的，创建了一个简单的数据库、表，并插入了一些数据，后续可以根据这个基础环境进行其他操作或测试。
5.2 模拟周一23:00全备
mysqldump -uroot -p -A --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers >/backup/full_$(date +%F).sql
查看GTID相关信息，GTID截取起点
SET @@GLOBAL.GTID_PURGED='9b52b744-eb82-11ea-986c-000c294983f8:1-6';
查看pos号，备份开始时binlog位置点信息
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1028;
这段内容描述了如何执行一次全备，并且查看与 GTID 相关的信息以及备份开始时的 binlog 位置。
模拟周一23:00全备
mysqldump -uroot -p -A --master-data=2 --single-transaction --max_allowed_packet=64M -R -E --triggers >/backup/full_$(date +%F).sql这条命令进行全库备份，具体参数说明：
-uroot -p：使用root用户执行备份，密码在命令行输入时手动提供。-A：备份所有数据库。--master-data=2：记录备份时的二进制日志位置，并将该位置写入备份文件中的注释部分，方便用于主从复制恢复。--single-transaction：启用事务，确保 InnoDB 表的备份不会锁定表。--max_allowed_packet=64M：设置最大允许的包大小为 64MB，避免大数据表备份时发生包大小限制错误。-R -E --triggers：备份存储过程、事件和触发器。>/backup/full_$(date +%F).sql：将备份输出到/backup目录，文件名包括当前日期（例如full_2025-01-13.sql）。查看GTID相关信息，GTID截取起点
SET @@GLOBAL.GTID_PURGED='9b52b744-eb82-11ea-986c-000c294983f8:1-6';
- 通过
SET @@GLOBAL.GTID_PURGED可以设置 GTID（全局事务标识符）已被清除的范围，用于恢复或主从同步时，确保同步数据的一致性。此命令将GTID_PURGED设置为9b52b744-eb82-11ea-986c-000c294983f8:1-6，意味着 GTID 事务从1到6的范围已经被清除或应用。查看pos号，备份开始时binlog位置点信息
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1028;
- 该命令用于设置复制的起始位置，在主从复制配置中使用。
MASTER_LOG_FILE表示主服务器的 binlog 文件名，MASTER_LOG_POS表示 binlog 中的位置点。备份时，这一位置点作为二进制日志的起点，可以用于恢复时的同步定位。这些命令和操作为备份和恢复提供了二进制日志和 GTID 的关键信息，确保在恢复过程中能够精确恢复数据，并避免数据丢失或不一致。
5.3 模拟周二白天数据变化
use luffy;
create table t2 (id int);
insert into t2 values(1),(2),(3);
commit;
这段内容模拟了周二白天数据的变化，具体步骤如下：
切换到
luffy数据库use luffy;该命令切换到
luffy数据库，后续操作将在此数据库中执行。创建新表
t2create table t2 (id int);这条命令在
luffy数据库中创建了一个名为t2的新表，表中包含一个id字段，类型为整数。插入数据到
t2表insert into t2 values(1),(2),(3);向
t2表插入了三条数据：1、2和3。提交事务
commit;使用
commit命令提交事务，使得插入的数据永久保存到数据库中。这些操作模拟了在备份之后，数据库中的数据发生变化的情况，例如新增了一个表
t2，并向该表插入了数据。
5.4 模拟周二下午2点，误删除了核心库
mysql> drop database luffy;
5.5 恢复数据
第一步：查看全备时候的位置点在哪里
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=2532
第二步：查看binlog定位终止点
# at 3119
#240110 15:24:15 server id 51 end_log_pos 3237 CRC32 0xd7f3dc06 Query thread_id=2
exec_time=0 error_code=0
SET TIMESTAMP=1641972255/*!*/;
DROP TABLE `t1` /* generated by server */
第三步：截取binlog
mysqlbinlog -d luffy --start-position=2532 --stop-position=3119 /data/mysql_3306/logs/mysql-bin.000007 > /tmp/backup.sql
第四步：将全备和binlog导入到测试服务器里
scp /tmp/full_2024-01-10.sql 10.0.0.41:/opt/
scp /tmp/backup.sql 10.0.0.41:/opt/
第五步：测试服务器导入数据
mysql -uroot -p123 < full_2024-01-10.sql
mysql -uroot -p123 < backup.sql
第六步：只导出被删除的表
mysqldump -uroot -p123 luffy t1 > luffy_t1.sql
第七步：将备份数据发送给生产服务器
scp luffy_t1.sql 10.0.0.51:/opt/
第八步：生产服务器导入恢复的数据
set sql_log_bin=0;
source /opt/luffy_t1.sql
set sql_log_bin=1;
这段内容描述了如何恢复误删除的数据库
luffy，并通过结合全备与 binlog 来完成数据恢复，步骤详细如下：5.4 模拟周二下午2点，误删除了核心库
drop database luffy;：这条命令模拟了在周二下午2点，误删除了名为luffy的数据库。5.5 恢复数据
第一步：查看全备时的 binlog 位置点
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=2532;
这条命令获取在进行全备时的 binlog 位置点，确保恢复时从正确的日志文件和位置开始。第二步：查看 binlog 的定位终止点
- 提供了从 binlog 中的日志信息。该日志记录了删除表
t1的操作。通过日志位置的end_log_pos确定恢复时的终止点，即3119。第三步：截取 binlog
mysqlbinlog -d luffy --start-position=2532 --stop-position=3119 /data/mysql_3306/logs/mysql-bin.000007 > /tmp/backup.sql
使用mysqlbinlog工具从指定的 binlog 文件中提取数据，从位置2532开始，到位置3119结束，并生成 SQL 文件保存到/tmp/backup.sql。第四步：将全备和 binlog 导入到测试服务器
scp /tmp/full_2024-01-10.sql 10.0.0.41:/opt/scp /tmp/backup.sql 10.0.0.41:/opt/
将全备和 binlog 文件复制到测试服务器上。第五步：测试服务器导入数据
mysql -uroot -p123 < full_2024-01-10.sqlmysql -uroot -p123 < backup.sql
在测试服务器上，首先导入全备文件，再导入从 binlog 截取的 SQL 文件，恢复数据库。第六步：只导出被删除的表
mysqldump -uroot -p123 luffy t1 > luffy_t1.sql
通过mysqldump导出误删除的表t1，生成备份文件luffy_t1.sql。第七步：将备份数据发送给生产服务器
scp luffy_t1.sql 10.0.0.51:/opt/
将luffy_t1.sql文件复制到生产服务器。第八步：生产服务器导入恢复的数据
set sql_log_bin=0;source /opt/luffy_t1.sqlset sql_log_bin=1;
在生产服务器上导入恢复的表数据。在执行导入之前，禁用二进制日志（set sql_log_bin=0），以避免将恢复操作记录到日志中，然后再启用二进制日志。通过这些步骤，能够在数据库误删除的情况下，通过全备和 binlog 完整地恢复丢失的数据，确保数据恢复的完整性和一致性。
6.mysqldump多种备份策略和恢复策略
6.1 场景
100G 全库数据 全库备份 30分钟-40分钟，恢复整库需要5倍时间2.5-3小时之间
一张表 1G 被误删除了
6.2 备份策略
第一种：full + binlog 增量备份思路
第一步：提取full全备中的故障表数据 ，恢复数据
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q' full_2021-06-28.sql > create_table.sql
sed -n '/CREATE TABLE `t1` /,/\;/p' /data/backup/full.sql
grep -i 'INSERT INTO `t1`' full_2021-06-28.sql > data.sql
第二步：binlog中截取全备到误删除t1之间对于这张表的修改
第二种：单库单表备份+binlog 增量思路
- 第一步：恢复单表的备份
- 第二步：binlog中截取备份到误删除t1之间对于这张表的修改
该段内容描述了两种备份恢复策略，用于应对误删除表的情况。第一种策略是使用全备（full）和 binlog 增量备份。首先，从全备文件中提取出故障表的数据（如
t1表），可以通过sed命令提取表结构和数据。具体操作是先使用sed提取CREATE TABLE语句生成create_table.sql，然后提取表中的数据插入语句（INSERT INTO）保存为data.sql。接着，从 binlog 中截取从全备开始到误删除表t1之间对该表的修改，以恢复误删除后的数据。第二种策略是使用单库单表备份和 binlog 增量备份。首先恢复单表的备份，再通过 binlog 截取从备份到误删除之间的修改。这两种策略的主要区别在于第一种方法依赖于全库备份加 binlog 来恢复整个表的数据，而第二种则是通过单独的表备份和增量 binlog 备份来恢复误删除的数据。
Xtrabackup物理备份
1.介绍
percona公司研发
xtrabackup --> C C++
innobackupex --> perl语言
8.0之前，2.4.x
8.0之后，8.0
物理备份工具，类似于cp文件。
支持：全备和增量备份
pt
2.安装
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
3.全量备份数据
全备命令
innobackupex -uroot -p123 /backup/
自定义目录名备份
innobackupex -uroot -p123 --no-timestamp /backup/full_$(date +%F)
查看备份完成的目录
[root@db-51 ~]# ll /data/backup/test/2020-09-14_22-06-11/
总用量 12348
-rw-r----- 1 root root 487 9月 14 22:06 backup-my.cnf
drwxr-x--- 2 root root 48 9月 14 22:06 gtdb
-rw-r----- 1 root root 10056 9月 14 22:06 ib_buffer_pool
-rw-r----- 1 root root 12582912 9月 14 22:06 ibdata1
drwxr-x--- 2 root root 52 9月 14 22:06 ku
drwxr-x--- 2 root root 52 9月 14 22:06 linux5
drwxr-x--- 2 root root 76 9月 14 22:06 linux6
drwxr-x--- 2 root root 4096 9月 14 22:06 mysql
drwxr-x--- 2 root root 90 9月 14 22:06 oldboy
drwxr-x--- 2 root root 134 9月 14 22:06 oldya
drwxr-x--- 2 root root 8192 9月 14 22:06 performance_schema
drwxr-x--- 2 root root 160 9月 14 22:06 school
drwxr-x--- 2 root root 8192 9月 14 22:06 sys
drwxr-x--- 2 root root 54 9月 14 22:06 test
drwxr-x--- 2 root root 144 9月 14 22:06 world
-rw-r----- 1 root root 63 9月 14 22:06 xtrabackup_binlog_info
-rw-r----- 1 root root 117 9月 14 22:06 xtrabackup_checkpoints
-rw-r----- 1 root root 546 9月 14 22:06 xtrabackup_info
-rw-r----- 1 root root 2560 9月 14 22:06 xtrabackup_logfile
相关文件
1.xtrabackup_binlog_info
记录binlog位置点， 截取binlog起点位置
2.xtrabackup_checkpoints
from_lsn = 0 # 一般增量备份会关注，一般上次备份的to_lsn的位置
to_lsn = 180881595 # CKPT-LSN 最近的内存数据落地到磁盘上的LSN号
last_lsn = 180881604 # xtrabackup_logfile LSN
3.xtrabackup_info
备份总览信息
4.xtrabackup_logfile
备份期间产生的redo变化
4.全量备份恢复
第0步：模拟删除
pkill mysqld
rm -rf /data/mysql_3306/*
第1步: prepare 准备备份阶段
innobackupex --apply-log /backup/2024-01-10_14-51-59
第2步: 恢复数据-任选一个方法即可
方法1): 使用命令复制全备文件到数据目录
innobackupex --copy-back /backup/2024-01-10_14-51-59/
innobackupex --move-back /backup/2024-01-10_14-51-59/
方法2): 修改配置文件,数据目录指向备份文件目录
[root@db-52 ~]# cat /etc/my.cnf
[mysqld]
port=3306
user=mysql
basedir=/opt/mysql
#直接修改为备份文件的目录
datadir=/backup/2024-01-10_14-51-59/
#主从复制参数
server_id=51
log_bin=/data/mysql_3306/mysql-bin
方法3): 创建备份目录的软链接到数据目录
mkdir /backup/2024-01-10_14-51-59/logs
touch /backup/2024-01-10_14-51-59/logs/mysql.err
chown -R mysql:mysql /backup/2024-01-10_14-51-59
ln -s /backup/2024-01-10_14-51-59/ /data/mysql_3306
第3步: 小坑-恢复数据后记得更改权限
1)备份恢复的时候不会把日志目录一起备份，比如错误日志和Binlog日志，恢复完成后需要手动创建
2)恢复后数据目录的用户权限都是root，需要手动更改权限
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld
5.增量备份数据
5.1 介绍
自带的功能。
每次增量一般是将最近一次备份作为参照物。
自动读取参照物cat xtrabackup_checkpoints中to_lsn值，与当前CKPT的LSN对比，备份变化过page。
备份期间新的数据变化，通过redo自动备份。
恢复数据时，需要把所有需要的增量合并到FULL中。无法通过增量单独恢复数据，依赖与全备。
5.2 增量备份演练 FULL(周日)+inc1(周一)+inc2(周二)+inc3(周三)
第1步: 实验数据准备
create database xbk charset utf8mb4;
use xbk
create table full (id int);
insert into full values(1),(2),(3);
创建一个名为
xbk的数据库，并设置字符集为utf8mb4。在
xbk数据库中创建一个表full，该表包含一个整数类型的列id。向
full表中插入三条数据(1),(2),(3)，为后续的备份和恢复提供数据。
第2步: 模拟周日 23:00 全备
innobackupex -uroot -p123 --no-timestamp /backup/full_$(date +%F)
使用
innobackupex工具执行 MySQL 全备（即备份整个数据库），并将备份存储在/backup/目录下，文件名带有当天的日期。
--no-timestamp选项禁止在备份目录中添加时间戳，这样可以确保备份文件名称一致。
-uroot -p123是指定 MySQL 的用户名和密码进行连接。
第3步: 模拟周一白天数据变化
use xbk
create table inc1 (id int);
insert into inc1 values(1),(2),(3);
在
xbk数据库中创建一个新的表inc1，并插入数据(1),(2),(3)。这个表的创建和数据插入代表了周一的数据库变化。
第4步: 第一次增量备份
innobackupex -uroot -p123 --no-timestamp --incremental --incremental-basedir=/backup/full_2022-01-13 /backup/inc1_$(date +%F)
使用
innobackupex进行第一次增量备份。
--incremental表示进行增量备份，而--incremental-basedir指定增量备份的基准目录（即上一次的全备目录）。本次备份将保存自全备以来的数据变化。备份文件存储在
/backup/inc1_$(date +%F)目录中，文件名带有当天的日期。
第5步: 模拟周二白天数据变化
use xbk
create table inc2 (id int);
insert into inc2 values(1),(2),(3);
在
xbk数据库中创建一个新的表inc2，并插入数据(1),(2),(3)。这代表了周二的数据库变化。
第6步: 第二次增量备份
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1_2022-01-13 /backup/inc2_$(date +%F)
在本步骤中，执行第二次增量备份。与第1次增量备份相似，但本次基准目录是第一次增量备份的目录。
-S /tmp/mysql.sock指定了 MySQL 的 Unix 套接字文件路径。备份文件存储在
/backup/inc2_$(date +%F)目录。
第7步: 模拟周三白天数据变化
use xbk
create table inc3 (id int);
insert into inc3 values(1),(2),(3);
在
xbk数据库中创建表inc3，并插入数据(1),(2),(3)。这表示周三的数据库变化。
第8步: 第三次增量备份
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc2_2022-01-13 /backup/inc3_$(date +%F)
执行第三次增量备份，与前两次相同，基准目录为第二次增量备份的目录。
备份文件存储在
/backup/inc3_$(date +%F)目录。
第9步: 模拟周四白天数据变化
use xbk
create table inc4(id int);
insert into inc4 values(1),(2),(3);
commit;
在
xbk数据库中创建一个新的表inc4，并插入数据(1),(2),(3)。这代表了周四的数据库变化。
第10步: 周四下午出现数据损坏。如何恢复到误删除之前。
pkill mysqld
rm -rf /data/mysql_3306/*
停止 MySQL 服务并清空数据库目录，模拟数据丢失和数据库损坏的情况。此时数据文件已经丢失，需要恢复数据。
第11步: 截取binlog位置点
全备-增量1-增量2-增量3-binlog
备份的数据:
全备-增量1-增量2-增量3
binlog起始和终止点
起始点:
cat /backup/inc3_2022-01-13/xtrabackup_binlog_info
mysql-bin.000001 21105729
查看增量备份（
inc3）中的 binlog 信息，获取备份时的 binlog 文件和位置点。这里是从mysql-bin.000001文件的21105729位置开始备份的。
终止点:
at 21106154
确定 binlog 文件的终止位置，在误删除数据后，通过
mysqlbinlog工具截取 binlog 数据时需要指定终止位置。
截取命令:
mysqlbinlog --start-position=21105729 --stop-position=21106154 /opt/logs/mysql-bin.000001 > /tmp/backup.sql
使用
mysqlbinlog工具从指定的 binlog 起始位置到终止位置截取 binlog 事件，保存为/tmp/backup.sql文件。这个步骤是为了获取从上次增量备份到误删除数据之间的所有变化。
第12步: 处理全备,将临时文件整合到磁盘里
innobackupex --apply-log --redo-only /backup/full_2022-01-13/
对全备数据执行
--apply-log --redo-only操作，准备增量备份合并。该步骤会应用全备数据中的事务日志，但不实际进行数据恢复。
第13步: inc1合并到full中，并且prepare
innobackupex --apply-log --redo-only --incremental-dir=inc1_2022-01-13 full_2022-01-13
检验合并结果:
cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc1_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
解释：
- 将第一次增量备份合并到全备数据中，并且通过
--apply-log --redo-only准备日志。- 这会将增量备份的数据合并到全备数据中，以便恢复。
第14步: inc2合并到full中，并且prepare
innobackupex --apply-log --redo-only --incremental-dir=inc2_2022-01-13 full_2022-01-13
检验合并结果:
cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc2_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
将第二次增量备份合并到全备数据中，并继续准备日志。
第15步: inc3合并到full中，并且prepare
innobackupex --apply-log --redo-only --incremental-dir=inc3_2022-01-13 full_2022-01-13
检验合并结果:
cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc3_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
将第三次增量备份合并到全备数据中，并准备日志。
第16步:将合并后全备再次prepare
innobackupex --apply-log /backup/full_2022-01-13
对合并后的全备进行最终的
--apply-log操作，这时数据已经准备好恢复。
第17步:恢复数据
innobackupex --copy-back /backup/full_2022-01-13
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld
mysql -uroot -p123 < /backup/backup.sql
将合并后的备份数据复制回 MySQL 数据目录。
创建必要的日志文件并设置文件权限。
启动 MySQL 服务并恢复备份期间截取的 binlog 数据。
第18步:检查数据
t100w
xbk inc1 inc2 inc3 inc4
进行数据验证，检查恢复后的数据库和表（
xbk、inc1、inc2、inc3、inc4）中是否包含恢复的数据。
第19步:立刻做一次全备
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/full_OK_$(date +%F)
在数据恢复完成后，立即进行一次新的全备操作，确保当前的数据库状态已备份，并能够进行后续的恢复操作。
grep “to_lsn”
> 将第三次增量备份合并到全备数据中，并准备日志。
**第16步:将合并后全备再次prepare**
```sql
innobackupex --apply-log /backup/full_2022-01-13
对合并后的全备进行最终的
--apply-log操作，这时数据已经准备好恢复。
第17步:恢复数据
innobackupex --copy-back /backup/full_2022-01-13
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld
mysql -uroot -p123 < /backup/backup.sql
将合并后的备份数据复制回 MySQL 数据目录。
创建必要的日志文件并设置文件权限。
启动 MySQL 服务并恢复备份期间截取的 binlog 数据。
第18步:检查数据
t100w
xbk inc1 inc2 inc3 inc4
进行数据验证，检查恢复后的数据库和表（
xbk、inc1、inc2、inc3、inc4）中是否包含恢复的数据。
第19步:立刻做一次全备
innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/full_OK_$(date +%F)
标签：--,恢复,备份,sql,day11,mysql,backup,数据库 From： https://blog.csdn.net/weixin_46545179/article/details/145119630在数据恢复完成后，立即进行一次新的全备操作，确保当前的数据库状态已备份，并能够进行后续的恢复操作。