mysqlbinlog浅析

mysqlbinlog浅析

在mysql中，当我们开启了二进制日志，任意时间对数据库所做的更改，都会被记录到日志文件中，例如当我们添加了一个表或者update了一条数据，这些时间都会被存储到mysqlbinlog中，之前对于二进制文件的理解不够透彻，今天在线上处理一个问题的时候，重新对二进制日志有了更加深刻的认知。

今天线上的问题是这样的，业务方的一个同学说某个服务的业务操作结果有些出入，存在一些未知的表数据变更，想要DBA帮忙查找到底是什么时候发生的数据变更，目前知道的结果只有大概的时间以及数据库名称和服务IP地址。接到这个需求的时候，当时我正在执行几个线上的存储过程，第一反应是大概二进制日志查找就行了，于是按照这个大概的变更发生时间去查找对应的二进制日志文件，这一查，发现了几个比较严重的问题，最致命的一个就是这个数据库写的速度相当快，1s的时间，在二进制日志中大概要产生几百行的记录，这样要想定位到变更发生的时间简直不可能。而且由于binlog比较大，大概1G左右，在binlog文件当中搜索某个关键字的速度相当慢，等待了一会儿，实在是无法忍受这种速度了，于是mysqlbinlog --help了一下，看到了很多有用的参数，这里挑选几个常用的列举出来：

--start-datetime

这个参数可以确定binlog的起始时间戳。

--stop-datetime

这个参数可以确定binlog的终止时间戳。

--database

这个参数可以在binlog中查询指定数据库的log日志

--start-position

这个参数可以确定binlog的偏移量起始值

--stop-position

这个参数可以确定binlog的偏移量终止值

--host=name

获取binlog的服务器IP地址或者域名

-v, --verbose

重新构建伪SQL语句的行信息输出，

-vv

会增加列类型的注释信息。

-u, --user=name

连接远程服务器的用户名。

看到了--start-datetime这个参数，于是改变了相关的思路，利用时间范围先将特定的二进制日志记录打印出来：

mysqlbinlog -vv --start-datetime='2019-02-25 16:10:00'

--stop-datetime='2019-02-25 16:20:48' mysqlbin.000761 >/tmp/a.log

这个a.log大概有20M，这就把1G的binlog解析成为了20M的可控的log文件，然而这个log文件，对于业务方来讲还是很大，可能他只关心其中某个时间段的update操作，这里通过管道的方式给出update相关的语句即可：

cat a.log|grep update > update.log

这个update.log当中只有大概20行左右的数据，这就非常清晰了。

2关于mysqlbin.index

在MySQL的复制过程中，经常需要打开binlog，而binlog在创建的时候，我们会看到一个mysqlbin.index的文件，这个文件里面的内容如下：

[root@ innodblog]# ll
total 
-rw-r--r--  root  root   Feb  : 1.txt
-rw-r--r--  root  root     Feb  : aa.log
-rw-r--r--  root  root   Feb  : a.tmp
-rw-r--r--  root  root         Feb  : c.log
-rw-r-----  mysql mysql  Feb  : ib_logfile0
-rw-r-----  mysql mysql  Feb  : ib_logfile1
-rw-r-----  mysql mysql  Feb  : mysqlbin.000759
-rw-r-----  mysql mysql  Feb  : mysqlbin.000760
-rw-r-----  mysql mysql  Feb  : mysqlbin.000761
-rw-r-----  mysql mysql   Feb  : mysqlbin.000762
-rw-r-----  mysql mysql    Feb  : mysqlbin.000763
-rw-r-----  mysql mysql         Feb  : mysqlbin.index
[root@ innodblog]# cat mysqlbin.index 
/data/mysql_4306/innodblog/mysqlbin.000759
/data/mysql_4306/innodblog/mysqlbin.000760
/data/mysql_4306/innodblog/mysqlbin.000761
/data/mysql_4306/innodblog/mysqlbin.000762
/data/mysql_4306/innodblog/mysqlbin.000763

可以看到这个文件中记录的是mysql的binlog的具体位置，这个路径在我们切换数据库服务的端口的时候，它也需要随之更改，否则服务无法启动，之前在搭建MGR的时候，发现这个问题，当时是把mysql的服务从4308改到4306端口，重新启动的时候，报错如下：

 :: mysqld_safe mysqld from pid file /data/mysql/AY14020816093477605eZ.pid ended140726 :: 
mysqld_safe Starting mysqld daemon with databases from /data/mysql/usr/local/mysql/bin/mysqld: 
File ‘./mysql-bin.index' not found (Errcode: 13)140726  0:31:19 
[ERROR] Aborting140726  0:31:19 
[Note] /usr/local/mysql/bin/mysqld: Shutdown complete

提示这个mysqlbin没有找到，最后查找原因就是mysqlbin.index中的路径中包含了端口号，没有改过来导致的。

通常情况下，如果我们不做主从复制的话，binlog文件其实没有什么用处的，可以通过mysql中的reset master命令去掉，这个命令会清空所有的mysql-bin文件。