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

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

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

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

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

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

是时候开始学习一波 MySQL 的私人秘书 binlog 了
基于 binlog 的主从架构

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 了

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

原创文章,作者:kepupublish,如若转载,请注明出处:https://blog.ytso.com/252405.html

(0)
上一篇 2022年5月4日
下一篇 2022年5月4日

相关推荐

发表回复

登录后才能评论