Mycat installation and fragmentation initial experience

One: JDK installation

Mycat is Java development, need to have a Java running environment, Mycat rely on the jdk1.7 environment, if this machine does not, you need to download the installation first.

To install the JDK, please refer to: http://blog.csdn.net/yabingshi_tech/article/details/51498017

Two: Mycat installation

Click here to download

Tar xvf mycat-server-1.4-release-20151019230038-linux.tar.gz

MV mycat//usr/local/

Cd/usr/local/mycat/conf

Vim wrapper.conf

To modify the path to Java:

# Java Application

Wrapper.java.command=/usr/local/jdk/bin/java

Cd/usr/local/mycat/bin

[root@pc bin]#./mycat start

Starting mycat-server ...

Mycat has already started Port 8066.

[Root@pc ~]# NETSTAT-NLTP | grep 8066

TCP 0 0::: 8066:::* LISTEN 12742/java

Mycat can be accessed through MYSQL-UTEST-PTEST-H127.0.0.1-P8066-DTESTDB.

Three: Configure MySQL

Note: If the Linux version of MySQL, you need to set the MySQL case is not sensitive, otherwise the table can not find problems.

Add a row to my.cnf [mysqld] in the MySQL configuration file

Lower_case_table_names = 1

Then restart MySQL to take effect.

Four: Configure Mycat connection MySQL

Modify the Mycat_home/conf/schema.xml file, modify the datahost corresponding connection information (here username password is the MySQL username you want to access password):

Before modification:

After modification such as:

V: Open firewall port

If you start a firewall, you need to open the port:

Vi/etc/sysconfig/iptables

Add to:

-A input-m state--state new-m tcp-p tcp--dport 8066-j ACCEPT

#重启防火墙

Service iptables Restart VI: Fragmentation experiment

Experiment Scene:

There is an employee (ID int,name varchar (), Deptno int) table. You want to divide it into several child tables based on its department number. 6.1: Create a fragment rule

Mycat Common partitioning rules like the next, there are some other slicing methods here are not all enumerated:
(1) Fragment enumeration: Sharding-by-intfile
(2) Primary key range: Auto-sharding-long
(3) Consistency Hash:sharding-by-murmur
(4) String hash parsing: Sharding-by-stringhash
(5) Fragment by Date (day): Sharding-by-date
(6) Split by one-month-per-hour: sharding-by-hour
(6) Natural Month Fragment: Sharding-by-month

This uses the Fragment enumeration method.

Here, you define three values, and the rules are defined in Rule.xml.

1. Tablerule

2. function

3. Mapfile First, define Tablerule

<tablerule name= "Sharding-by-intfile-userdb-employee" >

<rule>

<columns>deptno</columns>

<algorithm>hash-int-USERDB-employee</algorithm>

</rule>

</tableRule> Second, define function <function name= "Hash-int-userdb-employee" class= " Org.opencloudb.route.function.PartitionByFileMap ">
<property name= "Mapfile" >partition-hash-int-USERDB-employee.txt</property>
<property name= "Type" >0</property>
<property name= "Defaultnode" >0</property>
</function>

The type default value is 0,0 for integer, Non-zero for string.

Defaultnode default node: Less than 0 means no default node is set, greater than or equal to 0 means setting default node default node function: When enumerating slices, if you encounter an unrecognized enumeration value, let it be routed to the default node. If you do not configure a default node (the Defaultnode value is less than 0 and the default node is not configured), an error occurs when you encounter an unrecognized enumeration value, like This:can ' t find Datanode for sharding column:column_name val: FFFFFFFF Third Create Partition-hash-int-userdb-employee.txt file

Create the file under the Conf path to define the rules for the enumeration

VI Partition-hash-int-userdb-employee.txt

Add to:

10=0

20=1

30=2

Where the department Number 10 is distributed to the first node, the department number 20 data distribution is in the second node, the department number is 30 of the data in the third node. 6.2: New Logical Library, logical table

Schema tags are used to define logical libraries in MYCAT instances, and you can use schema tags to divide these different logical libraries.

A schema can specify a logical table, Datanode,rule.

Mycat The logical library TESTDB is configured by default.

<schema name= "TestDB" checksqlschema= "false" sqlmaxlimit= ">"

<table name= "Travelrecord" datanode= "Dn1,dn2,dn3" rule= "Auto-sharding-long" ></table>

</schema>

This configures a logical library USERDB:

Modify Server.xml

Add to:

<schema name= "UserDB" checksqlschema= "false" sqlmaxlimit= ">"

<table name= "Employee" datanode= "Dn10,dn11,dn12" rule= "Sharding-by-intfile-userdb-employee" ></table>

</schema> 6.3: Configure logical and Physical library correspondence

Because the configuration file is configured with only dn1,dn2,dn3 these datanode, additional configuration dn10,dn11,dn12 the corresponding physical library is required:

Add to:

<datanode name= "Dn10" datahost= "Localhost1" database= "DB10"/>

<datanode name= "Dn11" datahost= "Localhost1" database= "Db11"/>

<datanode name= "Dn12" datahost= "Localhost1" database= "Db12"/>

Otherwise restart Mycat, the log will be an error

INFO | JVM 1 | 2016/08/18 17:14:02 | Log4j:error 2016-08-18 17:14:02 Startup ERROR

INFO | JVM 1 | 2016/08/18 17:14:02 | caused By:org.opencloudb.config.util.ConfigException:dataNode ' dn10 ' is not found! 6.4: Create a physical library

Create dn10,dn11,dn12 corresponding database in MySQL:

mysql> CREATE DATABASE DB10;

Query OK, 1 rowaffected (0.00 sec)

mysql> CREATE DATABASE Db11;

Query OK, 1 rowaffected (0.01 sec)

mysql> CREATE DATABASE db12;

Query OK, 1 rowaffected (0.00 sec) 6.5: Set the user's corresponding logical library

Modify Server.xml to add the logical library for the test user:

If not added, the user will be able to access the logical library of Mycat with an error:

mysql> use UserDB;

ERROR 1044 (HY000): Access denied for user ' test ' to database ' UserDB '

After modifying the configuration file, restart the Mycat

[Root@pc conf]#. /bin/mycat stop

Stopping mycat-server ...

Stopped Mycat-server.

[Root@pc conf]#. /bin/mycat start

Starting mycat-server ... 6.6: Fragmentation test

Connect Mycat mysql-utest-ptest-h127.0.0.1-p8066-duserdb mysql> show tables; +------------------+
|
Tables in UserDB | +------------------+
|
Employee |
+------------------+ 1 row in Set (0.00 sec) mysql> select * from employee; ERROR 1105 (HY000): Table ' db12.
Employee ' doesn ' t exist the error because we haven't created the table yet.
Mysql> Explain CREATE TABLE employee (ID int,name varchar (), deptno int); +-----------+-----------------------------------------------+
| Data_node |
SQL | +-----------+-----------------------------------------------+
| Dn10 | CREATE TABLE employee (ID int,name varchar (), Deptno int) | | Dn11 | CREATE TABLE employee (ID int,name varchar (), Deptno int) | | Dn12 |
CREATE TABLE employee (ID int,name varchar (), Deptno int) | +-----------+-----------------------------------------------------------++-----------+-------------------------
----------------------+ 3 rows in Set (0.05 sec) is seen to be assigned to DN10,DN11,DN12. Mysql> Create TaBLE employee (ID int,name varchar (), deptno int); Query OK, 0 rows affected (0.16 sec) into MySQL to see the new table.
such as: mysql> use DB10; Reading table information for completion oftable and column names your can turn off this feature to get Aquicker startup wi
Th-a Database changed mysql> show tables; +----------------+
|
TABLES_IN_DB10 | +----------------+
|
Employee | +----------------+ 1 row in Set (0.00 sec) #准备测试数据 Connect Mycat, insert first to see where it will be inserted: mysql> explain INSERT into employee (Id,name,
DEPTNO) VALUES (1, ' Dan ', Ten), (2, ' jiao ',), (3, ' song ',), (4, ' Yang ',), (5, ' BM ', 10)); +-----------+----------------------------------------------------------------------------------+
| Data_node |
SQL | +-----------+----------------------------------------------------------------------------------+
| Dn10 | INSERT into employee (ID, name, deptno) VALUES (1, ' Dan ', Ten), (5, ' BM ', 10) | | Dn11 | INSERT into EmploYee (ID, name, deptno) VALUES (2, ' jiao ',), (4, ' Yang ', 20) | | Dn12 |
INSERT into employee (ID, name, deptno) VALUES (3, ' song ', 30) | +-----------+----------------------------------------------------------------------------------+ 3 rows in set ( 0.01 sec) mysql> INSERT into employee (ID,NAME,DEPTNO) VALUES (1, ' Dan ', Ten), (2, ' jiao ',), (3, ' song ',), (4, ' Yang ', 20
), (5, ' BM ', 10));
Query OK, 5 rows affected (0.11 sec) into MySQL validation: mysql> select * from Db10.employee; +------+------+--------+
| ID | name |
Deptno |   +------+------+--------+
| 1 |     Dan |   10 | | 5 |     BM |
10 |
+------+------+--------+ 2 rows in Set (0.00 sec) mysql> select * from Db11.employee; +------+------+--------+
| ID | name |
Deptno |   +------+------+--------+
| 2 |     Jiao |   20 | | 4 |     Yang |
20 |
+------+------+--------+ 2 rows in Set (0.01 sec) mysql> select * from Db12.employee; +------+------+--------+
| ID | name |
Deptno |  +------+------+--------+
| 3 |     Song |
30 | +------+------+--------+ 1 row in Set (0.00 sec)

Seven: Create a user in Mycat

modifying Mycat_home/conf/server.xml files

Follow the test user to create a new user mycat:

Modified as follows:

Log on to Port 9066 to make the configuration effective:

Reload @ @config_all;

If the error:

mysql> Reload @ @config_all;

ERROR 1003 (HY000): Reload config failure

Indicates that the configuration file is incorrectly configured.

Eight: Piecewise join of the Mycat

Mycat current version supports cross slicing join, the main implementation method has four kinds. Global tables, er slices, Catlett (AI) and Sharejoin,sharejoin are supported in the development version, with the preceding three ways 1.3.0.1 support. 8.1 Global Table

In a real business system, there are often multiple tables of similar dictionary tables, and these tables are rarely changed, as shown in the figure:

Configuration:

Global table configuration is relatively simple, do not write rule rules, the following configuration can be:

<table name= "Company" primarykey= "ID" type= "global" datanode= "Dn1,dn2,dn3"/>

Configure a global table province under UserDB:

VI Schema.xml

Add to:

<table name= "province" datanode= "Dn10,dn11,dn12" type= "global" ></table>

Login to Port 9066, execute command: reload @ @config_all;

Build table:
mysql-utest-ptest-h127.0.0.1-p8066-duserdb
mysql> CREATE table province (ID int,name varchar); 
  query OK, 0 rows affected (0.19 sec) 
mysql> insert into province (Id,name) VALUES (1, ' Beijing ');
Query OK, 3 rows affected (0.11 sec) 
mysql> select * from province limit 1;
+------+---------+
| id |  name    |
+------+---------+
|   1 | Beijing |
+------+---------+
1 row in Set (3.56 sec) 
all db10,db11,db12 in MySQL can see the inserted data:
mysql> SELECT * FROM db12.province;
+------+---------+
| id |  name    |
+------+---------+
|   1 | Beijing |
+------+---------+
1 row in Set (0.00 sec) 
mysql> select * from Db11.province;
+------+---------+
| id |  name    |
+------+---------+
|   1 | Beijing |
+------+---------+
1 row in Set (0.01 sec) 
mysql> select * from Db10.province;
+------+---------+
| id |  name    |
+------+---------+
|   1 | Beijing |
+------+---------+
1 row in Set (0.00 sec)

8.2 ER Join

The data partitioning strategy based on E-R relationship, the records of the child table and the associated parent table records are stored on the same data fragment.

Fragment on the DN1,DN2, orders rely on the parent table for fragmentation, two table association is orders.customer_id=customer.id. The schematic diagram is as follows:

Configuration:

<tablen ame= "Customer" datanode= "Dn1,dn2" rule= "Sharding-by-intfile" >

<childtable name= "Orders" joinkey= "customer_id" parentkey= "id"/>

</table>

Example:

I first created a fragment rule: Auto-sharding-long-custom

IDs belong to the 0-1000 range in partition 1, 1000-2000 in Partition 2, 2000-3000 in fragment 3.

VI Schema.xml

Configure at UserDB:

<table name= "Customer" datanode= "Dn10,dn11,dn12" rule= "Auto-sharding-long-custom" >

<childtable name= "Orders" joinkey= "customer_id" parentkey= "id"/>

</table>

Log on to Port 9066 to make the configuration effective:

Reload @ @config_all;

[root@pc conf]# mysql-u test-ptest-p8066-h 127.0.0.1 testdb table Reading for information completion and oftable  N names can turn off this feature to get Aquicker startup With-a to the MySQL Monitor. Commands End With;
or \g. Your MySQL Connection ID is 1 Server version:5.5.8-mycat-1.3 mycatserver (OPENCLOUNDDB) Copyright (c), Oracl e and/or itsaffiliates.
 
All rights reserved. Oracle is a registered trademark of the Oraclecorporation and/or its affiliates.
 
The other names may is trademarksof their respective owners. Type ' help, ' or ' \h ' for help.
 
Type ' \c ' toclear the current input statement.
mysql> use UserDB; Reading table information for completion oftable and column names your can turn off this feature to get Aquicker startup wi
Th-a Database changed mysql> show tables; +------------------+
|
Tables in UserDB | +------------------+
| Company | | Customer | | Employee | | Orders | |
Province | +------------------+ 5 rows in Set (0.01 sec) Build table: mysql> CREATE TABLE customer (ID int primary key,name varchar (30)); Query OK, 0 rows affected (0.13 sec) mysql> CREATE TABLE orders (ID int,name varchar (), customer_id Int,constraint FK
_companyid foreign KEY (customer_id) references customer (ID); Query OK, 0 rows affected (0.25 sec) inserts several data: Mysql>insert into Customer (id,name) values (999, ' Dan '), (1000, ' jiao '), (100
3, ' song '), (2002, ' Yang '); mysql> INSERT into orders (ID,NAME,CUSTOMER_ID) VALUES (1, ' mirror ', 999), (2, ' Banana ', 2002), (3, ' Apple ', 1003), (4, '
Pear ', 2002);
ERROR 1064 (HY000): childtable multi Insertnot provided it appears that mycat child tables do not support inserting multiple records at once.
mysql> INSERT into orders (ID,NAME,CUSTOMER_ID) VALUES (1, ' mirror ', 999);  Query OK, 1 row affected (0.07 sec) mysql> insert into orders (ID,NAME,CUSTOMER_ID) VALUES (2, ' Banana ', 2002) Query OK,
1 Row Affected (0.02 sec) mysql> insert into orders (ID,NAME,CUSTOMER_ID) VALUES (3, ' Apple ', 1003); Query OK, 1 row affected (0.02 sec) mysql> INsert into orders (id,name,customer_id) VALUES (4, ' Pear ', 2002);
Query OK, 1 row affected (0.02 sec) into MySQL authentication: mysql> select * from Db10.customer; +------+------+
| ID |
name | +------+------+
| 999 | Dan | | 1000 |
Jiao |
+------+------+ 2 rows in Set (0.01 sec) mysql> select * from Db11.customer; +------+------+
| ID |
name | +------+------+
| 1003 |
Song |
+------+------+ 1 row in Set (0.00 sec) mysql> select * from Db12.customer; +------+------+
| ID |
name | +------+------+
| 2002 |
Yang |
+------+------+ 1 row in Set (0.00 sec) mysql> select * from Db12.orders; +------+--------+-------------+
| ID | name |
customer_id |   +------+--------+-------------+
| 2 |        Banana |   2002 | | 4 |        Pear |
2002 |
+------+--------+-------------+ 2 rows in Set (0.00 sec) mysql> select * from Db11.orders; +------+-------+-------------+
| ID | name |
customer_id |   +------+-------+-------------+
| 3 |        Apple |
1003 | +------+-------+-------------+
1 row in Set (0.00 sec) mysql> select * from Db10.orders; +------+--------+-------------+
| ID | name |
customer_id |   +------+--------+-------------+
| 1 |         Mirror |
999 |
 
+------+--------+-------------+ 1 row in Set (0.00 sec) we see which fragment the customer ID of the orders column customer_id corresponds to, and which piece of the record the orders is in. 
It is possible to combine queries normally on Mycat: mysql> select B.*,a.name as Custome_name from customer a inner join orders B on a.id=b.customer_id; +------+--------+-------------+--------------+
| ID | name |
customer_id |custome_name |   +------+--------+-------------+--------------+
| 2 |        Banana |   2002 |yang | | 4 |        Pear | 2002 |   Yang | | 1 |         Mirror |   999 |dan | | 3 |        Apple | 1003 |
Song | +------+--------+-------------+--------------+ 4 rows in Set (0.02 sec)

8.3 Share Join

The Sharejoin is a simple cross fragment join, based on a HBT approach. The 2-table join is currently supported by parsing the SQL statements, splitting the SQL statement into a single table, and then aggregating the data from each node. Configure a,b tables that support any configuration such as:

A,b Datanode the same

A,b's datanode are different.

Example:

I first created a fragment rule: Auto-sharding-long-custom

The ID belongs to the 0-1000 range in section 1, and 1000-2000 is in Partition 2.

New definition two two tables in different slices

VI Schema.xml

Add at UserDB:

<table name= "Student" primarykey= "ID" datanode= "Dn10,dn11" rule= "Auto-sharding-long-custom" ></table>

<table name= "Score" primarykey= "ID" datanode= "Dn11,dn12" rule= "Auto-sharding-long-custom" ></table>

Log on to Port 9066 to make the configuration effective:

Reload @ @config_all;

Build table: mysql> CREATE TABLE student (ID int primary key,name varchar (30));  Query OK, 0 rows affected (0.19 sec) mysql> CREATE TABLE score (ID int,studentid int,score int,constraint
Foreign KEY (StudentID) references student (ID)); Query OK, 0 rows affected (0.13 sec) Insert data: mysql> INSERT INTO student (Id,name) VALUES (1, ' Dan '), (1002, ' Jiao '), (+, ' so
Ng '); Query OK, 3 rows affected (0.33 sec) records:2 duplicates:0 warnings:0 mysql> insert INTO score (Id,studentid,scor
(e) VALUES (1,1,100);
Query OK, 1 row affected (0.07 sec) mysql> insert INTO score (Id,studentid,score) values (1008,88,90);
Query OK, 1 row affected (0.03 sec) mysql> insert INTO score (Id,studentid,score) values (8,1002,99);
Query OK, 1 row affected (0.01 sec) into MySQL search: mysql> select * from Db10.student; +----+------+
| ID |
name | +----+------+
| 1 | Dan | | 88 |
Song |
+----+------+ 2 rows in Set (0.01 sec) mysql> select * from Db11.student; +------+------+
| ID |
name | +------+------+
| 1002 |
Jiao |
+------+------+ 1 row in Set (0.00 sec) mysql> select * from Db12.student;
ERROR 1146 (42S02): Table ' db12.student ' doesn ' t exist mysql> select * from Db10.score;
ERROR 1146 (42S02): Table ' db10.score ' doesn ' t exist mysql> select * from Db12.score; +------+-----------+-------+
| ID | StudentID |
Score | +------+-----------+-------+
|        1008 |   88 |
90 |
+------+-----------+-------+ 1 row in Set (0.00 sec) mysql> select * from Db11.score; +------+-----------+-------+
| ID | StudentID |
Score |   +------+-----------+-------+
|         1 |   1 |   100 | |      8 |    1002 |
99 | +------+-----------+-------+ 2 rows in Set (0.00 sec) do not use share join in MYCAT query: mysql> SELECT * FROM Student a inner Joi
N score b on A.id=b.studentid; +------+------+------+-----------+-------+
| ID | name | ID | StudentID |
score| +------+------+------+-----------+-------+
| 1002 |    Jiao |     8 |    1002 |
99 | +------+------+------+-----------+-------+ 1 row inSet (0.02 sec) only found the data in the DB11 partition. Using Share Join:mysql>/*!mycat:catlet=demo.catlets.sharejoin/select * FROM student a INNER join score B on A.id=b.stud
Entid; ERROR 1064 (HY000): Java.lang.ClassNotFoundException:demo.catlets.ShareJoin Share join is supported in the development version only, mine is Mycat 1.3, so it is not supported. If supported, you can find the matching three records, rather than one of the above.

--This article refers to the Official handbook,

Http://www.tuicool.com/articles/aUZVF3Q,

Http://blog.sina.com.cn/s/blog_638b7ebb0102vv80.html,

Http://www.cnblogs.com/lixiuran/p/4842888.html,