mycat单库分表

女爷i 2024-04-19 08:31 58阅读 0赞

单库分表功能要在mycat1.6版本以上，同库分表：所有的分表都在一个数据库中，由于数据库中表名不能重复，因此需要把数据表名起成不同的名字。将t\_source 表按照月份分成12张表

![20190916102249665.png][]

1、server.xml配置,添加mycat登陆账户，mycat端口8066

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:server SYSTEM "server.dtd">
    <mycat:server xmlns:mycat="http://io.mycat/">
    	<system>
         
    	<property name="nonePasswordLogin">0</property> 
    	<property name="useHandshakeV10">1</property>
        
    	<property name="useSqlStat">0</property>  
         
    	<property name="useGlobleTableCheck">0</property> 
        <property name="sequnceHandlerType">2</property>
       
    	<property name="subqueryRelationshipCheck">false</property> 
        <property name="processorBufferPoolType">0</property>
    		
        <property name="handleDistributedTransactions">0</property>
    		<property name="useOffHeapForMerge">1</property>
            <property name="memoryPageSize">64k</property>
    		<property name="spillsFileBufferSize">1k</property>
    		<property name="useStreamOutput">0</property>
    		<property name="systemReserveMemorySize">384m</property>
    		
    		<property name="useZKSwitch">false</property>
    		<property name="strictTxIsolation">false</property>		
    		<property name="useZKSwitch">true</property>	
    	</system>
    	
        
    	<user name="root">
    		<property name="password">123456</property>
    		<property name="schemas">split_db</property>
    	</user>
    
    </mycat:server>

2、schema.xml配置，配置t\_source的分片，物理数据库关联

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	
    	      
        <schema  name="split_db" checkSQLschema="false" sqlMaxLimit="100">
    	     
              
    		<table  name="t_source" primaryKey="ID" subTables="$t_source$1-12"  dataNode="dn1"   rule="sharding-by-month"></table>
    		
    	</schema>
    	
    	                                                                                   
        <dataNode name="dn1" dataHost="localhost1" database="split_db" /> 
    
        <dataHost name="localhost1" 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.1.164:3306" user="root" password="root">    
                  <readHost host="hostS1" url="192.168.1.164:3306" user="root" password="root" /> 		              
            </writeHost>                                                                                             
        </dataHost>          
    
    </mycat:schema>

3、rule.xml配置分表规则

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:rule SYSTEM "rule.dtd">
    <mycat:rule xmlns:mycat="http://io.mycat/">
    
    	<tableRule name="mod-long">
    		<rule>
    			<columns>id</columns>
    			<algorithm>mod-long</algorithm>
    		</rule>
    	</tableRule>
    	
    	
    	<tableRule name="sharding-by-month">
    		<rule>
    			<columns>apply_date</columns>
    			<algorithm>partbymonth</algorithm>
    		</rule>
    	</tableRule>
      
    	<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    		
    		<property name="count">3</property>
    	</function>
    	
    	<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
    		<property name="dateFormat">yyyy-MM-dd</property>
    		<property name="sBeginDate">2018-08-01</property>
    	</function>
    	
    </mycat:rule>

4、连接mycat

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70][]

[20190916102249665.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/351aad781b0249aa9c993161890f9333.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlbmdjaGVuZ3d1MjAxMg_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/3fc0205c2cff4dee96db546eaa221a6c.png