【006】Linux MySQL 学习笔记-读写分离（mycat，proxy，router）

DeusExMachina_V

已于 2022-05-27 14:29:50 修改

阅读量164

点赞数

分类专栏： Linux Mysql学习笔记文章标签： mysql 数据库 linux

于 2022-05-14 11:45:58 首次发布

本文链接：https://blog.csdn.net/DeusExMachina_V/article/details/124765743

版权

Linux Mysql学习笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1 读写分离

数据库中间件: mycat , atlas, mysql-proxy, mysql-router, cobar …

mycat: 开源, 社区活跃度高, 功能丰富(读写分离,分库分表等), 使用方便, 性能低.
mysql-router: 官方出品, 功能较少(读写分离), 使用较不方便(读操作和写操作使用不同端口), 性能高.
atlas: https://gitee.com/mirrors/atlas?utm_source=alading&utm_campaign=repo

2 Mycat

官方网站: http://www.mycat.org.cn/

2.1 Mycat简介

MyCat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理（类似于Mysql Proxy），用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。

MyCat发展到目前的版本，已经不是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。而在最终用户看来，无论是那种存储方式，在MyCat里，都是一个传统的数据库表，支持标准的SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发难度，提升开发速度

2.1.2 Mycat应用场景

Mycat发展到现在，适用的场景已经很丰富，而且不断有新用户给出新的创新性的方案，以下是几个典型的应用场景：

单纯的读写分离，此时配置最为简单，支持读写分离，主从切换；
分表分库，对于超过1000万的表进行分片，最大支持1000亿的单表分片；
多租户应用，每个应用一个库，但应用程序只连接Mycat，从而不改造程序本身，实现多租户化；
报表系统，借助于Mycat的分表能力，处理大规模报表的统计；
替代Hbase，分析大数据；
作为海量数据实时查询的一种简单有效方案，比如100亿条频繁查询的记录需要在3秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时Mycat可能是最简单有效的选择；

2.1.3 Mycat不适合的应用场景

设计使用Mycat时有非分片字段查询，请慎重使用Mycat，可以考虑放弃！
设计使用Mycat时有分页排序，请慎重使用Mycat，可以考虑放弃！
设计使用Mycat时如果要进行表JOIN操作，要确保两个表的关联字段具有相同的数据分布，否则请慎重使用Mycat，可以考虑放弃！
设计使用Mycat时如果有分布式事务，得先看是否得保证事务得强一致性，否则请慎重使用Mycat，可以考虑放弃！

2.1.4 Mycat工作原理

Mycat的原理并不复杂，复杂的是代码。Mycat的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。

2.1.5 Mycat分片策略

Mycat支持水平分片与垂直分片：
水平分片：一个表格的数据分割到多个节点上，按照行分隔。
垂直分片：一个数据库中多个表格A，B，C，A存储到节点1上，B存储到节点2上，C存储到节点3上。
Mycat通过定义表的分片规则来实现分片，每个表格可以捆绑一个分片规则，每个分片规则指定一个分片字段并绑定一个函数，来实现动态分片算法。

2.2 mycat配置读写分离

2.2.1 原理

在这里插入图片描述
写：高可用
读：负载均衡

2.2.2 环境准备

服务器准备:
mycat: 192.168.10.30
master1: 192.168.10.31
master2: 192.168.10.32
slave1: 192.168.10.41
slave2: 192.168.10.42
所有机器关闭防火墙,selinux
配置好主机名,互相解析

为了展示效果, 在此不配置主从关系, 在实际工作中一定要配置好

2.2.3 软件安装

mycat:安装jdk,mycat

安装java环境

  # tar xf jdk-8u181-linux-x64.tar.gz -C /usr/local/
  # ln -s /usr/local/jdk1.8.0_181/ /usr/local/java
  # vim /etc/profile.d/java.sh
    export JAVA_HOME=/usr/local/java
    export PATH=$JAVA_HOME/bin:$PATH
  # . /etc/profile.d/java.sh
  # java -version
    java version "1.8.0_181"
    Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
    Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

安装mycat

  # tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

mysql: 在所有mysql机器上安装mysql软件(略)

2.2.4 配置

mysql 配置:
1.在所有节点上授权mycat机器能够远程操作.我授权的是mycat用户,密码为mycat
2.在所有节点上创建一个相同的库(如db1),在此库中创建一个同名的表(如t1),在此表中插入不同的内容,以分辨读取的哪个表中的内容.

mycat配置: 主要是两个配置文件: server.xml, schema.xml

# cd /usr/local/mycat/conf/

配置server.xml, 这个文件主要配置客户端如何连接mycat

# vim server.xml

  ...
  <user name="root">                                                        #此处为连接mycat的用户名与密码
        <property name="password">root</property>
        <property name="schemas">TESTDB</property>          #数据逻辑库,如果有多个逻辑库可使用","分开

  </user>
  ...
  <user name="user">                                                       #这里配置的是一个只读用户,此用户不可进行写操作
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
  </user>
  ...

配置schema.xml, 这个文件用来配置分库分表及读写分离. 这是我们重点配置的文件.

# vim schema.xml

在这里插入图片描述

schema 标签, 总标签, 包括虚拟库, 以及连接属性, 还有虚拟节点
name - 定义了虚拟库的名字，这个名字需要和server.xml定义一致
dataNode - 定义了虚拟节点名字，这个需要和下一级的dataNode标签里的name属性一致

dataNode 标签，包括dataNode的名字，虚拟host属性，以及要使用后端物理机那个数据库
name - 定义dataNode的名字，该属性提供给schema里的dataNode使用，须一致
dataHost  - 可自定义，下一级标签需要使用该信息
database  - 指定使用后端物理机的那个数据库，就是把上级标签定义的虚拟数据库映射到后端物理机上的那个真实的数据库，该数据库在后端物理机上的Mysql是真实存在的

dataHost 标签，包括后端物理机的属性，地址，端口，以及用户名密码，还有该物理机是负责写还是读，等等
name - 使用上级标签也就是dataNode里的 dataHost的名字，这个必须保持一致
balance - 读负载均衡类型，目前的取值有 4 种：
  0 - 不开启读写分离机制，所有读操作都发送到当前可用的writeHost 上。
  1 - 全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，如上图中的mysql2,3,4都参与读负载均衡
  2 - 所有读操作都随机的在 writeHost、 readhost 上分发。
  3 - 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有， 1.3 没有。
writeType - 写负载均衡类型，目前的取值有 3 种：
  0 - 所有写操作发送到配置的第一个writeHost，第一个挂了切到备writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
  1 - 所有写操作都随机的发送到配置的 writeHost。
  2 - 没实现。
switchType - 切换类型
  -1 - 表示不自动切换
  1 - 默认值，自动切换
  2 - 基于MySQL 主从同步的状态决定是否切换

writeHost 标签, 定义后端物理机属性，定义了该物理机是可读还是可写，可以有多个。
heartbeat 标签, 定义如何探测后端物理机是否存活，使用select user语句去探测

2.2.5 启动服务并验证

启动mycat服务

# /usr/local/mycat/bin/mycat start &

查看是否启动成功

# netstat -tanp |grep .066
    tcp6       0      0 :::9066                 :::*                    LISTEN      1422/java        #管理端口   
    tcp6       0      0 :::8066                 :::*                    LISTEN      1422/java        #工作端口

验证
连接工作端口
1).客户端连接后进入到虚拟库查看表的内容,验证读负载均衡
2).插入一些数据验证写操作
3).关闭主写库验证写库切换

管理端口常用命令
连接管理端口: # mysql -uroot -proot -h 192.168.10.30 -P9066

> show @@help;         #列出所有管理命令
> reload @@config;     #更新 schema.xml 配置文件，例如更新 schema.xml 文件后在命令窗口输入该命令，不用重启即可进行配置文件的更新
> reload @@config_all;   #该命令用于更新所有配置文件，不用重启即可进行配置文件的更新
> show @@datanode;    #用于查询显示 Mycat 数据节点列表
> show @@version;       #查看版本
> show @@connection;   #查看连接信息
> show @@backend;      #查看后端连接状态

3 proxy

Mysql proxy 实现读写分离的原理就是将事务性语句交给主服务器操作，让从数据库处理select 查询。

在这里插入图片描述

3.1 配置mysql代理

准备工作
1.关闭防火墙
2.关闭selinux
3.配置固定ip地址
4.配置好yum源
5.修改主机名
6.配置好ssh双机互信
实验环境
master server：192.168.1.3 master.qf.com
slave server: 192.168.1.4 slave.qf.com
proxy server: 192.168.1.5 proxy.qf.com 无需安装mysql
两台mysql：
启动服务
建立用户并授权在主服务器上操作

MariaDB [(none)]> grant all on *.* to proxy@'%' identified by '123456';

proxy：

[root@proxy tmp]# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz 
   
[root@proxy tmp]# mv mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

[root@proxy tmp]# cd /usr/local/mysql-proxy/

[root@proxy mysql-proxy]# mkdir lua      #建脚本存放目录

[root@proxy mysql-proxy]# mkdir logs   #创建日志目录

[root@proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua  ./lua   #读写分离配置文件

[root@proxy mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua  ./lua   #脚本文件

[root@proxy mysql-proxy]# vim /etc/mysql-proxy.cnf   #创建配置文件
    [mysql-proxy]
    user=root
    admin-username=proxy
    admin-password=123456
    proxy-address=192.168.1.5:4040   #proxy地址端口
    proxy-read-only-backend-addresses=192.168.1.4   #只读服务器地址
    proxy-backend-addresses=192.168.1.3    #读写服务器地址
    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
    daemon=true
    keepalive=true
    
[root@proxy mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf

[root@proxy mysql-proxy]# vim /usr/local/mysql-proxy/lua/rw-splitting.lua 
        min_idle_connections = 1,    #默认超过4个连接才启用读写分离，改为1
        max_idle_connections = 1,    #默认为8，改为1

[root@proxy mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

3.2 测试：

1.连接proxy创建库或表，查看库所在的mysql服务器，如果库在主mysql服务器上表示成功

# mysql  -u proxy -h 192.168.1.5 -P 4040 -p123

4 router

实验: 使用mysql-router实现读写分离及负载均衡
实验目的: 客户端通过代理服务器连接数据库,将写操作分配到master服务器,将读操作平均分配到master和slave服务器

4.1 实验规划

router:  192.168.10.13
master: 192.168.10.11
slave:    192.168.10.12 
![在这里插入图片描述](https://img-blog.csdnimg.cn/420e81523b984ef89e6a1bc872001185.png#pic_center)    *为了验证实验效果,本实验关闭主从复制功能(实际工作中勿关闭)

4.2 环境准备

所有机器关闭防火墙
所有MySQL服务器上授权帐户
master & slave:

 > grant all on *.* to wing@'%' identified by '123';
    > flush privileges;

创建读测试表:
master:

> create table test.test(id int);
> insert into test.test set id=1;

slave:

> create table test.test(id int);
> insert into test.test set id=2;

4.3 router代理配置

安装mysql-router软件

# tar xf /mysql-router-2.1.4-linux-glibc2.12-x86-64bit.tar.gz
# mv mysql-router-2.1.4-linux-glibc2.12-x86-64bit/ /usr/local/mysql-router

创建配置文件

    # cd /usr/local/mysql-router
    # cp share/doc/mysqlrouter/sample_mysqlrouter.conf  ./mysqlrouter.conf
    # egrep -v "^#|^$" mysqlrouter.conf 
        [logger]
        level = INFO                               #日志级别
        [routing:basic_failover]
        bind_address = 192.168.10.13  #监听地址
        bind_port = 7001                       #写操作端口
        mode = read-write                     #模式:可写,调度策略为主备
        destinations = 192.168.10.11,192.168.10.12  #写服务器地址
        
        [routing:balancing]                     #配置负载均衡
        bind_address = 192.168.10.13   
        bind_port = 7002                        #读操作端口
        mode = read-only                    #模式:只读,调度策略为轮循
        destinations = 192.168.10.12,192.168.10.13,192.168.10.14
  3-3.启动服务
    # /usr/local/mysql-router/bin/mysqlrouter  &
    # netstat -tanp |grep 700               #查看服务是否启动成功
        tcp  0  0 192.168.10.13:7001   0.0.0.0:*               LISTEN      1002/mysqlrouter    
        tcp  0  0 192.168.10.13:7002   0.0.0.0:*               LISTEN      1002/mysqlrouter

4.4 客户端验证

1.验证读操作

  # mysql -uwing -p123 -h 192.168.10.13 -P 7002 -e "select * from test.test"
        +------+
        | id   |
        +------+
         |    1 |
        +------+
    # mysql -uwing -p123 -h 192.168.10.13 -P 7002 -e "select * from test.test"
        +------+
        | id  |
        +------+
         |   2 |
        +------+

验证写操作

 # mysql -uwing -p123 -h 192.168.10.13 -P 7001

注意:
mode 只影响调度策略,不能影响服务器,对于mode为read-only的服务器,需要将服务器本身设置为只读.只读属性不影响AB复制同步数据.

Reference

学习笔记