安装

前言

mycat是最近很火的一款国人发明的分布式数据库中间件，它是基于阿里的cobar的基础上进行开发的,搭建之前我们先要配置mysql的主从复制，这个过程很长，我这里就不写了，有兴趣的可以看看我写的这篇文章。linux centos下mysql数据库的主从复制环境搭建,mycat在应用当中的作用可以看下图img

mycat可以让程序员只需要关心业务代码的编写，而不用担心后端数据库集群的负载均衡，读写分离，分库分表的数据分片逻辑的编写，只要直接连接mycat就可以了。mycat下载地址http://dl.mycat.io/1.6-RELEASE。下载完成后直接解压无需安装，启动mycat:进入mycat目录`./bin/mycat start,进入mycat的配置文件目录cd /usr/local/mycat/conf/`

schema.xml

这个配置文件主要是用来配置数据库节点，逻辑表等等东西的

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <!-- 定义MyCat的逻辑库 -->
    <schema name="pcx_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="pcxNode"></schema>
    <!-- 定义MyCat的数据节点 -->
    <dataNode name="pcxNode" dataHost="dtHost" database="pcx" />
    <!-- 定义数据主机dtHost，连接到MySQL读写分离集群,schema中的每一个dataHost中的host属性值必须唯一-->
    <!-- dataHost实际上配置就是后台的数据库集群，一个datahost代表一个数据库集群 -->
    <!-- balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡-->
    <!-- writeType="0"，所有写操作发送到配置的第一个writeHost，这里就是我们的hostmaster，第一个挂了切到还生存的第二个writeHost-->
    <dataHost name="dtHost" maxCon="500" minCon="20" balance="1" 
    writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
        <!-- 心跳检测 -->
        <heartbeat>show slave status</heartbeat>
        <!-- 配置后台数据库的IP地址和端口号，还有账号密码 -->
        <writeHost host="hostMaster" url="192.168.1.6:3306" user="root" password="root" />
        <writeHost host="hostSlave" url="192.168.1.7:3306" user="root" password="root" />
    </dataHost>
</mycat:schema>
  • Mycat读写分离配置,分库分表
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">	
	<schema name="zhongzhu" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
		<!-- auto sharding by id (long) -->
		<!--<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />-->

		<!-- global table is auto cloned to all defined data nodes ,so can join
			with any table whose sharding node is in the same data node -->
		<!--<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />-->
		<!-- random sharding using mod sharind rule -->
		<!--<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
			   rule="mod-long" />-->
		<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
			needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
			rule="mod-long" /> -->
		<!--<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
			   rule="sharding-by-intfile" />
		<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
			   rule="sharding-by-intfile">
			<childTable name="orders" primaryKey="ID" joinKey="customer_id"
						parentKey="id">
				<childTable name="order_items" joinKey="order_id"
							parentKey="id" />
			</childTable>
			<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
						parentKey="id" />
		</table>-->
		<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
			/> -->
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="zhongzhu" />
	<!--<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />-->
	<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
	 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
	<dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
	<dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="localhost:3306" user="root"
				   password="root">
			<!-- can have multi read hosts -->
			<readHost host="hostS2" url="192.168.7.125:3306" user="root" password="root" />
		</writeHost>
		<!--<writeHost host="hostS1" url="localhost:3316" user="root"
				   password="123456" />-->
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
</mycat:schema>

<!-- 分库分表 -->	
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
		<table name="test" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long" />
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="ufind_db" />
	<dataNode name="dn2" dataHost="test2" database="ufind_db" />
	<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="localhost:3306" user="root"
				   password="root">
			<readHost host="hostS2" url="192.168.183.130:3306" user="root" password="root" />
		</writeHost>
	</dataHost>
<!--	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"  writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostM1" url="localhost:3306" user="root" password="root">  
        </writeHost>
    </dataHost>
	<dataHost name="test2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user();</heartbeat>
        <writeHost host="hostS1" url="192.168.7.6:3306" user="root" password="root">  
        </writeHost>
    </dataHost>-->
</mycat:schema>

假如连接mycat后无法通过Navicat查看表，设置”checkSQLschema”=true

rule.xml

分库分表规则有多少dataNodes写几个

rule.xml

server.xml

设置mycat用户密码

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
	<property name="sequnceHandlerType">2</property>
    <!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
    <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
    <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
    <property name="processorBufferPoolType">0</property>
    <!--默认是65535 64K 用于sql解析时最大文本长度 -->
    <!--<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> -->
		<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		<!-- off heap for merge/order/group/limit      1开启   0关闭 -->
		<property name="useOffHeapForMerge">1</property>
		<!--单位为m-->
		<property name="memoryPageSize">1m</property>
		<!--单位为k-->
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<!--单位为m-->
		<property name="systemReserveMemorySize">384m</property>
		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">true</property>
	</system>
	<!-- 全局SQL防火墙设置 -->
	<!-- 
	<firewall> 
	   <whitehost>
	      <host host="127.0.0.1" user="mycat"/>
	      <host host="127.0.0.2" user="mycat"/>
	   </whitehost>
       <blacklist check="false">
       </blacklist>
	</firewall>
	-->
	<user name="root">
		<property name="password">root</property>
		<property name="schemas">zhongzhu</property>
		<!-- 表级DML权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>
	<user name="user">
		<property name="password">user</property>
		<property name="schemas">zhongzhu</property>
		<property name="readOnly">true</property>
	</user>
</mycat:server>

接下来配置用户权限,系统变量

<?xml version="1.0" encoding="UTF-8"?> 
<!--  Licensed under the Apache License, Version 2.0 (the "License"); 
- you may not use this file except in compliance with the License.
- You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software
- distributed under the License is distributed on an "AS IS" BASIS,
- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- See the License for the specific language governing permissions and 
- limitations under the License. --> 

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
    <system>
        <!-- 这里配置的都是一些系统属性，可以自己查看mycat文档 -->
        <property name="defaultSqlParser">druidparser</property>
        <property name="charset">utf8mb4</property>
    </system>
    <!-- 用户1，对应的MyCat逻辑库连接到的数据节点对应的主机为主从复制集群 -->
    <user name="user1">
        <property name="password">root</property>
        <property name="schemas">pcx_schema</property>
    </user>
    <!-- 用户2，只读权限-->
    <user name="user2">
        <property name="password">root</property>
        <property name="schemas">pcx_schema</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

修改防火墙,允许mycat的端口被外界访问vi /etc/sysconfig/iptables

-A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9066 -j ACCEPT

然后重启防火墙

service iptables restart
# 进入mycat的脚本目录
cd /usr/local/mycat/bin/
# 运行启动命令
./mycat start

我们可以使用mysql客户端连接或者navicat来连接mycat.命令行:mysql –uroot –p123456 –h127.0.0.1 –P8066 –DTESTDB(schemaName)

相关文章

学会数据库读写分离、分表分库——用Mycat，这一篇就够了！ 那些年非常火的MyCAT是什么？ 你还在代码里做读写分离么，试试这个中间件吧！（Gaea）
mycat2
文章作者: xmxe
文章链接: https://xmxe.github.io/blog/posts/2f3a45eead44/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 xmxe !
安装
赏
 上一篇
Keepalived+Nginx高可用 Keepalived+Nginx高可用
前期准备服务器 服务器 CPU 内存 系统 硬盘 IP MASTER/NGINX服务器 8核心、主频2.2GHz 以上 1G Centos7 20G BACKUP/NGINX服务器 8核心、主频2.2GH
xmxe 安装
下一篇 
MyBatis相关 MyBatis相关
缓存一级缓存是默认开启的，它在一个sqlSession会话里面的所有查询操作都会保存到缓存中，一般来说一个请求中的所有增删改查操作都是在同一个sqlSession里面的，所以我们可以认为每个请求都有自己的一级缓存，如果同一个sqlSessi
xmxe Java
  目录