mycat1.6实现单库分表#

绝地灬酷狼 2021-09-26 09:14 260阅读 0赞

1，下载mycat1.6（[http://dl.mycat.io/1.6-RELEASE/][http_dl.mycat.io_1.6-RELEASE] “选择win版本 第6个文件就是”）  
2，把server.xml，rule.xml，schema.xml，wrapper.conf替换成如下配置  
3，在mysql创建 库名为 testdb2 然后运行 文章下方提供的sql  
4，重启mycat  
5，用Navicat登入mycat 执行3个INSERT `INSERT INTO t_order(id,name,t_user_id) VALUES(1,'dd1',1);` ，观察分表效果  
dome github [https://github.com/gyn781369549/MyCat1.6dome][https_github.com_gyn781369549_MyCat1.6dome]  
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="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">false</property>
    
        </system>
    
        
        
        
        <user name="root">
            
            <property name="password">root</property>
            <property name="schemas">TESTDB</property>
            
            <property name="readOnly">false</property>
            
    
        </user>
    
        <user name="user">
            <property name="password">user</property>
            <property name="schemas">TESTDB</property>
            <property name="readOnly">true</property>
    
    
        </user>
    
    </mycat:server>

mycat\\conf 下的rule.xml 分表规则 按id 平均分

mycat\\conf 下的schema.xml schema 是配置的mycat逻辑库 与数据库表名要对应

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
        
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" > 
            
            <table name="t_order" primaryKey="id" autoIncrement="true" subTables="t_order$1-3" dataNode="dn1" rule="mod-long" >
    
            </table>
            <table name="t_user" primaryKey="id" autoIncrement="true" dataNode="dn1" />    
    
    
    
        </schema> 
        
        <dataNode name="dn1" dataHost="localhost1" database="testdb2" /> 
        
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> 
                
                <heartbeat>select root()</heartbeat> 
                
                <writeHost host="hostM1" url="localhost:3306" user="root" password="pwd" /> 
        </dataHost>
    </mycat:schema>

mycat\\conf 下的 wrapper.conf wrapper.java.command=配置java

#********************************************************************
    # Wrapper Properties
    #********************************************************************
    # Java Application
    wrapper.java.command=D:\Java\jdk1.7.0_67\bin\java.exe
    wrapper.working.dir=..
    
    # Java Main class.  This class must implement the WrapperListener interface
    #  or guarantee that the WrapperManager class is initialized.  Helper
    #  classes are provided to do this for you. See the Integration section # of the documentation for details. wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperSimpleApp set.default.REPO_DIR=lib set.APP_BASE=. # Java Classpath (include wrapper.jar) Add class path elements as # needed starting from 1 wrapper.java.classpath.1=lib/wrapper.jar wrapper.java.classpath.2=conf wrapper.java.classpath.3=%REPO_DIR%/* # Java Library Path (location of Wrapper.DLL or libwrapper.so) wrapper.java.library.path.1=lib # Java Additional Parameters #wrapper.java.additional.1= wrapper.java.additional.1=-DMYCAT_HOME=. wrapper.java.additional.2=-server wrapper.java.additional.3=-XX:MaxPermSize=64M wrapper.java.additional.4=-XX:+AggressiveOpts wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G 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=64 # Application parameters. Add parameters as needed starting from 1 wrapper.app.parameter.1=io.mycat.MycatStartup wrapper.app.parameter.2=start #******************************************************************** # Wrapper Logging Properties #******************************************************************** # Format of output for the console. (See docs for formats) wrapper.console.format=PM # Log Level for console output. (See docs for log levels) wrapper.console.loglevel=INFO # Log file to use for wrapper output logging. wrapper.logfile=logs/wrapper.log # Format of output for the log file. (See docs for formats) wrapper.logfile.format=LPTM # Log Level for log file output. (See docs for log levels) wrapper.logfile.loglevel=INFO # Maximum size that the log file will be allowed to grow to before # the log is rolled. Size is specified in bytes. The default value # of 0, disables log rolling. May abbreviate with the 'k' (kb) or # 'm' (mb) suffix. For example: 10m = 10 megabytes. wrapper.logfile.maxsize=0 # Maximum number of rolled log files which will be allowed before old # files are deleted. The default value of 0 implies no limit. wrapper.logfile.maxfiles=0 # Log Level for sys/event log output. (See docs for log levels) wrapper.syslog.loglevel=NONE #******************************************************************** # Wrapper Windows Properties #******************************************************************** # Title to use when running as a console wrapper.console.title=Mycat-server #******************************************************************** # Wrapper Windows NT/2000/XP Service Properties #******************************************************************** # WARNING - Do not modify any of these properties when an application # using this configuration file has been installed as a service. # Please uninstall the service before modifying this section. The # service can then be reinstalled. # Name of the service wrapper.ntservice.name=mycat # Display name of the service wrapper.ntservice.displayname=Mycat-server # Description of the service wrapper.ntservice.description=The project of Mycat-server # Service dependencies. Add dependencies as needed starting from 1 wrapper.ntservice.dependency.1= # Mode in which the service is installed. AUTO_START or DEMAND_START wrapper.ntservice.starttype=AUTO_START # Allow the service to interact with the desktop. wrapper.ntservice.interactive=false wrapper.ping.timeout=120 configuration.directory.in.classpath.first=conf

新建数据名为 testdb2 ，这是库中所需表

DROP TABLE t_user;
    DROP TABLE t_order1;
    DROP TABLE t_order2;
    DROP TABLE t_order3;
    CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
    CREATE TABLE `t_order1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `t_user_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
    CREATE TABLE `t_order2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `t_user_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
    CREATE TABLE `t_order3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `t_user_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

由于1.6已经是release，现有的1.6分支当作1.6.5开发，导致无法修改，因此使用分表只能用替换class或者jar的方式：  
替换jar：lib目录下mycat-server：（基于1.6.5开发板）[http://songwie.com/attached/mycat/Mycat-server-1.6.5-DEV.jar][http_songwie.com_attached_mycat_Mycat-server-1.6.5-DEV.jar]

java程序连接mycat，与连接mysql相同，可以直接用Navicat直接连mycat与连接mysql一样，mycat默认端口8066

datasource.url=jdbc:mysql://localhost:8066/TESTDB?useUnicode=true&characterEncoding=utf-8

[http_dl.mycat.io_1.6-RELEASE]: http://dl.mycat.io/1.6-RELEASE/
[https_github.com_gyn781369549_MyCat1.6dome]: https://github.com/gyn781369549/MyCat1.6dome
[http_songwie.com_attached_mycat_Mycat-server-1.6.5-DEV.jar]: http://songwie.com/attached/mycat/Mycat-server-1.6.5-DEV.jar