Mycat Deployment Run (Windows environment) with detailed steps to use

1. Mycat Concept
1.1 Overall architecture

The MYCAT architecture is shown in the following illustration:

Mycat uses MySQL's communication protocol to simulate a MySQL server and establishes a complete schema (database), table (datasheet), user-logical model and maps the logical model to the back-end storage node Datanode (MySQL Instance), so that all clients that can use MySQL and the programming language can use Mycat as a mysqlserver, without developing new client protocols.

When Mycat receives a SQL request sent by a client, the SQL is parsed and checked first, the results of the analysis are used for SQL routing, the SQL routing strategy supports the traditional slicing field based on table, and also supports the unique slicing strategy based on the database E-r relationship. For SQL that is routed to multiple data nodes (Datanode), the received DataSet is "merged" and then exported to the client.

The process of SQL execution, simply, is to send SQL through the network protocol to the back end of the real database execution, for MySQL server, is through the MySQL network protocol to send messages, and resolve the results returned, if the SQL does not involve more than one fragment node, then directly return the result, In a socket stream written to the client, this procedure is non-blocking (NIO).

Datanode is a mycat logical Data node that maps to a database of one of the physical databases at the back end, in order to make the system highly available, each Datanode can configure multiple reference addresses (DataSource), When the primary datasource is detected as unavailable, the system automatically switches to the next available datasource, where DataSource can be considered the address of the MySQL master-slave server. 1.2 Logical Libraries

Like any traditional relational database,Mycat also provides a definition of "database" with user-authorized functionality, and the following are some concepts related to the mycat Logical Library: schema: Logical Library, Corresponds to the database in MySQL, the included table is defined in a logical library. Table: Tables, that is, a table stored in a physical database, unlike a traditional database, where a table needs to declare the logical data node Datanode it stores, which is implemented by the partitioning rule definition of a table, which can define the "child table" to which it belongs ( ChildTable) ", the fragment of the child table depends on the specific fragment address with the" parent table ", simply put, that is, all records of a child table belonging to a record a in the parent list are stored on the same fragment as a. Fragment Rule : A binding definition of a field and a function, which returns the ordinal of the Fragment (Datanode) that is stored according to the value of the field, each table can define a fragment rule, the fragment rule can be flexibly extended, and the default provides the Piecewise rule based on the number, String fragmentation rules, and so on. datanode: mycat Logical Data node, is the physical node that holds the table, also known as the Fragment node, through the DataSource to the back-end of a specific database, in general, for high availability, Each datanode is set to two DataSource, one master from the other, when the primary node is down, the system automatically switches to the from node. Datahost: Defines the access address of a physical library to bind to Datanode.

Mycat currently defines logical libraries and related configurations through the configuration file:

· Mycat_home/conf/schema.xml in the definition of logical library, table, fragment node, etc. content;

· Partitioning rules are defined in Mycat_home/conf/rule.xml;

· Mycat_home/conf/server.xml defines user and system-related variables, such as ports.

The following figure gives a possible logical library to the physical library (the complete mapping of MySQL), which can be seen as a powerful fragmentation capability and a flexible MySQL cluster integration capability ( Mycat ).


2, Mycat Basic use of the tutorial 2.1 Download and install

Mycat uses Java development because some of the features of JDK 7 are used, so make sure that JDK 7.0 is installed before use, requires JDK 7.0 , and sets the correct Java environment variables

Currently downloaded version is free of installation, decompression in any disk, root directory, to avoid the path in Chinese .

Directory of "mycat-server-1.2-ga-win.tar.gz" files, the extracted directory structure as shown in the following figure:

The directory description is shown in the following table:

Directory name	Description
Bin	The Windows version and the Linux version are available, in addition to the encapsulated service version, also provides nowrap shell script commands, easy to choose and modify. Windows Run: The Mycat.bat console starts the program in the console, or it can be loaded into a service, and if the program runs a problem, it can also run startup_nowrap.bat to ensure that the Java commands can be executed at the command. Warp the way the command can be installed into a service and start or stop. L Mycat Install (optional) L MYCAT Start Note that the Wrap method of the program, its JVM configuration parameters in conf/wrap.conf, can be modified to the appropriate parameters, parameter adjustment reference http://wrapper.tanukisoftware.com/doc/english/properties.html.
Conf	To store the configuration file: L Server.xml: is the Mycat server parameter adjustment and user authorization profile. L Schema.xml: A configuration file that is a logical library definition and a table and a fragment definition. L Rule.xml: Is the configuration file of the fragment rule, the specific parameter information of the fragment rule is stored separately as file, also in this directory, the configuration file modifies, need to reboot Mycat or pass 9066 port reload. L Wrapper.conf:JVM configuration Parameters settings. L log4j.xml: Log stored in the Logs/mycat.log, a daily file, log configuration is in Conf/log4j.xml, according to their needs, you can adjust the output level of debug,debug level, will output more information, convenient troubleshooting problems.
Lib	Mycat the storage directory of its own jar packages or dependent jar packages.
Logs	The storage directory of the Mycat log. Log stored in Logs/mycat.log, one file per day

2.2 Start and stop

Installation Mycat Service: Mycate Install

Start Mycat service: Mycate start

Stop Mycat service: mycate stop

Note: The Mycat service needs to be restarted after the configuration file has been modified

3, the use of the tutorial 3.1 hardware configuration and Installation database

Local Mycat 192.168.1.5

Server A MySQL 192.168.1.201

Server A MySQL 192.168.1.202

Install MySQL server and MySQL client, the author uses the MySQL server is not installed version: Mysql-noinstall-5.1.73-winx64,mysql client is: Navicat for MySQL, Installation method without installation version Please refer to: http://blog.csdn.net/q98842674/article/details/12094777

3.2 Creating a database

Create the fragmented database used in Server A, Server B, respectively;
CREATE database db1; 3.3 Configuration Files

Schema.xml configuration files, because the library is on a different server, so configure two datahost, and if multiple writehost are configured in one datahost, the primary from configuration. When type= "global", the global table,

<?xml version= "1.0"?>
<! DOCTYPE mycat:schema SYSTEM "SCHEMA.DTD" >
<mycat:schema xmlns:mycat= "http://org.opencloudb/" >

<schema name= "TestDB" checksqlschema= "false" sqlmaxlimit= ">"
<!--auto sharding by ID (long)-->
<table name= "Travelrecord" datanode= "dn1,dn2" rule= "Auto-sharding-long"/>

<!--global table is auto cloned to all defined data nodes, so can join
With any table whose sharding node are in the same data node-->
<table name= "Company" primarykey= "ID" type= "global" datanode= "DN1,DN2"/>
<table name= "goods" primarykey= "ID" type= "global" datanode= "DN1,DN2"/>

<!--random sharding using mod sharind rule-->
<table name= "Hotnews" primarykey= "ID" datanode= "DN1,DN2"
rule= "Mod-long"/>
<!--<table name= "dual" primarykey= "ID" datanode= "Dnx,dnoracle2" type= "global"
Needaddlimit= "false"/> <table name= "worker" primarykey= "ID" datanode= "Jdbc_dn1,jdbc_dn2,jdbc_dn3"
rule= "Mod-long"/>-->
<table name= "Employee" primarykey= "ID" datanode= "DN1,DN2"
rule= "Sharding-by-intfile"/>
<table name= "Customer" primarykey= "ID" datanode= "DN1,DN2"
rule= "Sharding-by-intfile" >
<childtable name= "Orders" primarykey= "ID" joinkey= "customer_id"
parentkey= "id" >
<childtable name= "Order_items" joinkey= "order_id"
parentkey= "id"/>
</childTable>
<childtable name= "customer_addr" primarykey= "ID" joinkey= "customer_id"
parentkey= "id"/>
</table>
</schema>
<datanode name= "dn1" datahost= "Localhost1" database= "DB1"/>
<datanode name= "DN2" datahost= "Localhost2" database= "DB1"/>
<datahost name= "Localhost1" maxcon= "1000" mincon= "ten" balance= "0"
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">"
<writehost host= "hostM1" url= "192.168.1.201:3306" user= "Shopuser"
password= "123456" >
</writeHost>
</dataHost>
<datahost name= "Localhost2" maxcon= "1000" mincon= "ten" balance= "0"
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">"
<writehost host= "hostM1" url= "192.168.1.202:3306" user= "Shopuser"
password= "123456" >
</writeHost>
</dataHost>
</mycat:schema>

Server.xml configuration file, this example is very simple, just define user,

Name: User Name

Password: password

Schemas: the instance name, which corresponds to the schema defined by Schema.xml, where the instance name is the virtual name, which is an alias to the Mycat service, the portal to the application and the client connection.

<?xml version= "1.0" encoding= "UTF-8"?>
<!----licensed under the Apache License, Version 2.0 (the "License");
-' may not ' use ' this file except in compliance with the License. -You
May obtain a copy of the License at-http://www.apache.org/licenses/LICENSE-2.0
--unless required by applicable or agreed into writing, software-
Distributed under the License is distributed on ' as is ' basis,-without
Warranties or CONDITIONS of any KIND, either express OR implied. -The
License for the specific language governing permissions And-limitations
Under the License. -->
<! DOCTYPE mycat:server SYSTEM "SERVER.DTD" >
<mycat:server xmlns:mycat= "http://org.opencloudb/" >
<system>
<property name= "Defaultsqlparser" >druidparser</property>
<!--<property name= "usecompression" >1</property>--> <!--1 for open MySQL compression protocol-->
<!--<property name= "Processorbufferchunk" >40960</property>-->
<!--
<property name= "Processors" >1</property>
<property name= "Processorexecutor" >32</property>
-->
<!--default is 65535 64K maximum text length for SQL Resolution-->
<!--<property name= "Maxstringliterallength" >65535</property>-->
<!--<property name= "Sequncehandlertype" >0</property>-->
<!--<property name= "Backsocketnodelay" >1</property>-->
<!--<property name= "Frontsocketnodelay" >1</property>-->
<!--<property name= "Processorexecutor" >16</property>-->
<!--
<property name= "Mutinodelimittype" >1</property> 0: Open a small order of magnitude (default); 1: Open billion-level data sorting
<property name= "Mutinodepatchsize" >100</property> million order batch quantity
<property name= "processors" >32</property> <property name= "Processorexecutor" >32</property >
<property name= "ServerPort" >8066</property> <property name= "Managerport" >9066</property>
<property name= "IdleTimeout" >300000</property> <property name= "Bindip" >0.0.0.0</property>
<property name= "Frontwritequeuesize" >4096</property> <property name= "processors" >32</ Property>-->
</system>
<user name= "Test" >
<property name= "Password" >test</property>
<property name= "Schemas" >TESTDB</property>
</user>
</mycat:server>

3.4 Login Mycat
On any client machine that has MySQL, connect Mycat, execute the following command
MYSQL-UTEST-PTEST-H192.168.1.5-P8066-DTESTDB Note: 8066 login mycat Data port, 9066 login Mycat management port (can see the configuration within Mycat, as well as the various database connections, very useful)

3.5 Test
Global table: Company

Mysql> CREATE TABLE company (ID int NOT NULL primary key,name varchar (), sharding_id int not null);
Query OK, 0 rows affected (0.30 sec)
Mysql> Explain CREATE TABLE company (ID int. NOT NULL primary key,name varchar (m), sharding_id int not null);
+-----------+------------------------------------------------------------------------------------------------+
| Data_node | SQL |
+-----------+------------------------------------------------------------------------------------------------+
| DN1 | CREATE TABLE company (ID int NOT NULL primary key,name varchar (m), sharding_id int not null) |
| DN2 | CREATE TABLE company (ID int NOT NULL primary key,name varchar (m), sharding_id int not null) |
+-----------+------------------------------------------------------------------------------------------------+
2 rows in Set (0.04 sec)

Mysql> INSERT INTO Company (ID,NAME,SHARDING_ID) VALUES (1, ' leader us ', 10000);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:6
Current Database:testdb

Query OK, 1 row affected (0.03 sec)
Mysql> explain Inser