当前位置:网站首页>Read write separation + load balancing

Read write separation + load balancing

2020-11-10 19:06:00 Always o

1. Code restore

1.1 IP Address problem

Problem description : windows Of IP No connection Linux Medium IP Address .
 Insert picture description here
 Insert picture description here
Problem specification : When virtual start starts , Virtual network card is not working properly . Just restart the network card .
 Insert picture description here

Restart the network card command :

`service NetworkManager stop 
chkconfig  NetworkManager  off     Permanent ban  Manager network card 
service  network restart           restart network network card ` 

*   1
*   2
*   3

1.2 Restore hosts file

 Insert picture description here

1.3 Restore image path

 Insert picture description here

1.4 Restore nginx To configure

Let the domain name access 8091 The server .
 Insert picture description here

2 Database related learning

2.1 Database backup strategy

2.1.1 Database cold backup

explain : Through database tools , Dump database files regularly , Ensure data security . ( commonly 2-3 God )
shortcoming :
1. Backup due to a sudden situation , May cause the backup to fail . It needs to be backed up repeatedly .
2. Because cold backup is a regular backup . So it may lead to the loss of data .

The core :
Data must be backed up . Backup data is the last effective means of recovery .
 Insert picture description here
 Insert picture description here

2.1.2 Database hot backup

explain : Through the database mechanism , Automatic data backup operation .
advantage : It can realize automatic operation , And it's real-time backup .
 Insert picture description here
Backup implementation principle :
1. When the data of the database host changes , The modified data will be written to the binary log file .
2. Pass through the library IO Threads , Read the binary log file of the main library , After acquisition , Save data to relay ( Temporary storage ) In the log .
3. Open from the library Sql Threads , Then read the data in the relay log , Then synchronize the data to the slave library .

2.2 Prepare multiple servers

2.2.1 Modify the virtual machine name

explain : Copy the virtual machine from the pre class materials , And renamed it JT_LINUX_S. After that, you can start .
 Insert picture description here

2.2.2 modify Linux Slave mac Address

In order to ensure 2 Virtual machines are running normally and stably , Need modification mac Address .
 Insert picture description here

2.2.3 Install database slave Library

Specific see Blog document .
https://blog.csdn.net/qq_1680...

2.3 Master slave database building

2.3.1 Open the main library binary file

explain : The binary log file of the main library , It's off by default , It needs to be turned on manually .
command : vim /etc/my.cnf
Edit binary log files :
 Insert picture description here
Restart the database , Check binary log files , Is it available .
 Insert picture description here
After reboot , Generate binaries
 Insert picture description here

2.3.2 Open the slave binary configuration file

Modify file : vim /etc/my.cnf
The operation added is consistent with the main database , Then restart the database server , The effect is as follows :
 Insert picture description here

2.3.3 Master slave database building

1). Check the status of the main library
 Insert picture description here
2). Realize the master-slave mount of database

`/* I am a 130  I'm from the library */
/*1. Realize the master-slave mount of database  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 the database master-slave service */
START SLAVE;

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

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



3). Verification of master-slave state

2.3.4 Database master-slave test

matters needing attention :
1. Modify the data of the main database , The slave library will follow the synchronized data .
2. If you modify the slave database data , Then the master-slave relationship will end .

2.4 Database read write separation / Load balancing implementation

2.4.1 Database optimization strategy

explain : The read-write separation of database can be realized by proxy database / Database load balancing operations , It further improves the capability of the overall architecture .
 Insert picture description here

2.4.2 Mycat

 Insert picture description here

2.4.3 Mycat characteristic

Support SQL92 standard
Support MySQL、Oracle、DB2、SQL Server、PostgreSQL etc. DB Common SQL grammar
comply with Mysql Native protocol , Cross language , Cross platform , Universal middleware agent across databases .
Automatic failover based on heartbeat , Support for read/write separation , Support MySQL Master-slave , as well as galera cluster colony .
Support Galera for MySQL colony ,Percona Cluster perhaps MariaDB cluster
be based on Nio Realization , Manage threads effectively , Solve the problem of high concurrency .
Support the automatic routing and aggregation of data , Support sum,count,max And so on , Support cross library paging .
Support arbitrary in single library join, Support cross Library 2 surface join, Even based on caltlet A lot of watches join.
Support through global tables ,ER The segmentation strategy of relationship , The realization of efficient multi table join Inquire about .
Support multi tenant scheme .
Support distributed transactions ( weak xa).
Support XA Distributed transactions (1.6.5).
Support global serial number , Solve the problem of generating primary key under distributed environment .
Rich segmentation rules , Plug in development , extensible .
Powerful web, Command line monitoring .
Support the front end as MySQL General agent , Back end JDBC Mode support Oracle、DB2、SQL Server 、 mongodb 、 Giant fir .
Support password encryption
Support service degradation
Support IP White list
Support SQL The blacklist 、sql Inject attack intercept
Support prepare Precompiling instructions (1.6)
Supports non heap memory (Direct Memory) Aggregate calculation (1.6)
Support PostgreSQL Of native agreement (1.6)
Support mysql and oracle stored procedure ,out Parameters 、 Multiple result sets return (1.6)
Support zookeeper Coordinate the master-slave switch 、zk Sequence 、 To configure zk turn (1.6)
Support in Library sub table (1.6)
Clustering is based on ZooKeeper management , Online upgrade , Capacity expansion , Intelligent optimization , Big data processing (2.0 Development Edition ).

2.5 Deployment steps

2.5.1 Upload myCat The server

Upload path : /usr/local/src
 Insert picture description here

2.5.2 decompression Mycat

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

*   1

Move installation files :
 Insert picture description here

2.5.3 server.xml Profile description

matters needing attention : Port number by default 8066 port

`<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>` 

2.5.3 schema.xml To configure

`<?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 the node name / Node host / Data name -->
    <dataNode name="jtdb" dataHost="localhost1" database="jtdb" />
        <!-- Parameter Introduction  UTF-8  Wrong report in Chinese -->
        <!--balance 0 Indicates that all read operations are 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 parameter   unchanged -->
        <!--switchType=-1  Does not automatically switch ,  The slave node will not be switched automatically after the host goes down -->
        <!--switchType=1   It means that it will switch automatically ( The default value is ) If the first primary node goes down ,Mycat Will be carried out in 3 Heartbeat detection , If 3 No response , It will automatically switch to the second master node -->
        <!-- And will update /conf/dnindex.properties Master node information of the file  localhost1=0  Represents the first node . Don't 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 database , Under default conditions Mycat The main operation of the first host in the first host has achieved read-write separation . Because the default write will be sent to 137 The database of . Read operations are sent to by default 141. 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 a second host   Because the database has already realized dual hot standby .-->
            <!--Mycat High availability . When the first host 137 After downtime .mycat It will automatically send out heartbeat detection . testing 3 Time .-->
            <!-- If the host 137 Not given Mycat In response, the host is judged dead . The second host in Qidong will continue to provide services for users .-->
            <!-- If 137 After the host recovers, it is in the waiting state . If 141 Downtime is 137 Once again, continue to provide services to users .-->
            <!-- Premise : Realize the hot standby of two computers .-->
        
        <!--<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 Realize configuration file upload

 Insert picture description here

2.5.5 Mycat command

Usage: ./mycat { console | start | stop | restart | status | dump }
 Insert picture description here

2.5.6 Check mycat journal

 Insert picture description here

2.5.7 Mycat Load balancing test

Modify the database from the library . Refresh the list page . Check for load balancing effect .
matters needing attention : If the test is done , Remember to change the data To ensure consistency .

版权声明
本文为[Always o]所创,转载请带上原文链接,感谢