Beijing Taobao project Day11

Time:2021-11-30

1. Database high availability implementation

1.1 principle of database read-write separation

Beijing Taobao project Day11

1.2 introduction to MYCAT

Beijing Taobao project Day11

1.3 MYCAT features

1. Support the front end as a MySQL general agent
2. The back-end JDBC supports Oracle, DB2, SQL server, mongodb and Jushan
3. Automatic failover based on heartbeat, supporting read-write separation
MySQL Cluster, Galera, percona and cluster are supported
Support multi chip automatic routing and aggregation of data
Support sum, count, Max and other common aggregation functions, and support cross library paging
It supports intra database split tables, any join global tables within a single database, and cross database 2 table joins
Multi table join based on cattlet
It supports the fragmentation strategy of global table and ER relationship, and realizes efficient multi table join query

1.4 specific installation steps

1.4.1 upload installation package

Beijing Taobao project Day11

1.4.2 unzip files

Command: ‘tar -xvf mycat-server-1.7.0-dev-20170416134921-linux. Tar. GZ’
Delete useless files, as shown in the figure:
Beijing Taobao project Day11

1.4.3 configuration principle of proxy server

Beijing Taobao project Day11

1.4.4 edit the server.xml configuration file

1. Proxy port number – 8066 common port numbers: 8080tomcat server, 3306 MySQL data, 80 nginx, 6379 redis
2. Modify database link
Beijing Taobao project Day11
3) . set user name and password
Beijing Taobao project Day11
4) The maximum number of links in the database link pool is 1000
5) Description of logical database: it only exists in the logical sense of database
Beijing Taobao project Day11

1.4.5 edit schema.xml configuration file information

Note: the main function of this file is to configure the relationship between the agent and the database. It mainly configures the specific address information of the database

`<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    
    <!-- The name attribute is customized. Datanode represents the node information of the database, and jtdb represents the logical 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 description UTF-8 Chinese error reporting uses single line comments -- >
        <!-- Balance 0 means that all read operations will be sent to the writehost host -- >  
        <!-- 1 means that all read operations are sent to readhost and idle master nodes -- >
        <!-- Writetype = 0. All write operations are sent to the first writehost host -- >    
        <!-- Writetype = 1 all write operations are randomly sent to writehost -- >
        <!-- Dbtype indicates the database type MySQL / Oracle -- >
        <!-- Dbdriver = "native" fixed parameter unchanged -- >
        <!-- Switchtype = - 1 means that the slave node will not be automatically switched after the host goes down -- >
        <!-- Switchtype = 1 means automatic switching (default). If the first primary node goes down, MYCAT will perform three heartbeat tests. If there is no response three times, MYCAT will automatically switch to the second primary node -- >
        <!-- The master node information of the / conf / dnindex.properties file will be updated. Localhost1 = 0 indicates the first node. Do not modify the file at will, otherwise there will be big problems -- >

        
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select 1</heartbeat>

        <!-- The first host is configured to mainly write to the library. Under the default conditions, MYCAT mainly operates the first host. The first host has realized read-write separation in the first host. Because the default write operation will be sent to 137 database. The read operation will be sent to 141 by default. If the slave node is busy, the master node will share 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 database 2 -- >
            <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 for 3 times. -- >
            <!-- If the host 137 does not respond to MYCAT, it is judged that the host is dead. Then go back to Qidong and the second host continues to provide services for the user. -- >
            <!-- If the 137 host is restored, it will be in a waiting state. If 141 is down, 137 will continue to provide services to users again. -- >
            <!-- Premise: realize dual machine hot standby. -- >
        
        <!--<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>`

1.5 uploading configuration files

Note: upload the configuration file to the MYCAT / conf directory. Note: delete the original file first
Beijing Taobao project Day11
Beijing Taobao project Day11

1.6 MYCAT startup

Note: MYCAT started in the JDK environment. Enter the bin directory and execute the following commands
Beijing Taobao project Day11

1.7 description of MYCAT error report

Check whether the service is started correctly through cat mycat.log | cat wrapper.log
Beijing Taobao project Day11

1.8 project testing

Test directory:
1. Add records to the database and check whether the master and slave synchronize data
2. Modify the records in the slave library, use the program to check the data information, and check whether there is load balancing effect
3. After modification, update the data to ensure the consistency of the data

1.9 dual hot standby

1.9.1 description

Because the master database in the database may be down due to special reasons, it is configured to be master-slave to each other in the form of dual computer hot standby
Prerequisite for high availability of database: master-slave synchronization of database
Beijing Taobao project Day11

1.9.2 configuration of dual machine hot standby

IP: before 192.168.126.129, the master database is now the slave database
IP: before 192.168.126.130, the slave database is now the master database

1. Main library 130 configuration:

`/*130 configure today's main library*/
SHOW MASTER STATUS;`

2. Slave library 129 configuration

`/*Set 129 as slave Library*/
CHANGE MASTER TO 
MASTER_HOST="192.168.126.130",
MASTER_PORT=3306,
MASTER_USER="root",
MASTER_PASSWORD="root",
MASTER_LOG_FILE="mysql-bin.000001",
MASTER_LOG_POS=468;

/*Start master-slave service*/
START SLAVE;

/*Check master-slave status*/
SHOW SLAVE STATUS;`

Beijing Taobao project Day11

1.9.3 high availability of database

Edit schema configuration information:

`<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    
    <!-- The name attribute is customized. Datanode represents the node information of the database, and jtdb represents the logical 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 description UTF-8 Chinese error reporting uses single line comments -- >
        <!-- Balance 0 means that all read operations will be sent to the writehost host -- >  
        <!-- 1 means that all read operations are sent to readhost and idle master nodes -- >
        <!-- Writetype = 0. All write operations are sent to the first writehost host -- >    
        <!-- Writetype = 1 all write operations are randomly sent to writehost -- >
        <!-- Dbtype indicates the database type MySQL / Oracle -- >
        <!-- Dbdriver = "native" fixed parameter unchanged -- >
        <!-- Switchtype = - 1 means that the slave node will not be automatically switched after the host goes down -- >
        <!-- Switchtype = 1 means automatic switching (default). If the first primary node goes down, MYCAT will perform three heartbeat tests. If there is no response three times, MYCAT will automatically switch to the second primary node -- >
        <!-- The master node information of the / conf / dnindex.properties file will be updated. Localhost1 = 0 indicates the first node. Do not modify the file at will, otherwise there will be big problems -- >

        
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select 1</heartbeat>

        <!-- The first host is configured to mainly write to the library. Under the default conditions, MYCAT mainly operates the first host. The first host has realized read-write separation in the first host. Because the default write operation will be sent to 137 database. The read operation will be sent to 141 by default. If the slave node is busy, the master node will share 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 database 2 -- >
            <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 for 3 times. -- >
            <!-- If the host 137 does not respond to MYCAT, it is judged that the host is dead. Then go back to Qidong and the second host continues to provide services for the user. -- >
            <!-- If the 137 host is restored, it will be in a waiting state. If 141 is down, 137 will continue to provide services to users again. -- >
            <!-- Premise: realize dual machine hot standby. -- >
        
        <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>`

1.9.4 configuration strategy

1) . shut down the MYCAT server
Beijing Taobao project Day11
2) . edit schema configuration file
Beijing Taobao project Day11
3) . restart MYCAT
Beijing Taobao project Day11

1.9.5 test high availability

1) . close the master database
2) . test whether the program operates normally
3) . restart the master database and check whether the data is synchronized

Beijing Taobao project Day11

2. Learn redis cache

2.1 cache mechanism description

Beijing Taobao project Day11

2.2 principle description of cache mechanism

Caching mechanism:
The data in the cache must be the data in the database. The main purpose of using the cache is to reduce the frequency of users accessing physical devices
If the database records are updated, the cache should be updated synchronously

1. The cached data structure is K-V structure for data saving

  1. Development language selection C language program
  2. The running environment of cache data is in memory, which is erased when power is off. The memory data is persisted
  3. If you keep adding cached records, it will certainly cause memory overflow. Optimize the cache mechanism regularly

1. LRU algorithm 2. LFU algorithm 3. Random 4. TTL

  1. Cache also needs to implement high availability mechanism. Build cache cluster

2.3 redis introduction

2.3.1 introduction to official website

URL address:http://www.redis.cn
Redis is an open source (BSD licensed) in memory data structure storage system, which can be used asDatabase, cache and message centerPieces. It supports many types of data structures, such asStrings, hashes, lists, sets, sorted setsAnd range query,Bitmaps, hyperlogs and geospatialIndex radius query. Redis has built-in replication, Lua scripting, LRU events, transactions and different levels of disk persistence, and provides high availability through redis sentinel and cluster.

Efficiency: Reading: 112000 times / S; writing: 86000 times / S; average: 100000 times / S
Pithy formula: 1-5-8

2.3.2 knowledge expansion

1. Database:
1. Relational database mysql database / Oracle data 2D table row / column
2. Non relational database mongodb / HBase / redis 1D table

  1. Middleware:

500000 times / s throughput
Beijing Taobao project Day11

2.4 redis installation

2.4.1 upload installation package

Beijing Taobao project Day11

2.4.2 decompressing redis

Note: tar -xvf redis-5.0.4.tar.gz delete the installation package / modify the file name after decompression
Beijing Taobao project Day11

2.4.3 installing redis

Note: execute the following command in the redis root directory
Command: 1. Make
2.make install
Beijing Taobao project Day11

2.4.4 binary conversion

1k => 1000 bytes
1kb => 1024 bytes
1m => 1000000 bytes
1mb => 1024_1024 bytes
1g => 1000000000 bytes
1gb => 1024_1024*1024 bytes

2.4.4 modify redis configuration file

1) . modify IP binding
Beijing Taobao project Day11
2) . turn off protection mode
Beijing Taobao project Day11
3) . enable back-end operation
Beijing Taobao project Day11

2.4.5 server commands

  1. Start redis redis server redis.conf
  2. Enter the client redis cli – P 6379 exit quit Ctrl + C
  3. Close redis cli – P 6379 shutdown

2.4.6 client commands

Note: practice redis command according to the document
Review AOP related knowledge