mycat下载安装

1. mycat下载地址:


2. 安装jdk (1.7 以上版本)

wget --no-check-certificate --no-cookies --header "Cookie: oraclelicense=accept-securebackup-cookie" http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm

3.解压mycat 放到 /usr/local目录下 
[root@mycat01 mycat]# ll
总用量 12
drwxr-xr-x. 2 mycat mycat  190 11月 29 17:37 bin
drwxrwxrwx. 2 mycat mycat    6 3月  1 2016 catlet
drwxrwxrwx. 4 mycat mycat 4096 11月 29 17:37 conf
drwxr-xr-x. 2 mycat mycat 4096 11月 29 17:37 lib
drwxrwxrwx. 2 mycat mycat    6 10月 28 2016 logs
-rwxrwxrwx. 1 mycat mycat  217 10月 28 2016 version.txt

4.创建用户,赋权限
useradd  mycat
chown -R mycat:mycat /usr/local/mycat

5.编辑配置文件
vim /usr/local/mycat/conf/wrapper.conf 
wrapper.java.command=/usr/local/jdk1.7/bin/java

# Java Additional Parameters
#wrapper.java.additional.1=
wrapper.java.additional.1=-DMYCAT_HOME=.
wrapper.java.additional.2=-server
wrapper.java.additional.3=-XX:MaxPermSize=1024M
wrapper.java.additional.4=-XX:+AggressiveOpts
wrapper.java.additional.5=-XX:MaxDirectMemorySize=2048M
wrapper.java.additional.6=-Dcom.sun.management.jmxremote
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
wrapper.java.additional.10=-Xmx4G
wrapper.java.additional.11=-Xms1G

# Initial Java Heap Size (in MB)
wrapper.java.initmemory=3

# Maximum Java Heap Size (in MB)
wrapper.java.maxmemory=512

5.创建连接
ln -s /usr/local/mycat/bin/mycat /usr/bin

6.启动mycat
[root@mycat01 /]# mycat start
Starting Mycat-server...
[root@mycat01 /]# 

[root@mycat01 /]# mycat --help
Usage: /usr/bin/mycat { console | start | stop | restart | status | dump }

7.查看进程
[root@mycat01 /]# ps -ef|grep mycat
root      19458      1  0 17:51 ?        00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat

8.端口开启(同时打开防火墙)

9.查看端口
[root@mycat01 /]# netstat -ant|grep 8066
tcp6      0      0 :::8066                :::*                    LISTEN 

10. 查看日志:
[root@mycat01 logs]# tail -f wrapper.log 


二、分库分表


环境:3个节点  56.56.56.201/204/206 

MySQL:申请3个节点配置相同 ,每个节点创建一个数据库,名称相同都为 dbproxy,接着每个库创建一个相同表sbtest1 
create table sbtest1 (
  id int(10) unsigned not null,
  k int(10) unsigned not null default '0',
  c char(120) not null default '',
  pad char(60) not null default '',
  primary key (id),
  key k_1 (k)
) engine=innodb default charset=utf8

配置

1. 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="sequnceHandlerType">2</property>
             <property name="processors">1</property>
             <property name="processorExecutor">32</property>
             <property name="processorBufferPoolType">0</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="frontWriteQueueSize">4096</property> 
             <property name="processors">32</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>
     </system>

     <user name="root">
           <property name="password">123456</property>
           <property name="schemas">testdb</property>    
     </user>
     <user name="user">
           <property name="password">123456</property>
           <property name="schemas">testdb</property>
           <property name="readOnly">true</property>
     </user>
</mycat:server>

  Mycat会对数据库访问资源进行管理,有2种类型账号,分为管理员、普通用户;管理员负责中间件各种资源管理(后端数据库访问)、查看、分配等,普通用户只能对数据库进行DDL和DML操作。每个用户配置了访问具体数据库资源,由schemas参数决定,未配置则无法访问。


2. 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">
                <!-- auto sharding by id (long) -->
                <table name="sbtest1" primaryKey="id" dataNode="dn01,dn02,dn03" rule="mod-long" />
        </schema>

        <dataNode name="dn01" dataHost="node1" database="dbproxy" />
        <dataNode name="dn02" dataHost="node2" database="dbproxy" />
        <dataNode name="dn03" dataHost="node3" database="dbproxy" />


        <dataHost name="node1" maxCon="2000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="56.56.56.201:3306" user="my" password="123456"></writeHost>
        </dataHost>

        <dataHost name="node2" maxCon="2000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="56.56.56.204:3306" user="my" password="123456"></writeHost>
        </dataHost>

        <dataHost name="node3" maxCon="2000" minCon="10" balance="0"  
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM3" url="56.56.56.206:3306" user="my" password="123456"></writeHost>
        </dataHost>

</mycat:schema>

  以上配置了4个逻辑库,只有主库没配从库,如有需要可以加上,一个表sbtest1数据切分到3个逻辑库上。其中’rule=”mod-long”‘ 表示选择取模算法,对应下面的配置
注意:这里需要在各个节点创建mysql的账号密码。(成功后记得看看远程能不能登录成功,我就掉到这个坑了)

3. rule.xml分片规则配置
  <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">3</property>
   </function>

表示 id % 3 = 路由到对应逻辑库上(dn01|dn02|dn03)


4.启动mycat
mycat start

5.连接mycat 
[root@mycat_test conf]# mysql -uroot -p -P8066 -h127.0.0.1
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> 

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| testdb  |
+----------+
1 row in set (0.00 sec)

6.测试在 mycat端插入10条数据
MySQL [testdb]> select * from sbtest1;
+----+-----+-----+-----+
| id | k  | c  | pad |
+----+-----+-----+-----+
|  1 | 100 | aaa | ccc |
|  4 | 400 | ddd | ccc |
|  7 | 500 | eee | ccc |
| 10 | 500 | eee | ccc |
|  3 | 300 | ccc | ccc |
|  6 | 500 | eee | ccc |
|  9 | 500 | eee | ccc |
|  2 | 200 | bbb | ccc |
|  5 | 500 | eee | ccc |
|  8 | 500 | eee | ccc |
+----+-----+-----+-----+
10 rows in set (0.00 sec)

7. 看看各个节点的数据

201节点
root@localhost :dbproxy02:39:22>select * from sbtest1;
+----+-----+-----+-----+
| id | k  | c  | pad |
+----+-----+-----+-----+
|  | 300 | ccc | ccc |
 6 | 500 | eee | ccc |
 9 | 500 | eee | ccc |
+----+-----+-----+-----+
3 rows in set (0.00 sec)

204节点
root@localhost :dbproxy12:21:07>select * from sbtest1;
+----+-----+-----+-----+
| id | k  | c  | pad |
+----+-----+-----+-----+
|  1 | 100 | aaa | ccc |
|  | 400 | ddd | ccc |
|  7 | 500 | eee | ccc |
10 | 500 | eee | ccc |
+----+-----+-----+-----+
4 rows in set (0.00 sec)


296节点数据
root@localhost :dbproxy12:21:47>select * from sbtest1;
+----+-----+-----+-----+
| id | k  | c  | pad |
+----+-----+-----+-----+
|  | 200 | bbb | ccc |
|  5 | 500 | eee | ccc |
|  8 | 500 | eee | ccc |
+----+-----+-----+-----+
3 rows in set (0.00 sec)

* 可以看到数据分散到各个节点啦

缺点:扩展的数据节点,数据迁移麻烦。

坑:查询不要带着数据库名字。Mycat拦截了SQL信息做了过滤,在转换的时候找不到目标路由。

MySQL [testdb]> select * from t1 where id =1;
+----+-----+-----+-----+
| id | k   | c   | pad |
+----+-----+-----+-----+
|  1 | 100 | aaa | aaa |
+----+-----+-----+-----+
1 row in set (0.03 sec)

MySQL [testdb]> select * from db2.t1 where id =1;
ERROR 1064 (HY000):  find no Route:select * from db2.t1 where id =1

问题2:

在配置了sharding策略之后,insert语句抛出了下面的错误,必须把字段带上
MySQL [testdb]> insert into t1 values(7,100,'aaa','aaa');
ERROR 1064 (HY000): partition table, insert must provide ColumnList

MySQL [testdb]> insert into t1(id,k,c,pad) values(5,100,'aaa','aaa');

分片规则:
(2)主键范围:auto-sharding-long
(3)一致性hash:sharding-by-murmur
(4)字符串hash解析:sharding-by-stringhash
(5)按日期(天)分片:sharding-by-date
(6)按单月小时拆分:sharding-by-hour
(7)自然月分片:sharding-by-month

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zhengwei125/article/details/79655512
个人分类: mycat
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭