MyCAT实现读写分离,主从切换[实例精华]

Rosine ·

更新时间:2024-11-14

· 514 次阅读

mycat实现独写分离,首先需要mysql配置好主从复制

show slave status

schema.xml定义了MyCAT逻辑和物理逻辑信息如果对schema.xml配置标签不了解,请看MyCAT安装及配置说明

示例配置一主一从结构是最简单的配置:
MyCat支持双主多从，如果有N个主，那么就配置N个writeHost兄弟节点；如果有M个从节点，那么就配置M个readHost节点即可。
注:

需要注意mycat数据节点dataNode配置的database物理数据库名,要与mysql主从复制中master服务配置my.cnf定义的同步数据库一致. Mycat主从分离只是在读的时候做了处理，写入数据的时候，只会写入到writehost，需要通过mysql的主从复制将数据复制到readhost！如果mysql主从复制没有搭建好,那么readhost将会没有数据.

数据测试:

通过ip+8066端口连接mycat服务,会根据scheam.xml定义的scheam创建MyCAT逻辑库KEVINDB 在itcast01数据库手动创建kevin数据库,itcast02数据库会自动复制 MyCAT逻辑库KEVINDB创建表tb_kevin和数据会自动同步到itcast01,itcast02

在这里插入图片描述
查看MyCAT运行日志:
tail -f mycat.log

02/26 10:39:52.053   INFO [Timer1] (AbstractConnection.java:452) -close connection,reason: idle  ,ServerConnection [id=9, schema=MYCATDB, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=MYCATDB]
02/26 10:44:02.710   INFO [Timer0] (PhysicalDatasource.java:269) -create connections ,because idle connection not enough ,cur is 7, minCon is 10 for hostM1
02/26 10:44:02.717   INFO [$_NIOREACTOR-1-RW] (NewConnectionRespHandler.java:44) -connectionAcquired MySQLConnection [id=2536, lastTime=1582685042709, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=10707, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.79.131, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/26 14:51:38.732   INFO [$_NIOREACTOR-2-RW] (FrontendAuthenticator.java:164) -ServerConnection [id=10, schema=null, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=null]'root' login success
02/26 14:52:59.512   INFO [$_NIOREACTOR-3-RW] (FrontendAuthenticator.java:164) -ServerConnection [id=11, schema=null, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=null]'root' login success
02/26 14:53:07.468   INFO [$_NIOREACTOR-0-RW] (FrontendAuthenticator.java:164) -ServerConnection [id=12, schema=null, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=null]'root' login success
02/26 14:53:07.860   INFO [$_NIOREACTOR-0-RW] (AbstractConnection.java:452) -close connection,reason:quit cmd ,ServerConnection [id=12, schema=KEVINDB, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=KEVINDB]
02/26 14:53:28.498   INFO [$_NIOREACTOR-1-RW] (FrontendAuthenticator.java:164) -ServerConnection [id=13, schema=null, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=null]'root' login success
02/26 14:53:28.611   INFO [$_NIOREACTOR-1-RW] (AbstractConnection.java:452) -close connection,reason:quit cmd ,ServerConnection [id=13, schema=KEVINDB, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=KEVINDB]
02/26 14:53:32.613   INFO [$_NIOREACTOR-2-RW] (FrontendAuthenticator.java:164) -ServerConnection [id=14, schema=null, host=192.168.79.1, user=root,txIsolation=3, autocommit=true, schema=null]'root' login success
02/26 15:22:06.370   WARN [$_NIOREACTOR-2-RW] (SingleNodeHandler.java:189) -execute  sql err : errno:1054 Unknown column 'KEVINup' in 'field list' con:MySQLConnection [id=14, lastTime=1582701725697, schema=kevin, old shema=kevin, borrowed=true, fromSlaveDB=false, threadId=9306, charset=utf8, txIsolation=3, autocommit=true, attachment=sync1{update TB_KEVIN set KEVIN=KEVINup where id=5}, respHandler=SingleNodeHandler [node=sync1{update TB_KEVIN set KEVIN=KEVINup where id=5}, packetId=1], host=192.168.79.130, port=3306, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7446fdae, writeQueue=0, modifiedSQLExecuted=true]

从日志看出,select * …查询通过192.168.79.131从机执行,update …修改语句通过192.168.79.130主机执行,从而实现的mycat读写分离!!!

作者：丰丰丰的csdn

读写分离 mycat

1024 个赞