Mycat+MySQL实现分表分库实例

野性酷女 2023-05-29 14:28 28阅读 0赞

关于分库分表，Mycat已经帮我们在内部实现了路由的功能，我们只需要在Mycat中配置以下切分规则即可，对于开发者来说，我们就可以把Mycat看做是一个数据库，接下来我们开始搭建环境

#### 1、准备： ####

Mycat是使用java写的数据库中间件，所以要运行Mycat前要准备要jdk的环境，要求是jdk1.7以上的环境。所以需要在系统中配置JAVA\_HOME的环境变量，如果没有配置好启动Mycat会是这样的提示：

[root@LinuxCentOS6 bin]# ./mycat start
    
    JAVA_HOME environment variable is not set
    
    [root@LinuxCentOS6 bin]#

正确安装JDK：[【运维笔记】Linux 安装 jdk1.8.0\_191 指南][Linux _ jdk1.8.0_191]

#### 2、下载MyCat安装包： ####

从官网下载Mycat，[http://dl.mycat.io/1.6-RELEASE/][http_dl.mycat.io_1.6-RELEASE] 我们是基于`CentOS6.5`来搭建Mycat环境的，所以下载版本：  
`Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz`

#### 3、将下载好的安装包上传到服务器上并解压.解压之后目录结构如下： ####

[root@LinuxCentOS6 mycat]# pwd
    /usr/local/mycat
    [root@LinuxCentOS6 mycat]# ll
    total 24
    drwxr-xr-x. 2 root root 4096 Oct 30 12:04 bin
    drwxrwxrwx. 2 root root 4096 Mar  1  2016 catlet
    drwxrwxrwx. 4 root root 4096 Oct 30 13:55 conf
    drwxr-xr-x. 2 root root 4096 Oct 30 12:04 lib
    drwxrwxrwx. 3 root root 4096 Oct 30 13:55 logs
    -rwxrwxrwx. 1 root root  217 Oct 28  2016 version.txt
    [root@LinuxCentOS6 mycat]#

#### 4、配置名为`db_sszh_mycat`的`逻辑库`信息，将如下配置复制粘贴覆盖`mycat/conf/schema.xml`的内容： ####

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	  
        <schema name="db_sszh_mycat" checkSQLschema="false" sqlMaxLimit="100">
    		<table name="user" primaryKey="id" dataNode="db_sszh_01,db_sszh_02" rule="rule1" />  
        </schema>
        
          
        <dataNode name="db_sszh_01" dataHost="db_sszh" database="db_sszh_01" />  
        <dataNode name="db_sszh_02" dataHost="db_sszh" database="db_sszh_02" />   
        
          
        <dataHost name="db_sszh" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">  
    		<heartbeat>select user()</heartbeat>  
    		<writeHost host="server1" url="192.168.10.88:3306" user="root" password="123456"/>  
        </dataHost> 
    </mycat:schema>

说明：

*  `<schema>`:表示的是在mycat中的逻辑库配置，逻辑库名称为：`db_sszh_mycat`
 *  `<table>`:表示在mycat中的逻辑表配置，逻辑表名称为：`user`,映射到两个数据库节点`dataNode`中,切分规则为:`rule1`(在rule.xml配置)
 *  `<dataNode>`:表示数据库节点,这个节点不一定是单节点，可以配置成读写分离
 *  `<dataHost>`:真实的数据库的地址配置
 *  `<heartbeat>`:用户心跳检测
 *  `<writeHost>`:写库的配置

#### 5、配置切分规则，将如下配置复制粘贴覆盖`mycat/conf/rule.xml`的内容： ####

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:rule SYSTEM "rule.dtd">
    <mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="rule1">
            <rule>
                <columns>id</columns>
                <algorithm>mod-long</algorithm>
            </rule>
        </tableRule>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
            
            <property name="count">2</property>
        </function>
    </mycat:rule>

说明：

*  这里定义的是切分规则，是按照id列进行切分，切分规则是采取取模的方式，`<property name="count">2</property>`:这里配置了我们有拆分了多个库(表)，需要和前面配置`<table name="user" primaryKey="id" dataNode="db_sszh_01,db_sszh_02" rule="rule1" />`中的dataNode个数一致，否则会出错

#### 6、配置mycat服务器信息，将如下配置复制粘贴覆盖`mycat/conf/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>  
    	<property name="useGlobleTableCheck">0</property>  
    		<property name="sequnceHandlerType">2</property>
    		 
            <property name="fakeMySQLVersion">5.6.20</property> 
    		
    		
    		
    		<property name="processorBufferPoolType">0</property>
    		
    		<property name="maxStringLiteralLength">65535</property>
    		<property name="sequnceHandlerType">0</property>
    		<property name="backSocketNoDelay">1</property>
    		<property name="frontSocketNoDelay">1</property>
    		<property name="processorExecutor">16</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">db_sszh_mycat</property>
    		
    		<property name="readOnly">false</property>
    		
    		
    	</user>
    
    	<user name="user">
    		<property name="password">123456</property>
    		<property name="schemas">db_sszh_mycat</property>
    		<property name="readOnly">true</property>
    	</user>
    
    </mycat:server>

#### 7、新建数据库：`在数据库中创建两个数据库 db_sszh_01, db_sszh_01` ####

建库语句，略...

#### 8、分别在每个库中执行如下建表语句： ####

CREATE TABLE `user` (
      `id` bigint(20) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### 9、启动MyCat，进入`/usr/local/mycat/bin`目录，执行命令`./mycat start`： ####

[root@LinuxCentOS6 bin]# pwd
    /usr/local/mycat/bin
    [root@LinuxCentOS6 bin]# ./mycat start
    Starting Mycat-server...
    Mycat-server is already running.
    [root@LinuxCentOS6 bin]#

#### 10、查看MyCat启动日志（位于`/usr/local/mycat/logs/wrapper.log`），启动成功会有如下显示，默认端口`8066`： ####

STATUS | wrapper  | 2019/10/30 15:08:37 | --> Wrapper Started as Daemon
    STATUS | wrapper  | 2019/10/30 15:08:37 | Launching a JVM...
    INFO   | jvm 1    | 2019/10/30 15:08:37 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
    INFO   | jvm 1    | 2019/10/30 15:08:38 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
    INFO   | jvm 1    | 2019/10/30 15:08:38 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
    INFO   | jvm 1    | 2019/10/30 15:08:38 | 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,098 [INFO ][WrapperSimpleAppMain] total resouces of dataHost db_sszh is :1  (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:100) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,112 [INFO ][WrapperSimpleAppMain] create layer cache pool TableID2DataNodeCache of type encache ,default cache size 10000 ,default expire seconds18000  (io.mycat.cache.CacheService:CacheService.java:125) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,123 [INFO ][WrapperSimpleAppMain] create child Cache: TESTDB_ORDERS for layered cache TableID2DataNodeCache, size 50000, expire seconds 18000  (io.mycat.cache.DefaultLayedCachePool:DefaultLayedCachePool.java:80) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,699 [INFO ][WrapperSimpleAppMain] dyna class load from ./catlet,and auto check for class file modified every 60 seconds  (io.mycat.config.classloader.DynaClassLoader:DynaClassLoader.java:34) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,699 [INFO ][WrapperSimpleAppMain] ===============================================  (io.mycat.MycatServer:MycatServer.java:266) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,699 [INFO ][WrapperSimpleAppMain] MyCat is ready to startup ...  (io.mycat.MycatServer:MycatServer.java:267) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,699 [INFO ][WrapperSimpleAppMain] Startup processors ...,total processors:32,aio thread pool size:16    
    INFO   | jvm 1    | 2019/10/30 15:08:39 |  each process allocated socket buffer pool  bytes ,a page size:2097152  a page's chunk number(PageSize/ChunkSize) is:512  buffer page's number is:40  (io.mycat.MycatServer:MycatServer.java:279) 
    INFO   | jvm 1    | 2019/10/30 15:08:39 | 2019-10-30 15:08:39,699 [INFO ][WrapperSimpleAppMain] sysconfig params:SystemConfig [processorBufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304, backSocketSoRcvbuf=4194304, backSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1, maxStringLiteralLength=65535, frontWriteQueueSize=4096, bindIp=0.0.0.0, serverPort=8066, managerPort=9066, charset=utf8, processors=32, processorExecutor=16, timerExecutor=2, managerExecutor=2, idleTimeout=300000, catletClassCheckSeconds=60, sqlExecuteTimeout=300, processorCheckPeriod=1000, dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=_HEARTBEAT_USER_, clusterHeartbeatPass=_HEARTBEAT_PASS_, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000, clusterHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10, bufferPoolPageSize=2097152, bufferPoolChunkSize=4096, bufferPoolPageNumber=40, maxResultSet=524288, bigResultSizeSqlCount=10, bufferUsagePercent=80, flowControlRejectStrategy=0, clearBigSqLResultSetMapMs=600000, defaultMaxLimit=100, sequnceHandlerType=0, sqlInterceptor=io.mycat.server.interceptor.impl.DefaultSqlInterceptor, sqlInterceptorType=select, sqlInterceptorFile=/usr/local/mycat/logs/sql.txt, mutiNodeLimitType=0, mutiNodePatchSize=100, defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216, mycatNodeId=1]  (io.mycat.MycatServer:MycatServer.java:280) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,243 [INFO ][WrapperSimpleAppMain] useOffHeapForMerge = 1  (io.mycat.memory.MyCatMemory:MyCatMemory.java:53) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,243 [INFO ][WrapperSimpleAppMain] memoryPageSize = 1m  (io.mycat.memory.MyCatMemory:MyCatMemory.java:54) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,243 [INFO ][WrapperSimpleAppMain] spillsFileBufferSize = 1k  (io.mycat.memory.MyCatMemory:MyCatMemory.java:55) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,243 [INFO ][WrapperSimpleAppMain] useStreamOutput = 0  (io.mycat.memory.MyCatMemory:MyCatMemory.java:56) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,243 [INFO ][WrapperSimpleAppMain] systemReserveMemorySize = 384m  (io.mycat.memory.MyCatMemory:MyCatMemory.java:57) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,249 [INFO ][WrapperSimpleAppMain] totalNetWorkBufferSize = 80MB  (io.mycat.memory.MyCatMemory:MyCatMemory.java:58) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,250 [INFO ][WrapperSimpleAppMain] dataNodeSortedTempDir = /usr/local/mycat  (io.mycat.memory.MyCatMemory:MyCatMemory.java:59) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,251 [INFO ][WrapperSimpleAppMain] mycat.memory.offHeap.size: 1321MB  (io.mycat.memory.MyCatMemory:MyCatMemory.java:122) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,254 [INFO ][WrapperSimpleAppMain] using nio network handler   (io.mycat.MycatServer:MycatServer.java:381) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,273 [INFO ][WrapperSimpleAppMain] $_MyCatManager is started and listening on 9066  (io.mycat.MycatServer:MycatServer.java:397) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,273 [INFO ][WrapperSimpleAppMain] $_MyCatServer is started and listening on 8066  (io.mycat.MycatServer:MycatServer.java:401) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,274 [INFO ][WrapperSimpleAppMain] ===============================================  (io.mycat.MycatServer:MycatServer.java:403) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,274 [INFO ][WrapperSimpleAppMain] Initialize dataHost ...  (io.mycat.MycatServer:MycatServer.java:407) 
    INFO   | jvm 1    | 2019/10/30 15:08:40 | 2019-10-30 15:08:40,274 [INFO ][WrapperSimpleAppMain] init backend myqsl source ,create connections total 10 for server1 index :0  (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:294)

#### 11、启动失败中的一种错误提示： ####

INFO   | jvm 4    | 2019/10/30 13:51:15 | sun.management.AgentConfigurationError:
     java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: 
     LinuxCentOS6: LinuxCentOS6: Name or service not known

解决办法：`修改hosts文件，绑定主机名`，我的主机名为`LinuxCentOS6`（这里要设置成自己的主机名）：

[root@LinuxCentOS6 bin]# vim /etc/hosts
    ~
    127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
    ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
    192.168.10.88 LinuxCentOS6 localhost localhost.localdomain localhost6 localhost6.localdomain6
    ~
    ~
    ~
    :wq
    [root@LinuxCentOS6 bin]#

重新启动mycat服务，命令：`./mycat start`  
查看MyCat进程，命令：`ps -ef |grep mycat`

#### 12、测试连接MyCat： ####

linux连接MyCat操作，命令格式：`mysql -u用户名 -p密码 -h主机地址 -P端口号 -D数据库名称`：

[root@LinuxCentOS6 logs]# mysql -uroot -p123456 -h192.168.10.88 -P8066 -Ddb_sszh_mycat
    
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.6.20-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [db_sszh_mycat]> select * from user;   
    +----+---------+
    | id | name    |
    +----+---------+
    |  2 | 张三 |
    |  4 | 李四 |
    |  6 | 王五 |
    |  8 | 赵六 |
    |  1 | A米  |
    |  3 | T利  |
    |  5 | 凯瑞 |
    |  7 | 芥末 |
    +----+---------+
    8 rows in set (0.002 sec)

JDBC连接MyCat，连接字符串：`jdbc:mysql://192.168.10.88:8066/db_sszh_mycat?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false`

Navicat连接工具连接MyCat，如图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70]  
主机`192.168.10.88:3306`，Mysql物理数据库`db_sszh_01`数据库数据：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70 1]  
主机`192.168.10.88:3306`，Mysql物理数据库`db_sszh_02`数据库数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70 2]  
主机`192.168.10.88:8066`，MyCat逻辑库`db_sszh_mycat`数据库数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70 3]

注：以上内容仅提供参考和交流，请勿用于商业用途，如有侵权联系本人删除！

--------------------

## 持续更新中… ##

如有对思路不清晰或有更好的解决思路，欢迎与本人交流，QQ群：273557553  
你遇到的问题是小编创作灵感的来源！

--------------------

[Linux _ jdk1.8.0_191]: https://blog.csdn.net/seesun2012/article/details/84638204
[http_dl.mycat.io_1.6-RELEASE]: http://dl.mycat.io/1.6-RELEASE/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191106202753337.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019110620332592.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019110620342048.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191106202924190.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NlZXN1bjIwMTI=,size_16,color_FFFFFF,t_70