`
sillycat
  • 浏览: 2543467 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

MySQL HA Solution 2019(3)MyCat

 
阅读更多
MySQL HA Solution 2019(3)MyCat

I already have a MySQL master running on ubuntu-master.
Slave running on ubuntu-dev5 and ubuntu-dev6.

Find a stable release version from here for MyCat https://github.com/MyCATApache/Mycat-download/tree/master/1.6-RELEASE

>wget https://raw.githubusercontent.com/MyCATApache/Mycat-download/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

Unzip and place the file in the working directory
> sudo ln -s /home/carl/tool/mycat-1.6 /opt/mycat-1.6
> sudo ln -s /opt/mycat-1.6 /opt/mycat

Some commands
> bin/mycat console

> bin/mycat start

> bin/mycat stop

> bin/mycat status
Mycat-server is not running.

Set Up User in master
> mysql -u debian-sys-maint -pG1FEbrOMSORmcaUK
> create user 'mycat'@'192.168.56.%' identified by 'mycat';
> grant all privileges on *.* to 'mycat'@'192.168.56.%' with grant option;
> flush privileges;

After that I can use this user to logon
> mysql -u mycat -pmycat -h ubuntu-master

Check the configurations
> vi conf/schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="ubuntu-master:3306" user="mycat"
  password="mycat">
</writeHost>
</dataHost>
</mycat:schema>

> cat conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit      1开启   0关闭
-->
<property name="useOffHeapForMerge">1</property>
<!--
单位为m
-->
<property name="memoryPageSize">1m</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换  -->
<property name="useZKSwitch">true</property>
</system>
<user name="root">
<property name="password">123456</property>
<property name="schemas">mycat</property>
</user>
<user name="mycat">
<property name="password">mycat</property>
<property name="schemas">mycat</property>
</user>
</mycat:server>

Test the connection
> mysql -umycat -pmycat -P8066 -h127.0.0.1

> show databases;
+----------+
| DATABASE |
+----------+
| mycat    |
+----------+

Stuck in command
> use mycat;

Let’s try to change the configuration
> bin/mycat status
Mycat-server is running (15583).

> bin/mycat stop
Stopping Mycat-server...
Stopped Mycat-server.

> cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostMaster" url="ubuntu-master:3306" user="mycat" password="mycat">
<readHost host="hostSlave1" url="ubuntu-dev5:3306" user="mycat" password="mycat"/>
<readHost host="hostSlave2" url="ubuntu-dev6:3306" user="mycat" password="mycat"/>
</writeHost>
</dataHost>
</mycat:schema>

More configuration Sample
https://segmentfault.com/a/1190000010208682#articleHeader0

Useful testing
https://www.jianshu.com/p/cb7ec06dae05

Try other version from here
https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE

> wget https://raw.githubusercontent.com/MyCATApache/Mycat-download/master/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz
> sudo ln -s /home/carl/tool/mycat-1.5.1 /opt/mycat-1.5.1
> sudo ln -s /opt/mycat-1.5.1 /opt/mycat

Create table there
> use mycat;
> create table mycatuser(id int auto_increment primary key,name varchar(32)) ;
> desc mycatuser;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Still not working well.
> cat conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="mutiNodeLimitType">1</property> 0:开启小数量级(默认) ;1:开启亿级数据排序
    <property name="mutiNodePatchSize">100</property> 亿级数量排序批量
<property name="processors">32</property> <property name="processorExecutor">32</property>
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
</system>
<user name="root">
<property name="password">root</property>
<property name="schemas">mycat</property>
</user>
<user name="mycat">
<property name="password">mycat</property>
<property name="schemas">mycat</property>
</user>
</mycat:server>

> cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<schema name="mycat" checkSQLschema="false" sqlMaxLimit="100">
<table name="mycatuser" dataNode="dn1"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="ubuntu-master:3306" user="mycat"
password="mycat">
<readHost host="hostS1" url="ubuntu-dev5:3306" user="mycat" password="mycat" />
<readHost host="hostS2" url="ubuntu-dev6:3306" user="mycat" password="mycat" />
</writeHost>
</dataHost>
</mycat:schema>

Newer version from here
> wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz
> sudo ln -s /home/carl/tool/mycat-1.6.7.1 /opt/mycat-1.6.7
> sudo ln -s /opt/mycat-1.6.7 /opt/mycat

Still not working as my expect.

References:
MyCat
https://blog.csdn.net/linuxlsq/article/details/52606225
https://blog.csdn.net/nxw_tsp/article/details/56277430
https://github.com/MyCATApache/Mycat-Server
https://blog.csdn.net/wangjun5159/article/details/51568249
https://segmentfault.com/a/1190000010208682
https://cloud.tencent.com/developer/article/1073522
https://github.com/MyCATApache/Mycat-Server/wiki/2.0-Mycat%E5%AE%89%E8%A3%85%E4%B8%8E%E4%BD%BF%E7%94%A8
https://www.jianshu.com/p/cb7ec06dae05

DBProxy
https://www.cnblogs.com/zhoujinyi/p/6697141.html
分享到:
评论

相关推荐

    mysql与mycat的读写分离

    ### MySQL与Mycat的读写分离 #### 一、MySQL主从配置 在了解如何实现MySQL与Mycat的读写分离之前,首先要掌握如何配置MySQL的主从复制。这是实现读写分离的基础。 ##### 开启MySQL主从数据复制 主从数据复制通过...

    mysql8.0.16Linuxl离线安装版+mycat2安装文件

    接着,配置 Mycat,将 MySQL 作为后端数据源,根据 `mycat2-install-template-1.21.zip` 中的模板修改 Mycat 的配置文件。最后,启动 Mycat 服务,就可以利用其强大的分布式数据库功能了。 总结来说,这个压缩包...

    mysql+mycat+keepalived+haproxy集群(生产环境实测).doc

    总结来说,这个集群环境通过MySQL提供数据库服务,Mycat进行数据分片和负载均衡,Keepalived确保主从切换的高可用性,而HAProxy则进一步增强了对外服务的负载均衡能力。这样的组合可以有效提升系统的稳定性和处理...

    MySQL分布式数据库中间件Mycat性能调优指南

    MySQL分布式数据库中间件Mycat是一款广泛应用于大数据处理和高并发场景的重要工具,它通过将数据分布到多个物理节点上,实现了数据的水平扩展。在实际应用中,Mycat的性能调优对于系统的整体效率至关重要。本指南将...

    Linux下安装mysql-5.6.14以及安装MyCat.docx

    Linux 下安装 MySQL 5.6.14 及 MyCat 本文将详细介绍如何在 Linux 系统下安装 MySQL 5.6.14 及 MyCat,从安装 MySQL 的准备工作到安装 MyCat,以及配置 MyCat 的规则和启动 MyCat 服务。 一、安装 MySQL 5.6.14 1...

    proxy与mycat对比测试

    本篇文章将深入探讨proxy和Mycat的对比测试,以及如何在MySQL主从架构中进行测试。 首先,我们来看proxy。Proxy通常指的是数据库代理服务器,它位于应用服务器和数据库服务器之间,起到路由请求、负载均衡和透明化...

    MySQL 中间件 MyCat 1.6.5 WIN 版 免安装

    MySQL中间件MyCat是一款在大数据环境下广泛使用的数据库管理和分发工具,特别针对MySQL数据库系统设计,旨在实现数据库的读写分离和水平扩展。MyCat 1.6.5是其一个稳定版本,专为Windows环境提供了免安装的便捷性。...

    windows下mysql主备双向复制与mycat负载均衡

    3. Windows系统下MySQL与Mycat的环境搭建: 环境搭建首先需要准备合适的MySQL版本,这里以mysql-5.6.27为例。解压下载好的mysql-5.6.27-winx64.zip文件,并进行安装配置。为区分主备服务器,需要在MySQL的配置文件my...

    Mysql+haproxy+mycat+pxc+zookeeper实现高可用集群

    Mysql+haproxy+mycat+pxc+zookeeper实现高可用集群 本文将详细介绍如何使用Mysql、Haproxy、Mycat、PXC、Zookeeper实现高可用集群,涵盖了整个架构图、应用程序的访问流程、数据的读写分离、负载均衡、数据库节点的...

    MySQL主从备份+Mycat读写分离学习笔记

    MySQL主从备份与Mycat读写分离是数据库管理和优化中的重要技术,它们在大型分布式系统中发挥着关键作用,确保数据的安全性、可用性和性能。以下是对这两个主题的详细阐述。 **一、MySQL主从备份** MySQL主从备份是...

    mysql分布式数据库mycat方案

    MySQL分布式数据库MyCAT方案 MySQL分布式数据库MyCAT方案是基于 MySQL 数据库管理系统和 MyCAT 分布式数据库中间件的实践方案。该方案的主要目的是为了解决传统 MySQL 数据库的单点故障和性能瓶颈问题,提高数据库...

    mycat1.7支持mysql8的jar包

    目前mycat并不支持mysql8的连接,附件是经过修改源码,使其支持mysql8的连接;支持版本为mycat1.7. 将这个jar替换到mycat/lib中的Mycat-server-1.7.0-DEV.jar

    MySql HA.docx

    ### MySQL 高可用性 (HA) 技术详解 #### 一、MySQL 主从复制 **主从复制介绍** MySQL 的主从复制机制是其实现高可用性和数据冗余的关键特性之一。通过这一机制,可以在多台服务器之间同步数据,确保数据的一致性...

    mysql+mycat视频教程

    自己从淘宝上花5元购买的MySql集群搭建,读写分离,使用MyCat工具。如果能够帮助到你,麻烦给个好评谢谢!

    基于数据库中间件Mycat的MySQL主从与读写分离配置详解与示例

    3. 设置路由规则:在Mycat中设定读写分离策略,如所有写操作都指向主库,读操作则随机或轮询选择从库。 4. 启动与测试:启动Mycat服务,通过简单的SQL测试验证读写分离功能是否正常工作,同时监控主从状态以确保...

    mycat作为中间件mysql的读写分离(单个mycat服务).md

    傻瓜式安装,用mycat作为中间键键mysql数据做读写分离的操作,单个mycat,解决问题mycat启动报Unable to start JVM: No such file or directory (2)

    MySQL分布式集群之MyCAT权威指南

    Cobar是由 Alibaba 开源的 MySQL 分布式处理中间件,它可以在分布式的环境下看上去像传统数据库一样提供海量数据服务。 Cobar自诞生之日起, 就受到广大程序员的追捧,但是自2013年后,几乎没有后续更新。在此情况下...

    keepalived+haproxy+mycat配置文件.rar

    3. **Mycat**: Mycat 是一个开源的分布式数据库中间件,主要用于解决大数据量下的数据库扩展问题。在 MySQL 双主读写分离的场景中,Mycat 作为数据库分片器,可以将读写请求智能地路由到不同的 MySQL 主从节点。主...

    mysql 读写分离配置 及 Mycat安装使用

    ### MySQL 读写分离配置及 Mycat 安装使用 #### 一、读写分离原理与服务器架构 **1.1 读写分离原理** 读写分离是一种提高数据库系统性能和扩展性的方法,通过将数据库的读操作和写操作分离到不同的服务器上,从而...

Global site tag (gtag.js) - Google Analytics