数据库：故障与备份详解

数据库故障与备份详解

深入理解数据库安全、故障类型与备份恢复策略

第一章数据安全措施

1.1 用户标识和鉴定

用户标识和鉴定（User Identification & Authentication）是数据库安全的第一道防线，用于确认"你是谁"，防止非授权用户进入系统。

1.1.1 基本概念

概念	说明
用户标识（Identification）	用户向系统声明自己身份的过程，如输入用户名
用户鉴定（Authentication）	系统验证用户所声明身份真实性的过程，如验证密码

两者缺一不可：标识说明"我是谁"，鉴定证明"我真的是我"。

1.1.2 鉴定方式分类

① 基于知识的鉴定（Something You Know）

最常见的方式，用户提供只有自己知道的信息：

静态口令：传统用户名+密码，简单但容易被猜测或窃取
动态口令（OTP）：一次性口令，每次登录生成唯一验证码，安全性更高
挑战-响应认证：服务器发送随机挑战值，用户用密钥加密后返回，避免密码在网络传输

② 基于持有物的鉴定（Something You Have）

用户持有特定物品作为凭证：

智能卡（Smart Card）：内置加密芯片，存储用户凭证
硬件令牌（USB Token）：如银行U盾，插入后才能使用
手机验证码：通过短信或APP发送的验证码

③ 基于生物特征的鉴定（Something You Are）

利用用户独有的生理或行为特征：

指纹识别
虹膜/视网膜识别
人脸识别
声纹识别

④ 多因素认证（MFA，Multi-Factor Authentication）

组合两种或以上认证方式，大幅提升安全性：

用户名+密码  →  手机验证码  →  指纹确认
     ↓               ↓              ↓
  知识因素       持有物因素      生物特征因素

1.1.3 数据库中的用户标识机制

以 MySQL 为例，用户标识由 用户名 + 主机名 共同构成：

-- 查看当前所有用户
SELECT user, host FROM mysql.user;

-- 结果示例：
-- 'root'@'localhost'     -- 只允许本机登录的root
-- 'app_user'@'%'         -- 允许任意主机登录的app_user
-- 'admin'@'192.168.1.%'  -- 只允许指定网段登录的admin

-- 创建用户（标识 + 鉴定）
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'SecurePass@2024';

-- 修改密码
ALTER USER 'db_user'@'localhost' IDENTIFIED BY 'NewSecurePass@2024';

-- 删除用户
DROP USER 'db_user'@'localhost';

1.1.4 口令安全策略

良好的口令策略是用户鉴定的核心保障：

-- MySQL 8.0 密码策略配置
-- 查看当前密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 常见策略变量：
-- validate_password.length       最小长度（默认8）
-- validate_password.policy       策略级别：LOW / MEDIUM / STRONG
-- validate_password.mixed_case_count 大小写字母最少数量
-- validate_password.number_count     数字最少数量
-- validate_password.special_char_count 特殊字符最少数量

策略级别	说明
LOW	仅检查长度
MEDIUM	检查长度、数字、大小写、特殊字符
STRONG	在 MEDIUM 基础上，还检查是否在字典文件中出现

1.1.5 账户锁定与失败保护

防止暴力破解，需要设置登录失败上限：

-- MySQL 8.0 账户锁定策略
-- 失败5次后锁定账户1天
CREATE USER 'secure_user'@'%'
  IDENTIFIED BY 'Pass@2024'
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1;

-- 手动解锁账户
ALTER USER 'secure_user'@'%' ACCOUNT UNLOCK;

1.1.6 鉴定流程图

用户输入用户名和密码
        ↓
  数据库查找用户记录
        ↓
   用户是否存在？ ——否——→ 拒绝访问
        ↓是
  账户是否被锁定？ ——是——→ 拒绝访问，提示账户锁定
        ↓否
  密码是否匹配？ ——否——→ 记录失败次数，达上限则锁定账户
        ↓是
  生成会话，允许访问

1.1.7 最佳实践

✅ 强制使用强密码策略（长度≥12，包含大小写+数字+特殊字符）
✅ 启用多因素认证（MFA）
✅ 定期强制更换密码（如每90天）
✅ 设置登录失败锁定机制
✅ 禁用默认账户或修改默认密码
✅ 按最小权限原则创建用户
❌ 避免使用共享账户
❌ 避免在代码中明文写入数据库密码

1.2 存取控制

存取控制（Access Control）是数据库安全的核心机制，解决"你能做什么"的问题——即在用户通过身份鉴定后，限制其只能访问和操作被授权的资源。

1.2.1 存取控制的两个核心要素

要素	说明
权限（Privilege）	用户对数据库对象可执行的操作类型（如SELECT、INSERT、UPDATE、DELETE）
授权（Authorization）	将权限赋予用户，或从用户处收回权限的过程

1.2.2 自主存取控制（DAC）

自主存取控制（Discretionary Access Control，DAC） 是关系型数据库最常用的方式。数据的拥有者可以自主地决定把数据的访问权限授予其他用户。

SQL 标准授权语句：

-- 语法：GRANT 权限 ON 对象 TO 用户 [WITH GRANT OPTION]

-- 授予查询权限
GRANT SELECT ON employees TO 'user1'@'localhost';

-- 授予多种权限
GRANT SELECT, INSERT, UPDATE ON orders TO 'user2'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

-- WITH GRANT OPTION：允许被授权者再将权限转授给他人
GRANT SELECT ON products TO 'manager'@'localhost' WITH GRANT OPTION;

-- 刷新权限（MySQL中生效）
FLUSH PRIVILEGES;

SQL 收回权限语句：

-- 语法：REVOKE 权限 ON 对象 FROM 用户

-- 收回查询权限
REVOKE SELECT ON employees FROM 'user1'@'localhost';

-- 收回所有权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'admin'@'localhost';

DAC 的授权传递示意：

数据库管理员（DBA）
    ↓ GRANT SELECT WITH GRANT OPTION
  用户A（经理）
    ↓ GRANT SELECT（可继续转授）
  用户B（员工）
    ↓ 若A的权限被撤销，B的权限也会级联撤销

⚠️ DAC 的缺点：权限过于灵活，可能因用户误操作或恶意转授导致权限扩散，难以实现统一的安全策略。

1.2.3 强制存取控制（MAC）

强制存取控制（Mandatory Access Control，MAC） 用于更高安全级别需求（如军事、政府系统）。系统为每个主体（用户）和客体（数据）标记安全级别，访问行为由系统统一决策，用户无法更改。

安全级别从低到高：

绝密（Top Secret，TS）
    ↑
 机密（Secret，S）
    ↑
 秘密（Confidential，C）
    ↑
 公开（Unclassified，U）

MAC 的两条核心规则（Bell-LaPadula 模型）：

规则	说明	目的
不上读（No Read Up）	主体只能读取安全级别 ≤ 自身级别的客体	防止低级别用户读取高级别数据
不下写（No Write Down）	主体只能写入安全级别 ≥ 自身级别的客体	防止高级别信息泄露到低级别客体

1.2.4 基于角色的存取控制（RBAC）

RBAC（Role-Based Access Control） 是现代数据库系统中最主流的方式。将权限赋予角色，再将角色赋予用户，实现权限的集中管理。

-- 第一步：创建角色
CREATE ROLE 'read_only_role';
CREATE ROLE 'developer_role';
CREATE ROLE 'dba_role';

-- 第二步：为角色分配权限
GRANT SELECT ON mydb.* TO 'read_only_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'developer_role';
GRANT ALL PRIVILEGES ON *.* TO 'dba_role';

-- 第三步：将角色赋予用户
GRANT 'read_only_role' TO 'analyst'@'%';
GRANT 'developer_role' TO 'dev_user'@'%';
GRANT 'dba_role' TO 'admin_user'@'%';

-- 激活角色（MySQL需要此步）
SET DEFAULT ROLE ALL TO 'analyst'@'%';

-- 查看用户角色
SHOW GRANTS FOR 'analyst'@'%';

RBAC 与直接授权的对比：

对比项	直接授权（DAC）	基于角色（RBAC）
管理复杂度	用户多时难以维护	统一管理角色，简单高效
权限变更	需逐一修改每个用户	修改角色即可批量生效
最小权限原则	难以精确控制	易于实现细粒度控制
适用场景	小型系统	企业级应用

1.2.5 权限检查流程

用户发起操作请求（如 SELECT * FROM orders）
        ↓
  检查用户是否具有该操作权限
        ↓
  ┌──────────────────────────────┐
  │  1. 检查用户级别直接权限     │
  │  2. 检查用户所属角色的权限   │
  │  3. 检查对象级别的权限       │
  └──────────────────────────────┘
        ↓
  有权限？ ——否——→ 返回权限不足错误（Access Denied）
        ↓是
     执行操作，返回结果

1.2.6 最小权限原则

最小权限原则（Principle of Least Privilege）：每个用户/进程只应拥有完成其工作所必需的最小权限集合。

实践建议：

✅ 应用程序账户只授予必要的 SELECT/INSERT 等权限，不授予 DROP、ALTER
✅ 只读报表账户仅授予 SELECT 权限
✅ 不同业务模块使用不同数据库账户
❌ 避免应用直连使用 root 账户

1.3 密码存储和传输

密码的安全存储和传输是数据库安全中极易被忽视但至关重要的环节。不安全的密码处理会导致数据库凭证泄露，进而使整个安全防线崩溃。

1.3.1 密码存储的错误做法

❌ 明文存储（最危险）：将密码以原文形式直接写入数据库

-- 错误示例：明文存储密码
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    password VARCHAR(100)   -- 存储 "123456" 这样的明文
);
INSERT INTO users VALUES (1, 'alice', '123456');  -- 极其危险！

❌ 简单加密存储：使用可逆加密算法（如 MD5 不加盐）

-- 错误示例：无盐 MD5，彩虹表可轻易破解
INSERT INTO users VALUES (1, 'alice', MD5('123456'));
-- MD5('123456') = 'e10adc3949ba59abbe56e057f20f883e'
-- 攻击者可以用彩虹表立即查出原始密码

1.3.2 正确的密码存储方式

① 加盐哈希（Salted Hash）

盐（Salt） 是一段随机生成的字符串，与密码拼接后再做哈希，使得相同密码存储结果不同：

最终存储 = Hash(密码 + 盐值)

用户1: Hash("123456" + "xK9#mP2q") = "a3f8..."  
用户2: Hash("123456" + "7Lz&nQ4w") = "b9c2..."  
-- 即使密码相同，存储结果也完全不同，彩虹表失效

② 推荐的哈希算法

算法	推荐度	说明
bcrypt	✅ 强烈推荐	内置盐值，计算代价可调，专为密码设计
Argon2	✅ 强烈推荐	2015年密码哈希竞赛冠军，内存密集型，抗GPU暴力破解
scrypt	✅ 推荐	内存密集型，抗ASIC硬件攻击
PBKDF2	⚠️ 可用	SHA标准支持，但相对较弱
SHA-256	❌ 不推荐直接用	设计用于文件完整性校验，不适合密码存储
MD5/SHA-1	❌ 禁止使用	已被破解，存在大量彩虹表

③ bcrypt 存储示例（Python 参考）

import bcrypt

# 注册时：生成哈希
password = "user_password_123"
salt = bcrypt.gensalt(rounds=12)  # rounds越大越安全，但越慢
hashed = bcrypt.hashpw(password.encode(), salt)
# 存入数据库：$2b$12$... (包含盐值和哈希)

# 验证时：比对
def verify_password(input_password, stored_hash):
    return bcrypt.checkpw(input_password.encode(), stored_hash)

④ MySQL 内置密码哈希

-- MySQL 8.0 使用更安全的 caching_sha2_password 插件
-- 查看当前认证插件
SELECT user, plugin FROM mysql.user;

-- 创建用户（自动使用安全哈希）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePass@2024';
-- MySQL内部使用SHA-256哈希存储，不建议自己实现密码哈希存储在业务表中

1.3.3 密码传输安全

密码在网络传输过程中同样面临被窃听的风险，需要通过加密传输通道保护。

① 启用数据库 SSL/TLS 加密连接

-- MySQL：检查SSL状态
SHOW VARIABLES LIKE '%ssl%';
SHOW STATUS LIKE 'Ssl_cipher';

-- 强制要求客户端使用SSL连接
ALTER USER 'secure_user'@'%' REQUIRE SSL;

-- 或者要求特定加密强度
ALTER USER 'secure_user'@'%' REQUIRE CIPHER 'AES256-SHA';

② 客户端连接时指定SSL（MySQL 命令行）

# 指定SSL证书连接
mysql -u app_user -p \
  --ssl-ca=/etc/mysql/ssl/ca.pem \
  --ssl-cert=/etc/mysql/ssl/client-cert.pem \
  --ssl-key=/etc/mysql/ssl/client-key.pem \
  -h db.example.com

# 验证当前连接是否使用SSL
mysql> STATUS;
# SSL: Cipher in use is TLS_AES_256_GCM_SHA384

③ 连接字符串中的密码保护

# 错误：硬编码密码在代码中
conn = mysql.connect(host="db", user="root", password="123456")

# 正确：从环境变量或密钥管理服务读取
import os
conn = mysql.connect(
    host=os.environ['DB_HOST'],
    user=os.environ['DB_USER'],
    password=os.environ['DB_PASSWORD'],  # 从环境变量读取
    ssl_ca='/path/to/ca.pem'
)

1.3.4 密码安全全流程总结

用户输入密码
     ↓ HTTPS加密传输
应用服务器接收
     ↓ bcrypt/Argon2 加盐哈希
数据库存储哈希值（永不存储明文）
     ↓ SSL/TLS加密连接
数据库服务器保存

验证时：
用户输入密码 → 应用服务器 → 取出存储哈希 → bcrypt比对 → 通过/拒绝

核心原则：密码只有用户自己知道，系统中任何地方都不应该能看到用户的原始密码！

1.4 视图的保护

视图（View）是数据库安全机制的重要工具，通过为不同用户提供定制化的数据"窗口"，实现数据级别的访问控制，屏蔽敏感字段和行，在不修改底层表结构的前提下实现安全隔离。

1.4.1 视图保护的核心思想

原始表 employees（包含所有字段）
┌──────┬──────────┬────────┬──────────┬───────────────┐
│  id  │  name    │  dept  │  salary  │  id_card_no   │
├──────┼──────────┼────────┼──────────┼───────────────┤
│  1   │  Alice   │  研发  │  30000   │  310xxx...    │
│  2   │  Bob     │  销售  │  18000   │  440xxx...    │
└──────┴──────────┴────────┴──────────┴───────────────┘

       ↓ 为普通员工创建视图（隐藏薪资和身份证）

视图 v_employees_public（公开视图）
┌──────┬──────────┬────────┐
│  id  │  name    │  dept  │
├──────┼──────────┼────────┤
│  1   │  Alice   │  研发  │
│  2   │  Bob     │  销售  │
└──────┴──────────┴────────┘

1.4.2 列级保护：屏蔽敏感字段

-- 原始表
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    department VARCHAR(50),
    salary     DECIMAL(10, 2),    -- 敏感：薪资
    id_card    VARCHAR(20),       -- 敏感：身份证号
    phone      VARCHAR(20)        -- 敏感：手机号
);

-- 创建脱敏视图：普通员工只能看到姓名和部门
CREATE VIEW v_emp_public AS
    SELECT id, name, department
    FROM employees;

-- 创建HR视图：HR能看薪资，但不能看身份证
CREATE VIEW v_emp_hr AS
    SELECT id, name, department, salary,
           CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS phone  -- 手机号脱敏
    FROM employees;

-- 授权：普通员工只能查公开视图
GRANT SELECT ON v_emp_public TO 'staff_role';

-- 授权：HR部门可查HR视图
GRANT SELECT ON v_emp_hr TO 'hr_role';

1.4.3 行级保护：过滤敏感数据行

-- 原始表：所有订单
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20),
    region      VARCHAR(50)
);

-- 行级视图：每个区域经理只能看到自己区域的订单
CREATE VIEW v_orders_east AS
    SELECT * FROM orders
    WHERE region = '东区';

-- 使用函数实现动态行过滤（基于当前登录用户）
CREATE VIEW v_my_orders AS
    SELECT * FROM orders
    WHERE customer_id = (
        SELECT customer_id FROM customers
        WHERE username = CURRENT_USER()
    );

1.4.4 WITH CHECK OPTION 防止数据越权修改

-- 创建带检查约束的视图
CREATE VIEW v_active_users AS
    SELECT * FROM users
    WHERE status = 'active'
    WITH CHECK OPTION;  -- 通过视图修改数据时，必须满足 status = 'active'

-- 测试：以下操作会失败，因为会导致行"逃出"视图范围
UPDATE v_active_users SET status = 'inactive' WHERE id = 1;
-- ERROR: CHECK OPTION failed 'v_active_users'

1.4.5 视图保护的优缺点

优点	缺点
无需修改底层表，灵活性高	复杂查询可能影响性能
实现列级和行级权限控制	视图不是物理隔离，DBA仍可访问原表
对应用透明，无需修改代码	需要维护视图的同步更新
可实现数据脱敏展示	某些复杂视图不支持DML操作

1.4.6 视图保护实战：员工信息系统

-- 场景：员工管理系统中，不同角色看到不同数据

-- 普通员工：只能看自己的信息
CREATE VIEW v_self_info AS
    SELECT id, name, department, phone
    FROM employees
    WHERE name = CURRENT_USER();
GRANT SELECT ON v_self_info TO 'employee_role';

-- 部门经理：能看本部门所有员工（含薪资）
CREATE VIEW v_dept_employees AS
    SELECT e.id, e.name, e.salary
    FROM employees e
    JOIN managers m ON e.department = m.department
    WHERE m.manager_name = CURRENT_USER();
GRANT SELECT ON v_dept_employees TO 'manager_role';

-- HR：能看所有员工完整信息（但身份证脱敏）
CREATE VIEW v_hr_employees AS
    SELECT id, name, department, salary,
           CONCAT('****', RIGHT(id_card, 4)) AS id_card_masked
    FROM employees;
GRANT SELECT ON v_hr_employees TO 'hr_role';

1.5 审计

审计（Audit）是数据库安全的最后一道防线，即使前面的安全措施都做到位，审计也能记录"谁在何时对何数据做了什么操作"，用于事后追责、合规检查和安全分析。

1.5.1 审计的基本概念

数据库审计：对用户访问数据库行为进行监控、记录和分析的机制。它不能阻止违规行为，但能留下不可抵赖的证据。

审计功能通常记录以下信息：

审计要素	说明
Who（谁）	执行操作的用户账号、来源IP
When（何时）	操作的精确时间戳
What（什么）	执行的SQL语句或操作类型
Where（哪里）	操作涉及的数据库、表、行
Result（结果）	操作是否成功，影响的行数

1.5.2 审计的类型

① 用户级审计：记录用户的登录、退出、权限变更等行为

② 语句级审计：记录特定类型的 SQL 语句（DDL、DML、DCL）

③ 对象级审计：记录对特定数据库对象（表、视图）的操作

④ 特权级审计：记录使用特殊权限（如 SUPER、DROP）的操作

1.5.3 MySQL 审计实现

MySQL 8.0 企业版内置 Audit 插件，社区版可使用 audit_log 或第三方 MariaDB Audit Plugin：

-- 安装审计插件（MySQL Enterprise）
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- 查看审计插件状态
SHOW PLUGINS;

-- 查看审计相关配置
SHOW VARIABLES LIKE 'audit_log%';

-- 常用配置：
-- audit_log_file      = /var/log/mysql/audit.log   日志路径
-- audit_log_format    = JSON                        日志格式
-- audit_log_policy    = ALL                         记录策略：ALL/LOGINS/QUERIES/NONE
-- audit_log_rotate_on_size = 104857600             日志轮转大小（100MB）

审计日志 JSON 格式示例：

{
  "timestamp": "2024-01-15T10:30:25.123456Z",
  "id": 12345,
  "class": "general",
  "event": "query",
  "connection_id": 1001,
  "account": {
    "user": "app_user",
    "host": "192.168.1.100"
  },
  "login": {
    "user": "app_user",
    "os": "",
    "ip": "192.168.1.100",
    "proxy": ""
  },
  "query_time": 0.001234,
  "status": 0,
  "query": "SELECT * FROM orders WHERE customer_id = 1001"
}

1.5.4 PostgreSQL 审计（pgaudit 扩展）

-- 安装 pgaudit 扩展
CREATE EXTENSION pgaudit;

-- 配置审计（postgresql.conf）
-- pgaudit.log = 'all'          -- 审计所有操作
-- pgaudit.log = 'read,write'   -- 只审计读写
-- pgaudit.log = 'ddl'          -- 只审计DDL
-- pgaudit.log_client = on      -- 在客户端日志中显示

-- 对象级审计：审计特定表
SET pgaudit.log = 'read';
-- 之后对该会话中的读取操作会被记录

pgaudit 日志示例：

2024-01-15 10:30:25 UTC [12345]: [1-1] user=admin,db=myapp,app=psql,client=127.0.0.1
    AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.users,
    "SELECT * FROM users WHERE id = 100",<not logged>

1.5.5 审计策略设计

审计级别	审计内容	适用场景
最小审计	仅记录失败的登录、权限错误	低安全需求系统
标准审计	登录/退出 + DML操作 + 权限变更	一般业务系统
强化审计	标准审计 + DDL + 特权操作	敏感数据系统
完全审计	所有操作（含SELECT）	金融、医疗等合规场景

⚠️ 注意：完全审计对性能影响较大，在高并发场景需权衡性能与安全性。

1.5.6 审计日志的保护

审计日志本身也需要保护，防止被篡改：

# 审计日志写入只能追加，不能修改
chattr +a /var/log/mysql/audit.log

# 审计日志实时传输至独立安全服务器
# 使用 rsyslog 将日志转发到 SIEM 系统
# /etc/rsyslog.conf:
# *.* @siem-server.example.com:514

# 审计日志定期归档并加密存储（至少保留180天）
tar -czf audit_$(date +%Y%m).tar.gz /var/log/mysql/audit-*.log
gpg --encrypt audit_2024*.tar.gz

1.5.7 常见合规要求

合规标准	审计要求
等级保护（等保2.0）	三级以上系统需开启数据库审计，日志保留6个月
GDPR	对涉及个人数据的访问需完整审计，数据泄露需72小时内报告
PCI DSS	支付系统需审计所有对持卡人数据的访问，日志至少保留1年
SOX	财务数据访问需完整审计记录

1.5.8 最佳实践

✅ 将审计日志存储在独立于数据库的专用服务器上
✅ 审计日志只写不改（WORM，Write Once Read Many）
✅ 开启实时告警：如检测到大量数据导出、异常时间登录等
✅ 定期审查审计日志，发现异常行为
✅ 将审计数据接入 SIEM（安全信息和事件管理）系统
❌ 不要让数据库管理员有权清除审计日志

第二章数据故障类型

2.1 事务本身的可预期故障

可预期故障（Expected Failures）是指在程序逻辑中可以预料到、并通过代码显式处理的故障，通常由业务规则违反或应用逻辑错误引起。

2.1.1 什么是可预期故障

可预期故障的特点：

可判断：系统能够检测到错误条件
可控制：应用程序通过 ROLLBACK 语句显式回滚事务
非突发性：属于正常业务逻辑中的错误分支

2.1.2 常见可预期故障场景

① 违反业务规则

-- 场景：转账业务中余额不足
BEGIN;

-- 检查账户余额
SELECT balance INTO @balance FROM accounts WHERE id = 1;

IF @balance < 1000 THEN
    -- 余额不足，可预期的业务错误，主动回滚
    ROLLBACK;
    -- 返回错误码给应用层
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '余额不足，转账失败';
ELSE
    UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
    COMMIT;
END IF;

② 违反完整性约束

BEGIN;

-- 插入重复主键 → 违反唯一约束 → 可预期错误
INSERT INTO orders (order_id, customer_id, amount) 
VALUES (10001, 1, 500.00);
-- ERROR 1062: Duplicate entry '10001' for key 'PRIMARY'
-- 这是可预期故障，应用层可以捕获并重试或提示用户

ROLLBACK;  -- 事务回滚，数据库恢复到事务开始前的状态

③ 并发冲突导致的死锁检测

-- 数据库检测到死锁后，选择代价最小的事务进行回滚
-- 被回滚的事务会收到错误：
-- ERROR 1213: Deadlock found when trying to get lock; 
--             try restarting transaction

-- 应用层处理死锁：
BEGIN;
-- ... 执行操作 ...
-- 如果捕获到 ERROR 1213，重试整个事务
ROLLBACK;
-- 等待短暂时间后重新开始

④ 用户主动取消操作

-- 用户在转账途中取消
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- ... 用户点击"取消"按钮 ...
ROLLBACK;  -- 应用层调用回滚，恢复余额

2.1.3 可预期故障的处理流程

事务开始（BEGIN）
    ↓
执行SQL操作（Step 1, 2, 3...）
    ↓
检测到错误条件（余额不足/约束违反等）
    ↓
应用程序主动执行 ROLLBACK
    ↓
数据库通过日志撤销事务中所有已执行的操作（UNDO）
    ↓
数据回到事务开始前的一致状态
    ↓
返回错误信息给用户

2.1.4 恢复机制：UNDO 日志

数据库通过 UNDO 日志（撤销日志） 来实现可预期故障的恢复：

操作	UNDO 日志记录内容
INSERT 一行	记录该行的主键，用于删除
UPDATE 一行	记录该行修改前的旧值
DELETE 一行	记录被删除行的完整数据

UNDO日志示例（转账事务）：
[LSN=1001] BEGIN T1
[LSN=1002] UPDATE accounts SET balance=10000 WHERE id=1  (旧值: balance=11000)
[LSN=1003] UPDATE accounts SET balance=9000  WHERE id=2  (旧值: balance=8000)
[LSN=1004] ROLLBACK T1

恢复过程（从后往前读UNDO日志）：
← 撤销 LSN=1003: 将 accounts[id=2].balance 恢复为 8000
← 撤销 LSN=1002: 将 accounts[id=1].balance 恢复为 11000
← 事务T1完全回滚

2.1.5 与不可预期故障的对比

对比项	可预期故障	不可预期故障
发生时机	业务逻辑判断时	随时
触发方式	应用程序主动 ROLLBACK	系统异常中断
恢复方式	显式 ROLLBACK + UNDO	系统自动恢复
数据丢失风险	无（事务未提交）	可能有（已提交未写盘）

2.2 事务本身的不可预期故障

不可预期故障（Unexpected Failures）是指程序逻辑无法预料、突然发生的运行时错误，通常由程序异常、资源耗尽或外部因素引起，事务无法通过正常的 ROLLBACK 处理。

2.2.1 与可预期故障的本质区别

对比项	可预期故障	不可预期故障
是否可预判	✅ 业务逻辑中可判断	❌ 随时突然发生
应用是否有机会处理	✅ 有，主动 ROLLBACK	❌ 无，程序直接崩溃
典型例子	余额不足、约束违反	程序崩溃、内存溢出
恢复方式	应用层显式回滚	数据库系统自动回滚

2.2.2 常见不可预期故障类型

① 运算溢出（Arithmetic Overflow）

-- 程序正在执行事务时，发生整数溢出：
-- Java: java.lang.ArithmeticException: integer overflow
-- C++:  undefined behavior（未定义行为）

-- 表现：程序未执行 ROLLBACK 直接抛出异常退出
-- 结果：事务处于"活跃但未完成"状态

② 程序崩溃（Program Crash）

-- 场景：插入10万条记录的批量事务中，程序在第5万条时崩溃
BEGIN;
INSERT INTO records VALUES (1, ...); -- OK
INSERT INTO records VALUES (2, ...); -- OK
... ...
INSERT INTO records VALUES (50000, ...); -- 程序崩溃 ← 
-- 未执行 COMMIT 或 ROLLBACK
-- 数据库系统需要在重启后自动检测并回滚此事务

③ 内存访问错误（Segmentation Fault）

-- C语言程序操作数据库时发生空指针访问：
-- Segmentation fault (core dumped)

-- 正在执行的事务被强制终止，未能提交或回滚

④ 并发控制超时（Lock Timeout）

-- 等待锁超过设定时间（不可预期的等待时长）
-- MySQL: ERROR 1205 (HY000): Lock wait timeout exceeded;
--        try restarting transaction

-- 此时事务被强制回滚（数据库自动处理）
-- innodb_lock_wait_timeout = 50  (默认 50 秒)

⑤ 连接意外断开（Connection Lost）

# Python 应用程序中：
conn = mysql.connect(...)
conn.begin()
cursor.execute("UPDATE accounts SET balance = balance - 1000 WHERE id = 1")
# 网络突然断开 → 连接丢失 → 事务悬挂
# 数据库检测到连接断开，自动回滚未提交的事务

2.2.3 不可预期故障的恢复原理

数据库无法依赖应用程序来回滚事务，必须由 数据库管理系统（DBMS） 自动处理：

进程崩溃
    ↓
数据库系统检测到连接断开/进程消失
    ↓
查找该连接相关的活跃事务（未 COMMIT 的事务）
    ↓
逐一对这些事务执行 ROLLBACK（读取 UNDO 日志）
    ↓
数据库恢复到事务开始前的一致状态

MySQL InnoDB 的自动回滚机制：

-- 查看当前活跃事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看 INNODB 状态（包含事务回滚信息）
SHOW ENGINE INNODB STATUS\G

-- 关键配置：
-- innodb_rollback_on_timeout = ON  (超时自动回滚整个事务)
-- 默认: OFF（超时只回滚当前语句，事务依然活跃）
SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';

2.2.4 事务状态流转

     BEGIN
       ↓
    活跃（Active）
       ↓ 执行SQL
  部分提交（Partially Committed）
    ↙          ↘
可预期故障    不可预期故障   系统故障
  (业务错误)   (程序崩溃)
    ↓               ↓
应用ROLLBACK   DBMS自动ROLLBACK
    ↓               ↓
  失败（Failed）→ 中止（Aborted）→ 数据恢复
                              ↓（正常情况）
                          提交（Committed）
                              ↓
                         持久（Durable）

2.2.5 防范与监控

-- 监控长时间运行的事务（可能是不可预期故障的受害者）
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
    trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30    -- 超过30秒
ORDER BY duration_sec DESC;

-- 设置合理的锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 30;  -- 30秒

-- 开启超时自动回滚整个事务
SET GLOBAL innodb_rollback_on_timeout = ON;

2.3 系统故障

系统故障（System Failure / Soft Crash）又称软故障，是指整个数据库系统（包括 DBMS、操作系统或硬件）发生的停止运行事件，导致内存中的数据全部丢失，但磁盘上的数据本身完好无损。

2.3.1 系统故障的定义与特点

特点	说明
影响范围	所有正在运行的事务全部中断
内存状态	完全丢失（Buffer Pool中的脏页未写盘）
磁盘状态	物理上完好，但数据可能处于不一致状态
恢复难度	需要 REDO + UNDO 双向恢复，比事务故障复杂

2.3.2 系统故障的常见原因

① 硬件故障（非介质层面）

CPU 故障、主板损坏
电源故障（突然断电、UPS失效）
内存故障（ECC内存校验失败）

② 操作系统崩溃（Kernel Panic / Blue Screen）

Linux:  Kernel panic - not syncing: Oops!
Windows: BSOD (Blue Screen of Death)

③ DBMS 自身进程崩溃

# MySQL 进程崩溃（OOM Killer 触发）
# Linux OOM Killer 在内存不足时强制杀死进程：
# kernel: Out of memory: Kill process 1234 (mysqld) score 900

# 查看被OOM Killer杀死的进程
dmesg | grep -i "oom\|killed"

④ 停电/计划外重启

计划外断电 → 数据库强制关闭
Buffer Pool 中的脏页（Dirty Pages）尚未写入磁盘
→ 磁盘上的数据页版本落后于实际状态

2.3.3 系统故障造成的数据不一致问题

系统故障发生时，存在两类有问题的事务：

时间轴 ─────────────────────────────────────→
                              ↑ 系统故障时间点

T1: [BEGIN]──[操作]──[COMMIT]──结束         ← 已提交但可能未写盘（需REDO）
T2: [BEGIN]──[操作]──[COMMIT]              ← 已提交，数据可能完整
T3:         [BEGIN]──[操作]──              ← 未提交，数据在内存中消失（需UNDO）
T4:                  [BEGIN]──            ← 未提交，数据在内存中消失（需UNDO）

两类问题：

问题类型	说明	解决方法
丢失更新（Lost Update）	已提交的事务（T1）修改已写入磁盘，但相关联的数据页未完全落盘	REDO（重做）：重新执行已提交事务的操作
脏数据残留	未提交的事务（T3/T4）已修改了部分数据页并写入磁盘	UNDO（撤销）：撤销未提交事务的操作

2.3.4 恢复流程（WAL + REDO + UNDO）

数据库使用 WAL（Write-Ahead Logging，预写日志） 原则，确保日志先于数据写入磁盘：

系统重启后的恢复步骤：

第一步：读取 Redo Log（重做日志）
    从最近的检查点开始，向前扫描Redo Log
    找出所有在检查点后开始的事务

第二步：REDO 阶段（重演历史）
    对所有有 COMMIT 记录的事务，重新执行其所有操作
    将数据库恢复到崩溃前最新的提交状态

第三步：UNDO 阶段（撤销未提交）  
    对所有没有 COMMIT 记录的事务（T3、T4），读取 UNDO 日志逐一撤销
    确保未提交的修改不会残留在数据库中

第四步：数据库恢复正常服务

MySQL InnoDB 的崩溃恢复（Crash Recovery）：

# 查看 MySQL 启动过程中的崩溃恢复日志
cat /var/log/mysql/error.log | grep -i "recovery\|redo\|undo"

# 正常恢复日志示例：
# [Note] InnoDB: Starting crash recovery.
# [Note] InnoDB: Doing recovery: scanned up to log sequence number 2345678
# [Note] InnoDB: Starting an apply batch of log records
# [Note] InnoDB: Apply batch completed
# [Note] InnoDB: 3 transaction(s) need to be rolled back

# 关键配置：
# innodb_force_recovery = 0  (0=正常，1-6=逐步降级恢复模式)
SHOW VARIABLES LIKE 'innodb_force_recovery';

2.3.5 系统故障 vs 其他故障对比

故障类型	影响层面	数据丢失	恢复时间	恢复手段
事务故障	单个事务	未提交数据	秒级	UNDO 回滚
系统故障	整个系统	已提交未写盘	分钟级	REDO + UNDO
介质故障	磁盘数据	已提交数据	小时级	从备份恢复

2.3.6 防范措施

# 1. 配置 UPS（不间断电源），防止突然断电
# 2. 使用 RAID 保证电源模块冗余
# 3. 合理设置检查点间隔（减少恢复时间）
# MySQL：
SHOW VARIABLES LIKE 'innodb_log_checkpoint_interval';

# 4. 监控系统资源，防止 OOM
# 配置 MySQL 内存上限（my.cnf）：
# innodb_buffer_pool_size = 4G  (不超过可用内存70%)

# 5. 开启数据库心跳监控，系统崩溃时快速告警

2.4 介质故障

介质故障（Media Failure / Hard Crash）又称硬故障，是最严重的一类故障。指存储数据库的外部存储设备（磁盘/SSD/磁带）发生物理损坏，导致数据库中存储的数据本身被破坏或永久丢失，无法通过日志恢复，必须依赖备份才能恢复。

2.4.1 介质故障的定义与特点

特点	说明
影响范围	磁盘数据物理损坏，已落盘数据也丢失
严重程度	最严重的故障类型
恢复难度	必须依赖备份文件 + Redo Log 恢复
恢复时间	小时级甚至天级（取决于备份策略）
日志是否有效	无效（日志文件可能也在损坏的磁盘上）

2.4.2 介质故障的常见原因

① 磁盘物理损坏（Hard Disk Failure）

磁头碰撞（Head Crash）：磁头接触磁盘表面，划伤磁道
坏道（Bad Sector）：磁盘扇区物理损坏，无法读写
电机故障：磁盘无法转动，数据无法访问

② 固态硬盘（SSD）故障

闪存颗粒寿命耗尽（P/E Cycles耗尽）
主控芯片损坏
固件 BUG 导致数据丢失

③ 存储阵列故障

RAID 控制器故障（尤其 RAID 0 没有冗余，任一磁盘故障即全部丢失）
多块磁盘同时故障（RAID5的两块磁盘同时损坏）
存储网络（SAN/NAS）连接中断

④ 自然灾害与意外

火灾 → 服务器机房受损
洪水 → 磁盘浸水
地震 → 物理设备损坏
误操作 → 磁盘格式化（rm -rf / 或 DROP DATABASE）

⑤ 勒索软件攻击（Ransomware）

攻击者加密磁盘上的所有文件（包括数据库文件和本地备份）
→ 数据无法读取，需要付款解密或从异地备份恢复

2.4.3 介质故障与其他故障的本质区别

故障类型层次：

应用层 ← 事务本身的可预期故障（业务逻辑错误）
   ↓
应用层 ← 事务本身的不可预期故障（程序崩溃）
   ↓
系统层 ← 系统故障（OS崩溃、断电 → 内存丢失，磁盘完好）
   ↓
存储层 ← 介质故障（磁盘物理损坏 → 磁盘数据也丢失）← 最严重

恢复能力：↑（容易）─────────────────────↓（困难）

2.4.4 介质故障的恢复方案

恢复前提：必须有完整的备份 + 备份后的归档日志

恢复步骤：

第一步：确认故障范围
  - 哪些数据文件损坏？
  - 日志文件是否也损坏？
  - 是否有可用备份？

第二步：将数据库下线
  systemctl stop mysql

第三步：从备份恢复数据文件
  # 将全量备份恢复到新磁盘
  cp /backup/full_20240115/ /var/lib/mysql/

第四步：利用归档日志将数据推进到故障前
  # 应用增量备份
  mysqlbinlog binlog.000001 binlog.000002 | mysql

第五步：验证数据一致性，重新上线

MySQL 恢复示例：

# 1. 从 mysqldump 备份恢复
mysql -u root -p target_database < backup_20240115.sql

# 2. 从物理备份（XtraBackup）恢复
# 准备备份（应用 redo log）
xtrabackup --prepare --target-dir=/backup/full/

# 恢复数据目录
xtrabackup --copy-back --target-dir=/backup/full/

# 修复权限
chown -R mysql:mysql /var/lib/mysql/

# 3. 应用二进制日志（将数据恢复到指定时间点）
mysqlbinlog --start-datetime="2024-01-15 08:00:00" \
            --stop-datetime="2024-01-15 16:30:00" \
            /var/log/mysql/mysql-bin.* | mysql -u root -p

# 4. 重启数据库
systemctl start mysql

2.4.5 介质故障预防策略

预防措施	说明	成本
RAID 冗余	RAID1/RAID5/RAID6 提供磁盘冗余	中
定期备份	全量 + 增量 + 日志备份	低
异地备份	将备份存储在不同地理位置	中
主从复制	实时同步数据到从库	中
多数据中心	跨机房部署，避免单点故障	高
云存储备份	使用对象存储（OSS/S3）存备份	低
磁盘健康监控	SMART 检测磁盘预警指标	低

磁盘 SMART 监控示例：

# 安装 smartmontools
apt install smartmontools

# 检查磁盘健康状态
smartctl -a /dev/sda

# 关键指标：
# Reallocated_Sector_Ct > 0  ← 有坏道，需警惕
# Current_Pending_Sector > 0 ← 待处理坏道，立即备份！
# Uncorrectable errors > 0   ← 已有数据损坏，立即替换！

# 定期自动检测
smartctl -t short /dev/sda  # 短测试（1-2分钟）
smartctl -t long /dev/sda   # 长测试（可能数小时）

2.4.6 四类故障总结对比

故障类型	触发原因	影响范围	持久数据是否损坏	恢复手段	恢复时间
可预期故障	业务逻辑违反	单事务	❌ 不损坏	ROLLBACK	秒级
不可预期故障	程序崩溃/超时	单/多事务	❌ 不损坏	DBMS自动UNDO	秒级
系统故障	OS崩溃/断电	所有活跃事务	❌ 磁盘完好	REDO+UNDO	分钟级
介质故障	磁盘损坏	整个数据库	✅ 数据损坏	备份+日志恢复	小时级+

第三章数据备份策略

3.1 静态转储

静态转储（Static Dump / Cold Backup，冷备份）是在数据库处于完全停止运行状态下进行的备份。在转储期间，不允许任何用户连接或操作数据库。

3.1.1 静态转储的工作原理

静态转储时序图：

数据库运行 ──→ 停止数据库服务 ──→ [ 复制所有数据文件 ] ──→ 重启数据库 ──→ 继续运行
                    ↓                       ↓
              所有连接断开           备份数据处于完全一致状态

3.1.2 静态转储的特点

特点	说明
数据一致性	✅ 极高，备份时数据库静止，无并发修改
备份完整性	✅ 文件级完整拷贝，恢复简单
是否需要停机	❌ 需要，影响业务连续性
对性能影响	无（数据库已停止）
恢复方式	直接将文件拷贝回原位，启动数据库即可

3.1.3 静态转储操作示例

MySQL 冷备份：

# 第一步：停止 MySQL 服务
systemctl stop mysql

# 第二步：备份数据文件目录
cp -a /var/lib/mysql/ /backup/mysql_cold_$(date +%Y%m%d)/

# 或者打包压缩
tar -czf /backup/mysql_cold_$(date +%Y%m%d).tar.gz /var/lib/mysql/

# 第三步：重启 MySQL 服务
systemctl start mysql

# 恢复时：
systemctl stop mysql
cp -a /backup/mysql_cold_20240115/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

3.1.4 适用场景

版本升级前备份：数据库升级前的保险措施，允许短暂停机
数据库迁移：将数据从一台服务器迁移到另一台
测试环境备份：测试环境可接受停机
小型/低优先级系统：可接受维护窗口停机的系统

3.1.5 静态转储的局限性

❌ 停机时间不可接受：24×7运行的关键业务系统（如电商、银行）不能接受长时间停机
❌ 备份窗口有限：数据量越大，停机时间越长

3.2 动态转储

动态转储（Dynamic Dump / Hot Backup，热备份）是在数据库正常运行、不停机的情况下进行的备份。转储期间，数据库可以继续提供读写服务。

3.2.1 动态转储的工作原理

动态转储必须结合日志文件（Log File） 才能保证数据一致性，因为转储期间数据库仍在变化：

动态转储时序图：

数据库运行中（用户正常读写）
       ↓
开始转储（记录转储起始时间点T1）
       ↓
[ 复制数据文件 ]  ← 同时，用户的写操作被记录到日志
       ↓
转储完成（记录转储结束时间点T2）
       ↓
转储结果 = 数据文件副本 + [T1, T2]期间的日志

恢复时：
  先恢复数据文件副本 → 再应用[T1, T2]期间的日志 → 数据库一致

3.2.2 动态转储的特点

特点	说明
是否需要停机	✅ 不需要，不影响业务
数据一致性	⚠️ 单独使用可能不一致，必须配合日志
技术复杂度	较高，需要 MVCC 或快照机制支持
备份时间	较长（大型数据库可能需要数小时）
适用场景	24×7运行的生产系统

3.2.3 动态转储技术实现

① 基于快照的热备份（Snapshot）

# Linux LVM 快照方式
# 原理：对数据库文件所在的LVM卷创建快照，快照瞬间完成

# 创建LVM快照（COW - Copy on Write）
lvcreate -L10G -s -n mysql_snap /dev/vg_data/mysql_data

# 挂载快照进行备份
mount /dev/vg_data/mysql_snap /mnt/backup
cp -a /mnt/backup/ /backup/mysql_hot_$(date +%Y%m%d)/

# 卸载并删除快照
umount /mnt/backup
lvremove /dev/vg_data/mysql_snap

② MySQL XtraBackup 热备份（最推荐）

# 安装 XtraBackup
apt install percona-xtrabackup-80

# 热备份（不停库）
xtrabackup --backup \
           --user=root --password='pass' \
           --target-dir=/backup/xtrabackup_$(date +%Y%m%d)

# 备份完成后，准备（应用备份期间产生的日志）
xtrabackup --prepare \
           --target-dir=/backup/xtrabackup_20240115

# 恢复
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/xtrabackup_20240115
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

③ mysqldump 逻辑热备份（适合小型数据库）

# 使用 --single-transaction 保证一致性（基于MVCC快照）
mysqldump --single-transaction \
          --routines \
          --triggers \
          --all-databases \
          -u root -p \
          > /backup/full_$(date +%Y%m%d).sql
# 注意：--single-transaction 仅对 InnoDB 有效
# MyISAM 表需要加 --lock-tables（会锁表）

3.2.4 静态转储 vs 动态转储对比

对比项	静态转储（冷备）	动态转储（热备）
停机要求	❌ 需要停机	✅ 无需停机
数据一致性	✅ 天然一致	⚠️ 需配合日志
实现复杂度	低	高（需MVCC/快照）
对业务影响	大（有停机时间）	小（仅占用I/O资源）
适用场景	可维护窗口停机的系统	生产核心系统
典型工具	文件系统复制	XtraBackup、mysqldump

3.3 完全备份

完全备份（Full Backup）是对数据库所有数据的完整复制，是最基础的备份类型，也是差异备份和增量备份的基础。

3.3.1 完全备份的定义

备份范围：
┌─────────────────────────────────┐
│  数据库所有数据（100%）          │
│  ├── 所有数据表                  │
│  ├── 所有存储过程/函数           │
│  ├── 所有视图                    │
│  ├── 所有触发器                  │
│  └── 数据库配置信息              │
└─────────────────────────────────┘
         ↓ 全部复制
   完全备份文件（最大）

3.3.2 完全备份的特点

特点	说明
备份大小	最大（包含全量数据）
备份时间	最长
恢复时间	最短（直接恢复单个文件即可）
恢复复杂度	最低
存储成本	最高
独立性	完全独立，无需依赖其他备份

3.3.3 完全备份操作示例

mysqldump 逻辑全量备份：

# 备份所有数据库
mysqldump \
  --single-transaction \           # InnoDB热备，保证一致性
  --flush-logs \                   # 切换新binlog文件（便于后续增量备份）
  --master-data=2 \                # 记录binlog文件名和位置
  --all-databases \                # 备份所有库
  --routines \                     # 包含存储过程和函数
  --triggers \                     # 包含触发器
  --events \                       # 包含定时任务
  -u root -p \
  | gzip > /backup/full_$(date +%Y%m%d_%H%M).sql.gz

# 恢复全量备份
gunzip -c /backup/full_20240115_020000.sql.gz | mysql -u root -p

XtraBackup 物理全量备份（大型数据库推荐）：

# 执行全量备份
xtrabackup --backup \
           --user=root \
           --password='pass' \
           --slave-info \          # 记录主从同步信息
           --target-dir=/backup/full_$(date +%Y%m%d)

# 备份完成后的目录结构
# /backup/full_20240115/
# ├── xtrabackup_info          # 备份信息（包含binlog位置）
# ├── xtrabackup_checkpoints   # 备份元数据（LSN范围）
# ├── ibdata1                  # 系统表空间
# ├── mydb/                    # 各数据库目录
# │   ├── table1.ibd           # 表数据文件
# │   └── ...
# └── ib_logfile*              # Redo日志

# 验证备份完整性
xtrabackup --stats --target-dir=/backup/full_20240115

3.3.4 完全备份的时间窗口规划

建议备份策略（以周为周期）：

周日凌晨 02:00 → 全量备份（完全备份）
周一至周六 → 差异或增量备份

备份保留策略（3-2-1原则）：
  保留 3 份备份副本
  存储在 2 种不同介质（本地磁盘 + 对象存储）
  保留 1 份异地备份（不同机房或云端）

3.4 差异备份

差异备份（Differential Backup）是指备份自上次完全备份以来所有发生变化的数据。每次差异备份都以最近一次完全备份为基准。

3.4.1 差异备份的工作原理

时间轴：

周日 ─── 周一 ─── 周二 ─── 周三 ─── 周四
 ↓         ↓         ↓         ↓
全量     差异备份  差异备份  差异备份

差异备份的范围（相对于周日的全量备份）：
  周一差异：仅备份周日→周一 之间变化的数据（小）
  周二差异：备份周日→周二 之间变化的数据（中）
  周三差异：备份周日→周三 之间变化的数据（较大）
  周四差异：备份周日→周四 之间变化的数据（大）

恢复到周四的方法：
  ① 恢复周日全量备份
  ② 恢复周四差异备份
  （只需 2 个备份，不需要逐天恢复）

3.4.2 差异备份的特点

特点	说明
备份范围	相对上次全量备份后的所有变化
备份大小	随时间推移逐渐增大
恢复步骤	仅需：全量备份 + 最新差异备份 = 共 2 步
恢复速度	比增量备份快
存储占用	比增量备份多（每次备份的数据有重叠）

3.4.3 差异备份与完全备份的对比

备份大小示意：

                周日   周一   周二   周三   周四
完全备份         ████   ████   ████   ████   ████   （每次都很大）
差异备份         ████   █      ██     ███    ████   （随时间增大）
增量备份         ████   █      █      █      █      （始终最小）

恢复所需文件数：
完全备份          1      1      1      1      1
差异备份          2      2      2      2      2     （全量+最新差异）
增量备份          2      3      4      5      6     （全量+每天增量）

3.4.4 差异备份实现（MySQL Binlog方案）

# 差异备份通常通过记录 Binlog 位置来实现
# 在全量备份时记录 Binlog 位置
mysqldump --single-transaction --flush-logs --master-data=2 \
          --all-databases -u root -p | gzip > full_20240114.sql.gz
# 查看全量备份的 Binlog 起点
grep "MASTER_LOG_FILE\|MASTER_LOG_POS" full_20240114.sql.gz | head
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154;

# 差异备份（备份从上次全量备份起点到现在的所有 Binlog）
mysqlbinlog \
  --start-datetime="2024-01-14 02:00:00" \  # 全量备份时间点
  /var/log/mysql/mysql-bin.000012 \
  /var/log/mysql/mysql-bin.000013 \
  | gzip > diff_$(date +%Y%m%d).sql.gz

# 恢复：
# 1. 恢复全量备份
gunzip -c full_20240114.sql.gz | mysql -u root -p

# 2. 应用差异备份（当天的 Binlog）
gunzip -c diff_20240115.sql.gz | mysql -u root -p

3.5 增量备份

增量备份（Incremental Backup）只备份自**上次备份（无论是全量还是增量）**以来新增或修改的数据，是备份量最小、速度最快的方式。

3.5.1 增量备份的工作原理

时间轴：

周日   周一   周二   周三   周四
 ↓       ↓       ↓       ↓       ↓
全量   增量1  增量2  增量3  增量4

增量备份的范围（相对于上次备份）：
  周日全量：所有数据
  周一增量1：仅备份周日→周一 之间变化的数据
  周二增量2：仅备份周一→周二 之间变化的数据（在增量1基础上）
  周三增量3：仅备份周二→周三 之间变化的数据
  周四增量4：仅备份周三→周四 之间变化的数据

恢复到周四的方法：
  ① 恢复全量（周日）
  ② 应用增量1（周一）
  ③ 应用增量2（周二）
  ④ 应用增量3（周三）
  ⑤ 应用增量4（周四）
  （需要 5 步，恢复最慢但备份文件最小）

3.5.2 增量备份的特点

特点	说明
备份大小	最小（仅变化的数据）
备份速度	最快
恢复步骤	最多（全量 + 所有中间增量）
恢复复杂度	最高（需按顺序应用）
存储成本	最低
依赖关系	每次增量依赖前一次备份

3.5.3 增量备份的实现

① MySQL Binlog 增量备份

# Binlog 就是天然的增量备份！
# 每个 Binlog 文件记录了该文件期间所有的数据变化

# 查看当前 Binlog 文件列表
SHOW BINARY LOGS;
# +------------------+-----------+
# | Log_name         | File_size |
# +------------------+-----------+
# | mysql-bin.000010 | 107374182 |  ← 周日备份时的文件
# | mysql-bin.000011 |  52428800 |  ← 周一的变化
# | mysql-bin.000012 |  39845992 |  ← 周二的变化

# 每天自动切换 Binlog（方便按天增量备份）
FLUSH BINARY LOGS;

# 增量备份：只需复制新的 Binlog 文件
cp /var/log/mysql/mysql-bin.000011 /backup/incr_20240115/
cp /var/log/mysql/mysql-bin.000012 /backup/incr_20240116/

# 自动增量备份脚本（cron job 每天 23:55 执行）
#!/bin/bash
BACKUP_DIR=/backup/incr_$(date +%Y%m%d)
mkdir -p $BACKUP_DIR
# 获取当前Binlog文件名
CURRENT_BINLOG=$(mysql -u root -p"$MYSQL_PASS" -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
# 将旧的Binlog文件同步到备份目录
rsync -av /var/log/mysql/mysql-bin.0000* $BACKUP_DIR/ --exclude="${CURRENT_BINLOG}"

② XtraBackup 增量物理备份

# 第一步：全量备份（周日）
xtrabackup --backup \
           --target-dir=/backup/base_20240114 \
           --user=root --password='pass'

# 第二步：增量备份1（周一，基于周日全量）
xtrabackup --backup \
           --target-dir=/backup/incr_20240115 \
           --incremental-basedir=/backup/base_20240114 \
           --user=root --password='pass'
# 只备份 LSN > base_20240114 中记录的LSN 的数据页

# 第三步：增量备份2（周二，基于周一增量）
xtrabackup --backup \
           --target-dir=/backup/incr_20240116 \
           --incremental-basedir=/backup/incr_20240115 \
           --user=root --password='pass'

# 恢复（按顺序应用所有备份）：
# 1. 准备全量备份（不提交，等待后续增量）
xtrabackup --prepare --apply-log-only \
           --target-dir=/backup/base_20240114

# 2. 应用增量备份1
xtrabackup --prepare --apply-log-only \
           --target-dir=/backup/base_20240114 \
           --incremental-dir=/backup/incr_20240115

# 3. 应用增量备份2（最后一个增量不加 --apply-log-only）
xtrabackup --prepare \
           --target-dir=/backup/base_20240114 \
           --incremental-dir=/backup/incr_20240116

# 4. 恢复数据
xtrabackup --copy-back --target-dir=/backup/base_20240114
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

3.5.4 三种备份策略综合对比

对比项	完全备份	差异备份	增量备份
单次备份大小	最大（100%）	中（累积增长）	最小（仅变化部分）
单次备份时间	最长	中	最短
恢复文件数量	1 个	2 个	N+1 个
恢复时间	最短	中	最长
恢复复杂度	低	中	高
存储占用	最高	中	最低
推荐组合	周备或月备	日备	小时备或分钟备

3.6 日志文件

日志文件（Log Files）是数据库故障恢复的核心机制。数据库系统通过维护各种日志，在发生故障时能够将数据恢复到一致状态。

3.6.1 数据库日志的分类

数据库日志体系
├── Redo Log（重做日志）  → 保证已提交事务的持久性（Durability）
├── Undo Log（撤销日志）  → 保证未提交事务可回滚（Atomicity）
├── Binary Log（二进制日志）→ 记录所有写操作，用于主从复制和备份
├── Error Log（错误日志）  → 记录启动/关闭/错误信息
├── Slow Query Log（慢查询日志）→ 记录超过阈值的SQL
└── General Log（通用查询日志）→ 记录所有SQL（生产慎用）

3.6.2 Redo Log（重做日志）

Redo Log 是 InnoDB 最重要的日志，实现了 WAL（Write-Ahead Logging）原则：

WAL 原则：先写日志，再改数据页
  事务提交 → 先写 Redo Log → Redo Log 落盘 → 事务成功
           → 数据页稍后异步写盘（减少随机IO）

如果崩溃：
  重启 → 读取 Redo Log → 重演所有已提交的变更 → 数据恢复

-- 查看 Redo Log 配置
SHOW VARIABLES LIKE 'innodb_log%';

-- 关键参数：
-- innodb_log_files_in_group = 2        Redo Log 文件数量（ib_logfile0, ib_logfile1）
-- innodb_log_file_size = 512M          每个文件大小（建议512M-4G）
-- innodb_flush_log_at_trx_commit = 1   刷盘策略：
--   0: 每秒刷盘（性能最好，但可能丢失1秒数据）
--   1: 每次提交立即刷盘（最安全，性能最低）✅ 推荐
--   2: 写OS缓冲区，每秒刷盘（折中方案）

-- Redo Log 文件位置
-- /var/lib/mysql/ib_logfile0
-- /var/lib/mysql/ib_logfile1

3.6.3 Undo Log（撤销日志）

Undo Log 是实现事务回滚和 MVCC（多版本并发控制）的基础：

-- Undo Log 的作用：
-- 1. 事务回滚：通过 Undo Log 的逆操作撤销变更
-- 2. MVCC 快照读：读取数据的历史版本，实现无锁并发

-- 查看 Undo Log 配置
SHOW VARIABLES LIKE 'innodb_undo%';

-- 关键参数：
-- innodb_undo_tablespaces = 2        Undo 表空间数量
-- innodb_undo_log_truncate = ON      自动清理过期 Undo Log（MySQL 8.0+推荐）
-- innodb_max_undo_log_size = 1G      Undo 表空间最大大小

-- 查看 Undo Log 使用情况
SELECT TABLESPACE_NAME, FILE_NAME, TOTAL_EXTENTS, FREE_EXTENTS
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

Undo Log 记录内容示例：

事务 T1 执行：UPDATE accounts SET balance = 5000 WHERE id = 1;  (原值: 10000)

Undo Log 记录：
[TRX_ID=1001] [OP=UPDATE] [TABLE=accounts] [PK=1] [OLD_BALANCE=10000]

如果 T1 回滚：
  读取 Undo Log → 将 accounts[id=1].balance 恢复为 10000

3.6.4 Binary Log（二进制日志）

Binary Log 是 MySQL 的服务器层日志，主从复制和时间点恢复的核心：

-- 查看 Binlog 配置
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_format';

-- Binlog 格式（推荐ROW）：
-- STATEMENT: 记录原始SQL语句（空间小，但某些SQL有不确定性）
-- ROW: 记录行数据变化（最完整，推荐生产使用）✅
-- MIXED: 自动选择STATEMENT或ROW

-- 查看当前 Binlog 文件
SHOW BINARY LOGS;
SHOW MASTER STATUS;

-- 查看 Binlog 内容
SHOW BINLOG EVENTS IN 'mysql-bin.000010' LIMIT 20;

-- 使用 mysqlbinlog 工具解析
mysqlbinlog --no-defaults \
            --start-datetime="2024-01-15 09:00:00" \
            --stop-datetime="2024-01-15 12:00:00" \
            /var/log/mysql/mysql-bin.000010 | head -100

-- 重要配置：
SET GLOBAL expire_logs_days = 7;        -- Binlog 保留 7 天
SET GLOBAL max_binlog_size = 500M;      -- 单个文件上限 500MB（自动轮转）
SET GLOBAL sync_binlog = 1;             -- 每次提交同步到磁盘（防止Binlog丢失）

Binlog 用于时间点恢复（PITR）：

# 场景：误操作 DELETE 了表中的数据，需要恢复到删除前

# 第一步：找到误操作的时间点
mysqlbinlog mysql-bin.000015 | grep -i "DELETE FROM orders" -A 5

# 第二步：恢复全量备份（假设最近一次全量在昨天）
mysql -u root -p < full_20240114.sql

# 第三步：应用 Binlog 到误操作前的时间点
mysqlbinlog --stop-datetime="2024-01-15 10:30:00" \  # 误操作发生的时间前1秒
            /var/log/mysql/mysql-bin.000015 \
            | mysql -u root -p

# 数据恢复完成！

3.6.5 日志文件在故障恢复中的协作关系

数据库故障恢复流程：

                    ┌─────────────────────────────────────┐
                    │            日志文件体系              │
                    │  Redo Log  │  Undo Log  │  Binlog   │
                    └─────────────────────────────────────┘
                         ↓              ↓           ↓
    事务故障         不需要        用于ROLLBACK   不使用
    系统故障       REDO重做+      UNDO撤销未     用于时间
                  重演已提交      提交事务       点恢复
    介质故障       备份+Binlog    不直接用       用于追加
                   做归档恢复                    增量数据

3.6.6 Binlog 自动清理与归档

# 配置 Binlog 自动保留策略
mysql -u root -p -e "SET GLOBAL binlog_expire_logs_seconds = 604800;"  # 7天

# 立即清理过期 Binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# 清理到指定文件（谨慎使用）
PURGE BINARY LOGS TO 'mysql-bin.000010';

# 定期归档 Binlog 到对象存储（cron 脚本）
#!/bin/bash
BINLOG_DIR=/var/log/mysql
ARCHIVE_DIR=s3://my-backup-bucket/binlogs/$(date +%Y%m%d)
aws s3 sync $BINLOG_DIR/ $ARCHIVE_DIR/ --exclude "*" --include "mysql-bin.*"

第四章故障恢复机制

4.1 事务故障恢复

事务故障恢复（Transaction Failure Recovery）是数据库系统针对单个事务的故障处理机制，确保未完成的事务不会对数据库留下部分修改。

4.1.1 恢复目标

目标：将因故障而中断的事务，恢复到事务开始前的数据状态（原子性保证）。

4.1.2 恢复算法（UNDO 算法）

事务故障恢复步骤：

1. 识别需要撤销的事务
   - 从日志尾部向前扫描
   - 找出没有 COMMIT 记录、但有 BEGIN 记录的事务

2. 逆向扫描 UNDO 日志
   - 从日志末尾向前读取（时间逆序）
   - 对属于该事务的每条日志记录执行逆操作

3. 逆操作规则：
   ┌─────────────────────────────────────┐
   │ 原操作          逆操作（UNDO）      │
   │ INSERT(row)  → DELETE(row)          │
   │ DELETE(row)  → INSERT(old_row)      │
   │ UPDATE(row)  → UPDATE(old_value)    │
   └─────────────────────────────────────┘

4. 写入 ABORT/ROLLBACK 日志标记
   - 表示该事务已完全回滚

4.1.3 具体示例

【转账事务执行过程】
日志记录（时间正序）：
  LSN=100: [BEGIN T1]
  LSN=101: [T1, accounts, id=1, balance: 10000→9000]     ← UPDATE 减款
  LSN=102: [T1, accounts, id=2, balance:  8000→9000]     ← UPDATE 加款
  LSN=103: [T1, orders, id=5001, status: NULL→'DONE']    ← INSERT 订单
  -- 故障发生，T1 没有 COMMIT 记录 --

【事务故障恢复过程（从 LSN=103 向前逆序执行）】
  撤销 LSN=103: DELETE FROM orders WHERE id=5001          ← 撤销INSERT
  撤销 LSN=102: UPDATE accounts SET balance=8000 WHERE id=2  ← 恢复原值
  撤销 LSN=101: UPDATE accounts SET balance=10000 WHERE id=1 ← 恢复原值
  写入: [ABORT T1]

【恢复完成】数据库回到 T1 开始前的状态

4.1.4 MySQL InnoDB 事务回滚实现

-- 查看回滚段（Rollback Segment）状态
SHOW ENGINE INNODB STATUS\G
-- 找到以下部分：
-- TRANSACTIONS
-- ---TRANSACTION 123456, ACTIVE 5 sec starting index read
-- ...
-- ROLLING BACK 5 lock struct(s), ...

-- 手动回滚（显式事务）
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- 发现错误
ROLLBACK;  -- InnoDB 读取 UNDO 段，逆序撤销所有已执行的操作

-- 设置保存点（部分回滚）
BEGIN;
UPDATE t1 SET col=1 WHERE id=1;
SAVEPOINT sp1;                   -- 设置保存点
UPDATE t2 SET col=2 WHERE id=2;
ROLLBACK TO SAVEPOINT sp1;       -- 只回滚到 sp1，t1 的修改保留
COMMIT;

4.1.5 事务故障恢复的时间复杂度

影响因素	说明
事务执行时间	事务运行越久，UNDO 日志越多，回滚越慢
操作的数据量	大批量操作（如批量UPDATE）回滚耗时长
UNDO 段大小	如果 UNDO 段满了，大事务可能无法执行

✅ 最佳实践：避免超大事务（如一次性UPDATE百万行），改用分批提交减少单次回滚代价。

4.2 系统故障恢复

系统故障恢复处理的是整个系统崩溃的场景，涉及已提交事务的 REDO（重做）和未提交事务的 UNDO（撤销）。

4.2.1 系统故障恢复的挑战

系统崩溃时，Buffer Pool（内存缓冲池）中的数据全部丢失：

内存（Buffer Pool）                  磁盘（数据文件）
┌─────────────────────────┐          ┌──────────────────────────┐
│  T1脏页（T1已COMMIT）   │   崩溃   │  T1数据页（可能未写盘）  │
│  T2脏页（T2未COMMIT）   │──────→   │  T2数据页（可能部分写盘）│
└─────────────────────────┘          └──────────────────────────┘

两类问题：
  T1: 已 COMMIT 但磁盘数据可能落后 → 需要 REDO
  T2: 未 COMMIT 但部分已写入磁盘   → 需要 UNDO

4.2.2 ARIES 恢复算法（工业标准）

ARIES（Algorithm for Recovery and Isolation Exploiting Semantics） 是现代数据库（InnoDB、SQL Server、DB2）普遍采用的恢复算法：

ARIES 三阶段：

阶段1：分析阶段（Analysis Pass）
──────────────────────────────
  从最近的检查点开始扫描日志
  目标：确定哪些事务在崩溃时是活跃的
  输出：
    - 脏页表（Dirty Page Table）：哪些数据页在崩溃时还未落盘
    - 活跃事务表（Active Transaction Table）：崩溃时哪些事务还在运行

阶段2：REDO 阶段（Redo Pass）
──────────────────────────────
  从分析阶段确定的最早起始点开始
  正向扫描日志（时间顺序），重演所有操作
  包括已提交的事务 和 未提交的事务（全部REDO！）
  保证数据页恢复到崩溃前的"最新状态"

阶段3：UNDO 阶段（Undo Pass）
──────────────────────────────
  对活跃事务表中所有未提交的事务，逆向撤销
  确保数据库没有"半途而废"的事务
  写入 ABORT 日志记录

4.2.3 MySQL InnoDB 崩溃恢复过程

# 模拟 MySQL 崩溃恢复的日志（启动时自动执行）：
cat /var/log/mysql/error.log | grep -A 20 "Starting crash recovery"

# 典型恢复日志：
# 2024-01-15T02:30:15.123456Z 0 [Note] InnoDB: Starting crash recovery.
# 2024-01-15T02:30:15.234567Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 12345678901
# 2024-01-15T02:30:15.345678Z 0 [Note] InnoDB: Starting an apply batch of log records
# 2024-01-15T02:30:16.456789Z 0 [Note] InnoDB: Apply batch completed
# 2024-01-15T02:30:16.567890Z 0 [Note] InnoDB: 5 transaction(s) need to be rolled back
# 2024-01-15T02:30:16.678901Z 0 [Note] InnoDB: Trx id counter is 56789012
# 2024-01-15T02:30:17.123456Z 0 [Note] InnoDB: Roll back of trx with id 56789001 completed
# ... （逐个回滚未提交的事务）
# 2024-01-15T02:30:20.987654Z 0 [Note] InnoDB: Completed initialization of buffer pool
# 2024-01-15T02:30:21.000000Z 0 [Note] /usr/sbin/mysqld: ready for connections.

4.2.4 恢复性能优化：检查点

-- 检查点（Checkpoint）减少恢复时间：
-- 定期将脏页写盘，并记录检查点，下次恢复只需从检查点开始

-- 查看检查点信息
SHOW ENGINE INNODB STATUS\G
-- LOG 部分：
-- Log sequence number          12345678901  ← Redo Log 最新位置
-- Log flushed up to            12345678901  ← 已刷盘位置
-- Pages flushed up to          12345678000  ← 脏页已刷盘的LSN
-- Last checkpoint at           12345677500  ← 最后检查点LSN

-- 调整检查点频率（影响IO和恢复时间的平衡）
SET GLOBAL innodb_io_capacity = 2000;         -- 提高IO能力，加速检查点
SET GLOBAL innodb_max_dirty_pages_pct = 90;   -- 允许更多脏页（减少检查点频率）

4.2.5 系统故障恢复时间估算

恢复时间 ≈ (最后检查点 到 崩溃点 期间的Redo Log量) / 系统IO能力

优化方法：
  ① 提高检查点频率（更小的检查点间隔）→ 减少需要REDO的日志量
  ② 升级到更快的存储（NVMe SSD）→ 提高IO能力
  ③ 合理设置 innodb_log_file_size（过大会增加恢复时间）

4.3 介质故障恢复

介质故障（磁盘损坏）是最严重的故障，恢复过程需要从备份开始，结合归档日志将数据前滚到故障时间点。

4.3.1 恢复所需条件

介质故障恢复的必要材料：
  ✅ 最近一次有效的全量备份文件
  ✅ 从全量备份起点到故障点的所有 Binlog（归档日志）
  ✅ 一台新的存储设备（替换损坏磁盘）
  ✅ 与原系统兼容的 MySQL 版本

4.3.2 完整恢复流程（RPO/RTO 目标）

RPO（恢复点目标）= 最大可接受数据丢失量
RTO（恢复时间目标）= 最大可接受停机时间

介质故障恢复流程：

①  确认故障          ← 磁盘故障报警，确认损坏范围
       ↓
②  评估备份可用性    ← 确认最新备份文件完整性
       ↓
③  准备新环境         ← 新磁盘/服务器，安装MySQL
       ↓
④  恢复全量备份      ← 将全量备份文件还原
       ↓
⑤  应用增量/差异备份  ← 将差异或增量备份叠加
       ↓
⑥  应用归档 Binlog   ← 将数据推进到故障前时间点（PITR）
       ↓
⑦  数据一致性验证    ← 检查行数、业务关键数据
       ↓
⑧  切换业务流量      ← 修改连接配置，恢复服务

4.3.3 MySQL 介质故障恢复实战

# 场景：磁盘损坏，全量备份时间：2024-01-14 02:00，故障时间：2024-01-15 10:30

# === 第一步：准备新环境 ===
# 安装相同版本的 MySQL
apt install mysql-server-8.0

# === 第二步：恢复全量备份 ===
# 方法A：逻辑备份恢复（mysqldump）
mysql -u root -p < full_20240114_0200.sql

# 方法B：物理备份恢复（XtraBackup，速度更快）
systemctl stop mysql
xtrabackup --prepare --target-dir=/restore/full_20240114
xtrabackup --copy-back --target-dir=/restore/full_20240114
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

# === 第三步：应用归档 Binlog（前滚到故障前） ===
# 查看全量备份的 Binlog 起点（从备份文件中获取）
grep "MASTER_LOG_FILE\|MASTER_LOG_POS" full_20240114_0200.sql | head -2
# -- MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=154

# 从全量备份的 Binlog 位置开始，应用到故障前
mysqlbinlog \
  --start-position=154 \
  --stop-datetime="2024-01-15 10:29:59" \   # 故障前1秒
  /archive/mysql-bin.000012 \
  /archive/mysql-bin.000013 \
  /archive/mysql-bin.000014 \
  | mysql -u root -p

# === 第四步：验证数据 ===
mysql -u root -p -e "
SELECT 
    table_schema,
    table_name,
    table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
ORDER BY table_rows DESC
LIMIT 20;"

# === 第五步：切换业务流量 ===
# 更新应用配置中的数据库连接地址
# 重启应用服务

4.3.4 恢复验证清单

# 验证数据库启动无误
systemctl status mysql

# 验证关键业务表数据完整性
mysql -u root -p -e "
  SELECT COUNT(*) as order_count,
         MAX(created_at) as latest_order
  FROM mydb.orders;"

# 验证主从同步状态（如果有从库）
mysql -u root -p -e "SHOW SLAVE STATUS\G"

# 测试关键业务接口
curl -X POST http://localhost:8080/api/orders \
     -H "Content-Type: application/json" \
     -d '{"test": true}'

4.4 检查点机制

检查点（Checkpoint）是数据库定期创建的"一致性快照标记"，告诉系统"在此时间点之前已提交的数据已全部写入磁盘"，从而减少故障恢复时需要重新处理的日志量。

4.4.1 检查点的核心价值

没有检查点的恢复：
  启动时需要从第一条日志开始REDO → 可能需要REDO数天的日志 → 恢复极慢

有检查点的恢复：
  启动时只需从最近的检查点开始REDO → 通常只需几分钟的日志 → 恢复快速

检查点位置在日志文件中的作用：
  
  日志文件: ──[T1]──[T2]──[CKPT]──[T3]──[T4]──[崩溃]
                           ↑
                         检查点
  恢复时：只需从[CKPT]开始，T1和T2已确认写盘，无需再REDO

4.4.2 检查点的工作原理

检查点触发时：

1. 系统记录当前所有活跃事务（未提交）
2. 将 Buffer Pool 中的所有脏页刷写到磁盘
3. 在日志中写入检查点记录：
   [CHECKPOINT: LSN=12345678, Active_TXs={T3, T4}]
4. 更新系统目录中的检查点指针

恢复时从检查点开始：
  读取检查点记录 → 知道哪些事务当时是活跃的
  REDO 从检查点之后开始 → 不需要重播整个历史
  UNDO 针对检查点时活跃的未提交事务

4.4.3 检查点的类型

① 模糊检查点（Fuzzy Checkpoint）— MySQL InnoDB 默认

模糊检查点不要求立即将所有脏页写盘
优点：对业务影响小（不会出现大量I/O抖动）
检查点期间业务照常运行

② 一致性检查点（Consistent Checkpoint）— 更强

要求将所有脏页立即写盘
优点：恢复最快（检查点后只有很少的REDO）
缺点：写入瞬间可能造成I/O抖动

4.4.4 MySQL InnoDB 检查点配置

-- 查看当前检查点相关配置
SHOW VARIABLES LIKE 'innodb_io_capacity%';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct%';
SHOW VARIABLES LIKE 'innodb_flush_method';

-- 相关参数说明：
-- innodb_io_capacity = 200              后台IO能力（磁盘IOPS估值）
-- innodb_io_capacity_max = 2000         峰值IO能力
-- innodb_max_dirty_pages_pct = 90       脏页比例超过这个值触发检查点
-- innodb_max_dirty_pages_pct_lwm = 10   低水位，低于此值停止检查点

-- 查看检查点进度
SHOW ENGINE INNODB STATUS\G
-- 关注 LOG 部分：
-- Log sequence number     = 当前LSN
-- Last checkpoint at      = 上次检查点LSN
-- （两者差值即为需要REDO的日志量）

-- 手动触发检查点（生产慎用）
SET GLOBAL innodb_max_dirty_pages_pct = 0;  -- 触发积极检查点
-- 等待片刻后恢复
SET GLOBAL innodb_max_dirty_pages_pct = 90;

4.4.5 检查点与恢复时间的关系

恢复时间 = REDO 的日志量 / 磁盘 I/O 能力

优化检查点来减少恢复时间：

情况1：检查点太频繁
  ✅ 恢复快     ❌ 对业务 I/O 压力大（频繁刷脏页）

情况2：检查点太少
  ✅ 对业务影响小  ❌ 恢复时间长（要重演很多历史日志）

最佳实践：
  ① 根据业务 RTO 设置合理的检查点间隔
  ② 使用高速 SSD 既能保证业务 I/O，又能加速恢复
  ③ 建议 innodb_log_file_size 配置为 1~4G
     过小 → 检查点过频繁
     过大 → 恢复时间过长

4.4.6 四种故障恢复方式汇总

故障类型	恢复方式	关键技术	无需人工干预
事务故障	UNDO 回滚	Undo Log	✅ 自动
系统故障	REDO+UNDO	Redo Log + Undo Log + 检查点	✅ 自动
介质故障	备份+前滚	备份文件 + Binlog	❌ 需要人工
检查点作用	加速恢复	减少 REDO 范围	✅ 自动触发

第五章实战案例

5.1 MySQL备份方案

5.1.1 生产级 MySQL 备份方案设计

推荐策略（适用于 TB 级以下数据库）：

备份频率：
  全量备份：每周日凌晨 02:00 执行（XtraBackup 物理备份）
  增量备份：每天凌晨 03:00 执行（XtraBackup 增量备份）
  Binlog 归档：实时归档（每产生一个Binlog文件即同步）

存储路径（本地+云存储）：
  /data/backup/
    ├── full/             # 本地全量备份（保留4周）
    │   └── 20240114/
    ├── incr/             # 本地增量备份（保留4周）
    │   ├── 20240115/
    │   └── 20240116/
    └── binlog/           # 归档Binlog（保留30天）
        └── mysql-bin.000001 ...
  
  OSS/S3: my-db-backup/mysql/
    ├── full/     # 全量备份（保留3个月）
    └── binlog/   # Binlog（保留30天）

5.1.2 自动化备份脚本

全量备份脚本（每周执行）：

#!/bin/bash
# mysql_full_backup.sh

set -e
BACKUP_ROOT="/data/backup"
DATE=$(date +%Y%m%d)
FULL_DIR="$BACKUP_ROOT/full/$DATE"
LOG_FILE="$BACKUP_ROOT/logs/full_$DATE.log"
MYSQL_USER="backup_user"
MYSQL_PASS="${MYSQL_BACKUP_PASS}"  # 从环境变量读取
OSS_BUCKET="oss://my-db-backup/mysql/full/"

echo "=== 开始全量备份: $(date) ===" | tee $LOG_FILE

# 执行 XtraBackup 全量备份
xtrabackup --backup \
           --user=$MYSQL_USER \
           --password=$MYSQL_PASS \
           --compress \              # 备份时压缩
           --compress-threads=4 \   # 4线程压缩
           --target-dir=$FULL_DIR \
           2>> $LOG_FILE

echo "本地备份完成: $(date)" | tee -a $LOG_FILE

# 准备备份（应用 Redo Log）
xtrabackup --prepare --target-dir=$FULL_DIR 2>> $LOG_FILE

# 上传到对象存储
ossutil cp -r $FULL_DIR $OSS_BUCKET$DATE/ >> $LOG_FILE 2>&1

# 删除30天前的本地全量备份
find $BACKUP_ROOT/full/ -maxdepth 1 -type d -mtime +30 -exec rm -rf {} \;

echo "=== 全量备份完成: $(date) ===" | tee -a $LOG_FILE

# 发送通知
curl -X POST https://hooks.slack.com/... \
     -d "{\"text\":\"MySQL全量备份完成: $DATE, 大小: $(du -sh $FULL_DIR | cut -f1)\"}"

增量备份脚本（每天执行）：

#!/bin/bash
# mysql_incr_backup.sh

DATE=$(date +%Y%m%d)
YESTERDAY=$(date -d "yesterday" +%Y%m%d)
BACKUP_ROOT="/data/backup"
FULL_DIR="$BACKUP_ROOT/full/$(ls $BACKUP_ROOT/full/ | tail -1)"  # 最近的全量
INCR_BASE="$BACKUP_ROOT/incr/$(ls $BACKUP_ROOT/incr/ 2>/dev/null | tail -1)"
INCR_DIR="$BACKUP_ROOT/incr/$DATE"

# 如果今天是周日，跳过增量（全量日）
if [ $(date +%u) -eq 7 ]; then
    echo "今天是全量备份日，跳过增量备份"
    exit 0
fi

# 第一次增量：基于全量; 后续：基于上一次增量
BASEDIR=${INCR_BASE:-$FULL_DIR}

xtrabackup --backup \
           --user=backup_user \
           --password=$MYSQL_BACKUP_PASS \
           --incremental-basedir=$BASEDIR \
           --target-dir=$INCR_DIR

echo "增量备份完成: $DATE"

5.1.3 专用备份账户设置

-- 创建最小权限的备份账户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass@2024';

-- 授予备份所需的最小权限
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT,
      BACKUP_ADMIN, PROCESS ON *.* TO 'backup_user'@'localhost';

-- MySQL 8.0 XtraBackup 还需要：
GRANT INNODB_REDO_LOG_ARCHIVE ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

5.1.4 备份验证（重要！）

#!/bin/bash
# verify_backup.sh - 每次备份后自动验证

BACKUP_DIR="$1"

echo "验证备份: $BACKUP_DIR"

# 方法1：从备份启动一个测试实例
docker run -d --name mysql_verify \
    -v $BACKUP_DIR:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=test \
    mysql:8.0

sleep 30  # 等待启动和恢复

# 执行简单查询验证
docker exec mysql_verify mysql -u root -ptest \
    -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='mydb';"

# 关闭测试实例
docker stop mysql_verify && docker rm mysql_verify

echo "备份验证完成"

5.2 PostgreSQL备份方案

5.2.1 PostgreSQL 备份工具概览

工具	类型	特点
pg_dump	逻辑备份	单库备份，支持自定义格式
pg_dumpall	逻辑备份	全实例备份，包含用户和全局设置
pg_basebackup	物理备份	基础备份，配合WAL归档实现PITR
Barman	物理备份管理	企业级备份管理工具
pgBackRest	物理备份管理	支持并行备份，大型数据库推荐

5.2.2 pg_dump 逻辑备份

# 备份单个数据库（自定义格式，支持并行恢复）
pg_dump \
  -h localhost \
  -U postgres \
  -d mydb \
  -F c \                    # c=自定义格式（推荐），p=SQL文本，t=tar
  -Z 9 \                    # 压缩级别（0-9）
  -f /backup/mydb_$(date +%Y%m%d).dump

# 恢复（支持并行恢复）
pg_restore \
  -h localhost \
  -U postgres \
  -d mydb_restore \
  -j 4 \                    # 4并行恢复（加速恢复）
  /backup/mydb_20240115.dump

# 备份所有数据库（含全局配置）
pg_dumpall -U postgres | gzip > /backup/pg_all_$(date +%Y%m%d).sql.gz

5.2.3 pg_basebackup 物理热备份

# 配置 postgresql.conf（开启WAL归档）
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /archive/%f'  # 或同步到S3

# 执行物理备份
pg_basebackup \
  -h localhost \
  -U postgres \
  -D /backup/pg_base_$(date +%Y%m%d) \
  -F tar \                  # tar格式打包
  -z \                      # 压缩
  -P \                      # 显示进度
  --wal-method=stream       # 流式传输WAL

# 备份完成后的目录结构：
# /backup/pg_base_20240115/
# ├── base.tar.gz      # 数据目录
# └── pg_wal.tar.gz    # 备份期间的WAL文件

5.2.4 pgBackRest 企业级备份（推荐）

# 安装 pgbackrest
apt install pgbackrest

# 配置 /etc/pgbackrest.conf
[global]
repo1-path=/data/pgbackrest
repo1-s3-bucket=my-backup-bucket
repo1-s3-region=cn-hangzhou
repo1-type=s3
log-level-console=info
start-fast=y              # 快速启动检查点

[mydb]
pg1-path=/var/lib/postgresql/14/main
pg1-user=postgres

# 初始化
pgbackrest --stanza=mydb stanza-create

# 全量备份
pgbackrest --stanza=mydb --type=full backup

# 差异备份
pgbackrest --stanza=mydb --type=diff backup

# 增量备份
pgbackrest --stanza=mydb --type=incr backup

# 查看备份列表
pgbackrest --stanza=mydb info

# 恢复到指定时间点（PITR）
pgbackrest --stanza=mydb \
           --type=time \
           --target="2024-01-15 10:30:00" \
           restore

# 验证备份完整性
pgbackrest --stanza=mydb check

5.3 备份策略设计

5.3.1 根据业务定义 RPO 和 RTO

RPO（Recovery Point Objective）= 最大可接受数据丢失量
RTO（Recovery Time Objective）= 最大可接受业务中断时间

不同业务场景的典型目标：

┌──────────────┬─────────┬─────────┬──────────────────────────────┐
│ 业务类型      │  RPO    │  RTO    │  推荐方案                    │
├──────────────┼─────────┼─────────┼──────────────────────────────┤
│ 金融核心系统  │  0秒    │  <1分钟 │ 主从同步+自动故障切换        │
│ 电商订单系统  │  <1分钟 │  <5分钟 │ 半同步复制+热备库            │
│ 一般业务系统  │  <1小时 │  <1小时 │ 全量+增量+Binlog备份         │
│ 内部管理系统  │  <24小时│  <4小时 │ 每日全量备份                 │
│ 开发测试环境  │  不限   │  不限   │ 按需备份即可                 │
└──────────────┴─────────┴─────────┴──────────────────────────────┘

5.3.2 备份策略层次设计（3-2-1 原则）

3-2-1 黄金法则：
  3 份数据副本  = 原始数据 + 本地备份 + 异地备份
  2 种存储介质  = 本地磁盘阵列 + 对象存储（OSS/S3）
  1 份异地备份  = 跨机房或云端备份

实现方案：
  本地数据库（主库）
    ↓ 主从复制（实时）
  从库（本地热备）
    ↓ XtraBackup（每日物理备份）
  本地NAS存储
    ↓ rsync 同步（每日）
  阿里云OSS/AWS S3（异地冷备）

5.3.3 备份频率矩阵

备份类型	频率	保留时间	存储位置	恢复用途
全量备份	每周一次	4周	本地+OSS	灾难恢复基础
增量备份	每天一次	2周	本地+OSS	快速日常恢复
Binlog归档	实时（产生即归档）	30天	OSS	精确时间点恢复
从库快照	每4小时	7天	云快照	快速切换

5.3.4 成本估算模型

# 备份成本计算示例（阿里云OSS标准存储）
# OSS 标准存储：¥0.12/GB/月

DB_SIZE_GB = 500          # 数据库大小 500GB
FULL_BACKUP_GB = 200      # 全量备份（压缩后约40%）
INCR_BACKUP_GB = 10       # 每日增量（约2%）
BINLOG_PER_DAY_GB = 5     # 每日Binlog

# 存储成本/月
full_backup_cost = FULL_BACKUP_GB * 4 * 0.12      # 4周全量 = ¥96
incr_backup_cost = INCR_BACKUP_GB * 28 * 0.12     # 28天增量 = ¥33.6
binlog_cost = BINLOG_PER_DAY_GB * 30 * 0.12       # 30天Binlog = ¥18

total_monthly_cost = full_backup_cost + incr_backup_cost + binlog_cost
print(f"月存储成本约: ¥{total_monthly_cost:.1f}")  # 约 ¥147.6/月

5.4 灾难恢复演练

灾难恢复演练（DR Drill）是验证备份有效性和恢复能力的唯一方式。备份不演练等于没有备份！

5.4.1 演练的重要性

真实案例教训：
  ❌ 某公司数据库宕机，发现备份文件已损坏3个月（从未验证过）
  ❌ 某团队恢复了备份，但发现重要表被遗漏（备份脚本有bug）
  ❌ 恢复花了预计1小时，实际花了12小时（RTO严重超标）

演练的目标：
  ✅ 验证备份文件的完整性
  ✅ 测量实际的 RTO
  ✅ 发现恢复流程中的问题
  ✅ 培训团队成员熟悉恢复操作

5.4.2 演练计划模板

演练类型：
  ① 定期验证演练（每月）：自动化验证备份完整性
  ② 场景演练（每季度）：模拟特定故障类型，手动执行恢复
  ③ 全面演练（每年）：完整模拟灾难场景，包含团队协作流程

演练环境：
  - 独立的测试集群（不影响生产）
  - 与生产环境相同规格的硬件配置
  - 真实的备份数据（非造假数据）

5.4.3 演练脚本：误删数据恢复

#!/bin/bash
# dr_drill_delete_recovery.sh
# 场景：模拟误删除表数据后恢复到指定时间点

echo "=== 灾难恢复演练开始 ==="
echo "场景：orders表被误删，恢复到删除前5分钟"
START_TIME=$(date +%s)

RECOVER_TARGET_TIME="2024-01-15 10:29:59"  # 误删时间前1分钟
FULL_BACKUP="/backup/full/20240114"
BINLOG_DIR="/archive/binlog"
RESTORE_HOST="db-recover-test.internal"

# --- 第一步：验证备份可用性 ---
echo "[Step 1] 检查备份文件..."
if [ ! -d "$FULL_BACKUP" ]; then
    echo "❌ 错误：全量备份不存在！"
    exit 1
fi
echo "✅ 全量备份存在: $(du -sh $FULL_BACKUP | cut -f1)"

# --- 第二步：在测试库恢复 ---
echo "[Step 2] 恢复全量备份到测试库..."
mysql -h $RESTORE_HOST -u root -p < $FULL_BACKUP/dump.sql
echo "✅ 全量备份恢复完成"

# --- 第三步：应用 Binlog ---
echo "[Step 3] 应用 Binlog 到目标时间点..."
mysqlbinlog \
    --stop-datetime="$RECOVER_TARGET_TIME" \
    $BINLOG_DIR/mysql-bin.* \
    | mysql -h $RESTORE_HOST -u root -p
echo "✅ Binlog 应用完成"

# --- 第四步：验证恢复结果 ---
echo "[Step 4] 验证数据..."
RECOVERED_COUNT=$(mysql -h $RESTORE_HOST -u root -p -sN \
    -e "SELECT COUNT(*) FROM mydb.orders WHERE created_at < '$RECOVER_TARGET_TIME'")
echo "✅ 恢复的订单数量: $RECOVERED_COUNT"

# --- 第五步：计算 RTO ---
END_TIME=$(date +%s)
ELAPSED=$((END_TIME - START_TIME))
echo "=== 演练完成 ==="
echo "实际恢复时间: ${ELAPSED}秒 (约 $((ELAPSED/60)) 分钟)"
echo "目标 RTO: 60分钟"

if [ $ELAPSED -lt 3600 ]; then
    echo "✅ 达到 RTO 目标"
else
    echo "❌ 超过 RTO 目标！需要优化恢复方案"
fi

5.4.4 演练报告模板

# 灾难恢复演练报告

## 基本信息
- 演练日期：2024-01-20
- 演练类型：误删数据恢复演练
- 参与人员：DBA团队 + 运维团队

## 演练结果
| 指标           | 目标     | 实际   | 是否达标 |
| -------------- | -------- | ------ | -------- |
| RPO            | < 5分钟  | 3分钟  | ✅ 达标   |
| RTO            | < 60分钟 | 45分钟 | ✅ 达标   |
| 数据完整性     | 100%     | 100%   | ✅ 达标   |
| 备份文件有效性 | 有效     | 有效   | ✅ 达标   |

## 发现的问题
1. Binlog归档延迟约2分钟（需优化归档频率）
2. 恢复脚本需要人工确认3次，可进一步自动化

## 改进建议
1. 将 Binlog 归档频率从每小时改为每10分钟
2. 优化恢复脚本，减少人工确认步骤
3. 下次演练时增加磁盘故障场景

## 结论
本次演练成功，备份方案有效，满足业务 RPO/RTO 要求。

附录

附录A：备份命令速查

MySQL 备份命令

# ===== mysqldump 逻辑备份 =====

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份单个表
mysqldump -u root -p mydb orders > orders_backup.sql

# 全量备份（推荐选项）
mysqldump -u root -p \
  --single-transaction \   # InnoDB 热备
  --flush-logs \           # 切换Binlog
  --master-data=2 \        # 记录Binlog位置
  --all-databases \        # 所有库
  --routines \             # 存储过程
  --triggers \             # 触发器
  | gzip > full_$(date +%Y%m%d).sql.gz

# 恢复 mysqldump 备份
mysql -u root -p [database] < backup.sql
gunzip -c backup.sql.gz | mysql -u root -p

# ===== XtraBackup 物理备份 =====

# 全量备份
xtrabackup --backup --user=root --password=pass \
           --target-dir=/backup/full_$(date +%Y%m%d)

# 增量备份
xtrabackup --backup --user=root --password=pass \
           --incremental-basedir=/backup/full_20240114 \
           --target-dir=/backup/incr_$(date +%Y%m%d)

# 准备（应用 Redo Log）
xtrabackup --prepare --target-dir=/backup/full_20240114

# 恢复物理备份
xtrabackup --copy-back --target-dir=/backup/full_20240114
chown -R mysql:mysql /var/lib/mysql/

# ===== Binlog 操作 =====

# 查看 Binlog 列表
SHOW BINARY LOGS;
SHOW MASTER STATUS;

# 解析 Binlog
mysqlbinlog /var/log/mysql/mysql-bin.000001

# 按时间范围解析
mysqlbinlog --start-datetime="2024-01-15 09:00:00" \
            --stop-datetime="2024-01-15 10:30:00" \
            /var/log/mysql/mysql-bin.000001 | mysql -u root -p

# 清理旧 Binlog
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

# ===== 账户管理 =====
# 创建备份账户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass@2024';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, BACKUP_ADMIN, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

# ===== 检查与监控 =====
SHOW ENGINE INNODB STATUS\G          -- 查看InnoDB状态（含检查点/日志）
SHOW VARIABLES LIKE 'innodb_log%';  -- 查看Redo Log配置
SELECT * FROM information_schema.INNODB_TRX;  -- 查看活跃事务

PostgreSQL 备份命令

# ===== pg_dump 逻辑备份 =====

# 备份单个库（自定义格式）
pg_dump -U postgres -F c -Z 9 -d mydb -f mydb_$(date +%Y%m%d).dump

# 备份所有库
pg_dumpall -U postgres | gzip > pg_all_$(date +%Y%m%d).sql.gz

# 恢复
pg_restore -U postgres -d mydb -j 4 mydb_20240115.dump

# ===== pg_basebackup 物理备份 =====

pg_basebackup -U postgres -D /backup/pg_base_$(date +%Y%m%d) \
              -F tar -z -P --wal-method=stream

# ===== pgBackRest =====
pgbackrest --stanza=mydb --type=full backup   # 全量
pgbackrest --stanza=mydb --type=incr backup   # 增量
pgbackrest --stanza=mydb info                 # 查看备份列表
pgbackrest --stanza=mydb check                # 验证备份

附录B：常见问题FAQ

Q1: mysqldump 备份时锁表影响业务怎么办？

对 InnoDB 表使用 --single-transaction 参数，基于 MVCC 快照实现无锁热备。对于 MyISAM 表则无法避免锁表，建议业务系统统一使用 InnoDB 引擎就能根本解决问题。

Q2: Binlog 文件太大，磁盘撑不住怎么办？

-- 设置单个Binlog文件大小上限（达到后自动轮转）
SET GLOBAL max_binlog_size = 200M;

-- 设置自动清理策略（保留7天）
SET GLOBAL binlog_expire_logs_seconds = 604800;

-- 将Binlog文件转移到独立挂载点
-- 修改 my.cnf: log_bin=/data/binlog/mysql-bin

Q3: 备份文件应该加密吗？如何加密？

# 强烈建议加密，尤其是上传到云存储的备份
# 方法1：GPG对称加密
gpg --batch --symmetric --passphrase "$BACKUP_PASS" backup.sql.gz

# 方法2：openssl 加密
openssl enc -aes-256-cbc -salt \
    -in backup.sql.gz \
    -out backup.sql.gz.enc \
    -pass env:BACKUP_PASS

# 解密
openssl enc -aes-256-cbc -d \
    -in backup.sql.gz.enc \
    -out backup.sql.gz \
    -pass env:BACKUP_PASS

Q4: 如何监控备份任务是否正常执行？

# 方法1：检查备份文件的修改时间
LAST_BACKUP=$(ls -lt /backup/full/ | head -2 | tail -1 | awk '{print $6, $7, $8}')
echo "最近一次全量备份: $LAST_BACKUP"

# 方法2：将备份结果写入监控表
mysql -u root -p -e "
INSERT INTO backup_monitor (backup_type, backup_time, file_size, status)
VALUES ('full', NOW(), $(du -sb /backup/full/latest | cut -f1), 'SUCCESS');"

# 方法3：Prometheus + Alertmanager
# 监控备份文件最后修改时间，超过24小时未更新则告警

Q5: 恢复慢，有什么加速方法？

场景	加速方法
mysqldump恢复慢	先禁用索引 `ALTER TABLE t DISABLE KEYS`，导入后重建
XtraBackup恢复慢	使用 `--parallel=8` 多线程复制文件
Binlog应用慢	使用 `mysqlbinlog` 并行（MySQL 8.0支持）
pg_restore慢	使用 `-j 8` 并行恢复

Q6: 为什么 innodb_flush_log_at_trx_commit 推荐设置为 1？

innodb_flush_log_at_trx_commit 参数控制 Redo Log 刷盘策略：

0: 每秒刷盘 → 最高性能，但崩溃可能丢失最多1秒的数据
1: 每次提交立即刷盘 → 最安全，事务提交成功即意味着数据不丢 ✅
2: 每次提交写OS缓冲，每秒刷盘 → 折中

生产建议：
  金融/关键业务 → 设置为 1（数据不允许丢失）
  高并发写入场景 → 可考虑设置为 2（配合 sync_binlog=1）
  测试/开发环境 → 可设置为 0（追求性能）

Q7: 主从复制和备份有什么区别？

对比项	主从复制	备份（XtraBackup/mysqldump）
目的	读写分离/高可用	灾难恢复/数据归档
数据延迟	秒级（异步）或零（同步）	小时级（备份窗口）
误操作防护	❌ 无（误操作会同步到从库）	✅ 有（可恢复到误操作前）
存储成本	高（需要完整的从库服务器）	低（只需存储备份文件）
实际应用	两者都需要配合使用	互补关系，不能互相替代

注：本文详细讲解数据库安全、故障类型与备份恢复的核心概念和实战应用。

转载自CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/clearhenry/article/details/158392804

数据库：故障与备份详解