本文共 6622 字，大约阅读时间需要 22 分钟。

MySQL的主从复制

主从复制概述

在我们日常的数据库运维中，经常会因为各种原因需要停止数据库，比如MySQL版本升级，服务器硬件升级等，如果没有备份的话很容易造成数据丢失，且可用性降低。MySQL的主备可以提高数据库的可用性。

MySQL几乎所有的高可用架构，都直接依赖于binlog。虽然这些高可用架构已经呈现出越来越复杂的趋势，但都是从最基本的一主一备演化过来的。

主从复制原理

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
• I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中执行。

一个完整的流程：

备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：

1. 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量。
2. 在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接。
3. 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。
4. 备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。
5. sql_thread读取中转日志，解析出日志里的命令，并执行。

binlog有三种格式，一种是statement(记录执行的SQL语句)，一种是row(具体记录对某一行的具体操作)，另一种是前两种的混合mixed。

注：MySQL的主从复制是从接入点开始同步的

配置方法

step1：修改主库my.ini(windows)配置文件

[mysql]# 设置mysql客户端默认字符集default-character-set=utf8[mysqld]#设置3306端口port = 3306# 设置mysql的安装目录# 设置mysql数据库的数据的存放目录# 允许最大连接数max_connections=200# 服务端使用的字符集默认为8比特编码的latin1字符集character-set-server=utf8# 创建新表时将使用的默认存储引擎default-storage-engine=INNODB#=========主从复制关键配置=====================server_id=1 #主库和从库需要不一致,配一个唯一的ID编号，1至32。 手动设定log-bin=mysql-bin #二进制文件存放路径,存放在根目录databinlog-do-db=test #需要复制的库,多个库用逗号隔开,如果此项不配置所有主库都参与复制binlog-ignore-db=mysql #不需要复制的库，和上项同理binlog_format = ROW#=========主从复制关键配置=====================

保存，重启主库。

step2：修改从库my.cnf(Linux)配置文件

#=========主从复制关键配置=====================server_id=1 #主库和从库需要不一致,配一个唯一的ID编号，1至32。 手动设定relay-log=mysql-relay #中继文件存放路径binlog-do-db=test #需要复制的库,多个库用逗号隔开,如果此项不配置所有主库都参与复制binlog-ignore-db=mysql #不需要复制的库，和上项同理#=========主从复制关键配置=====================

保存，重启主库，sudo service mysql restart

step3：主库上创建用户

在主库中建立一个用户（专门用给从库连接的，注意这是在主库里面建立的)

grant replication slave, reload, super on *.* to myslave@localhost identified by    'myslavepassword';

myslave是用户名，myslave密码是用户对应的密码，localhost也可以写ip。

step4：在主库找到日志偏移量

show master status; 找到File 和 Position 的值记录下来。

step5：在从库中执行代码

change master to master_host='127.0.0.1',master_user='myslave',master_password='myslave', master_log_file='binlog.000078', master_log_pos=155;

启动从服务器复制功能start slave，配置完成。

主备切换

主备切换的可靠性优先策略：

1. 判断备库B现在的seconds_behind_master，如果小于某个值（比如5秒）继续下一步，否则持续重试这一步；
2. 把主库A改成只读状态，即把readonly设置为true；
3. 判断备库B的seconds_behind_master的值，直到这个值变成0为止；
4. 把备库B改成可读写状态，也就是把readonly 设置为false；
5. 把业务请求切到备库B。

还可以采用可用性优先策略，seconds_behind_master大于0时及切换主备，可能会产生数据不一致的现象。

读写分离

读写分离概述

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 一般读请求比写请求多得多；
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

p.s. 必须先使用MySQL实现主从复制，再用数据库中间件实现读写分离。

主从延迟问题

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态，解决的方案有：

1. 强制走主库方案：对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。

利用Mycat加注解即可实现：/*#mycat:db_type=master*/ SELECT * FROM tb_item ;

1. sleep 方案：主库更新后，读从库之前先sleep一下。这个方案的假设是，大多数情况下主备延迟在1秒之内，做一个sleep可以有很大概率拿到最新的数据。
2. 判断主备无延迟方案：先判断seconds_behind_master是否已经等于0，如果还不等于0 ，那就必须等到这个参数变为0才能执行查询请求；或对比位点确保主备无延迟。
3. 配合semi-sync：如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。
4. 等主库位点方案：trx1事务更新完成后，马上执行show master status得到当前主库执行到的File和Position；选定一个从库执行查询语句，在从库上执行select master_pos_wait(File, Position, 1)；，如果返回值是>=0的正整数，则在这个从库执行查询语句；否则，到主库执行查询语句。

Mycat

Mycat概述

MycAT是支持MySQL集群的数据库中间件产品，，提供高可用性数据分片集群。可以像使用mysql一样使用Mycat，对于开发人员来说根本感觉不到Mycat的存在。

Mycat是基于阿里开源的 cobar 演变而来，对 cobar 的代码进行了彻底的重构，使用 NIO 重构了网络模块，并且优化了 Buffer 内核，增强了聚合，Join 等基本特性，同时兼容绝大多数数据库成为通用的数据库中间件。

作用：

• 读写分离
• 数据分片
• 多数据源整合(MySQL、ORACLE、MongoDB)

Mycat原理：

Mycat主要是通过对SQL的拦截，然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等，然后将SQL发给后端真实的数据块，并将返回的结果做适当处理返回给客户端。

安装并运行

MyCat的官方网站：

下载地址：

第一步：将Mycat-server-1.6-release-20161028204710-linux.tar.gz上传至服务器

第二步：将压缩包解压缩，建议将mycat放到/usr/local/mycat目录下：

tar -xzvf Mycat-server-1.6-release-20161028204710-linux.tar.gzmv mycat /usr/local

第三步：进入mycat目录的bin目录，启动mycat

./mycat start

停止：

./mycat stop

Mycat的默认端口号为：8066

配置文件

schema.xml作为Mycat中重要的配置文件之一，管理着MycCat的逻辑库、逻辑表以及对应的分片规则、DataNode以及DataSource。

schema标签用于定义Mycat实例中的逻辑库；

table标签定义了Mycat中的逻辑表，rule用于指定分片规则，auto-sharding-long的分片规则是按ID值的范围进行分片 1-5000000 为第1片、5000001-10000000 为第2片…。

dataNode标签定义了Mycat中的数据节点，也就是我们通常说所的数据分片；

dataHost标签在Mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句。

在服务器上创建3个数据库，分别是db1、db2、db3：

   
   	
    		
     	
    	
    	
    	
    	
    		
     
      select user()
     		
                 
      		
     	
    	
   

对于读写分离的核心参数为balance，负载均衡类型，目前的取值有4 种：

• “0”：不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
• “1”：全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2都参与 select 语句的负载均衡。
• “2”：所有读操作都随机的在 writeHost、readhost 上分发。
• “3”：所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力。

server.xml几乎保存了所有mycat需要的系统配置信息，最常用的是在此配置用户名、密码及权限.

   
       
            
     
      0
             
     
      0
               
     
      2
             
     
      0
             
     
      0
             
     
      1
             
     
      1m
             
     
      1k
             
     
      0
             
     
      384m
             
     
      true
         
        
            
     
      user
             
     
      TESTDB
         
    
   

分库与分表

关系型数据库本身比较容易成为系统瓶颈（单表500万，单库5000万），单机存储容量、连接数、处理能力都有限。当单表的数据量达到1000W或100G以后，由于查询维度较多，即使添加从库、优化索引，做很多操作时性能仍下降严重。此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。

垂直切分

垂直切分常见有垂直分库和垂直分表两种。

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。如图：

操作可参考：https://blog.csdn.net/weixin_38319645/article/details/81537849

垂直切分的优点：

• 解决业务系统层面的耦合，业务清晰；
• 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等；
• 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈。

垂直切分的缺点：

• 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度；
• 分布式事务处理复杂；
• 依然存在单表数据量过大的问题（需要水平切分）。

水平切分

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：

按行散列的方式有多种，如果采用根据数值范围，会出现热点数据频繁访问的问题，采用根据数值取模方式较好。

配置rule.xml，采用求模法：

       
            
     
      user_id
             
     
      mod-long
         
    
   
       
        
    
     3
    
   

一致性哈希：一致性哈希的算法简单而巧妙，很容易做到数据均分布，其单调性也保证了扩缩容的数据迁移是比较少的。

跨节点关联查询join

切分之前，系统中很多列表和详情页所需的数据可以通过sql join来完成。而切分之后，数据可能分布在不同的节点上，此时join带来的问题就比较麻烦了，考虑到性能，尽量避免使用join查询。

解决办法有：

全局表

全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。

ER分片

ER分片表：子表的记录与所关联的父表记录存放在同一个数据分片上，避免数据Join跨库操作，以order与order_detail例子为例，schema.xml中定义合适的分片配置,order,order_detail 根据order_id迕行数据切分，保证相同order_id的数据分到同一个分片上，在进行数据插入操作时，Mycat会获取order所在的分片，然后将order_detail也插入到order所在的分片。

数据组装

在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

参考：https://zhuanlan.zhihu.com/p/93724977