MySQL distributed cluster MyCAT (1) brief introduction-Mysql Tutorial-php.cn

It took me a long time to update my blog. Recently, I changed my database from Oracle to MySQL. After studying it for a while, I feel that the community version of MySQL is inferior to Oracle in all aspects. Oracle is really convenient. !
Okay, no nonsense, this time I am going to record something about the construction of MySQL distributed cluster, MyCAT, I understand it as a MySQL agent.
-------------------------------------------------- ------------------Important TIPs----------------------- ------------------------------------------------
MyCAT team has released version 1.4Alpha, which has fixed many bugs and added new features.
The blogger used version 1.3 for testing, so the test with the latest version The results may be inconsistent!

----------------------------------------- --------------------------Background introduction------------- -------------------------------------------------- ---

The background introduction of MyCAT is skipped directly, it is useless. Of course, this is something developed by JAVA, which needs to be understood~.

-------------------------------------------------- --------------------------MyCAT's predecessor------------------------ --------------------------------------------------
The predecessor of MyCAT is Alibaba officially open sourced the database middleware Cobar on June 19, 2012. Cobar’s The predecessor is Amoeba, which has long been open source. However, after its author Chen Siru left to go to Shanda, Alibaba internally took into account Amoeba's stability, performance and functional support, as well as other factors, re-established a project team and replaced The name is Cobar. Cobar is made by Alibaba's open source MySQL distributed processing middleware can provide massive data services in a distributed environment just like a traditional database.

Cobar has been sought after by programmers since its birth, but since 2013, there have been almost no subsequent updates. Under this situation, MyCAT emerged as the times require. It was developed based on Alibaba’s open source Cobar product. Cobar’s stability, reliability, excellent architecture and performance, as well as many mature use cases made MyCAT a good starting point. From the starting point, standing on the shoulders of giants, MyCAT can see further.

-------------------------------- ----------------------------Important features of MyCAT-------------- -------------------------------------------------- ---
Supports SQL 92 standard;
Supports MySQL cluster and can be used as a Proxy;
Supports JDBC connection to ORACLE and DB2 , SQL Server, simulate it as MySQL Server;
supports galera for mysql cluster, percona-cluster or mariadb cluster, providing high availability data sharding cluster;
Automatic failover, high availability;
Supports read-write separation, supports MySQL dual master and multiple slaves, and one master and multiple slaves mode;
Supports global tables, Data is automatically sharded to multiple nodes for efficient table correlation queries;
Supports a unique sharding strategy based on E-R relationships, realizing efficient table correlation queries;
Multi-platform support, simple deployment and implementation.

----------------------------------------- -----------------------MyCAT architecture------------------ --------------------------------------------------

It is generally divided into three parts. The front end is the connector. Thread management uses resource pools and uses AIO by default (these basic information can be seen in the startup log) ; Feel the existence of
SQL Executor, it feels more like a SQL Process things, DataNode and heartbeat detection are two components implemented by the middle layer, one is related to the MySQL library (note, not the instance), and the other is a common monitoring mechanism Function module; The lowest level storage is the cluster of MySQ
L~ How to use the cluster of MySQL is up to us to decide╰(?? ▽ ??)╯.

-------------------------------------------------- ------------------How to use MyCAT----------------------- ----------------------------------
MyCAT is currently configured through the configuration file The method to define the logical library and related configurations mainly includes three files:
MYCAT_HOME/conf/schema.xml defines the logical library, tables, shard nodes, etc.;
Define fragmentation rules in MYCAT_HOME/conf/rule.xml;
Define user and system-related variables, such as ports, etc., in MYCAT_HOME/conf/server.xml .

Don’t worry, this article briefly introduces the functions of these configuration files and the meaning of some parameters.
Let’s take a look at schema.xml first. This is a sample template extracted from the Internet. ##

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <schema name="weixin" checkSQLschema="false" sqlMaxLimit="100" dataNode="weixin" >
        
    
    <schema name="yixin" checkSQLschema="false" sqlMaxLimit="100" dataNode="yixin" />
        
    
    <dataNode name="dn1" dataHost="localhost0" database="weixin" />
    <dataNode name="dn2" dataHost="localhost0" database="yixin" />
    <dataHost name="localhost0" maxCon="450" minCon="10" balance="1"
       writeType="0" dbType="mysql" dbDriver="native">
       <heartbeat>select user()</heartbeat>
       <!-- can have multi write hosts -->
       <writeHost host="hostM1" url="localhost:3306" user="root" password="123456" />
           <readHost host="hostS1" url="localhost:3307" user="test" password="123456" />
    </dataHost>
</mycat:schema>

Copy after login

The first is schema name = "weixin

", this The effect after configuring this item is that when the MySQL client connects to MyCAT, through the Show DATABASE command, you can see the name of the database, . For example, in this configuration file, two are configured Databases, weixin and yixin, each contain a user table.
Note: The database displayed by MyCAT on the external end and the tables in the database are all configured in the schema. There are no tables or libraries written in this, even if they exist in the back-end MySQL, they cannot Access through MyCAT, but MyCAT will not define the structure of the specific table.
Then comes the datanode. This attribute specifies the schema table and which database it is stored in. For example, in this configuration, the data node of dn1 is specified to be located at localhost0, and the name of this database instance is weixi## The database of
#n is the same as dn2. datahost lists the specific information of the actual back-end MySQL cluster. writehost is the MySQL instance responsible for writing data, and writehost is the MySQL instance responsible for reading. If the specific information of the two instances is written the same , which means that the backend uses a single instance. If configured as different instances, then configure master-slave synchronization between the two instances, and then implement read-write separation through MyCAT
to the database Vertical segmentation is mainly done by schema.xml, which will be introduced in detail later.
##rule.xml as example

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/"> 
 <tableRule name="rule">
    <rule>
      <columns>user_id</columns>
      <algorithm>func1</algorithm>
    </rule>
 </tableRule>
 <function name="func1" class="org.opencloudb.route.function.PartitionByLong">
    <property name="partitionCount">2</property>
    <property name="partitionLength">512</property>
 </function>
</mycat:rule>

Copy after login

Other segmentation strategies will be introduced in detail later

server.xml example

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
    <system>
        <property name="sequnceHandlerType">0</property> 
    </system>
    <user name="test">
       <property name="password">test</property>
       <property name="schemas">weixin，yixin</property>
    </user>
</mycat:server>

Copy after login

server.xml里面配置MyCAT的逻辑库参数，如示例，配置的就是逻辑库weixin和yixin的登录用户名和密码
这个XML里面其实还有一些有关于MyCAT性能调整的参数，不过略去了，东西太多，以后再详细介绍

----------------------------------------------------------------------华丽的分割线-------------------------------------------------------------

简单的MyCAT搭建大致上就包括这些内容，现在讲讲使用一段时间以后，对MyCAT的一些总结；
1.MyCAT的性能表现还是不错的，这几天一直对MyCAT的各方面进行测试，发现MyCAT作为一个代理，虽然是在JAVA虚拟机上面运行，但是面对接近9K的QPS的峰值的时候，本身并没有出现无响应或者丢失连接的问题；
2.MyCAT对前端显示的所有的库，表，全部由schema来配置，但是本身不定义表结构，这使得后端的表结构如果出现不一致，MyCAT前端是察觉不到的，不太方便吧；
3.第二点的不方便，也反映了一点，没有配置到schema的表，完全无法通过MyCAT去操作，这也算是安全性良好的一个表现吧；
4.之前说SQL Executor没感觉到，也是因为在一些测试中，发现MyCAT更像一个提供转发和结果合并功能的代理，只是对SQL和结果进行了process，不过这个需要去看源代码才知晓细节了。

The above is the detailed content of MySQL distributed cluster MyCAT (1) brief introduction. For more information, please follow other related articles on the PHP Chinese website!