I. MyCat Basic Concepts

1. What is MyCat

2. Why use MyCat

MyCat effect:

3. Database middleware comparison

4. Supported databases

5. Core Technology

2. MyCat installation and deployment

Environmental Science:

JDK: Requires JDK to be version 1.7 and above
 MySQL: It is recommended that MySQL be at least version 5.5
 MyCat's official website: http://www.mycat.org.cn/

PS:mysql uses an out-of-the-box host (dual-master + keepalived, master-slave as well).If you don't have mysql, check out a blog deployment.

1. Download and install it, using the official website address above.

Copy link, wget Download

[root@localhost ~]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

Decompress the package.It is recommended that mycat be placed in the / usr/local/mycat directory.

[root@localhost ~]# tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@localhost ~]# cd mycat/

Modify profile:

[root@mycat mycat]# vim conf/server.xml  
80         <user name="mycat">      //Define users who connect to the middleware
81                 <property name="password">123456</property>  //Password
82                 <property name="schemas">TESTDB</property>      //Logical Library
[root@mycat mycat]# cp conf/schema.xml conf/schema.xml.bak //Backup Profile
[root@mycat mycat]# vim 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">     //Indicate the logical library, and the nodes
        </schema>
        <dataNode name="dn1" dataHost="host1" database="test_db" />     //Specify the real library in the back-end server
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.1.1:3306" user="root"    //Define Write Server (Primary) and IP Address, User Password
                                   password="123.com">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.1.8:3306" user="root" password="123.com" />    //Define read server (slave) and IP address, user password
                </writeHost>
        </dataHost>
</mycat:schema>

This profile has deleted unnecessary configuration items and comments

Restart mycat service
Let mycat have mysql commands

[root@mysql02 ~]# scp /usr/local/mysql/bin/mysql 192.168.1.3:/usr/local/sbin/

Two mysql authorizations to allow mycat to log on remotely

mysql> grant all on *.* to root@'192.168.1.%' identified by '123.com';

MyCat remote login test:

[root@mycat mycat]# mysql -uroot -h 192.168.1.1 -P 3306 -p123.com
[root@mycat mycat]# mysql -uroot -h 192.168.1.8 -P 3306 -p123.com

Start the mycat service:

[root@mycat mycat]# ./bin/mycat help
Usage: ./bin/mycat { console | start | stop | restart | status | dump }
[root@mycat mycat]# ./bin/mycat console 

[root@mycat ~]# ss -anplt |grep java

###mycat port 8806

Test to see if the data is synchronized:

[root@mycat ~]# mysql -umycat -p123456 -h 192.168.1.3 -P8066
mysql> show databases;

mysql> use TESTDB
mysql> show tables;

mysql> select * from tab1;

3. MyCat Read-Write Separation

Mycat's read-write separation is based on the master-slave replication of Mysql

Modify the configuration file schema.xml

[root@mycat ~]# cd mycat/
[root@mycat mycat]# vim conf/schema.xml

1. Set balance="1" and writeType="0"

Balance parameter settings:
    The modified balance attribute, which configures read-write separated type load balancing types, currently has four values: 
_balance="0", read-write separation mechanism is not enabled, all read operations are sent to the currently available writeHost. 
_balance="1". All readHost s and stand by writeHost participate in the load balancing of the select statement. Simply put, when the dual-master-dual-slave mode (M1->S1, M2->S2, and M1 and M2 are mutually prepared), under normal circumstances, M2,S1,S2 all participate in the load balancing of the select statement.
_balance="2", all read operations are randomly distributed on writeHost, readhost.
_balance='3', all read requests are randomly distributed to readhost for execution, and writerHost does not bear reading pressure
 WriteType parameter settings:
_writeType="0", all writes are sent to the available writeHost.
_writeType='1', all writes are randomly sent to readHost.
_writeType="2". All write operations are randomly distributed on the writeHost, readhost scores.

2. Set switchType="2" and slaveThreshold="100"

There are currently three options for switchType:
-1: Indicates no automatic switching
 _1: Default value, automatic switch
 _2: Decide whether to switch based on the status of MySQL master-slave synchronization

3. Modify the configuration file and start the program

[root@mycat mycat]# vim conf/schema.xml

[root@mycat mycat]# ./bin/mycat console

Logon Background Administration Window This login is used to manage maintenance Mycat

[root@mycat ~]# mysql -umycat -p123456 -h 192.168.1.3 -P8066

4. Verify read-write separation my.cnf binlog_format=STATEMENT

(1) Insert data on the write host:

[root@mysql02 ~]# mysql -uroot -p123.com
mysql> use test1;
mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> insert into tab1 values(4);
Query OK, 1 row affected (0.02 sec)

mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

Master-slave host data inconsistent

[root@mysql01 ~]# mysql -uroot -p123.com
mysql> use test1
mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

(2) Query in Macat:

mysql> use TESTDB
mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)

4. Vertical Split--Branch

In this case, a new mysql is required for the repository operation.

Delete the data inserted in the previous case first

[root@mysql02 ~]# mysql -uroot -p123.com
mysql> delete from tab1 where id=4;
Query OK, 1 row affected (0.01 sec)

mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

[root@mycat ~]# mysql -umycat -p123456 -h 192.168.1.3 -P8066
mysql> use TESTDB

mysql> select * from tab1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

The following four tables:
Customer tables are divided into one database, and the other three require associated queries, which are divided into another database.

#Customer table rows:200,000
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
#Order form rows: 6 million
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#Order Detail Rows: 6 million
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#Order Status Dictionary Table rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);

Implement repositories:

1. Modify schema configuration file

[root@mycat mycat]# vim 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">
                <table name="customer" dataNode="dn2"> </table>
        </schema> 
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.1.1:3306" user="root"
                                   password="123.com">
                        <readHost host="hostS2" url="192.168.1.8:3306" user="root" password="123.com" />
                </writeHost>
        </dataHost> 
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.1.9:3306" user="root"
                                   password="123.com">
                </writeHost> 
        </dataHost>

</mycat:schema>

Authorize MyCat to remotely log on to mysql03

[root@mysql03 ~]# mysql -uroot -p123.com
mysql> grant all on *.* to root@'192.168.1.%' identified by '123.com';

Because the real library mysql03 corresponding to the logical library in the configuration file does not exist, it needs to be created in mysql03

mysql> create database test1;

2. Restart mycat, access MyCat, create tables

[root@mycat mycat]# mysql -umycat -p123456 -h 192.168.1.3 -P8066

mysql> use TESTDB
mysql> CREATE TABLE customer(
    -> id INT AUTO_INCREMENT,
    -> NAME VARCHAR(200),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE orders(
    -> id INT AUTO_INCREMENT,
    -> order_type INT,
    -> customer_id INT,
    -> amount DECIMAL(10,2),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE orders_detail(
    -> id INT AUTO_INCREMENT,
    -> detail VARCHAR(2000),
    -> order_id INT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE dict_order_type(
    -> id INT AUTO_INCREMENT,
    -> order_type VARCHAR(200),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------     +
| Tables_in_test1 |
+-----------------     +
| customer           |
| dict_order_type |
| orders               |
| orders_detail    |
| tab1                  |
+-----------------    +
5 rows in set (0.05 sec)

View the table using mysql01:

mysql> show tables;
+-----------------    +
| Tables_in_test1 |
+-----------------    +
| dict_order_type |
| orders               |
| orders_detail    |
| tab1                  |
+-----------------    +
4 rows in set (0.00 sec)

View the table using mysql03:

mysql> use test1;
mysql> show tables;
+-----------------     +
| Tables_in_test1 |
+-----------------     +
| customer           |
+-----------------     +
1 row in set (0.00 sec)

View structure

mysql> desc customer;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id          | int(11)           | NO    | PRI | NULL    | auto_increment |
| NAME  | varchar(200) | YES  |     | NULL       |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

5. Horizontal Split--Split Table

1. Modify the configuration file schema.xml

[root@mycat mycat]# vim 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">
                <table name="customer" dataNode="dn2"> </table>
                <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
                        <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
                </table>
        </schema> 
        <dataNode name="dn1" dataHost="host1" database="test1" />
        <dataNode name="dn2" dataHost="host2" database="test1" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="192.168.1.1:3306" user="root"
                                   password="123.com">
                        <readHost host="hostS2" url="192.168.1.8:3306" user="root" password="123.com" />
                </writeHost>
        </dataHost> 
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" url="192.168.1.9:3306" user="root"
                                   password="123.com">
                </writeHost> 
        </dataHost>

2. Modify the configuration file rule.xml

[root@mycat mycat]# vim conf/rule.xml

 38         <tableRule name="mod_rule">
 39                 <rule>
 40                         <columns>customer_id</columns>
 41                         <algorithm>mod-long</algorithm>
 42                 </rule>
 43         </tableRule>
 ......
 105         <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
 106                 <!-- how many data nodes -->
 107                 <property name="count">2</property>
#Configuration algorithm mod-long parameter count is 2, two nodes
 108         </function>

3. Because there is no order table on dn2(mysql03:192.168.1.9), create order table on data node dn2 and restart mycat service

[root@mysql03 ~]# mysql -uroot -p123.com
mysql> use test1;
mysql> CREATE TABLE orders(
    -> id INT AUTO_INCREMENT,
    -> order_type INT,
    -> customer_id INT,
    -> amount DECIMAL(10,2),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

4. Insert data into orders table in MyCat, insert field cannot be omitted

[root@mycat mycat]# mysql -umycat -p123456 -P8066 -h192.168.1.3
mysql> use TESTDB

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
Query OK, 1 row affected (0.01 sec)

#View orders table data in mycat, dn1, dn2, table breakdown succeeded

dn1(mysql01:192.168.1.1):

[root@mysql01 ~]# mysql -uroot -p123.com
mysql> use test1;
mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  2 |        101 |         100 | 100300.00 |
|  4 |        101 |         101 | 103000.00 |
|  6 |        102 |         100 | 100020.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

dn2(mysql03:192.168.1.9):

[root@mysql03 ~]# mysql -uroot -p123.com
mysql> use test1;

mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  1 |        101 |         100 | 100100.00 |
|  3 |        101 |         101 | 120000.00 |
|  5 |        102 |         101 | 100400.00 |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

Create orders_detail table in dn2(mysql03:192.168.1.9)

mysql> CREATE TABLE orders_detail(
    -> id INT AUTO_INCREMENT,
    -> detail VARCHAR(2000),
    -> order_id INT,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

Restart Mycat to access Mycat to insert data into orders_detail table

[root@mycat mycat]# mysql -umycat -p123456 -h 192.168.1.3 -P8066
mysql> use TESTDB
mysql> insert into orders_detail(id,detail,order_id) values (1,'detail',1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into orders_detail(id,detail,order_id) values (2,'detail',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into orders_detail(id,detail,order_id) values (3,'detail',3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders_detail(id,detail,order_id) values (4,'detail',4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into orders_detail(id,detail,order_id) values (5,'detail',5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into orders_detail(id,detail,order_id) values (6,'detail',6);
Query OK, 1 row affected (0.01 sec)

mysql> select o.*,od.detail from orders as o inner join orders_detail as od on o.id=od.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount    | detail |
+----+------------+-------------+-----------+--------+
|  1 |        101 |         100 | 100100.00 | detail |
|  3 |        101 |         101 | 120000.00 | detail |
|  5 |        102 |         101 | 100400.00 | detail |
|  2 |        101 |         100 | 100300.00 | detail |
|  4 |        101 |         101 | 103000.00 | detail |
|  6 |        102 |         100 | 100020.00 | detail |
+----+------------+-------------+-----------+--------+
6 rows in set (0.03 sec)

View using dn1, dn2:

mysql> select * from orders_detail;

mysql> select * from orders_detail;

6. Global Tables

Change infrequently 
The total amount of data does not change much 
Data is small, with few records exceeding hundreds of thousands 

Insertions and updates of global tables are performed on all nodes in real time, maintaining data consistency across fragments
 Query operations for global tables, obtained from only one node 
Global tables can be defined as global tables by JOIN operations with any table, which solves the problem of data JOIN. 

Mycat can meet more than 80% of enterprise application development through global table + fragmentation based on E-R relationship

[root@mycat mycat]# vim conf/schema.xml

                <table name="dict_order_type" dataNode="dn1,dn2" type="global"> </table>

Create dict_order_type table in dn2(mysql03:192.168.1.9)

mysql> CREATE TABLE dict_order_type(
    -> id INT AUTO_INCREMENT,
    -> order_type VARCHAR(200),
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

Restart MyCat and access MyCat to insert data into dict_order_type table

[root@mycat mycat]# mysql -umycat -p123456 -h 192.168.1.3 -P8066
mysql> use TESTDB

mysql>  insert into dict_order_type (id,order_type) values (101,'type1');
Query OK, 1 row affected (0.03 sec)

mysql>  insert into dict_order_type (id,order_type) values (102,'type2');
Query OK, 1 row affected (0.01 sec)

mysql> select * from dict_order_type;
+-----+------------+
| id  | order_type |
+-----+------------+
| 101 | type1      |
| 102 | type2      |
+-----+------------+
2 rows in set (0.03 sec)

Common slicing rules