Mycat deployment and implementation of read/write separations

Mycat is MySQL middleware, formerly Ali famous Cobar,cobar in open source for a period of time, after nothing. So mycat carried up this banner, in the era of big data, its importance is increasingly manifested. This article is primarily about MYCAT deployment.

First, install Java

Because Mycat is developed in Java, it is necessary to install Java in the experimental environment, the official recommendation jdk1.7 and above version

The official Java Oracle is:

Http://www.oracle.com/technetwork/java/javase/archive-139210.html

Unzip the jdk-7u79-linux-x64.tar.gz file and configure the Java environment variables

# tar XVF jdk-7u79-linux-x64.tar.gz

# MV jdk1.7.0_79//usr/local/

Edit/etc/profile File

# Vim/etc/profile Add the following:

Export Java_home=/usr/local/jdk1.7.0_79export path= $JAVA _home/bin: $PATHexport classpath=.: $JAVA _home/lib/dt.jar:$ Java_home/lib/tools.jar

# Source/etc/profile--Make/etc/profile file effective

# java-version

Java version "1.7.0_79" Java (tm) SE Runtime Environment (build 1.7.0_79-b15) Java HotSpot (tm) 64-bit Server VM (Build 24.79- B02, Mixed mode)

Second, install MySQL

MySQL official as follows:

http://dev.mysql.com/downloads/mysql/

There are various versions, including RPM, binary, source package.

For the sake of convenience, I choose here is RPM package

# yum Install-y mysql-community-server-5.6.26-2.el5.x86_64.rpm

Start MySQL

#/etc/init.d/mysqld Start

Starting mysqld (via Systemctl):                           [  OK  ]

Create a password for the root account

# mysqladmin-u root password "123456"

Recommended for 123456, the following mycat configuration file Schema.xml will be used in the

Third, installation Mycat

Create a Mycat user and set a password

# Useradd Mycat

# passwd Mycat

Unzip the Mycat file

# tar XVF mycat-server-1.5-alpha-20151221110028-linux.tar.gz

# MV mycat//usr/local/

Set the owner and Mycat of the directory

# Chown-r mycat.mycat/usr/local/mycat/

Iv. Test Mycat

First create three databases on MySQL: db1,db2,db3.

mysql> CREATE DATABASE db1; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database DB2; Query OK, 1 row Affected (0.00 sec) mysql> CREATE database db3; Query OK, 1 row Affected (0.00 sec)

Start the Mycat service

# cd/usr/local/mycat/bin/

#./mycat Start

Starting mycat-server ...

See if the Mycat service is started

# Ps-ef |grep Mycat

Root       9640   7257  0 22:47 pts/3    00:00:00 grep--color=auto mycat

And it didn't start.

View Logs

# cd/usr/local/mycat/logs/

# Cat Wrapper.log

STATUS | Wrapper | 2016/01/07 22:44:23 | --Wrapper Started as Daemonstatus | Wrapper | 2016/01/07 22:44:23 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:25 | JVM exited while loading the Application.info | JVM 1 | 2016/01/07 22:44:25 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:29 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:29 | JVM exited while loading the Application.info | JVM 2 | 2016/01/07 22:44:29 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:34 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:34 | JVM exited while loading the Application.info | JVM 3 | 2016/01/07 22:44:34 | Error:Exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostException:My Sql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:38 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:38 | JVM exited while loading the Application.info | JVM 4 | 2016/01/07 22:44:38 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownstatus | Wrapper | 2016/01/07 22:44:42 | Launching a JVM ... ERROR | Wrapper | 2016/01/07 22:44:43 | JVM exited while loading the Application.info | JVM 5 | 2016/01/07 22:44:43 | Error:exception thrown by the agent:java.net.MalformedURLException:Local host name Unknown:java.net.UnknownHostExcept Ion:mysql-server1:mysql-server1:name or service not Knownfatal | Wrapper | 2016/01/07 22:44:43 | There were 5 failed launches in a row, each LastiNg less than seconds. Giving up. FATAL | Wrapper |   2016/01/07 22:44:43 | There may a configuration problem:please check the logs. STATUS | Wrapper | 2016/01/07 22:44:43 | <--Wrapper Stopped

Based on the error message, the host name is suspected to be unbound

Modify Hosts file, bind host name

# vim/etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost Localhost.localdomain Localhost6 localhost6.localdomain6192.168.244.144 Mysql-server1

Restarting the Mycat service

# cd/usr/local/mycat/bin/

#./mycat Start

This mycat normal start.

# Ps-ef |grep Mycat

Root      10725      1  0 22:54?        00:00:00/usr/local/mycat/bin/./wrapper-linux-x86-64/usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident= Mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=true wrapper.lockfile=/var/lock/subsys/ Mycatroot      10881   7257  0 22:55 pts/3 00:00:00    grep--color=auto mycat

The following is an example of Travelrecord, for inserting, querying, routing analysis and other basic operations.

First connect MySQL database with Mycat

# MYSQL-UTEST-PTEST-H127.0.0.1-P8066-DTESTDB

8066 is the Mycat listening port, similar to the 3306 port of MySQL, where-u,-p,-h is the user name, password and host, and-D is the logical library of the connection.

As for why these are, this is related to configuration files.

The red section indicates that the connection is mycat.

Create a Travelrecord table

CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int);

Inserting data

mysql> INSERT INTO Travelrecord (id,user_id,traveldate,fee,days)  values (1, ' Victor ', 20160101,100,10); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into Travelrecord (id,user_id,traveldate,fee,days)  values ( 5000001, ' Job ', 20160102,100,10); Query OK, 1 row Affected (0.00 sec) mysql> INSERT into Travelrecord (id,user_id,traveldate,fee,days)  values ( 10000001, ' Slow ', 20160103,100,10); Query OK, 1 row Affected (0.00 sec)

As to why the ID takes three values, this is related to the definition of autopartition-long.txt in the Conf directory, which mainly defines auto-sharding-long rules.

# range Start-end, data node index# k=1000,m=10000.0-500m=0500m-1000m=11000m-1500m=2

I mainly test the effect of sharding when the ID takes a different interval value.

Here's how the Shard works.

Mysql> SELECT * from db1.travelrecord;+----+---------+------------+------+------+| ID | user_id | TravelDate | Fee  | days |+----+---------+------------+------+------+|  1 | Victor  | 2016-01-01 |  |   |+----+---------+------------+------+------+1 row in Set (0.00 sec) mysql> Select * from db2.travelrecord;+-------- -+---------+------------+------+------+| ID      | user_id | traveldate | fee  | days |+---------+---------+------------+------+------+| 5000001 | Job     | 2016-01-02 |  |   |+---------+---------+------------+------+------+1 row in Set (0.00 sec) mysql> select * FROM db3.travelrecord;+--- -------+---------+------------+------+------+| ID       | user_id | traveldate | fee  | days |+----------+---------+------------+------+------+| 10000001 | Slow    | 2016-01-03 |  |   |+----------+---------+------------+------+------+1 row in Set (0.00 sec)

Sure enough, it was assigned to three different nodes.

If you want to see where the Mycat specifically assigns the data to, you can pass the route analysis.

The syntax is actually quite simple, that is, the SQL statement is preceded by the explain statement.

The following is based on the explain command to see which datanode the CREATE statement and INSERT statement are assigned to.

Mysql> Explain CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar (+), TravelDate DATE, fee deci mal,days int); +-----------+------------------------------------------------------------------------------------ -----------------------------------+| Data_node | SQL |+-- ---------+----------------------------------------------------------------------------------------------------- ------------------+| DN1 | CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int) | | DN2 | CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int) | | DN3 | CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int) |+-- ---------+-----------------------------------------------------------------------------------------------------------------------+3 rows in Set (0.00 sec) mysql> explain insert Into Travelrecord (id,user_id,traveldate,fee,days) VALUES (1, ' Victor ', 20160101,100,10), +-----------+-------------- --------------------------------------------------------------------------------+| Data_node | SQL |+-----------+--------------- -------------------------------------------------------------------------------+| DN1 | Insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (1, ' Victor ', 20160101,100,10) |+-----------+-------- --------------------------------------------------------------------------------------+1 Row in Set (0.01 sec) MySQL > Explain insert INTO Travelrecord (id,user_id,traveldate,fee,days) VALUES (5000001, ' Job ', 20160102,100,10); +------ -----+-------------------------------------------------------------------------------------------------+| Data_noDE | SQL |+-----------+------------ -------------------------------------------------------------------------------------+| DN2 | Insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (5000001, ' Job ', 20160102,100,10) |+-----------+----- --------------------------------------------------------------------------------------------+1 Row in Set (0.00 sec ) mysql> explain insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (10000001, ' Slow ', 20160103,100,10) ;+-----------+------------------------------------------------------------------------------------------------- --+| Data_node | SQL |+-----------+---------- -----------------------------------------------------------------------------------------+| DN3 | Insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (10000001, ' Slow ', 20160103,100,10) |+-----------+------------------------------------------------------------------------ ---------------------------+1 Row in Set (0.00 sec)

Summarize:

The configuration of the mycat is actually quite simple, the most important is familiar with the rules of each configuration file. The above user name, password, how to sub-library, are defined in the configuration file, follow-up, there is time to one by one detailed table.

For configuration files, the main following three conf directories need to be familiar.

Server.xml is a configuration file for Mycat server parameter tuning and user authorization

Schema.xml is a configuration file for logical library definitions and tables and shard definitions

Rule.xml is a configuration file for a shard rule

http://www.cnblogs.com/ivictor/p/5111495.html mycat for MySQL read-write separation

MySQL middleware before the advent, for MySQL master and slave cluster, if you want to implement its read and write separation, is generally implemented in the terminal, this brings a problem, that is, the database and program coupling is too high, if my database address changes, then I will also make corresponding changes to the terminal If the database is accidentally hung out, it also means that the program is not available, which is unacceptable to many applications.

The introduction of MySQL middleware is a good way to decouple the terminal and the database, so that the terminal only needs to focus on the address of the database middleware, without knowing how the underlying database provides the service.

As the hottest MySQL middleware, Mycat realizes that the read and write separation of MySQL master-slave cluster should be justified, and its configuration is quite simple.

Here, I use three instances to compose MySQL master-slave cluster, to verify the Mycat read and write separation function, in fact, a master from can be satisfied, the reason used three, is to verify the Mycat shard function.

The cluster consists of the following:

Role Host name host IP

Master Mysql-server1 192.168.244.145

Slave Mysql-server2 192.168.244.146

Slave Mysql-server3 192.168.244.144

Here, the test is done using the Travelrecord table.

First edit the Mycat configuration file Schema.xml, the configuration information about Datahost is as follows:

<datahost name= "Localhost1" maxcon= "$" mincon= "balance=" 1 "                writetype=" 0 "dbtype=" MySQL "dbdriver=" Native "Switchtype="-1 "  slavethreshold=" >                
In this, there are two parameters to note, balance and switchtype.
Among them, balance refers to the load balancer type, the current value has 4 kinds:
1. balance= "0", does not open the read and write separation mechanism, all read operations are sent to the currently available writehost.
2. Balance= "1", all readhost and stand by writehost participate in the load balancing of the SELECT statement, simply speaking, when the dual master dual slave mode (M1->S1,M2->S2, and M1 and M2 are mainly prepared), under normal circumstances, M2,S1,S2 all participate in load balancing of the SELECT statement.
3. Balance= "2", all read operations are randomly distributed on writehost, Readhost.
4. Balance= "3", all read requests randomly distributed to wiriterhost corresponding readhost execution, Writerhost does not bear reading pressure
Switchtype refers to the mode of switching, the current value also has 4 kinds:
1. switchtype= '-1 ' means no automatic switching
2. switchtype= ' 1 ' default value, indicating automatic switching
3. Switchtype= ' 2 ' is based on the state of MySQL master-slave synchronization to decide whether to switch, heartbeat statement for show slave status
4. Switchtype= ' 3 ' based on the switch mechanism of MySQL galary cluster (for cluster) (1.4.1), the heartbeat statement is show status like ' wsrep% '.
Therefore, the balance= "1" in the configuration file means that hostS1 and hostS2 as stand by Writehost will participate in load balancing of the SELECT statement, which enables master-slave read-write separation, switchtype= '-1 ' This means that when the master hangs up, it does not switch automatically, that is, hostS1 and hostS2 are not promoted and still provide only read functionality. This avoids the possibility of reading the 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.
Verifying read-Write separation
Here to verify that
Create a Travelrecord table
CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int);
Inserting data
mysql> INSERT INTO Travelrecord (id,user_id,traveldate,fee,days)  values (1,@ @hostname, 20160101,100,10); Query OK, 1 row affected, 1 Warning (0.02 sec) mysql> insert into Travelrecord (id,user_id,traveldate,fee,days)  Valu ES (5000001,@ @hostname, 20160102,100,10); Query OK, 1 row affected, 1 warning (0.01 sec)
In this case, a trickery method is used, that is, the host name of the current instance is inserted into the user_id, which can visually observe whether the read-write is detached and the Mycat shard function. The reason for this is that my current MySQL version -5.6.26 defaults to statement-based replication, which would be undesirable if row-based replication was used.
Querying data
Mysql> SELECT * from travelrecord;+---------+---------------+------------+------+------+| ID | user_id | TravelDate | Fee |       Days |+---------+---------------+------------+------+------+| 1 | Mysql-server2 |  2016-01-01 |   100 | 10 | | 5000001 | Mysql-server3 |  2016-01-02 |   100 | |+---------+---------------+------------+------+------+2 rows in Set (0.01 sec) mysql> select * from travelrecord;+ ---------+---------------+------------+------+------+| ID | user_id | TravelDate | Fee | Days |+---------+---------------+------------+------+------+| 5000001 | Mysql-server3 |  2016-01-02 |   100 |       10 | | 1 | Mysql-server2 |  2016-01-01 |   100 | |+---------+---------------+------------+------+------+2 rows in Set (0.02 sec) mysql> select * from travelrecord;+ ---------+---------------+------------+------+------+| ID | user_id | TravelDate | Fee | Days |+---------+---------------+------------+------+------+| 5000001 | Mysql-server3 | 2016-01-02 |   100 |       10 | | 1 | Mysql-server3 |  2016-01-01 |   100 | |+---------+---------------+------------+------+------+2 rows in Set (0.01 sec) mysql> select * from travelrecord;+ ---------+---------------+------------+------+------+| ID | user_id | TravelDate | Fee | Days |+---------+---------------+------------+------+------+| 5000001 | Mysql-server3 |  2016-01-02 |   100 |       10 | | 1 | Mysql-server3 |  2016-01-01 |   100 | |+---------+---------------+------------+------+------+2 rows in Set (0.01 sec) mysql> select * from travelrecord;+ ---------+---------------+------------+------+------+| ID | user_id | TravelDate | Fee |       Days |+---------+---------------+------------+------+------+| 1 | Mysql-server2 |  2016-01-01 |   100 | 10 | | 5000001 | Mysql-server2 |  2016-01-02 |   100 | Ten |+---------+---------------+------------+------+------+
From the above output, the following two points can be drawn:
First, the configuration has been implemented read and write separation, read out the data does not have the master node.
Second, the random distribution of mycat is not based on statement, that is, a SELECT statement queries one of the nodes, and the other SELECT statement queries another node. It is distributed against the slices, and the result of the same SELECT statement is returned with a different datanode.
In addition, information about read and write separations can be obtained from the Mycat log, assuming that the Mycat log level is Debug. Log related information is as follows:
Verify that the Mater is hung, slave can also provide read function
For MySQL master-slave cluster, our requirement is master hang up, slave also can provide read function.
Let's test it here.
First, artificially shutting down the main library
[[email protected] ~]#/etc/init.d/mysqld stop
Login Mycat
[Email protected] ~]# Mysql-utest-ptest-h127.0.0.1-p8066-dtestdb
Inserting data
mysql> INSERT INTO Travelrecord (id,user_id,traveldate,fee,days)  values (10000001,@ @hostname, 20160103,100,10 ); ERROR 1184 (HY000): Connection refusedmysql> select * from travelrecord;+---------+---------------+------------+--- ---+------+| ID      | user_id       | traveldate | fee  | days |+---------+---------------+------------+------+------+|       1 | Mysql-server2 | 2016-01-01 |  |   10 | | 5000001 | Mysql-server3 | 2016-01-02 |  |   |+---------+---------------+------------+------+------+2 rows in Set (0.02 sec)
Visible cannot insert data, but does not affect reading data.
At this point, mycat implementation of MySQL read-write separation deployment test completed.
Summarize:
1. In fact, the first configuration is the Readhost node, configured as follows:
<datahost name= "Localhost1" maxcon= "$" mincon= "balance=" 1 "                writetype=" 0 "dbtype=" MySQL "dbdriver=" Native "Switchtype="-1 "  slavethreshold=" >                
But this way there is a problem, that is, master hangs, Slave also can not provide services, and this violates the original purpose of MySQL master and slave cluster.
2. If transaction mode is turned on, set autocommit=0, the master node is read within the transaction, not from the node.
Http://www.cnblogs.com/ivictor/p/5131480.html
Mycat deployment and implementation of read/write separation (RPM)