MariaDB 二进制日志 -- 中国DBA社区

在前面的3篇文章中，我们看了MariaDB的3种日志：
MariaDB 的错误日志
http://www.cndba.cn/dave/article/2635http://www.cndba.cn/dave/article/2645

MariaDB 查询日志
http://www.cndba.cn/dave/article/2636

MariaDB 慢查询日志
http://www.cndba.cn/dave/article/2637 http://www.cndba.cn/dave/article/2645http://www.cndba.cn/dave/article/2645

这里我们看第四种日志：二进制日志(binlog)。

1 二进制日志概念

MariaDB的二进制日志(binlog)记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MariaDB的二进制日志是事务安全型的。
二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

一般来说开启二进制日志大概会有1%的性能损耗。二进制有两个最重要的使用场景: 
1). MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 
2). 数据恢复，通过使用mysqlbinlog工具来使恢复数据。

MariaDB的binlog 有点类似与Oracle 数据库的归档文件。假设数据库每晚12点进行备份。当第二天早上10点，数据库异常，需要进行恢复，我们就可以利用昨天的备份，加之后生成的binlog 进行恢复。先恢复昨天的备份，在在这个基础上应用binlog，重新执行一次操作，即可完成数据，所以这一点和Oracle的恢复很像。

2 二进制日志相关参数

log_bin : 控制是否开启二进制日志，是只读变量，只能通过修改my.cnf的方式进行修改，默认在数据目录下，默认名为主机名后面跟“-bin”。修改后重启数据库生效。该参数用来指定二进制日志的前缀，后缀名会进行自动编号，每次日志滚动后，后缀名编号自动加1。比如设置log_bin的值为mybinlog，那么在DATADIR目录中会自动生成一个以mybinlog为文件名前缀的二进制日志文件。该参数设置后，在查询时仍然只显示为ON或者OFF, 不会显示具体的参数值。

sql_log_bin ：标识当前会话中的操作是否会被记录于二进制日志，是一个会话界别的变量，只能在当前会话中使用set sql_log_bin命令进行设置，不能使用set global sql_log_bin命令进行设置，因为它是会话级别的变量，如在my.cnf文件中配置，可能会无法启动mysql。此变量值可以设置为ON|OFF，表示在当前数据库连接中，对数据库进行修改的语句是否记录到binlog中，在主从复制结构中，设置为OFF的会话的语句由于没有被binlog记录，所以也不会同步到从节点。http://www.cndba.cn/dave/article/2645

binlog_format ：指定二进制日志的记录方式，此变量的值可以设置为statement、row、mixed，默认为MIXED。

max_binlog_size ：设置单个二进制日志文件的最大大小，以字节为单位，超过此值大小，则二进制日志文件会自动滚动，比如设置为500M为524288000。

sync_binlog ：默认为0.

1). 设置为0，表示事务提交之后，MariaDB不会立即将内存中的binlog刷到磁盘中的binlog日志文件中，而是由文件系统决定什么时候刷写，这可能取决于文件系统的缓存机制，当此值设置为0时，一旦操作系统宕机，那么将丢失未从内存中同步到磁盘中的binlog。
2). 设置为1，表示每1次事务提交后，会将内存中的二进制日志立即同步到磁盘中的二进制日志文件中。
3). 还可以将该值设定为特定值。比如将此值设置为3，则表示每3次事务提交后，将binlog从内存刷写到磁盘一次，值设置的越大，有可能丢失的日志数据将会越多。

expire_logs_days : 二进制日志自动删除的天数。默认值为0,表示”没有自动删除”。

3 二进制日志的三种模式

MariaDB的二进制日志参数binlog_format有三种值。这里看下具体的说明。

3.1 Row Level

日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。

优点：

对于一个写入操作，row格式binlog分为四部分，仿次为:
BEGIN -> Table_map -> Write_rows/Delete_rows/Update_rows -> COMMIT

在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，只需要记录哪一条记录被修改，修改的值。所以row level的日志内容会非常清楚的记录下每一行数据修改的细节，在任何情况都可以被复制，这对复制来说是最安全可靠的。而且不会出现某些特定情况下的存储过程，或function，以及 trigger的调用和触发无法被正确复制的问题。

多数情况下，从服务器上的表如果有主键的话，复制就会快了很多；
复制以下几种语句时的行锁更少：

1) INSERT … SELECT
2) 包含 AUTO_INCREMENT 字段的 INSERT
3) 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句执行 INSERT，UPDATE，DELETE 语句时锁更少；
从服务器上采用多线程来执行复制成为可能；

缺点：

row level下，所有的执行的语句在记录到日志中时，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner_member_id = ‘dave’ where owner_member_id = ‘www.cndba.cn’，执行之后，日志中记录的不是这条update语句所对应的事件(MySQL以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的多个事件。自然，bin-log日志的量就会很大。尤其是当执行alter table之类的语句的时候，产生的日志量是惊人的。因为MySQL对于alter table之类的表结构变更语句的处理方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记录到日志中。

3.2 Statement Level

每一条会修改数据的sql都会记录到 master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。

优点：

statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为他只需要记录在Master上所执行的语句的细节，以及执行语句时候的上下文的信息。

缺点：

由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于MySQL现在发展比较快，很多的新功能不断的加入，使MySQL得复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能真确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化，所以不会出现类似的问题。

3.3 Mixed Level

是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

3.4 在配置文件中参数

可以直接修改my.cnf 配置文件，也可以用命令修改：

log-bin=mysql-bin
#binlog_format=”STATEMENT”

运行时在线修改：

mysql> set session binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)

mysql> set session binlog_format='row';
Query OK, 0 rows affected (0.00 sec)

mysql> set session binlog_format='mixed';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global binlog_format='mixed';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

4 常用binlog日志操作命令

4.1 查看所有binlog日志列表

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     28964 |
| mysql-bin.000002 |      1323 |
| mysql-bin.000003 |       365 |
| mysql-bin.000004 |       625 |
| mysql-bin.000005 |       365 |
| mysql-bin.000006 |      2941 |
| mysql-bin.000007 |       408 |
| mysql-bin.000008 |       478 |
| mysql-bin.000009 |       342 |
+------------------+-----------+
9 rows in set (0.00 sec)

4.2 查看master状态

即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      342 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4.3 刷新log日志

自此刻开始产生一个新编号的binlog日志文件

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
……
| mysql-bin.000011 |       432 |
| mysql-bin.000012 |       385 |
+------------------+-----------+
12 rows in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
……
| mysql-bin.000011 |       432 |
| mysql-bin.000012 |       432 |
| mysql-bin.000013 |       385 |
+------------------+-----------+
13 rows in set (0.00 sec)

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

5 查看binlog日志的内容

查看某个binlog日志内容，有两种方式.

5.1 使用mysqlbinlog自带查看命令

binlog是二进制文件，普通文件查看器cat more vi等都无法打开，可以使用自带的 mysqlbinlog 命令查看。

注意：在使用时如果报以下错误，加上“—no-defaults”选项就可以解决：

mysqlbinlog: unknown variable 'default-character-set=utf8'
mysqlbinlog --no-defaults --help

具体帮助可以参考help，这里看几个常用的选项：

mysqlbinlog [options] log-files
--start-position     ：开始位置
--stop-position     ：结束位置
--start-datetime 'yyyy-mm-dd hh:mm:ss'  ：开始时间
--stop-datetime 'yyyy-mm-dd hh:mm:ss'  ：结束时间

—查看binlog的完成内容：

[root@www.cndba.cn/dave ~]#  mysqlbinlog --no-defaults /mysql/data/mysql-bin.000020
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180208 12:34:21 server id 1  end_log_pos 256 CRC32 0x5e150d29     Start: binlog v 4, server v 10.2.12-MariaDB-log created 180208 12:34:21 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TdN7Wg8BAAAA/AAAAAABAAABAAQAMTAuMi4xMi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABN03taEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgEpDRVe
'/*!*/;
# at 256
#180208 12:34:21 server id 1  end_log_pos 299 CRC32 0x422cbf22     Gtid list [0-1-74]
# at 299
#180208 12:34:21 server id 1  end_log_pos 342 CRC32 0xcd5f0223     Binlog checkpoint mysql-bin.000020
# at 342
#180208 13:09:03 server id 1  end_log_pos 384 CRC32 0x8d423d5f     GTID 0-1-75 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=75*//*!*/;
BEGIN
/*!*/;
# at 384
#180208 13:09:03 server id 1  end_log_pos 487 CRC32 0x120e5948     Query    thread_id=10    exec_time=0    error_code=0
use `cndba`/*!*/;
SET TIMESTAMP=1518066543/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!/C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into dave values(2,'oracle')
/*!*/;
# at 487
#180208 13:09:03 server id 1  end_log_pos 518 CRC32 0x50ff5147     Xid = 10
COMMIT/*!*/;
# at 518
#180208 13:09:10 server id 1  end_log_pos 560 CRC32 0x552ab324     GTID 0-1-76 trans
/*!100001 SET @@session.gtid_seq_no=76*//*!*/;
BEGIN
/*!*/;
# at 560
#180208 13:09:10 server id 1  end_log_pos 663 CRC32 0x38812230     Query    thread_id=10    exec_time=0    error_code=0
SET TIMESTAMP=1518066550/*!*/;
insert into dave values(2,'oracle')
/*!*/;
# at 663
#180208 13:09:10 server id 1  end_log_pos 694 CRC32 0x4c4f9508     Xid = 12
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@www.cndba.cn/dave ~]#

注:

server id 1 数据库主机的服务号；
at 663 表示开始的pos点
end_log_pos 663 pos点
thread_id=10 线程号

按位置过滤查看binlog的内容：

[root@www.cndba.cn/dave ~]# mysqlbinlog --no-defaults --start-position=560 --stop-position=663 
DELIMITER /*!*/;
# at 4
#180208 12:34:21 server id 1  end_log_pos 256 CRC32 0x5e150d29     Start: binlog v 4, server v 10.2.12-MariaDB-log created 180208 12:34:21 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
# at 560
#180208 13:09:10 server id 1  end_log_pos 663 CRC32 0x38812230     Query    thread_id=10    exec_time=0    error_code=0
use `cndba`/*!*/;
SET TIMESTAMP=1518066550/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!/C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into dave values(2,'oracle')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@www.cndba.cn/dave ~]#

按时间过滤查看binlog的内容：http://www.cndba.cn/dave/article/2645

[root@www.cndba.cn/dave ~]# mysqlbinlog --no-defaults --start-datetime='2018-02-08 13:09:03' --stop-datetime='2018-02-08 13:09:10' /mysql/data/mysql-bin.000020
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180208 12:34:21 server id 1  end_log_pos 256 CRC32 0x5e150d29     Start: binlog v 4, server v 10.2.12-MariaDB-log created 180208 12:34:21 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TdN7Wg8BAAAA/AAAAAABAAABAAQAMTAuMi4xMi1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABN03taEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgEpDRVe
'/*!*/;
# at 342
#180208 13:09:03 server id 1  end_log_pos 384 CRC32 0x8d423d5f     GTID 0-1-75 trans
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=75*//*!*/;
BEGIN
/*!*/;
# at 384
#180208 13:09:03 server id 1  end_log_pos 487 CRC32 0x120e5948     Query    thread_id=10    exec_time=0    error_code=0
use `cndba`/*!*/;
SET TIMESTAMP=1518066543/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!/C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into dave values(2,'oracle')
/*!*/;
# at 487
#180208 13:09:03 server id 1  end_log_pos 518 CRC32 0x50ff5147     Xid = 10
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@www.cndba.cn/dave ~]#

小插曲：
MariaDB mysqlbinlog 出现read_log_event(): ‘Found invalid event in binary log’ 错误解决方法
http://www.cndba.cn/dave/article/2643

MariaDB character-set-server 参数导致数据库无法启动
http://www.cndba.cn/dave/article/2642 http://www.cndba.cn/dave/article/2645

5.2 直接在数据库中查询

使用mysqlbinlog的办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，可以在数据库中直接使用命令更方便的查询：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
选项解析：
1. IN 'log_name'   指定要查询的binlog文件名(不指定就是第一个binlog文件)
2. FROM pos        指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
3. LIMIT [offset,] 偏移量(不指定就是0)
4. row_count       查询总条数(不指定就是所有行)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     28964 |
……
| mysql-bin.000019 |       365 |
| mysql-bin.000020 |       694 |
+------------------+-----------+
20 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000020';
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------+
| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                             |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------+
| mysql-bin.000020 |   4 | Format_desc       |         1 |         256 | Server ver: 10.2.12-MariaDB-log, Binlog ver: 4   |
| mysql-bin.000020 | 256 | Gtid_list         |         1 |         299 | [0-1-74]                                         |
| mysql-bin.000020 | 299 | Binlog_checkpoint |         1 |         342 | mysql-bin.000020                                 |
| mysql-bin.000020 | 342 | Gtid              |         1 |         384 | BEGIN GTID 0-1-75                                |
| mysql-bin.000020 | 384 | Query             |         1 |         487 | use `cndba`; insert into dave values(2,'oracle') |
| mysql-bin.000020 | 487 | Xid               |         1 |         518 | COMMIT /* xid=10 */                              |
| mysql-bin.000020 | 518 | Gtid              |         1 |         560 | BEGIN GTID 0-1-76                                |
| mysql-bin.000020 | 560 | Query             |         1 |         663 | use `cndba`; insert into dave values(2,'oracle') |
| mysql-bin.000020 | 663 | Xid               |         1 |         694 | COMMIT /* xid=12 */                              |
+------------------+-----+-------------------+-----------+-------------+--------------------------------------------------+
9 rows in set (0.00 sec)

mysql>

也可以加上/G以列模式查看：

show binlog events in 'mysql-bin.000020'/G

其他类似命令示例：

A.查询第一个(最早)的binlog日志：
mysql> show binlog events/G; 

B.指定查询 mysql-bin.000021 这个文件：
mysql> show binlog events in 'mysql-bin.000021'/G;

C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：
mysql> show binlog events in 'mysql-bin.000021' from 8224/G;

D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10/G;

E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10/G;

6 删除binlog日志

当开启mysql数据库主从后，会产生大量如mysql-bin.00000* log的文件，这会大量耗费您的硬盘空间。有三种解决方法：
1.关闭mysql主从，关闭binlog；
2.开启mysql主从，设置expire_logs_days；
3.手动清除binlog文件，

6.1 方法1: PURGE MASTER LOGS：

语法：
PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除。

删除mysql-bin.000002之前的日志（不包括mysql-bin.000002）:

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

删除2018-02-07 11:53:59时间点之前的日志

mysql> PURGE BINARY LOGS BEFORE '2018-02-07 11:53:59';  
Query OK, 0 rows affected (0.01 sec)

注意事项：http://www.cndba.cn/dave/article/2645

在删除binlog日志同时，也会清理mysql-bin.index的文件记录，清理完后命令中指定的日志文件成为第一个。
主从架构下，如果复制正在进行中，执行该命令是安全的，例如slave正在读取我们要删除的log，该语句将什么也不会做，并返回一个错误；如果复制是停止的，我们删除了一个slave还未读取的日志，则复制重新建立连接时将会失败。

建议操作步骤：
1) 在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
2) 使用SHOW MASTER LOGS获得主服务器上的一系列日志。
3) 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是最新的，这是清单上的最后一个日志。
4) 备份您将要删除的所有日志。（这个步骤是自选的，但是建议采用。）
5) 清理除目标日志之外的所有日志。

6.2 方法2: 手动删除binlog日志文件

手动删除binlog日志文件http://www.cndba.cn/dave/article/2645

[root@www.cndba.cn/dave data]# rm -rf mysql-bin.00009

更新索引文件中的记录：

[root@www.cndba.cn/dave data]# vim mysql-bin.index 
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017
./mysql-bin.000018
./mysql-bin.000019
./mysql-bin.000020

6.3 方法3: 指定过期天数（expire_logs_days）

mysql> show variables like 'expire_logs_days'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.01 sec)

临时修改过期时间

mysql> set global expire_logs_days = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 10    |
+------------------+-------+
1 row in set (0.00 sec)

修改/etc/my.cnf 增加参数

expire_logs_days=10

重启mysql 服务生效

刷新日志，触发日志清除：

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

注意事项
在主从复制环境下，应确保过期天数不应小于从机追赶主机binlog日志的时间。

触发过期删除的条件：
每次进行 log flush的时候会自动删除过期的日志。

触发log flush的条件为：

重启mysql；
BINLOG文件大小达到参数max_binlog_size限制；
手工执行命令。

6.4 方法4: RESET MASTER;

该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个以.000001为后缀新的二进制日志文件。
该语法一般只用在主从环境下初次建立复制时。
在主从复制进行过程中，该语句是无效的。

mysql> reset master;
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.00 sec)

这里有个小插曲，参考：
MariaDB reset master后binlog 并不从000001开始
http://www.cndba.cn/dave/article/2644

6.5 清除binlog时，对从mysql的影响

如果有一个活性的从属服务器，该服务器当前正在读取试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。

7 关闭binlog

修改配置文件，注释掉binlog的相关日志即可：

[root@www.cndba.cn/dave data]# cat /etc/my.cnf | grep -E 'log[_-]bin|binlog_format|expire_logs_days'
expire_logs_days=10
log-bin=mysql-bin
#log-bin=mysql-bin
#binlog_format=mixed

注释后然后重启MariaDB即可。

签到成功

CNDBA社区