免费个人运维知识库，欢迎您的订阅：literator_ray.flowus.cn

一、前提条件：二进制日志配置

作用

• 用于数据库恢复（时间点恢复）

• ​建议​：单独存储路径，与数据文件分离

启用步骤

效果# 编辑配置文件
vim /etc/my.cnf

# 添加内容
[mysqld]
log_bin=/path/to/binlogs/bin-log  # 自定义路径和文件名前缀

效果

• 日志文件：/path/to/binlogs/bin-log.000001

• 索引文件：/path/to/binlogs/bin-log.index

二、mysqldump 备份恢复

特性​

• 逻辑备份工具，支持所有存储引擎

• 支持温备（InnoDB 可热备）

• 支持完全/部分备份，增量备份需结合 binlog

备份与还原场景

场景​	​备份命令​	​还原命令​	​关键说明​
单库或多表（不备份库定义）	mysqldump -uroot -p db1 [tb1] > dump.sql	mysql db1 < dump.sql	​库必须已存在​
单库或多库（含库定义）	mysqldump -uroot -p -B db1 [db2] > dump.sql	mysql < dump.sql	自动创建库
所有数据库（不含系统库）	mysqldump -uroot -p -A > alldb.sql	mysql < alldb.sql	排除 performance_schema/information_schema

还原优化操作

-- 临时关闭二进制日志记录（避免记录恢复操作）
SET sql_log_bin = OFF;

-- 清除历史 binlog（可选）
RESET MASTER;

-- 导入备份文件
SOURCE /path/to/alldb.sql;

-- 重新开启二进制日志
SET sql_log_bin = ON;

三、mysqldump关键参数详解

--master-data=[=#]	​必须开启 binlog​
1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭–lock-tables功能，自动打开-x	–lock-all-tables功能（除非开启–single-transaction）
-A / --all-databases	备份所有数据库，含create database
-B / --databases db_name…	指定备份的数据库，包括create database语句
-E / --events	备份相关的所有event scheduler 计划任务
-R / --routines	备份所有存储过程和自定义函数
--triggers	备份表相关触发器，默认启用,用–skip-triggers，不备份触发器
--default-character-set=utf8	指定字符集
-F / --flush-logs	备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–single-transaction或-x，–master-data 一起使用实现，此时只刷新一次日志
--compact	去掉注释，适合调试，生产不使用
-d / --no-data	只备份表结构
-t / --no-create-info	只备份数据,不备份create table
-n / --no-create-db	不备份create database，可被-A或-B覆盖
--flush-privileges	刷新权限，备份mysql或相关时需要使用
-f / --force	忽略SQL错误，继续执行
--hex-blob	使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q / --quick	不缓存查询，直接输出，加快备份速度

⚠️ --master-data会自动启用 --lock-all-tables（除非搭配 --single-transaction）

通过备份文件和二进制文件恢复数据库到当前装填

通常数据库的备份都发生在凌晨用户访问量较少的时候进行，例如我们在凌晨3点对数据库进行了完全备份，但是在上午11点的时候，因为管理员的误操作，到时候数据库某张表被删除，如果我们用数据库的完整备份的话，就只能恢复到凌晨3点，那么要恢复3点到11点这段时间的数据，就需要用到二进制日志

前提:在进行数据库备份的时候加了–master-data=[1|2]选项，记录了数据库备份时，日志的位置

四、Binlog + 全备恢复流程

场景模拟​

• 凌晨 3:00 进行全备

• 上午 11:00 误删除数据表

• ​目标​：恢复到误删前状态（3:00 ~ 11:00 的数据从 binlog 恢复）

操作步骤

4.1 全量备份​（记录 binlog 位置）

[ root@Centos~]# mysqldump -A --master-data=2 > database.sql
[ root@Centos~]# less database.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='XX-bin.000001', MASTER_LOG_POS=2638793;
这条记录了当前的备份所在的日志位置，为XX-bin.000001的2638793

4.2 提取增量 binlog

[root@Centos~]# mysqlbinlog --start-position=2638793 /XXX/mariadb-bin.000007 > binary.sql

4.3 恢复数据

-- 关闭 binlog 记录
SET sql_log_bin = OFF;

-- 导入全量备份
SOURCE full_backup.sql;

-- 导入增量 binlog
SOURCE incremental.sql;

-- 重新启用 binlog
SET sql_log_bin = ON;

五、注意事项​

1. ​迁移数据库​：不可用 rm -rf /var/lib/mysql/*暴力删除（破坏系统表），应用 mysqladmin正规操作。

2. ​路径安全​：Binlog 存储目录需确保 MySQL 用户有写权限。

3. ​一致性​：MyISAM 表备份需加 --lock-all-tables，InnoDB 推荐加 --single-transaction。

4. ​版本兼容​：备份/恢复建议使用相同 MySQL 版本。

✨ ​进阶提示​：定期清理 binlog（PURGE BINARY LOGS）避免磁盘写满，建议配合 expire_logs_days参数使用。

请不要以此视为定论，这只是我的个人经验