如何进行的MySql主备一致是基于binlog日志的,为啥使用binlog,
首先说下基本的MySQL的主备切换的流程
在状态1,客户端的读写都是访问的节点A,节点B是A的备库,只是将A的更新同步过来
切换成了状态2,就是B为master,客户端读写访问的都是节点B,节点A是B的备库
在主备这种设计下
建议将备库设置为read only,主要考虑为
1.对于某些内部的查询会在备库上查询,设置为只读避免出现错误
2.避免在切换过程中出现双写的情况
3.用于利用readonly状态来判断节点的角色
而且设置为了readonly,仍然可以做到同步更新,因为是超级权限用户
整体的一个同步流程如上
1.在备库B上通过change master命令,设主库A的IP 端口 用户名 密码 以及binlog的起始地址和日志偏移量
2.备库上执行start slave,备库启动了两个线程,一个是io_thread 一个是sql_thread,备库通过io_thread和主库建立了连接
3,主库A检验完成了,读取本地binlog文件,然后发送给B
4,B拿到了binlog文件,写到本地文件 称为relay log
5.sql_thread读取中转日志,分析并执行
根据binlog的格式不同,传递的方式也不同
分为 statement row mixed
对于一条语句,不同的格式如何存储和传递的?
delete from t where a>=4 and t_modified <= ‘2018-11-10’ limit 1;
对于statement格式,记录的就是binlog里面记录的是SQL语句的原文,可以用
mysql>show binlog events in ‘master.000001’; 来查看binlog中的内容
第一行为了主备切换而准备
第二行表示事务的开始
第三行表示真实的执行语句,前面的use ‘test’是为了保证能够直接找到test库
然后是Sql语句的原文
然后是commit,后面跟着xid是为了崩溃恢复的时候找到对应的事务
接下来是
warning,出现了警告信息,主要原因是binlog设置为了statement格式的,并且带有limit,导致出现了不安全的问题
为什么会出现这个情况,是因为delete语句使用的索引a,也就是会找到第一个满足条件的行,也就是a=4
但如果使用了t_modified,那么删除的就是t_modified,那么删除的就是 t_modified=’2018-11-19’也就是a=5这一行
这就是statement格式下,因为记录的sql原语句,那么会出现索引选择不对的问题,于是可能是不安全的
对于row格式
statement格式和row格式相比较
BEGIN和COMMIT一样但是这里记录的是Table_map和Delet_rows
Table_map表示的是操作的是test库的t表,
Delete_rows event定义的是删除的行为
对于row格式的binlog,我们通过mysqlbinlog该工具测试
mysqlbinlog -vv data/master/000001 –start-position=8900;(vv表示显示出所有字段的内容)
翻译过来如下:
首先是server_id 表明执行的库
然后Table_map表示了打开的表
在binlog_row_image中包含了删除的所有行的字段信息
这里面是指记录了id =4 说明设置的是MINIMAL
这样能够确保在主备同步的时候,肯定删除了id=4的一行
最后是mixed格式的binlog
这就是为了避免上面可能出现的主备不一致的问题而准备的
而且row格式很占空间,如果是statement格式下删除很多数据,那么可能一条语句就OK了
如果是row格式下,删除,则可能出现了记录很多行的数据,占用更大的空间,占用IO资源
于是有了mixed格式的binlog,为了避免主备不一致
也就是说,如果删除出现了limit这种情况,那么会记录为row,不然会记录为statement格式
当然设置为row格式的binlog在现在来说,被设置的更为广泛
因为在恢复数据的时候,更为方便
如果是delete语句,在row中,会将整个行的所有信息保存下来,可以快速的构建出一个insert语句
如果是insert语句,那更加不必说
如果是update语句的话 那么也可以根据修改前的数据和修改后的整行数据来进行恢复
binlog日志还可以用于日志回滚工具
在MariaDB版本中Flashback工具,就是基于的上面的原理来回滚的
这里说明一个简单的问题
如果使用了mixed格式,执行如下的语句
insert into t values(10,10,now());
是使用了statement还是row格式
发现采用了statement格式来存储
那么如果MySql直接传递这个语句,会导致主备的数据不一致吗
因为now()函数仍然去当前的时间为基准啊
但是可以放心的是,在实际的记录上
会记录上当前的时间戳
方便备库利用时间戳来恢复
顺便一提,就是直接执行mysqlbinlog中的statement语句,可能是有风险的,比较保险的还是用mysqlbinlog解析出来,然后将解析的行发给MySql执行
msqlbinlog master.00001 –start-position=2738 –stop-position=2973 |mysql
其次就是循环复制问题
现在很多MySQl中,并不是采用的M-S结构,而是更多的使用了双M结构,这样在两个库之间切换,不用切换M/S关系
但是有一个问题,因为双方都生成了
binlog日志,那么如何保证主库不会拿从库的binlog在复制一遍呢
解决方案很简单,就是每次生成binlog的时候,记录自己的server id
一个备库接到binlog并且重放的时候,会检测这个日志的server id,如果和自己的server id相同,就直接跳过binlog
按照这个逻辑,就可以变成如下的执行流程
1.从节点A更新的事务,binlog中记录了A的server id
2.传到节点B再执行一次,节点B生成的binlog的server id就是A的server id
3.再传给A,A判断server id相同,切断循环
除非,在主库更新事务之后,修改了server_id,使用了命令set global server_id=x,导致双M双方发现都不是自己的serverid
还有就是如下的场景
