mycat 读写分离, 写分片 (docker)

水深无声 2022-12-21 00:44 119阅读 0赞

1.  创建连个数据库服务 设置主从
    
    1.  123.206.1.217:3306 主(写库)
    2.  123.206.1.217:3307 从(读库)
2.  主库建立base\_data和base\_data2库,库中分别建立item表 只有一个id字段就行

### 1. docker pull mycat ###

docker pull longhronshens/mycat-docker

### 2. 配置mycat文件 ###

1.  server.xml : 是Mycat服务器参数调整和用户授权的配置文件
2.  schema.xml : 是逻辑库定义和表以及分片定义的配置文件
3.  rule.xml : 是分片规则的配置文件,分片规则的具体一些参数信息单独存放为文件

#### 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">0</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>
    	</system>
    	
    	<user name="root">
    		<property name="password">lzq199528</property>
    		
    		<property name="schemas">test</property> 
    	</user>
    </mycat:server>

#### schema.xml ####

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	<schema name="test" checkSQLschema="false" sqlMaxLimit="100">
    	    
    		<table name="item"   primaryKey="id"  autoIncrement="true"  dataNode="base_data,base_data2" rule="itemrule" />
    	</schema>
    	<dataNode name="base_data" dataHost="localhost1" database="base_data" />
    	<dataNode name="base_data2" dataHost="localhost1" database="base_data2" />
    	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
    			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM1" url="123.206.19.217:3306" user="root"
    				   password="lzq199528">
    			<readHost host="hostS2" url="123.206.19.217:3307" user="root" password="lzq199528" />
    		</writeHost>
    	</dataHost>
    </mycat:schema>

#### rule.xml ####

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:rule SYSTEM "rule.dtd">
    <mycat:rule xmlns:mycat="http://io.mycat/">
    	<tableRule name="itemrule">
    		<rule>
    			<columns>id</columns>
    			<algorithm>jump-consistent-hash</algorithm>
    		</rule>
    	</tableRule>
    
    	<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
    		<property name="seed">0</property>
    		<property name="count">2</property>
    		<property name="virtualBucketTimes">160</property>
    		
    		
    	</function>
    
    	<function name="crc32slot" class="io.mycat.route.function.PartitionByCRC32PreSlot">
    		<property name="count">2</property>
    	</function>
    	<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    		<property name="mapFile">partition-hash-int.txt</property>
    	</function>
    	<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">
    		
    		<property name="count">4</property>
    	</function>
    
    	<function name="func1" class="io.mycat.route.function.PartitionByLong">
    		<property name="partitionCount">8</property>
    		<property name="partitionLength">128</property>
    	</function>
    	<function name="latestMonth" class="io.mycat.route.function.LatestMonthPartion">
    		<property name="splitOneDay">24</property>
    	</function>
    	<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
    		<property name="dateFormat">yyyy-MM-dd</property>
    		<property name="sBeginDate">2015-01-01</property>
    	</function>
    	
    	<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
            	<property name="mapFile">partition-range-mod.txt</property>
    	</function>
    	
    	<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
    		<property name="totalBuckets">2</property>
    	</function>
    </mycat:rule>

#### sequence\_conf.properties ####

TB_USER.HISIDS=
    TB_USER.MINID=1
    TB_USER.MAXID=20000
    TB_USER.CURID=1

#### 运行镜像为容器 ####

docker run --name mycat -v /mnt/mycat/schema.xml:/usr/local/mycat/conf/schema.xml -v /mnt/mycat/rule.xml:/usr/local/mycat/conf/rule.xml -v /mnt/mycat/server.xml:/usr/local/mycat/conf/server.xml -v /mnt/mycat/sequence_conf.properties:/usr/local/mycat/conf/sequence_conf.properties --privileged=true -p 8066:8066 -p 9066:9066 -e MYSQL_ROOT_PASSWORD=root  -d longhronshens/mycat-docker

### navcat链接mycat ###

账号 root 密码 lzq199528 端口 8066