Mysql 中二进制日志的初步认知

心已赠人 2023-09-24 21:02 53阅读 0赞

### 二进制日志 ###

二进制日志中以“事件”的形式记录了数据库中数据的变化情况，对于MySQL数据库的灾难恢复起着重要的作用。

--------------------

#### 开启二进制日志 ####

可以在 `my.cnf` 文件或者 `my.ini` 文件中进行如下配置来开启二进制日志。

--------------------

[mysqld]
    log_bin = /data/mysql/log/bin_log/mysql-bin
    binlog_format= mixed
    binlog_cache_size=32m
    max_binlog_cache_size=64m
    max_binlog_size=512m
    expire_logs_days = 10

各项配置说明如下：

*  `log_bin`：表示开启二进制日志。如果没有为此项赋值，则 MySQL 会在 DATADIR 选项指定的目录（MySQL的数据存放目录）下创建二进制文件。
 *  `binlog_format`：二进制文件的格式。取值可以是`STATEMENT`、`ROW`和`MIXED`。
    
     *  STATEMENT
        
        记录SQL语句。日志文件小，节约IO，但是对一些系统函数不能准确复制或不能复制，如now()、uuid()等
     *  ROW
        
        记录表的行更改情况，可以为数据库的恢复、复制带来更好的可靠性，但是二进制文件的大小相较于STATEMENT会有所增加
     *  MIXED
        
        STATEMENT和ROW模式的混合。默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式。

业内目前推荐使用的是 ROW 模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。

*  `binlog_cache_size`：二进制日志的缓存大小。
 *  `max_binlog_cache_size`：二进制日志的最大缓存大小。
 *  `max_binlog_size`：单个二进制日志文件的最大大小，当文件大小超过此选项配置的值时，会发生日志滚动，重新生成一个新的二进制文件。
 *  `expire_logs_days`：二进制日志的过期时间。如果配置了此选项，则 MySQL 会自动清理过期的二进制日志。此选项的默认值为 0 ，表示 MySQL 不会清理过期日志。

配置完成后，重启 MySQL 才能使配置生效。此时，会在 `/data/mysql/log/bin_log` 目录下生成 MySQL 的二进制文件。

重启之后，可以看到我们配置的二进制日志的相关信息。

show variables like '%log_bin%';

--------------------

#### 查看二进制日志 ####

二进制日志文件不能以纯文本文件的形式来查看，可以使用 MySQL 的 `mysqlbinlog` 命令进行查看。接下来简单介绍一下查看 MySQL 二进制日志的步骤。

注：我提前创建的测试表

CREATE TABLE `test1` (
      `id` int NOT NULL,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

（1）向 test1 表中插入两条测试数据。

INSERT INTO test1 (id,name) VALUES(5,"赵山河");
    INSERT INTO test1 (id,name) VALUES(6,"跟着飞哥学编程");

--------------------

（2）使用 `mysqlbinlog` 命令查看二进制日志

--------------------

mysqlbinlog --no-defaults /data/mysql/log/bin_log/mysql-bin.000001

--------------------

二进制日志中记录了向 `test1` 数据表中插入数据的 SQL 语句。

注意：查看 `/data/mysql/log/bin_log` 目录下生成的 MySQL 二进制文件时，发现有一个 mysql-bin.index 文件，这个文件不记录二进制内容，其中记录的是当前目录下存在的所有二进制文件的完整路径。可以以纯文本文件的形式来查看 mysql-bin.index 文件。

--------------------

cat /data/mysql/log/bin_log/mysql-bin.index

--------------------

#### 删除二进制日志 ####

MySQL中除了通过配置二进制日志的过期时间，由 MySQL 自动删除过期的二进制日志外，还提供了**3**种安全的手动删除二进制日志的方法。

在正式介绍手动删除 MySQL 二进制日志的方法之前，先对 MySQL 进行多次重启操作，使 MySQL 能够生成多个二进制日志文件，以便进行删除测试。

多次重启MySQL后，再次查看 `/data/mysql/log/bin_log` 目录下的文件。

--------------------

1．根据编号删除二进制日志

根据编号删除二进制日志，语法格式如下：

--------------------

PURGE {
         BINARY | MASTER } LOGS TO 'log_name'

--------------------

在MySQL命令行执行此语法格式的SQL语句，会删除比指定文件名编号小的所有二进制日志文件。例如，删除比mysql-bin.000003文件编号小的所有二进制日志文件。

--------------------

mysql> PURGE MASTER LOGS TO 'mysql-bin.000003';
    Query OK, 0 rows affected (0.01 sec)

--------------------

SQL语句执行成功，查看/data/mysql/log/bin\_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
    total 36
    -rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000003
    -rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000004
    -rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000005
    -rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000006
    -rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
    -rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
    -rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
    -rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
    -rw-r----- 1 mysql mysql 328 Jan 17 16:29 mysql-bin.index

发现mysql-bin.000001文件和mysql-bin.000002文件被删除了。说明根据编号删除二进制日志时，只会删除比当前指定的文件编号小的二进制日志文件，不会删除当前指定的二进制日志文件。

2．根据时间删除二进制日志

根据时间删除二进制日志，语法格式如下：

PURGE { BINARY | MASTER } LOGS BEFORE datetime_expr

执行此语法格式的SQL语句时，MySQL会删除指定时间以前的二进制日志。

例如，删除“2020-01-17 16:21:00”之前的二进制日志文件。

mysql> PURGE MASTER LOGS BEFORE '2020-01-17 16:21:00';
    Query OK, 0 rows affected (0.00 sec)

SQL语句执行成功，查看/data/mysql/log/bin\_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
    total 20
    -rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
    -rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
    -rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
    -rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
    -rw-r----- 1 mysql mysql 164 Jan 17 16:37 mysql-bin.index

“2020-01-17 16:21:00”之前的二进制日志文件已经被删除了，但不会删除“2020-01-17 16:21:00”时间点的二进制日志文件。

3．删除所有二进制日志

在MySQL命令行执行如下命令即可删除所有二进制日志文件。

mysql> RESET MASTER;
    Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功，再次查看/data/mysql/log/bin\_log目录下的文件。

[root@binghe150 ~]# ll /data/mysql/log/bin_log
    total 8
    -rw-r----- 1 mysql mysql 155 Jan 17 16:41 mysql-bin.000001
    -rw-r----- 1 mysql mysql  41 Jan 17 16:41 mysql-bin.index

此时/data/mysql/log/bin\_log目录下的所有二进制文件已经被删除，并且二进制文件重新从000001开始编号。

#### 临时开启与关闭二进制 ####

在 Mysql 命令行执行如下命令暂时关闭二进制日志：

--------------------

mysql> SET sql_log_bin = 0;
    Query OK, 0 rows affected (0.00 sec)

--------------------

暂时开启二进制日志，则需要在MySQL命令行执行如下命令：

--------------------

mysql> SET sql\_log\_bin = 1;  
Query OK, 0 rows affected (0.00 sec)

--------------------