数据库系列之MySQL基于Mycat实现读写分离

最新推荐文章于 2022-12-26 21:18:33 发布

solihawk

最新推荐文章于 2022-12-26 21:18:33 发布

阅读量276

点赞数

分类专栏：数据库系列 # mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/solihawk/article/details/117697662

版权

数据库系列同时被 2 个专栏收录

55 篇文章 13 订阅

订阅专栏

mysql

28 篇文章 0 订阅

订阅专栏

MySQL主从复制读写分离的实现有很多种，比如使用中间件mysql-proxy、proxysql和mycat三种中间件实现读写分离并进行测试验证。本文主要讨论基于mycat中间件实现读写分离：

数据库系列之MySQL基于MySQL-proxy实现读写分离
 数据库系列之MySQL基于proxySQL实现读写分离
 数据库系列之MySQL基于Mycat实现读写分离

1.3 基于Mycat

1.3.1 Mycat介绍

Mycat是开源的分布式数据库中间件，对于前端用户可以看做是数据库代理，后端可以用MySQL原生协议与多个MySQL服务器进行通信，进而实现数据库表读写分离、分表分库功能。MyCAT支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库，同时也支持MongoDB等非关系型数据库。

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

1.3.2 安装配置Mycat

1）创建用户mycat

[root@tango-01 ~]# useradd mycat
[root@tango-01 ~]# passwd mycat

2）下载并安装Mycat

[root@tango-01 src]# tar -xzvf Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz
[root@tango-01 src]# mv mycat /usr/local
[root@tango-01 local]# chown -R mycat.mycat /usr/local/mycat/

3）修改server.xml中配置user节点配置用户信息，详细说明如下：

[root@tango-01 mycat]# vi conf/server.xml
<user name="root" defaultAccount="true">
                <property name="password">password</property>
                <property name="schemas">test01</property>
</user>

–name：登录的用户名，也就是连接Mycat的用户名
–password：登录的密码，也就是连接Mycat的密码
–schemas：数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个数据库db1,db2，则配置db1,dbs

4）在schema.xml文件中配置读写分离

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="test01" checkSQLschema="false" sqlMaxLimit="100" dataNode="node01">
        </schema>
        <dataNode name="node01" dataHost="192.168.112.10" database="test01" />
        <dataHost name="192.168.112.10" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="192.168.112.101" url="192.168.112.101:3306" user="root"
                                   password="password">
                        <!-- can have multi read hosts -->
                        <readHost host="192.168.112.102" url="192.168.112.102:3306" user="root" password="password" />
                        <readHost host="192.168.112.103" url="192.168.112.103:3306" user="root" password="password" />
                </writeHost>
        </dataHost>

</mycat:schema>

schema：数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode：分片信息，也就是分库相关配置
dataHost：物理数据库，真正存储数据的数据库
balance的取值决定了负载均衡对非事务内的读操作的处理，balance属性负载均衡类型，目前的取值有4种：
balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
balance=“1”，全部的readHost与 stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且M1与M2互为主备)，正常情况下， M2,S1,S2都参与select语句的负载均衡
balance=“2”，所有读操作都随机的在writeHost、readhost上分发
balance=“3”，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost 不负担读压力，注意balance=3只在1.4及其以后版本有，1.3没有
writeType属性，负载均衡类型，目前的取值有 3 种：
writeType="0"所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties
writeType="1"所有写操作都随机的发送到配置的writeHost
writeType="2"没实现
主从切换（双主failover）：switchType属性
switchType="-1"：不自动切换
switchType=“1”：默认值，自动切换
switchType=“2”：基于MySQL主从同步的状态来决定是否切换。需修改heartbeat语句（即心跳语句）：show slave status
switchType=“3”：基于Mysql Galera Cluster（集群多节点复制）的切换机制。需修改heartbeat语句（即心跳语句）：show status like ‘wsrep%’

5）启动Mycat

[root@tango-01 mycat]# ./bin/mycat start
Starting Mycat-server...
[root@tango-01 mycat]# ./bin/mycat status
Mycat-server is running (110818).
[root@tango-01 mycat]# lsof -i:8066
COMMAND    PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    112844 root   81u  IPv6 1642396      0t0  TCP *:8066 (LISTEN)
[root@tango-01 mycat]# lsof -i:9066
COMMAND    PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    112844 root   77u  IPv6 1642391      0t0  TCP *:9066 (LISTEN)

6）连接mycat

[root@tango-01 mysql]# ./bin/mysql -uroot -ppassword -h192.168.112.10 -P8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.1-release-20200209222254 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+----------+
| DATABASE |
+----------+
| test01   |
+----------+
1 row in set (0.00 sec)

mysql> use test01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_test01 |
+------------------+
| tb01             |
+------------------+
1 row in set (0.01 sec)

mysql> select * from test01.tb01;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user5    | 123      |
| user6    | 123      |
| user4    | 123      |
+----------+----------+
6 rows in set (0.09 sec)

mysql>

1.3.3 测试读写分离效果

1）将mycat的日志输出级别改完debug（默认是info级别），在conf/log4j2.xml里配置，然后去查询去添加数据在logs/mycat.log日志文件里查看sql被路由到了哪个服务器上

[root@tango-01 mycat]# vi conf/log4j2.xml
把
<asyncRoot level="info" includeLocation="true">
改成
<asyncRoot level="debug" includeLocation="true">

2）重启mycat服务

[root@tango-01 mycat]# ./bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@tango-01 mycat]# lsof -i:8066
COMMAND    PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    113881 root   81u  IPv6 1655125      0t0  TCP *:8066 (LISTEN)

3）登录mycat更新表数据

[root@tango-01 mysql]# ./bin/mysql -uroot -ppassword -h192.168.112.10 -P8066

通过mycat写入一条数据或读数据，查看sql被路由到了代理的哪个mysql服务器上了：

mysql> insert into test01.tb01 values('user7','1234');
Query OK, 1 row affected (0.36 sec)

mysql> select * from test01.tb01;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 123      |
| user3    | 123      |
| user5    | 123      |
| user6    | 123      |
| user4    | 123      |
| user7    | 1234     |
+----------+----------+
7 rows in set (0.01 sec)

mysql>

4）然后查看mycat.log的debug日志，观察mysql的sql被路由到了哪个服务器上了

2020-12-19 00:08:16.769 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:463)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=2, lastTime=1608307696768, user=root, schema=test01, old shema=test01, borrowed=true, fromSlaveDB=false, threadId=6040, charset=utf8, txIsolation=3, autocommit=true, attachment=node01{insert into test01.tb01 values('user7','1234')}, respHandler=SingleNodeHandler [node=node01{insert into test01.tb01 values('user7','1234')}, packetId=0], host=192.168.112.101, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
2020-12-19 00:08:16.828 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection [id=2, lastTime=1608307696765, user=root, schema=test01, old shema=test01, borrowed=true, fromSlaveDB=false, threadId=6040, charset=utf8, txIsolation=3, autocommit=true, attachment=node01{insert into test01.tb01 values('user7','1234')}, respHandler=SingleNodeHandler [node=node01{insert into test01.tb01 values('user7','1234')}, packetId=1], host=192.168.112.101, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
…
2020-12-19 00:43:11.025 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:463)) - con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=13, lastTime=1608309791025, user=root, schema=test01, old shema=test01, borrowed=true, fromSlaveDB=true, threadId=6562, charset=utf8, txIsolation=3, autocommit=true, attachment=node01{select test01.tb01}, respHandler=SingleNodeHandler [node=node01{select test01.tb01}, packetId=0], host=192.168.112.102, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

从日志中可以看出,"insert into …"插入语句路由到了192.168.112.101的主机上了，"select * …"的查询语句路由到了host=192.168.112.102的从机上了，这就实现了mysql的读写分离。

1.4 总结

mysql-proxy是官方自带的中间件，功能上相对简单些，配置也相对容易，但是在验证读写分离时候需要借助TCPDUMP等网络包数据进行确认，不够直观。
proxysql配置上相对简单，通过定义写组和读组将mysql服务器定义进去即可实现读写分离，同时有系统表可以查询读写分离的执行情况，确认结果更直接明确。
Mycat是功能强大的开源中间件，除了读写分离还可以实现其它分库分表、分布式事务等功能，配置上相较于前两种要复杂些，读写分离的验证环节也比proxysql复杂些，需要开启日志进行确认，相对没有那么直观。

参考资料：

转载请注明原文地址：https://blog.csdn.net/solihawk/article/details/117697662
文章会同步在公众号“牧羊人的方向”更新，感兴趣的可以关注公众号，谢谢！

solihawk

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据库系列之MySQL基于Mycat实现读写分离

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

扫一扫

专栏目录