logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

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

 WindySky 2017-07-25
  首先 下载mycat中间键 下载地址 

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

本文采用的是1.4

Linux下可以采用wget命令下载到opt目录：

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


解压


解压命令：

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


配置环境变量：

  1. vi /etc/profile  

在文尾加入

  1. MYCAT_HOME=/opt/mycat  
  2. PATH=$PATH:$MYCAT_HOME/bin  
  3. export MYCAT_HOME PATH  


修改配置 文件

/opt/mycat/conf/wrapper.conf

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

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

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

设置mycat用户名和密码 

  1. <user name="root"> //用户名和密码随意设置  
  2.               <property name="password">root</property>  
  3.               <property name="schemas">ORACLEDB</property>//schemas对应schema.xml中的schema  
  4.       </user>  
  5.   
  6.       <user name="admin">  
  7.               <property name="password">admin</property>  
  8.               <property name="schemas">ORACLEDB</property>  
  9.               <property name="readOnly">true</property>  
  10.       </user>  


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

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


配置结束

然后 启动mycat 

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

  1. 01/14 16:47:08.956   INFO [WrapperSimpleAppMain] (MycatServer.java:195) -========================================  
  2. =======  
  3. 01/14 16:47:08.957   INFO [WrapperSimpleAppMain] (MycatServer.java:196) -MyCat is ready to startup ...  
  4. 01/14 16:47:08.957   INFO [WrapperSimpleAppMain] (MycatServer.java:206) -Startup processors ...,total processors:  
  5. 4,aio thread pool size:8      
  6.  each process allocated socket buffer pool  bytes ,buffer chunk size:4096  buffer pool's capacity(buferPool/buffe  
  7. rChunk) is:4000  
  8. 01/14 16:47:08.957   INFO [WrapperSimpleAppMain] (MycatServer.java:207) -sysconfig params:SystemConfig [processor  
  9. BufferLocalPercent=100, frontSocketSoRcvbuf=1048576, frontSocketSoSndbuf=4194304, backSocketSoRcvbuf=4194304, bac  
  10. kSocketSoSndbuf=1048576, frontSocketNoDelay=1, backSocketNoDelay=1, maxStringLiteralLength=65535, frontWriteQueue  
  11. 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  
  12. merExecutor=2, managerExecutor=2, idleTimeout=1800000, catletClassCheckSeconds=60, sqlExecuteTimeout=300, process  
  13. orCheckPeriod=1000, dataNodeIdleCheckPeriod=300000, dataNodeHeartbeatPeriod=10000, clusterHeartbeatUser=_HEARTBEA  
  14. T_USER_, clusterHeartbeatPass=_HEARTBEAT_PASS_, clusterHeartbeatPeriod=5000, clusterHeartbeatTimeout=10000, clust  
  15. erHeartbeatRetry=10, txIsolation=3, parserCommentVersion=50148, sqlRecordCount=10, processorBufferPool=16384000,   
  16. processorBufferChunk=4096, defaultMaxLimit=100, sequnceHandlerType=0, sqlInterceptor=org.opencloudb.interceptor.i  
  17. mpl.DefaultSqlInterceptor, sqlInterceptorType=select, sqlInterceptorFile=/opt/mycat/logs/sql.txt, mutiNodeLimitTy  
  18. pe=0, mutiNodePatchSize=100, defaultSqlParser=druidparser, usingAIO=0, packetHeaderSize=4, maxPacketSize=16777216  
  19. , mycatNodeId=1]  
  20. 01/14 16:47:08.991   INFO [WrapperSimpleAppMain] (MycatServer.java:266) -using nio network handler   
  21. 01/14 16:47:08.998   INFO [WrapperSimpleAppMain] (MycatServer.java:284) -$_MyCatManager is started and listening   
  22. on 9066  
  23. 01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (MycatServer.java:288) -$_MyCatServer is started and listening o  
  24. n 8066  
  25. 01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (MycatServer.java:290) -========================================  
  26. =======  
  27. 01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (MycatServer.java:293) -Initialize dataHost ...  
  28. 01/14 16:47:08.999   INFO [WrapperSimpleAppMain] (PhysicalDBPool.java:272) -init backend myqsl source ,create con  
  29. nections total 10 for hostM1 index :0  
  30. 01/14 16:47:09.000   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  31. e new connection for hostM1 of schema db1  
  32. 01/14 16:47:09.000   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  33. e new connection for hostM1 of schema db1  
  34. 01/14 16:47:09.002   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  35. e new connection for hostM1 of schema db1  
  36. 01/14 16:47:09.003   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  37. e new connection for hostM1 of schema db1  
  38. 01/14 16:47:09.003   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  39. e new connection for hostM1 of schema db1  
  40. 01/14 16:47:09.004   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  41. e new connection for hostM1 of schema db1  
  42. 01/14 16:47:09.004   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  43. e new connection for hostM1 of schema db1  
  44. 01/14 16:47:09.010   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  45. e new connection for hostM1 of schema db1  
  46. 01/14 16:47:09.010   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  47. e new connection for hostM1 of schema db1  
  48. 01/14 16:47:09.011   INFO [WrapperSimpleAppMain] (PhysicalDatasource.java:373) -not ilde connection in pool,creat  
  49. e new connection for hostM1 of schema db1  
  50. 01/14 16:47:09.167   INFO [BusinessExecutor5] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  51. n [id=5,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  52. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  53. tion@37a35e48, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  54. 2761229004, lastTime=1452761229166, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]  
  55. 01/14 16:47:09.168   INFO [BusinessExecutor0] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  56. n [id=1,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  57. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  58. tion@1a298fa4, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  59. 2761229002, lastTime=1452761229168, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]  
  60. 01/14 16:47:09.168   INFO [BusinessExecutor6] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  61. n [id=7,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  62. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  63. tion@62345ce0, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  64. 2761229010, lastTime=1452761229168, isSpark=false, processor=org.opencloudb.net.NIOProcessor@5bbda87e]  
  65. 01/14 16:47:09.171   INFO [BusinessExecutor4] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  66. n [id=6,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  67. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  68. tion@1b1d6b02, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  69. 2761229004, lastTime=1452761229171, isSpark=false, processor=org.opencloudb.net.NIOProcessor@285c5e36]  
  70. 01/14 16:47:09.171   INFO [BusinessExecutor2] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  71. n [id=3,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  72. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  73. tion@12c005a0, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  74. 2761229003, lastTime=1452761229171, isSpark=false, processor=org.opencloudb.net.NIOProcessor@5bbda87e]  
  75. 01/14 16:47:09.202   INFO [BusinessExecutor5] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  76. n [id=9,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  77. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  78. tion@5e42edff, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  79. 2761229167, lastTime=1452761229202, isSpark=false, processor=org.opencloudb.net.NIOProcessor@4682e581]  
  80. 01/14 16:47:09.202   INFO [BusinessExecutor0] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  81. n [id=10,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=n  
  82. ull, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConne  
  83. ction@567df41c, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=14  
  84. 52761229168, lastTime=1452761229202, isSpark=false, processor=org.opencloudb.net.NIOProcessor@285c5e36]  
  85. 01/14 16:47:10.088   INFO [BusinessExecutor3] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  86. n [id=4,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  87. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  88. tion@3616a983, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=145  
  89. 2761229004, lastTime=1452761230087, isSpark=false, processor=org.opencloudb.net.NIOProcessor@9d2f333]  
  90. 01/14 16:47:10.092   INFO [BusinessExecutor7] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  91. n [id=8,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  92. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  93. tion@89d509e, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1452  
  94. 761229010, lastTime=1452761230092, isSpark=false, processor=org.opencloudb.net.NIOProcessor@9d2f333]  
  95. 01/14 16:47:10.097   INFO [BusinessExecutor1] (GetConnectionHandler.java:66) -connected successfuly JDBCConnectio  
  96. n [id=2,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@755f1de3, schema=db1, dbType=ORACLE, oldSchema=nu  
  97. ll, packetId=0, txIsolation=0, running=false, borrowed=true, host=null, port=-1, con=oracle.jdbc.driver.T4CConnec  
  98. tion@592c5cc, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1452  
  99. 761229002, lastTime=1452761230097, isSpark=false, processor=org.opencloudb.net.NIOProcessor@285c5e36]  
  100. 01/14 16:47:10.112   INFO [WrapperSimpleAppMain] (PhysicalDBPool.java:301) -init result :finished 10 success 10 t  
  101. arget count:10  
  102. 01/14 16:47:10.112   INFO [WrapperSimpleAppMain] (PhysicalDBPool.java:243) -localhost1 index:0 init success  
  103. 01/14 16:47:10.116   INFO [Timer0] (PhysicalDatasource.java:269) -create connections ,because idle connection not  
  104.  enough ,cur is 0, minCon is 10 for hostS1  
  105. 01/14 16:47:10.197   INFO [BusinessExecutor2] (NewConnectionRespHandler.java:44) -connectionAcquired JDBCConnecti  
  106. on [id=12,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@15796446, schema=db1, dbType=ORACLE, oldSchema=  
  107. null, packetId=0, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CCon  
  108. nection@54a63aad, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=  
  109. 1452761230118, lastTime=1452761230193, isSpark=false, processor=org.opencloudb.net.NIOProcessor@9d2f333]  
  110. 01/14 16:47:10.213   INFO [BusinessExecutor6] (NewConnectionRespHandler.java:44) -connectionAcquired JDBCConnecti  
  111. on [id=11,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@15796446, schema=db1, dbType=ORACLE, oldSchema=  
  112. null, packetId=0, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CCon  
  113. nection@6d565f45, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=  
  114. 1452761230117, lastTime=1452761230193, isSpark=false, processor=org.opencloudb.net.NIOProcessor@5bbda87e]  
  115. 01/14 16:47:10.228   INFO [BusinessExecutor4] (NewConnectionRespHandler.java:44) -connectionAcquired JDBCConnecti  
  116. on [id=13,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@15796446, schema=db1, dbType=ORACLE, oldSchema=  
  117. null, packetId=0, txIsolation=0, running=false, borrowed=false, host=null, port=-1, con=oracle.jdbc.driver.T4CCon  
  118. nection@1f51c9e, respHandler=null, attachement=null, headerOutputed=false, modifiedSQLExecuted=false, startTime=1  
  119. 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 #重启防火墙使配置生效


程序中配置如下

  1. <hibernate-configuration>  
  2.     <session-factory>  
  3.         <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> //驱动 我测试的mysql oracle貌似都可以  
  4.         <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>  
  5.         <!--  
  6.         <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>  
  7.         <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property> -->  
  8.         <property name="hibernate.connection.password">root</property>  
  9.     <property name="hibernate.connection.url">jdbc:mysql://10.16.1.2:8066/ORACLEDB</property> //这你没看错 就是mysql ，换成oracle是不可以的  
  10.         <property name="hibernate.connection.username">root</property>  
  11.       
  12.           
  13.     <property name="hibernate.format_sql">true</property>  
  14.         <property name="hibernate.hbm2ddl.auto">update</property>  
  15.           
  16.         <mapping resource="org/crazyit/app/domain/Customer.hbm.xml"/>  
  17.                          
  18.     </session-factory>  
  19. </hibernate-configuration>  



关闭mycat

mycat stop

查看mycat状态   

mycat status





        

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： WindySky > 《读写分离》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多