Mysql集群架构

1. 集群架构设计

1.1 架构设计理念

主要从三个维度考虑问题：

• 可用性
• 扩展性
• 一致性

1.2 可用性设计

• 站点高可用，冗余站点
• 服务高可用，冗余服务
• 数据高可用，冗余数据
  保证高可用的方法是冗余。 但是数据冗余带来的问题是数据一致性问题。
  实现高可用有以下几种架构：
• 主从模式
  简单灵活，能满足多种需求。比较主流的用法，但是写操作高可用需要自行处理。
• 双主模式
  互为主从，有双主双写、双主单写两种方式，建议使用双主单写。

1.3扩展性设计
扩展性主要指读操作扩展、写操作扩展。

• 如何扩展以提高读性能
  1）加从库
  简单易操作，方案成熟。
  从库过多会引发主库性能损耗。建议不要作为长期的扩充方案，应该设法用良好的设计来避免加从库来缓解读性能问题。
  2）分库分表
  可以分为垂直区分和水平区分，垂直区分可以缓解部分压力，水平拆分理论上可以无限扩展。
• 如何拓展以提高写性能
  1）分库分表

1.4一致性设计
一致性主要考虑集群中各数据库数据同步以及同步延迟问题，方案如下：

• 不适用从库
  扩展读性能问题需要单独考虑，否则容易出现系统瓶颈。
• 增加访问路由层
  可以先得到主从同步最长时间t，在数据反生修改后的t时间内，读取主库。

2.主从模式

2.1 使用场景
Mysql主从模式是指数据可以从一个Mysql数据库服务器节点复制到另外一个或多个节点。Mysql默认采用异步复制方式，这样可以从节点不用一直访问主服务器来更新自己的数据，从数据库可以复制主数据库所有库或者特定数据库或者特定表。

mysql主从复制用途：

• 实时灾备，用于故障切换（高可用）
• 读写分离，提供查询服务（读扩展）
• 数据备份，避免影响业务（高可用）

主从部署必要条件：

• 从库可以连通主库
• 主库开启binglog日志（设置log-bin参数）
• 主从server-id不同

2.2 实现原理

2.2.1 主从复制
原理图：

主从复制分为以下三个步骤：

1）主库将自己的数据库变更操作记录到Binlog日志文件中。
2）从库读取主库中的Binlog日志文件信息写入到从库的Replay Log中继日志中。
3）从库读取中继日志信息在从库进行Replay，更新从库数据信息。

上述三个过程中，设计了Master的BinlogDump Thread和Slave的I/O Thread、SQL Thread，他们作用如下:

• Master服务器对数据库更改操作记录在Binlog中，BinlogDump Thread接收到写入请求后，读取Binlog信息推送给Slave 的I/O Thread。
• Slave的I/O Thread将读取到的Binlog信息写入到本地Replay Log中。
• Slave 的SQL Thread 检测到Relay Log的变更请求，解析relay log中内容在从库上执行。

上述过程都是异步操作，俗称异步复制，存在数据延迟现象。
下图是异步复制的时序图：

Mysql主从复制存在的问题：

• 主数据库宕机后，数据可能丢失
• 从库只有一个SQL Thread，主库写压力大，复制可能延时

解决办法：

• 半同步复制 – 解决数据丢失问题
• 并行复制–解决从库复制延迟问题

2.2.2 半同步复制

为了提升数据安全，Mysql让Master在某一个时间点等待Slave节点的ACK（Acknowledgecharacter）消息，接收到ACK消息后才进行事物的提交，这也是半同步的基础，Mysql从5.5开始引入了半同步复制机制来降低数据丢失的概率。
我们知道Mysql事物写入碰到主从事物时的完整过程，主库事物写入分为4个步骤：

• InnoDB Redo File Write（Prepare Write）
• Binlog File Flush & Sync to Binlog File
• InnoDB Redo File Commit（Commit Write）
• Send Binlog to Slave

当Master不需要关注Slave是否接受到Binlog Event时，即为传统主从复制。
当Master需要在第三步等待Slave返回ACK时，即为after-commit，半同步复制（Mysql 5.5 引入）
当Master 需要在第二步等待Slave返回ACK时，即为after-sync，增强半同步（Mysql 5.7引入）
下图是MySQL官方对半同步复制的时序图，主库等待从库写入replay log并返回ACK后才进行Engine Commit。

2.3 并行复制

MySQL从5.6版本开始追加了并行复制功能，目的就是为了改善复制延迟问题，并行复制称为enhanced multi-threaded slave（简称MTS）。
在从库中有两个线程IO Thread和SQL Thread，都是单线程工作模式，因此有了延迟问题，可以采用多线程机制来加强，减少从库复制延迟。（IO Thread多线程意义不大，主要指Sql Thread多线程）
在Mysql的5.6、5.7、8.0 版本上，都是基于上述SQL Thread多线程思想，不断优化，减少复制延迟。

2.3.1 Mysql 5.6 并行复制原理
MySQL 5.6版本也支持所谓的并行复制，但是其并行是基于库的。如果用户的MySQL数据库中是多个库，对于从库复制的速度可以有较大的帮助。

基于库的并行复制，实现相对比较简单，使用也相对简单些。基于库的并行复制遇到单库多表的场景就发挥不出优势了，另外对事物并行处理的执行顺序也是个大问题。

2.3.2 MySQL 5.7 并行复制原理

MySQL 5.7 是基于组提交的并行复制，MySQL 5.7 才可称为真正的并行复制，这其中最为主要的原因就是slave服务器的回放和master服务器是一致的，即master服务器上面怎么并行执行的slave上就怎么样并行回放。不再有库的并行复制限制。

MySQL 5.7中组提交的并行复制是如何实现的？

MySQL 5.7是通过对事务进行分组，当事务提交时，它们将在单个操作中写入到二进制日志中。如果多个事务能同时提交成功，那么它们意味着没有冲突，因此可以在Slave上并行执行，所以通过在主库上的二进制日志中添加组提交信息。

MySQL 5.7的并行复制基于一个前提，即所有已经处于prepare阶段的事务，都是可以并行提交的。这些当然也可以在从库中并行提交，因为处理这个阶段的事务都是没有冲突的。在一个组里提交的事务，一定不会修改同一行。这是一种新的并行复制思路，完全摆脱了原来一直致力于为了防止冲突而做的分发算法，等待策略等复杂的而又效率底下的工作。

InnoDB事务提交采用的是两阶段提交模式。一个阶段是prepare，另一个commit。

为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：DATABASE（默认值，基于库的并行复制方式）、LOGICAL_CLOCK（基于组提交的并行复制方式）。

那么如何知道事务是否在同一组中，生成的Binlog内容如何告诉Slave哪些事务是可以并行复制的？

在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。为了避免用户没有开启GTID功能（gtid_mode=OFF），MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型ANONYMOUS_GTID_LOG_EVENT。

通过mysqlbinlog工具分析binlog日志，就可以发现组提交的内部信息。

可以发现MySQL 5.7二进制日志比之前的二进制日志内容多了last_committed和sequenct_number，last_committed表示事物 提交的时候，上次事物提交的编号，如果事物具有相同的last_committed，表示这些食物都在一组内，可以进行并行的回放。

2.3.3 MySQL 8.0 并行复制

MySQL8.0 是基于write-set的并行复制。MySQL会有一个集合变量来存储事务修改的记录信息（主键哈希值），所有已经提交的事务所修改的主键值经过hash后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行。这样的粒度，就到了 row级别了，此时并行的粒度更加精细，并行的速度会更快。

2.3.4 并行复制配置与调优

• binlog_transaction_dependency_history_size
  用于控制集合变量的大小
• binlog_transaction_depandency_tracking
  用于控制binglog文件中事物之间的依赖关系，即last_committed的值。
• COMMIT_ORDERE:基于组提交机制
• WRITESET:基于写集合机制
• WRITESET_SESSION:基于写集合，比writeset多了一个约束，同一个session中的事务last_committed按先后顺序递增
• transaction_write_set_extraction
  用于控制事物的检测算法，参数值：OFF、XXHASH64、MURMUR32
• master_info_repository
  开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。
• slave_parallel_workers
  若将slave_parallel_workers设置为0，则MySQL5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker 线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差。
• slave_preserve_commit_order
  MySQL5.7后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为LOGICAL_CLOCK，但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relaylog中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relaylog中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。

总结：要开启enhanced multi-threaded slave 其实很简单，只需要根据如下配置：

slave-parallel-type=LOGICAL_CLOCK 
slave-parallel-workers=16 
slave_pending_jobs_size_max=2147483648 
slave_preserve_commit_order=1 
master_info_repository=TABLE 
relay_log_info_repository=TABLE 
relay_log_recovery=ON

2.3.5 并行复制监控
在使用了MTS后，复制的监控依旧可以通过SHOW SLAVE STATUS \G，但是MySQL5.7在performance_schema库中提供了很多元数据表，可以更详细的监控并行复制过程。

show tables like ‘replication%’;

通过replication_applier_status_by_worker可以看到worker进程的工作情况：

select *f rom replication_applier_status_by_worker;

如果MySQL 5.7要使用MTS功能，建议使用新版本，最少升级到5.7.19 version，修复了很多bug。

---

Mysql主从同步配置：
master版本：8.0.11 slave版本：8.0.11

配置master：

• 编辑my.cnf配置文件
  
  重启mysql服务

• 授权
  grant replication slave on . to ‘root’@’%’ identified by ‘密码’;
  grant all privileges on . to ‘root’@’%’ identified by ‘密码’;
  刷新权限：flush privileges;

• 查看master状态：show master status
  
  配置slave：

• 编辑my.cnf配置文件
  
  重启mysql服务。

• 查看slave状态，show slave status
  
  此时还是空的。

• 配置主从同步

change master to master_host=‘masterIP地址’,
master_port=3308,
master_user=‘root’,
master_password=‘密码’,
master_log_file=‘master File （mysql-bin.000001）’,
master_log_pos=master Position(862);

• 开启从服务器同步，start slave，查看从服务器状态。
  
  可以看出slave连接正常。

修改slave配置：

• stop slave
• researt slave;
• change master to master_host=‘masterIP地址’,
  master_port=3308,
  master_user=‘root’,
  master_password=‘密码’,
  master_log_file=‘master File （mysql-bin.000001）’,
  master_log_pos=master Position(862);
• start slave

测试：

• 在master上执行创建数据库、创建表、添加数据语句
  
  正确查询出数据

• 在slave中执行查询语句
  
  可以看到数据已经全部同步功能

---

MySQL半同步复制

半同步复制的安装部署

1）MySQL 5.5版本及以上
2）变量have_dynamic_loading 为 yes
3）异步复制已经存在

• 查看是否支持插件动态安装

select @@have_dynamic_loading;

• 安装semi插件

主：

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

从：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

• 查看是否安装成功

show plugins;

• 查看插件信息及开启插件

查看信息：
show variables like '%semi%';

启动半同步复制：
主：

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

从：

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

或者通过修改配置文件开启半同步复制：

主：

plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1

从：

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

• 查看主、从 半同步是否正在运行

• 重启slave的IO线程

stop slave IO_THREAD；
start slave IO_THREAD；

总结：事实上，半同步复制并不是严格意义上的半同步复制

当半同步复制发生超时时（由rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为半同步复制。

测试：

分为三个阶段：

1）在slave 执行stop slave 之前，主的insert 操作很快可以返回。
2）在Slave执行stop slave后，主的insert操作需要 大于10s才返回，而这与rpl_semi_sync_master_timeout参数的时间相吻合。这时，查看两个状态的值，均为“OFF”了。
同时，主的error.log打印错误信息：

2016-08-05T11:51:49.855452Z 6 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000003, pos: 1447), semi-sync up to file mysql-bin.000003, position 1196.
2016-08-05T11:51:49.855742Z 6 [Note] Semi-sync replication switched OFF.

3）在Slave执行start slave后，主的insert操作很快就能返回，此时，两个状态的值也变为“ON”了。

1⃣️ 第一种情况：
2⃣️第二种情况

master 一直阻塞状态

3⃣️第三种情况

---

Mysql并行复制配置

• 查看master 组信息

show variables like '%binlog_group%';

设置组提交的延时控制参数：
set global binlog_group_commit_sync_delay = 1000;
set global binlog_group_commit_sync_no_delay_count= 100;

• slave配置

查看从库并行复制方式：

show variables like 'slave_parallel_type';
show variables like '%relay_log%';

设置从库配置文件my.cnf

# slave
[mysqld] 
# The TCP/IP Port the MySQL Server will listen on
port=3308
server-id=2
# 设置只读模式
read_only=1
# 设置relay_log别名
relay_log=mysql-relay-bin
#设置并行复制方式（基于组提交的并行复制方式）
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
#开启保证relay-log的完整性
relay_log_recovery=ON

---

读写分离

读写分离引入时机

很多业务中，往往是读多写少，这时候数据库的读操作会成为数据库性能的瓶颈。如果已经优化了sql，但是读仍然是瓶颈时，就可以考虑"读写分离"架构了。
读写分离首先需要将数据库分为主从库，一个主库用于写数据，多个从库用于完成读数据的操作，主从库之间通过主从复制机制进行数据同步，如图所示：

在应用中可以在从库中追加多个索引来优化查询，主库的索引可以不加，用于提升写效率。
读写分离架构也能够消除写锁冲突从而提升数据库的读性能。使用读写分离架构需要注意：

• 主从同步延迟
• 读写分配机制

主从同步延迟

使用读写分离架构时，数据库主从同步具有延迟性，数据一致性会有影响，对于一些实时性要求比较高的操作，可以采用以下方案：

• 写后立刻读
  在写入数据库 后，某个时间段内去访问主库，之后操作在读取从库。
• 二次查询
  先去从库读取数据，找不到时就去主库进行数据读取。该操作容易将压力返还给主库，为了避免恶意攻击，建议对数据库访问API操作进行封装，有利于安全和耦合。
• 根据业务特殊处理
  根据业务特点和重要程度进行调整，比如重要的，实时性要求高的业务数据读写可以放在主库。对于次要的业务，实时性要求不高可以进行读写分离，查询时去从库查询。

读写分配机制

读写路由分配机制 是实现读写分离架构最关键的一个环节，就是控制何时去主库写，何时去从库读。目前较为常见的实现方案为以下两种：

• 基于编程和配置实现（应用端）
  在代码中根据操作类型进行路由分配，增删改时操作主库，查询操作从库。优点实现简单，因为程序在代码中实现，不需要额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手，如果其中一个数据库宕机了，就需要修改配置重启项目。
• 基于服务器端代理实现（服务器端）
  
  中间件代理一般介于应用服务器和数据库服务器之间，从图中可以看到，应用服务器并不直接进入到master数据库或者slave数据库，而是进入Mysql proxy代理服务器。代理服务器接收到应用服务器的请求后，先进行判断然后转发到后端master和slave数据库。

常见的数据库中间件：MySQL Proxy、MyCat、Shardingsphere等。

• MySQL Proxy：是官方提供的MySQL中间件产品可以实现负载平衡、读写分离等。
• MyCat：MyCat是一款基于阿里开源产品Cobar而研发的，基于 Java 语言编写的开源数据库中间
  件。
• ShardingSphere：ShardingSphere是一套开源的分布式数据库中间件解决方案，它由ShardingJDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。已经在2020年4月16日从Apache孵化器毕业，成为Apache顶级项目。
• Atlas：Atlas是由 Qihoo 360公司Web平台部基础架构团队开发维护的一个数据库中间件。
• Amoeba：变形虫，该开源框架于2008年开始发布一款 Amoeba for MySQL软件。

Mysql配置读写分离

使用MySQL Proxy实现读写分离

1）下载安装mysql-proxy实现读写分离

wget -P /mydata  https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

2）配置mysql-proxy，创建主配置文件

vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=root #主从mysql共有的用户
admin-password=root #用户的密码
proxy-address=192.168.0.204:4040 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.0.203:3308 #指定后端从slave读取数据
proxy-backend-addresses=192.168.0.202:3308 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
保存退出!
chmod 660 /etc/mysql-porxy.cnf

3）修改读写分离配置文件

vi /usr/local/mysql-proxy/lua/rw-splitting.lua
if not proxy.global.config.rwsplit then
 proxy.global.config.rwsplit = {
  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1
  max_idle_connections = 1, #默认8，改为1
  is_debug = false
 }
end

4）启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
netstat -tupln | grep 4000 #已经启动
tcp 0 0 192.168.0.204:4000 0.0.0.0:* LISTEN 1264/mysql-proxy
关闭mysql-proxy使用：killall -9 mysql-proxy

---

3. 双主模式

3.1 适用场景

MySQL主从模式一定程度提高了MySQL 高可用、扩展性，但是单主如果发生单点故障，从库切换为主库还需要作改动。因此，如果是双主或者多主，就会增加MySQL入口，提升了主库的可用性。双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。

那是使用双主双写 还是双主单写？

建议使用双主单写模式，因为双写存在以下问题：

• ID冲突
  在A主库写入，当A数据未同步到B主库时，对B主库写入，如果采用自动递增容易发生ID主键的冲突。
  可以采用MySQL自身的自动增长步长来解决，例如A的主键为1,3,5,7…，B的主键为2,4,6,8… ，但是对数据库运维、扩展都不友好。
• 更新丢失
  同一条记录在两个主库中进行更新，会发生前面覆盖后面的更新丢失。

高可用架构如下图所示，其中一个Master提供线上服务，另一个Master作为备胎供高可用切换，Master下游挂载Slave承担读请求。

随着业务发展，架构会从主从模式演变为双主模式，建议用双主单写，在引入高可用组件，例如Keepalived和MMM工具，实现主库故障自动切换。

3.2 MMM架构

MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换 的第三方软件。MMM 使用Perl语言开发，虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。下图是基于MMM实现的双主高可用架构。

• MMM故障处理机制
  MMM包含writer和reader两类角色，分别对应写节点和读节点。

1）当writer节点出现故障，程序会自动移除该节点上的VIP
2）写操作切换到Master2，并将Master2 设置为writer
3）将所有的Slaver节点指向Master2
除了管理双主节点，MMM 也会管理 Slave 节点，在出现宕机、复制延迟或复制错误，MMM 会移除该节点的 VIP，直到节点恢复正常。

• MMM监控机制
  MMM 包含monitor和agent两类程序，功能如下：
  1）monitor：监控集群内数据库的状态，在发现异常时发布切换命令，一般和数据库分开部署。
  2）agent：运行在每个 MySQL 服务器上的代理进程，monitor 命令的执行者，完成监控的探针
  工作和具体服务设置，例如设置 VIP（虚拟IP）、指向新同步节点。

3.3 MHA架构

MHA（Master High Availability）是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA还支持在线快速将Master切换到其他主机，通常只需0.5－2秒。
目前MHA主要支持一主多从的架构，要搭建MHA，要求一个复制集群中必须最少要有三台数据库服务器。

MHA由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。

• MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。
• MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志。

MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。

MHA故障处理机制：

• 把宕机master的binlog保存下来
• 根据binlog位置点找到最新的slave
• 用最新slave的relay log修复其它slave
• 将保存下来的binlog在最新的slave上恢复
• 将最新的slave提升为master
• 将其它slave重新指向新提升的master，并开启主从复制

MHA优点：

• 自动故障转移快
• 主库崩溃不存在数据一致性问题
• 性能优秀，支持半同步复制和异步复制
• 一个Manager监控节点可以监控多个集群

3.4 主备切换

主备切换是指将备库变为主库，主库变为备库，有可靠性优先和可用性优先两种策略。

主备延迟问题

主备延迟是由主从数据同步延迟导致的，与数据同步有关的时间点主要包括以下三个：
1）主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
2）之后将binlog传给备库 B，我们把备库 B 接收完 binlog 的时刻记为 T2;
3）备库 B 执行完成这个binlog复制，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。
在备库上执行show slave status命令，它可以返回结果信息，seconds_behind_master表示当前备库延迟了多少秒。

同步延迟主要原因如下：

1）备库机器性能问题
机器性能差，甚至一台机器充当多个主库的备库。
2）分工问题
备库提供了读操作，或者执行一些后台分析处理的操作，消耗大量的CPU资源。
3）大事物操作
大事务耗费的时间比较长，导致主备复制时间长。比如一些大量数据的delete或大表DDL操作都可能会引发大事务。

可靠性优先

主备切换过程一般由专门的HA高可用组件完成，但是切换过程中会存在短时间不可用，因为在切换过程中某一时刻主库A和从库B都处于只读状态。如下图所示：

主库A切换到主库B，切换流程如下：

• 判断从库B的Seconds_Behind_Master值，当小于某个值才继续下一步
• 把主库A改为只读状态（readonly=true）
• 等待从库B的Seconds_Behind_Master值降为 0
• 把从库B改为可读写状态（readonly=false）
• 把业务请求切换至从库B

可用性优先

不等主从同步完成， 直接把业务请求切换至从库B ，并且让 从库B可读写 ，这样几乎不存在不可用时间，但可能会数据不一致。

如上图所示，在A切换到B过程中，执行两个INSERT操作，过程如下：

• 主库A执行完 INSERT c=4 ，得到 (4,4) ，然后开始执行 主从切换
• 主从之间有5S的同步延迟，从库B会先执行 INSERT c=5 ，得到 (4,5)
• 从库B执行主库A传过来的binlog日志 INSERT c=4 ，得到 (5,4)
• 主库A执行从库B传过来的binlog日志 INSERT c=5 ，得到 (5,5)
• 此时主库A和从库B会有 两行 不一致的数据

通过上面介绍了解到，主备切换采用可用性优先策略，由于可能会导致数据不一致，所以大多数情况下，优先选择可靠性优先策略。在满足数据可靠性的前提下，MySQL的可用性依赖于同步延时的大小，同步延时越小，可用性就越高。

MySQL配置双写：

至少需要两台MySQL服务，分别为master1、master2

• 配置mysql配置文件

master1:
[mysqld]
port=3308
# 开启binlog
log-bin=mysql-bin
# 唯一服务id
server-id=1
# 设置哪些数据库不需要同步
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 设置需要同步的库
# binlog-do-db=DBName
secure_file_priv=/var/lib/mysql
#以下是需要配置双主模式的配置
relay-log=mysql-relay-bin
log-slave-updates=1
#双写模式下 主键策略：1，3，5....
auto_increment_offset=1
auto_increment_increment=2


master2：

port=3308
# 开启binlog
log-bin=mysql-bin
# 唯一服务id
server-id=2
# 设置哪些数据库不需要同步
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 设置需要同步的库
# binlog-do-db=DBName
secure_file_priv=/var/lib/mysql
#以下是需要配置双主模式的配置
relay-log=mysql-relay-bin
log-slave-updates=1
#双写模式下 主键策略：2，4，6....
auto_increment_offset=2
auto_increment_increment=2

• 分别为master1、master2授权

为master2 授权（此时master2作为slave）：

grant replication slave on *.* to 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%'  identified by 'root';
flush privileges;

change master to master_host=‘master1IP地址’,
master_port=3308,
master_user=‘root’,
master_password=‘密码’,
master_log_file=‘master File （mysql-bin.000001）’,
master_log_pos=master Position(862);

为master1 授权（此时master1作为slave）：
grant replication slave on *.* to 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%'  identified by 'root';
flush privileges;

change master to master_host=‘master2IP地址’,
master_port=3308,
master_user=‘root’,
master_password=‘密码’,
master_log_file=‘master File （mysql-bin.000001）’,
master_log_pos=master Position(862);

---

4 分库分表

互联网系统需要处理大量用户的请求。比如微信日活用户破10亿，海量的用户每天产生海量的数量；美团外卖，每天都是几千万的订单，那这些系统的用户表、订单表、交易流水表等是如何处理呢？

数据量只增不减，历史数据又必须要留存，非常容易成为性能的瓶颈，而要解决这样的数据库瓶颈问题，“读写分离”和缓存往往都不合适，目前比较普遍的方案就是使用NoSQL/NewSQL或者采用分库分表。

使用分库分表时，主要有垂直拆分和水平拆分两种拆分模式，都属于物理空间的拆分。

分库分表方案：只分库、只分表、分库又分表。
垂直拆分：由于表数量多导致的单个库大。将表拆分到多个库中。
水平拆分：由于表记录多导致的单个库大。将表记录拆分到多个表中。

4.1 拆分方式

• 垂直拆分
  垂直拆分又称为纵向拆分，垂直拆分是将表按库进行分离，或者修改表结构按照访问的差异将某些列拆分出去。应用时有垂直分库和垂直分表两种方式，一般谈到的垂直拆分主要指的是垂直分库。
  入图所示，采用垂直分库，将用户和订单表拆分到不同的数据库中：
  
  垂直分表就是将一张表中不常用的字段拆分到另外一张表中，从而保证第一张表中的字段较少，避免数据库跨页存储的问题，从而提升查询效率。
  解决：一个表中字段过长，还有些字段不经常使用，或者有些text类型字段，可以考虑垂直分表的方式。

按列进行垂直拆分，即把一条记录分开多个地方保存，每个字表的行数是相同的。把主键和一些列拆分到另外一张表中，然后把主键和另外的列拆分到另外一张表中。

垂直拆分优点：

• 拆分后业务清晰，拆分规则明确
• 易于数据的维护和扩展
• 可以使行数据变小，一个数据块（block）就可以存放更多的数据，在查询时就可以减少I/O次数。
• 便于实现冷热分离的数据库表设计。

垂直拆分缺点：

• 主键冗余。
• 会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力，但是增大了系统的复杂度。
• 依然存在标数量过大的问题。
• 事物处理复杂。

4.2 水平拆分

水平拆分又称为横向拆分。 相对于垂直拆分，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个表仅包含数据的一部分，如下图所示：

水平拆分是将表数据拆分为几张结构相同的表，如果一张表中的记录数过多，那么会对数据库的读写性能产生较大的影响，虽然此时仍然能够正确地读写，但读写的速度已经到了业务无法忍受的地步，此时就需要使用水平分表来解决这个问题。

水平拆分：解决表中数据量过大的问题。
垂直拆分：解决表过多或表字段过多问题。

水平拆分优点

• 拆分规则设计好，join操作基本可以数据库做。
• 不存在单表大数据，高并发的性能瓶颈。
• 切分的表结构相同，应用层改造较少，只需要增加路由规则就好。
• 提高了系统稳定性和负载能力。

水平拆分缺点

• 拆分规则难以抽象
• 跨库join性能较差
• 分片事物一致性问题难以解决
• 数据扩容难度和维护量级极大

分库分表：

有些财务系统，需要按月份和年份汇总，所有用户资金的情况，这是就可以采用分库分表。

每年都有单独的数据库，每个数据库中，都有12张表，每张表存储一个月的用户资金数据。

这样分库分表之后，就可以非常高效的查出某个用户每个月或者每年的资金了。
此外，还有些比较特殊的需求，比如需要按照地域分库，比如：华中、华北、华南等区，每个区都有一个单独的数据库。

分表：假如一个商城有积分业务，一个用户一天当中，获取积分、消费积分都有可能有很多此，那么，一个用户每天产生的积分记录就有很多条。如果用户量很大，积分相关数据是很庞大的。此时就可以采用分表策略

一个积分数据库，可以分若干张表。根据用户id进行hash除以128取模。

分表数量最好是2的幂次方，方便以后扩容

总结：

分库：是为了解决数据库连接资源不足问题，和磁盘IO的性能瓶颈问题。
分表：是为了解决单表数据量太大，sql语句查询数据时，即使走了索引也非常耗时问题，此外还可以解决cpu资源问题。
分库分表：可以解决 数据库连接资源不足、磁盘IO的性能瓶颈、检索数据耗时 和 消耗cpu资源等问题。

如果在某些业务场景，用户并发量很大，但是需要保存的数据量很少，这是可以只分库，不分表。

如果在有些业务场景中，用户并发量不大，但是需要保存的数据量很多，这是可以只分表，不分库。

如果在有些业务场景中，用户并发量大，并且需要保存的数量也很多时，可以分库分表。