mysql二进制日志太大_MySQL二进制日志 binlog 详细介绍

二进制日志(binlog) 记录了所有 DDL 语句和 DML 语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。通过binlog可以做数据恢复,做主从复制,此日志对于灾难时的数据恢复起着极其重要的作用。

开启 binlog日志

mysql5.7 默认是不开启 binlog 的, 需要在配置文件或MySQL客户端配置1

2

3

4

5

6

7

8

9

10

11

12# 添加三个参数

log_bin=ON # 打开 binlog 日志

log_bin_basename=/var/lib/mysql/mysql-bin # binlog 日志基本文件名,后面会追加标识来表示每一个文件

log_bin_index=/var/lib/mysql/mysql-bin.index # binlog 文件的索引文件,这个文件管理了所有的 binlog 文件的目录

# 上面三个参数的配置也可以简化为一个,下面这个配置的作用等同于上面三个

log-bin=/var/lib/mysql/mysql-bin

# 上面的配置修改好后,对于 5.7版本以下的MySQL,就算是完成了,

# 但是对于 5.7 及以上的版本,还需要配置一个 server-id, 且不能和集群中的其他MySQL服务器相同

server-id=1

常用操作

查看状态

重启 MySQL 后, 用 mysql 客户端登录

查看是否开启binlog: show variables like '%log_bin%'

查看当前 mysql 的 binlog 情况: show master status1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21MySQL [(none)]> show variables like "%log_bin%"

-> ;

+---------------------------------+----------------------------+

| Variable_name | Value |

+---------------------------------+----------------------------+

| log_bin | ON |

| log_bin_basename | /var/mysql/mysql-bin |

| log_bin_index | /var/mysql/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

+---------------------------------+----------------------------+

6 rows in set (0.00 sec)

MySQL [(none)]> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.04 sec)

手动刷新 binlog

通过 flush logs, 会创建一个新的 binlog 文件, 注意下面的 File 变为了 mysql-bin.0000021

2

3

4

5

6

7

8

9

10MySQL [(none)]> flush logs;

Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

清空 binlog

日志的删除

对于比较繁忙的系统，每天产生大量日志，这些日志如果长时间不清除，将会对磁盘空间带来极大的浪费，因此，需要定期删除日志。删除所有日志

执行 reset master1

2

3

4

5

6

7

8

9

10MySQL [(none)]> reset master;

Query OK, 0 rows affected (0.03 sec)

MySQL [(none)]> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 154 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

命令将删除所有 binlog 日志，新日志编号从“000001”开始删除指定序号之前的日志文件

执行 “purge master logs to ‘mysql-bin.**‘”命令，将删除编号之前的所有日志(不删除命令中指定的文件)删除指定日期前的日志

执行 “purge master log before ‘yyyy-mm-dd hh24:mi:ss’” 命令将删除指定日期前的所有日志修改配置文件，自动删除

在配置文件的[mysqld]中设置参数 expire_logs_days=#, 此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除。

查看 binlog 文件

binlog 文件是二进制文件,mysql 提供了 mysqlbinlog 工具用于查看 binlog 文件1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21[email protected]:/var/mysql# mysqlbinlog mysql-bin.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#180621 11:33:12 server id 1 end_log_pos 123 CRC32 0xb2899866 Start: binlog v 4, server v 5.7.22-0ubuntu0.16.04.1-log created 180621 11:33:12 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

eBwrWw8BAAAAdwAAAHsAAAABAAQANS43LjIyLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAB4HCtbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AWaYibI=

'/*!*/;

# at 123

#180621 11:33:12 server id 1 end_log_pos 154 CRC32 0x5d42e0ee Previous-GTIDs

# [empty]

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET [email protected]_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

这里主要关注 end_log_pos 154, 表示 binlog 当前的位置,主从复制中,从数据库就是根据这个位置完成增量复制

用 binlog 完成数据恢复

用 binlog 完成主从复制

日志的格式

二进制日志的格式分为 3 种：statement、row、mixed，可以在启动时通过参数 –binlog_format 进行设置，这 3 种格式的区别如下：

STATEMENT

mysql 5.1 之前的版本都采用这种方式，日志中记录的都是语句(statement)，每一条对数据造成修改的sql语句都会记录到日志中，通过 mysqlbinlog 工具，可以清晰的看到每条语句的文本。主从复制时，从库(slave)会将日志解析为原文本，并在从库中重新执行一次。这种格式的优点的日志记录清晰易读，日志量少，对 I/O 影响较小。缺点是在某些情况下 slave 的日志复制会出错。

ROW

mysql 5.1.11 之后，出现了这种新的日志格式，它将每一行的变更记录到日志中，而不是记录sql语句，比如一个简单的跟新sql：

update emp set name=’abc’

如果是 statement 格式，日志中会记录一行 sql 文本；

如果是 row 格式，由于是对全表进行更新，也就是每一行记录都会发生改变，如果是一个 100 万行的大表，则日志中会记录 100万条记录的变化情况。日志量大大增加。

这种格式的优点是会记录每一行数据变化的细节，不会出现某些情况下无法复制的情况，缺点是日志量大，对 I/O 影响较大。

MIXED

这是目前 MySQL 默认的日志格式，即混合了 statement 和 row 两种日志。默认情况下采用 statement，但在一些特殊情况下采用 row 来进行记录，比如

采用 NDB 存储引擎，此时对表的 DML 语句全部采用 row；

客户端使用了临时表；

客户端采用了不确定函数，比如 current_user() 等；

因为这种不确定函数在主从中得到的值可能不同，导致主从数据产生不一致。mixed 格式能尽量利用两种模式的优点，而避开他们的缺点。

注意：可以在 global 和 session 级别对 binlog_format 进行日志格式设置，但一定要谨慎操作，确保从库的复制能够正常进行。

其他选项

二进制日志记录了数据的变化过程，对于数据的完整性和安全性起着非常重要的作用。因此，MySQL 还提供了一些其他参数选项来进行更小粒度的管理

–binlog-db=db_name

该选项告诉主服务器，如果当前的数据库(即 use 选定的数据库)是 db_name, 应将更新记录到二进制文件中，其他所有么有显式指定的数据库更新将被忽略，不记录在日志中。

–binlog-ignore-db=db_name

该选项告诉主服务器，如果当前的数据库(即 use 选定的数据库) 是 db_name,不应将更新保存到二进制日志中，其他没有显式忽略的数据库都将进行记录。

如果想记录或忽略多个数据库，可以对上面两个选项分别使用多次。

–innodb-safe-binlog

此选项经常和 –sync-binlog = N (每写 N 次日志同步磁盘)一起配合使用，使得事务在日志中的记录更加安全。