十四、MySQL日志之 binlog日志

最新推荐文章于 2024-09-11 08:48:55 发布

繁华依在

最新推荐文章于 2024-09-11 08:48:55 发布

阅读量1.1k

点赞数 23

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42171272/article/details/137033372

版权

MySQL 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的二进制日志（Binarylog）在数据恢复、数据复制中的关键作用，包括默认设置、参数调整、查看日志方法以及删除策略，并对比了与redolog的区别。

摘要由CSDN通过智能技术生成

一、二进制日志（Binary log）

binlog可以说是MySQL中比较重要的日志了，在日常开发和运维中经常用到。

binlog即binary log，二进制日志文件，它记录了数据库中所有执行的DDL和DML等数据库更新时间的语句。但是不包括没有修改任何数据的语句（例如 select show 等）。

它以时间形式，记录并保存在二进制文件中，通过这些信息，我们可以再现数据更新操作的全过程。

1、binlog主要应用场景：

用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务。
用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到主从一致的目的。

可以说MySQL数据库的数据备份、主备、单主、多主、MGR都离不开binlog，需要用它来同步数据，保持数据的一致性。

2、查看默认情况

查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。
(root@localhost) [(none)]> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             | //开关
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
//存放路径
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
//索引文件，管理了所有binlog文件的目录
| log_bin_trust_function_creators | OFF                            |
//函数创建
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |//变更sql记录下来
+---------------------------------+--------------------------------+
log_bin_basename:是binlog日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_index:是binlog文件的素引文件，这个文件管理了所有的binlog文件的目录
log_bin_trust_function_creators:限制存储过程，前面我们已经讲过了，这是因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
log_bin_use_v1_row_events此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行（MySQL5.6的默认值为2)。

3、日志参数设置

（1）永久设置

在MySQL的配置文件中（my.cnf)

[mysqld]
#启用二进制日志
log-bin=mysql-bin
binlog_expire_logs_seconds= 600  //此参数控制二进制日志文件保留的时长单位是秒，默认2592000 30天 --14400 4小时;86400 1天; 259200 3天;
max_binlog_size=100M  //控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。默认值是1GB
binlog_rows_query_log_evects = 1 //记录行的操作

（2）临时性设置

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql 8 中只有会话级别的设置，没有了global级别的设置。

(root@localhost) [test]> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF   |
+------------------------------+-------+
(root@localhost) [test]> set binlog_rows_query_log_events=1;
# global 级别
mysql> set global sql_log_bin= 0 ;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used
with SET GLOBAL

# session级别
mysql> SET sql_log_bin = 0 ;
Query OK, 0 rows affected (0.01 秒)

4、查看日志

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。

MySQL服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按 1 递增。即日志文件的数与MySQL服务启动的次数相同；如果日志长度超过了max_binlog_size的上限（默认是1GB），就会创建一个新的日志文件。

查看当前的二进制日志文件列表及大小。

(root@localhost) [(none)]> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000096 |       178 | No        |
| mysql-bin.000097 |       178 | No        |
| mysql-bin.000098 |      1141 | No        |
| mysql-bin.000099 |       178 | No        |
| mysql-bin.000100 |       863 | No        |
| mysql-bin.000101 |      5620 | No        |
| mysql-bin.000102 |       178 | No        |
| mysql-bin.000103 |       178 | No        |
| mysql-bin.000104 |    140033 | No        |
| mysql-bin.000105 |      3875 | No        |
| mysql-bin.000106 |       178 | No        |
| mysql-bin.000107 |       938 | No        |
| mysql-bin.000108 |       155 | No        |
+------------------+-----------+-----------+
查看binlog 格式
(root@localhost) [(none)]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

所有对数据库的修改都会记录在binglog中。但binlog是二进制文件，无法直接查看，借助mysqlbinlog命令工具了。

关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。简单分享几个常用的语句，更多操作可以参考官方文档。

(root@localhost) [test]> 
(root@localhost) [test]> show binlog events in 'mysql-bin.000024';
+------------------+------+----------------+-----------+-------------+--------------------
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info  
                                     
    Log_name    : 这是二进制日志文件的名称。
    Pos    : 这是当前事件在日志文件中的位置。
    Event_type    : 这是事件的类型，例如：
    FORMAT_DESCRIPTION_EVENT: 二进制日志的格式描述。
    QUERY_EVENT: SQL查询事件。
    UPDATE_ROWS_EVENT: 更新事件，表示对行的更改。
    WRITE_ROWS_EVENT: 插入事件，表示向表中插入新行。
    DELETE_ROWS_EVENT: 删除事件，表示从表中删除行。

    Server_id    : 产生该事件的MySQL服务器的ID。在复制环境中，这是很重要的，因为从服务器会使用这个ID来识别它应该从哪个位置开始读取二进制日志。
    End_log_pos    : 该事件在日志文件中的结束位置。
    Info    : 这通常提供了关于事件的更多详细信息

# 可查看参数帮助
mysqlbinlog --no-defaults --help

# 查看最后 100 行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000108 |tail - n 100


# 根据position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv mysql-bin.000108 |grep -A 20 '4939002'
上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
IN 'log_name'：指定要查询的binlog文件名
FROM pos：指定从哪个pos起始点开始查起
LIMIT [offset]：偏移量(不指定就是 0 )
row_count :查询总条数（不指定就是所有行）
#a、查询第一个最早的binlog日志:
show binlog events\G ;

#b、指定查询mysql-bin.088802这个文件
show binlog events in 'mysql-bin.000108'\G

#c、指定查询mysql-bin. 080802这个文件，从pos点:391开始查起:
show binlog events in 'mysql-bin.000108' from 391\G

#d、指定查询mysql-bin.000802这个文件，从pos点:391开始查起，查询5条（即5条语句)
show binlog events in 'mysql-bin.000108' from 391 limit 5\G

#e、指定查询 mysql-bin.880002这个文件，从pos点:391开始查起，偏移2行〈即中间跳过2个）查询5条（即5条语句)。
show binlog events in 'mysql-bin.000108' from 391 limit 2,5\G

具体恢复数据请查看八、MySQL备份与数据恢复-CSDN博客

5、删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS只删除指定部分的二进制日志文件，RESET MASTER删除所有的二进制日志文件。具体如下：

（1）purge master logs 删除指定日志文件

语法：
PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’
示例：
1、使用PURGE MASTER LOGS语句删除创建时间比mysql-bin.000101早的所有日志
PURGE MASTER LOGS T0 "mysql-bin.000101";
2、使用PURGE MASTER LOGS语句删除2023年10月21日前创建的所有日志文件。
    1、显示二进制日志文件列表
    show binary logs；
    2、执行mysqlbinlog 命令查看二进制日志文件mysql-bin.000101 的内容
    mysqlbinlog --no-defaults "/var/lib/mysql/mysql-bin.000101"
    3、删除
    purge master logs before "20221021";

（2）删除所有

reset master;

总结：

binlog与redolog区别：

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层
虽然它们都属于持久化的保证，但是则重点不同。
- redo log让InnoDB存储引擎拥有了崩溃恢复能力。
- binlog保证了MySQL集群架构的数据一致性。