mysql binlog的应用

2019-06-26 本文已影响0人 ZMRWEGo

binlog 就是binary log，二进制日志文件，这个文件记录了mysql所有的dml操作。通过binlog日志我们可以做数据恢复，做主主复制和主从复制等等。

1.开启mysql bin_log日志

mysql默认是打开慢查询日志(默认超过10s为慢查询)，但是bin_log默认是关闭的，我们需要首先在(windows下，linux相似)在my.ini下添加如下配置

server-id=11           #由于bug,所以需要设置该参数.否则无法启动mysql实例
log-bin = mysql-bin    #其中mysql-bin代表的是basename就是生成二进制日志文件的前缀部分,默认的位置在datadir目录下,也可以设置为其他的路径

之后我们需要重启服务 net restart MYSQL(这里填写你自己的服务名)

查看binlog相关系统参数
show variables like '%log_bin%'

查看所有的二进制日志
show binary logs

查看当前正在使用的二进制文件
show master status

查看日志详情
SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,]row_count]

+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000001 | 219 | Query          |         1 |         296 | BEGIN                                 |
| mysql-bin.000001 | 296 | Table_map      |         1 |         356 | table_id: 110 (employees.score)       |
| mysql-bin.000001 | 356 | Delete_rows    |         1 |         406 | table_id: 110 flags: STMT_END_F       |
| mysql-bin.000001 | 406 | Xid            |         1 |         437 | COMMIT /* xid=34 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)

2. binlog的其他设置

实时将缓存中数据同步到硬盘中
sync_binlog
sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0)，MySQL 在每写 N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

（如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作；其实还是对应于每一个事务，事务提交时进行写操作）

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

定义二进制格式日志
binlog_format= Mixed|Statement|Row

1. statement模式

每一条会修改数据的sql都会记录到master的binlog中，slave在复制的时候sql进程会解析成和原来master端执行多相同的sql再执行。主要是记录sql

优点：在statement模式下首先就是解决了row模式的缺点，不需要记录每一行数据的变化减少了binlog日志量，节省了I/O以及存储资源，提高性能。因为他只需要记录在master上所执行的语句的细节以及执行语句的上下文信息。

缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

2. row模式

默认情况下，binlog为row模式。
优点：在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出发无法被正确复制问题。
缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

3.mixed模式

智能判断使用row模式还是statement模式

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：日志刷盘
如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。
上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。