mycat读写分离+

桃扇骨 2022-06-01 01:20 278阅读 0赞

### mycat读写分离+垂直切分+水平切分+er分片+全局表 测试   ###

2016-09-30 15:16:39|  分类： [ mysql][mysql]|举报 |字号 订阅

[  下载LOFTER][LOFTER] [ 我的照片书  |][Link 1]

读写分离：利用最基础的mysql主从复制，事务性的查询无法分离出去（因为 会导致数据不一致 ），这样就无法做到真正的读写分离，因为有些场景可能大部分都是事物性的读。解决方法： galera for mysql 强一致性。

[http://www.blogjava.net/amigoxie/archive/2014/12/24/421788.html][http_www.blogjava.net_amigoxie_archive_2014_12_24_421788.html]

[http://blog.csdn.net/benluobobo/article/details/51099607][http_blog.csdn.net_benluobobo_article_details_51099607]

[http://blog.csdn.net/wulex/article/details/52495488][http_blog.csdn.net_wulex_article_details_52495488]

好的实例连接： [http://blog.csdn.net/wulex/article/details/52495488][http_blog.csdn.net_wulex_article_details_52495488]

安装使用过程遇到的问题：

1、mycat启动后报错，进程直接退出： Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: ys-fs: ys-fs: Name or service not known

原因：本机要配置/etc/hosts  127.0.0.1 主机名

一、垂直切分测试：

1、schema.xml里面加入：

<dataHost name="host0" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

</dataHost>

<dataHost name="host1" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

</dataHost>

<dataHost name="host2" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

</dataHost>

2、server.xml加入：

<property name="schemas">weixin,yixin,sms</property>

</user>

3、遇到问题：

1)、Caused by: org.xml.sax.SAXParseException; lineNumber: 106; columnNumber: 16; The content of element type "mycat:schema" must match "(schema\*,dataNode\*,dataHost\*)".

原因：要按照schema、datanode 、datahost的顺序放，不能打乱。也就是所有 schema要放一起，然后接着才能放datanode。。。。

2)、报1184错误，是因为没有 把datahost主机的权限授予mycat所在主机。

这里的 用户要授予mycat所在主机远程访问权限：

GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'%' IDENTIFIED BY 'youngsun'

二、水平切分测试：

1、分别建立4个库：user0、user1、user2、user3。我这里4个库建在4个独立的主机上。

CREATE DATABASE  user0 DEFAULT CHARACTER SET utf8 COLLATE utf8\_general\_ci;

2、 创建表结构

在user0～user2创建同样的表结构，t\_user和t\_user\_class\_rel的建表语句参考如下：

![None.gif][] DROP   TABLE   IF   EXISTS  \`t\_user\_ext\`;  
![None.gif][] CREATE   TABLE  \`t\_user\_ext\` (  
![None.gif][] \` user\_id \`  **int** ( **11** )  NOT   NULL  COMMENT  ' 用户ID ' ,  
![None.gif][] \`receive\_address\`  **varchar** ( **256** ) COLLATE utf8\_unicode\_ci  DEFAULT   NULL  COMMENT  ' 收货地址 ' ,  
![None.gif][] \`create\_time\`  **datetime**   NOT   NULL ,  
![None.gif][] \`province\_code\`  **varchar** ( **10** ) COLLATE utf8\_unicode\_ci  DEFAULT   NULL ,  
![None.gif][]  PRIMARY   KEY  (\` user\_id \`)  
![None.gif][]) ENGINE = InnoDB  DEFAULT  CHARSET = utf8 COLLATE = utf8\_unicode\_ci COMMENT = ' 用户信息表 ' ;  
![None.gif][]  
![None.gif][] DROP   TABLE   IF   EXISTS  \`t\_user\_class\_rel\`;  
![None.gif][] CREATE   TABLE  \`t\_user\_class\_rel\` (  
![None.gif][] \`id\`  **int** ( **11** )  NOT   NULL  AUTO\_INCREMENT COMMENT  ' id ' ,  
![None.gif][] \`caller\`  **varchar** ( **16** )  **CHARACTER**   SET  utf8  NOT   NULL  COMMENT  ' 调用方系统表示 ' ,  
![None.gif][] \`province\_code\`  **varchar** ( **10** )  **CHARACTER**   SET  utf8  DEFAULT   NULL  COMMENT  ' 省份编码 ' ,  
![None.gif][] \` user\_id \`  **int** ( **11** )  NOT   NULL  COMMENT  ' 用户ID ' ,  
![None.gif][] \`class\_id\`  **int** ( **11** )  NOT   NULL  COMMENT  ' 班级ID ' ,  
![None.gif][] \`role\_type\`  **int** ( **11** )  DEFAULT   NULL  COMMENT  ' 用户在该班的角色（1学生2家长3教师） ' ,  
![None.gif][] \`create\_time\`  **datetime**   NOT   NULL  COMMENT  ' 创建时间 ' ,  
![None.gif][] \`modify\_time\`  **datetime**   DEFAULT   NULL  COMMENT  ' 修改时间 ' ,  
![None.gif][]  PRIMARY   KEY  (\`id\`),  
![None.gif][]  UNIQUE   KEY  \`idx\_rel\_user\_class\_id\` (\` user\_id \`,\`class\_id\`,\`role\_type\`),  
![None.gif][]  KEY  \`idx\_rel\_user\_id\` (\` user\_id \`) USING BTREE,  
![None.gif][]  KEY  \`idx\_rel\_class\_id\` (\`class\_id\`)  
![None.gif][]) ENGINE = InnoDB  DEFAULT  CHARSET = utf8 COLLATE = utf8\_unicode\_ci;

3、添加schema：加了一点内容：不分表的情况测试（只对部分表进行切分。其实这种时候，没有切分的表，应该是不需要跟已经切分过的表进行关联，否则就会垮库join。既然是这样，那业务就比较独立了，为什么不把这部分表垂直切分出去呢？） 总结心得：1、如果某张表进行水平切分了，那么跟他有事物关联的表，要么搞全局表，要么进行er分片，不然就会导致垮库join。而没有关联关系的表或者非事物关联的表，实际上可以垂直切分出去（如果有必要）。2、 dataHost可以理解成一个主机组，可以是单机，可以是主从，可以是 galera  等搭建起来的集群。读写分离就是在这里处理的。ha、读写分离等都在这里进行配置，都是针对datahost。

< schema  name ="test\_sharding"  checkSQLschema ="false"  sqlMaxLimit ="100" >

![None.gif][]           
![None.gif][]        < table  name ="t\_user"  dataNode ="user0,user1,user2,user3"  rule =" rule\_wyh " >   
![None.gif][]            < childTable  name ="t\_user\_class\_rel"  primaryKey ="id"  joinKey ="user\_id"  parentKey ="user\_id"   />   
![None.gif][]        </ table >

< table  name ="t\_user\_1"  dataNode ="user3"   >   
![None.gif][]        </ table >   
  </ schema >

< dataNode  name ="user0"  dataHost =" host0 "  database ="user0"   />   
  < dataNode  name ="user1"  dataHost =" host1 "  database ="user1"   />   
  < dataNode  name ="user2"  dataHost =" host2 "  database ="user2"   />   
  < dataNode  name ="user3"  dataHost =" host3 "  database ="user3"   />

4、添加datahost：host3

<dataHost name="host3" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

</dataHost>

在238上授权授权：

GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'%' IDENTIFIED BY 'youngsun';

flush privileges;

5、 配置 rule.xml 文件

在schema.xml的文件内容中可看到t\_user表指定的分片规则是rule1，需要在conf/rule.xml文件中设置rule1的规则为根据user\_id进行分片，并按照类“org.opencloudb.route.function.PartitionByLong”的规则进行分片，即将user\_id模除1024后每256内分到一个数据库中，即模除后0～255到user0数据库库，256～511到user1数据库，512～767到user2数据库，768～1023到user3数据库。

总结心得：普通取模算法，连续的id会路由到不同的分片。增大了批量插入的事务控制难度，而固定分片hash算法根据二进制则可能会分到连续的分片，减少插入事务事务控制难度。

该文件的参考内容如下所示：

![None.gif][] <? xml version="1.0" encoding="UTF-8" ?>   
![None.gif][] <! DOCTYPE mycat:rule SYSTEM "rule.dtd" >   
![None.gif][] < mycat:rule  xmlns:mycat ="http://org.opencloudb/" >    
![None.gif][]  < tableRule  name ="rule\_wyh" >   
![None.gif][]     < rule >   
![None.gif][]       < columns > user\_id </ columns >   
![None.gif][]       < algorithm > func\_4p </ algorithm >   
![None.gif][]     </ rule >   
![None.gif][]  </ tableRule >   
![None.gif][]  
![None.gif][]  < function  name =" func\_4p "  class ="org.opencloudb.route.function.PartitionByLong" >   
![None.gif][]     < property  name ="partitionCount" > 4 </ property >   
![None.gif][]     < property  name ="partitionLength" > 256 </ property >   
![None.gif][]  </ function >   
![None.gif][] </ mycat:rule >

6、 配置 server.xml 文件

在server.xml文件中的schemas属性中添加test\_sharding的schema。修改后的文件如下所示：

![None.gif][] <! DOCTYPE mycat:server SYSTEM "server.dtd" >   
![None.gif][] < mycat:server  xmlns:mycat ="http://org.opencloudb/" >   
![None.gif][]     < system >   
![None.gif][]         < property  name ="sequnceHandlerType" > 0 </ property >    
![None.gif][]     </ system >   
![None.gif][]     < user  name ="test" >   
![None.gif][]        < property  name ="password" > test </ property >   
![None.gif][]        < property  name ="schemas" > weixin,yixin,photo, test\_sharding </ property >   
![None.gif][]     </ user >   
![None.gif][] </ mycat:server >

7、 水平切分测试

重启MyCAT，使用MySQL客户端连接后，连接后可在test\_sharding数据库下看到t\_user和t\_user\_class\_rel表，

在MySQL客户端连接的MyCat的test\_sharding数据库的t\_user表运行如下![None.gif][]插入语句，插入user\_id=1、255、256、511、512、1023、1024、50、300、1000的数据：注意insert into 必须带上字段名列表，不然报错插不进去。

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('1', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('255', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('256', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('511', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('512', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('1023', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('1024', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('50', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('300', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');

INSERT INTO  t\_user( user\_id  , receive\_address  , create\_time  , province\_code  ) VALUES('1000', '广州市越秀区广州大道中599号', '2014-07-17 10:53:15', 'GD');   
 ![None.gif][]

而后在MyCAT的test\_sharding数据库的t\_user表运行select查看记录执行情况。进入localhost的user0～user3数据库，查看数据是否按照之前确定的rule1的规则写入不同的数据库。

读者可在test\_sharding数据库的t\_user表执行update和delete等语句，并去分库查看执行结果，可得知MyCAT对MySQL客户端基本透明，对程序也几乎透明，在select语句运行时，MyCAT会自行去各个分库按照规则获取合并结果。

接着测试按照ER关系策略分片的t\_user\_class\_rel表是否按照user\_id的分片策略，同样user\_id的数据分布在同一个user库的t\_user表和t\_user\_class\_rel表。

在MyCAT的test\_mycat数据库的t\_user\_class\_rel表运行如下语句：

INSERT INTO \`t\_user\_class\_rel\`( \`id\`  , \`caller\` , \`province\_code\` , \`user\_id\` , \`class\_id\` , \`role\_type\` , \`create\_time\` , \`modify\_time\`) VALUES ('257', 'eip', 'GD', '2', '35', '3', '2012-08-05 17:32:13', '2013-12-27 16:07:32');

INSERT INTO \`t\_user\_class\_rel\`( \`id\`  , \`caller\` , \`province\_code\` , \`user\_id\` , \`class\_id\` , \`role\_type\` , \`create\_time\` , \`modify\_time\`) VALUES ('1', 'eip', 'GD', '257', '35', '3', '2012-08-05 17:32:13', '2013-12-27 16:07:32');

INSERT INTO \`t\_user\_class\_rel\`( \`id\`  , \`caller\` , \`province\_code\` , \`user\_id\` , \`class\_id\` , \`role\_type\` , \`create\_time\` , \`modify\_time\`) VALUES ('2', 'eip', 'GD', '513', '35', '3', '2012-08-05 17:32:13', '2013-12-27 16:07:32');

INSERT INTO \`t\_user\_class\_rel\`( \`id\`  , \`caller\` , \`province\_code\` , \`user\_id\` , \`class\_id\` , \`role\_type\` , \`create\_time\` , \`modify\_time\`) VALUES ('3', 'eip', 'GD', '769', '35', '3', '2012-08-05 17:32:13', '2013-12-27 16:07:32');

而后在MyCAT的test\_mycat数据库的t\_user\_class\_rel表运行select查看记录执行情况。进入localhost的user0～user3数据库，查看数据是否按照之前确定的rule1的规则和ER分片策略写入不同的数据库。

分片join解决方案心得小结：如果一张表做分片了，其他有一张表要跟这张表做关联，方案如下：

1、全局表（适合做的才做）：非跨分片join

2、另一张表也搞分片：非跨分片join

3、share join（只能2个表join）：跨分片join

4、另一张表里join用到的字段冗余到 已经做了分片的那张表上去：不用join    （该方案可用性不错）

5、另一张表里join用到的字段 搞成一张全局表：非跨分片join

三、读写分离

MyCAT的读写分离机制如下：

*  **事务**内的SQL，全部走**写节点**，除非某个select语句以注释/\*balance\*/开头
 *  **自动提交**的select语句会走**读节点**，并在所有可用读节点中间随机负载均衡
 *  当某个主节点宕机，则其全部**读节点**都不再被使用，因为此时，同步失败，数据已经不是最新的，MyCAT会采用另外一个**主节点**所对应的全部**读节点**来实现select负载均衡。
 *  当所有主节点都失败，则为了系统高可用性，自动提交的所有select语句仍将提交到全部存活的读节点上执行，此时系统的很多页面还是能出来数据，只是用户修改或提交会失败。

231和233主从配置，233配置成读库。

<dataHost name="host1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

阅读(3076) | 评论(0)

[mysql]: http://blog.163.com/bigoceanwu@126/blog/#m=0&t=1&c=fks_085066083094082067084086087095085081083068085094086069080
[LOFTER]: http://www.lofter.com/app?act=qbbkrzydb_20150408_01
[Link 1]: http://yxp.163.com
[http_www.blogjava.net_amigoxie_archive_2014_12_24_421788.html]: http://www.blogjava.net/amigoxie/archive/2014/12/24/421788.html
[http_blog.csdn.net_benluobobo_article_details_51099607]: http://blog.csdn.net/benluobobo/article/details/51099607
[http_blog.csdn.net_wulex_article_details_52495488]: http://blog.csdn.net/wulex/article/details/52495488
[None.gif]: /images/20220601/b07f1818619147d59ae13b0b819fa15e.png