印刻数据库

外观

MariaDB 单表恢复 ​

单表恢复概述 ​

单表恢复是指只恢复数据库中的某个特定表，而不是整个数据库的过程。单表恢复可以快速恢复误操作的表，减少对其他表的影响，提高恢复效率。

单表恢复类型 ​

恢复类型适用场景恢复速度数据完整性工具
逻辑单表恢复小型表、跨版本恢复、表结构变更慢高mysqldump、mydumper、mysqlbinlog
物理单表恢复大型表、快速恢复需求、表结构不变快高mariabackup、xtrabackup、表空间传输

逻辑单表恢复 ​

1. 使用 mysqldump 进行单表恢复 ​

恢复准备 ​

bash
# 确保目标表不存在或已备份
mysql -u root -p -e "DROP TABLE IF EXISTS mydatabase.mytable;"
# 或备份现有表
mysqldump -u root -p mydatabase mytable > /backup/mariadb/mytable_bak_$(date +%Y%m%d_%H%M%S).sql

从全量备份中提取单表 ​

bash
# 从全量备份中提取单表结构和数据
mysqldump -u root -p mydatabase mytable < /backup/mariadb/full/mydatabase_full_20231227_120000.sql

# 或使用 grep 从备份文件中提取单表
# 提取表结构
grep -A 100 "CREATE TABLE `mytable`" /backup/mariadb/full/mydatabase_full_20231227_120000.sql > /tmp/mytable_schema.sql
# 提取表数据
grep -A 5 "INSERT INTO `mytable`" /backup/mariadb/full/mydatabase_full_20231227_120000.sql > /tmp/mytable_data.sql

执行单表恢复 ​

bash
# 恢复表结构
mysql -u root -p mydatabase < /tmp/mytable_schema.sql

# 恢复表数据
mysql -u root -p mydatabase < /tmp/mytable_data.sql

2. 使用 mydumper/myloader 进行单表恢复 ​

恢复准备 ​

bash
# 确保目标表不存在或已备份
mysql -u root -p -e "DROP TABLE IF EXISTS mydatabase.mytable;"

执行单表恢复 ​

bash
# 使用 myloader 恢复单表
myloader \
  --user=root \
  --password=password \
  --database=mydatabase \
  --directory=/backup/mariadb/mydumper_20231227_120000 \
  --tables=mytable \
  --overwrite-tables \
  --verbose=3

3. 使用 mysqlbinlog 进行单表时间点恢复 ​

恢复准备 ​

bash
# 查找误操作在 Binlog 中的位置
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/binlog.000001 | grep -A 10 -B 10 "DELETE FROM mydatabase.mytable"

# 提取单表的 Binlog 事件
mysqlbinlog --start-position=123456 \
  --stop-position=789012 \
  --database=mydatabase \
  --table=mytable \
  /var/lib/mysql/binlog.000001 \
  > /tmp/mytable_binlog.sql

执行单表恢复 ​

bash
# 恢复单表到误操作之前的状态
mysql -u root -p mydatabase < /tmp/mytable_binlog.sql

物理单表恢复 ​

1. 使用表空间传输进行单表恢复 ​

恢复准备 ​

bash
# 确保目标表存在且结构相同
mysql -u root -p -e "SHOW CREATE TABLE mydatabase.mytable;"

# 确保表空间传输功能已启用
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_file_per_table';"  # 必须为 ON

执行单表恢复 ​

bash
# 1. 在源数据库中导出表空间
mysql -u root -p -e "ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;"

# 2. 从备份中复制表空间文件
cp /backup/mariadb/full/latest/mydatabase/mytable.ibd /var/lib/mysql/mydatabase/
cp /backup/mariadb/full/latest/mydatabase/mytable.cfg /var/lib/mysql/mydatabase/  # 如果存在

# 3. 设置正确的权限
chown -R mysql:mysql /var/lib/mysql/mydatabase/mytable.ibd
chmod 660 /var/lib/mysql/mydatabase/mytable.ibd

# 4. 在目标数据库中导入表空间
mysql -u root -p -e "ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;"

2. 使用 mariabackup 进行单表恢复 ​

恢复准备 ​

bash
# 创建临时恢复目录
mkdir -p /restore/temp

# 从全量备份中恢复单表的数据文件
mariabackup --copy-back \
  --target-dir=/backup/mariadb/full/latest \
  --datadir=/restore/temp \
  --parallel=4

# 确保临时目录权限正确
chown -R mysql:mysql /restore/temp

执行单表恢复 ​

bash
# 1. 在目标数据库中丢弃表空间
mysql -u root -p -e "ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;"

# 2. 复制单表的数据文件
cp /restore/temp/mydatabase/mytable.ibd /var/lib/mysql/mydatabase/
cp /restore/temp/mydatabase/mytable.frm /var/lib/mysql/mydatabase/  # 对于 MariaDB 10.2 及以下版本

# 3. 设置正确的权限
chown -R mysql:mysql /var/lib/mysql/mydatabase/mytable.ibd
chmod 660 /var/lib/mysql/mydatabase/mytable.ibd

# 4. 导入表空间
mysql -u root -p -e "ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;"

不同场景下的单表恢复 ​

场景1：误删除表恢复 ​

  1. 从全量备份中恢复表结构和数据：

    bash
    # 提取表结构
mysqldump -u root -p --no-data mydatabase mytable < /backup/mariadb/full/mydatabase_full_20231227_120000.sql > /tmp/mytable_schema.sql

# 提取表数据
mysqldump -u root -p --no-create-info mydatabase mytable < /backup/mariadb/full/mydatabase_full_20231227_120000.sql > /tmp/mytable_data.sql

# 恢复表
mysql -u root -p mydatabase < /tmp/mytable_schema.sql
mysql -u root -p mydatabase < /tmp/mytable_data.sql

  2. 使用 mydumper/myloader 恢复：

    bash
    myloader --user=root --password=password --database=mydatabase --directory=/backup/mariadb/mydumper_20231227_120000 --tables=mytable --verbose=3

场景2：误更新表数据恢复 ​

  1. 使用 Binlog 恢复到误操作之前的状态：

    bash
    # 查找误操作的时间点
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/binlog.000001 | grep -i "update mydatabase.mytable"

# 恢复到误操作之前的时间点
mysqlbinlog --stop-datetime="2023-12-28 10:30:00" --database=mydatabase --table=mytable /var/lib/mysql/binlog.000001 | mysql -u root -p

  2. 使用备份恢复并应用 Binlog：

    bash
    # 从备份恢复表
mysqldump -u root -p mydatabase mytable < /backup/mariadb/full/mydatabase_full_20231227_120000.sql

# 应用 Binlog 到误操作之前
mysqlbinlog --start-position=123456 --stop-datetime="2023-12-28 10:30:00" --database=mydatabase --table=mytable /var/lib/mysql/binlog.000001 | mysql -u root -p

场景3：跨数据库实例单表恢复 ​

  1. 逻辑恢复方法：

    bash
    # 从源实例导出表
mysqldump -h source_host -u root -p mydatabase mytable > /tmp/mytable.sql

# 导入到目标实例
mysql -h target_host -u root -p target_database < /tmp/mytable.sql

  2. 物理恢复方法（表空间传输）：

    bash
    # 在源实例中
mysql -h source_host -u root -p -e "ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;"
scp /var/lib/mysql/mydatabase/mytable.ibd target_host:/var/lib/mysql/target_database/

# 在目标实例中
mysql -h target_host -u root -p -e "ALTER TABLE target_database.mytable DISCARD TABLESPACE;"
chown -R mysql:mysql /var/lib/mysql/target_database/mytable.ibd
mysql -h target_host -u root -p -e "ALTER TABLE target_database.mytable IMPORT TABLESPACE;"

单表恢复性能优化 ​

1. 逻辑单表恢复优化 ​

  • 使用 mydumper/myloader 代替 mysqldump：mydumper/myloader 支持并行导出和导入，速度更快
  • 增加 myloader 的 --threads 参数：根据 CPU 核心数调整并行线程数
  • 关闭二进制日志和慢查询日志：恢复过程中临时关闭，减少写入开销
  • 使用 --disable-keys：恢复 MyISAM 表时禁用索引，恢复后重建
    bash
    mysql -u root -p -e "SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SOURCE /tmp/mytable.sql; SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1;"

2. 物理单表恢复优化 ​

  • 使用 SSD 存储：SSD 的 I/O 性能远高于 HDD，可大幅提升恢复速度
  • 减少表空间碎片：恢复前优化表空间，减少碎片
    bash
    mysql -u root -p -e "OPTIMIZE TABLE mydatabase.mytable;"
  • 调整 innodb_buffer_pool_size：增加缓冲池大小，提高恢复速度
  • 使用并行复制：对于大型表，可以考虑使用并行复制工具

常见问题（FAQ） ​

Q1: 逻辑单表恢复速度太慢，如何优化？ ​

A: 可以尝试以下优化措施：

  • 使用 mydumper/myloader 代替 mysqldump，支持并行恢复
  • 增加 myloader 的 --threads 参数，提高并行度
  • 关闭二进制日志和慢查询日志，减少写入开销
  • 调整 innodb_buffer_pool_size，增加缓冲池大小
  • 使用 --disable-keys 和 --foreign-key-checks=0 参数

Q2: 物理单表恢复失败，提示 "Tablespace does not match the tablespace ID in the data file"？ ​

A: 这是因为目标表的表空间 ID 与备份文件中的表空间 ID 不匹配。

解决方案：

  • 确保目标表的结构与备份中的表结构完全相同
  • 重新创建目标表，确保表空间 ID 一致
  • 使用逻辑恢复方法代替物理恢复

Q3: 如何从压缩的备份文件中提取单表？ ​

A: 对于 gzip 压缩的备份文件，可以使用以下命令：

bash
# 提取表结构
gunzip -c /backup/mariadb/mydatabase_full_20231227_120000.sql.gz | grep -A 100 "CREATE TABLE `mytable`" > /tmp/mytable_schema.sql

# 提取表数据
gunzip -c /backup/mariadb/mydatabase_full_20231227_120000.sql.gz | grep -A 5 "INSERT INTO `mytable`" > /tmp/mytable_data.sql

Q4: 单表恢复后，表中的数据与预期不符？ ​

A: 可能是因为：

  • 备份文件中的数据不完整
  • 恢复过程中出现错误
  • Binlog 应用不完整
  • 表结构不匹配

解决方案：

  1. 验证备份文件的完整性
  2. 检查恢复过程中的日志，查找错误信息
  3. 确认表结构与备份中的表结构一致
  4. 重新执行恢复操作，确保所有步骤正确

Q5: 如何恢复被截断（TRUNCATE）的表？ ​

A: TRUNCATE 操作无法通过 Binlog 直接恢复，因为 TRUNCATE 操作在 Binlog 中记录为 DDL 语句，而不是 DML 语句。

解决方案：

  1. 从全量备份中恢复表：
    bash
    mysqldump -u root -p mydatabase mytable < /backup/mariadb/full/mydatabase_full_20231227_120000.sql
  2. 然后应用 Binlog 到 TRUNCATE 操作之前的时间点：
    bash
    mysqlbinlog --start-position=123456 --stop-datetime="2023-12-28 10:30:00" --database=mydatabase --table=mytable /var/lib/mysql/binlog.000001 | mysql -u root -p

最佳实践 ​

  1. 定期备份：确保有最新的全量备份和增量备份，便于单表恢复
  2. 启用 Binlog：Binlog 是时间点恢复的基础，必须启用
  3. 使用 innodb_file_per_table：启用独立表空间，便于物理单表恢复
  4. 定期测试单表恢复：至少每季度进行一次单表恢复测试，确保备份可用
  5. 记录表结构变更：记录表结构的变更历史，便于跨版本恢复
  6. 使用自动化脚本：编写单表恢复脚本，减少手动操作错误
  7. 监控恢复过程：恢复过程中监控系统资源使用和日志输出
  8. 验证恢复结果：恢复完成后验证表数据的完整性和一致性
  9. 文档化恢复流程：将单表恢复流程纳入运维文档，便于团队成员参考
  10. 建立应急响应机制：建立误操作应急响应机制，明确单表恢复的流程和责任人

通过遵循以上步骤和最佳实践，可以确保 MariaDB 单表恢复的顺利进行，快速恢复误操作的表，减少对其他表的影响，提高恢复效率。