The characteristics of mycat and the master-slave database

Database backup strategy

2.1.1 database cold backup

Description: through database tools, dump database files regularly to ensure the security of data (generally 2-3 days)
Disadvantages:
1. The backup may fail due to emergencies during backup Repeated backups are required
2. Cold backup is a regular backup This may result in data loss

Core:
Data must be backed up Backup data is the last effective means of recovery

2.1.2 database hot backup

Description: the data backup operation can be realized automatically through the database mechanism
Advantages: it can realize automatic operation and real-time backup

Backup implementation principle:
1. When the data of the database host changes, the modified data will be written to the binary log file
2. Read the binary log file of the main library from the library through the IO thread, and save the data to the relay (temporary storage) log
3. Start the Sql thread from the library, read the data in the relay log, and then synchronize the data to the slave library

2.2 prepare multiple servers

2.2.1 modifying virtual machine name

Note: copy the virtual machine in the pre class materials and change its name to JT_LINUX_S. Then start it

2.2.2 modify mac address of Linux slave

In order to ensure the normal and stable operation of the two virtual machines, the mac address needs to be modified

2.2.3 installing database slave

See the blog documentation for details
https://blog.csdn.net/qq_1680...

2.3 construction of master-slave database

2.3.1 open binary file of main library

Note: the binary log file of the main library is closed by default and needs to be opened manually
Command: VIM / etc / my cnf
Edit binary log file:

Restart the database and check whether the binary log file is available

After restart, binary files are generated

2.3.2 enable slave binary configuration file

Modify the file: VIM / etc / my cnf
The adding operation is consistent with that of the main database, and then restart the database server. The effects are as follows:

2.3.3 construction of master-slave database

1). Check the status of the main library

2). Realize database master-slave mounting

`/*I'm 130. I'm from the library*/
/*1.host/port/user/password / binary log / pos*/
CHANGE MASTER TO MASTER_HOST="192.168.126.129",
MASTER_PORT=3306,
MASTER_USER="root",
MASTER_PASSWORD="root",
MASTER_LOG_FILE="mysql-bin.000001",
MASTER_LOG_POS=245;

/*2.Start database master-slave service*/
START SLAVE;

/*3.Check database startup status*/
SHOW SLAVE STATUS;

/*4.If there is a database problem, 1 Turn off the master-slave service, 2 Check the error status 3 Rebuild service*/
STOP SLAVE    
/*Check the error information and modify the record according to the error*/
/*Rebuild the master-slave relationship*/` 

*   1
*   2
*   3
*   4
*   5
*   6
*   7
*   8
*   9
*   10
*   11
*   12
*   13
*   14
*   15
*   16
*   17
*   18
*   19

3). Verification of master-slave status

2.3.4 database master-slave test

matters needing attention:
1. Modify the data of the master database, and the slave database will synchronize the data
2. If the slave database data is modified, the master-slave relationship will be terminated

2.4 implementation of database read / write separation / load balancing

2.4.1 database optimization strategy

Note: through the proxy database, the read-write separation of the database / database load balancing operation can be realized, which further improves the ability of the overall architecture

2.4.2 Mycat

2.4.3 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 and cross database general middleware agent.
Heartbeat based automatic failover supports read-write separation, MySQL master-slave and galera cluster.
Support Galera for MySQL Cluster, Percona Cluster or MariaDB cluster
Nio based implementation can effectively manage threads and solve the problem of high concurrency.
Support multi slice automatic routing and aggregation of data, support common aggregation functions such as sum, count and Max, and support cross database paging.
Support any join within a single database, cross database 2 table join, and even multi table join based on calllet.
It supports the fragmentation strategy of global table and ER relationship, and realizes efficient multi table join query.
Support multi tenant scheme.
Support distributed transactions (weak xa).
Support XA distributed transactions (1.6.5).
Support global serial number and solve the problem of primary key generation under distributed.
The fragmentation rules are rich, plug-in development, and easy to expand.
Powerful web, command line monitoring.
Support the front-end as the general agent of MySQL, and the back-end JDBC supports Oracle, DB2, SQL Server, mongodb and Jushan.
Support password encryption
Support service degradation
Support IP whitelist
Support SQL blacklist and SQL injection attack interception
Support prepare precompiled instruction (1.6)
Support non heap memory aggregate computing (1.6)
Support PostgreSQL native protocol (1.6)
Support mysql and oracle stored procedures, out parameters and multi result set return (1.6)
Support zookeeper to coordinate master-slave switching, zk sequence and zk configuration (1.6)
Sub table in support library (1.6)
The cluster is based on ZooKeeper management, online upgrade, capacity expansion, intelligent optimization and big data processing (version 2.0).

2.5 deployment steps

2.5.1 uploading to myCat server

Upload path: / usr/local/src

2.5.2 unzip Mycat

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

*   1

Move installation files:

2.5.3 server.xml configuration file description

Note: the default port number is 8066

`<user name="root">
        <property name="password">root</property>
        <!--And schema.xml The configuration in is the same. Pay attention to the case of the database-->
        <property name="schemas">jtdb</property>
    </user>
    
    <user name="user">
        <property name="password">user</property>
        <property name="schemas">jtdb</property>
        <property name="readOnly">true</property>
    </user>` 

*   1
*   2
*   3
*   4
*   5
*   6
*   7
*   8
*   9
*   10
*   11

2.5.3 schema.xml configuration

`<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    
    <!--name Properties are custom  dataNode Represents the node information of the database  jtdb Represents a 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 introduction UTF-8 Chinese error reporting-->
        <!--balance 0 Indicates that all read operations will be sent to writeHost host -->  
        <!--1 Indicates that all read operations are sent to readHost And idle master nodes-->
        <!--writeType=0 All writes are sent to the first writeHost host-->    
        <!--writeType=1 All writes are sent randomly to writeHost in-->
        <!--dbType Represents the database type mysql/oracle-->
        <!--dbDriver="native"  Fixed parameters unchanged-->
        <!--switchType=-1 Indicates no automatic switching, The slave node will not be automatically switched after the host goes down-->
        <!--switchType=1  Indicates automatic switching(Default value)If the first primary node goes down,Mycat Three heartbeat tests will be performed,If there is no response three times,It will automatically switch to the second master node-->
        <!--And will be updated/conf/dnindex.properties Master node information of the file localhost1=0 Represents 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>

        <!--Configure the first host to write to the library,Under default conditions Mycat It mainly operates the first host, in which the read-write separation has been realized.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,Then the main 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 database 2-->
            <readHost host="hostS2" url="192.168.126.129:3306" user="root" password="root" />
            
        </writeHost>

            <!--Define the second host, because the database has realized dual machine hot standby.-->
            <!--Mycat Achieve high availability.When the first host 137 goes down.mycat It will automatically send out heartbeat detection.Test 3 times.-->
            <!--If the host 137 does not give Mycat In response, the host is judged dead.The second host back to Qidong will continue to provide services for users.-->
            <!--If the host is restored, it is in a waiting state.If 141 goes down, 137 continues 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
*   2
*   3
*   4
*   5
*   6
*   7
*   8
*   9
*   10
*   11
*   12
*   13
*   14
*   15
*   16
*   17
*   18
*   19
*   20
*   21
*   22
*   23
*   24
*   25
*   26
*   27
*   28
*   29
*   30
*   31
*   32
*   33
*   34
*   35
*   36
*   37
*   38
*   39
*   40
*   41
*   42
*   43
*   44
*   45
*   46
*   47
*   48

2.5.4 upload configuration files

2.5.5 Mycat command

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

2.5.6 check mycat log

2.5.7 Mycat load balancing test

Modify the database from the library Refresh the list page Check whether there is load balancing effect
Note: if the test is completed, remember to modify the data to ensure consistency

Tags: Java

Posted by ArizonaJohn on Tue, 03 May 2022 10:40:01 +0300