[MySQL高级](七) MySQL主从复制及读写分离实战

迷南。 2023-06-19 02:22 7阅读 0赞

# 1. 简介 #

随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。  
　　MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。  
下图就描述了一个多个数据库间主从复制与读写分离的模型(来源网络)：  
![这里写图片描述][format_png]  
　　在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。

## 1.1 为什么要做主从复制？ ##

*  读写分离。在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
 *  做数据的热备
 *  架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

## 1.2 主从复制的原理是什么？ ##

MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（Master）复制到另一个MySQL数据库（Slave），在Master和Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中两个线程（SQL线程和IO线程）在Slave端，另一个线程（I/O线程）在Master端。  
　　要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。binlog: binary log，是主库中保存所有更新事件日志的二进制文件。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作。  
　　![这里写图片描述][70]  
我们根据上图来分析一下整个主从复制的过程：  
（1）在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制。  
（2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接Master服务器，并请求从执行binlog日志文件中的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。  
（3）Master服务器接收来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。  
（4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容  
（5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

## 1.3 复制的基本原则 ##

*  每个Slave只有一个Master
 *  每个Slave只能有一个唯一的服务ID
 *  每个Master可以有多个Slave

## 1.4 复制的最大问题 ##

**延时**  
主从同步延迟原理和解决方案：  
[https://www.cnblogs.com/cnmenglang/p/6393769.html][https_www.cnblogs.com_cnmenglang_p_6393769.html]

# 2. 实战MySQL主从复制 #

## 2.1 环境说明 ##

*  两个CentOS7虚拟机
 *  MySQL 5.6.4
 *  Master\_IP:192.168.131.140
 *  Slave\_IP:192.168.131.141

**注意**：MySQL版本号最好一致，为了方便学习测试，建议关闭防火墙

## 2.2 故障避免 ##

我的mysql安装过程是在一台虚拟机上安装好MySQL后，克隆虚拟机得到的两个环境，所以在后面会报一个错：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server  
![这里写图片描述][70 1]  
原因是：mysql 5.6的复制引入了uuid的概念，各个复制结构中的server\_uuid得保证不一样，但是查看到直接copy data文件夹后server\_uuid是相同的，show variables like ‘%server\_uuid%’;

解决方法：  
mysql 5.6的复制引入了uuid的概念，各个复制结构中的server\_uuid得保证不一样，但是查看到server\_uuid是相同的，

show variables like '%server_uuid%';

找到/var/lib/mysql文件夹下的auto.cnf文件，修改里面的uuid值，保证各个db的uuid不一样，重启db即可

systemctl restart mysqld.service

[https://blog.csdn.net/cug\_jiang126com/article/details/46846031][https_blog.csdn.net_cug_jiang126com_article_details_46846031]

## 2.3 MySQL主从复制的复制方式 ##

MySQL的主从复制并不完美，存在着几个由来已久的问题，首先一个问题是复制方式：

*  基于SQL语句的复制（statement-based replication,SBR）
 *  基于行的复制（row-based replication,RBR）
 *  混合模式复制（mixed-based replication,MBR）
 *  全局事务标识符 GTID（Global Transaction Identifier,GTID）

基于SQL语句的方式是最古老的方式，也是目前默认的复制方式，后来的三种是MySQL 5以后才出现的复制方式。

### 2.3.1 SBR方式的优缺点 ###

**SBR的优点**

*  历史悠久，技术成熟
 *  binlog文件较小
 *  binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
 *  binlog可以用于实时的还原，而不仅仅用于复制
 *  主从版本可以不一样，从服务器版本可以比主服务器版本高

**SBR的缺点：**

*  不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候
 *  复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
 *  对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记  
    录产生影响
 *  数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
 *  执行复杂语句如果出错的话，会消耗更多资源

### 2.3.2 RBR方式的优缺点 ###

**RBR的优点**

*  任何情况都可以被复制，这对复制来说是最安全可靠的
 *  和其他大多数数据库系统的复制技术一样
 *  多数情况下，从服务器上的表如果有主键的话，复制就会快了很多

**RBR 的缺点：**

*  binlog 大了很多
 *  复杂的回滚时 binlog 中会包含大量的数据
 *  主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会  
    导致频繁发生 binlog 的并发写问题
 *  无法从 binlog 中看到都复制了写什么语句

### 2.3.3 混合方式 ###

混合方式就是有mysql自动选择RBR方式和SBR方式，能够充分发挥两种方式的优点，一般情况下都使用该种方式实现主从复制

### 2.3.4 全局事务标识符 GTID ###

这种方式虽然能够大大提高主从复制的效率，减小主从复制的延时，但也存在问题，具体请参看下面的博客。  
[https://blog.csdn.net/guotao521/article/details/45483833][https_blog.csdn.net_guotao521_article_details_45483833]  
[http://hamilton.duapp.com/detail?articleId=47][http_hamilton.duapp.com_detail_articleId_47]

## 2.4 实现MySQL主从复制需要进行的配置 ##

*  主服务器Master

开启二进制日志 binlog
    配置唯一的server-id
    获得master二进制文件名及位置
    创建一个用于slave和master通信的用户账号

*  从服务器Slave

配置唯一的server-id
    使用master分配的用户账号读取master二进制日志
    启动slave服务

## 2.5 修改master配置 ##

*  找到`主数据库`的配置文件my.cnf(或者my.ini)，我的在/etc/my.cnf,在\[mysqld\]部分插入如下：

[mysqld]
    #开启二进制日志
    log-bin=mysql-bin 
    #设置server-id，建议使用ip最后3位
    server-id=140

*  找到`从数据库`的配置文件my.cnf(或者my.ini)，我的在/etc/my.cnf,在\[mysqld\]部分插入如下：

#开启中继日志
    relay-log=mysql-relay
    #设置server-id，建议使用ip最后3位
    server-id=141

*  重启mysql服务

systemctl restart mysqld.service

## 2.6 在主机上建立账户并授权slave ##

GRANT REPLICATION SLAVE ON *.* TO 'mysql141'@'192.168.131.141' IDENTIFIED BY 'mysql141';
    
    flush privileges;
    
    --查询master的状态
    show master status\G

![这里写图片描述][70 2]  
记录上图结果中File和Position的值。  
**注意：**`执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态发生状态值变化。`

## 2.7 告知从服务器二进制文件名与位置 ##

这里要根据上面主服务器的状态来填写，不要直接用下面的SQL，需要根据实际值修改。

CHANGE MASTER TO master_host = '192.168.131.140',
     master_user = 'mysql141',
     master_password = 'mysql141',
     master_log_file = 'mysql-bin.000001',
     master_log_pos = 120;

## 2.8 查看从服务器状态 ##

//开启复制
    start slave;
    
    //查看主从复制是否配置成功
    SHOW SLAVE STATUS\G

![这里写图片描述][70 3]  
当看到Slave\_IO\_State:Waiting for master ot send event 、Slave\_IO\_Running: YES、Slave\_SQL\_Running: YES才表明状态正常。

## 2.9 测试主从复制是否成功 ##

*  Master中和Slave中执行SQL

SHOW DATABASES;

![这里写图片描述][70 4]

*  在Master中创建数据库并创建数据表并插入一条数据

create database test;
    use test;
    create table tab1(id int auto_increment,name varchar(10),primary key(id));
    insert into tab1(id,name) values (1,'why');

*  在Slave中查询这条数据  
    ![这里写图片描述][70 5]

至此，MySQL主从复制就实现了。

## 2.10 常用语句 ##

show master status: 查看master的状态，尤其是当前的日志及位置
    show slave status 查看slave的状态
    reset slave 重置slave状态
    start slave 启动slave状态
    stop slave 暂停slave状态

# 3. 读写分离实践 #

绝大多数的企业的应用场景对于数据库来说都是读多写少，比如微博，明星发一条微博，上千万人读。所以为了分担数据库压力，做负载均衡，首先考虑到的就是读写分离，读写分离基于上面实现的主从复制，使用主库作为写库，从库为读库，提高数据库性能，提高IO性能。

## 3.1 读写分离的实现方式 ##

为了实现读写分离，出现了很多解决方案，其中比较流行的是采用中间件做为Proxy，保持应用层代码不随数据库的变动而发生变化，这里包括Amoeba、Atlas、Cobar、Mycat、MySQL Proxy等，而Mycat是目前开源的数据库中间件中比较成熟的解决方案。可以说Mycat真的非常强大，但是建议慎重考虑使用，具体原因请自行百度mycat发起人和Mycat社区现状。  
　　但我们以学习的目的进行使用还是非常好的，Mycat确实非常强大，我们可以学习他的思想和技术。所以我们使用Mycat中间件作为读写分离的实现方式。  
　　《Mycat权威指南》和《分布式数据库架构及企业实践-基于mycat中间件》两本书都介绍的非常详细。

## 3.2 使用Mycat实现读写分离 ##

### 3.2.1 下载安装Mycat ###

原文链接：[https://www.cnblogs.com/joylee/p/7513038.html][https_www.cnblogs.com_joylee_p_7513038.html]

*  Mycat官网：[http://www.mycat.io/][http_www.mycat.io]  
    可以了解下Mycat的背景和应用情况，这样使用起来比较有信心。
 *  Mycat下载地址：[http://dl.mycat.io/][http_dl.mycat.io]  
    官网有个文档，属于详细的介绍，初次入门，看起来比较花时间。

**下载：**  
建议大家选择 1.6-RELEASE 版本，毕竟是比较稳定的版本。

**安装：**  
根据不同的系统选择不同的版本。包括linux、windows、mac,作者考虑还是非常周全的，当然，也有源码版的。  
![这里写图片描述][format_png 1]  
Mycat的安装其实只要解压下载的目录就可以了，非常简单。  
**安装完成后，目录如下：**

<table> 
 <thead> 
  <tr> 
   <th>目录</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>bin</td> 
   <td>mycat命令，启动、重启、停止等</td> 
  </tr> 
  <tr> 
   <td>catlet</td> 
   <td>catlet为Mycat的一个扩展功能</td> 
  </tr> 
  <tr> 
   <td>conf</td> 
   <td>Mycat 配置信息,重点关注</td> 
  </tr> 
  <tr> 
   <td>lib</td> 
   <td>Mycat引用的jar包，Mycat是java开发的</td> 
  </tr> 
  <tr> 
   <td>logs</td> 
   <td>日志文件，包括Mycat启动的日志和运行的日志。</td> 
  </tr> 
 </tbody> 
</table>

**配置**  
Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

<table> 
 <thead> 
  <tr> 
   <th>文件</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>server.xml</td> 
   <td>Mycat的配置文件，设置账号、参数等</td> 
  </tr> 
  <tr> 
   <td>schema.xml</td> 
   <td>Mycat对应的物理数据库和数据库表的配置</td> 
  </tr> 
  <tr> 
   <td>rule.xml</td> 
   <td>Mycat分片（分库分表）规则</td> 
  </tr> 
 </tbody> 
</table>

Mycat的架构其实很好理解，Mycat是代理，Mycat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说，访问的都是Mycat，不会接触到后端的数据库。  
我们现在做一个主从、读写分离，简单分表的示例。结构如下图：  
![这里写图片描述][70 6]  
　　Mycat作为主数据库中间件，肯定是与代码弱关联的，所以代码是不用修改的，使用Mycat后，连接数据库是不变的，默认端口是8066。连接方式和普通数据库一样，如：jdbc:mysql://192.168.0.2:8066/。

### 3.2.2 配置Mycat ###

我们只针对mycat实现简单的读写分离，更多其他特性如分库分表切片的功能请参看上面推荐的书，这里只针对简单的读写分离的配置。  
　　我们真实的物理数据库名称为 `itoo_cloud`，包含一个表 `ta_user`，以免大家对后面的配置不明白。

*  **server.xml**

<user name="root">
            <property name="password">root</property>
            <property name="schemas">itoo</property>
            <property name="readOnly">false</property>
            
            
        </user>

重点关注下面这段，其他默认即可  
![这里写图片描述][70 7]  
我这里配置了一个账号root密码也是root,针对逻辑数据库itoo（自己定义别名，不是真实的物理数据库）,读写权限都有，没有针对表做任何特殊的权限。

*  **schema.xml**  
    schema.xml是最主要的配置项，首先看我的配置文件。

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
        <schema name="itoo" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>
    
        <dataNode name="dn1" dataHost="auth" database="itoo_cloud" />
    
    
        <dataHost name="auth" maxCon="1000" minCon="10" balance="3"
                  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <writeHost host="hostM" url="192.168.131.140:3306" user="root" password="root">
                <readHost host="hostS1" url="192.168.131.141:3306" user="test" password="test" />
            </writeHost>
        </dataHost>
    </mycat:schema>

注意：读库的用户test是添加的mysql用户，只具有读权限的用户：

GRANT Select ON *.* TO 'test'@'%' IDENTIFIED BY "test"

下面是关于每个节点的配置说明;

![这里写图片描述][70 8]

### 3.2.3 启动mycat ###

Mycat的启动也非常简单，进入到bin目录下：

##启动
    ./mycat start
    
    ##停止
    ./mycat stop
    
    ##重启
    ./mycat restart

如果在启动时发现异常，在logs目录中查看日志。

*  wrapper.log 为程序启动的日志，启动时的问题看这个
 *  mycat.log 为脚本执行时的日志，SQL脚本执行报错后的具体错误内容,查看这个文件。mycat.log是最新的错误日志，历史日志会根据时间生成目录保存。

mycat启动后，执行命令不成功，可能实际上配置有错误，导致后面的命令没有很好的执行。

## 3.3 测试读写分离 ##

使用navicat连接mycat，如下图所示，注意端口为8066。  
![这里写图片描述][70 9]

*  测试写，插入一条数据，查看是否成功，查看从库是否数据已经同步过去
 *  测试读，这里为了确保读的是从库，我们用root账号登录从库，将刚才插入的数据name值改为别的，然后再次执行查询，看查询出的数据是否为从库的数据。（因为此时主库的name值为原来insert的，而从库的改为了别的）。

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWFnZXMyMDE1LmNuYmxvZ3MuY29tL2Jsb2cvMTA0MzYxNi8yMDE2MTIvMTA0MzYxNi0yMDE2MTIxMzE1MTE1NzU1OC0xMTUwMzA1MzUwLmpwZw?x-oss-process=image/format,png
[70]: https://img-blog.csdn.net/20180526151858493?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[https_www.cnblogs.com_cnmenglang_p_6393769.html]: https://www.cnblogs.com/cnmenglang/p/6393769.html
[70 1]: https://img-blog.csdn.net/201805261550575?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[https_blog.csdn.net_cug_jiang126com_article_details_46846031]: https://blog.csdn.net/cug_jiang126com/article/details/46846031
[https_blog.csdn.net_guotao521_article_details_45483833]: https://blog.csdn.net/guotao521/article/details/45483833
[http_hamilton.duapp.com_detail_articleId_47]: http://hamilton.duapp.com/detail?articleId=47
[70 2]: https://img-blog.csdn.net/20180526162103331?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 3]: https://img-blog.csdn.net/20180526162858868?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 4]: https://img-blog.csdn.net/20180526163234567?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 5]: https://img-blog.csdn.net/20180526164026502?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[https_www.cnblogs.com_joylee_p_7513038.html]: https://www.cnblogs.com/joylee/p/7513038.html
[http_www.mycat.io]: http://www.mycat.io/
[http_dl.mycat.io]: http://dl.mycat.io/
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly93czMuc2luYWltZy5jbi9sYXJnZS8wMDZ0TmM3OWd5MWZqZmVyc2V4eTdqMzFrdzBhYzB6Yi5qcGc?x-oss-process=image/format,png
[70 6]: https://img-blog.csdn.net/20180529115757890?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 7]: https://img-blog.csdn.net/20180529144308186?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 8]: https://img-blog.csdn.net/20180529145250980?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 9]: https://img-blog.csdn.net/20180529152357285?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3doeTE1NzMyNjI1OTk4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70