MySQL binlog数据库同步技术总结
下载网站:www.SyncNavigator.CN
客服QQ1793040
----------------------------------------------------------
关于HKROnline SyncNavigator 注册机价格的问题
HKROnline SyncNavigator 8.4.1 非破解版 注册机 授权激活教程
最近一直在研究数据库同步的问题,在网上查了很多资料,也请教了很多人,找到了一种通过快照复制的方法。研究了一番后发现之前就是用的这个方法,效果不是很好,果断放弃。经过了一番寻觅和他人指点,最后从一位热心网友那里得知一款很好用的软件—— SyncNavigator。
好东西就要拿出来跟大家分享,所以今天向大家介绍一下这款软件,及其一些使用方法。下面先看看它有什么强大的功能吧!
SyncNavigator的基本功能:
自动同步数据/定时同步数据
无论是实时同步/24小时不间断同步,还是根据计划任务(每小时/每日/每周/等)定时自动同步都能完全胜任。
完整支持 Microsoft SQL Server
完整支持 Microsoft SQL Server 2000 2005 2008 数据库类型。并能在不同数据库版本之间相互同步数据。
支持 MySQL 4.1 以上版本
支持 MySQL 4.1 5.0 5.1 5.4 5.5。并能在不同数据库版本之间相互同步数据。
无人值守和故障自动恢复
当数据库故障或网络故障以后,无需人工干预(或操作)自动恢复同步并确保数据完全准确,可靠。
同构数据库同步/异构数据库同步
SQL Server to SQL Server, MySQL to MySQL, SQL Server to MySQL 等都能轻松实现。
断点续传和增量同步
当同步完成(或中断)后,再次同步时能继续上一次的位置增量同步,避免每次都需要从头开始的问题。
binlog是MySQL以二进制形式打印的日志,它默认不加密、不压缩。每个正常的binlog文件头部有4个字节的标记,值为0xfe 0x62 0x69 0x6e(magic数字标记是binlog文件)。从第4个字节之后是一个一个的event,log_event是binlog里的单位,即正常情况下binlog按照逐log_event的形式增长。除去头部的标记,binlog就是一个log_event的序列。每个log_event都独立单元,没有互相引用的关系,它也有自己的二进制头部,主要是记录了时间戳、类型标记等描述信息。
Binlog模式分三种Row、Statement、Mixed
Row模式存储的是数据修改后的结果,binlog中可以不记录执行的sql语句的上下文相关的信息,仅仅只需要记录那一条记录被修改了,修改成什么样了。对于update mytable set col1=’abc’ where col2=’c’在row模式下可能产生大量的数据,因为语句虽然是一条,但实际影响的数据记录却可能很多。而对于alter table、drop table、create table等信息在Row模式下则不会产生大量的log条目,因为它还是记录的语句,而不是单行数据的变化情况。
Statemnet模式每一条会修改数据的sql都会记录到 master的binlog中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。由于他是记录的执行语句,所以,为了让这些语句在slave端也能正确执行,那么他还必须记录每条语句在执行的时候的一些相关信息,也就是上下文信息,以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。
Mixed模式则是前两种的混合,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。选择性的使用面向行数据变化的Row方式记录,主要是面对一些未决语句(nondeterministic),考虑到安全问题,避免主从库之间数据出现不一致,比如语句面向多行插入,其中又有auto-increment的字段,数据库存储引擎不同,可能带来插入顺序
mysql主从同步延迟原理
要了解MySQL数据库主从同步延迟原理,我们先从MySQL的数据库主从复制原理说起:
MySQL的主从复制都是单线程的操作,主库对所有DDL和DML产生的日志写进binlog,由于binlog是顺序写,所以效率很高。Slave的IO Thread线程从主库中binlog中读取取日志。Slave的SQLThread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的,不是顺序的,成本高很多。由于SQL Thread也是单线程的,如果slave上的其他查询产生lock争用,又或者一个DML语句(大事务、大查询)执行了几分钟,那么所有之后的DML会等待这个DML执行完才会继续执行,这就导致了延时。
主从同步延迟的引发原因
1、Master负载
2、Slave负载
3、网络延迟
4、机器配置(cpu、内存、硬盘)
总之,当主库的并发较高时,产生的DML数量超过slave的SQL Thread所能处理的速度,或者当slave中有大型query语句产生了锁等待那么延时就产生了。
同步延迟的解决方案
- 最简单的减少slave同步延时,尽量让主库的DDL快速执行。还有就是主库是写,对数据安全性较高,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置,而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog,innodb_flushlog也可以设置为0来提高sql的执行效率,从库关闭binlog记录。另外就是使用比主库更好的硬件设备作为slave。
- 在业务架构上将不同的业务分布到不同的物理数据库上,同一个业务分库架构,在业务层与数据库之间增加分布式缓存以便减少主库的压力。
- 在mysql 5.7版本后开始支持多线程并行复制技术enhanced multi-threaded slave(简称MTS)。
MySQL并行同步机制
并行同步在MySQL5.6就开始支持,只不过在5.6时是基于schema的,也就是基于库的,相比于5.7的机制要逊色的多。MySQL 5.7才可称为真正的并行复制,这其中最为主要的原因就是slave服务器的回放与主机是一致的。即master服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制。
从MySQL官方来看,其并行复制的原本计划是支持表级的并行复制和行级的并行复制,行级的并行复制通过解析ROW格式的二进制日志的方式来完成。但是最终出现给小伙伴的确是在开发计划中称为:MTS: Prepared transactions slave parallel applier。该并行复制的思想最早是由MariaDB的Kristain提出,并已在MariaDB 10中出现,相信很多选择MariaDB的小伙伴最为看重的功能之一就是并行复制。
MySQL 5.7并行复制的思想简单易懂,一言以蔽之:一个组提交的事务都是可以并行回放,因为这些事务都已进入到事务的prepare阶段,则说明事务之间没有任何冲突(否则就不可能提交)。为了兼容MySQL 5.6基于库的并行复制,5.7引入了新的变量slave-parallel-type,其可以配置的值有:
• DATABASE:默认值,基于库的并行复制方式
• LOGICAL_CLOCK:基于组提交的并行复制方式
MySQL在binlog中定义了两个Logical_clock的变量:
• max_c ommitted_transaction:记录上次组提交时的logical_clock,代表上述mysqlbinlog中的last_committed
• transaction_counter:记录当前组提交中各事务的logcial_clock,代表上述mysqlbinlog中的sequence_number
通过工具检查binlog文件,可以发现较之原来的二进制日志内容多了last_committed和sequence_number,last_committed表示事务提交的时候,上次事务提交的编号,如果事务具有相同的last_committed,表示这些事务都在一组内,可以进行并行的回放。例如上述last_committed为0的事务有6个,表示组提交时提交了6个事务,而这6个事务在从机是可以进行并行回放的。
提升同步的策略中,有很多实现,包括MySQL各版本的实现及MariaDB等分支的实现上,按按粒度区分的三个策略,粒度从粗到细是按库、按表、按行,而MySQL5.7按照commit_id的策略适用范围更广,改进策略并发性更优。