linux-mycat-installation Configuration

Mycat Introduction

Enterprise application Development "big database Cluster" Support transaction, ACID, can replace MySQL's enhanced version of the database
An enterprise-class database that can be considered a "Mysql" cluster to replace expensive Oracle clusters
A new type of SQL Server that incorporates memory caching technology, NoSQL technology, and HDFs big Data
A new generation of enterprise database products combined with traditional database and new distributed Data Warehouse
Mycat's goals are:
Low-cost, smooth migration of existing stand-alone databases and applications to the "cloud" side, addressing data bottlenecks in the context of rapidly growing data storage and business scale.

Key features of the Mycat

Supports SQL 92 standard
Support MySQL cluster, can be used as proxy
Support JDBC to connect Oracle, DB2, SQL Server, and emulate it as MySQL? Server uses
Supports Galera for MySQL cluster, percona-cluster or MARIADB cluster, providing high-availability data shard clusters
Automatic failover, high availability
Supports read/write separation, supports MySQL dual master multi-slave, and a master multi-slave mode
Supports global tables, data is automatically fragmented to multiple nodes for efficient table association queries
Support unique partition strategy based on E-r relationship, realize efficient Table association query
Multi-platform support, simple deployment and implementation

Mycat Architecture

Mycat High-availability parameters

For Mycat high-availability schema.xml configuration parameters related instructions:
Balance property?
Load balancing type, the current value has the following 4 kinds.

*?balance= "0"?: does not open the read-write separation mechanism, all read operations are sent to the currently available writehost.

*?balance= "1"?: All Readhost and Stand by writehost participate in load balancing of the SELECT statement, in short, when the dual-master dual-slave mode (M1->S1,M2->S2, and M1 and M2 are primarily prepared), Under normal circumstances, M2, S1, and S2 all participate in load balancing of the SELECT statement.

*?balance= "2"?: All read operations are randomly distributed on writehost, Readhost.

*?balance= "3"?: all read requests are distributed randomly to the writehost corresponding readhost, and the writehost does not load the read pressure.
Writetype property?
There are two types of load balancer type that are currently being evaluated.

*?writetype= "0"?: All write operations are sent to the configuration of the 1th Writehost, writehost hanging is cut to writeHost2, re-restore the WriteHost1 node, will not be cut back, or WRITEHOST2 as the standard , toggle record in Profile dnindex.properties.?

*?writetype= "0"?: All write operations are sent randomly to the configured Writehost, which is no longer recommended for Mycat1.5 versions.
Switchtype property?

*?switchtype= "-1"?: Indicates no automatic switching.

*?switchtype= "1"?: is the default value (or 0), which means automatic switching.

*?switchtype= "2"?: Indicates whether the switch is based on the state of MySQL master-slave synchronization, the heartbeat statement is as follows:
Show slave status

Switchtype= "3"?: Represents the switch mechanism based on the MySQL galary cluster, the heartbeat statement is as follows:
Show status like ' wsrep% '

MySQL master-slave read/write separation environment
Project Mycat Mysql-master Mysql-slavea MYSQL-SLAVEB
IP 192.168.32.149 192.168.32.146 192.168.32.129 192.168.32.130
Port 8066/9066 3306 3306 3306

Java installation package:
Http://www.oracle.com/technetwork/java/javase/downloads/jdk10-downloads-4416644.html
Mycat installation package:
http://www.mycat.io/

Unzip to directory/usr/local/

#cd/usr/local/

#tar ZXF jdk-10.0.1_linux-x64_bin.tar.gz
#tar ZXF mycat-server-1.6-release-20161028204710-linux.tar.gz

#/usr/local/mycat/bin/mycat start
#ss-LNT | grep mycat

#cd mycat/conf/

Configure Server.xml First

XXX for Logical Library

#vim Server.xml

<user name= "root" >
<property name= "Password" >123456</property>
<property name= "Schemas" >TESTDB,aaa,bbb</property>

Configure schema

#vim Schema.xml

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

    <!-- 逻辑库配置 DB1,DB2是逻辑库并不是真的数据库-->      <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/>     <schema name="aaa" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"/>     <schema name="bbb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"/>     <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"             /> -->

<!--node configuration db1,db2,db3 is the real database (need to be created at MySQL Master)--
<datanode name= "dn1" datahost= "Localhost1" database= "DB1"/>
<datanode name= "DN2" datahost= "Localhost1" database= "DB2"/>
<datanode name= "Dn3" datahost= "Localhost1" database= "db3"/>
<!--<datanode name= "Dn4" datahost= "sequoiadb1" database= "SAMPLE"/>

<datanode name= "jdbc_dn1" datahost= "Jdbchost" database= "DB1"/>
<datanode name= "jdbc_dn2" datahost= "Jdbchost" database= "DB2"/>
<datanode name= "Jdbc_dn3" datahost= "Jdbchost" database= "db3"/>--

<datahost name= "Localhost1" maxcon= "+" mincon= "balance=" 1 "
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">"
<!--can have multi write hosts--
<!--Note: Writehost host= is the main MySQL readhost host= from MySQL--
<writehost host= "hostM1" url= "192.168.32.146:3306" user= "root" password= "123456" >
<!--can have multi read hosts--
<!--can configure it to read multiple libraries--
<readhost host= "hostS2" url= "192.168.32.129:3306" user= "root" password= "123456"/>
</writeHost>
<!--main fault, replace the write node, the main normal is to share read pressure--
<writehost host= "hostS1" url= "localhost:3316" user= "root"
password= "123456"/>

#/usr/local/mycat/bin/mycat restart
#mysql-u root-p123456-p 8066-h 127.0.0.1

mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
| AAA |
| BBB |
+----------+
3 Rows in Set (0.00 sec)

Mysql> use AAA;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed

Mysql> Show tables;
+---------------+
| TABLES_IN_DB2 |
+---------------+
| BBB |
+---------------+
1 row in Set (0.00 sec)

Mysql> INSERT into BBB values (1, ' Lijialiang ');
Query OK, 1 row affected (0.04 sec)

MYSQL-SLAVEB:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| DB1 |
| DB2 |
| DB3 |
| MySQL |
| Performance_schema |
| SYS |
+--------------------+
7 rows in Set (0.10 sec)

Mysql> use DB2;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> Show tables;
+---------------+
| TABLES_IN_DB2 |
+---------------+
| BBB |
+---------------+
1 row in Set (0.01 sec)

Mysql> select * from BBB;
+------+------------+
| ID | name |
+------+------------+
| 1 | Lijialiang |
+------+------------+
1 rows in Set (0.00 sec)

linux-mycat-installation Configuration