Shulou(Shulou.com)06/01 Report--

It took me a long time to think of updating my blog. Recently, the database has been changed from Oracle to MySQL. After studying it for a period of time, I feel that the community version of MySQL is inferior to Oracle,Oracle in all aspects.

All right, nonsense, this time I'm going to record something about building a MySQL distributed cluster, MyCAT, which I understand as a MySQL agent.

-important TIPs -

MyCAT's team has released the 1.4Alpha version, which fixes a lot of bug and adds new features.

The blogger is testing version 1.3, so the test results may be inconsistent with the latest version!

-background introduction-- -

It is useless to skip the background introduction of MyCAT directly, of course, this is something developed by JAVA, which needs to be understood.

-the predecessor of MyCAT-- -

The predecessor of MyCAT is Alibaba on June 19, 2012. the formal open source database middleware Cobar,Cobar is already open source Amoeba, but after its author Chen Silu left to go to Shanda, Alibaba re-established a project team and changed its name to Cobar considering the stability, performance and functional support of Amoeba, as well as other factors. Cobar is a MySQL distributed processing middleware open source by Alibaba, which can provide massive data services like traditional databases in a distributed environment.

Cobar has been popular with programmers since its birth, but there have been few subsequent updates since 2013. In this case, MyCAT arises at the historic moment, it is based on Ali open source Cobar products and research and development, Cobar stability, reliability, excellent architecture and performance, as well as many mature use cases make MyCAT have a good starting point, standing on the shoulders of giants, MyCAT can see further.

-MyCAT download method

The SVN address of MyCAT is: http://code.taobao.org/svn/openclouddb/

-important features of MyCAT-

Support for SQL 92 standard

MySQL clusters are supported and can be used as Proxy

Support JDBC to connect ORACLE, DB2, SQL Server to simulate the use of MySQL Server

Support galera for mysql cluster, percona-cluster or mariadb cluster, and provide high availability data sharding cluster

Automatic failover, high availability

Support read-write separation, support MySQL dual master multi-slave mode, and one master multi-slave mode

Supports global tables and automatically splits data to multiple nodes for efficient table association queries

The unique slicing strategy based on Emurr relationship is supported, and efficient table association query is realized.

Multi-platform support, easy to deploy and implement.

-MyCAT architecture

Generally speaking, it is divided into three parts, the front end is the connector, thread management uses a resource pool, and the default is AIO (these basic information can be seen in the startup log)

The middle layer has been described very clearly in the figure. SQL parser + SQL routing, SQL Executor needs to look at the source code in order to understand, because through the test of MyCAT during this period of time, I do not feel the existence of SQL Executor, more feel is a SQL process thing, DataNode and heartbeat detection can be regarded as the two components of the middle layer, one is related to the library of MySQL (note, not an example), and the other is the functional module of the common monitoring mechanism.

The lowest level of storage is the MySQL cluster ~ how to play the MySQL cluster, it is up to us to decide ╰ (? ▽?) ╯.

-how to use MyCAT

MyCAT currently defines logical libraries and related configurations through configuration files, mainly including three files:

Define logical libraries, tables, sharding nodes, etc., in MYCAT_HOME/conf/schema.xml

Define sharding rules in MYCAT_HOME/conf/rule.xml

Users and system-related variables, such as ports, are defined in MYCAT_HOME/conf/server.xml.

Don't worry, this article briefly introduces the role of these configuration files and the significance of some parameters.

One by one, let's take a look at schema.xml. This is a sample template extracted from the Internet.

Click (here) to collapse or open

Select user ()

The first is schema name = "weixin". The effect of this configuration is that when a MySQL client connects to MyCAT, you can see the name of the database through the Show DATABASE command.

For example, in this configuration file, you configure two databases, weixin and yixin, each containing an user table.

Note: the database displayed on the external end and the tables in the database are all configured in schema by MyCAT, and the tables or libraries are not written in it. Even if there is a MySQL in the backend, it cannot be accessed through MyCAT. However, MyCAT does not define the structure of specific tables.

Then there is datanode, which specifies the table of schema and which database it is stored in. For example, in this configuration, it specifies that the data node of dn1 is located in localhost0, and the database of this database instance is named weixin. Dn2 is the same.

Datahost lists the specific information of the actual backend 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 the same, it means that the backend uses a single instance. If it is configured as a different instance, then configure master-slave synchronization between the two instances, and then achieve read-write separation through MyCAT.

Vertical segmentation of the database is mainly done by schema.xml, which will be described in more detail later.

Rule.xml as an example

Click (here) to collapse or open

User_id

Func1

two

five hundred and twelve

The configuration in rule.xml is mainly used to split the table horizontally. MyCAt itself provides many strategies for horizontal slicing. This example shows that the module is divided into four pieces, and user_id takes the module for 1024, and then divides it into two pieces, each with 512s.

Other segmentation strategies will be described in detail later.

Server.xml as an example

Click (here) to collapse or open

0

Test

Weixin,yixin

Configure the logical library parameters of MyCAT in server.xml. For example, the login username and password of logical libraries weixin and yixin are configured.

In fact, there are some parameters about MyCAT performance adjustment in this XML, but they are omitted. There are too many things. I will introduce them in detail later.

-gorgeous dividing line- -

Simple MyCAT building generally includes these contents. Now let's talk about some summary of MyCAT after using it for a period of time.

The performance of 1.MyCAT is still good. Over the past few days, we have been testing all aspects of MyCAT. It is found that MyCAT, as an agent, runs on the JAVA virtual machine, but when facing the peak of QPS near 9K, there is no problem of no response or loss of connection.

2.MyCAT configures all the libraries and tables displayed at the front end by schema, but does not define the table structure itself, which makes the MyCAT front end unaware of the inconsistency in the back end table structure, which is not very convenient.

3. The inconvenience of the second point also reflects that tables that are not configured to schema cannot be operated through MyCAT at all, which can be regarded as a sign of good security.

4. SQL Executor didn't feel it before, but also because in some tests, it was found that MyCAT was more like an agent that provided forwarding and result merging functions, only process the SQL and results, but you need to look at the source code to know the details.

I'm only going to write so much this time. Some details about MyCAT will be left for the next chapter.

Next chapter: http://blog.itpub.net/29510932/viewspace-1667814/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.