Vi. MySQL cluster-mycat

Key characteristics of Mycat key Features

• Support SQL92 Standard

• Common SQL syntax support for MySQL, Oracle, DB2, SQL Server, PostgreSQL, etc. db

• Adhere to MySQL native protocol, cross-language, cross-platform, cross-database generic middleware agent.

• Automatic heartbeat-based failover, supports read and write separations, supports MySQL master and slave, and galera cluster clusters.

• Supports Galera for MySQL cluster, Percona cluster or MARIADB cluster

• Based on NIO implementation, effectively manage threads and solve high concurrency problems.

• Supports multi-slice automatic routing and aggregation of data, supports common aggregation functions such as Sum,count,max, and supports cross-library paging.

• Supports arbitrary joins within a library, supports cross-Library 2 table joins, and even multi-table joins based on Caltlet.

• The Multi-table join query is implemented by using the global table and ER relation's sharding strategy.

• Supports multi-tenancy scenarios.

• Supports distributed transactions (weak XA).

• Supports XA distributed transactions (1.6.5).

• Support global serial number, solve the problem of primary key generation under distributed.

• Fragmented rules are rich, plug-in development, easy to expand.

• Powerful Web, command-line monitoring.

• Support Front end as MySQL general agent, back-end JDBC Mode support Oracle, DB2, SQL Server, MongoDB, Giant fir.

• Support for password encryption

• Support Service downgrade

• IP Whitelist Support

• Support for SQL blacklist, SQL injection * * * interception

• Support for prepare pre-compilation instructions (1.6)

• Support Non-heap memory (Direct memory) aggregation calculations (1.6)

• Support for PostgreSQL native Protocol (1.6)

• Supports MySQL and Oracle stored procedures, out parameters, multi-result set returns (1.6)

• Support Zookeeper coordinate master-slave switchover, ZK sequence, configuration ZK (1.6)

• Support in-Library sub-tables (1.6)

• The cluster is based on zookeeper management, online upgrade, expansion, intelligent optimization, Big Data Processing (2.0 development version).

What is Mycat

• A fully open source, large database cluster for enterprise application development

• Support for transactional, ACID, and MySQL-replaceable, enhanced databases

• An enterprise-class database that can be viewed as a MySQL cluster to replace expensive Oracle clusters

• A new 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

• A new database middleware product

Installing MYCAT for load read-write separation

Hosts configuration 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 mycat::1 localhost localhost    . localdomain localhost6 localhost6.localdomain6172.16.1.55 yum172.16.1.200 master172.16.1.105 slave172.16.1.130 Mycat

Installing jdk1.7

Http://www.oracle.com/technetwork/java/javase/downloads/java-archive-downloads-javase7-521261.htmlmkdir/javacd /javatar XF jdk-7u80-linux-x64.tar.gzchown-r root:root/java/jdk1.7.0_80cat << EOF >>/etc/profileexport Path=/java/jdk1.7.0_80/bin: $PATHexport classpath=.:/ Java/jdk1.7.0_80/lib/dt.jar: $JAVA _home/lib/tools.jareofsource/etc/profilecd/application/tools/wget/http Dl.mycat.io/1.6-release/mycat-server-1.6-release-20161028204710-linux.tar.gztar XF MYCAT-SERVER-1.6-RELEASE-20161028204710-LINUX.TAR.GZMV mycat//application/

Setting environment variables for Mycat

echo "Export Mycat_home=/application/mycat" >>/etc/profileecho "Export path= $PATH:/application/mycat/bin" > >/etc/profilesource/etc/profile

cd /application/mycat/conf/vi schema.xml  <! doctype mycat:schema system  "Schema.dtd" ><mycat:schema xmlns:mycat= "http://io.mycat/ >        <!--define logical libraries and logical tables for MYCAT instances-->         <schema name= "appdb"  checksqlschema= "false"  sqlmaxlimit= " " Datanode= "Master,master2,slave" >  </schema>         <!--define the real host for all the Datanode mentioned in the Mycat logical table, and the real database to which Datanode belongs-->         <datanode name= "Dn_test"  datahost= "Dn85"  database= "appdb"  />         <!--defines the number of connection limits, load balancing orientations, and real read/write addresses (Writehost and readhost) of the datahost mentioned by Datanode         <!--Balance Properties   Load balancing type, there are currently 3 types of values-->         <!--balance= "0",  all read operations are sent toCurrently available writehost on-->        <!--balance= "1", all read operations are sent randomly to readhost-- >        <!--balance= "2", all read operations are randomly distributed on writehost, Readhost                 <!-- Writetype Properties   Load Balancing type, currently has 3 kinds of-->        <!--writetype= "0",   All write operations are sent to the available writehost-->        <!--writetype= "1", All write operations are sent randomly to readhost-->        <!--writetype= "2", All write operations are randomly writehost, readhost-->                 <!--switchtype= " -1,1,2,3"   whether master-slave switching is enabled-->         <!--switchtype= '-1 '   indicates that-->        < is not automatically switched!-- Switchtype= ' 1 '   default value, which means auto-switch-->        <!--switchtype= ' 2 '   based on MySQL master-slave synchronization state determines whether to switch, heartbeat statement is  show slave  status-->        <!--switchtype= ' 3 ' based on Mysql galary  cluster switching mechanism (for cluster) (1.4.1), Heartbeat statement for  show status like  ' wsrep% '-->         <datahost name= "Dn85"  maxcon= " mincon=" 3 " balance="- 1 "         writetype=" 0 " dbtype=" MySQL " dbDriver=" Native " switchtype=" 2 "  slavethreshold=" ><!--Real Read and write address-->                 

Create a user in the database, Mycat will have this user to connect to the database
Create user [email protected] '% ' identified by ' mtls0352 ', create database appdb char set utf8;grant all on appdb.* to [EMA Il protected] '% ';

Open Mycat
Mycat start

View ports
Netstat-tnlptcp *:9066 (LISTEN) #虚拟schema管理端口 TCP *:8066 (LISTEN) #虚拟schema登陆端口

 Login Mycat Management side:  
 
mysql -uappuser -p123455 -h172.16.1.130 -p9066mysql> show @ @heartbeat; MySQL > show @ @heartbeat, +--------+-------+--------------+------+---------+-------+--------+---------+- -------------+---------------------+-------+| name   | type  | host          | port | rs_code | retry |  STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME     | stop  |+--------+-------+--------------+------+---------+-------+--------+---------+--------- -----+---------------------+-------+| 200_m  | mysql | 172.16.1.200 |  3306 |       1 |     0 | idle    |       0 | 23,23,23      | 2018-05-29 22:42:18 | false | |  105_S1 | mysql | 172.16.1.105 | 3306 |        1 |     0 | idle   |        0 | 75,75,75     | 2018-05-29 22:42:18  | false |+--------+-------+--------------+------+---------+-------+--------+---------+-------- ------+---------------------+-------+2 rows in set  (0.00 sec)


Mycat read-Write separation verification

Change the output mode of the log vi/application/mycat/conf/log4j.xml modify <asyncroot level= "info" includelocation= "true" > for < Asyncroot level= "Debug" includelocation= "true" >

Create a table
Mysql-uappuser-p123456-h172.16.1.130-p8066create table Test (ID bigint NOT NULL primary key,user_id varchar, date DATE, fee decimal), insert into Test (Id,user_id,date,fee) VALUES (1,@ @hostname, 20161201,100), insert INTO test (id,user_ Id,date,fee) VALUES (5000001,@ @hostname, 20161202,100); select * from Test;

Querying for changes in the observation log
select * from testtail -f /application/mycat/logs/wrapper.log [[email  protected] logs]# cat  wrapper.log  | grep  "select read  Source 105_s1 "info   | jvm 1    | 2018/05/29  22:46:13 | 2018-05-29 22:46:13,624 [debug][$_nioreactor-0-rw] select read  source 105_s1 for datahost:dn85   (Io.mycat.backend.datasource.PhysicalDBPool: physicaldbpool.java:456)  info   | jvm 1    | 2018/05/29  22:46:34 | 2018-05-29 22:46:34,026 [debug][$_nioreactor-0-rw] select read  source 105_S1 for dataHost:dn85   (Io.mycat.backend.datasource.PhysicalDBPool: physicaldbpool.java:456)  info   | jvm 1    | 2018/05/29  22:46:34 | 2018-05-29 22:46:34,550 [debug][$_nioreactor-0-rw] select read source 105_s1 for  dataHost:dn85   (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info    | jvm 1    | 2018/05/29 22:46:34 |  2018-05-29 22:46:34,914 [debug][$_nioreactor-0-rw] select read source 105_s1  for dataHost:dn85   (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info   | jvm 1    | 2018/05/29 22:46:35 |  2018-05-29 22:46:35,225 [debug][$_nioreactor-0-rw] select read source 105_ s1 for datahost:dn85   (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info   | jvm 1    | 2018/05/29 22:46:35 |  2018-05-29 22:46:35,546 [debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85    (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info   |  jvm 1    | 2018/05/29 22:46:35 | 2018-05-29  22:46:35,877 [debug][$_nioreactor-0-rw] select read source 105_s1 for  datahost:dn85   (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  INFO    | jvm 1    | 2018/05/29 22:46:36 | 2018-05-29  22:46:36,237 [debug][$_nioreactor-0-rw] select read source 105_s1 for  dataHost:dn85   (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info    | jvm 1    | 2018/05/29 22:46:36 |  2018-05-29 22:46:36,559 [debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85   ( io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456) &NBSP;INFO&NBSP;&NBSP;&NBSP;|&NBSP;JVM  1    | 2018/05/29 22:46:36 | 2018-05-29 22:46:36,892 [ debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85    (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  INFO   |  jvm 1    | 2018/05/29 22:46:37 | 2018-05-29 22:46:37,199  [debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85    (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info   |  jvm 1    | 2018/05/29 22:46:37 | 2018-05-29  22:46:37,523 [debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85   ( io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456) &NBSP;INFO&NBSP;&NBSP;&NBSP;|&NBSP;JVM  1    | 2018/05/29 22:46:37 | 2018-05-29 22:46:37,840 [ debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85    (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  INFO   |  jvm 1    | 2018/05/29 22:46:38 | 2018-05-29 22:46:38,153  [debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85    (io.mycat.backend.datasource.physicaldbpool:physicaldbpool.java:456)  info   |  jvm 1    | 2018/05/29 22:46:38 | 2018-05-29  22:46:38,477 [debug][$_nioreactor-0-rw] select read source 105_s1 for datahost:dn85   (Io.mycat.backend.datasource.PhysicalDBPool: physicaldbpool.java:456)  info   | jvm 1    | 2018/05/29  22:46:38 | 2018-05-29 22:46:38,831 [debug][$_nioreactor-0-rw] select read  source 105_S1 for dataHost:dn85   (Io.mycat.backend.datasource.PhysicalDBPool: physicaldbpool.java:456)  info   | jvm 1    | 2018/05/29  22:46:39 | 2018-05-29 22:46:39,193 [debug][$_nioreactor-0-rw] select read  source 105_S1 for dataHost:dn85   (Io.mycat.backend.datasource.PhysicalDBPool: physicaldbpool.java:456)  [[email protected] logs]# cat  wrapper.log  |  grep  "SELECT&NBSP;READ&NBSP;SOURCE&NBSP;105_S1" &NBSP;|&NBSP;WC&NBSP;-L17 conducted 17 query results proving that the read-write separation configuration was successful

Vi. MySQL cluster-mycat