4. MYSQL数据库隔离级别、服务日志与备份恢复详解

MySQL 数据库日志管理与数据备份恢复

一、数据库事务工作流程(ACID 事务隔离性详解)

1.1 读隔离性:隔离级别 + MVCC(乐观锁/悲观锁)

隔离级别 说明 特点
RU(读未提交) 可以直接读取其他事务内存中未提交的数据 脏读
RC(读已提交) 只能读取其他事务已提交的数据 不可重复读
RR(可重复读) 只能读取自己事务中的信息(写操作不干扰读) MySQL 默认级别
SR(串行化) 事务对行操作时(读或写)都会产生行锁,阻塞其他事务 并发性能最低

1.2 MVCC 机制(多版本并发控制)

MVCC 类似 Git 的多分支机制,实质是应用快照技术实现读数据信息的隔离。

隔离级别 快照策略 效果
RC 基于语句级别的快照读 每执行一条查询,都获取最新已提交事务的快照 → 产生不可重复读
RR 基于事务级别的快照读 事务中首条查询生成快照,后续一直读取该快照 → 保证可重复读

1.3 写隔离性:隔离级别 + 锁

锁的作用:

  1. 避免事务并发冲突问题
  2. 避免资源信息抢占释放

数据库服务应用分类-锁:

锁类型 说明 特点
S 锁(共享锁) 对同一数据信息操作时,可以有多个锁申请 读操作
X 锁(排他锁) 对同一数据信息操作时,只有一个锁可以申请 写操作

早期应用在表上:

A-读取数据-S锁   B-读取数据-S锁  ✅ 可以
A-写入数据-X锁   B-写入数据-?? ❌ 无法申请

目前应用在行上:

A-读取数据-S锁   B-读取数据-S锁  ✅ 可以
A-写入数据-X锁   B-写入数据-?? ❌ 无法申请(行级锁更精细)

意向锁(IS/IX)—— 表级锁,行操作的"预告":

锁类型 说明 作用
IS(意向读锁) 对表中某行进行读取时,必须先取得 IS 锁 表-IS → 行-S
IX(意向排他锁) 对表中某行进行修改时,必须先取得 IX 锁 表-IX → 行-X

二、数据库服务日志概述

数据库中有多个不同的日志,分别保存在不同文件中:

层级 日志类型 说明
服务层 general_log / error_log / bin_log / slow_log 记录服务运行情况、数据存储情况(用于数据恢复 / 数据同步)
引擎层 redo 日志 / undo 日志 / dwr 日志 InnoDB 存储引擎内部日志

三、数据库服务日志分类详解

3.1 general_log(通用查询日志)

作用: 记录数据库运行过程中所有操作数据库的语句信息,可用于测试和审计

general_log=OFF
# -- 默认关闭,建议在需要调试时(功能测试、语句审计)打开
general_log_file=/data/3307/logs/general_log/xiaoQ-01.log
# -- 建议日志路径与数据存放路径分离

3.2 error_log(错误日志)

作用: 记录数据库服务运行状态日志(note / error / warning)

log_error=./xiaoQ-01.edu.err
# -- 建议日志路径与数据存放路径分离

⚠️ 数据库无法启动时,如果看不到错误日志信息?
99.9% 的原因是:数据库服务配置文件写错了数据库服务没有正常初始化

3.3 bin_log(二进制日志)⭐

作用: 存储数据库 DDL / DML(INSERT / UPDATE / DELETE)操作命令语句信息

  • 实现数据恢复(增量数据恢复)
  • 实现数据同步(主从复制)

3.3.1 激活 binlog 日志

log_bin=/data/3307/logs/bin_log/db01-binlog
# -- 8.0.26 默认已开启

3.3.2 查看 binlog 日志状态

-- 查看有多少个 binlog 文件及内容变化量(查询不会记录)
SHOW BINARY LOGS;

-- 查看当前日志状态信息
SHOW MASTER STATUS;

SHOW MASTER STATUS 关键字段:

字段 说明
Position 记录日志事件变化的位置点
Binlog_Do_DB 白名单设置(主从复制)
Binlog_Ignore_DB 黑名单设置(主从复制)
Executed_Gtid_Set 全局事务编号(5.7/8.0)

3.3.3 查看 binlog 日志内容

方式一:数据库内部查看

SHOW BINLOG EVENTS IN 'binlog.000002';

方式二:系统命令查看

mysqlbinlog db01-binlog.000003

3.3.4 筛选 binlog 日志事件

方式一:利用 grep 管道过滤

mysql -S /tmp/mysql3307.sock -e "show binlog events in 'db01-binlog.000005'" | grep 'drop database world'
# 输出:db01-binlog.000005  722583  Query  1  722690  drop database world /* xid=5413 */

方式二:使用 pager 过滤

-- 搭配 less 分页查看
mysql> PAGER less;
mysql> SHOW BINLOG EVENTS IN 'db01-binlog.000005';

-- 搭配 grep 精确过滤
mysql> PAGER grep "drop database";
PAGER set to 'grep "drop database"'
mysql> SHOW BINLOG EVENTS IN 'db01-binlog.000005';

3.3.5 binlog 核心参数

参数一:sync_binlog(刷新日志到磁盘策略) — 数据库"双一"配置参数之一

SELECT @@sync_binlog;
说明 特点
0 由操作系统缓存自己决定何时刷新 性能最好,安全性最低
1 每次事务提交立即刷新到磁盘 ⭐ 安全性最高(推荐)
N 每组 N 次事务提交后刷新 减少 IO 损耗,折中方案

参数二:binlog_format(binlog 日志格式)

SELECT @@binlog_format;
格式 全称 说明
ROW RBR(Row-Based Replication) 记录行的变化信息,底层记录,可能有多条日志
STATEMENT SBR(Statement-Based Replication) 记录原原本本的语句;DDL / DCL 只能用此格式
MIXED MBR(Mixed-Based Replication) 混合格式,由数据库自行决定记录语句还是行的变化

3.3.6 日志滚动切割(4 种方式)

-- 方式一:数据库中操作
FLUSH LOGS;
# 方式二:命令行操作
mysqladmin -S /tmp/mysql3307.sock flush-logs

# 方式三:重启数据库服务
systemctl restart mysqld3307
-- 方式四:自动切割(按大小)
SELECT @@max_binlog_size;
-- 设置 binlog 切割时的存储容量上限

3.3.7 日志清理方法

方式一:自动过期清理

binlog_expire_logs_seconds=2592000
# -- 按秒清理(2592000秒 = 30天)

expire_logs_days=0
# -- 按天清理(0 表示不自动清理)

方式二:手动清理

-- 将 binlog 清理到指定文件(保留该文件之后的日志)
PURGE BINARY LOGS TO 'db01-binlog.000006';

-- 根据时间清理
PURGE BINARY LOGS BEFORE '2019-04-04 22:46:26';

⚠️ 企业清理建议: binlog 日志保留至少两个全备周期内的日志量

周日(0点)    周一(0)   周二(0)  ...  周六(0)      周日(0)      周一(0)
全备         binlog   binlog       binlog       全备         binlog
                                                    binlog

3.3.8 日志远程备份

利用专门存储服务器同步备份 binlog 日志:

mkdir -p /binlog_backup

# 远程拉取 binlog 日志(--raw 保留原始格式,--stop-never 持续同步)
mysqlbinlog -R --host=10.0.0.51 --user=root --password=xxxx --port=3307 \
  --raw --stop-never db01-binlog.000006 &

# 后台永久运行(二选一)
screen    # screen 会话
nohup     # nohup 后台

3.3.9 binlog 实战:模拟误删恢复

步骤一:模拟创建数据

CREATE DATABASE bindb;
USE bindb;
CREATE TABLE t1(id INT);
INSERT INTO t1 VALUES(1),(2),(3);

步骤二:模拟误删除

DROP DATABASE bindb;

步骤三:利用 binlog 恢复数据

# 根据 position 截取日志
mysqlbinlog --start-position=156 --stop-position=896 \
  /data/3307/logs/bin_log/db01-binlog.000001 >/tmp/bin.sql

解密 DML 加密语句(ROW 格式下的查看):

mysqlbinlog --base64-output=decode-rows -vvv /data/3306/data/binlog.000003

# 解密后显示内容等价于:
# INSERT INTO test02 SET id=1, name='xiaoA', age=18;
# INSERT INTO test02 VALUES(2, 'xiaoB', 19);

3.4 slow_log(慢查询日志)

作用: 记录查询较慢的语句,关注慢查询可减少磁盘 IO 消耗

-- 查看慢查询相关参数
SELECT @@slow_query_log;                  -- 是否激活慢查询日志
SELECT @@slow_query_log_file;             -- 日志保存路径
SELECT @@long_query_time;                 -- 超过多少秒算慢查询(建议 0.01~0.1)
SELECT @@log_queries_not_using_indexes;   -- 是否记录未走索引的查询

动态开启慢查询:

SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=0.01;
SET GLOBAL log_queries_not_using_indexes=1;

慢查询日志分析工具:

# -s c 按查询次数排序  -t 3 取前3条
mysqldumpslow -s c -t 3 /data/3307/data/db01-slow.log

四、数据库备份恢复方式

4.1 为什么需要备份恢复

损坏类型 原因 恢复手段
逻辑损坏 误删除、误修改 备份 + 日志恢复 / 延时从库
物理损坏 磁盘故障、文件系统损坏、数据文件损坏 主从 / 高可用 / 备份 + 日志恢复

4.2 备份方式对比

备份方式 分类 原理 适用场景
物理备份 冷备 / 热备 底层数据页或文件级备份 数据量 > 50G(xtrabackup)
逻辑备份 热备 导出 SQL 语句备份 数据量 < 50G(mysqldump)

五、mysqldump 逻辑备份实践

5.1 基本语法

mysqldump -u用户 -p密码 [备份参数] > /路径/备份文件.sql

常用参数:

参数 说明
-A 全库备份(All databases)
-B 指定要备份的数据库
-F 备份完毕后,自动切换 binlog 日志

5.2 全库备份

mysqldump -P3307 -S /tmp/mysql3307.sock -A > /database_backup/all_database.sql

5.3 单库 / 多库备份

# 单库备份
mysqldump -P3307 -S /tmp/mysql3307.sock -B oldboy > /database_backup/oldboy.sql

# 多库备份
mysqldump -P3307 -S /tmp/mysql3307.sock -B oldboy bindb > /database_backup/oldboy_world.sql

5.4 单表 / 多表备份

# 单表备份
mysqldump -uroot -p123456 test1 t1 > /database_backup/t1_bak.sql
# 单表恢复
mysql -uroot -p123456 test1 < /database_backup/t1_bak.sql

# 多表备份
mysqldump -uroot -p123456 test1 t1 t2 t3 > /database_backup/t123_bak.sql

六、mysqldump 进阶参数详解

6.1 核心进阶参数

参数 说明
--single-transaction ⭐ 备份时对数据"拍快照",备份期间数据库可继续读写(InnoDB 热备关键参数)
--master-data=2 备份文件中记录 binlog 位置点信息(注释形式),用于增量恢复
-R 备份存储过程(Procedure)
-E 备份事件(Event)
--triggers 备份触发器(Trigger)
--max_allowed_packet=64M 设置最大数据包大小,防止大字段导出失败

6.2 企业级备份命令

# 全库备份(企业推荐写法)
mysqldump -uroot -p123456 -S /tmp/mysql3306.sock \
  --master-data=2 --single-transaction -A \
  -R -E --triggers --max_allowed_packet=64M \
  > /database_backup/xiaoQ-01.sql

# 单库备份(带日期命名)
mysqldump -uroot -poldboy123 -B mdb \
  --master-data=2 --single-transaction -R -E --triggers \
  > /databases_backup/oldboy_`date +%F`.sql

6.3 备份文件中的关键信息解读

-- 查看备份文件头部信息
-- vim /database_backup/full_2022-11-26.sql

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '9d14be39-6423-11ed-bb21-000c2996c4f5:1-6';
-- 表示恢复时会跳过 GTID 1-6 的事件(全备中已包含),从 GTID 编号 7 开始增量恢复

CHANGE MASTER TO MASTER_LOG_FILE='binlog.000013', MASTER_LOG_POS=1312;
-- 增量数据临界点:binlog.000013 文件的 1312 位置(备份结束时的位置点)

七、逻辑备份企业实战案例(全备 + 增量恢复)

7.1 场景描述

周一~周二:正常业务录入数据
周二晚:  进行全库备份
周三上午:增量数据写入 + 误删 DROP DATABASE
周三下午:利用全备 + binlog 增量恢复

7.2 完整恢复步骤

第一步:模拟正常业务数据(周一~周二)

FLUSH LOGS;
CREATE DATABASE mdb;
USE mdb;
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
BEGIN;
INSERT INTO t1 VALUES(1),(2),(3);
INSERT INTO t2 VALUES(1),(2),(3);
COMMIT;

第二步:全库备份(周二晚)

mysqldump -uroot -p123456 -S /tmp/mysql3306.sock \
  -A --master-data=2 --single-transaction -R -E --triggers \
  --max_allowed_packet=64M > /database_backup/full_`date +%F`.sql

第三步:增量数据写入(周三上午)

USE mdb;
BEGIN;
CREATE TABLE t3 (id INT);
INSERT INTO t3 VALUES(1),(2),(3);
INSERT INTO t2 VALUES(4),(5),(6);
COMMIT;

第四步:误删除(周三上午)

DROP DATABASE mdb;

第五步:全备数据恢复

-- ⭐ 恢复前先关闭 binlog 记录,防止恢复操作被记录到日志中
SET sql_log_bin=0;
SOURCE /database_backup/full_2023-05-09.sql;

第六步:获取增量起始点,截取 binlog

# 查看备份文件中的位置点
vim /database_backup/full_2023-05-09.sql
# CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1090;

# 截取增量日志(从全备结束位置到误删之前的正常数据)
mysqlbinlog --start-position=1090 --stop-position=1839 \
  /data/3306/data/binlog.000001 > /tmp/add.sql

第七步:恢复增量数据

SOURCE /tmp/add.sql;

第八步:追加误删之后的正常数据(如有)

mysqlbinlog --start-position=2017 /data/3306/data/binlog.000001 > /tmp/add02.sql
SOURCE /tmp/add02.sql;

⚠️ 以上恢复操作建议在从库或备份库上进行,恢复完成后切换业务到从库/备库


八、逻辑备份痛点与 GTID 事务管理

8.1 逻辑备份痛点

  • 大的数据库中仅有少量数据损坏时,全备恢复代价太大
  • 应尽量用增量数据修复故障数据,避免采用全备恢复

8.2 GTID(全局事务 ID)介绍

GTID(Global Transaction ID)标识 binlog 日志记录的唯一性

GTID 格式: server_uuid:N

组成部分 说明
server_uuid 数据库初始化后自动生成的随机数(全局唯一)
N 第几个事务/事件,不断自增

GTID 核心作用:

  • 标识事务唯一性
  • 保证日志恢复时的一致性
  • 具备"幂等性"(同一事务不会重复执行)

8.3 GTID 功能配置

SELECT @@gtid_mode;                -- 是否激活 GTID 功能
SELECT @@enforce_gtid_consistency;  -- 是否开启强制一致性(开发侧)
SELECT @@log_slave_updates;         -- 从库是否也记录 binlog(从库复制链需要)

8.4 利用 GTID 实现日志截取恢复

场景:事务分布在多个 binlog 文件中,需要精确恢复

第一步:生成新的 binlog 日志

FLUSH LOGS;

第二步:进行事务操作

CREATE DATABASE gtdb;
USE gtdb;
CREATE TABLE t1(id INT);
INSERT INTO t1 VALUES(1); COMMIT;
INSERT INTO t1 VALUES(2); COMMIT;
INSERT INTO t1 VALUES(3); COMMIT;

第三步:模拟误删除

DROP DATABASE gtdb;

第四步:获取需要保留的事务编号

SHOW BINLOG EVENTS IN 'binlog.000006';
-- 确认需要保留的事务编号(如 5-9)

第五步:利用 GTID 截取日志并恢复

# ⭐ 使用 --skip-gtids 去除幂等性限制,防止恢复时报 GTID 冲突
mysqlbinlog --skip-gtids \
  --include-gtids='4176544b-e999-11ed-b55a-000c292774fb:5-9' \
  /data/3306/data/binlog.000006 > /tmp/gtid02.sql

# 恢复时临时关闭 binlog 记录
SET sql_log_bin=0;
SOURCE /tmp/gtid02.sql;

排除指定 GTID 截取:

# --exclude-gtids 排除指定事务,--include-gtids 包含范围事务
mysqlbinlog --exclude-gtids='uuid:4' \
  --include-gtids='uuid:3-7' \
  /data/3306/data/binlog.000004

跨多日志文件截取:

mysqlbinlog --skip-gtids \
  --include-gtids='uuid:1-10' \
  /data/3306/data/binlog.000001 \
  /data/3306/data/binlog.000002 \
  /data/3306/data/binlog.000003 > /tmp/gtid.sql

九、单库 / 单表 / 部分行数据恢复

9.1 A 计划:单库日志截取(企业实战)

第一步:创建数据环境

CREATE DATABASE test1;
USE test1;
CREATE TABLE t1 (id INT);
BEGIN; INSERT INTO t1 VALUES(1),(2); COMMIT;

CREATE DATABASE test2;
USE test2;
CREATE TABLE t2 (id INT);
BEGIN; INSERT INTO t2 VALUES(1),(2); COMMIT;

-- 跨库事务
BEGIN;
USE test1; INSERT INTO t1 VALUES(3),(4);
USE test2; INSERT INTO t2 VALUES(3),(4);
COMMIT;

第二步:破坏数据

DROP DATABASE test1;

第三步:按数据库截取日志

# -d 指定只截取 test1 库的操作
mysqlbinlog --skip-gtids --start-position=6029 --stop-position=7980 \
  -d test1 /data/3306/data/binlog.000006 > /tmp/bin.sql

9.2 B 计划:binlog2sql 工具(精确到行级别恢复)

第一步:安装工具

cd /opt
unzip binlog2sql-master.zip
yum install -y python3
pip3 install -r requirements.txt

第二步:分析 binlog 日志

# 解析指定库表的 binlog 操作
python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 \
  -d test1 -t t1 --start-file='binlog.000006'

第三步:恢复误删除(-B 参数生成反向 SQL)

# 解析 DELETE 操作并生成反向 INSERT 语句
python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 \
  -d test1 -t t1 --sql-type=delete --start-file='binlog.000006' -B
# 输出:INSERT INTO `test1`.`t1`(`id`) VALUES (3);

# 解析 UPDATE 操作并生成反向 UPDATE 语句
python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 \
  -d test1 -t t1 --sql-type=update --start-file='binlog.000006' -B
# 输出:UPDATE `test1`.`t1` SET `id`=1 WHERE `id`=10 LIMIT 1;

binlog2sql 的 -B(Back)参数 可以将 DELETE 反转为 INSERT,UPDATE 反转为反向 UPDATE,实现精确到行级别的数据恢复


十、Xtrabackup 物理备份操作

10.1 物理备份 vs 逻辑备份

对比项 mysqldump(逻辑) Xtrabackup(物理)
备份原理 导出 SQL 语句 底层文件 cp
备份速度 慢(需要生成 SQL) ⭐ 快(直接复制文件)
恢复速度 慢(需要逐条执行 SQL) ⭐ 快(直接复制回文件)
适用数据量 < 50G > 50G
精度 支持库/表级别 通常全库级别

10.2 安装

yum install -y percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm

10.3 全量备份

# 创建备份目录
mkdir /data/backup/full -p

# 全量备份
xtrabackup --defaults-file=/etc/my.cnf \
  --host=10.0.0.51 --user=root --password=123456 --port=3306 \
  --backup --target-dir=/data/backup/full

10.4 全量恢复

# 1. 清空数据目录
\rm -rf /data/3306/data/*

# 2. 准备(回滚未提交事务,应用已提交事务)
xtrabackup --prepare --target-dir=/data/backup/full

# 3. 恢复(将备份文件拷回数据目录)
xtrabackup --copy-back --target-dir=/data/backup/full

# 4. 修改权限并启动
chown -R mysql. /data/
systemctl start mysqld

10.5 增量备份

mkdir /data/backup/full -p
mkdir /data/backup/inc -p

# 全量备份(基准备份)
xtrabackup --defaults-file=/etc/my.cnf \
  --host=10.0.0.51 --user=root --password=123456 --port=3306 \
  --backup --parallel=4 --target-dir=/data/backup/full

# 模拟业务数据写入
# CREATE DATABASE pxb; USE pxb; CREATE TABLE t1(id INT); INSERT INTO t1 VALUES(1),(2),(3);

# 第一次增量备份(基于全备)
xtrabackup --defaults-file=/etc/my.cnf \
  --host=10.0.0.51 --user=root --password=123456 --port=3306 \
  --backup --parallel=4 --target-dir=/data/backup/inc \
  --incremental-basedir=/data/backup/full

# 第二次增量备份(基于第一次增量)
xtrabackup --defaults-file=/etc/my.cnf \
  --host=10.0.0.51 --user=root --password=123456 --port=3306 \
  --backup --parallel=4 --target-dir=/data/backup/inc02 \
  --incremental-basedir=/data/backup/inc

10.6 增量恢复

# 1. 合并增量到全备(⭐ 注意中间步骤必须加 --apply-log-only)
# 准备全备(只应用 redo log,不处理 undo log)
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full

# 合并第一次增量
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full \
  --incremental-dir=/data/backup/inc

# 合并第二次增量(最后一步不加 --apply-log-only)
xtrabackup --prepare --target-dir=/data/backup/full \
  --incremental-dir=/data/backup/inc02

# 2. 恢复数据(包含全量 + 所有增量)
xtrabackup --copy-back --target-dir=/data/backup/full

⚠️ --apply-log-only 的作用: 恢复时只识别 redo log 信息,不识别 undo log 信息。合并中间增量时必须加此参数,只有最后一步不加。


十一、MySQL 8.0 Clone Plugin 克隆操作

11.1 克隆作用

  1. 大数据量迁移时,克隆效率最高
  2. 实现云主机数据资源迁移到物理主机(如 RDS → 自建)

11.2 克隆方式

方式 说明 场景
本地克隆 数据备份 同服务器内数据克隆
远程克隆 数据迁移 跨服务器数据迁移

附录:故障排查速查表

问题现象 排查方向 解决方案
数据库无法启动,无错误日志 配置文件 / 初始化 检查 my.cnf 配置语法,确认数据目录已初始化
binlog 恢复报 GTID 冲突 幂等性限制 添加 --skip-gtids 参数
mysqldump 导出大字段失败 max_allowed_packet 不足 添加 --max_allowed_packet=64M
xtrabackup 增量恢复数据丢失 --apply-log-only 遗漏 确保中间合并步骤都加了 --apply-log-only
binlog2sql 无法解析 ROW 格式 binlog_format 配置 确认 binlog_format=ROW
全备恢复后数据不完整 binlog 增量未追加 检查备份文件中的 MASTER_LOG_POS,截取后续 binlog
posted @ 2026-03-30 23:02  gzjwo  阅读(20)  评论(0)    收藏  举报