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.000002Position: 154Binlog_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;