前言

做运维或者DBA的同学肯定都遇到过这种情况：不小心执行了一个DROP TABLE语句，或者DELETE忘加WHERE条件，把数据删了。这时候怎么办？别急，MySQL的binlog日志可以帮你把数据找回来。

很多新手同学不知道binlog是什么，也不知道怎么用它来恢复数据。这篇文章就把binlog的知识点从头到尾讲清楚，包括binlog的三种格式、怎么开启、怎么查看、怎么用它恢复数据，看完之后你就彻底搞懂了。

一、binlog是什么

binlog的全称是二进制日志（Binary Log）。它记录了MySQL数据库里所有的写操作（INSERT、UPDATE、DELETE、CREATE TABLE这些），但是不记录SELECT查询。

binlog有什么用

1. 数据恢复：不小心删了数据，可以用binlog恢复
2. 主从复制：主库把binlog传给从库，从库重放binlog，实现主从同步
3. 数据审计：可以通过binlog追溯谁在什么时候做了什么操作

二、binlog的三种格式

binlog有三种格式，不同的格式记录的内容不一样。

1. STATEMENT（语句模式）

这种格式记录的是SQL语句本身。比如你执行了一条INSERT语句，binlog里就记录这条INSERT语句。

优点：

• 日志量小，占用空间少
• 同步的时候从库重放SQL就行

缺点：

• 有些函数（比如NOW()、UUID()）在主库和从库执行结果可能不一样，导致主从不一致
• 某些复杂的SQL可能在从库上执行结果不一样

2. ROW（行模式）

这种格式记录的是每一行数据的修改。比如你更新了100行数据，binlog里就记录这100行每一行改前是什么样，改后是什么样。

优点：

• 记录的是真实的数据修改，不会出现主从不一致的问题
• 能精确知道哪一行被改了

缺点：

• 日志量大，因为每一行修改都要记录
• 批量更新的时候，日志会特别大

3. MIXED（混合模式）

这种模式是前两种的混合。MySQL会自动判断：一般的SQL用STATEMENT模式，遇到那些可能出问题的SQL（比如用了NOW()、UUID()这些函数的），就自动切换成ROW模式。

总结： 现在一般推荐用ROW模式，虽然日志量大一点，但是数据一致性有保证，不会出主从不一致的问题。

三、怎么开启binlog

默认情况下，MySQL的binlog可能是没开的。我们先看看开没开：

SHOW VARIABLES LIKE 'log_bin';

如果Value是ON，说明开了；如果是OFF，说明没开。

开启binlog

编辑MySQL的配置文件 /etc/my.cnf，在[mysqld]下面添加：

[mysqld]
# 开启binlog
log_bin = mysql-bin

# binlog的格式，推荐用ROW
binlog_format = ROW

# 保存多少天的binlog，过期自动删除
expire_logs_days = 7

# 单个binlog文件最大多大
max_binlog_size = 100M

修改完之后重启MySQL服务：

systemctl restart mysqld

重启完之后再查一下，应该就开了。

四、怎么查看binlog

查看有哪些binlog文件

SHOW BINARY LOGS;

执行完之后会看到类似这样的结果：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     15264 |
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |       126 |
+------------------+-----------+

binlog文件是按编号来的，mysql-bin.000001满了就生成mysql-bin.000002，以此类推。

查看binlog里的内容

binlog是二进制文件，不能直接用cat看，要用mysqlbinlog工具。

比如要看mysql-bin.000001这个文件：

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

如果是ROW格式的binlog，直接看是看不懂的，要加-vv参数：

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

这样就能看到具体改了哪一行，改前是什么，改后是什么。

五、怎么用binlog恢复数据

这是大家最关心的：不小心删了数据，怎么用binlog恢复？

恢复的原理

binlog记录了所有的写操作。如果你不小心删了某段时间的数据，只要把这段时间之前的binlog重新执行一遍，数据就回来了。

举个例子

假设你在2026-10-05 10:00的时候，不小心执行了一条DELETE语句，把testdb库的users表全删了。现在要恢复数据。

步骤1：找到要恢复的binlog文件

先看看有哪些binlog文件，找到那个时间点对应的文件：

SHOW BINARY LOGS;

步骤2：找到删除操作的位置

用mysqlbinlog工具查看binlog，找到DELETE语句的位置：

mysqlbinlog --start-datetime="2026-10-05 09:00:00" --stop-datetime="2026-10-05 11:00:00" /var/lib/mysql/mysql-bin.000003

然后找到那条DELETE语句在binlog里的位置（position）。

步骤3：恢复数据

把DELETE语句之前的binlog重新执行一遍，数据就回来了：

mysqlbinlog --stop-position=刚才找到的位置 /var/lib/mysql/mysql-bin.000003 | mysql -u root -p

这样就把DELETE之前的数据恢复了。

注意： 恢复完之后，DELETE之后的操作也没了，所以还要把DELETE之后的操作也重新执行一遍，直到最新的位置。

六、binlog的其他常用操作

手动生成新的binlog文件

FLUSH LOGS;

这个命令会关闭当前的binlog文件，生成一个新的。一般备份完数据库之后执行这个，这样备份之后的操作都记录到新的binlog里，恢复的时候方便。

删除旧的binlog文件

-- 删除指定文件之前的所有binlog
PURGE BINARY LOGS TO 'mysql-bin.000003';

-- 删除指定日期之前的所有binlog
PURGE BINARY LOGS BEFORE '2026-10-01 00:00:00';

查看当前正在写的binlog

SHOW MASTER STATUS;

这个命令会显示当前正在写的binlog文件名和位置，主从复制的时候经常用到。

常见坑

坑1：binlog没开，删了数据找不回来

很多同学的MySQL默认没开binlog，结果不小心删了数据，才发现根本没有日志可以恢复，欲哭无泪。生产环境一定要开binlog！

坑2：binlog格式用了STATEMENT，主从不一致

很多同学用默认的STATEMENT格式，结果主从数据不一致。推荐用ROW格式，虽然日志大一点，但是数据一致性有保证。

坑3：binlog占满磁盘

很多同学开了binlog但是没设置过期时间，结果binlog文件越来越多，把磁盘占满了，MySQL直接挂了。一定要设置expire_logs_days，让旧的binlog自动删除。

坑4：恢复数据的时候把DELETE也执行了

很多同学恢复的时候，把DELETE语句也一起执行了，结果恢复完数据又被删了。一定要找到DELETE之前的位置，只恢复到那个位置之前。

坑5：恢复完没验证

很多同学恢复完数据就以为完事了，结果一查发现数据不对。恢复完一定要仔细核对数据，确保没问题了再上线。

总结

MySQL binlog的核心知识点：

1. binlog记录所有写操作，不记录查询
2. binlog的三个作用：数据恢复、主从复制、数据审计
3. 三种格式：
   • STATEMENT：记录SQL语句，日志小，但是可能主从不一致
   • ROW：记录每行数据修改，日志大，但是数据一致，推荐用
   • MIXED：混合模式，自动选择
4. 开启binlog：在my.cnf里配置log_bin和binlog_format
5. 查看binlog：用mysqlbinlog工具，ROW格式要加-vv
6. 恢复数据：找到误操作之前的位置，重新执行之前的binlog
7. 注意设置过期时间：不然binlog会把磁盘占满

binlog是MySQL最重要的日志之一，生产环境必须开。学会了用binlog恢复数据，以后不小心删数据的时候就不用慌了。

遇到问题加QQ23979811 协助处理

前言

做运维或者开发的同学都知道，数据库是整个网站最核心的资产。一旦数据库出了问题，数据丢了，那损失可就大了。所以做好数据库备份是非常重要的一件事。

很多新手同学可能从来没做过数据库备份，等真的出问题了才后悔莫及。这篇文章就详细讲讲MySQL数据库怎么备份和恢复，包括常用的备份命令、恢复方法，还有自动定时备份的脚本。

一、备份方法

MySQL备份最常用的工具就是mysqldump，它是MySQL自带的备份工具，不需要额外安装。

1. 备份整个数据库

备份单个数据库是最常用的场景。比如你要把本地的testdb数据库备份成一个sql文件：

mysqldump -u root -p testdb > /root/testdb.sql

执行完之后输入MySQL的密码，就开始备份了。备份完之后会在/root/目录下生成一个testdb.sql文件。

2. 备份单张表

有时候你只需要备份数据库里的某一张表，比如只备份users表：

mysqldump -u root -p testdb users > /root/users.sql

3. 备份所有数据库

如果你想把MySQL里所有的数据库都备份下来，可以用 --all-databases 参数：

mysqldump -u root -p --all-databases > /root/all.sql

4. 只备份表结构，不备份数据

有时候你只需要备份表结构，不需要备份数据，这时候可以用 --no-data 参数：

mysqldump -u root -p --no-data testdb > /root/testdb_schema.sql

5. 备份的时候加锁保证数据一致性

备份大表的时候，为了保证备份过程中数据不被修改，可以加 --lock-tables 参数：

mysqldump -u root -p --lock-tables testdb > /root/testdb.sql

6. 备份的时候压缩文件

如果数据库很大，备份出来的sql文件也会很大，这时候可以用gzip压缩一下：

mysqldump -u root -p testdb | gzip > /root/testdb.sql.gz

二、恢复方法

备份完了，怎么恢复呢？

1. 恢复整个数据库

恢复整个数据库的命令：

mysql -u root -p testdb < /root/testdb.sql

注意：恢复之前一定要确保testdb这个数据库已经存在了。如果不存在，需要先创建数据库：

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. 恢复单张表

恢复单张表的话，先登录MySQL，然后选择对应的数据库，再用source命令恢复：

mysql -u root -p

USE testdb;
source /root/users.sql;

3. 恢复压缩的备份文件

如果你的备份文件是用gzip压缩过的，恢复的时候需要先解压：

gunzip < /root/testdb.sql.gz | mysql -u root -p testdb

三、自动定时备份脚本

手动备份太麻烦了，我们可以写个脚本，每天自动备份数据库。

备份脚本内容

创建一个备份脚本 /root/mysql_backup.sh：

#!/bin/bash

# 数据库配置
DB_USER="root"
DB_PASSWORD="你的MySQL密码"
DB_NAME="testdb"
BACKUP_DIR="/root/backup"

# 备份文件名，用日期命名
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/testdb_$DATE.sql"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u$DB_USER -p$DB_PASSWORD $DB_NAME > $BACKUP_FILE

# 压缩备份文件
gzip $BACKUP_FILE

# 删除7天前的备份文件
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "备份完成：$BACKUP_FILE.gz"

给脚本加执行权限

chmod +x /root/mysql_backup.sh

添加到定时任务

用crontab添加定时任务，每天凌晨3点自动备份：

crontab -e

添加下面这行：

0 3 * * * /root/mysql_backup.sh

这样每天凌晨3点就会自动备份数据库了，而且只保留最近7天的备份，旧的会自动删除。

常见坑

坑1：备份的时候忘了加密码，执行的时候卡住了

很多同学写备份命令的时候，直接写 mysqldump -u root -p testdb > xxx.sql，然后执行的时候就会停下来让你输入密码。如果是在脚本里用的话，就会卡住。

解决方法是把密码直接写在命令里：mysqldump -u root -p你的密码 testdb > xxx.sql。注意-p和密码之间没有空格。

坑2：恢复的时候数据库不存在

很多同学恢复的时候直接执行 mysql -u root -p testdb < xxx.sql，结果报错说数据库不存在。记住恢复之前一定要先创建好对应的数据库。

坑3：备份文件太大，恢复的时候超时了

大文件恢复的时候很容易超时，或者因为各种原因中断。这时候可以用source命令在MySQL内部恢复，会稳定很多。

坑4：备份文件存在本地，服务器挂了备份也没了

很多同学备份文件直接放在服务器本地，觉得这样就安全了。其实不对，如果服务器磁盘坏了，或者被黑客删了，备份文件也没了。

正确的做法是：

1. 每天备份完之后，把备份文件下载到本地电脑
2. 或者上传到云存储（比如阿里云OSS、腾讯云COS）
3. 重要的备份最好异地多存几份

总结

MySQL数据库备份和恢复的核心内容：

1. 备份命令：mysqldump，常用参数有 --all-databases、--no-data、--lock-tables
2. 恢复命令：mysql < 备份文件，或者用source命令
3. 自动备份：写个shell脚本，配合crontab定时执行
4. 备份文件一定要异地存储，不能只放在服务器本地

数据库备份是个大事，千万不要等出问题了才想起来备份。养成每天自动备份的好习惯，出问题的时候才能从容应对。

遇到问题加QQ23979811 协助处理