mysql 复制策略_MySQL-复制

复制的原理

主库应该会有一个ServerSocket监听端口

从库通过 change master 命令

设置主库的ip 端口 用户名和密码 这些简单说是连接校验信息；

还需要设置 请求binlog的开始位置

从库执行start slave指令，会启动两种线程

io线程，负责做网络连接的

sql线程，负责同步relay log中的数据，转化为sql语句在从库执行。

主库校验完相关的信息后，按照从库指定的位置把binlog日志传递给从库

从库的IO thread拿到日志后，写入到relay log

sql线程把相关数据解析并执行

binlog日志

binlog日志有三种格式，statement，row和mixed。

当设置为statement时，保存的是SQL语句；当设置为row时，保存的是具体被操作的行信息。

在某种情况下时，使用statement格式可能会导致主从不一致。比如：当delete 语句中包含了limit时，根据不同的索引定位到的数据行不同时(但是这些行都满足where条件)，很可能在主库上删除的是A行，但是在从库中删除的却是B行。

根本原因：因为保存的是sql，所以还需要通过各种步骤进行解析处理，在这个过程很可能两个库解析处理的结果不同。

如果修改为row格式，记录的不是sql，而是被操作的行，这样就不会有歧义。

但是row格式也有缺点：

因为记录了每一个操作的行，所以不可避免地会产生很大的日志量。为了应对这个问题，MySQL提供了mix模式，在mix模式中，由mysql来判断具体使用哪种格式存入binlog，这种方式更加灵活。

生产实践中，更推荐用row格式，因为利用row格式可以做数据恢复，其他格式却不可以。

具体原理：row格式中记录了行的所有字段信息，这样就可以方便的做相关的逆向操作信息。

MariaDB的Flashback工具就是利用row格式的binlog来做数据恢复的。

另外，在生产实践中，不要直接复制粘贴mysql中的sql语句来做手动的操作，因为有些指令是依赖上下文的，只使用sql语句可能会出错。应该使用mysqlbinlog 解析出来，然后一股脑地交给mysql去处理。

mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

复制延迟

复制延迟查看指标

因为是异步复制，所以一定存在延迟的问题。

在show slave status中可以看到一个seconds_behind_master字段，这个字段代表了备库执行完这个事务时的时间点和主库写入binlog的时间点的差值。

注意，如果主库和备库的系统时间设置的不一样，也没有问题，因为备库在连接上主库的时候会获取当前主库的系统时间，在计算seconds_behind_master时会自动应用这个时间差(这里需要考虑是只获取一次？如果中间系统时间被修改了呢？)。

这个时间差值代表了总延时。我们还可以通过查看下面的差值查看到每个子步骤之间的延迟。

IO线程的延迟：主库的Position和从库的 Read_Master_Log_Pos 的差值

SQL线程的延迟： 从库的 Read_Master_Log_Pos 和 从库的Exec_Master_Log_Pos 的差值

复制高延迟的来源

下面的因素会导致较大的复制延迟。

备库的性能显著低于主库-非对称部署

大量统计读请求导致备库压力大

大事务或者大表DDL

备库的并行复制能力

并行复制能力核心原则

同一个行多个事务一定要导入同一个worker线程处理。(因为调度是无法确定顺序的)

同一个事务不能被拆开，需要导入同一个worker线程处理。(如果是不同worker处理，那么无法保证事务的隔离性)

并行复制能力的演化

MySQL5.6之前都是单线程，不支持并行的。如果使用的是5.6之前的版本，无法升级，但是又需要提高并行能力，可以参考下面的两种方案。

按表分配

按表分配的核心原则是，如果两个事务更新不同的表，那么他们可以并行执行。

分发事务T的算法如下：

如果事务T和任何一个worker都不冲突，那么选择一个最空闲的worker

如果事务T和多于1个worker冲突，那么事务进行等待，直到只和一个worker冲突，那么就把这个事务T分配给冲突的worker

这个策略适合事务均匀分布在多个表中的场景，如果事务都分布在同一个表中(热点表)，那么最终都会分配到一个线程中执行，又变成了单线程。

按行分配

按行分配的核心原则是，如果两个事务更新不同的行，那么他们可以并行执行。

但是不能只考虑主键索引，还需要考虑其他的唯一索引。 核心是 当唯一索引存在时，修改唯一索引是否成功是依赖于事务的执行顺序的。

比如有下表

CREATE TABLE `t1` (

`id` int(11) NOT NULL,

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `a` (`a`)

) ENGINE=InnoDB;

insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

两个事务，A事务修改id=1的行的a为6，B事务修改id=2的行a=1。

如果是第一个事务先执行，那么可以修改成功。

如果是第二个事务先执行，就违背了a的唯一索引约束，会失败。

所以针对唯一约束，我们需要重新设计HashMap中的key。

key = 库名 + 表名 + 索引列名+ 索引值，如果更新后的值和更新前不同，那么需要记录两个key。

针对上面的例子：

A事务对应的worker中的HashMap中的key为：

库名 + 表名 + id + 1 value为2(因为更新前后id不变)

库名 + 表名 + a + 1 value为1

库名 + 表名 + a + 6 value为1

当B事务需要分配worker时，首先计算B事务的HashMap

库名 + 表名 + id + 2 value为2(因为更新前后id不变)

库名 + 表名 + a + 2 value为1

库名 + 表名 + a + 1 value为1

因为第三个key冲突，所以B事务需要放入A事务的worker中串行执行

这种解决方案的缺点，当有大事务时，会特别消耗内存，因为hash，所以会特别消耗cpu。

MySQL5.6版本的并行复制策略

按库分发，这种方式的好处：

内存和cpu都消耗较小

不要求binlog的格式，statement格式也可以方便的拿到库名。

这种方式的缺点：

如果只有一个业务库，那么其实还是单线程的。

MariaDB 和MySQL5.7的并行复制策略

待研究