Vertical database sharding and horizontal sharding. The cobar version is mycat.

Vertical database sharding and horizontal sharding. The cobar version is mycat.
1. About Mycat

Mycat intelligence
Based on Alibaba's open-source cobar, it can be used in production systems. The following improvements are currently being made:
Non-blocking IO implementation, compared with the current cobar, The concurrency performance is greatly improved, and will not fall into the false state
Optimized the allocation of thread pools. Currently, the cobar thread pool allocation efficiency is not high.
Fix cobar bugs
Refer to the Java code in impala front in impala to implement efficient Map-Reduce, which can process hundreds of millions of data records.
Automatic sharding is implemented. Currently, cobar requires manual sharding and has certain programming restrictions.
Official Website:
Https://github.com/MyCATApache/
Many documents, most of which are in word format, are detailed.
Https://github.com/MyCATApache/Mycat-doc
Mycat is a cobar re-optimized version, which is similar to many cobar configurations.
Refer to the previously written cobar installation:
Http://blog.csdn.net/freewebsys/article/details/44022421

2. Install the Mycat Service

Download binary installation files
Https://github.com/MyCATApache/Mycat-download
Decompress the package to/usr/local/mycat.
Modify the configuration file:

<? Xml version = "1.0"?> <! DOCTYPE mycat: schema SYSTEM "schema. dtd "> <mycat: schema xmlns: mycat =" http://org.opencloudb/"> # The Database Name Is TESTDB, sqlMaxLimit setting limit prevents incorrect SQL queries of large amounts of data <schema name = "TESTDB" checkSQLschema = "false" sqlMaxLimit = "100"> # automatic database sharding rules <! -- Auto sharding by id (long) --> <table name = "travelrecord" dataNode = "dn1, dn2, dn3 "rule =" auto-sharding-long "/> # global configuration table. All data is synchronized to each database. <! -- Global table is auto cloned to all defined data nodes, so can join with any table whose sharding node is in the same data node --> <table name = "company" primaryKey = "ID" type = "global" dataNode = "dn1, dn2, dn3 "/> <table name =" goods "primaryKey =" ID "type =" global "dataNode =" dn1, dn2 "/> <! -- Random sharding using mod sharind rule --> <table name = "hotnews" primaryKey = "ID" dataNode = "dn1, dn2, dn3 "rule =" mod-long "/> <table name =" employee "primaryKey =" ID "dataNode =" dn1, dn2 "rule =" sharding-by-intfile "/> # associate the sub-table configuration, not quite clear <table name =" customer "primaryKey =" ID "dataNode =" dn1, dn2 "rule =" sharding-by-intfile "> <childTable name =" orders "primaryKey =" ID "joinKey =" customer_id "parentKey =" id "> <childTa Ble name = "order_items" joinKey = "order_id" parentKey = "id"/> </childTable> <childTable name = "customer_addr" primaryKey = "ID" joinKey = "customer_id" parentKey = "id"/> </table> </schema> # configure multiple dataNode, specify the dataHost and database name. <DataNode name = "dn1" dataHost = "localhost1" database = "db1"/> <dataNode name = "dn2" dataHost = "localhost1" database = "db2"/> <dataNode name = "dn3" dataHost = "localhost1" database = "db3"/> # configure dataHost read/write allocation. At the same time, you must configure mysql, master-Slave or Master-Master <dataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "0" writeType = "0" dbType = "mysql" dbDriver = "native"> 

Wrapper. conf is the startup parameter of the mycat configuration file.
Rule. xml is the configuration rule xml
Several other configuration files are being studied.
Create a database

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE db2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE db3 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Service Startup:/bin/mycat start
/Bin/mycat can be used as a service at the same time and copied to the/etc/init. d/directory.

3. log on to the mycat service and management terminal.

Log on to the mycat Service (Port 8066)

# mysql -utest -ptest -h 127.0.0.1 -P8066Warning: USING a password ON the command line interface can be insecure.Welcome TO the MySQL monitor. Commands END WITH ; OR \g.Your MySQL connection id IS 1Server version: 5.5.8-mycat-1.3 MyCat Server (OpenCloundDB)Copyright (c) 2000, 2015, Oracle AND/OR its affiliates. ALL rights reserved.Oracle IS a registered trademark OF Oracle Corporation AND/OR itsaffiliates. Other names may be trademarks OF their respectiveowners.TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the CURRENT INPUT statement.mysql> SHOW DATABASES;+----------+| DATABASE |+----------+| TESTDB |+----------+1 ROW IN SET (0.02 sec)mysql> USE TESTDB;Reading TABLE information FOR completion OF TABLE AND COLUMN namesYou can turn off this feature TO GET a quicker startup WITH -ADATABASE changedmysql> SHOW TABLES;+------------------+| TABLES IN TESTDB |+------------------+| company || customer || customer_addr || employee || goods || hotnews || orders || order_items || travelrecord |+------------------+9 ROWS IN SET (0.00 sec)mysql> CREATE TABLE company(id INT NOT NULL PRIMARY KEY,name VARCHAR(100));Query OK, 0 ROWS affected (0.05 sec)mysql> EXPLAIN CREATE TABLE company(id INT NOT NULL PRIMARY KEY,name VARCHAR(100));+-----------+---------------------------------------------------------------------+| DATA_NODE | SQL |+-----------+---------------------------------------------------------------------+| dn1 | CREATE TABLE company(id INT NOT NULL PRIMARY KEY,name VARCHAR(100)) || dn2 | CREATE TABLE company(id INT NOT NULL PRIMARY KEY,name VARCHAR(100)) || dn3 | CREATE TABLE company(id INT NOT NULL PRIMARY KEY,name VARCHAR(100)) |+-----------+---------------------------------------------------------------------+3 ROWS IN SET (0.01 sec)mysql> INSERT INTO company(id,name) VALUES(1,'hp'); INSERT INTO company(id,name) VALUES(2,'ibm'); INSERT INTO company(id,name) VALUES(3,'oracle');Query OK, 3 ROWS affected (0.37 sec)Query OK, 3 ROWS affected (0.01 sec)Query OK, 3 ROWS affected (0.00 sec)mysql> SELECT * FROM company ;+----+--------+| id | name |+----+--------+| 1 | hp || 2 | ibm || 3 | oracle |+----+--------+3 ROWS IN SET (0.01 sec)

Log on to the Management Terminal: (Port 9066)

# mysql -utest -ptest -h 127.0.0.1 -P9066Warning: USING a password ON the command line interface can be insecure.Welcome TO the MySQL monitor. Commands END WITH ; OR \g.Your MySQL connection id IS 2Server version: 5.5.8-mycat-1.3 CobarManager@AlibabaCopyright (c) 2000, 2015, Oracle AND/OR its affiliates. ALL rights reserved.Oracle IS a registered trademark OF Oracle Corporation AND/OR itsaffiliates. Other names may be trademarks OF their respectiveowners.TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the CURRENT INPUT statement.mysql> SHOW @@help;+--------------------------------------+-----------------------------------+| STATEMENT | DESCRIPTION |+--------------------------------------+-----------------------------------+| clear @@slow WHERE datanode = ? | Clear slow SQL BY datanode || clear @@slow WHERE schema = ? | Clear slow SQL BY schema || KILL @@connection id1,id2,... | KILL the specified connections || offline | CHANGE MyCat STATUS TO OFF || online | CHANGE MyCat STATUS TO ON || reload @@config | Reload ALL config FROM file || reload @@route | Reload route config FROM file || reload @@USER | Reload USER config FROM file || ROLLBACK @@config | ROLLBACK ALL config FROM memory || ROLLBACK @@route | ROLLBACK route config FROM memory || ROLLBACK @@USER | ROLLBACK USER config FROM memory || SHOW @@backend | Report backend connection STATUS || SHOW @@cache | Report system cache usage || SHOW @@command | Report commands STATUS || SHOW @@connection | Report connection STATUS || SHOW @@connection.SQL | Report connection SQL || SHOW @@DATABASE | Report DATABASES || SHOW @@datanode | Report dataNodes || SHOW @@datanode WHERE schema = ? | Report dataNodes || SHOW @@datasource | Report dataSources || SHOW @@datasource WHERE dataNode = ? | Report dataSources || SHOW @@heartbeat | Report heartbeat STATUS || SHOW @@parser | Report parser STATUS || SHOW @@processor | Report processor STATUS || SHOW @@router | Report router STATUS || SHOW @@server | Report server STATUS || SHOW @@SESSION | Report front SESSION details || SHOW @@slow WHERE datanode = ? | Report datanode slow SQL || SHOW @@slow WHERE schema = ? | Report schema slow SQL || SHOW @@SQL WHERE id = ? | Report specify SQL || SHOW @@SQL.detail WHERE id = ? | Report EXECUTE detail STATUS || SHOW @@SQL.EXECUTE | Report EXECUTE STATUS || SHOW @@SQL.slow | Report slow SQL || SHOW @@threadpool | Report threadPool STATUS || SHOW @@TIME.CURRENT | Report CURRENT TIMESTAMP || SHOW @@TIME.startup | Report startup TIMESTAMP || SHOW @@version | Report Mycat Server version || stop @@heartbeat name:TIME | Pause dataNode heartbeat || switch @@datasource name:INDEX | Switch dataSource |+--------------------------------------+-----------------------------------+39 ROWS IN SET (0.03 sec)mysql> SHOW @@backend;+------------+------+---------+-----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+------------+| processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema | txlevel | autocommit |+------------+------+---------+-----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+------------+| Processor0 | 12 | 33 | 127.0.0.1 | 3306 | 42505 | 596 | 501 | 3384 | FALSE | FALSE | 0 .......17 ROWS IN SET (0.03 sec)mysql> SHOW @@connection;+------------+------+-----------+------+------------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+| PROCESSOR | ID | HOST | PORT | LOCAL_PORT | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |+------------+------+-----------+------+------------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+| Processor0 | 2 | 127.0.0.1 | 9066 | 35952 | NULL | utf8 | 156 | 4107 | 40 | 4096 | 0 | NULL | NULL || Processor0 | 1 | 127.0.0.1 | 8066 | 16525 | TESTDB | utf8 | 2005 | 5132 | 3461 | 4096 | 0 | 3 | TRUE |+------------+------+-----------+------+------------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+2 ROWS IN SET (0.02 sec)mysql> SHOW @@heartbeat;+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| hostM1 | mysql | 127.0.0.1 | 3306 | 1 | 0 | idle | 30000 | 0,0,0 | 2015-03-03 14:45:00 | FALSE |+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+1 ROW IN SET (0.01 sec)mysql> SHOW @@datanode;+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+| dn1 | localhost1/db1 | 0 | mysql | 0 | 9 | 50 | 27 | 0 | 0 | 0 | -1 || dn2 | localhost1/db2 | 0 | mysql | 0 | 4 | 50 | 26 | 0 | 0 | 0 | -1 || dn3 | localhost1/db3 | 0 | mysql | 0 | 3 | 50 | 17 | 0 | 0 | 0 | -1 |+------+----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+3 ROWS IN SET (0.01 sec)mysql> SHOW @@cache;+-------------------------------------+-------+------+--------+------+------+---------------+---------------+| CACHE | MAX | CUR | ACCESS | HIT | PUT | LAST_ACCESS | LAST_PUT |+-------------------------------------+-------+------+--------+------+------+---------------+---------------+| SQLRouteCache | 10000 | 1 | 8 | 0 | 1 | 1425364473193 | 1425363891040 || TableID2DataNodeCache.TESTDB_ORDERS | 50000 | 0 | 0 | 0 | 0 | 0 | 0 || ER_SQL2PARENTID | 1000 | 0 | 0 | 0 | 0 | 0 | 0 |+-------------------------------------+-------+------+--------+------+------+---------------+---------------+3 ROWS IN SET (0.05 sec)

4. Summary

The initial installation and testing of the mycat component is still very good, indeed there are a lot of progress than cobar.
At the same time, their development team also said that they solved the problems that existed before cobar.
With global tables, you can use join and add read/write splitting.
Many rules have been added to meet most of the requirements.
Like cobar, mycat can be directly disguised as a mysql server, which vertically splits services and horizontally splits services. Smooth data expansion.
Ensure Optimization on the original system. Next we will continue to study mycat, a very good solution.