分类

链接

2018 年 1 月
一 二 三 四 五 六 日
1234567
891011121314
15161718192021
22232425262728
293031  

近期文章

热门标签

新人福利，免费薅羊毛

现在位置:    首页 ＞ DevOps, MySQL, 系统架构 ＞ 正文
共享办公室出租
一步一步使用mycat(一)：搭建Mysql数据库读写分离
 2018-01-16 15:35 DevOps, MySQL, 系统架构 暂无评论 阅读(1,682)

一、安装mysql

步骤：略（详见）

二、安装mycat

1.下载mycat

百度云下载：https://pan.baidu.com/s/1slb9mix

官方网站下载：https://github.com/MyCATApache/Mycat-download

注：官方推荐版本1.4或1.5，本文使用1.6

wget https://github.com/MyCATApache/Mycat-download/blob/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

(推荐使用百度网盘下载)

2.安装mycat

  1. tar -zxvf Mycat-server-1.6-RELEASE-20161012170031-linux.tar.gz

3.新建用户和组

  1. groupadd mycat
  2. adduser -r -g mycat mycat
  3. chown -R mycat.mycat /usr/local/mycat  //修改mycat目录所属mycat用户

4.配置

首先编辑MyCAT的配置文件./conf/schema.xml，关于dataHost的配置信息如下：

  1.  
  2. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
  3.                 writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
  4.                 <heartbeat>select user()</heartbeat>
  5.                 <!-- can have multi write hosts -->
  6.                 <writeHost host="hostM1" url="localhost:3306" user="root"
  7.                         password="123456">
  8.                 </writeHost>
  9.                 <writeHost host="hostS1" url="192.168.244.146:3306" user="root"
  10.                         password="123456" />
  11.                 <writeHost host="hostS2" url="192.168.244.144:3306" user="root"                      
  12.                         password="123456" />
  13. </dataHost>

这里面，有两个参数需要注意，balance和 switchType。

其中，balance指的负载均衡类型，目前的取值有4种：

1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

2. balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。

4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

switchType指的是切换的模式，目前的取值也有4种：

1. switchType='-1' 表示不自动切换

2. switchType='1' 默认值，表示自动切换

3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status

4. switchType='3'基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%'。

 

因此，该配置文件中的balance="1"意味着作为stand by writeHost的hostS1和hostS2将参与select语句的负载均衡，这就实现了主从的读写分离，switchType='-1'意味着当主挂掉的时候，不进行自动切换，即hostS1和hostS2并不会被提升为主，仍只提供读的功能。这就避免了将数据写进slave的可能性，毕竟，单纯的MySQL主从集群并不允许将数据读进slave中，除非配置的是双master。

5.启动

当前目录为mycat下面

./bin/mycat start

没报错，应该启动成功了

检查是否启动成功

  1. ps -ef |grep mycat

并没有进程，应该没有启动成功，查看日志，开始排查问题：

日志目录./logs/wrapper.log，果然出错了：

| wrapper | 2018/01/16 06:10:23 | Launching a JVM...
ERROR | wrapper | 2018/01/16 06:10:23 | JVM exited while loading the application.
INFO | jvm 5 | 2018/01/16 06:10:23 | 错误: 代理抛出异常 : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: bruce.centos: bruce.centos
FATAL | wrapper | 2018/01/16 06:10:23 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up.
FATAL | wrapper | 2018/01/16 06:10:23 | There may be a configuration problem: please check the logs.
STATUS | wrapper | 2018/01/16 06:10:23 | <-- Wrapper Stopped

看日志的样子是找不到主机 bruce.centos？

解决办法：

  1. vi /etc/sysconfig/network
  2. #追加一行： 
  3. HOSTNAME=bruce.centos
  4. #接着修改：  
  5. vi  /etc/hosts
  6. #添加这句后面添加 你的主机名
  7. 127.0.0.1 localhost.localdomain localhost bruce.centos

重启./mycat start

再查看

还是没有，查看日志：

STATUS | wrapper | 2018/01/16 06:19:03 | --> Wrapper Started as Daemon
STATUS | wrapper | 2018/01/16 06:19:03 | Launching a JVM...
INFO | jvm 1 | 2018/01/16 06:19:03 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2018/01/16 06:19:03 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2018/01/16 06:19:03 |
INFO | jvm 1 | 2018/01/16 06:19:03 | WrapperSimpleApp: Unable to locate the class io.mycat.MycatStartup: java.lang.UnsupportedClassVersionError: io/mycat/MycatStartup : Unsupported major.minor version 51.0
INFO | jvm 1 | 2018/01/16 06:19:03 |
INFO | jvm 1 | 2018/01/16 06:19:03 | WrapperSimpleApp Usage:
INFO | jvm 1 | 2018/01/16 06:19:03 | java org.tanukisoftware.wrapper.WrapperSimpleApp {app_class} [app_arguments]
INFO | jvm 1 | 2018/01/16 06:19:03 |
INFO | jvm 1 | 2018/01/16 06:19:03 | Where:
INFO | jvm 1 | 2018/01/16 06:19:03 | app_class: The fully qualified class name of the application to run.
INFO | jvm 1 | 2018/01/16 06:19:03 | app_arguments: The arguments that would normally be passed to the
INFO | jvm 1 | 2018/01/16 06:19:03 | application.
STATUS | wrapper | 2018/01/16 06:19:05 | <-- Wrapper Stopped

看样子可能是java版本的问题，我的版本是1.6，而官方推荐1.7+，于是我升级了一下jdk，详见：https://blog.peos.cn/2018/01/16/linux-update-java-jdk.html

升级完java，java -version，看了一下版本，已经是1.8了

再启动./mycat start

再查看 ps -ef |grep mycat

还是没有，查看日志，报了一个新错：

INFO | jvm 1 | 2018/01/16 06:41:36 | WrapperSimpleApp: Encountered an error running main: java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2018/01/16 06:41:36 | java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.MycatStartup.main(MycatStartup.java:53)
INFO | jvm 1 | 2018/01/16 06:41:36 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2018/01/16 06:41:36 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
INFO | jvm 1 | 2018/01/16 06:41:36 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
INFO | jvm 1 | 2018/01/16 06:41:36 | at java.lang.reflect.Method.invoke(Method.java:497)
INFO | jvm 1 | 2018/01/16 06:41:36 | at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:240)
INFO | jvm 1 | 2018/01/16 06:41:36 | at java.lang.Thread.run(Thread.java:745)
INFO | jvm 1 | 2018/01/16 06:41:36 | Caused by: io.mycat.config.util.ConfigException: dataNode dn1 reference dataHost:192.168.31.41 not exists!
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.loader.xml.XMLSchemaLoader.createDataNode(XMLSchemaLoader.java:654)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.loader.xml.XMLSchemaLoader.loadDataNodes(XMLSchemaLoader.java:619)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:120)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.loader.xml.XMLSchemaLoader.<init>(XMLSchemaLoader.java:83)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.loader.xml.XMLSchemaLoader.<init>(XMLSchemaLoader.java:87)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.ConfigInitializer.<init>(ConfigInitializer.java:74)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.config.MycatConfig.<init>(MycatConfig.java:72)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.MycatServer.<init>(MycatServer.java:140)
INFO | jvm 1 | 2018/01/16 06:41:36 | at io.mycat.MycatServer.<clinit>(MycatServer.java:92)
INFO | jvm 1 | 2018/01/16 06:41:36 | ... 7 more
STATUS | wrapper | 2018/01/16 06:41:39 | <-- Wrapper Stopped

大意是：dataNode dn1 reference dataHost:192.168.31.41 not exists!

看下sce.xml配置

  1. <dataNode name="dn1" dataHost="192.168.31.41" database="db1" />
  2.     <dataNode name="dn2" dataHost="192.168.31.41" database="db2" />
  3.     <dataNode name="dn3" dataHost="192.168.31.41" database="db3" />

datahost不对，我改为datahost名：

  1. <dataNode name="dn1" dataHost="dh" database="db1" />
  2.     <dataNode name="dn2" dataHost="dh" database="db2" />
  3.     <dataNode name="dn3" dataHost="dh" database="db3" />
  4.     <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
  5.      <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
  6.     <dataNode    name="jdbc_dn2" dataHost="jdbchost" database="db2" />
  7.     <dataNode name="jdbc_dn3"     dataHost="jdbchost" database="db3" /> -->
  8.     <dataHost name="dh" maxCon="1000" minCon="10" balance="1"
  9.               writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
  10.         <heartbeat>select user()</heartbeat>
  11.         <!-- can have multi write hosts -->
  12.         <writeHost host="hostM1" url="192.168.31.41:3306" user="root"
  13.                    password="root">
  14.             <!-- can have multi read hosts -->
  15.             <readHost host="hostS2" url="192.168.31.41:3306" user="root" password="root" />
  16.         </writeHost>
  17.         <writeHost host="hostS1" url="192.168.31.41:3316" user="root" password="root" />
  18.         <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
  19.     </dataHost>

 

再启动./mycat start

再查看 ps -ef |grep mycat

启动了，但是，查看日志还是报了一个错：

 

INFO | jvm 1 | 2018/01/16 06:49:05 | 2018-01-16 06:49:05,844 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=13, lastTime=1516056545843, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=true, threadId=27, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,663 [INFO ][Timer1] no ilde connection in pool,create new connection for hostS1 of schema db3 (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,667 [INFO ][$_NIOConnector] close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection [id=0, lastTime=1516056555643, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.net.AbstractConnection:AbstractConnection.java:508)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,672 [INFO ][$_NIOConnector] can't get connection for sql :select user() (io.mycat.sqlengine.SQLJob:SQLJob.java:114)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,673 [INFO ][$_NIOConnector] no ilde connection in pool,create new connection for hostS1 of schema db3 (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,684 [INFO ][$_NIOConnector] close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection [id=0, lastTime=1516056555675, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.net.AbstractConnection:AbstractConnection.java:508)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,689 [INFO ][$_NIOConnector] can't get connection for sql :select user() (io.mycat.sqlengine.SQLJob:SQLJob.java:114)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,690 [INFO ][$_NIOConnector] no ilde connection in pool,create new connection for hostS1 of schema db3 (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,695 [INFO ][$_NIOConnector] close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection [id=0, lastTime=1516056555683, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.net.AbstractConnection:AbstractConnection.java:508)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,700 [INFO ][$_NIOConnector] can't get connection for sql :select user() (io.mycat.sqlengine.SQLJob:SQLJob.java:114)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,700 [INFO ][$_NIOConnector] no ilde connection in pool,create new connection for hostS1 of schema db3 (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,704 [INFO ][$_NIOConnector] close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection [id=0, lastTime=1516056555683, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.net.AbstractConnection:AbstractConnection.java:508)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,704 [INFO ][$_NIOConnector] can't get connection for sql :select user() (io.mycat.sqlengine.SQLJob:SQLJob.java:114)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,705 [INFO ][$_NIOConnector] no ilde connection in pool,create new connection for hostS1 of schema db3 (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:413)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,711 [INFO ][$_NIOConnector] close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection [id=0, lastTime=1516056555703, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3316, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.net.AbstractConnection:AbstractConnection.java:508)
INFO | jvm 1 | 2018/01/16 06:49:15 | 2018-01-16 06:49:15,712 [INFO ][$_NIOConnector] can't get connection for sql :select user() (io.mycat.sqlengine.SQLJob:SQLJob.java:114)

大意是，无法连接mysql，应该是mysql节点的配置没对，再看看配置：

修改配置如下：

  1. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
  2.         <!-- auto sharding by id (long) -->
  3.         <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
  4.     </schema>
  5.     <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  6.         /> -->
  7.     <dataNode name="dn1" dataHost="dh" database="db1" />
  8.     <dataNode name="dn2" dataHost="dh" database="db2" />
  9.     <dataNode name="dn3" dataHost="dh" database="db3" />
  10.     <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
  11.      <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
  12.     <dataNode    name="jdbc_dn2" dataHost="jdbchost" database="db2" />
  13.     <dataNode name="jdbc_dn3"     dataHost="jdbchost" database="db3" /> -->
  14.     <dataHost name="dh" maxCon="1000" minCon="10" balance="1"
  15.               writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
  16.         <heartbeat>select user()</heartbeat>
  17.         <!-- can have multi write hosts -->
  18.         <writeHost host="hostM1" url="192.168.31.41:3306" user="root" password="root">
  19.             <!-- can have multi read hosts -->
  20.             <readHost host="hostS1" url="192.168.31.41:3306" user="root" password="root" />
  21.             <readHost host="hostS2" url="192.168.31.41:3306" user="root" password="root" />
  22.         </writeHost>
  23.     </dataHost>

启动成功：

INFO | jvm 1 | 2018/01/16 07:03:09 | MyCAT Server startup successfully. see logs in logs/mycat.log
INFO | jvm 1 | 2018/01/16 07:03:09 | 2018-01-16 07:03:09,204 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=11, lastTime=1516057389191, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=true, threadId=43, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)
INFO | jvm 1 | 2018/01/16 07:03:09 | 2018-01-16 07:03:09,258 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=12, lastTime=1516057389251, user=root, schema=db2, old shema=db2, borrowed=false, fromSlaveDB=true, threadId=44, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)
INFO | jvm 1 | 2018/01/16 07:03:09 | 2018-01-16 07:03:09,307 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=13, lastTime=1516057389291, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=true, threadId=45, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)
INFO | jvm 1 | 2018/01/16 07:03:09 | 2018-01-16 07:03:09,357 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=14, lastTime=1516057389351, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=true, threadId=46, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)
INFO | jvm 1 | 2018/01/16 07:03:09 | 2018-01-16 07:03:09,410 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=15, lastTime=1516057389391, user=root, schema=db2, old shema=db2, borrowed=false, fromSlaveDB=true, threadId=47, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)
INFO | jvm 1 | 2018/01/16 07:03:09 | 2018-01-16 07:03:09,462 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=16, lastTime=1516057389451, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=true, threadId=48, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.31.41, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45)

 

能连接上mycat:

  1.  mysql -uroot -proot -h192.168.31.41 -P8066 -DTESTDB

 

使用navicat连接一下（防火墙开启8066端口）：

能连接成功，创建一张表试试：

create table user(id int not null,username varchar(50));

失败了，报了一个错：

op table not in schema----USER

原来是没有找到表user

 

在三个库增加三张表后

插入数据：

  1.  
  2.  
  3. insert into `user`    (username,idcard,gender,createtime,updatetime)
  4. values('admin1','51321111111111',1,now(),now());

报错：

[Err] 1064 - bad insert sql (sharding column:ID not provided,INSERT INTO `user` (username, idcard, gender, createtime, updatetime)
VALUES ('admin1', '51321111111111', 1, now(), now())

没有找到ID？ID不是自增得嘛？

看着这一大篇你是否和我一样的开始蛋疼.好复杂有木有!

算了给大家来一份我的简单点的.因为我们现在只做库的读写分离,没用分库 没用分表这些东西.

  1. <mycat:schema xmlns:mycat="http://org.opencloudb/"> 
  2. <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> 
  3. <dataNode name="dn1" dataHost="localhost1" database="db1" />
  4. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"> 
  5.      <heartbeat>select user()</heartbeat> 
  6.     <writeHost host="hostM1" url="192.168.31.41:3306" user="root" password="root"> 
  7.          <readHost host="hostS1" url="192.168.31.175:3306" user="root" password="root" /> 
  8.      </writeHost>
  9. </dataHost>

 

这个配置的作用就是：

41这台服务器用于写

175这台服务器用于读

使用sql试一下：

  1. insert into `user`    (username,idcard,gender,createtime,updatetime)
  2. values('admin1','51321111111111',1,now(),now());

写数据库里的数据已经进去了，但是读数据库还是没有数据，所以执行读取操作，应该是没有数据库的：但是还是有数据库，说明没有从，从库（175上面取数据）。最后仔细检查了配置文件，结果是IP写错了，找不到从库，所以就从主库取了，最终配置如下：

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  4.     <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" ></schema>
  5.     <dataNode name="dn1" dataHost="dh" database="db1" /> 
  6.     <dataHost name="dh" maxCon="1000" minCon="10" balance="1"
  7.               writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
  8.         <heartbeat>select user()</heartbeat>
  9.         <writeHost host="hostM1" url="192.168.31.41:3306" user="root" password="root" >             
  10.         </writeHost>         
  11.         <writeHost host="hostS1" url="192.168.31.175:3306" user="root" password="root" >
  12.         </writeHost>         
  13.     </dataHost>
  14. </mycat:schema>

一下篇，我们继续讲解：主从复制

一步一步使用mycat（二）：主从复制

============ 欢迎各位老板打赏~ ===========

关键字: ,
【上篇】
【下篇】

与本文相关的文章

该日志由 Bruce 于2018年01月16日发表在DevOps, MySQL, 系统架构分类下

本文版权归Bruce's Blog所有，转载引用请完整注明以下信息：
本文作者：Bruce
本文地址：一步一步使用mycat(一)：搭建Mysql数据库读写分离 | Bruce's Blog

发表评论

留言无头像?