是时候开始学习一波 MySQL 的私人秘书 binlog 了

一个系统，不仅要有好的代码，还要有优秀的日志记录。方便我们在出错时进行排查问题。

其实，写日志这件事，不仅是你在开发时会写。MySQL 也会写，binlog 就是 MySQL 中，我们常常会用到的日志文件了。它能帮助我们恢复数据，备份数据，是我们的好帮手。

如果你不懂 binlog，你看前面我的微信群里就有网友发生过惨案《难以置信，群里网友翻车，数据库被黑客删除！》。以及我的同事，在过年前 2 天发生惨案《泪奔，同事执行 update 语句没有添加 where 条件！》。这个时候，你就知道 binlog 是有多重要了！

binlog 日志由配置文件的 log-bin 选项负责启用，Mysql 会把用户对所有数据库的内容和结构的修改记入 xxx-bin.n 文件，而不会记录 SELECT 和没有实际更新的 UPDATE 语句。xxx 默认是 mysql-bin，可以自己修改。

binlog 简介

当 MySQL 数据库停止或重启时，服务器会把日志文件记入下一个日志文件，Mysql 会在重启时生成一个新的 binlog 日志文件，文件序号递增，此外，如果日志文件超过 max_binlog_size 系统变量配置的上限时，也会生成新的日志文件，默认 100M。

如何开启 binlog 日志

在 my.cnf 或 my.ini 中的 mysqld 下增加下面的配置：

[mysqld]
log-bin=mysql-bin
binlog_format=row

其中：log-bin 若不显示指定存储目录，则默认存储在 mysql 的 data 目录下；binlog_format 的几种格式：(STATEMENT，ROW 和 MIXED)。

• STATEMENT：基于SQL语句的复制(statement-based replication, SBR)
• ROW：基于行的复制(row-based replication, RBR)
• MIXED：混合模式复制(mixed-based replication, MBR)

当 Mysql 启动后会产生 mysql-bin.* 这样的文件，每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到max_binlog_size，mysql 自动创建新的二进制日志。如果你正使用大的事务，二进制日志还会超过 max_binlog_size：事务全写入一个二进制日志中，绝对不要写入不同的二进制日志中。

另外还有两个配置需要注意：

# expire_logs_days = 10
# max_binlog_size = 100M

• Expire_logs_days: 定义了mysql清除过期日志的时间,二进制日志自动删除的天数。默认值为0,表示没有自动删除。启动时和二进制日志循环时可能删除。
• max_binlog_size: 如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。

此外 mysqladmin flush-logs 也可以刷新 binary log。没执行一次，生成一个 binlog 文件。

查看 binlog 开启情况的语句如下：

show variables like 'log_bin%';
show variables like 'binlog%';

mysql 的 binlog 是一个二进制的文件，你打开后，可能就是 0 和 1。通常我们可以使用 mysqlbinlog 命令打开 binlog 文件，方便我们查看。

mysqlbinlog /usr/local/mysql/data/mysql-bin.000001;

执行上面这个语句后，部分显示的内容摘录如下：

# at 188
#140705 11:23:55 server id 1  end_log_pos 271   Query   thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1404573835/*!*/;
create database test
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

几个重要的元素介绍如下：

• Position:位于文件中的位置，即第一行的(#at 4)和第二行的(log_pos 4)，说明该事件记录从文件第4字节开始。  
• Timestamp:事件发生的时间戳，即第二行的(#070813 14:16:36)
• Exec_time:事件的执行花费时间
• Error_code:错误码
• Type:事件类型：
• Master ID:创建二进制事件的主机服务器ID
• Master Pos:事件在原始二进制文件中的位置
• Flags:标志信息

常用的和 binlog 日志相关的语句整理如下：

show master logs;  #查看数据库所有日志文件。
#查看master状态，即最后(最新)一个binlog日志的编号名称
#及其最后一个操作事件pos结束点(Position)值
show master status;
show binlog events;  #查看当前使用的binlog文件信息。
show binlog events in 'mysql-bin.000016';  #查看指定的binlog文件信息。
#将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。
flush logs;
reset master;  #删除所有二进制日志，并重新(mysql-bin.000001)开始记录。
#查看 master 的 logs 文件
show master logs;
#Linux 查询日志文件地址
find / -name "mysql-bin.000001"

如果你有权限访问生产环境，你就可以确定你们公司生产环境是否开启了 binlog，一般的我猜都开启了。这几天太忙了，一年 366 篇原创的目标真不容易啊。

： » 是时候开始学习一波 MySQL 的私人秘书 binlog 了