MYSQL的BinLog实现主备同步

一般线上的MYSQL的数据库都会有备库， 主备库之间的同步都是使用的Bin Log日志来实现的。

备库建议设置成只读

• 备库只读的话， 它只能进行查询语句的操作 ， 不会误改数据库导致主备不一致
• 防止在备库切换成主库过程中，双写的时候出现不一致的问题
• 可以通过数据库是否只读的状态可以判断出主库和备库

bin log如何同步的

• 备库需要设置成只读，设置主库的ip和用户名密码， 以及开始同步的bin log起点偏移量
• 备库和主库之间维持长连接
• 主库有一个单独的dump_thread线程， 会在后台把日志传送给备库
• 备库在开启同步后， 会在后台开启两个线程， io-thread和sql_thread .
• io-thread 用来接收主库发送过来的日志信息， 并将其写入到本地的relay log日志文件中
• sql_thread负责读取本地的relay log日志文件， 并把日志中内容在备库重现出来。 这样就实现了数据的同步

bin log的三种格式

statement 格式

这种格式实际上保存的就是sql语句， 使用这种格式的bin log ， 在备库接收到对应的sql后，执行下理论上就会实现数据同步了。

但是这种格式会有问题。 如果主库和备库的索引结构不一样， 会导致同样的语句在主备库会有不同的执行结果。
比如：delete from t where a>=4 and t_modified<=‘2018-11-10’ limit 1;

• 主库会使用A索引来检索这个语句， 那么检索到的第一个语句值肯定是a=4的。
• 如果备库的A索引不存在， t_modified字段存在索引， 那么删除的第一个语句会是t_modified='2018-11-10’的， 但是不一定a=4.

主要原因是索引是有序的结构， sql语句在检索的时候会根据索引的顺序取出记录行。 如果同样语句在主备库使用不同的索引， 会导致取出的结果的顺序不一样， 而示例中的语句又只是删除第一行的数据， 很有可能会导致两个库删除不一样的记录。

疑问： 为何主备库的索引不一样？
因为备库可能只是用来查询的，为了方便查询，增加了索引，而这个索引在主库是没有必要的，所以就会有这个问题。 本质还是主备库的使用场景不同，所以使用的索引也会不同。

row格式

这是另外一种格式， 它在bin log中保存的是主库每次执行语句后修改的结果记录， 保存的并不是sql语句。 所以他不存在一个语句在主备不同结果的情况。

问题： 如果主库执行一个语句后修改了全表的90%的数据，那么会把90%的记录的都会写入到bin log吗？
是的， 这就是row的缺点。

mixed 混合格式

这个是为了解决上面两种格式的缺点， 使用两种格式的优点而出现的格式。

主库进行了一条修改操作后， MYSQL会自行判断这个操作会不会在主备库出现不一致的情况， 如果会就使用row格式写入到bin log中， 如果不会就使用statement格式写入到日志。

这种格式完美集合了两种格式的优缺点。

现在用的多的是row格式
statement格式是明显会有问题的， 不推荐使用，但是现在用的多的反而不是mixed 格式。

这是因为row有一个无可比拟的优势， 他会在bin log中保存有数据本身。

• 比如误删数据了， 我们可以直接在日志中把数据找出来。
• 现在还有很多的中间件也会使用bin log日志。 比如redis为了保证数据和数据库中一致性， 可以接收binlog中的数据信息，直接存入到redis中。

双主数据库

在实际的线上生产中， 双主数据库的模式也是用的很多的。 在双主数据库中，两个数据库互为主备， 互相接收对方的bin log。

双主两个数据库同时写一行数据怎么办

这种情况会出现问题， 使用双主数据库了， 那么你就要在业务上隔离开来， 比如这个表只会在A数据库上修改， 不能在B数据库上修改。 就是需要避免两个库出现双写的情况。

其实双主模式也可以在业务上对一个库作为只读 ， 是为了方便出现故障的时候，顺滑的切换到另外一个库。

如何解决循环同步复制问题

一个sql操作在A库执行了后， 会同步到B库， B库执行了后又会同步到A库， A库会再次执行吗？

答案是不会， 因为每次的事务都有一个唯一的id。 A库执行了后， 会记录下来自己的所有已执行的事务id。 当他收到同步日志后，会查看日志中的同步id， 发现这个是自己执行过的，就会丢掉。

MYSQL通过事务id来避免了循环复制的问题。

问题

• 在备库和主库延迟很大了， 备库赶不上了主库的时候，可以把备库设置sync_binlog=1000 和innodb_flush_logs_at_trx_commit=2 这表示备库会在1000个事务后才会提交bin log日志真正落盘，并且备库的redo log 在每次事务提交的时候也只是提交到文件的file cache中。
• 在大促秒杀场景开始的前， DBA会基于考虑可能会把sync_binlog 和innodb_flush_logs_at_trx_commit 设置成都不为1的情况。
• 在批量导入数据的时候，为了加快速度， 可以把sync_binlog 和innodb_flush_logs_at_trx_commit 设置成都不为1的情况。

---

（END）