Databases / 运维笔记

MYCAT 安装部署

Einic Yeo · 6月18日 · 2019年 ·

一、文档介绍

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

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

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

一个能平滑扩展支持大表的版权声明：本文遵循 CC 4.0 BY-SA 版权协议，若要转载请务必附上原文出处链接及本声明，谢谢合作！分布式数据库系统

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

二版权声明：本文遵循 CC 4.0 BY-SA 版权协议，若要转载请务必附上原文出处链接及本声明，谢谢合作！、实验环境


192.168.8.87  mysql + mycat

192.168.8.86  mysql

三、环境搭建 版权声明：本文遵循 CC 4.0 BY-SA 版权协议，若要转载请务必附上原文出处链接及本声明，谢谢合作！

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]#

四、分库分表测试

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,可以看到连接的是my版权声明：本文遵循 CC 4.0 BY-SA 版权协议，若要转载请务必附上原文出处链接及本声明，谢谢合作！cat 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>

以上操作创建了两张表,每张表插入了两版权声明：本文遵循 CC 4.0 BY-SA 版权协议，若要转载请务必附上原文出处链接及本声明，谢谢合作！条数据,待会验证是不是分到不同的数据库表上去了.

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上每个表上也只有一条数据

五、测试结果

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

相关文章

LVM 浅析

7月10日 · 2019年

MHA 安装部署

3月20日 · 2019年

30分钟学会SQL语句

8月8日 · 2018年

Mysql 高可用方案

6月26日 · 2018年
0 条回应
© 2017~ Infvie Envoy. All Rights Reserved.
IPV6 | GITHUB