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复杂些,需要开启日志进行确认,相对没有那么直观。
参考资料:
- http://mycat.org.cn/document/mycat-definitive-guide.pdf
- https://www.cnblogs.com/kevingrace/p/9365840.html
转载请注明原文地址:https://blog.csdn.net/solihawk/article/details/117697662
文章会同步在公众号“牧羊人的方向”更新,感兴趣的可以关注公众号,谢谢!