Mycat Basic concepts and read-write separation one

Installation and startup

Linux can download mycat-server-xxxxx.linux.tar.gz decompression in a directory, recommended in the Usr/local/mycat directory, note that the directory can not have spaces. Renamed after downloading, extract to/usr/server, project name Mycat, configure environment variables, and make it effective:

1 vi/etc/profile, increase in the system environment variable file

2 Make the environment variable effective

Source/etc/profile

[note] If you are a mycat cluster that is built on multiple Linux systems, you need to configure the mapping of other IP and hostname on the server where Mycat server resides, as follows: Vi/etc/hosts
For example: I have 4 machines, configured as follows:
IP Host Name:

192.168.0.1 server_1 
192.168.0.2 server_2 
192.168.0.3 server_3 

3) Start Mycat

./mycat Start

4) Connection Mycat

Mycat default use port 8066, management port 9066

MYSQL-UROOT-P123456-P9066-H127.0.0.1-DTESTDB username password is configured in Server.xml, or User/user
mysql-uroot-p123456-p8066- h127.0.0.1

Where-u,-p,-h is the username, password and host,-D is the logical library of the connection.
Mycat Directory Introduction

1 Bin directory: Linux run:./mycat console, first of all, to chmod +x *

Note: Mycat supported command {console | start | stop | restart | status | dump}

2) Conf directory to store the configuration file, Server.xml is the Mycat server parameter adjustment and user authorization configuration file, Schema.xml is the logical library definition and table as well as the fragment definition configuration file, Rule.xml is the fragment rule configuration file, the fragment rule specific parameter information is stored separately as the file, also in this directory, the configuration file modifies, needs  Reboot the Mycat or pass the 9066 port reload. 3 log stored in the Logs/mycat.log, a daily file, log configuration is in the Conf/log4j.xml, according to their own needs, you can adjust the output level for the debug,debug level, will output more information, easy to troubleshoot problems.

4 Note: Linux deployment install MySQL, the default does not ignore table name case, need to manually to/etc/my.cnf under the configuration Lower_case_table_names=1 so that the Linux environment MySQL ignore table name case, Otherwise, you will be prompted to not find the table error when using Mycat. Mycat Three most important configuration files

First, Schema.xml

1. As one of the important configuration files in Mycat, Schema.xml manages mycat logical libraries, tables, partitioning rules, Datanode and DataSource. Understanding these configurations is a prerequisite for proper use of mycat.
Schema tags are used to define logical libraries in MYCAT instances, MYCAT can have multiple logical libraries, and each logical library has its own related configuration. The concept of a logical library is the same as the database in MySQL
Checksqlschema is set to false and the best way to provide a SQL SELECT * from Testdb.travelrecord statement is to not take testdb this field. Sqlmaxlimit set this value, Mycat the default will be the query to all the information displayed, resulting in too much output. Therefore, in normal use, it is recommended to add a value to reduce excessive data return.
2. The table label defines the logical table in Mycat, and all tables that need to be split need to be defined in this tag. The name defined in the same schema tag must be unique. The Database property specifies the real name of the databases, and you need to add a PrimaryKey to the logical table that corresponds to the primary key for the real table. The Rule property is used to specify the rules name to use for the logical table, and the rule name is defined in Rule.xml and must correspond to the value one by one of the Name property attribute in the Tablerule label. Type Property This property defines the type of logical table, and the current logical table has only two types, global table and normal table. Corresponding configuration: Global table: Globals, normal table: Do not specify all tables for which the value is Globla. With a global table, you can use join and increase read-write separation.
3, the Datanode label defines the data node in the Mycat, which is what we usually say the data fragment. A datanode tag is a separate data fragment.

Using the DB1 physical database on the LCH3307 database instance, this constitutes a data fragment, and finally, we use the name DN1 to identify the fragment.
The Datahost property is used to define which database instance the fragment belongs to, and the property value is a reference to the Name property defined on the Datahost label.
4, the Datahost property as the last label in the Schema.xml, the tag in the Mycat logical library is also as the lowest level of the label exists, directly define the specific database instance, read-write separation configuration and heartbeat statements.
1 Maxcon and Mincon Specify the maximum minimum connection for each read-write instance connection pool.
2) Balance load balancing type, the current value has 3 kinds: balance= "1", open read-write separation mechanism
3) Writetype load Balancing type, currently has 3 kinds of values:
1. Writetype= "0", all writes are sent to the configuration of the first Writehost, the first to hang the cut to still survive the second
4) Writehost, after reboot to switch after, the switch record in the configuration file: Dnindex.properties. The rest is not recommended
5) Heartbeat This label indicates the statement used for the heartbeat check with the back-end database.
6 Writehost tags, readhost tags complex look at the document
Multiple writehost and readhost can be defined within a datahost. However, if the specified back-end database is Writehost, then all readhost bound by this writehost will be unavailable. On the other hand, the Writehost downtime system is automatically detected and switched to the standby writehost.

Therefore, the balance= "1" In this configuration file means that hostS1 and hostS2, as stand by Writehost, will participate in the load balancing of the SELECT statement, which enables the master-slave read-write separation, switchtype= '- 1 ' means that when the main is dead, no automatic switching is made, that is, hostS1 and hostS2 will not be promoted to the main, still only provide read functionality. This avoids the possibility of writing data into the slave, after all, the simple MySQL master-slave cluster does not allow the data to be read into the slave unless it is configured with dual master.

Add MYCAT1 This library

<schema name= "MYCAT1" checksqlschema= "false" sqlmaxlimit= ">
	<!--auto sharding by ID (long)-->
	<table name= "food" datanode= "Dn1,dn2,dn3" rule= "Auto-sharding-long"/>
</schema>

The database should correspond to the actual databases instance:
<datanode name= "dn1" datahost= "Localhost1" database= "Zy_mycat1"/>
< Datanode name= "DN2" datahost= "Localhost1" database= "Zy_mycat2"/> <datanode name= "dn3" datahost= "
Localhost1 "database=" zy_mycat3/>
datahost is the key, the user name password to correspond with the actual database, or MYCAT is not launched. I only have one master configured here, no slave
<datahost name= "Localhost1" maxcon= "1000" mincon= "ten" balance= "1"
		  writetype= "0" Dbtype= "MySQL" dbdriver= "native" switchtype= "1"  slavethreshold= "M" >
	
 

 

Second, Server.xml

 
System This tag has all of the property tags nested in the configuration of the systems are related to the other include the definition of user name password, to specify access to fragmentation, etc., modify root user properties, increase MYCAT1 this library.


<user name= "root" >
	<property name= "password" >123456</property>
	<property name= " Schemas ">TESTDB,mycat1</property>
</user>
Reboot Mycat

./mycat Restart
Third, Rule.xml

 
The definition of the rules involved in splitting the table is defined in the Rule.xml. This file contains both the Tablerule and function tags. verifying read-write separation


CREATE TABLE food (ID bigint NOT NULL primary key,user_id varchar (MB), TravelDate DATE, fee decimal,days int);
INSERT into food (id,user_id,traveldate,fee,days)  values (1,@ @hostname, 20160101,100,10), (5000001,@ @hostname, 20160102,100,10), (10000001,@ @hostname, 20160103,100,10);
Shut down 172.23.29.111 of the MySQL service and found that the connection failed when inserting data, and the query did not affect it.
Reference: http://www.cnblogs.com/ivictor/p/5111495.html two articles