二进制日志（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%'show master status
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 MySQL [(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.000002
1 2 3 4 5 6 7 8 9 10 MySQL [(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 master
1 2 3 4 5 6 7 8 9 10 MySQL [(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 root@c5da76d1ecdc:/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 COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
这里主要关注 end_log_pos 154, 表示 binlog 当前的位置,主从复制中,从数据库就是根据这个位置完成增量复制
用 binlog 完成数据恢复 使用 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：
这种格式的优点是会记录每一行数据变化的细节，不会出现某些情况下无法复制的情况，缺点是日志量大，对 I/O 影响较大。
MIXED 这是目前 MySQL 默认的日志格式，即混合了 statement 和 row 两种日志。默认情况下采用 statement，但在一些特殊情况下采用 row 来进行记录，比如
因为这种不确定函数在主从中得到的值可能不同，导致主从数据产生不一致。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 次日志同步磁盘）一起配合使用，使得事务在日志中的记录更加安全。
