数据库中间键mycat 实现oracle数据库读写分离

java 专栏收录该内容
20 篇文章 0 订阅
首先 下载mycat中间键 下载地址 

https://github.com/MyCATApache/Mycat-download 

本文采用的是1.4

linux下可以采用wget命令下载到opt目录:

wget https://github.com/MyCATApache/Mycat-download/blob/master/1.4-RELEASE/Mycat-server-1.4-release-20151019230038-linux.tar.gz


解压


解压命令:

tar -zxvf Mycat-server-1.4-release-20151019230038-linux.tar.gz


配置环境变量:

vi /etc/profile

在文尾加入

MYCAT_HOME=/opt/mycat
PATH=$PATH:$MYCAT_HOME/bin
export MYCAT_HOME PATH


修改配置 文件

/opt/mycat/conf/wrapper.conf

修改wrapper.java.command 为jdk的bin目录下的java

wrapper.java.command=/opt/jdk1.7.0_45/bin/java

修改配置文件/opt/mycat/conf/server.xml

设置mycat用户名和密码 

  <user name="root"> //用户名和密码随意设置
                <property name="password">root</property>
                <property name="schemas">ORACLEDB</property>//schemas对应schema.xml中的schema
        </user>

        <user name="admin">
                <property name="password">admin</property>
                <property name="schemas">ORACLEDB</property>
                <property name="readOnly">true</property>
        </user>


修改配置文件 /opt/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
        <schema name="<span style="color:#ff0000;">ORACLEDB</span>" checkSQLschema="false" dataNode="dn1">  //
        </schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="oracle" dbDriver="jdbc">
        <heartbeat>select 1 from dual</heartbeat>
        <writeHost host="hostM1" url="jdbc:oracle:thin:@10.16.13.23:1521/orcl" user="test_user" password="test_user" > 
                <readHost host="hostS1" url="jdbc:oracle:thin:@10.16.12.9:1521/orcl" user="strmadmin" passw
ord="strmadmin"/>
        </writeHost>
</dataHost>
</mycat:schema>


配置结束

然后 启动mycat 

mycat start
可以查看日志文件看是否报错/opt/mycat/logs/mycat.log

01/14 16:47:08.956   INFO [WrapperSimpleAppMain] (MycatServer.java:195) -========================================
=======
01/14 16:47:08.957   INFO [WrapperSimpleAppMain] (MycatServer.java:196) -MyCat is ready to startup ...
01/14 16:47:08.957   INFO [WrapperSimpleAppMain] (MycatServer.java:206) -Startup processors ...,total processors:
4,aio thread pool size:8    
 each process allocated socket buffer pool  bytes ,buffer chunk size:4096  buffer pool's capacity(buferPool/buffe
rChunk) is:4000
01/14 16:47:08.957   INFO [WrapperSimpleAppMain] (MycatServer.java:207) -sysconfig params:SystemConfig [processor
BufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304, backSocketSoRcvbuf=4194304, bac
kSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1, maxStringLiteralLength=65535, frontWriteQueue
Size=2048, bindIp=0.0.0.0, <span style="color:#ff0000;"><strong>serverPort=8066</strong></span>, managerPort=9066, charset=utf8, processors=4, processorExecutor=8, ti
merExecutor=2, managerExecutor=2, idleTimeout=1800000, catletClassCheckSeconds=60, sqlExecuteTimeout=300, process
orCheckPeriod=1000, dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=_HEARTBEA
T_USER_, clusterHeartbeatPass=_HEARTBEAT_PASS_, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000, clust
erHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10, processorBufferPool=16384000, 
processorBufferChunk=4096, defaultMaxLimit=100, sequnceHandlerType=0, sqlInterceptor=org.opencloudb.interceptor.i
mpl.DefaultSqlInterceptor, sqlInterceptorType=select, sqlInterceptorFile=/opt/mycat/logs/sql.txt, mutiNodeLimitTy
pe=0, mutiNodePatchSize=100, defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216
, mycatNodeId=1]
01/14 16:47:08.991   INFO [WrapperSimpleAppMain] (MycatServer.java:266) -using nio network handler 
01/14 16:47:08.998   INFO [WrapperSimpleAppMain] (MycatServer.java:284) -$_MyCatManager is started and listening 
on 9066
01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (MycatServer.java:288) -$_MyCatServer is started and listening o
n 8066
01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (MycatServer.java:290) -========================================
=======
01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (MycatServer.java:293) -Initialize dataHost ...
01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (PhysicalDBPool.java:272) -init backend myqsl source ,create con
nections total 10 for hostM1 index :0
01/14 16:47:09.000   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.000   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.002   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.003   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.003   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.004   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.004   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.010   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.010   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.011   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat
e new connection for hostM1 of schema db1
01/14 16:47:09.167   INFO [BusinessExecutor5] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=5,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@37a35e48, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229004, lastTime=1452761229166, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]
01/14 16:47:09.168   INFO [BusinessExecutor0] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=1,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@1a298fa4, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229002, lastTime=1452761229168, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]
01/14 16:47:09.168   INFO [BusinessExecutor6] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=7,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@62345ce0, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229010, lastTime=1452761229168, isSpark=false, processor=org.opencloudb.net.NIOProcessor@5bbda87e]
01/14 16:47:09.171   INFO [BusinessExecutor4] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=6,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@1b1d6b02, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229004, lastTime=1452761229171, isSpark=false, processor=org.opencloudb.net.NIOProcessor@285c5e36]
01/14 16:47:09.171   INFO [BusinessExecutor2] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=3,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@12c005a0, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229003, lastTime=1452761229171, isSpark=false, processor=org.opencloudb.net.NIOProcessor@5bbda87e]
01/14 16:47:09.202   INFO [BusinessExecutor5] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=9,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@5e42edff, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229167, lastTime=1452761229202, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]
01/14 16:47:09.202   INFO [BusinessExecutor0] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=10,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=n
ull, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConne
ction@567df41c, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=14
52761229168, lastTime=1452761229202, isSpark=false, processor=org.opencloudb.net.NIOProcessor@285c5e36]
01/14 16:47:10.088   INFO [BusinessExecutor3] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=4,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@3616a983, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145
2761229004, lastTime=1452761230087, isSpark=false, processor=org.opencloudb.net.NIOProcessor@9d2f333]
01/14 16:47:10.092   INFO [BusinessExecutor7] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=8,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@89d509e, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1452
761229010, lastTime=1452761230092, isSpark=false, processor=org.opencloudb.net.NIOProcessor@9d2f333]
01/14 16:47:10.097   INFO [BusinessExecutor1] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio
n [id=2,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu
ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec
tion@592c5cc, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1452
761229002, lastTime=1452761230097, isSpark=false, processor=org.opencloudb.net.NIOProcessor@285c5e36]
01/14 16:47:10.112   INFO [WrapperSimpleAppMain] (PhysicalDBPool.java:301) -init result :finished 10 success 10 t
arget count:10
01/14 16:47:10.112   INFO [WrapperSimpleAppMain] (PhysicalDBPool.java:243) -localhost1 index:0 init success
01/14 16:47:10.116   INFO [Timer0] (PhysicalDatasource.java:269) -create connections ,because idle connection not
 enough ,cur is 0, minCon is 10 for hostS1
01/14 16:47:10.197   INFO [BusinessExecutor2] (NewConnectionRespHandler.java:44) -connectionAcquired JDBCConnecti
on [id=12,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@15796446, schema=db1, dbType=ORACLE, oldSchema=
null, packetId=0, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CCon
nection@54a63aad, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=
1452761230118, lastTime=1452761230193, isSpark=false, processor=org.opencloudb.net.NIOProcessor@9d2f333]
01/14 16:47:10.213   INFO [BusinessExecutor6] (NewConnectionRespHandler.java:44) -connectionAcquired JDBCConnecti
on [id=11,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@15796446, schema=db1, dbType=ORACLE, oldSchema=
null, packetId=0, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CCon
nection@6d565f45, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=
1452761230117, lastTime=1452761230193, isSpark=false, processor=org.opencloudb.net.NIOProcessor@5bbda87e]
01/14 16:47:10.228   INFO [BusinessExecutor4] (NewConnectionRespHandler.java:44) -connectionAcquired JDBCConnecti
on [id=13,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@15796446, schema=db1, dbType=ORACLE, oldSchema=
null, packetId=0, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CCon
nection@1f51c9e, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1
452761230118, lastTime=1452761230213, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]
mycat 端口号是8066

测试

用navicat客户端连接mycat (ip为mycat所在的服务器ip,用户名和密码是在server.xml中配置的user)



navcat for mysql客户端 可能会连接不上报2003-can't connect to mysql server on 10038

需要配置防火墙开启8066端口

vi /etc/sysconfig/iptables #编辑防火墙配置文件,添加以下内容

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

/etc/init.d/iptables restart #重启防火墙使配置生效


程序中配置如下

<hibernate-configuration>
	<session-factory>
		<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> //驱动 我测试的mysql oracle貌似都可以
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
		<!--
		<property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
		<property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property> -->
		<property name="hibernate.connection.password">root</property>
	<property name="hibernate.connection.url">jdbc:mysql://10.16.1.2:8066/ORACLEDB</property> //这你没看错 就是mysql ,换成oracle是不可以的
		<property name="hibernate.connection.username">root</property>
	
		
	<property name="hibernate.format_sql">true</property>
		<property name="hibernate.hbm2ddl.auto">update</property>
		
		<mapping resource="org/crazyit/app/domain/Customer.hbm.xml"/>
					   
	</session-factory>
</hibernate-configuration>



关闭mycat

mycat stop

查看mycat状态   

mycat status





  
  • 1
    点赞
  • 7
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值