数据库日志

GTID

在/mysql/data/目录下面存在一个auto.cnf文件

[auto]
server-uuid=aff809d2-80bc-11e8-991b-005056a33cce

我们可以在后面将添加ip,便于之后的识别,如172.18.100.57,则修改为:

[auto]
server-uuid=aff809d2-80bc-11e8-991b-005056a33cce172-18-100-57

会话级别不产生日志

#注意: 这种模式一般不常使用,针对大表操作,不想产生日志的情况下,不过会导致不同步,需要在同步的库也执行同样的操作
set sql_log_bin = 0;
start transacation;
update ....
commit;set sql_log_bin = 1;

二进制日志

解析二进制日志需要基于完整的数据字典

  • Mysql的为Binglog
  • Oracle的为Redolog
  • Postgresql的为WAL

解析Redo与Undo

  • Redo把binlog的日志数据解析对源sql的重复执行,主要用于数据恢复
  • Undo把binlog日志解析成对源sql的反向执行,应用与回滚操作

解析工具: 使用binlog2log这个开源python工具进行

注意: 一般使用没有问题能达到99%的解析正确,但是数据库进行迁移之后会导致数据字典变化,所以这时候binlog2log会执行报错

binlog写入模式

直接关系到数据同步的效率,优化数据写入,可以优化数据同步效率

  • 普通写入(5.6之前)
  • 基于组提交的写入(大概5.7之后)- Group Commit
  • 基于写集合的写入(大概5.7.22后)-WRSET 1584151938505

上面的两条sql,后一条语句执行依赖于前面语句先执行,反例:比如id=1和id=2就没有依赖

组提交

原理:

同一时刻(维度是微秒)可能有很多事务提交,就是通过日志延迟提交的方式,将这些事务成组的提交,批量写入binlog

缺点:

1.组提交只能做到表级别,操作两个不同表的时候才能判断没有依赖,可以成为一组的,不能做到行的判断,颗粒度很大
有一些热表,可能一直在发生数据,可能就永远不能进行组提交,所以,就有后面的写集合的出现
2.有延迟,对主库生产产生微小的性能影响
3.日志风险,不立即提交,宕机之后可能导致风向,依靠redo进行风险保障

参数:

# 最大等待时间,单位微秒,默认0,范围: 0~ 1000000(即:1s)
binlog_group_commit_sync_delay = 0

# 全局动态变量,单位个数
binlog_group_commit_sync_no_delay_count

组提交日志查看

Cat binlog.log | grep last committed

日志分析时的

sequence_number 所有事务发生现后顺序数值。

last_committed=序列号: 旧组最近的一个sequence_number值,日志在应用复制时需要按顺序执行

序列号不一样表示不能在一个组,可能是因为不是在一个时间,或者不是在同一个表

序列号一样表示在一个组(是否在一组,依赖于主库的并发)

意义:

减少写日志的IO压力: 物理IO代价大,减少IO的次数,IO吞吐下降,CPU也下降

同一组事务可以在应用复制时并行执行

写集合

参数(8.0之后)

binlog_transaction_dependency_history_size

同一时刻库表列Hash值是一样的就是存在依赖,不一样就可以放在一个组里面

借鉴思路

来源: DBA提供ms脚本,用于数据库运维的基本命令的综合运行脚本

开发: 把tomcat,redis,jdk等运维操作都用脚本的形式进行简化

交流记录

  1. 日志多大和数据多大没有必然关系,主要是在于并发,在于DML等操作的频率

  2. 保守的binlog保留时间推荐: 7-10天

  3. 进行日志延迟,主库的日志一天之后才能到达从库,这样就是规避删库跑路的操作等恶意处理

  4. 主从都停掉之后,对比数据是否同步就是对重要的表的数量或者业务数据的总数(如余额sum)进行对比
  5. 误操作可以通过Undo日志进行恢复
  6. 现在不是gtid,切换到gtid只需要开启gtid模式就可以,建议旧的就不要切换到GTID模式
  7. gtid限制:只有这种语句有限制create table2 as select * from table1;
# 规避以上语句限制
create table like table2;

insert into table select * from table2