2020-11-11mycat

Hits: 0

1.1 [Mycat] Features

Support SQL92 standard Support common SQL syntax of
MySQL, Oracle, DB2, SQL Server, [PostgreSQL]
and other DB Comply with MySQL native protocol, cross-language, cross-platform, cross-database general middleware agent.
Automatic failover based on heartbeat, support for read-write separation, MySQL master-slave, and galera cluster.
Support Galera for MySQL cluster, Percona Cluster or MariaDB cluster
based on Nio implementation, effectively manage threads and solve [high concurrency] problems.
Supports automatic routing and aggregation of multiple pieces of data, supports common aggregation functions such as sum, count, max, and supports cross-database paging.
Supports any join within a single database, supports cross-database 2-table join, and even caltlet-based multi-table join.
It supports the sharding strategy of global table and ER relationship, and realizes efficient multi-table join query.
Supports multi-tenancy scenarios.
Support for distributed transactions (weak xa).
Support for XA distributed transactions (1.6.5).
Support global serial number to solve the problem of primary key generation in distributed environment.
Sharding rules are rich, plug-in development, easy to expand.
Powerful web, command line monitoring.
The front-end is supported as a general proxy for MySQL, and the back-end JDBC mode supports Oracle, DB2, SQL Server, mongodb, and giant sequoia.
Support password encryption
Support service downgrade
Support IP whitelist
Support SQL blacklist, SQL injection attack interception
Support prepare precompile instructions (1.6)
Support non-heap memory (Direct Memory) aggregate computing (1.6)
Support PostgreSQL native protocol (1.6)
Support mysql and oracle stored procedures, out parameters, multiple result set returns (1.6)
Support zookeeper coordination master From switching, zk sequence, configuration zkization (1.6)
to support database sub-table (1.6)
cluster management based on ZooKeeper, online upgrade, expansion, intelligent optimization, big data processing (2.0 development version).

2.1 Deployment Steps 2.1.1

Upload myCat server

Upload path: /usr/local/src

2.1.2 Unzip Mycat

tar -xvf  Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz

Mobile installation files:

2.1.3 server.xml configuration file description

Note: The default port number is 8066 port

< user  name = "root" > 
        < property  name = "password" > root </ property > 
        <!--Same as the configuration in schema.xml Note the capitalization of the database --> 
        < property  name = "schemas" > jtdb </ property > 
    </ user >

    <user >
        <property >user</property>
        <property >jtdb</property>
        <property >true</property>
    </user>

2.1.3 schema.xml configuration

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

    <!--name attribute is a custom dataNode that represents the node information of the database jtdb represents the logic library --> 
    < schema  name = "jtdb"  checkSQLschema = "false"  sqlMaxLimit = "100"  dataNode = "jtdb" />

    <!--Define node name/node host/data name--> 
    < dataNode  name = "jtdb"  dataHost = "localhost1"  database = "jtdb" /> 
        <!--Parameter introduction UTF-8 Chinese error report--> 
        < !--balance 0 means that all read operations will be sent to the writeHost host -->   
        <!--1 means that all read operations will be sent to readHost and the idle master node --> 
        <!--writeType=0 all writes Operations are sent to the first writeHost host -->  
        <!--writeType=1 All write operations are sent to writeHost randomly --> 
        <!--dbType indicates the database type mysql/oracle--> 
        <!--dbDriver ="native" The fixed parameters remain unchanged --> 
        <!--switchType=-1 means no automatic switching, and the slave node will not automatically switch after the host goes down --> 
        <!--switchType=1 means it will switch automatically (default value) If the first master node goes down, Mycat will perform 3 heartbeat detections, and if there is no response for 3 times, it will automatically switch to the second master node --> 
        <!-- and will update /conf/ dnindex.The main node information localhost1=0 of the properties file represents the first node. Do not modify this file at will or there will be major problems --> 
    <dataHost 
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select 1</heartbeat>

        <!--Configure the first host to mainly perform database write operations. Under the default conditions, Mycat mainly operates the first host. The first host has achieved read-write separation. Because the default write operation will be sent to the database of 137 . The read operation is sent to 141 by default. If the slave node is busy, the master node shares part of the pressure.
        --> 
        < writeHost  host = "hostM1"  url = "192.168.126.129:3306"  user = "root"  password = "root" > 
            <!--read database 1--> 
            < readHost  host = "hostS1"  url = " 192.168.126.130:3306"  user = "root"  password = "root" /> 
            <!--read database2--> 
            < readHost  host = "hostS2"  url = "192.168.126.129:3306" user="root" password="root" />

        </writeHost>

            <!--Define the second host because the database has implemented dual-machine hot standby.--> 
            <!--Mycat achieves high availability. When the first host 137 goes down, mycat will automatically send out heartbeat detection. Detection 3 times.--> 
            <!--If the host 137 does not respond to Mycat, it is judged that the host is dead. Then the second host of Qidong will continue to provide services to users.--> 
            <!--If the host 137 recovers, it is waiting Status. If 141 is down, 137 will continue to provide services to users again.--> 
            <!--Premise: Realize dual-system hot backup.-->

        <!--<writeHost host="hostM2" url="192.168.126.130:3306" user="root" password="root">

            <readHost host="hostS1" url="192.168.126.130:3306" user="root" password="root" />

            <readHost host="hostS2" url="192.168.126.129:3306" user="root" password="root" />
        </writeHost>-->

    </dataHost>
</mycat:schema>

2.1.4 Implement configuration file upload

2.1.5 Mycat command

Usage: ./mycat {console | start | stop | restart | status | dump}

2.1.6 Check mycat logs

2.1.7 Mycat load balancing test

Modify the database in the slave library. Refresh the list page. Check whether there is a load balancing effect.
Note: If the test is completed, remember to make the data modification consistent.

3.1. Realize database high availability

3.2 Building strategy

3.2.1 Problem description
Description: If the database agent is implemented according to the following configuration, if the main database of the database is down, it will directly affect the execution of the entire program. Therefore, it is necessary to implement a high availability mechanism.
The problem of high availability implementation:
if the database high If available, the database can be automatically switched. Since the user directly operates the slave database, when the master database is started and the data is found to be inconsistent, the master-slave synchronization state will be terminated.

3.3 Dual-system hot backup (dual-master mode)

Note: The database high availability can be achieved through the database dual-primary mode.
The dual-primary mode is essentially a host that backs up each other.

3.4 Implementation of dual-machine hot backup

Previous configuration:
Host: 192.168.126.129
Slave: 192.168.126.130
Current configuration:
Host: 192.168.126.130
Slave: 192.168.126.129

1). Check the main library status
2). Realize database master-slave construction

3.5 Database High Availability Implementation

1). Edit the configuration file

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

    <!--name attribute is a custom dataNode that represents the node information of the database jtdb represents the logic library --> 
    < schema  name = "jtdb"  checkSQLschema = "false"  sqlMaxLimit = "100"  dataNode = "jtdb" />

    <!--Define node name/node host/data name--> 
    < dataNode  name = "jtdb"  dataHost = "localhost1"  database = "jtdb" /> 
        <!--Parameter introduction UTF-8 Chinese error report--> 
        < !--balance 0 means that all read operations will be sent to the writeHost host -->   
        <!--1 means that all read operations will be sent to readHost and the idle master node --> 
        <!--writeType=0 all writes Operations are sent to the first writeHost host -->  
        <!--writeType=1 All write operations are sent to writeHost randomly --> 
        <!--dbType indicates the database type mysql/oracle--> 
        <!--dbDriver ="native" The fixed parameters remain unchanged --> 
        <!--switchType=-1 means no automatic switching, and the slave node will not automatically switch after the host goes down --> 
        <!--switchType=1 means it will switch automatically (default value) If the first master node goes down, Mycat will perform 3 heartbeat detections, and if there is no response for 3 times, it will automatically switch to the second master node --> 
        <!-- and will update /conf/ dnindex.The main node information localhost1=0 of the properties file represents the first node. Do not modify this file at will or there will be major problems --> 
    <dataHost 
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select 1</heartbeat>

        <!--Configure the first host to mainly perform database write operations. Under the default conditions, Mycat mainly operates the first host. The first host has achieved read-write separation. Because the default write operation will be sent to the database of 137 . The read operation is sent to 141 by default. If the slave node is busy, the master node shares part of the pressure.
        --> 
        < writeHost  host = "hostM1"  url = "192.168.126.129:3306"  user = "root"  password = "root" > 
            <!--read database 1--> 
            < readHost  host = "hostS1"  url = " 192.168.126.130:3306"  user = "root"  password = "root" /> 
            <!--read database2--> 
            < readHost  host = "hostS2"  url = "192.168.126.129:3306" user="root" password="root" />

        </writeHost>

            <!--Define the second host because the database has implemented dual-machine hot standby.--> 
            <!--Mycat achieves high availability. When the first host 137 goes down, mycat will automatically send out heartbeat detection. Detection 3 times.--> 
            <!--If the host 137 does not respond to Mycat, it is judged that the host is dead. Then the second host of Qidong will continue to provide services to users.--> 
            <!--If the host 137 recovers, it is waiting Status. If 141 is down, 137 will continue to provide services to users again.--> 
            <!--Premise: Realize dual-system hot backup.-->

        <writeHost host="hostM2" url="192.168.126.130:3306" user="root" password="root">

            <readHost host="hostS1" url="192.168.126.130:3306" user="root" password="root" />

            <readHost host="hostS2" url="192.168.126.129:3306" user="root" password="root" />
        </writeHost>

    </dataHost>
</mycat:schema>

2). Upload configuration file:
3). Restart the mycat server

3.6 Database High Availability Test

  1. Start the server and test whether the mysql database is normal.
  2. Close the main database database and check again whether the user’s operation is normal.
    3). Modify the content in the database, restart the main database, and check whether the data is synchronized

3.7 Check Port Occupancy

4.1.1 Redis

Redis is an open source (BSD licensed) in-memory data structure store used as a database, cache and message broker. It supports data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperlogs, geospatial indexes with radius queries and streams. Redis has built-in replication, Lua scripts, LRU eviction, transactions, and different levels of disk persistence, and provides high availability through Redis Sentinel and Redis Cluster automatic partitioning.
Website: Redis.cn
Put the installation package into the usr/local/src directory
tar -xvf redis-5.0.4.tar.gz
mv redis-5.0.4.tar.gz software/
mv redis-5.0.4 redis
cd redis/
make
makeinstall
2.3. 4
Command:
g Change the redis.conf document (three changes)
Start the command
redis-server redis.conf to
view the process
ps -ef |grep redis
enter the client
redis-cli -p 6379
exit the client
exit/ctrl+c
close the client
kill -9 command
redis-cli -p 6379 shutdown

4.1.2 Redis installation

1). Unzip the redis file
2). Move the file/modify the file
3). Install the Redis
command 1: make
command 2: make install

4.1.3 Redis commands

  1. Start redis redis-server redis.conf
  2. Check the redis service item
  3. Enter the redis client

redis-cli  -p 6379

  1. Shut down redis
    1). Command redis-cli -p 6379 shutdown
    2). Kill command kill -9 pid number

5.1 SpringBoot integrates Redis

5.1.1 Import jar package

        <dependency>
            <groupId>redis.clients</groupId>
            <artifactId>jedis</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-redis</artifactId>
        </dependency>

5.1.2 Getting Started Case

package com.jt;

import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import redis.clients.jedis.Jedis;
import redis.clients.jedis.params.SetParams;

public class TestRedis {
    /**
     * 1. Implement redis test
     * Error checking:
     * 1. Check redis.conf configuration file 1. IP binding problem 2. Protected mode problem 3. Background startup problem
     * 2. Check the redis startup mode redis-server redis.conf
     * 3. Check the firewall
     *      */
    @Test
    public void test01(){
        Jedis jedis = new Jedis("192.168.126.129",6379);
        jedis.set ( "2007" , " Redis entry case" );
        System.out.println(jedis.get("2007"));
    }

    /**
     * I want to judge whether there is key data, if not, add data, if there is, give up adding new */ 
    @ Test
     public  void  test02 (){
        Jedis jedis = new Jedis( "192.168.126.129" , 6379 );
 // if(!jedis.exists("2007")){ //Check whether the data exists. 
// jedis.set("2007", "Test case 2222"); 
// } 
        //setnx function: If there is data, it will not be processed. 
        jedis.setnx( "2007" , "Test advanced usage" );
        System.out.println(jedis.get("2007"));

    }

    /**
     * need:
     * Add a data.set-key-value to redis, which requires adding a timeout of 100 seconds.
     * Hide bugs: During code execution, if an error is reported, the deletion may fail.
     * Atomicity: either succeed at the same time, or fail at the same time.
     * Solution: Set the storage operation/timeout time at the same time. setex
     */
    @Test
    public void test03() throws InterruptedException {
        Jedis jedis = new Jedis( "192.168.126.129" , 6379 );
         //jedis.set("2007", "test time"); 
        //Hidden meaning: the business needs to delete data at expiration 
        //jedis.expire("2007 ", 100); 
        jedis.setex( "2007" , 100 , "Test Time" );

    }

    /**
     * 1. If the data exists, do not operate the data setnx
     * 2. Set the timeout time at the same time, pay attention to the atomic setex
     * Parameter Description:
     * 1. XX = "xx"; only if the key exists, the operation will be performed
     * 2. NX = "nx"; no key, write operation
     * 3. PX = "px"; milliseconds
     */
    @Test
    public void test04() throws InterruptedException {
        Jedis jedis = new Jedis("192.168.126.129",6379);
        SetParams setParams = new SetParams();
        setParams.xx().ex(100);
        jedis.set("2007", "bbbbb",setParams);
        System.out.println(jedis.get("2007"));
    }
}

2.3.3 Building the project structure

You may also like...

Leave a Reply

Your email address will not be published.