提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  IT要闻

当前栏目

MyCAT实现MySQL读写分离

2023-02-18 16:34:37 时间
MySQL中间件：

用户连接到MySQL的中间件（代理），中间件接收用户的访问转发给后端的mysql数据库。

MyCat:

是MySQL的一个中间件软件，Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器。
用户通过mysql客户端工具访问到Mycat，Mycat将这些请求转发给后端的MySQL服务器。

MyCat的工作原理：

Mycat它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析：判断用户是读还是写，然后把请求发给后端对应的MySQL数据库，并将MySQL返回的数据信息经过处理后返回给用户。

Mycat的安装：
[root@CentOS8 ~]# yum install java -y

[root@CentOS8 ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-
1.6.7.4-release-20200105164103-linux.tar.gz

[root@CentOS8 ~]# mkdir /apps

[root@CentOS8 ~]# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz  -C /apps
Mycat的目录结构：
[root@CentOS8 mycat]# tree -L 1
.
├── bin
├── catlet
├── conf
├── lib
├── logs
└── version.txt

bin mycat命令，启动、重启、停止等

catlet catlet为Mycat的一个扩展功能

conf Mycat 配置信息,重点关注

lib Mycat引用的jar包，Mycat是java开发的

logs 日志文件，包括Mycat启动的日志和运行的日志

version.txt mycat版本说明

logs目录:
wrapper.log mycat启动日志

mycat.log mycat详细工作日志
Mycat的配置文件
[root@CentOS8 conf]# tree -L 1
.
......
├── rule.xml
├── schema.xml
......
├── server.xml
.......
几个重要的配置文件:

  1. server.xml： Mycat软件自身的相关配置。

  2. schema.xml： Mycat连接MySQL服务器的相关配置

  3. rule.xml： Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等

Mycat 主要配置文件说明
server.xml

存放Mycat软件本身相关的配置文件，比如：连接Mycat的用户，密码，数据库名称等

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
	在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
	<property name="useHandshakeV10">1</property>
    <property name="removeGraveAccent">1</property>
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
		<property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
		<property name="sequnceHandlerType">1</property>
		<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
		INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
		-->
		<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
		<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
	<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
	<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
		<property name="processorBufferPoolType">0</property>
		<!--默认是65535 64K 用于sql解析时最大文本长度 -->
		<!--<property name="maxStringLiteralLength">65535</property>-->
		<!--<property name="sequnceHandlerType">0</property>-->
		<!--<property name="backSocketNoDelay">1</property>-->
		<!--<property name="frontSocketNoDelay">1</property>-->
		<!--<property name="processorExecutor">16</property>-->
		<!--
			<property name="serverPort">8066</property> <property name="managerPort">9066</property> 
			<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
			<property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
			<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
		<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		
			<!--
			off heap for merge/order/group/limit      1开启   0关闭
		-->
		<property name="useOffHeapForMerge">0</property>

		<!--
			单位为m
		-->
        <property name="memoryPageSize">64k</property>

		<!--
			单位为k
		-->
		<property name="spillsFileBufferSize">1k</property>

		<property name="useStreamOutput">0</property>

		<!--
			单位为m
		-->
		<property name="systemReserveMemorySize">384m</property>


		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">false</property>

		<!-- XA Recovery Log日志路径 -->
		<!--<property name="XARecoveryLogBaseDir">./</property>-->

		<!-- XA Recovery Log日志名称 -->
		<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
		<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
		<property name="strictTxIsolation">false</property>
		
		<property name="useZKSwitch">true</property>
		<!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
		<property name="parallExecute">0</property>
	</system>
	
	<!-- 全局SQL防火墙设置 -->
	<!--白名单可以使用通配符%或着*-->
	<!--例如<host host="127.0.0.*" user="root"/>-->
	<!--例如<host host="127.0.*" user="root"/>-->
	<!--例如<host host="127.*" user="root"/>-->
	<!--例如<host host="1*7.*" user="root"/>-->
	<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
	<!--
	<firewall>
	   <whitehost>
	      <host host="1*7.0.0.*" user="root"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->

	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">TESTDB</property>
		<property name="defaultSchema">TESTDB</property>
		<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">true</property>
		<property name="defaultSchema">TESTDB</property>
	</user>

</mycat:server>
参数 说明
user 用户配置节点
name 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。
password 客户端登录MyCAT的密码
schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2
privileges 配置用户针对表的增删改查的权限
readOnly mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false
注意：

  • server.xml文件里登录mycat的用户名和密码可以任意定义，这个账号和密码是为客户机登录mycat时使用的账号信息

  • 逻辑库名(如上面的TESTDB，也就是登录mycat后显示的库名，切换这个库之后，显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义，否则会导致mycat服务启动失败！

  • 这里只定义了一个标签，所以把多余的都注释了。如果定义多个标签，即设置多个连接mycat的用户名和密码，那么就需要在schema.xml文件中定义多个对应的库！

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

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<!-- auto sharding by id (long) -->
		<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
		<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
			<childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable>
		</table>
		<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
			/> -->
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />
	<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
	 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
	<dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
	<dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
		</writeHost>
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
schema.xml文件中配置的参数解释说明：:
参数 说明
schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
dataNode 分片信息，也就是分库相关配置
dataHost 物理数据库，真正存储数据的数据库
配置说明:

schema:

属性 说明
name 逻辑数据库名，与server.xml中的schema对应
checkSQLschema 数据库前缀相关设置，这里为false
sqlMaxLimit select 时默认的limit，避免查询全表

table

属性 说明
name 表名， 物理数据库中表名
dataNode 表存储到哪些节点，多个节点用逗号分隔。节点为下文dataNode设置的name
primaryKey 主键字段名，自动生成主键时需要设置
autoIncrement 是否自增
rule 分片规则名，具体规则下文rule详细介绍

dataNode

属性 说明
name 节点名，与table中dataNode对应
datahost 物理数据库名，与datahost中name对应
database 物理数据库中数据库名

dataHost

属性 说明
name 物理数据库名，与dataNode中dataHost对应
balance 均衡负载的方式
writeType 写入方式
dbType 数据库类型
heartbeat 心跳检测语句，注意语句结尾的分号要加
注意：

schema.xml文件中有三点需要注意：balance="1"，writeType="0" ,switchType="1"

schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。

balance 属性的取值范围：

  • 0：不开启读写分离模式

  • 1.表示开启读写分离模式

  • 2：所有读操作都随机的在writeHost、 readhost上分发

  • 3：所有读请求随机的分发到wiriterHost对应的readhost执行（只在 1.4 及其以后版本 有，1.3没有）

writeHost和readHost 标签：

这两个标签都指定后端数据库的相关配置给mycat，用于实例化后端连接池。

唯一不同的是：writeHost指定写实例、readHost指定读实例，组着这些读写实例来满足系统的要求。

注意：
Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mycat的
主从复制将数据复制到readhost

范例：使用Mycat实现读写分离

实验环境：CentOS8.3
Mycat服务器：10.0.0.10 -- 2G内存以上，内存太小跑不动java
mysql：master：10.0.0.13 -- MySQL8.0
MySQL：slave:10.0.0.14 --- mysql8.0

实验准备：
关闭selinux、防火墙
设置时间同步
创建mysql主从数据库

安装mycat：

[root@CentOS8 ~]# yum install java -y

#下载并安装
[root@centos8 ~]#wgethttp://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-
1.6.7.6-release-20210303094759-linux.tar.gz

[root@centos8 ~]#mkdir /apps
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/  #-C:解压到对应的目录

配置环境变量：

[root@CentOS8 bin]# pwd
/apps/mycat/bin

echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh

source /etc/profile.d/mycat.sh

安装好Mycat后启动mycat：
启动方法：

[root@CentOS8 bin]# mycat 
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }

[root@CentOS8 bin]# mycat start 
Starting Mycat-server...

#启动以后查看mycat监听的端口：
[root@CentOS8 bin]# ss -ntlup
Netid     State      Recv-Q     Send-Q          Local Address:Port           Peer Address:Port                                                                   
udp       UNCONN     0          0              0.0.0.0%virbr0:67                  0.0.0.0:*         users:(("dnsmasq",pid=1266,fd=3))                            
udp       UNCONN     0          0                     0.0.0.0:111                 0.0.0.0:*         users:(("rpcbind",pid=743,fd=5),("systemd",pid=1,fd=78))     
udp       UNCONN     0          0                        [::]:111                    [::]:*         users:(("rpcbind",pid=743,fd=7),("systemd",pid=1,fd=80))     
tcp       LISTEN     0          128                   0.0.0.0:111                 0.0.0.0:*         users:(("rpcbind",pid=743,fd=4),("systemd",pid=1,fd=77))     
tcp       LISTEN     0          128                   0.0.0.0:22                  0.0.0.0:*         users:(("sshd",pid=827,fd=4))                                
tcp       LISTEN     0          1                   127.0.0.1:32000               0.0.0.0:*         users:(("java",pid=89594,fd=4))                              
tcp       LISTEN     0          128                      [::]:111                    [::]:*         users:(("rpcbind",pid=743,fd=6),("systemd",pid=1,fd=79))     
tcp       LISTEN     0          128                      [::]:22                     [::]:*         users:(("sshd",pid=827,fd=6))                                
tcp       LISTEN     0          50                          *:35287                     *:*         users:(("java",pid=89594,fd=66))                             
tcp       LISTEN     0          50                          *:39839                     *:*         users:(("java",pid=89594,fd=68))                             
tcp       LISTEN     0          50                          *:1984                      *:*         users:(("java",pid=89594,fd=67))                             
tcp       LISTEN     0          100                         *:8066                      *:*         users:(("java",pid=89594,fd=91))                             
tcp       LISTEN     0          70                          *:33060                     *:*         users:(("mysqld",pid=87104,fd=32))                           
tcp       LISTEN     0          100                         *:9066                      *:*         users:(("java",pid=89594,fd=87))                             
tcp       LISTEN     0          128                         *:3306                      *:*         users:(("mysqld",pid=87104,fd=34))
#8066端口用于连接MyCAT

查看mycat的日志，确认是否启动成功

[root@CentOS8 bin]# tail /apps/mycat/logs/wrapper.log
STATUS | wrapper  | 2022/09/27 00:29:37 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/09/27 00:29:38 | Launching a JVM...
INFO   | jvm 1    | 2022/09/27 00:29:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/09/27 00:29:40 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/09/27 00:29:40 | 
INFO   | jvm 1    | 2022/09/27 00:29:45 | MyCAT Server startup successfully. see logs in logs/mycat.log

在客户端使用默认密码123456来连接mycat

root@ubuntu1804-1:~# mysql -uroot -p123456 -h 10.0.0.10 -P8066
mysql: [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 4
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> exit
Bye

在mycat 服务器上修改server.xml文件配置Mycat的连接信息
例如：指定连接名、密码和用户连接上来能看到的数据库名

#这里只将端口号修改为了3306 下面可以修改默认账号和密码
<property name="serverPort">3306</property> <property name="managerPort">9066</property> 
                        <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
                        <property name="dataNodeIdleCheckPeriod">300000</property> 
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>

修改schema.xml实现读写分离策略

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

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>

	<dataNode name="dn1" dataHost="localhost1" database="hellodb" />

	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="hostM1" url="10.0.0.13:3306" user="root" password="123456">
			<readHost host="hostS1" url="10.0.0.14:3306" user="root" password="123456" />

		</writeHost>
	</dataHost>

</mycat:schema>

客户端连接到mycat：

root@ubuntu1804-1:~# mysql -uroot -p123456 -h 10.0.0.10 -P3306
mysql: [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 2
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

测试：分别开启master和slave的通用日志，会发现读操作都在slave上进行，写操作都在master上进行。

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'general_log_file';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log_file | /var/lib/mysql/CentOS8.log |
+------------------+----------------------------+
1 row in set (0.02 sec)

相关文章