MySQLBinlog介绍

　　Binlog简介
　　MySQL中一般有以下几种日志：
　　日志类型
　　写入日志的信息
　　错误日志
　　记录在启动，运行或停止mysqld时遇到的问题
　　通用查询日志
　　记录建立的客户端连接和执行的语句
　　二进制日志
　　记录更改数据的语句
　　中继日志
　　从复制主服务器接收的数据更改
　　慢查询日志
　　记录所有执行时间超过longquerytime秒的所有查询或不使用索引的查询
　　DDL日志（元数据日志）
　　元数据操作由DDL语句执行
　　本文主要介绍二进制日志binlog。
　　MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog的主要目的是复制和恢复。Binlog日志的两个最重要的使用场景MySQL主从复制：MySQLReplication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到masterslave数据一致的目的数据恢复：通过使用mysqlbinlog工具来使恢复数据启用Binlog
　　注：笔者实验的MySQL版本为：5。7。22
　　一般来说开启binlog日志大概会有1的性能损耗。
　　启用binlog，通过配置etcmy。cnf或etcmysqlmysql。conf。dmysqld。cnf配置文件的logbin选项：
　　在配置文件中加入logbin配置，表示启用binlog，如果没有给定值，写成logbin，则默认名称为主机名。（注：名称若带有小数点，则只取第一个小数点前的部分作为名称）〔mysqld〕logbinmybinlogname
　　也可以通过SETSQLLOGBIN1命令来启用binlog，通过SETSQLLOGBIN0命令停用binlog。启用binlog之后须重启MySQL才能生效。常用的Binlog操作命令是否启用binlog日志showvariableslikelogbin；查看详细的日志配置信息showglobalvariableslikelog；mysql数据存储目录showvariableslikedir；查看binlog的目录showglobalvariableslikelogbin；查看当前服务器使用的biglog文件及大小showbinarylogs；查看主服务器使用的biglog文件及大小查看最新一个binlog日志文件名称和Positionshowmasterstatus；事件查询命令INlogname：指定要查询的binlog文件名（不指定就是第一个binlog文件）FROMpos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）LIMIT〔offset，〕：偏移量（不指定就是0）rowcount：查询总条数（不指定就是所有行）showbinlogevents〔INlogname〕〔FROMpos〕〔LIMIT〔offset，〕rowcount〕；查看binlog内容showbinlogevents；查看具体一个binlog文件的内容（in后面为binlog的文件名）showbinlogeventsinmaster。000003；设置binlog文件保存事件，过期删除，单位天setglobalexpirelogdays3；删除当前的binlog文件resetmaster；删除slave的中继日志resetslave；删除指定日期前的日志索引中binlog日志文件purgemasterlogsbefore2019030914：00：00；删除指定日志文件purgemasterlogstomaster。000003；写Binlog的时机
　　对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog什么时候刷新到磁盘跟参数syncbinlog相关。如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；如果设置为不为0的值，则表示每syncbinlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。
　　如果syncbinlog0或syncbinlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。
　　在MySQL5。7。7之前，默认值syncbinlog是0，MySQL5。7。7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。Binlog文件以及扩展
　　binlog日志包括两类文件：二进制日志索引文件（文件名后缀为。index）用于记录所有有效的的二进制文件二进制日志文件（文件名后缀为。00000）记录数据库所有的DDL和DML语句事件
　　binlog是一个二进制文件集合，每个binlog文件以一个4字节的魔数开头，接着是一组Events：魔数：0xfe62696e对应的是0xfebin；Event：每个Event包含header和data两个部分；header提供了Event的创建时间，哪个服务器等信息，data部分提供的是针对该Event的具体信息，如具体数据的修改；第一个Event用于描述binlog文件的格式版本，这个格式就是event写入binlog文件的格式；其余的Event按照第一个Event的格式版本写入；最后一个Event用于说明下一个binlog文件；binlog的索引文件是一个文本文件，其中内容为当前的binlog文件列表
　　当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：MySQL服务器停止或重启时使用flushlogs命令；当binlog文件大小超过maxbinlogsize变量的值时；
　　maxbinlogsize的最小值是4096字节，最大值和默认值是1GB（1073741824字节）。事务被写入到binlog的一个块中，所以它不会在几个二进制日志之间被拆分。因此，如果你有很大的事务，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的日志都记录到当前日志文件中，直到事务结束，你可能会看到binlog文件大于maxbinlogsize的情况。Binlog的日志格式
　　记录在二进制日志中的事件的格式取决于二进制记录格式。支持三种格式类型：STATEMENT：基于SQL语句的复制（statementbasedreplication，SBR）ROW：基于行的复制（rowbasedreplication，RBR）MIXED：混合模式复制（mixedbasedreplication，MBR）
　　在MySQL5。7。7之前，默认的格式是STATEMENT，在MySQL5。7。7及更高版本中，默认值是ROW。日志格式通过binlogformat指定，如binlogformatSTATEMENT、binlogformatROW、binlogformatMIXED。Statement
　　每一条会修改数据的sql都会记录在binlog中
　　优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高了性能。
　　缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。Row
　　5。1。5版本的MySQL才开始支持rowlevel的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。
　　优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
　　缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
　　注：将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如CREATETABLE，ALTERTABLE，或DROPTABLE。Mixed
　　从5。1。8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
　　在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。mysqlbinlog命令的使用
　　服务器以二进制格式将binlog日志写入binlog文件，如何要以文本格式显示其内容，可以使用mysqlbinlog命令。mysqlbinlog的执行格式mysqlbinlog〔options〕logfile。。。查看binlog二进制文件（shell方式）mysqlbinlogvbase64outputdecoderowsvarlibmysqlmaster。000003查看binlog二进制文件（带查询条件）mysqlbinlogvbase64outputdecoderowsvarlibmysqlmaster。000003startdatetime2019030100：00：00stopdatetime2019031000：00：00startposition5000stopposition20000
　　设置日志格式为ROW时，在我的机器上输出了以下信息！50530SETSESSION。PSEUDOSLAVEMODE1；！50003SETOLDCOMPLETIONTYPECOMPLETIONTYPE，COMPLETIONTYPE0；DELIMITER！；at419030810：05：03serverid1endlogpos123CRC320xff02e23dStart：binlogv4，serverv5。7。22logcreated19030810：05：03Warning：thisbinlogiseitherinuseorwasnotclosedproperly。at12319030810：05：03serverid1endlogpos154CRC320xb81da4c5PreviousGTIDs〔empty〕at15419030810：05：09serverid1endlogpos219CRC320xfb30d42cAnonymousGTIDlastcommitted0sequencenumber1rbronlyyes！50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED！；SETSESSION。GTIDNEXTANONYMOUS！；at219。。。。。。at2101919030810：10：09serverid1endlogpos21094CRC320x7a405abcQuerythreadid113exectime0errorcode0SETTIMESTAMP1552011009！；BEGIN！；at2109419030810：10：09serverid1endlogpos21161CRC320xdb7a2b35Tablemap：maxwell。positionsmappedtonumber110at2116119030810：10：09serverid1endlogpos21275CRC320xec3be372Updaterows：tableid110flags：STMTENDFUPDATEmaxwell。positionsWHERE112master。0000033202624NULL5maxwell6NULL71552011005707SET112master。0000033209234NULL5maxwell6NULL71552011009790at2127519030810：10：09serverid1endlogpos21306CRC320xe6c4346dXid13088COMMIT！；SETSESSION。GTIDNEXTAUTOMATICaddedbymysqlbinlog！；DELIMITER；Endoflogfile！50003SETCOMPLETIONTYPEOLDCOMPLETIONTYPE；！50530SETSESSION。PSEUDOSLAVEMODE0；
　　截取其中的一段进行分析：at2101919030810：10：09serverid1endlogpos21094CRC320x7a405abcQuerythreadid113exectime0errorcode0SETTIMESTAMP1552011009！；BEGIN！；
　　上面输出包括信息：position：位于文件中的位置，即第一行的（at21019），说明该事件记录从文件第21019个字节开始timestamp：事件发生的时间戳，即第二行的（19030810：10：09）serverid：服务器标识（1）endlogpos表示下一个事件开始的位置（即当前事件的结束位置1）threadid：执行该事件的线程id（threadid113）exectime：事件执行的花费时间errorcode：错误码，0意味着没有发生错误type：事件类型QueryBinlog事件类型
　　binlog事件的结构主要有3个版本：v1：在MySQL3。23中使用v3：在MySQL4。0。2到4。1中使用v4：在MySQL5。0及以上版本中使用
　　现在一般不会使用MySQL5。0以下版本，所以下面仅介绍v4版本的binlog事件类型。binlog的事件类型较多，本文在此做一些简单的汇总
　　事件类型
　　说明
　　UNKNOWNEVENT
　　此事件从不会被触发，也不会被写入binlog中；发生在当读取binlog时，不能被识别其他任何事件，那被视为UNKNOWNEVENT
　　STARTEVENTV3
　　每个binlog文件开始的时候写入的事件，此事件被用在MySQL3。234。1，MYSQL5。0以后已经被FORMATDESCRIPTIONEVENT取代
　　QUERYEVENT
　　执行更新语句时会生成此事件，包括：create，insert，update，delete；
　　STOPEVENT
　　当mysqld停止时生成此事件
　　ROTATEEVENT
　　当mysqld切换到新的binlog文件生成此事件，切换到新的binlog文件可以通过执行flushlogs命令或者binlog文件大于maxbinlogsize参数配置的大小；
　　INTVAREVENT
　　当sql语句中使用了AUTOINCREMENT的字段或者LASTINSERTID（）函数；此事件没有被用在binlogformat为ROW模式的情况下
　　LOADEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL3。23版本中使用
　　SLAVEEVENT
　　未使用
　　CREATEFILEEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL4。0和4。1版本中使用
　　APPENDBLOCKEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL4。0版本中使用
　　EXECLOADEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL4。0和4。1版本中使用
　　DELETEFILEEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL4。0版本中使用
　　NEWLOADEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL4。0和4。1版本中使用
　　RANDEVENT
　　执行包含RAND（）函数的语句产生此事件，此事件没有被用在binlogformat为ROW模式的情况下
　　USERVAREVENT
　　执行包含了用户变量的语句产生此事件，此事件没有被用在binlogformat为ROW模式的情况下
　　FORMATDESCRIPTIONEVENT
　　描述事件，被写在每个binlog文件的开始位置，用在MySQL5。0以后的版本中，代替了STARTEVENTV3
　　XIDEVENT
　　支持XA的存储引擎才有，本地测试的数据库存储引擎是innodb，所有上面出现了XIDEVENT；innodb事务提交产生了QUERYEVENT的BEGIN声明，QUERYEVENT以及COMMIT声明，如果是myIsam存储引擎也会有BEGIN和COMMIT声明，只是COMMIT类型不是XIDEVENT
　　BEGINLOADQUERYEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL5。0版本中使用
　　EXECUTELOADQUERYEVENT
　　执行LOADDATAINFILE语句时产生此事件，在MySQL5。0版本中使用
　　TABLEMAPEVENT
　　用在binlogformat为ROW模式下，将表的定义映射到一个数字，在行操作事件之前记录（包括：WRITEROWSEVENT，UPDATEROWSEVENT，DELETEROWSEVENT）
　　PREGAWRITEROWSEVENT
　　已过期，被WRITEROWSEVENT代替
　　PREGAUPDATEROWSEVENT
　　已过期，被UPDATEROWSEVENT代替
　　PREGADELETEROWSEVENT
　　已过期，被DELETEROWSEVENT代替
　　WRITEROWSEVENT
　　用在binlogformat为ROW模式下，对应insert操作
　　UPDATEROWSEVENT
　　用在binlogformat为ROW模式下，对应update操作
　　DELETEROWSEVENT
　　用在binlogformat为ROW模式下，对应delete操作
　　INCIDENTEVENT
　　主服务器发生了不正常的事件，通知从服务器并告知可能会导致数据处于不一致的状态
　　HEARTBEATLOGEVENT
　　主服务器告诉从服务器，主服务器还活着，不写入到日志文件中Binlog事件的结构
　　一个事件对象分为事件头和事件体，事件的结构如下：eventtimestamp0：4headertypecode4：1serverid5：4eventlength9：4nextposition13：4flags17：2extraheaders19：x19eventfixedpartx：ydatavariablepart
　　如果事件头的长度是x字节，那么事件体的长度为（eventlengthx）字节；设事件体中fixedpart的长度为y字节，那么variablepart的长度为（eventlength（xy））字节BinlogEvent简要分析
　　从一个最简单的实例来分析Event，包括创建表，插入数据，更新数据，删除数据；CREATETABLEtest（idbigint（20）NOTNULLAUTOINCREMENT，ageint（11）DEFAULTNULL，namevarchar（255）DEFAULTNULL，PRIMARYKEY（id））ENGINEInnoDBDEFAULTCHARSETutf8；insertintotestvalues（1，22，小旋锋）；updatetestsetnamewhirlywhereid1；deletefromtestwhereid1；
　　如果感觉小编写得不错，请素质三连：点赞转发关注。我会努力写出更好的作品分享给大家。更多JAVA进阶学习资料小编已打包好，可以关注私信找我领取哦！