这篇文章给大家分享的是有关MyCat如何实现MySQL双主一从读写分离的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

1）配置server.xml

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

配置用户root，密码为mysql，对TESTDB具有权限

2）配置schema.xml

<schemaname="TESTDB"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1"></schema><!--<dataNodename="dn1$0-743"dataHost="localhost1"database="db$0-743"/>--><dataNodename="dn1"dataHost="localhost1"database="test"/><dataNodename="dn2"dataHost="localhost1"database="dedecms"/><dataNodename="dn3"dataHost="localhost1"database="xcredit"/><!--<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="master1"url="192.168.8.57:3306"user="root"password="mysql"><!--canhavemultireadhosts--><readHosthost="slave"url="192.168.8.59:3306"user="root"password="mysql"/></writeHost><writeHosthost="master2"url="192.168.8.58:3306"user="root"password="mysql"/>

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

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

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

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

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

2. balance="1"，全部的readHost与stand by writeHost参与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%'。

3）启动mycat，并观察日志

/usr/local/mycat/bin/mycat start

[root@oneproxylogs]#tail-fwrapper.logSTATUS|wrapper|2018/08/2510:32:39|-->WrapperStartedasDaemonSTATUS|wrapper|2018/08/2510:32:40|LaunchingaJVM...INFO|jvm1|2018/08/2510:32:40|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/08/2510:32:40|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/08/2510:32:40|INFO|jvm1|2018/08/2510:32:41|log4j:WARNNoappenderscouldbefoundforlogger(io.mycat.memory.MyCatMemory).INFO|jvm1|2018/08/2510:32:41|log4j:WARNPleaseinitializethelog4jsystemproperly.INFO|jvm1|2018/08/2510:32:41|log4j:WARNSeehttp://logging.apache.org/log4j/1.2/faq.html#noconfigformoreinfo.INFO|jvm1|2018/08/2510:32:41|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

4）在mycat服务器登录mysql

mysql -uroot -pmysql -P8066 -h292.168.8.63

mysql> show databases;

+----------+

| DATABASE |

+----------+

| TESTDB |

+----------+

5）测试读写分离

mysql>useTESTDB;mysql>createtablet4(hostname1varchar(12),hostname2varchar(12),hostname3varchar(12));mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);mysql>mysql>insertintot4values(@@hostname,@@hostname,@@hostname);

mysql>select*fromt4;+-----------+-----------+-----------+|hostname1|hostname2|hostname3|+-----------+-----------+-----------+|master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1||master1|master1|master1|+-----------+-----------+-----------+64rowsinset(0.00sec)

插入的数据都是从master1插入的，通过查询mycat日志，发现所有的查询全部分发到了slave节点。

感谢各位的阅读！关于“MyCat如何实现MySQL双主一从读写分离”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！