From: http://blog.csdn.net/u011325787/article/details/51423424

mycat is a distribution invented by a popular Chinese recently data base Middleware, which is developed on the basis of Ali's cobar

We need to configure it before we build it. MySQL Master-slave duplication, this process is very long, I do not write here, interested can see this article I wrote.

Construction of master-slave replication environment for mysql database under linux centos

The role of mycat in applications can be seen in the following figure

mycat allows programmers to only care about the writing of business code, without worrying about the load balance of back-end database cluster, the separation of reading and writing, the compilation of data fragmentation logic of sub-database and sub-table, as long as they connect mycat directly.

First we prepare a clean centos machine and install jdk

Unzip the installation package of mycat to / user/local / below

Setting environment variables for mycat

1. vi /etc/profile  

Make the configuration file take effect immediately

1. source /etc/profile  

Enter mycat's configuration file directory

1. cd /usr/local/mycat/conf/  

1. vi schema.xml  

This configuration file is mainly used to configure database nodes, logical tables, and so on.

1. <?xml version="1.0"?>  
2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
3. <mycat:schema xmlns:mycat="http://org.opencloudb/">  
4.       
5.     <!-- Definition MyCat Logic library -->  
6.     <schema name="pcx_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="pcxNode"></schema>  
7.   
8.     <!-- Definition MyCat Data Node -->  
9.     <dataNode name="pcxNode" dataHost="dtHost" database="pcx" />  
10.   
11.       
12.     <!-- Define data host dtHost，connection to MySQL Read-Write Separation Cluster ,schema Each of them dataHost Medium host Attribute values must be unique-->  
13.     <!-- dataHost In fact, the configuration is the database cluster in the background, one datahost Represents a database cluster -->  
14.     <!-- balance="1"，All-out readHost and stand by writeHost participate in select Statement load balancing-->  
15.     <!-- writeType="0"，All write operations are sent to the first configuration writeHost，This is ours. hostmaster，The first hung until the second survived. writeHost-->  
16.     <dataHost name="dtHost" maxCon="500" minCon="20" balance="1"  
17.         writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">  
18.         <!--runtastic Heart Rate PRO -->  
19.         <heartbeat>show slave status</heartbeat>  
20.           
21.         <!--Configuring the background database IP Address and port number, and account password -->  
22.         <writeHost host="hostMaster" url="192.168.1.6:3306" user="root" password="root" />  
23.         <writeHost host="hostSlave" url="192.168.1.7:3306" user="root" password="root" />  
24.     </dataHost>  
25.       
26.   
27. </mycat:schema>  

Next, configure user privileges, system variables

1. vi server.xml  

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <!-- - - Licensed under the Apache License, Version 2.0 (the "License");   
3.     - you may not use this file except in compliance with the License. - You   
4.     may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0   
5.     - - Unless required by applicable law or agreed to in writing, software -   
6.     distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT   
7.     WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the   
8.     License for the specific language governing permissions and - limitations   
9.     under the License. -->  
10. <!DOCTYPE mycat:server SYSTEM "server.dtd">  
11. <mycat:server xmlns:mycat="http://org.opencloudb/">  
12.     <system>  
13.     <!-- Here are some system attributes that you can view by yourself. mycat File -->  
14.     <property name="defaultSqlParser">druidparser</property>  
15.     <property name="charset">utf8mb4</property>  
16.     </system>  
17.   
18.       
19.                               
20.     <!-- User 1, corresponding MyCat Host-slave replication cluster corresponding to data nodes connected by logical database -->  
21.     <user name="user1">  
22.         <property name="password">root</property>  
23.         <property name="schemas">pcx_schema</property>  
24.     </user>  
25.                               
26.     <!-- User 2, read-only permission-->  
27.     <user name="user2">  
28.         <property name="password">root</property>  
29.         <property name="schemas">pcx_schema</property>  
30.         <property name="readOnly">true</property>  
31.     </user>  
32.   
33. </mycat:server>  

Modify the firewall to allow mycat ports to be accessed from outside

1. vi /etc/sysconfig/iptables  

1. -A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT  
2. -A INPUT -m state --state NEW -m tcp -p tcp --dport 9066 -j ACCEPT  

Then restart the firewall

1. service iptables restart  

1. cd /usr/local/mycat/bin/  

Enter mycat's script directory

Run Start Command

1. ./mycat start  

We can use mysql client connection or navicat to connect mycat

Next we go test Read-write separation

Enter mycat's log directory

1. cd /usr/local/mycat/logs/  

First test the read operation

Let's connect to mycat and send a select * command.

You can see that the select operation is routed to 192.168.1.7, our slave node.

So let's do it many times.

The result is still routed to the read node

Next, let's test the write operation.

Visible inserts are routed to master nodes

Finally, let's see if master's data is synchronized to slave

The successful synchronization of records shows that the separation of reading and writing is successful.