数据库中间件-MyCAT
2021-06-15 本文已影响0人
一个小运维
MyCAT概述
-
mycat是基于Java的分布式数据库系统中间件，为高并发环境的分布式存储提供解决方案
-
适合数据大量写入的存储需求
-
支持MySQL、Oracle、Sqlserver、Mongodb等
-
提供数据读写分离服务
-
提供数据分片服务
-
基于阿里巴巴Cobar进行研发的开源软件
分片规则
- 枚举法 sharding-by-intfile
- 固定分片 rule
- 范围约定 auto-sharding-long
- 求模法 mod-long
- 日期列分区法 sharding-by-date
- 通配取模 sharding-by-pattern
- ASCII码求模通配 sharding-by-prefixpattern
- 编程指定 sharding-by-substring
- 字符串拆分hash解析 sharding-by-stringhash
- 一致性hash sharding-by-murmur
工作过程
-
解析SQL命令涉及到的表
-
然后看对表的配置，如果有分片规则，则获取SQL命令里分片字段的值，并匹配分片函数，获得分片列表
-
然后将SQL命令发往对应的数据库服务器去执行
-
最后收集和处理所有分片结果数据，并返回到客户端
配置MyCat服务器
- 部署MyCat 运行环境
[root@mycat1 ~]# yum -y install java-1.8.0-openjdk
[root@mycat1 ~]# which java
/usr/bin/java
[root@mycat1 ~]# java -version
openjdk version "1.8.0_222-ea"
OpenJDK Runtime Environment (build 1.8.0_222-ea-b03)
OpenJDK 64-Bit Server VM (build 25.222-b03, mixed mode)
- 安装软件包
[root@mycat1 ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat1 ~]# mv mycat /usr/local/
# 设置PATH环境变量
[root@mycat1 ~]# vim /etc/profile.d/mycat.sh
export PATH=$PATH:/usr/local/mycat/bin
[root@mycat1 ~]# source /etc/profile.d/mycat.sh
- 修改配置文件：设置连接账号及逻辑库
# 保持默认配置即可
[root@mycat1 ~]# vim /usr/local/mycat/conf/server.xml
# 该文件中用户及逻辑库的说明如下：
<user name="root"> <!--连接mycat服务时使用的用户名-->
<property name="password">123456</property> <!--用户连接mycat用户时使用的密码-->
<property name="schemas">TESTDB</property> <!--逻辑库名-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property> <!--只读权限，连接mycat服务后只有读记录的权限,不写这一行则是可读可写 -->
</user>
- 修改配置文件：配置数据分片
[root@mycat1 ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <!--对TESTDB库下的表做分片存储 -->
<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <!-- 对travelrecord表做分片存储 -->
<!-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node -->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<!-- random sharding using mod sharind rule -->
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
rule="mod-long" /> -->
<table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id" />
</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="mysql1" database="db1" />
<dataNode name="dn2" dataHost="mysql2" database="db2" />
<dataNode name="dn3" dataHost="mysql3" database="db3" />
<!-- 定义mysql1主机名对应的数据库服务器ip地址 -->
<dataHost name="mysql1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.11:3306" user="mycatadmin"
password="TEST2021@guodong.com">
</writeHost>
</dataHost>
<dataHost name="mysql2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.1.12:3306" user="mycatadmin"
password="TEST2021@guodong.com">
</writeHost>
</dataHost>
<dataHost name="mysql3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.1.13:3306" user="mycatadmin"
password="TEST2021@guodong.com">
</writeHost>
</dataHost>
</mycat:schema>
配置数据库服务器
[root@mysql1 ~]# mysql -uroot -pTEST2021@guodong.com
mysql> create database db1 default charset utf8mb4;
mysql> grant all on *.* to mycatadmin@'%' identified by 'TEST2021@guodong.com';
[root@mysql2 ~]# mysql -uroot -pTEST2021@guodong.com
mysql> create database db2 default charset utf8mb4;
mysql> grant all on *.* to mycatadmin@'%' identified by 'TEST2021@guodong.com';
[root@mysql3 ~]# mysql -uroot -pTEST2021@guodong.com
mysql> create database db3 default charset utf8mb4;
mysql> grant all on *.* to mycatadmin@'%' identified by 'TEST2021@guodong.com';
启动MyCat
- 在MyCat服务器上测试到3台数据库服务器的连接账户
# 安装mysql客户端软件
[root@mycat1 ~]# yum install -y mysql-community-client
[root@mycat1 ~]# mysql -h192.168.1.11 -umycatadmin -pTEST2021@guodong.com
[root@mycat1 ~]# mysql -h192.168.1.12 -umycatadmin -pTEST2021@guodong.com
[root@mycat1 ~]# mysql -h192.168.1.13 -umycatadmin -pTEST2021@guodong.com
- 启动服务
[root@mycat1 ~]# mycat start
[root@mycat1 ~]# netstat -tlnp | grep :8066
tcp6 0 0 :::8066 :::* LISTEN 13835/java
- 客户端连接测试
[root@node10 ~]# mysql -h192.168.1.15 -P8066 -uroot -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB;
mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
9 rows in set (0.00 sec)
