mysql通过 BINLOG 恢复数据总结

目录

1、BINLOG 常见操作命令

通过 MySQL Cli 查询 BINLOG 信息

2、通过 mysqlbinlog 查询 BINLOG 信息

3、查看最后一个bin-log日志信息

4、查询 BINLOG 日志并导出，用于分析和排查 sql 语句：

5、数据恢复

注意

---

平时对数据库的操作基本是对数据表以及表中的数据操作，对数据库的备份和还原用到的较少，今天在此总结下，当作是一个自我学习的过程。

日常开发，运维中，经常会出现误删数据的情况。误删数据的类型大致可分为以下几类：

• 使用 delete 误删行
• 使用 drop table 或 truncate table 误删表
• 使用 drop database 语句误删数据库
• 使用 rm 命令误删整个 MySQL 实例。

不同的情况，都会有其优先的解决方案：

• 针对误删行，可以通过 Flashback 工具将数据恢复
• 针对误删表或库，一般采用通过 BINLOG 将数据恢复。

本篇主要讨论的内容是误删表或者库，会先介绍有关 BINLOG 的操作命令，然后会对误删表的这种情况进行实际的模拟。

1、BINLOG 常见操作命令

BINLOG 的查询方式一般分为两种，一种是进入 MySQL 控制台进行查询，另一种是通过 MySQL 提供的工具 mysqlbinlog 进行查询，两者的不同会在下面介绍。

通过 MySQL Cli 查询 BINLOG 信息

在 cli 中，常见的命令如下：

# 查询 BINLOG 格式
show VARIABLES like 'binlog_format';

# 查询 BINLOG 位置
show VARIABLES like 'datadir';

# 查询当前数据库中 BINLOG 名称及大小
show binary logs;

# 查看 master 正在写入的 BINLOG 信息
show master status\G;

# 通过 offset 查看 BINLOG 信息
show BINLOG events in 'mysql_bin.000024' limit 9000,  10;

# 通过 position 查看 binlog 信息
show BINLOG events in 'mysql_bin.000024' from 1742635 limit 10;

2、通过 mysqlbinlog 查询 BINLOG 信息

在介绍 mysqlbinlog 工具使用前，先来看下 BINLOG 文件的内容：

# 查询 BINLOG 的信息
mysqlbinlog  --no-defaults mysql_bin.000024 | less

# at 2166
#210811 14:43:26 server id 1  end_log_pos 2239 CRC32 0x35633d0a         Query   thread_id=3     exec_time=0     error_code=0

• at 表示 offset 或者说事件开始的起始位置
• 210811 14:43:26 server id 1 表示 server 1 开始执行事件的日期
• end_log_pos 2239 表示事件的结束位置 + 1，或者说是下一个事件的起始位置。
• exec_time 表示在 master 上花费的时间，在 salve 上，记录的时间是从 Master 记录开始，一直到 Slave 结束完成所花费的时间。
• rror_code=0 表示没有错误发生。

在大致了解 binlog 的内容后，mysqlbinlog 的用途有哪些？

• mysqlbinlog 可以作为代替 cli 读取 binlog 的工具。
• mysqlbinlog 可以将执行过的 SQL 语句输出，用于数据的恢复或备份。

3、查看最后一个bin-log日志信息

mysql> SHOW master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000024 |  1433667 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4、查询 BINLOG 日志并导出，用于分析和排查 sql 语句：

[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql_bin.000024 | less > 1.txt

5、数据恢复

mysqlbinlog mysql_bin.000024 --start-position=1432992 --stop-position=1433271 -v | mysql -uroot -p123456

这个操作相当于将之前从创建表到误操作之前的数据全部执行一遍，恢复到之前的数据，以最近的备份数据的备份点来作为起始点，drop操作的前一个操作为结束点。

注意

3、必要时需做好备份，将数据库的表都删除，恢复到指定时间点，因为是将之前的所有操作操作一遍，难免会有主键冲突等问题
4、按理来说，就算有新数据，将表删除之后，跳过两次drop操作，即可恢复原本数据
5、数据库每一次重启都会产生一个binlog.0000的日志，找好对应的日志，即可