Introduction of MyCat

Mycat is a database middleware developed by open source community on the basis of Ali Cobar. Is an open source distributed database system, is a Server to implement the MySQL protocol, front-end users can regard it as a database agent, with MySQL client tools and command line access, and back-end can use MySQL Native protocol and multiple MySQL Server communication. You can also use JDBC protocol to communicate with most mainstream database servers. Its core function is to divide tables and libraries, that is, a large table is horizontally divided into N small tables and stored in the back-end MySQL server or other databases.

Mycat is no longer a simple MySQL agent. Its backend can support mainstream databases such as MySQL, SQL Server, Oracle, DB2 and PostgreSQL, as well as MongoDB storage in a new NoSQL mode. More types of storage will be supported in the future.

For DBAs, Mycat can be understood as follows: Mycat is the MySQL Server, and the MySQL Server connected to Mycat is like the storage engine of MySQL, such as InnoDB, MyISAM, etc. Therefore, Mycat does not store data, data is stored in the backend MySQL. So data reliability and transactions are guaranteed by MySQL;

For architects, Mycat can be understood as follows: Mycat is a powerful database middleware, not only can be used as a separation, speaking, reading and writing, and table depots, disaster tolerance, and can be used for multi-tenant application development, cloud infrastructure, let you of the architecture has the very strong adaptability and flexibility, with the help of the upcoming Mycat intelligent optimization module, system data access bottleneck and focus in the be clear at a glance, Based on this statistical analysis, you can automatically or manually adjust the backend storage to map different tables to different storage engines without changing a line of code for the entire application.

Mycat1.6 website

Mycat1 Authoritative Guide · Language Finch (yuque.com)

MyCat role

  1. Reading and writing separation

  1. Data fragmentation

    Vertical split (database), horizontal split (table), vertical + horizontal split (database table)

  1. Multi-data integration

Principle of MyCat

The most important verb in Mycat’s principle is “intercept”. It intercepts the SQL statement sent by the user. First, it does some specific analysis of the SQL statement: Such as fragment analysis, route analysis, read/write separation analysis, cache analysis, and so on, the SQL is sent to the real database at the back end, and the returned result is processed properly, and finally returned to the user.

MyCat installation

  1. Install the Java runtime environment JDK
$ sudo wget http://dl.mycat.org.cn/jdk-8u20-linux-x64.tar.gz $ sudo tar -zxf jdk-8u20-linux-x64.tar.gz $ sudo mv Jdk1.8.0 _20 / usr /local/jdk

Configure environment variables
~$ sudo vim /etc/profile.d/jdk.sh
JAVA_HOME=/usr/local/jdk
JRE_HOME=$JAVA_HOME/jre
CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME JRE_HOME CLASSPATH PATH

# validation
[xdl@CentOS] ~$ source /etc/profile
[xdl@CentOS] ~$ java -version
java version "1.8.0 comes with _20"
Java(TM) SE Runtime Environment (build 1.8.0_20-b26)
Java HotSpot(TM) 64-Bit Server VM (build 25.20-b23, mixed mode)
Copy the code

MyCat installation

~ $sudo wget http://dl.mycat.org.cn/1.6.7.1/Mycat-server-1.6.7.1-release-20200209222254-linux.tar.gz ~ $tar ZXF. - Mycat - server - 1.6.7.1 - release - 20200209222254 - Linux. Tar. Gz - C/usr /local
~$ ls /usr/local/mycat
bin  catlet  conf  lib  logs  version.txt

Set the Java path
~$ sudo vim /usr/local/mycat/conf/wrapper.conf
wrapper.java.command=/usr/local/jdk/bin/java

wrapper.startup.timeout=300

Resize virtual memory if startup error occurs
wrapper.java.additional.9=-Xmx2G
wrapper.java.additional.10=-Xms512M

Set environment variables
~$ sudo vim /etc/profile.d/mycat.sh
MYCAT_HOME=/usr/local/mycat

~$ source /etc/profile
Copy the code

MyCat read-write separation architecture

The MyCat backend connects to the real MySQL database instance, and separates the write data from the read data, reducing the pressure on a single MySQL instance.

Configuring Read/Write Separation

The configuration file

① Schema. XML: define logical libraries, tables, shard nodes, etc

② rule-xml: Define sharding rules

③server.xml: Define user and system related variables, such as ports, etc

MySQL > alter user mycat; MySQL > alter user mycat;

~$ sudo vim /usr/local/mycat/conf/server.xml ... p110 <user name="root" defaultAccount="true">
        <property name="password">mycatpw</property>
        <property name="schemas">testdb</property>
        <property name="defaultSchema">testdb</property>
Copy the code

Here the main setting of MyCat user name, password, logical library name and other attributes

2. Modify the configuration file schema.xml

Delete the table information between

tags, leaving only one

tag, only one

tag, and only one pair of


tags




~$ sudo vim /usr/local/mycat/conf/schema.xml <? xml version="1.0"? > <! DOCTYPE mycat:schema SYSTEM"schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">

    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"> <heartbeat>select user()</heartbeat> <! -- can have multi write hosts --> <writeHost host="hostM1" url="10.10.10.11:3306" user="root"
                   password="zxcv"> <! -- can have multiread hosts -->
            <readHost host="hostS2" url="10.10.10.12:3306" user="root" password="zxcv" />
        </writeHost>
    </dataHost>
</mycat:schema>
Copy the code

The data node is set here, along with the corresponding back-end real MySQL instance properties, write library and read library properties

The dataHost tag has a balance attribute that sets read/write separation

  • Balance =”0″, the read/write separation mechanism is disabled and all read operations are sent to the currently available writeHost.
  • Balance =”1″, all readHost and stand by writeHost participate in load balancing of SELECT statement;
  • Balance =”2″, all read operations are distributed randomly on writeHost and readhost.
  • Balance =”3″, all read requests are randomly distributed to readhost for execution, writerHost does not bear the read pressure

MariaDB database account Settings

MariaDB [(none)]> grant all privileges on *.* to root@The '%' identified by 'zxcv';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Copy the code

Start the MyCat

# Foreground start
~$ sudo /usr/local/mycat/bin/mycat console

# background boot
~$ sudo /usr/local/mycat/bin/mycat start

# check log
~$ sudo tail -f /usr/local/mycat/logs/wrapper.log STATUS | wrapper | 2021/08/04 16:49:51 | --> Wrapper Started as Daemon STATUS | wrapper | 2021/08/04 16:49:51 | Launching a JVM... The INFO 1 | | JVM 2021/08/04 16:50:17 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org INFO 1 | | JVM 2021/08/04  16:50:17 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserve INFO | jvm 1 | 2021/08/04 16:50:17 | INFO | jvm 1 | 2021/08/04 16:50:44 | MyCAT Server startup successfully. see logsin logs/mycat.log
Copy the code

Test MyCat

MyCat port 8066 is used to connect to the database and port 9066 is used to manage MyCat

~$mysql -h 10.10.10.13 -p 8066 -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with; or \g. Your MySQL connection id is 4 Server version: Mycat Server (OpenCloudDB) Copyright (C) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type'help; ' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| testdb   |
+----------+
1 row in set (0.00 sec)
Copy the code

MyCat is the best open source database middleware