MySQL的备份和恢复
安装Xtrabackup
https://www.percona.com/
# 2.安装
[root@db02 ~]# yum localinstall -y percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm
[root@db02 ~]# innobackupex ·
[root@db02 ~]# xtrabackup
Xtrabackup
1）对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2）对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3）备份/恢复时读取配置文件/etc/my.cnf
Xtrabckup 和 innobackupex区别
XtraBackup:
1）XtraBackup 是一个开源工具，专门用于 MySQL 数据库的 InnoDB 引擎的备份。它支持在线热备份，意味着在备份过程中，数据库仍然可以正常提供服务而不需要停机。
2）它直接与 InnoDB 存储引擎进行交互，创建物理备份，这使得备份速度快且性能开销低。
innobackupex:
1）innobackupex 是 XtraBackup 的一个 Perl 脚本封装器，旨在提供更友好的命令行接口和一些额外的功能。它不仅支持 InnoDB，还可以处理 MyISAM 和其他存储引擎的数据，同时管理 MySQL 配置文件和日志文件。
2）innobackupex 可以自动处理 MySQL 的各种引擎，包括非 InnoDB 引擎，它也负责生成备份日志，和将 XtraBackup 的备份与 MySQL 的非 InnoDB 数据一起进行备份。
全备
[root@db02 ~]# cat /etc/my.cnf
[mysqld]
socket=/opt/mysql.sock
[client]
socket=/opt/mysql.sock
## 热备 -------- 不锁表 备份
innobackupex --user=root --password='123' /backup
innobackupex -uroot /backup
## 温备份 -------- 锁表备份
xtrabackup -uroot --backup --target-dir=/001
备份文件解读
-rw-r----- 1 backup-my.cnf
-rw-r----- 1 ibdata1
drwxr-x--- 2 mysql
drwxr-x--- 2 performance_schema
drwxr-x--- 2 test
-rw-r----- 1 xtrabackup_binlog_info # 本次备份二进制日志信息
-rw-r----- 1 xtrabackup_checkpoints # 本次备份sql记录点日志信息
-rw-r----- 1 xtrabackup_info # 本次备份的一些版本信息
-rw-r----- 1 xtrabackup_logfile # 本次备份的redolog文件
cat xtrabackup_binlog_info
mysql-bin.000001 120 #----记录当前二进制日志数据点点
cat xtrabackup_checkpoints
backup_type = full-backuped # 这表示备份类型是完整备份（full backup）
from_lsn = 0 # 备份的起始位置
to_lsn = 1626017 # 备份的结束位置
last_lsn = 1626017 # 备份期间的最后一个日志序列号
compact = 0 # 是否使用了紧凑备份模式
recover_binlog_info = 0 # 是否在备份时收集了二进制日志信息
flushed_lsn = 1626017 # 表示已刷新到磁盘的 LSN。
cat xtrabackup_info
uuid = 776597be-6051-11ef-9767-000c293e8325 ## 本次备份的UUID
name = ## 备份的名称
tool_name = innobackupex ## 生成此次备份的工具名称
tool_command = -uroot /backup ## 命令
tool_version = 2.4.29 ## 生成此次备份的 innobackupex 工具的版本号
ibbackup_version = 2.4.29 ## XtraBackup 的版本号
server_version = 5.6.50-log ## MySQL 服务器版本
start_time = 2024-08-22 14:40:46 ## 备份开始的时间
end_time = 2024-08-22 14:40:48 ## 备份结束的时间
lock_time = 0 ## 备份期间用于表锁定的时间
binlog_pos = filename 'mysql-bin.000001', position '120' ## 二进制日志位置。
innodb_from_lsn = 0 ## 开始的 lsn
innodb_to_lsn = 1626017 ## 结束的 lsn
partial = N ## 表示是否为部分备份。
incremental = N ## 指明是否为增量备份
format = file ## 备份的存储格式
compact = N ## 表示是否使用了紧凑备份模式
compressed = N ## 表示备份是否经过压缩
encrypted = N ## 表示备份是否经过加密
###### 详解一些 ##########
LSN:日志序列号（Log Sequence Number, LSN）
backup_type的状态:
full-backuped ------ 完整备份
incremental ------ 增量备份
prepared ------ 已准备好恢复
incremental-prepared ------ 增量备份已准备好恢复
backuping ------ 备份正在进行中
apply-log ------ 正在应用日志到备份中
merged ------ 备份已合并。
全备的恢复
前提1：被恢复的目录是空的
前提2：被恢复的数据库的实例是关闭的
# 停库
[root@db02 ~]# /etc/init.d/mysqld stop
# redo中的数据 重做，undo中的数据 回滚
[root@db02 ~]# innobackupex --apply-log /backup/2024-08-22_10-25-34/
# 清空datadir
[root@db02 ~]# rm -fr /app/mysql/data
# 恢复数据
[root@db02 ~]# innobackupex --copy-back /backup/2024-08-22_10-44-56/
# 授权
[root@db02 ~]# chown -R mysql.mysql /app/mysql/data
# 启动
[root@db02 ~]# /etc/init.d/mysqld start
##----------------## 不建议的方法 ## ------------------------##
# redo中的数据 重做，undo中的数据 回滚
[root@db02 ~]# innobackupex --apply-log /backup/2024-08-22_10-25-34/
# 清空datadir
[root@db02 ~]# rm -fr /app/mysql/data
# 恢复数据
[root@db02 ~]# mv /backup/2024-08-22_10-25-34/ /app/mysql/data
# 授权
[root@db02 ~]# chown -R mysql.mysql /app/mysql/data
# 启动
[root@db02 ~]# /etc/init.d/mysqld start
增量备份
备份方式
1）基于上一次备份进行增量
2）增量备份无法单独恢复，必须基于全备进行恢复
3）所有增量必须要按顺序合并到全备当中
#### 增量备份命令详解
#--全量备份
innobackupex -uroot -p123 --no-timestamp /backup/full_$(date +%F)
#--后续增量备份
innobackupex -uroot -p --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc1_$(date +%F-%H)
--no-timestamp
这个选项告诉 innobackupex 不要在备份目录名称中附加时间戳。如果不使用这个选项，innobackupex 默认会在生成的备份目录名后加上时间戳。
--incremental
这个选项表示创建增量备份。增量备份只会包含自上次备份以来的更改部分。
--incremental-basedir=/backup/full_2024-08-22/
指定此次增量备份的基准目录，也就是上一次备份的目录。innobackupex 将根据该基准目录中的 LSN（Log Sequence Number）确定自上次备份以来的更改。
/backup/inc1_$(date +%F-%H)
指定增量备份的目标目录
增量备份实战
##### 情况汇报
有每日凌晨的全备
有每一小时的曾备
持续有数据写入
误删除数据
利用备份 和binlog 来恢复误删除的数据
select * from b.b;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
a.a 库中有持续写如的数据
[root@db02 ~]# cat insql.sh
#!/bin/bash
mysql -e 'create database if not exists a'
mysql -e 'create table if not exists a.a(id int)'
num=1
while true ;do
mysql -e "insert into a.a values($((num++)))"
sleep 3
done
cat xtrabackup_binlog_info xtrabackup_checkpoints
############ 开始模拟故障 #############
#-----01 凌晨的全备
innobackupex -uroot -p123 --no-timestamp /backup/full_$(date+%F)
起始lsn： 0
终止lsn： 2142395
binlog数据点： 13771
#-----02 每小时的增备一号
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full_2024-08-22/ /backup/inc1_$(date +%F-%H)
起始lsn： 2142395
终止lsn： 2200479
binlog数据点： 25754
#-----03 每小时的增备二号
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc1_2024-08-22-15/ /backup/inc2_$(date +%F-%H)
起始lsn： 2200479
终止lsn： 2250825
binlog数据点： 42696
#-----04 每小时的增备三号
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc2_2024-08-22-15/ /backup/inc3_$(date +%F-%H)
起始lsn： 2250825
终止lsn： 2270831
binlog数据点： 53728
#-----05 发生事故删表
drop table b.b;
#-----06 用户还在持续写入数据 到停止数据库服务
[root@db02 ~]# sh insql.sh
^C
####### 增量备份恢复 ########
1）full+inc1+inc2
2）需要将inc1和inc2按顺序合并到full中
3）分步骤进行--apply-log
######## 开始操作 ######
# 停库
[root@db02 ~]# /etc/init.d/mysqld stop
# 清空datadir
[root@db02 ~]# mv /app/mysql/data /tmp/
### 增量备份的整合 ### 全备只做redo，不做undo
1） 先只提交全备的redo
innobackupex --apply-log --redo-only /backup/full_2024-08-22/
状态：log-applied
起始lsn： 0
终止lsn： 2142395
binlog数据点： 13771
2） 将备份1添加到全备中 只提交redo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1_2024-08-22-15/ /backup/full_2024-08-22/
状态：log-applied
起始lsn： 0
终止lsn： 2200479
binlog数据点： 25754
3） 将备份2添加到全备中 只提交redo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2_2024-08-22-15/ /backup/full_2024-08-22/
状态：log-applied
起始lsn： 0
终止lsn： 2250825
binlog数据点： 42696
4） 将备份3添加到全备中 只提交redo undo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc3_2024-08-22-15/ /backup/full_2024-08-22/
状态：log-applied
起始lsn： 0
终止lsn： 2270831
binlog数据点： 53728
5）将全备 redo 和 undo都做一遍
innobackupex --apply-log /backup/full_2024-08-22/
状态：full-prepared
起始lsn： 0
终止lsn： 2270831
binlog数据点： 53728
6）将整合的备份恢复
innobackupex --copy-back /backup/full_2024-08-22/
b.b 已经恢复了
a.a 恢复到268
7） 截取binlog日志 备份 --- 删表
起始位置点：53728 # cat xtrabackup_binlog_inf
终止位置点：64366 # mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001|grep -iC 10 'drop'
mysqlbinlog --start-position=53728 --stop-position=64366 ./mysql-bin.000001 > /tmp/inc1.sql
8) 截取binlog日志 删表后 --- 停表
起始位置点：64477 # cat xtrabackup_binlog_inf
终止位置点：70607 # ll ./mysql-bin.000001
mysqlbinlog --start-position=64477 --stop-position=70607 ./mysql-bin.000001 > /tmp/inc2.sql
9） 导入数据
set sql_log_bin=0 # 临时关闭 binlog
mysql < /tmp/inc1.sql
mysql < /tmp/inc2.sql
10） 数据库上线