免费资源部落

这篇文章主要介绍“MyCat怎么实现MySQL一主两从读写分离”，在日常操作中，相信很多人在MyCat怎么实现MySQL一主两从读写分离问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MyCat怎么实现MySQL一主两从读写分离”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

环境搭建（一主两从）：

macat：192.168.8.30

master：192.168.8.31

slave1：192.168.8.32

slave2：192.168.8.33

工具包：

java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64

Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

一、安装JDK和MyCat

系统已安装JDK，查看安装路径

[root@mycat~]#java-versionopenjdkversion"1.8.0_161"OpenJDKRuntimeEnvironment(build1.8.0_161-b14)OpenJDK64-BitServerVM(build25.161-b14,mixedmode)[root@mycat~]#ls-l/usr/bin/javalrwxrwxrwx.1rootroot22Oct2210:30/usr/bin/java->/etc/alternatives/java[root@mycat~]#ls-l/etc/alternatives/javalrwxrwxrwx.1rootroot71Oct2210:30/etc/alternatives/java->/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jre/bin/java

安装MyCat

cd/softwaretarzxvfMycat-server-1.6.6.1-release-20181031195535-linux.tar.gzcp-rmycat/usr/local/

配置环境变量

[root@mycat~]#cat~/.bash_profile#.bash_profile#Getthealiasesandfunctionsif[-f~/.bashrc];then.~/.bashrcfi#UserspecificenvironmentandstartupprogramsexportJAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64exportJRE_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jreexportMYCAT_HOME=/usr/local/mycatPATH=$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin:/usr/bin:/sbin:/bin:$MYCAT_HOME/bin:$JAVA_HOME/binexportPATH

二、创建测试库

master创建，slave同步

createdatabasetestdb1;createtabletestdb1.t11(name1varchar(40),name2varchar(40),name3varchar(40));createdatabasetestdb2;createtabletestdb2.t21(name1varchar(40),name2varchar(40),name3varchar(40));createdatabasetestdb3;createtabletestdb3.t31(name1varchar(40),name2varchar(40),name3varchar(40));

三、配置schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="mycatdb1"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn1"></schema><schemaname="mycatdb2"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn2"></schema><schemaname="mycatdb3"checkSQLschema="true"sqlMaxLimit="100"dataNode="dn3"></schema><!--<dataNodename="dn1$0-743"dataHost="localhost1"database="db$0-743"/>--><dataNodename="dn1"dataHost="localhost1"database="testdb1"/><dataNodename="dn2"dataHost="localhost1"database="testdb2"/><dataNodename="dn3"dataHost="localhost1"database="testdb3"/><!--<dataNodename="dn4"dataHost="sequoiadb1"database="SAMPLE"/><dataNodename="jdbc_dn1"dataHost="jdbchost"database="db1"/><dataNodename="jdbc_dn2"dataHost="jdbchost"database="db2"/><dataNodename="jdbc_dn3"dataHost="jdbchost"database="db3"/>--><dataHostname="localhost1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><writeHosthost="192.168.8.31"url="192.168.8.31:3306"user="root"password="mysql"><!--canhavemultireadhosts--><readHosthost="192.168.8.32"url="192.168.8.32:3306"user="root"password="mysql"/><readHosthost="192.168.8.33"url="192.168.8.33:3306"user="root"password="mysql"/></writeHost><writeHosthost="192.168.8.32"url="192.168.8.32:3306"user="root"password="mysql"/><!--<writeHosthost="hostM2"url="localhost:3316"user="root"password="123456"/>--></dataHost><!--<dataHostname="sequoiadb1"maxCon="1000"minCon="1"balance="0"dbType="sequoiadb"dbDriver="jdbc"><heartbeat></heartbeat><writeHosthost="hostM1"url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE"user="jifeng"password="jifeng"></writeHost></dataHost><dataHostname="oracle1"maxCon="1000"minCon="1"balance="0"writeType="0"dbType="oracle"dbDriver="jdbc"><heartbeat>select1fromdual</heartbeat><connectionInitSql>altersessionsetnls_date_format='yyyy-mm-ddhh34:mi:ss'</connectionInitSql><writeHosthost="hostM1"url="jdbc:oracle:thin:@127.0.0.1:1521:nange"user="base"password="123456"></writeHost></dataHost><dataHostname="jdbchost"maxCon="1000"minCon="1"balance="0"writeType="0"dbType="mongodb"dbDriver="jdbc"><heartbeat>selectuser()</heartbeat><writeHosthost="hostM"url="mongodb://192.168.0.99/test"user="admin"password="123456"></writeHost></dataHost><dataHostname="sparksql"maxCon="1000"minCon="1"balance="0"dbType="spark"dbDriver="jdbc"><heartbeat></heartbeat><writeHosthost="hostM1"url="jdbc:hive2://feng01:10000"user="jifeng"password="jifeng"></writeHost></dataHost>--><!--<dataHostname="jdbchost"maxCon="1000"minCon="10"balance="0"dbType="mysql"dbDriver="jdbc"><heartbeat>selectuser()</heartbeat><writeHosthost="hostM1"url="jdbc:mysql://localhost:3306"user="root"password="123456"></writeHost></dataHost>--></mycat:schema>

schema标签：schema name属性指定逻辑库名，dataNode属性指定下边的dataNode

其中 checkSQLschema 表明是否检查并过滤 SQL 中包含schema的情况，如逻辑库为mycatdb1，则可能写为 select * from mycatdb1.t11，此时会自动过滤 mycatdb1，SQL变为select * from t11，若不会出现上述写法，则可以关闭属性为false。

sqlMaxLimit默认返回的最大记录数限制，MyCat1.4 版本里面，用户的Limit参数会覆盖掉MyCat的sqlMaxLimit默认设置

dataNode标签：dataHost指定下边的dataHost，database指定具体的database

dataHost标签：balance指的负载均衡类型，switchType指的是切换的模式

schema中的每一个dataHost中的host属性值必须唯一，否则会出现主从在所有dataHost中全部切换的现象

其中，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 不负担读压力

因此，该配置文件中的 balance="1"意味着作为stand by writeHost的hostS1和hostS2将参与select语句的负载均衡，这就实现了主从的读写分离

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

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

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

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

MyCat1.4 开始支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

MyCat心跳检查语句配置为 show slave status，dataHost上定义两个新属性：switchType="2"与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制，MyCat 心跳机制通过检测show slave status 中的"Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running"

三个字段来确定当前主从同步的状态以及 Seconds_Behind_Master 主从复制时延，当 Seconds_Behind_Master 大于 slaveThreshold 时，读写分离筛选器会过滤掉此Slave机器，防止读到很久之前的旧数据，而当主节点宕机后，切换逻辑会检查Slave上的Seconds_Behind_Master 是否为 0，为 0 时则表示主从同步，可以安全切换，否则不会切换。

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

四、配置server.xml

<username="root"defaultAccount="true"><propertyname="password">mysql</property><propertyname="schemas">mycatdb1,mycatdb2,mycatdb3</property><!--表级DML权限设置--><!--<privilegescheck="false"><schemaname="TESTDB"dml="0110"><tablename="tb01"dml="0000"></table><tablename="tb02"dml="1111"></table></schema></privileges>--></user><username="user"><propertyname="password">user</property><propertyname="schemas">mycatdb1,mycatdb2,mycatdb3</property><propertyname="readOnly">true</property></user>

五、修改MyCat日志级别

vi log4j2.xml

修改<asyncRoot level="info" includeLocation="true">

为 <asyncRoot level="debug" includeLocation="true">

六、启动mycat

[root@mycatconf]#/usr/local/mycat/bin/mycatstartStartingMycat-server...

查看日志

STATUS|wrapper|2018/11/2111:08:50|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2111:08:51|LaunchingaJVM...INFO|jvm1|2018/11/2111:08:51|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2111:08:54|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2111:08:54|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2111:08:54|INFO|jvm1|2018/11/2111:08:58|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

查看端口

[root@mycatconf]#netstat-an|grep8066tcp600:::8066

查看mycat进程

[root@mycatconf]#ps-ef|grepmycatavahi6461009:56?00:00:03avahi-daemon:running[mycat.local]root26531011:08?00:00:00/usr/local/mycat/bin/./wrapper-linux-x86-64/usr/local/mycat/conf/wrapper.confwrapper.syslog.ident=mycatwrapper.pidfile=/usr/local/mycat/logs/mycat.pidwrapper.daemonize=TRUEwrapper.lockfile=/var/lock/subsys/mycaroot26552653711:08?00:00:07java-DMYCAT_HOME=.-server-XX:MaxPermSize=64M-XX:+AggressiveOpts-XX:MaxDirectMemorySize=2G-Dcom.sun.management.jmxremote-Dcom.sun.management.jmxremote.port=1984-Dcom.sun.management.jmxremote.authenticate=false-Dcom.sun.management.jmxremote.ssl=false-Xmx4G-Xms1G-Djava.library.path=lib-classpathlib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/log4j-1.2-api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.6.1-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar-Dwrapper.key=G87oQ7EZv67RXK9D-Dwrapper.port=32000-Dwrapper.jvm.port.min=31000-Dwrapper.jvm.port.max=31999-Dwrapper.pid=2653-Dwrapper.version=3.2.3-Dwrapper.native_library=wrapper-Dwrapper.service=TRUE-Dwrapper.cpu.timeout=10-Dwrapper.jvmid=1org.tanukisoftware.wrapper.WrapperSimpleAppio.mycat.MycatStartupstart

七、登录mysql，查看逻辑库

[root@mycat~]#mysql-uroot-pmysql-P8066-h292.168.8.30mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.6.29-mycat-1.6.6.1-release-20181031195535MyCatServer(OpenCloudDB)Copyright(c)2009-2018PerconaLLCand/oritsaffiliatesCopyright(c)2000,2018,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>showdatabases;+----------+|DATABASE|+----------+|mycatdb1||mycatdb2||mycatdb3|+----------+3rowsinset(0.01sec)mysql>usemycatdb1;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tables_in_testdb1|+-------------------+|t11|+-------------------+1rowinset(0.04sec)mysql>select*fromt11;Emptyset(0.22sec)

八、读写分离验证

[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave1|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|+------------+[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select@@hostname"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+------------+|@@hostname|+------------+|slave2|

可以看到读操作全部在slave1和slave2

注意：这里的负载均衡并不是在slave1和slave2上边轮询，而是总体保持负载均衡。

[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb1.t11"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+--------+--------+--------+|name1|name2|name3|+--------+--------+--------+|master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master|+--------+--------+--------+

九、主从切换测试

关闭master上的MySQL进程

[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR1184(HY000)atline1:java.net.ConnectException:Connectionrefused[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.ERROR1184(HY000)atline1:java.net.ConnectException:Connectionrefused[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"insertintomycatdb1.t11values(@@hostname,@@hostname,@@hostname)"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@mycatconf]#mysql-uroot-pmysql-P8066-h292.168.8.30-e"select*frommycatdb1.t11"mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.+--------+--------+--------+|name1|name2|name3|+--------+--------+--------+|master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||master|master|master||slave1|slave1|slave1||slave1|slave1|slave1||slave1|slave1|slave1|+--------+--------+--------+

短暂的连接失败之后，写操作连进slave1，主从切换成功。

到此，关于“MyCat怎么实现MySQL一主两从读写分离”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！