MySQL bin-log日志恢复数据

目录

一、开启二进制日志

二、检查二进制日志是否开启

三、使用二进制日志备份和恢复

使用二进制日志备份恢复前先创建备份：

应用二进制日志：

扩展用法：

四、常见命令和操作

五. 使用 mysqlbinlog 工具查看二进制日志

1. 查看二进制日志的内容

2. 解码二进制日志并将内容保存到文件

3. 查看特定时间范围内的日志

4. 查看特定位置范围内的日志

5. 查看所有二进制日志文件的内容

6. 只查看头部信息

7. 使用过滤条件查看特定表的日志

---

在 MySQL 中，二进制日志（Binary Log, bin-log）是用于记录所有更改数据库数据的 SQL 语句的日志文件。开启和管理二进制日志可以用于数据恢复、主从复制等场景。默认是关闭的。

一、开启二进制日志

编辑MySQL配置文件（/etc/my.cnf）：
在 [mysqld] 部分添加以下行：

log-bin=mysql-bin
binlog-format=ROW
server-id=1

• log-bin：指定二进制日志文件的名称（这里是 mysql-bin）。
• binlog-format：设置二进制日志记录的格式（ROW 是推荐的格式）。
• server-id：服务器唯一标识符（用于主从复制）。

重启MySQL服务：
使更改生效，重启MySQL服务：

systemctl restart mysqld

二、检查二进制日志是否开启

登录MySQL：

   mysql -u root -p[密码]

检查二进制日志状态：

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;

• 通过 SHOW VARIABLES LIKE 'log_bin'; 验证 log_bin 是否为 ON。
• 通过 SHOW BINARY LOGS; 检查现有的二进制日志文件。

三、使用二进制日志备份和恢复

使用二进制日志备份恢复前先创建备份：

在定期备份数据时，创建物理导出（如 mysqldump）并记录此时的二进制日志位置。

mysqldump -u root -p[密码] --all-databases --single-transaction --flush-logs --master-data=2 > backup.sql

• --single-transaction：在一个事务中执行，确保一致性。
• --flush-logs：刷新日志文件，开始新的二进制日志。
• --master-data=2：在导出文件中注释的形式记录二进制日志的文件名和位置。

应用二进制日志：

假设从基于时间点（时间戳）恢复，不限定于某个特定的库或表：

mysqlbinlog --start-datetime="2024-06-10 10:00:00" --stop-datetime="2024-06-10 12:00:00" mysql-bin.000001 | mysql -u root -p

或者基于位置点恢复，不限定于某个特定的库或表：

mysqlbinlog --start-position=107 --stop-position=2345 mysql-bin.000001 | mysql -u root -p

注：也可以在上面命令末尾加上一个库名，表示这些数据要在指定的库中执行

扩展用法：

指定库的操作

直接指定库在 MySQL 中解析和应用二进制日志时，可以使用 --database 选项：

mysqlbinlog --start-datetime="2024-06-10 10:00:00" --stop-datetime="2024-06-10 12:00:00" --database=your_database mysql-bin.000001 | mysql -u root -p

 

在这条命令中，--database=your_database 确保只有与 your_database 数据库相关的 SQL 语句被解析和执行。

针对特定库或表解析日志

例如，假设只关心 test_db 数据库中的操作，将想要的生成sql语句文件

mysqlbinlog --start-position=107 --stop-position=2345 mysql-bin.000001 | grep 'use `test_db`\|table_name' > filtered-log.sql

然后应用筛选后的 SQL 日志 

mysql -u root -p[密码] < filtered-log.sql

四、常见命令和操作

列出所有二进制日志文件：

SHOW BINARY LOGS;

查看当前二进制日志文件和位置：

SHOW MASTER STATUS;

清理旧的二进制日志：

假设想删除前30天的二进制日志：

PURGE BINARY LOGS BEFORE DATE_SUB( NOW(), INTERVAL 30 DAY);

或者删除所有:

PURGE BINARY LOGS TO 'mysql-bin.010';

五. 使用 mysqlbinlog 工具查看二进制日志

1. 查看二进制日志的内容

mysqlbinlog mysql-bin.000001

这个命令会将 mysql-bin.000001 文件的内容输出到控制台，内容为可读的 SQL 语句。

2. 解码二进制日志并将内容保存到文件

mysqlbinlog mysql-bin.000001 > decoded-binlog.sql

这个命令会将 mysql-bin.000001 文件的内容解码，并将结果保存到 decoded-binlog.sql 文件中。

3. 查看特定时间范围内的日志

mysqlbinlog --start-datetime="2024-06-10 10:00:00" --stop-datetime="2024-06-10 12:00:00" mysql-bin.000001

这个命令会输出指定时间范围内的二进制日志内容。

4. 查看特定位置范围内的日志

mysqlbinlog --start-position=107 --stop-position=2345 mysql-bin.000001

这个命令会输出指定位置范围内的二进制日志内容。

5. 查看所有二进制日志文件的内容

如果有多个二进制日志文件，可以使用以下命令查看所有二进制日志文件的内容：

mysqlbinlog mysql-bin.000001 mysql-bin.000002 mysql-bin.000003

或者更方便的方法：

mysqlbinlog mysql-bin.0000*

6. 只查看头部信息

有时您可能只需要查看二进制日志文件的头部信息，可以使用 --base64-output=DECODE-ROWS -vv 选项。

mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000001

7. 使用过滤条件查看特定表的日志

使用 mysqlbinlog 结合 grep 来筛选特定表的日志内容：

mysqlbinlog mysql-bin.000001 | grep -i "table_name"

8.查看当前二进制文件

SHOW MASTER STATUS\G;

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

输出结果说明：
File: 当前正在写入的二进制日志文件名。
Position: 二进制日志中的当前写入位置。复制从服务器将从这个位置开始读取日志。
Binlog_Do_DB: 配置中要求记录到日志的数据库名。
Binlog_Ignore_DB: 配置中要求忽略的数据库名。
Executed_Gtid_Set: 已执行的 GTID（全局事务 ID）集，如果开启了 GTID 基于二进制日志。

 

9. 会创建新的日志文件 

FLUSH LOGS;

 执行成功后，MySQL 会创建新的日志文件，每个启用了日志的类别（例如二进制日志、错误日志、慢查询日志）都将使用新的文件名来记录后续的日志信息。

具体用途

• 二进制日志：生成一个新的二进制日志文件，现有的二进制日志文件会保留，可以用于恢复和备份操作。
• 错误日志：重新启动 MySQL 错误日志文件。
• 慢查询日志：重新启动慢查询日志文件。
• 常规日志：重新启动常规查询日志文件。

 

扩展：刷新特定类型的日志 

• 刷新二进制日志：

FLUSH BINARY LOGS;

• 刷新错误日志：

FLUSH ERROR LOGS;

• 刷新慢查询日志：

FLUSH SLOW LOGS;

• 刷新常规查询日志：

FLUSH GENERAL LOGS;