Mycat概述
Mycat并不是一个存储数据的服务器,更像是一个连接各种数据的数据引擎。对于开发人员来说,Mycat就是一款分库分表的工具。只是该工具对于分库分表的性能相对高处很多,而且他不仅仅可以用做分库分表,还能用做读写分离、以及容灾备份。
Mycat为什么被称为数据库中间件
被称为中间件要从他的一个主要功能说起,那就是分片分库。对于分片分库的所有支持和操作,不管是路由,分发等等都并没有给真是的数据库带来存储的类似存储引擎的作用,只是对保证了多库,分表的数据有效分离和获取。图中的位置就是为了突出Mycat它的一个作用,已经它在我们整个系统架构中间的位置。
Mycat的安装与配置
这里安装配置不多说,就是安装JDK和mycat的解压,mycat不需要预编译安装,解压直接使用就行
[root@mysql ] yum list java-*
[root@mysql ] yum install java-1.8.0-openjdk
[root@mysql ]mv Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz /usr/src/
[root@mysql ]tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
[root@mysql ]cd /usr/local/mycat/bin
[root@mysql bin]# ./mycat start
[root@mysql ] yum list java-*
[root@mysql ] yum install java-1.8.0-openjdk
[root@mysql ]mv Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz /usr/src/
[root@mysql ]tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
[root@mysql ]cd /usr/local/mycat/bin
[root@mysql bin]# ./mycat start
Mycat的报错解决
[root@mysql ~]# tail -100f /usr/local/mycat/logs/wrapper.log
STATUS | wrapper | 2020/03/27 08:39:11 | Launching a JVM...
INFO | jvm 2 | 2020/03/27 08:39:12 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
ERROR | wrapper | 2020/03/27 08:39:12 | JVM exited while loading the application.
INFO | jvm 2 | 2020/03/27 08:39:12 | Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: mysql: mysql: Name or service not known
如果mycat报错,无非是内存问题或者是主机名的问题,修改方法如下所示
[root@mysql ~]# vim /usr/local/mycat/conf/wrapper.conf
wrapper.java.additional.5=-XX:MaxDirectMemorySize=512M
wrapper.java.additional.10=-Xmx512M
wrapper.java.additional.11=-Xms512M
[root@mysql ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 mysql
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 mysql
Mycat的启动和登入
启动Mycat
[root@mysql ~]# /usr/local/mycat/bin/mycat start
[root@mysql ~]# /usr/local/mycat/bin/mycat status
Mycat-server is running (3731).
[root@mysql ~]# netstat -tpln | grep 8066 --成功启动后通过mysql客户端工具输入root密码123456就可以登入到mycat了
tcp 0 0 :::8066 :::* LISTEN 3733/java
登入mycat中间件
(1)客户端工具sqllog进行登入
(2)linux上MySQL客户端工具进行登入
[root@slave2 ~]# mysql -uroot -p123456 -h 192.168.179.69 -P8066 -DTESTDB
mysql> show databases;
+----------+
| TESTDB |
+----------+
server.xml
server.xml: mycat的系统信息等。
schema.xml
schema.xml: 定义逻辑表,表分片节点信息等。
rule.xml详解
自己的测试,读者无需看这部分!!!!
[root@mysql conf]# vim server.xml
[root@mysql conf]# vim schema.xml
针对schema.xml该条做的测试
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataHost name="localhost1" 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.179.66:3306" user="mycat_test"
password="mycat_test">
<readHost host="hostS2" url="192.168.179.66:3306" user="mycat_test" password="mycat_test" />
</writeHost>
</dataHost>
192.168.179.66上MySQL创建用户并且授予权限,这样就可以登入Mycat中间件了
mysql> create user 'mycat_test'@'%' identified by 'mycat_test';
mysql> grant all on *.* to 'mycat_test'@'%';
192.168.179.66上MySQL创建三个数据库
mysql> create database db1;
mysql> create database db2;
mysql> create database db3;
[root@slave1 ~]# mysql -uroot -p123456 -h 192.168.179.69 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| MYTEST |
+----------+
mysql> use MYTEST;
mysql> show tables;
+------------------+
| Tables in MYTEST |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
mysql> create table employee (id int primary key,name varchar(10),sharding_id int);
mysql> explain create table employee (id int primary key,name varchar(10),sharding_id int); --通过explian可以看到,表创建在dn1和dn2节点
+-----------+-----------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------+
| dn1 | create table employee (id int primary key,name varchar(10),sharding_id int) |
| dn2 | create table employee (id int primary key,name varchar(10),sharding_id int) |
+-----------+-----------------------------------------------------------------------------+
192.168.179.66上MySQL上可以看到创建的表进入了两个库db1 db2,可以看到mycat自动为我分配表到指定数据库中,不需要我手动指定
mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| employee |
+---------------+
mysql> use db2;
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| employee |
+---------------+
数据的分片
使用mycat中间件插入数据
mysql> insert into employee (id,name,sharding_id) values(1,'hellow','10000');
mysql> explain insert into employee(id,name,sharding_id) values(1,'hellow','10000');
+-----------+----------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------+
| dn1 | INSERT INTO employee (id, name, sharding_id) VALUES (1, 'hellow', '10000') |
+-----------+----------------------------------------------------------------------------+
mysql> insert into employee(id,name,sharding_id) values (1,'aaa',10010);
mysql> explain insert into employee(id,name,sharding_id) values (1,'aaa',10010);
+-----------+-------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------+
| dn2 | INSERT INTO employee (id, name, sharding_id) VALUES (1, 'aaa', '10010') |
+-----------+-------------------------------------------------------------------------+
为什么要有sharding_id,为什么插入不同的值插入的数据库是不同的一个为dn1,一个为dn2
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
rule定义了表的分片规则,将数据分片到不同的节点上
[root@mysql conf]# vim rule.xml
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns> --可以看到该列名称为sharding_id
<algorithm>hash-int</algorithm> --拆分的策略
</rule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
[root@mysql conf]# ls partition-hash-int.txt
partition-hash-int.txt
[root@mysql conf]# cat partition-hash-int.txt
10000=0
10010=1
针对schema.xml该条做的测试
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3"
通过Mycat来创建company这张表
mysql> use MYTEST;
mysql> create table company(id int primary key,name varchar(10));
mysql> explain create table company(id int primary key,name varchar(10));;
+-----------+-----------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------+
| dn1 | create table company(id int primary key,name varchar(10)) |
| dn2 | create table company(id int primary key,name varchar(10)) |
| dn3 | create table company(id int primary key,name varchar(10)) |
+-----------+-----------------------------------------------------------+
mysql> insert into company values(1,'lulei');
mysql> explain insert into company values(1,'lulei');
+-----------+---------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------+
| dn1 | insert into company values(1,'lulei') |
| dn2 | insert into company values(1,'lulei') |
| dn3 | insert into company values(1,'lulei') |
+-----------+---------------------------------------+
mysql> explain select * from company;
+-----------+---------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------+
| dn1 | SELECT * FROM company LIMIT 100 |