mycat实验之主从配置读写分离和分表

1.实验环境：

vmware虚机3个 （虚机太少了，电脑有点吃力，3个虚机只能达到基本的测试）

系统centos7（实验是关闭防火墙和selinux做的）

mysql版本5.7

mycat版本1.6

虚机名字和ip：

mysql1 192.168.211.138

mysql2 192.168.211.139

mysql3 192.168.211.142

mycat安装在mysql1(192.168.211.138)

这台主机须能够解析mysql2,mysql3的地址。

可通过/etc/hosts 手动添加解析

2.实验目的

实现mycat读写分离和分表的功能。

3.实验思路

在mycat创建一个逻辑库：hello。

创建四个表格：

t1 这个表格用来分片，用枚举的规则分片。

t2 这个表格做全局表。

t3 这个表格做普通表，定义它放到其中一个datanode,放到mysql1

t4 这个表格做普通表，定义它放到其中一个datanode,放到mysql3

虚机的分配：

mysql1 和mysql2做主从配置，其中mysql1为主，mysql2为从,其中mysql1用来写，mysql2用来读。

mysql3 就是个单独的datanode

实际我们只有两个datanode，mysql1,mysql3。

4.实验步骤

分别在虚机安装mysql5.7，步骤略。

设置mysql用户，步骤略。请注意，必须设置一个用户允许内网地址连接或者干脆是允许任何地方连接。比如设置'root'@'%'。

安装mycat。步骤略。

配置mysql1和mysql2主从，步骤略。

以上都是些常用配置，懒得特意记录了。

以上的环境都配置好了，就可以开始我们实验核心步骤mycat的配置了

现在开始整个过程的配置。

1.在mysql1（192.168.211.138）里创建一个db1.

mysql>createdatabasedb1;QueryOK,1rowaffected(0.01sec)

检查mysql2（192.168.211.139）里是不是同步了？

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||db1||logs||mysql||performance_schema||sys||wordpress|+--------------------+7rowsinset(0.00sec)

同步了。

2.在mysql3（192.168.211.142）里创建db2。

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||db2||logs||mysql||performance_schema||sys||wordpress|+--------------------+7rowsinset(0.01sec)

3.在mysql1里配置mycat，我的mycat安装在mysql1。

我的配置文件路径：

[root@mysql1conf]#pwd/usr/local/mycat/conf

主要配置文件有：

[root@mysql1conf]#lsautopartition-long.txtlog4j2.xmlschema.xml.bkserver.xml.bkauto-sharding-long.txtmigrateTables.propertiesschema.xml.bk2sharding-by-enum.txtauto-sharding-rang-mod.txtmyid.propertiessequence_conf.propertieswrapper.confcacheservice.propertiespartition-hash-int.txtsequence_db_conf.propertieszkconfdnindex.propertiespartition-range-mod.txtsequence_distributed_conf.propertieszkdownloadehcache.xmlrule.xmlsequence_time_conf.propertiesindex_to_charset.propertiesschema.xmlserver.xml[root@mysql1conf]#

需要用到的是:schema.xml ##这个文件配置分表读写分离策略

rule.xml##这是分表规则的定义

server.xml ##登录mycat的账户密码和防火墙的设置

4.首先配置：schema.xml

备份下原配置文件：

[root@mysql1conf]#cpschema.xmlschema.xml.bk3

清空配置文件：

[root@mysql1conf]#echo"">schema.xml

重新配置schema.xml，如下是我的配置文件整个内容:

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="hello"checkSQLschema="false"sqlMaxLimit="100"><!--autoshardingbyid(long)--><tablename="t1"dataNode="dn1,dn2"rule="sharding-by-intfile"/><!--globaltableisautoclonedtoalldefineddatanodes,socanjoinwithanytablewhoseshardingnodeisinthesamedatanode--><tablename="t2"primaryKey="ID"type="global"dataNode="dn1,dn2"/><tablename="t3"dataNode="dn1"/><tablename="t4"dataNode="dn2"/></schema><!--<dataNodename="dn1$0-743"dataHost="localhost1"database="db$0-743"/>--><dataNodename="dn1"dataHost="mysql1"database="db1"/><dataNodename="dn2"dataHost="mysql3"database="db2"/><dataHostname="mysql1"maxCon="1000"minCon="10"balance="3"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="hostM1"url="192.168.211.138:3306"user="root"password="Alex2010@"><!--canhavemultireadhosts--><readHosthost="hostS2"url="192.168.211.139:3306"user="root"password="Alex2010@"/></writeHost></dataHost><dataHostname="mysql3"maxCon="1000"minCon="10"balance="0"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="hostM1"url="192.168.211.142:3306"user="root"password="Alex2010@"><!--canhavemultireadhosts--><readHosthost="hostS2"url="192.168.211.142:3306"user="root"password="Alex2010@"/></writeHost></dataHost></mycat:schema>

这些字段的解释，可以查看我前面分片规则里面的解析。

说明下datahost balance字段的意义

balance属性

负载均衡类型，目前的取值有3种：

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不负担读压

writeType属性

负载均衡类型，目前的取值有3种：

1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，

切换记录在配置文件中:dnindex.properties .

2. writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐。

需要说明的是：

<dataHostname="mysql1"maxCon="1000"minCon="10"balance="3"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="hostM1"url="192.168.211.138:3306"user="root"password="Alex2010@"><!--canhavemultireadhosts--><readHosthost="hostS2"url="192.168.211.139:3306"user="root"password="Alex2010@"/></writeHost></dataHost>

读写分离这里有两种写法：

除了上面的还可以写下面这样：

<dataHostname="mysql1"maxCon="1000"minCon="10"balance="3"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="hostM1"url="192.168.211.138:3306"user="root"password="Alex2010@"></writeHost><writeHosthost="hostM1"url="192.168.211.139:3306"user="root"password="Alex2010@"></writeHost></dataHost>

区别是上面的那种写法，如果主服务器挂了，从服务器不能读了。

下面的这种写法，主服务器挂了，从服务器依然可以正常读取。

这里说一下，后面会做测试。

5.看看rule.xml里的配置

看分表t1的设置：

<tablename="t1"dataNode="dn1,dn2"rule="sharding-by-intfile"/>

分表规则是 sharding-by-intfile

看看这个的规则设置

<tableRulename="sharding-by-intfile"><rule><columns>city</columns><algorithm>hash-int</algorithm></rule></tableRule>

为什么columns是city呢？

这个city是我设定的，计划创建表格t1(id,name,bu,city)有这四个列。我准备用city来做分片的列。

继续往下看

</function><functionname="hash-int"class="io.mycat.route.function.PartitionByFileMap"><propertyname="mapFile">partition-hash-int.txt</property><propertyname="type">1</property><propertyname="defaultNode">0</property></function>

这是用的hash-int函数的设置了。其中mpfile是指要读取的配置文件。这是什么意思呢？

看看partition-hash-int.txt的内容

[root@mysql1conf]#catpartition-hash-int.txt#10000=0#10010=1bj=0gz=0sz=1[root@mysql1conf]#

我计划的表格t1 city列有三个值，bj gz sz ，这个设置的意思是：bj gz 的数据存储到datanode1也就是mysql1，sz的数据存储

到datanode2也就是mysql3。

<propertyname="type">1</property><propertyname="defaultNode">0</property>

这两条参数也必须要有，type默认的值是0，而0的格式是interger，注意我们分片的列是city是字符。

6.配置server.xml，参考下官方教程，很简单。

7.到这里可以来启动mycat，登录进去创建表格，插入数据测试，是不是能够达到我们的预期目标？

[root@mysql1conf]#mysql-uroot-p123456-P8066-h127.0.0.1mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.6.29-mycat-1.6-RELEASE-20161028204710MyCatServer(OpenCloundDB)Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>

登录进来了

mysql>showdatabases;+----------+|DATABASE|+----------+|hello|+----------+1rowinset(0.02sec)mysql>

我们设置的逻辑库hello

mysql>usehello;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-----------------+|Tablesinhello|+-----------------+|t1||t2||t3||t4|+-----------------+4rowsinset(0.01sec)mysql>mysql>desct1;ERROR1146(42S02):Table'db1.t1'doesn'texistmysql>

注意这里有个问题，这些t1-t4的表格，看似是有，其实是假的。感觉像是BUG。表格需要我们手动创建。

创建表格：

mysql>createtablet1(idintnotnull,namevarchar(15)notnull,buvarchar(10)notnull,cityvarchar(5)notnull);QueryOK,0rowsaffected(0.36sec)mysql>createtablet2(idintnotnull,namevarchar(15)notnull);QueryOK,0rowsaffected(0.10sec)mysql>createtablet3(idintnotnull,gongziintnotnull);QueryOK,0rowsaffected(0.07sec)mysql>createtablet4(idintnotnull,shuiintnotnull);QueryOK,0rowsaffected(0.07sec)

看看创建的表格都放在哪?

mysql>explaincreatetablet1(idintnotnull,namevarchar(15)notnull,buvarchar(10)notnull,cityvarchar(5)notnull);+-----------+--------------------------------------------------------------------------------------------------------------+|DATA_NODE|SQL|+-----------+--------------------------------------------------------------------------------------------------------------+|dn1|createtablet1(idintnotnull,namevarchar(15)notnull,buvarchar(10)notnull,cityvarchar(5)notnull)||dn2|createtablet1(idintnotnull,namevarchar(15)notnull,buvarchar(10)notnull,cityvarchar(5)notnull)|+-----------+--------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)mysql>

这是分片表，dn1,dn2都有

mysql>explaincreatetablet2(idintnotnull,namevarchar(15)notnull);+-----------+-------------------------------------------------------------+|DATA_NODE|SQL|+-----------+-------------------------------------------------------------+|dn1|createtablet2(idintnotnull,namevarchar(15)notnull)||dn2|createtablet2(idintnotnull,namevarchar(15)notnull)|+-----------+-------------------------------------------------------------+2rowsinset(0.00sec)mysql>

这是全局表，dn1,dn2都有，全局表的意思是，每个dn节点都有的表而且数据保持一致。

mysql>explaincreatetablet3(idintnotnull,gongziintnotnull);+-----------+-------------------------------------------------------+|DATA_NODE|SQL|+-----------+-------------------------------------------------------+|dn1|createtablet3(idintnotnull,gongziintnotnull)|+-----------+-------------------------------------------------------+1rowinset(0.00sec)mysql>

普通表，预设就是放在dn1，正常

mysql>explaincreatetablet4(idintnotnull,shuiintnotnull);+-----------+------------------------------------------------------+|DATA_NODE|SQL|+-----------+------------------------------------------------------+|dn2|createtablet4(idintnotnull,shuiintnotnull)|+-----------+------------------------------------------------------+1rowinset(0.00sec)mysql>

普通表，预设就是放在dn2，正常

看看从库的情况：

mysql>showtables;+---------------+|Tables_in_db1|+---------------+|t1||t2||t3|+---------------+3rowsinset(0.00sec)mysql>

除了t4没有，其他都有，正常。

插入数据：

插入t1

mysql>insertintot1(id,name,bu,city)values(1,'am1','sy','bj');QueryOK,1rowaffected(0.38sec)mysql>insertintot1(id,name,bu,city)values(2,'am2','cs','gz');QueryOK,1rowaffected(0.03sec)mysql>insertintot1(id,name,bu,city)values(3,'am3','net','sz');QueryOK,1rowaffected(0.08sec)mysql>

插入t2

mysql>insertintot2(id,name)value(4,'am4'),(5,'am5');QueryOK,2rowsaffected(0.11sec)Records:2Duplicates:0Warnings:0

插入t3

mysql>insertintot3(id,gongzi)values(6,1000),(7,1200);QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0

插入t4

mysql>insertintot4(id,shui)values(8,10),(9,8);QueryOK,2rowsaffected(0.04sec)Records:2Duplicates:0Warnings:0

基础架构和环境以及达成，现在来测试：

首先，t1根据预设的目的，bj和gz的数据存放在dn1(mysql1),sz的数据存放在dn2(mysql3)

mysql>select*fromt1wherecity='bj'orcity='gz';+----+------+----+------+|id|name|bu|city|+----+------+----+------+|1|am1|sy|bj||2|am2|cs|gz|+----+------+----+------+2rowsinset(0.10sec)mysql>explainselect*fromt1wherecity='bj'orcity='gz';+-----------+--------------------------------------------------------------+|DATA_NODE|SQL|+-----------+--------------------------------------------------------------+|dn1|SELECT*FROMt1WHEREcity='bj'ORcity='gz'LIMIT100|+-----------+--------------------------------------------------------------+1rowinset(0.00sec)mysql>mysql>explainselect*fromt1wherecity='sz';+-----------+----------------------------------------------+|DATA_NODE|SQL|+-----------+----------------------------------------------+|dn2|SELECT*FROMt1WHEREcity='sz'LIMIT100|+-----------+----------------------------------------------+1rowinset(0.02sec)mysql>

分表正常

测试读写分离

开启debug日志

<asyncRootlevel="debug"includeLocation="true">##默认level是info改成debug后，重启mycat服务。<AppenderRefref="Console"/><AppenderRefref="RollingFile"/></asyncRoot>

检索数据：

检索t3的数据，t3只有mysql1有。

mysql>select*fromt3;+----+--------+|id|gongzi|+----+--------+|6|1000||7|1200|+----+--------+2rowsinset(0.02sec)

以下是mycat.log日志，可以看到是从mysql2(192.168.211.139)读取的数据

2017-12-1501:47:23.280DEBUG[$_NIOREACTOR-0-RW](io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341))-releaseconnectionMySQLConnection[id=22,lastTime=1513320443251,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=true,threadId=33,charset=utf8,txIsolation=3,autocommit=true,attachment=dn1{SELECT*FROMt3LIMIT100},respHandler=SingleNodeHandler[node=dn1{SELECT*FROMt3LIMIT100},packetId=6],host=192.168.211.139,port=3306,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]2017-12-1501:54:37.777DEBUG[$_NIOREACTOR-0-RW](io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341))-releaseconnectionMySQLConnection[id=26,lastTime=1513320877751,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=true,threadId=31,charset=utf8,txIsolation=3,autocommit=true,attachment=dn1{SELECT*FROMt3LIMIT100},respHandler=SingleNodeHandler[node=dn1{SELECT*FROMt3LIMIT100},packetId=6],host=192.168.211.139,port=3306,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]

mysql>select*fromt1;+----+------+-----+------+|id|name|bu|city|+----+------+-----+------+|3|am3|net|sz||1|am1|sy|bj||2|am2|cs|gz|+----+------+-----+------+3rowsinset(0.04sec)

日志

2017-12-1502:02:35.818DEBUG[$_NIOREACTOR-0-RW](io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:448))-conneedsyn,totalsyncmd1commandsSETnamesutf8;schemachange:falsecon:MySQLConnection[id=29,lastTime=1513321355817,user=root,schema=db1,oldshema=db1,borrowed=true,fromSlaveDB=true,threadId=35,charset=utf8,txIsolation=3,autocommit=true,attachment=dn1{SELECT*FROMt1LIMIT100},respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@11eaaf37,host=192.168.211.139,port=3306,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]2017-12-1502:02:35.819DEBUG[$_NIOREACTOR-0-RW](io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:448))-conneedsyn,totalsyncmd1commandsSETnamesutf8;schemachange:falsecon:MySQLConnection[id=20,lastTime=1513321355819,user=root,schema=db2,oldshema=db2,borrowed=true,fromSlaveDB=false,threadId=65,charset=utf8,txIsolation=3,autocommit=true,attachment=dn2{SELECT*FROMt1LIMIT100},respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@11eaaf37,host=192.168.211.142,port=3306,statusSync=null,writeQueue=0,modifiedSQLExecuted=false]

多执行几次检索存在dn1的数据，查看日志，可以确认，数据都是从mysql2(192.168.211.139)读取。实现了读写分离。

8.读写两种不同写法的测试

前面有提到读写分离有两种写法，

<dataHostname="mysql1"maxCon="1000"minCon="10"balance="3"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="hostM1"url="192.168.211.138:3306"user="root"password="Alex2010@"><!--canhavemultireadhosts--><readHosthost="hostS2"url="192.168.211.139:3306"user="root"password="Alex2010@"/></writeHost></dataHost>

读写分离这里有两种写法：

除了上面的还可以写下面这样：

<dataHostname="mysql1"maxCon="1000"minCon="10"balance="3"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="hostM1"url="192.168.211.138:3306"user="root"password="Alex2010@"></writeHost><writeHosthost="hostM1"url="192.168.211.139:3306"user="root"password="Alex2010@"></writeHost></dataHost>

区别是上面的那种写法，如果主服务器挂了，从服务器不能读了。

下面的这种写法，主服务器挂了，从服务器依然可以正常读取。

现在服务器的写法是第一种，主服务器挂了，从服务器不能读了。执行看看

[root@mysql1~]#systemctlstopmysqld.service[root@mysql1~]#systemctlstatusmysqld.service●mysqld.service-MySQLServerLoaded:loaded(/usr/lib/systemd/system/mysqld.service;enabled;vendorpreset:disabled)Active:inactive(dead)sinceFri2017-12-1502:16:43EST;35sagoDocs:man:mysqld(8)

[root@mysql1~]#mysql-uroot-p123456-P8066-h227.0.0.1mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis2Serverversion:5.6.29-mycat-1.6-RELEASE-20161028204710MyCatServer(OpenCloundDB)Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>select*fromt1;ERROR3000(HY000):NoMyCATDatabaseselectedmysql>select*fromt1;ERROR3000(HY000):NoMyCATDatabaseselected

确实无法读取

测试第二种

修改schema.xml配置文件

重新启动mysql和mycat 保证环境正常

正常使用环境，我们现在关闭mysql1就是主节点

[root@mysql1conf]#systemctlstopmysqld.service[root@mysql1conf]#systemctlstatusmysqld.service●mysqld.service-MySQLServerLoaded:loaded(/usr/lib/systemd/system/mysqld.service;enabled;vendorpreset:disabled)Active:inactive(dead)sinceFri2017-12-1502:23:55EST;23sagoDocs:man:mysqld(8)

检索数据看看

[root@mysql1~]#mysql-uroot-p123456-P8066-h227.0.0.1mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.6.29-mycat-1.6-RELEASE-20161028204710MyCatServer(OpenCloundDB)Copyright(c)2000,2017,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>usehello;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*fromt1;+----+------+-----+------+|id|name|bu|city|+----+------+-----+------+|3|am3|net|sz||1|am1|sy|bj||2|am2|cs|gz|+----+------+-----+------+3rowsinset(0.46sec)

果然如此，孰优孰劣无须说明了吧。

后记：通过环境的搭建和简单的测试，达到了设计的预期目标。