mysql主从同步及其分库分表基本流程

主存同步

原理

mysql主从同步中设计三个线程(log dump thread,i/o thread,sql thread)，两个日志（bin log ,relay log）。

• 主节点 log dump 线程

  当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

• 从节点I/O线程 当从节点上执行start
  slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump
  进程发来的更新之后，保存在本地relay-log中。

• 从节点SQL线程
  SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

执行流程

数据库更新后会将更新的数据写入到bin log当中，从从节点i/o线程读取主节点bin log 将内容更新到relay log中，在通过sql进程在从节点上执行。

复制方式

MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件，并把bin log中的sql 通过I/O relay到本地。

• 异步模式（mysql async-mode） 这种模式下，主节点不会主动push bin
  log到从节点（主节点执行完直接提交），这样有可能导致failover的情况下，也许从节点没有及时的将最新的bin log同步到本地。

• 全同步模式
  全同步模式是指主节点和全部从节点执行了commit并确认才会向客户端返回成功
• 半同步模式(mysql semi-sync)
  这种模式下主节点只需要接收到其中一台从节点的返回信息（一个从节点commit），就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；

binlog记录格式

binlog的生成有三种方式

• 基于SQL语句的复制（statement-based replication，SBR）
• 基于行的复制（row-based replication，RBR)
• 混合模式复制（mixed-based replication,MBR)

基于SQL语句的复制

Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4 及之前的版本都是使用的这种复制格式。
优点：只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能。
缺点：在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。

基于行的复制

Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是指记录哪条数据被修改了，修改成什么样。
优点：不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。

混合模式复制

Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。
参考 mysql的主从同步详解-原理篇

分库分表

为什么要分库分表

数据库中数据太多，导致查询效率变慢，之前所使用的增加索引，读写分离，优化索引作用不大时，来进行分库分表。
单表瓶颈，数据过大，查询效率变低。
单库瓶颈，cpu,内存，带宽，磁盘压力过大。

分库分表方式

• 垂直分表：可以把一个宽表的字段按访问频次、业务耦合松紧、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。
• 垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。
• 水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题。
• 水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库分表方案。

拆分步骤

1. 目标评估 拆分为几个表几个库
2. 切分策略
   • 范围切分 按照字段区间切分 1-100 101-200 后续写数据不会均匀分布
   • 中间表映射 缺点中间映射表可能很大
   • hash切分（推荐） 数据均匀
3. 选择分表字段 尽量减少夸库查询
4. 资源准备
5. 双写 新老数据库同时写
6. 全量数据迁移 将老库数据放到新库中
7. 数据一致性校验
8. 灰度切读 将流量逐步迁移到新库上
9. 切读完毕后停写老库。

需要工具

监听bin Log 工具 DataBus canal
分库分表工具 Sharding-jdbc
参考 十亿级商品数据，分库分表核心流程详解
分库分表原因，分库分表的方式，分库分表带来的问题