binlog
- 每当我们重启一次,会自动生成一个binlog文件
通过 flush logs,同样会新创建一个binlog文件
临时暂停binlog日志
set sql_log_bin=on/off
为什么要关闭binlogbinlog刷盘策略
sync_binlog:【binlog写入磁盘机制】
sync_binglog = 0:每次提交事务都只write(写到操作系统page cache里),不sync同步到磁盘。
- sync_binlog 设置为1,每次事务提交都刷新binlog。最安全。【5.7.7之后默认】
- n,是累计n个后提交。
设置为n,可能会丢失最近 N 个事务的 binlog 日志。
【有redo log=1为什么还要binlog=1】
双1配置
sync_binlog、 innodb_flush_log_at_trx_commit 都设置成1。【双1配置】
一个事务提交,需要两次刷盘。
一次redo_log prepare阶段。
一次binlog 刷盘。
binlog日志格式【binlog_format】
- ① STATEMENT模式(SBR)
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)- ② ROW模式(RBR)【默认】
不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。
复制高效。- ③ MIXED模式(MBR)
以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
【好文】关于binary log一点总结[转] - 周伯通的麦田 - 博客园
明文or二进制存储
当binlog_format=statement的时候,DDL及DML都是明文按照SQL记录存储
当binlog_format=row的时候,其他参数默认,DDL明文存储SQL脚本,DML都是加密存储且存储的是每一行的行记录修改情况
默认FULL
例子:
安全考虑binlog_row_image建议尽量使用FULL - 知乎
binlog_rows_query_log_events 【binlog中记录原sql注释】
binlog_rows_query_log_events =1
在row模式下..开启该参数,将把sql语句打印到binlog日志里面.默认是0(off);
虽然将语句放入了binlog,但不会执行这个sql,就相当于注释一样.但对于dba来说,在查看binlog的时候,很有用处.
log_slave_updates:记录同步过来的日志到binlog
用于级联复制
A>B>C实现三级同步时,AB库除了需要设置log-bin参数还需要添加一个参数:log-slave-updates
查看binlog【单位是log event】
binlog顾名思义是二进制的,通过pos定义一个event。
方法一:show binlog events
查看最新的binlog文件:
下面是binlog format = statement才显示SQL语句的。
row格式可以显示部分:
方法二:mysqlbinlog解析binlog
- -v:反解析二进制日志
- —start-position:开始位置【需要精确】
- —stop-position:结束位置
- —start-datetime:可通过start datetime查找position【不需要精确】
- base64-output=DECODE-ROWS:将原本base64的行数据解析为可见。
查看详细sql:binlog_rows_query_log_events
https://www.yuque.com/arnold/note/yxv1hs#RHLHZ
恢复binlog【mysqlbinlog】
方法1:binlog转sql
方法2:直接binlog管道导入mysql
flashback
- -B:flashback
delete变insert
删除binlog
- 操作系统删除(不建议)
- reset master
- purge
- 配置过期时间:expire_logs_days,默认不启用
组提交(在多个事务并发执行的情况下,怎么保证在 binlog 和 redolog 中的顺序一致?)【多个线程多个事务的多次io合并一次io】
两阶段提交解决一个事务的一致性问题。并发如何解决一致性问题。MySQL 5.6 版本之前保证事务提交顺序,使用 prepare_commit_mutex 对整个 2PC 过程进行加锁。
细化锁粒度:
MySQL 5.6 引入了 binlog 的组提交(group commit)功能,prepare 阶段不变,只针对 commit 阶段,将 commit 阶段拆分为三个过程:
- flush stage:多个线程按进入的顺序将 binlog 从 cache 写入文件(多次write,不刷盘);
- sync stage:对 binlog 文件做 fsync 操作(多个线程的 binlog 合并一次刷盘);
- commit stage:各个线程按顺序做 InnoDB commit 操作。
5.7组提交优化:
redolog Prepare阶段拆分。
推迟redo log flush到组提交flush阶段
优化后:
- flush阶段:所有redolog刷盘+ binlog write
- 最后一次redolog commit只需要write不用fsync,即使丢失也算事务提交成功。
原来两次fsync,通过组提交减少了fsync(变为1次fsync?)(多线程多事务一起fsync)
- binlog_group_commit_sync_delay 参数,表示延迟多少微秒后才调用 fsync;
- binlog_group_commit_sync_no_delay_count 参数,表示累积多少次事务以后才调用 fsync
这俩参数不用调优。
先满足sync_binlog,然后再判断是否满 足binlog_group_commit_sync_delay或binlog_group_commit_sync_no_delay_count。 23 | MySQL是怎么保证数据不丢的?