深入了解MySQL日志——开启binlog日志

一、为什么需要开启binlog日志

MySQL的binlog是一种二进制日志，主要用于记录MySQL服务器上的操作，包括对数据库进行的更改操作、授权操作等。当出现数据错误或者丢失数据时，我们可以通过查看binlog日志来了解确切的操作情况，以此快速定位问题并进行修复。同时，binlog日志还可以用于数据复制与同步，备份与还原等操作。

二、如何开启binlog日志

MySQL开启binlog日志的操作十分简单，只需要在配置文件中添加对应的参数即可。

#打开MySQL配置文件
vim /etc/my.cnf

#进入文件后，在[mysqld]标签下添加如下参数：
log-bin=mysql-bin
expire_logs_days=7

其中，”log-bin”表示开启binlog日志，”expire_logs_days”表示binlog日志的保留时间，在该时间到达后会自动删除。

三、binlog日志参数说明

除了上述的两个参数，我们还可以在配置文件中添加其他参数来对binlog日志进行更为详细的设置。

1. max_binlog_size

该参数用于设置binlog日志文件的最大大小，一旦文件达到该大小，MySQL会自动创建一个新的binlog日志文件继续记录。默认值为1073741824（1GB）。

2. binlog_format

该参数用于设置binlog日志的格式，有三种：statement、row和mixed。

statement模式记录的是每个执行的SQL语句，适用于大部分情况；row模式记录的是每个操作的具体行数，适用于更为复杂的操作场景；mixed模式会自动根据SQL语句的操作类型（如简单的select语句会使用statement模式，而复杂的更新语句会使用row模式）来选择何种模式记录。

3. binlog-do-db和binlog-ignore-db

这两个参数用于设置哪些数据库需要被记录到binlog日志中或者被忽略，多个数据库之间需要使用”,”隔开。

#binlog-do-db
binlog-do-db=db1,db2

#binlog-ignore-db
binlog-ignore-db=mysql,sys

四、常用的binlog日志操作

1. 查看当前的binlog日志状态

#通过登录MySQL服务器来查询状态
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 107
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

在结果中，”File”对应的是当前使用的binlog文件名，”Position”表示该文件中当前的偏移位置。

2. 查看当前所有的binlog日志

该操作可以通过命令行工具来查看，也可以在MySQL客户端中执行。

#通过命令行工具查看
[root@localhost data]# ls -l /var/lib/mysql/mysql-bin.*
-rw-r----- 1 mysql mysql 314936 Mar  6 14:34 /var/lib/mysql/mysql-bin.000010
-rw-r----- 1 mysql mysql 1073743650 Mar  7 14:32 /var/lib/mysql/mysql-bin.000011
-rw-r----- 1 mysql mysql 1073741988 Mar  8 14:30 /var/lib/mysql/mysql-bin.000012

#通过MySQL客户端查看
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     36978 |
| mysql-bin.000002 |     75440 |
| mysql-bin.000003 |  12020411 |
+------------------+-----------+

3. 查看指定时间段内的binlog记录

该操作需要使用MySQL的binlog解析工具”mysqlbinlog”，可以通过该工具来查看指定时间段内的binlog记录。

#查看从指定时间开始的binlog
[root@localhost data]# mysqlbinlog --start-datetime="2022-03-06T00:00:00" /var/lib/mysql/mysql-bin.000012

#查看从指定时间到指定时间之间的binlog
[root@localhost data]# mysqlbinlog --start-datetime="2022-03-06T00:00:00" --stop-datetime="2022-03-06T12:00:00" /var/lib/mysql/mysql-bin.000012

4. 清空过期的binlog日志

当binlog日志达到保留时间后，我们需要手动清理过期的binlog日志。

#检查当前的binlog状态，找出需要删除的binlog文件。
mysql> show master status;
+------------------+-----------+
| File             | Position  |
+------------------+-----------+
| mysql-bin.000012 | 108766634 |
+------------------+-----------+
1 row in set (0.00 sec)

#通过PURGE BINARY LOGS命令来删除过期的binlog日志，所有在当前文件之前的binlog文件都会被清除。
mysql> PURGE BINARY LOGS TO 'mysql-bin.000011';

五、总结

binlog日志是MySQL非常重要的一部分，通过了解它的特性以及如何开启、配置和操作binlog日志，我们可以更好地保护数据的完整性和安全性，保证数据的一致性，并为数据的复制、同步和备份提供了方便和保障。