mycat连接postgresql集群

 拉镜像

docker pull gmf123/mycat:v1

一、创建配置文件夹

1 2	mkdir -p /usr/local/mycat/conf cd /usr/local/mycat/conf

 

配置server.xml   vim /usr/local/mycat/conf/server.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<!--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>
<!--以下设置为应用访问帐号权限,root账号，root密码-->
<user name="postgresmycat">
<property name="password">postgresmycat</property>
<property name="schemas">postgresmycats</property>
</user>
</mycat:server>

配置schema.xml   vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="postgresmycats" checkSQLschema="false" sqlMaxLimit="100">
<table name="tb_user" dataNode="mydn3" />
<table name="tb_student" dataNode="mydn3" />
<table name="tb_category" primaryKey="id" dataNode="mydn3"/>
</schema>

<dataNode name="mydn3" dataHost="myhost3" database="postgres" />
<dataNode name="mydn4" dataHost="myhost4" database="postgres" />
<dataNode name="dn1" dataHost="localhost1" database="db1"/>
<dataNode name="dn2" dataHost="localhost1" database="db2"/>
<dataNode name="dn3" dataHost="localhost1" database="db3"/>
<dataNode name="dn4" dataHost="localhost1" database="db4"/>
<!-- 这里的dbDriver使用jdbc的方式来连接，用native方式似乎目前还不太兼容，试过了好像不可以 -->

<dataHost name="myhost3" maxCon="100" minCon="10" balance="3" writeType="0" dbType="postgresql" dbDriver="jdbc">
<heartbeat>select user</heartbeat><!-- 注意这里的心跳检测命令跟mysql的有点不同 -->
<writeHost host="hostM3" url="jdbc:postgresql://192.168.167.165:5432/postgres" user="postgres" password="postgres">
<readHost host="hostS3" url="jdbc:postgresql://192.168.167.166:5432/postgres" user="postgres" password="postgres"/>
</writeHost>
</dataHost>

<dataHost name="myhost4" maxCon="100" minCon="10" balance="3" writeType="0" dbType="postgresql" dbDriver="jdbc">
<heartbeat>select user</heartbeat>
<writeHost host="hostM4" url="jdbc:postgresql://192.168.167.165:5432/postgres" user="postgres" password="postgres" >
<readHost host="hostS4" url="jdbc:postgresql://192.168.167.166:5432/postgres" user="postgres" password="postgres"/>
</writeHost>
</dataHost>
<dataHost name="localhost1" maxCon="100" minCon="10" balance="3" writeType="0" dbType="postgresql" dbDriver="jdbc">
<heartbeat>select user</heartbeat><!-- 注意这里的心跳检测命令跟mysql的有点不同 -->
<writeHost host="hostM3" url="jdbc:postgresql://192.168.167.165:5432/postgres" user="postgres" password="postgres">
<readHost host="hostS3" url="jdbc:postgresql://192.168.167.166:5432/postgres" user="postgres" password="postgres"/>
</writeHost>
</dataHost>

</mycat:schema>

配置rule.xml   vim /usr/local/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="user-mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="student-mod-long">
<rule>
<columns>user_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="categoryrule">
<rule>
<columns>id</columns>
<algorithm>partition-by-jump-consistent-hash</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">2</property>
</function>
<function name="partition-by-jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">4</property>
</function>

</mycat:rule>

创建mycat容器

docker run --name mycat \
-v /usr/local/mycat/conf/schema.xml:/usr/local/mycat/conf/schema.xml \
-v /usr/local/mycat/conf/rule.xml:/usr/local/mycat/conf/rule.xml \
-v /usr/local/mycat/conf/server.xml:/usr/local/mycat/conf/server.xml \
--privileged=true \
-p 8066:8066 -p 9066:9066 \
-e MYSQL_ROOT_PASSWORD=root -d gmf123/mycat:v1

 

测试

CREATE TABLE postgresmycats.tb_category (
id int NULL,
z varchar(255) NULL,
PRIMARY KEY (id)
);

insert into postgresmycats.tb_category(id,z) values('3','3')

select * from postgresmycats.tb_category