程序员求职经验分享与学习资料整理平台

网站首页 > 文章精选 正文

mariadb数据库备份恢复第二弹-使用binlog日志恢复数据

balukai 2025-03-30 14:22:28 文章精选 5 ℃

从 MariaDB 的二进制日志（binlog）中恢复数据是处理误删除、误更新或数据库损坏等问题的关键方法。

启用binlog配置

1. 修改配置文件

MariaDB 的配置文件通常位于以下路径（根据系统不同可能有所差异）：

  • Linux: /etc/my.cnf, /etc/mysql/my.cnf 或 /etc/my.cnf.d/server.cnf
  • Windows: C:\Program Files\MariaDB\data\my.ini

添加或修改以下配置项：

[mysqld]
# 启用 binlog
log_bin = /var/lib/mysql/mysql-bin.log  # 指定 binlog 文件名和存储路径（Linux）
# log_bin = mysql-bin  # Windows 默认路径

# 指定 binlog 格式（可选：ROW, STATEMENT, MIXED）
binlog_format = ROW

# 设置 server_id（主从复制必需，单机可设为 1）
server_id = 1

# 其他常用配置
expire_logs_days = 7    # 自动清理 7 天前的 binlog
max_binlog_size = 100M  # 单个 binlog 文件最大 100MB

参数说明：

  • log_bin: 必须启用，指定 binlog 文件名和路径（如不指定路径，默认在数据目录）。
  • binlog_format:
  • ROW: 记录行级变更（推荐，数据恢复更安全）。
  • STATEMENT: 记录 SQL 语句（可能因函数或触发器导致不一致）。
  • MIXED: 混合模式。
  • server_id: 唯一标识当前实例（主从复制必需）。

2. 重启 MariaDB 服务

  • Linux:
sudo systemctl restart mariadb
  • Windows: 通过服务管理器重启 "MariaDB" 服务。

3. 验证配置

检查 binlog 是否启用：

SHOW VARIABLES LIKE 'log_bin';

输出应为 ON：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin           | ON      |
+---------------+-------+

查看当前 binlog 文件：

SHOW BINARY LOGS;

输出示例：

+------------------+-----------+
| Log_name           | File_size    |
+------------------+-----------+
| mysql-bin.000001 |       123   |
+------------------+-----------+

4. 测试 binlog 是否正常工作

执行一条测试 SQL（如创建表或插入数据）后，检查 binlog 是否记录：

CREATE DATABASE binlog_test;
USE binlog_test;
CREATE TABLE test (id INT);
INSERT INTO test VALUES (1);

查看 binlog 内容：

mysqlbinlog /var/lib/mysql/mysql-bin.000001

（根据实际路径替换文件名）

5. 注意事项

  • 权限问题:
  • 确保 MariaDB 用户对 binlog 目录有写入权限（Linux 默认路径 /var/lib/mysql 属主应为 mysql:mysql）。
  • 多实例冲突:
  • 如果一台主机运行多个 MariaDB 实例，需为每个实例配置不同的 server_id 和 binlog 路径。
  • 磁盘空间:
  • 监控 binlog 文件大小，避免磁盘占满导致服务崩溃。
  • 主从复制:
  • 如果用于主从复制，需同时配置 log_slave_updates（从库记录中继日志到自身 binlog）。

示例完整配置（Linux）

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Binlog 配置
log_bin = /var/lib/mysql/mysql-bin.log
binlog_format = ROW
server_id = 1
expire_logs_days = 7
max_binlog_size = 100M

# 其他优化配置（可选）
innodb_buffer_pool_size = 1G
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

使用binlog恢复数据

1. 确保 binlog 已启用

在恢复前需确认 MariaDB 启用了二进制日志功能：

SHOW VARIABLES LIKE 'log_bin';
  • 如果结果为 ON，表示已启用。
  • 如果为 OFF，则无法通过 binlog 恢复（需提前配置 log-bin 参数）。

2. 定位需要恢复的 binlog 文件

查看所有 binlog 文件列表：

SHOW BINARY LOGS;

输出示例：

+------------------+-----------+
| Log_name           | File_size    |
+------------------+-----------+
| mysql-bin.000001 |    1048576|
| mysql-bin.000002 |     123456|
+------------------+-----------+

确定误操作的时间或位置：

  • 通过时间点恢复：记录误操作发生的具体时间（如 2024-01-01 12:00:00）。
  • 通过位置恢复：记录误操作在 binlog 中的起始和结束位置（需提前监控或分析 binlog）。

3. 导出 binlog 为 SQL 文件

使用 mysqlbinlog 工具将 binlog 转换为可执行的 SQL 文件。

常用命令：

  • 按时间范围导出：
mysqlbinlog \
    --start-datetime="2024-01-01 11:00:00" \
    --stop-datetime="2024-01-01 12:30:00" \
    mysql-bin.000002 > recovery.sql
  • 按位置范围导出：
mysqlbinlog \
    --start-position=1234 \
    --stop-position=5678 \
    mysql-bin.000002 > recovery.sql
  • 忽略某些数据库（如系统库）：
mysqlbinlog --exclude-databases=mysql,sys \
    mysql-bin.000002 > recovery.sql

4. 执行 SQL 文件恢复数据

mysql -u root -p < recovery.sql
  • 执行前建议检查 recovery.sql 内容，确保只包含需要的操作（如跳过误删除语句）。

5. 高级恢复场景

仅恢复某个表：

  • 导出 binlog 到 SQL 文件。
  • 使用文本工具（如 sed 或编辑器）过滤出涉及目标表的 SQL 语句：
grep -iE 'INSERT INTO `table_name`|UPDATE `table_name`|DELETE FROM `table_name`' recovery.sql > table_recovery.sql
  • 执行过滤后的 SQL：
mysql -u root -p my_database < table_recovery.sql

基于 GTID 恢复（如果启用了 GTID）：

mysqlbinlog --include-gtids='1-10' mysql-bin.000002 | mysql -u root -p

6. 注意事项

  • 停止写入：
  • 恢复前暂停数据库写入，避免新数据覆盖 binlog。
  • 备份当前状态：
  • 导出当前数据和 binlog 文件，防止恢复失败导致二次损坏。
  • 验证 SQL 内容：
  • 使用 mysqlbinlog 的 -v（详细模式）或 --base64-output=DECODE-ROWS 查看具体操作。
  • 版本兼容性：
  • 确保 mysqlbinlog 工具版本与 MariaDB 服务端版本一致。
  • 权限要求：
  • 执行恢复的用户需有 REPLICATION CLIENT 和 SUPER 权限。

7. 示例完整流程

  • 模拟误删除：
DELETE FROM users WHERE id = 100;
  • 定位 binlog 和时间点：
SHOW BINARY LOGS;

-- 假设误操作发生在 mysql-bin.000002，时间在 2024-01-01 12:00:00 左右

  • 导出恢复 SQL：
mysqlbinlog \
    --start-datetime="2024-01-01 11:58:00" \
    --stop-datetime="2024-01-01 12:02:00" \
    mysql-bin.000002 > recovery.sql
  • 手动编辑 SQL 文件：
  • 删除误操作的 DELETE 语句，保留其他有效操作。
  • 执行恢复：
mysql -u root -p my_database < recovery.sql

8. 预防措施

  • 定期备份：结合全量备份（如 mysqldump）和 binlog 实现增量恢复。
  • 监控 binlog：设置 expire_logs_days 清理旧日志，避免磁盘占满。
  • 权限隔离：限制高危 SQL 的执行权限（如 DELETE、DROP）。

Tags：

猜你喜欢

最近发表
标签列表