MYCAT安装部署文档

1、文档介绍

    本文档目的在于介绍mycat的安装部署, 以及给出一两个mycat水平切分的例子。

1.1 MYCAT简介

一个用于MySQL读写分离和与数据切分的高可用中间件

一个模拟为MySQLServer的超级数据库代理

一个能平滑扩展支持大表的分布式数据库系统

一个可管控多种关系数据库的数据库路由器

 

2、实验环境

192.168.8.87  mysql + mycat

192.168.8.86  mysql

 

3、环境搭建

3.1 mysql安装略

3.2 mycat安装

1) 上传mycat安装文件

[iotmp@host87 mycat]$ ll
total 15296
-rw-r--r-- 1 iotmp iotmp 15662280 Mar 18 11:02 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[iotmp@host87 mycat]$

2) 解压安装文件

[iotmp@host87 mycat]$ tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[iotmp@host87 mycat]$ cd mycat/

[iotmp@host87 mycat]$ cd conf/

[iotmp@host87 conf]$

3) 修改配置文件server.xml

[iotmp@host87 conf]$ cat server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
        <property name="charset">utf8</property>

        <property name="sequnceHandlerType">2</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>

        <property name="useOffHeapForMerge">1</property>

        <property name="memoryPageSize">1m</property>

        <property name="spillsFileBufferSize">1k</property>

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

        <property name="systemReserveMemorySize">384m</property>
        <property name="useZKSwitch">true</property>

        <property name="defaultSqlParser">druidparser</property> 
        <property name="mutiNodeLimitType">1</property> 
        <property name="serverPort">8066</property> 
        <property name="managerPort">9066</property>  
        <property name="bindIp">0.0.0.0</property>
        </system>
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">mydb1,mydb2</property>
        </user>

</mycat:server>
[iotmp@host87 conf]$

4) 修改配置文件schema.xml

[root@host87 conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="mydb1" checkSQLschema="false" sqlMaxLimit="100">
                <table name="t1" primaryKey="ID" dataNode="dn1,dn3" rule="mod-long" />
        </schema>

        <schema name="mydb2" checkSQLschema="false" sqlMaxLimit="100">
                <table name="t2" primaryKey="ID" dataNode="dn2,dn4" rule="auto-sharding-long" />
        </schema>

        <dataNode name="dn1" dataHost="dh86" database="testdb1" />
        <dataNode name="dn2" dataHost="dh86" database="testdb2" />
        <dataNode name="dn3" dataHost="dh87" database="testdb1" />
        <dataNode name="dn4" dataHost="dh87" database="testdb2" />

        <dataHost name="dh86" 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.8.86:3306" user="root"
                                   password="123456">
                </writeHost>

        </dataHost>


        <dataHost name="dh87" 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.8.87:3306" user="root"
                                   password="123456">
                </writeHost>

        </dataHost>

</mycat:schema>
[root@host87 conf]#
[iotmp@host87 conf]$

5) 配置文件rule.xml

[root@host87 conf]# cat rule.xml
        <tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>
        </tableRule>
     <tableRule name="mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>


        <function name="rang-long"
                class="io.mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
        </function>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>


[root@host87 conf]# cat autopartition-long.txt 
# range start-end ,data node index
# K=1000,M=10000.
0-100000=0
100001-200000=1
[root@host87 conf]#

 

4、分库分表测试

1)创建测试库和表, 在两节点均执行

mysql> create database testdb1 default charset "utf8";
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> create database testdb2 default charset "utf8";
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use testdb1
Database changed
mysql> 
mysql> create table t1(id bigint);
Query OK, 0 rows affected (0.13 sec)
mysql> 
mysql> use testdb2
Database changed
mysql> 
mysql> create table t2(id bigint);
Query OK, 0 rows affected (0.08 sec)
mysql>

2) 启动mycat

[root@host87 conf]# /home/iotmp/mycat/mycat/bin/mycat start
Starting Mycat-server...
[root@host87 conf]# 
[root@host87 conf]# sudo netstat -nltp|grep -E "8066|9066"
tcp6       0      0 :::8066                 :::*                    LISTEN      18759/java          
tcp6       0      0 :::9066                 :::*                    LISTEN      18759/java          
[root@host87 conf]#

3) 使用客户端连接mycat, 默认端口8066,可以看到连接的是mycat server.

[root@host87 conf]# mysql -uroot -p123456 -P8066 -h192.168.8.87
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-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

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.

4) 查看实例

mysql> show databases;
+----------+
| DATABASE |
+----------+
| mydb1    |
| mydb2    |
+----------+
2 rows in set (0.00 sec)

5)插入数据

mysql>
mysql> use mydb1
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> 
mysql> show tables;
+-----------------+
| Tables in mydb1 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql> insert into t1(id)  values(1);
Query OK, 1 row affected (0.05 sec)

mysql> 
mysql> insert into t1(id)  values(2);
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.04 sec)

mysql>

mysql> use mydb2
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> 
mysql> insert into t2(id)  values(100);
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> insert into t2(id)  values(100001);
Query OK, 1 row affected (0.04 sec)

mysql> 
mysql> select * from t2;
+--------+
| id     |
+--------+
| 100001 |
|    100 |
+--------+
2 rows in set (0.00 sec)

mysql>

以上操作创建了两张表,每张表插入了两条数据,待会验证是不是分到不同的数据库表上去了.

6) 登陆到实际的后端db,验证表数据

[root@host87 conf]# mysql -uroot -p123456 -P3306 -h192.168.8.87
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 2445
Server version: 5.6.41-log MySQL Community Server (GPL)

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>
mysql> use testdb1;
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> 
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> use testdb2
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> 
mysql> select * from t2;
+--------+
| id     |
+--------+
| 100001 |
+--------+
1 row in set (0.00 sec)

mysql>

87上每个表只有一条数据,再看86的

[root@host87 conf]# mysql -uroot -p123456 -P3306 -h192.168.8.86
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 410955
Server version: 5.6.41-log MySQL Community Server (GPL)

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> use testdb1;
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> 
mysql> select * from t1;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

mysql> use testdb2
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> 
mysql> select * from t2;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql>

86上每个表上也只有一条数据

 

5、测试结果

在本次实验中,可以看到,使用mycat插入的数据,按照分片规则(rang-long和mod-long)分别插入到了相应的后端物理数据库中, 分库分表验证成功.