MySQL binlog

binlog的作用1.可以查看mysql变更记录的是数据库内部的所有变动 update del alter  不记录select show将数据库的改变以二进制的形式写入到mysql的binlog内部2.mysql的复制架构（主从）3.mysql备份恢复（可以通过binlog进行数据的补齐或者回滚）记录了mysql的变化，重复应用binlog，可以复现mysql之前的情况

binlog的位置[mysqld]log_bin = home/mysql3306/mysql3306/mysql-bin#binlog路径+binlog前缀名 如：mysql-bin.000001

binlog的生成方式1、每次启动mysql的时候都会生成新的binlog2、mysql服务器执行flush logs，可以产生新的

bin_log的参数

log-bin决定了msyql 的binlog的名字，生成的binlog名字为mysql-bin.000001

开启关闭

sql_log_bin =on开启关闭

binlog_cache_size = 4Msession级别的参数，会话级别，设置不宜过大，否则容易OOM#线程级参数，不能设置太大

max_binlog_cache_size = 8G（在线参数）#全局级参数，最大的binlog cache不能超过max_binlog_cache_size。就会报错：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage主库的max_binlog_cache_size比从库大，导致主库的事务，在从库报错。

max_binlog_size   = 1024M（有可能大于）内存落盘binlog的时候，binlog文件的最大的大小落盘如果在达到max后，事务没有完成，那么binlog会等事务完成后，并且binlog完全落盘后，再进行切换；max_binlog_size   = 1024M（有可能大于）内存落盘binlog的时候，binlog文件的最大的大小落盘如果在达到max后，事务没有完成，那么binlog会等事务完成后，并且binlog完全落盘后，再进行切换。

binlog_ignore_db此参数表示不记录指定的数据库的二进制日志

expire_logs_days过期时间

binlog_do_db此参数表示只记录指定数据库的二进制日志

sync_binlog (binlog落盘策略)0---每一秒刷一次磁盘1---每次事务提交都刷一次磁盘n---（100 200 500）每n次提交落盘一次innodb_flush_log_at_trx_commit =1sync_binlog=1双1配置，数据库的安全性是最高的，不会丢事务。

binlog格式1.statementbinlog格式1.statement

在该格式下，binlog记录的是SQL语句。优点：占用体积比较小，容易传输缺点：有可能主库的某些sql，执行到库后，会产生不一致的结果

主从的时候，有可能造成数据库的主从的不一致sysdate()----->sysdate()uuid-----uuid主从的时候，有可能造成数据库的主从的不一致sysdate()----->sysdate()uuid-----uuid

2.row记录mysql内部的数据行变化的binlog格式优点：不会产生statment的问题，最安全的binlog格式缺点：会产生比较大IO和网络压力

3.mixed普通情况下，使用statement，特殊情况使用row格式的binlog如果主从因为带宽问题，造成了延迟，可以考虑使用mixed格式进行优化

sync_binlog (binlog落盘策略)0---每一秒刷一次磁盘1---每次事务提交都刷一次磁盘n---（100 200 500）每n次提交落盘一次innodb_flush_log_at_trx_commit =1sync_binlog=1双1配置，数据库的安全性是最高的，不会丢事务。

清理过期binlog日志手工删除binlogmysql> reset master; 删除master的binlogmysql> reset slave; 删除slave的中继日志mysql> purge master logs before '2012-03-30 17:20:00'; 删除指定日期以前的日志索引中binlog日志文件mysql> purge master logs to 'binlog.000002'; 删除指定日志文件的日志索引中binlog日志文件或者直接用操作系统命令直接删除

自动删除binlog通过binlog参数（expire_logs_days ）来实现mysql自动删除binlogshow binary logs;show variables like 'expire_logs_days';set global expire_logs_days=3;

set sql_log_bin=1/0; 如果用户有super权限，可以启用或禁用当前会话的binlog记录show master logs; 查看master的binlog日志show binary logs; 查看master的binlog日志show master status; 用于提供master二进制日志文件的状态信息show slave hosts; 显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中

查看binlog

show binlog events;

Log_name： binlog文件名Pos：binlog的position点，写入binlog的日志的字节数，就是pos的大小，在每次新生成binlog的时候，pos点会重置，但是在一个binlog内部，这个pos是单调递增的。xid：分布式事务ID，Innodb支持分布式事务，Innodb支持的单实例内部的，server层和存储引擎层的分布式事务，还有多节点分布式事务（ndb）Server_id：实例server_id解析binlog工具/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > tmp/all.bin

Row格式 reset master之后，操作如下

如果想倒回到delete之前，只需要将这部分注释掉

再应用就恢复到delete之前的数据

mysqlbinlog -vv  mysql-bin.000001 > /tmp/all.bin
#180308  0:47:19 server id 15133306  end_log_pos 583 CRC32 0xcfededc0   Write_rows: table id 157 flags: STMT_END_F
BINLOG '
lxegWhN66uYAMQAAABsCAAAAAJ0AAAAAAAEABXRlc3QxAAJ0NAACAw8CLAACi4yOcg==
lxegWh566uYALAAAAEcCAAAAAJ0AAAAAAAEAAgAC//wBAAAAA214dMDt7c8=
'/*!*/;
### INSERT INTO `test1`.`t4`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='mxt' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
# at 583

解析不带行数据
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000004
如果binlog解析出来需要恢复到数据库，那么需要注意不要加--base64-output=DECODE-ROWS。
start-datetime  开始时间
stop-datetime   停止时间

start-position  开始时间的position
stop-position   停止时间的positionstart-position  开始时间的position
stop-position   停止时间的position

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2017-04-20 9:00:00" --stop-datetime="2017-07-20 18:00:00"/home/mysql3306/mysql3306/mysql-bin.000001 > /tmp/mysql_binlog.sql