Mariadb数据库的备份与恢复过程详细介绍_数据库_开发者

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

作用

用于数据库恢复（时间点恢复）
建议：单独存储路径，与数据文件分离

启用步骤

效果# 编辑配置文件
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 datajsbase
`-B / --databases db_name…`	指定备份的数据库，包括create database语句
`-E / --events`	备份相关的所有event sch编程eduler 计划任务
`-R / --routines`	备份所有存储过程和自定义函数
`--triggers`	备份表相关触发器，默认启用,用–skip-triggers，不备份触发器
`--default-charaandroidcter-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，Bwww.devze.comIT的数据类型的列时使用，避免乱码
`-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

[rootwww.devze.com@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;