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;

相关推荐

  1. MySQL bin-log日志恢复数据

    2024-06-13 19:28:01       32 阅读
  2. MySQL数据库利用binlog日志文件恢复数据

    2024-06-13 19:28:01       62 阅读
  3. 【Mysql】使用binlog日志进行数据库迁移和数据恢复

    2024-06-13 19:28:01       119 阅读
  4. Python日志logging

    2024-06-13 19:28:01       42 阅读
  5. MySQL 8.0 架构 之 DDL日志(元数据日志)(DDL log

    2024-06-13 19:28:01       40 阅读

最近更新

  1. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-06-13 19:28:01       94 阅读
  2. Could not load dynamic library ‘cudart64_100.dll‘

    2024-06-13 19:28:01       100 阅读
  3. 在Django里面运行非项目文件

    2024-06-13 19:28:01       82 阅读
  4. Python语言-面向对象

    2024-06-13 19:28:01       91 阅读

热门阅读

  1. LVS ipvsadm命令的使用(二)

    2024-06-13 19:28:01       28 阅读
  2. CVE-2020-1472域渗透 NetLogon 权限提升漏洞

    2024-06-13 19:28:01       34 阅读
  3. 在 Visual Studio 调试器中指定符号 (.pdb) 和源文件

    2024-06-13 19:28:01       29 阅读
  4. Android 调用系统相册、系统相机拍照

    2024-06-13 19:28:01       25 阅读
  5. 新视野大学英语2 词组 6.13

    2024-06-13 19:28:01       32 阅读
  6. Hutool有哪些常用方法

    2024-06-13 19:28:01       23 阅读
  7. SQL Server几种琐

    2024-06-13 19:28:01       28 阅读
  8. 等保测评练习题

    2024-06-13 19:28:01       29 阅读