符合中小企业对网站设计、功能常规化式的企业展示型网站建设
本套餐主要针对企业品牌型网站、中高端设计、前端互动体验...
商城网站建设因基本功能的需求不同费用上面也有很大的差别...
手机微信网站开发、微信官网、微信商城网站...
这篇文章给大家分享的是有关MyCat如何实现MySQL双主一从读写分离的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
创新互联为企业提供:品牌网站制作、网络营销策划、小程序开发、营销型网站建设和网站运营托管,一站式网络营销整体服务。实现不断获取潜在客户之核心目标,建立了企业专属的“全网整合营销推广”,就用不着再为了获取潜在客户而苦恼,相反,客户会主动找您,生意就找上门来了!1)配置server.xml
mysql TESTDB
配置用户root,密码为mysql,对TESTDB具有权限
2)配置schema.xml
select user()
schema标签:schema name属性指定逻辑库名,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@oneproxy logs]# tail -f wrapper.log STATUS | wrapper | 2018/08/25 10:32:39 | --> Wrapper Started as Daemon STATUS | wrapper | 2018/08/25 10:32:40 | Launching a JVM... INFO | jvm 1 | 2018/08/25 10:32:40 | Wrapper (Version 3.2.3) /tupian/20230522/home.html INFO | jvm 1 | 2018/08/25 10:32:40 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. INFO | jvm 1 | 2018/08/25 10:32:40 | INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN No appenders could be found for logger (io.mycat.memory.MyCatMemory). INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN Please initialize the log4j system properly. INFO | jvm 1 | 2018/08/25 10:32:41 | log4j:WARN See /tupian/20230522/faq.html INFO | jvm 1 | 2018/08/25 10:32:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
4)在mycat服务器登录mysql
mysql -uroot -pmysql -P8066 -h292.168.8.63
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
5)测试读写分离
mysql> use TESTDB; mysql> create table t4(hostname1 varchar(12),hostname2 varchar(12),hostname3 varchar(12)); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname); mysql> mysql> insert into t4 values(@@hostname,@@hostname,@@hostname);
mysql> select * from t4; +-----------+-----------+-----------+ | 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 | +-----------+-----------+-----------+ 64 rows in set (0.00 sec)
插入的数据都是从master1插入的,通过查询mycat日志,发现所有的查询全部分发到了slave节点。
感谢各位的阅读!关于“MyCat如何实现MySQL双主一从读写分离”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!