当前位置:网站首页>Jingtao Day10 database optimization

Jingtao Day10 database optimization

2020-12-07 17:59:11 Xiao Han

One 、 Database synchronization

1. Database cold backup

Manually put the database file , regular Dump .
shortcoming :
1. Low efficiency
2. It's possible to lose data
reason : Database cold backup It's the last effective way for companies to recover data .

2. Database hot backup

Backup principle :
1. When the database is modified , Will change the information , Write to binary log file ( Binaries are closed by default ).
2. When there is data in the binary log file , The database slave will pass through IO Thread reads binary file information .
3.IO The thread writes the read data to the relay log .
4.Sql The thread will relay the files in the log Write from database , Finally, the master-slave synchronization of database is realized .
 Insert picture description here

Two 、 Modify database master database configuration

1. Build strategy

129 As the main library ,130 As a slave library

2. Modifying binary log files

1). Edit binary location
 Insert picture description here
2). Modify the contents of the binary file
 Insert picture description here
3). Restart the database , Check whether the binary log file is valid
 Insert picture description here

3、 ... and 、 Modify database slave configuration

 Insert picture description here
Restart the database and check the binary log
 Insert picture description here

Four 、 Realize the master-slave synchronization of database

4.1 Determine the master database status

 Insert picture description here

4.2 Configure the master-slave mount

/* I am a 130   Slave Library   IP/PORT/USER/PASSWROD/ 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;
/* Turn on the master-slave service */
START SLAVE;    
/* Check the master-slave service status */
SHOW SLAVE STATUS;
/* If the start is not successful   Perform the following steps    Check the last few items for error information  
   Then modify the configuration file .  Turn off the master-slave service . Then execute the mount command again 
*/
STOP SLAVE;

4.3 Database master-slave test

Add a database table to the main database , Check whether the slave library is synchronized .
 Insert picture description here

5、 ... and 、 Database high availability (HA)

1. Realize the separation of reading and writing in database / Load balancing

 Insert picture description here

2.Mycat agent

2.1 Mycat Introduce

Mycat The middleware of database sub database and sub table is active 、 Open source database middleware with good performance !

2.2 Mycat Key features

  • 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.3 MyCat To configure

2.3.1 Upload installation package

 Insert picture description here

2.3.2 decompression Mycat

1). decompression Mycat
[root@localhost src]# tar -xvf Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz
2). Move Mycat file
 Insert picture description here

2.3.3 edit Server.xml

explain : Link between user and agent database , adopt Server.xml To configure .
1). Default port
 Insert picture description here
2). edit server.xml
 Insert picture description here

2.3.4 edit schemas.xml

explain : This file represents the agent and database configuration HOST:PORT: user name : password : Database name

<?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    Use a single line comment  -->
        <!--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>` 
2.3.5 Upload installation files

Upload the files from the pre class materials to the designated location : /usr/local/src/mycat/conf
 Insert picture description here

2.3.6 Mycat command

 Insert picture description here

2.3.7 Error check

 Insert picture description here

2.3.8 Load balancing test

Information from the database , Modify it manually . effect : The data of master database and slave database are inconsistent temporarily ( Risk exists )
 Insert picture description here
2). If the test is successful , Then modify and save the data . Check whether the read-write separation is successful .
 Insert picture description here

3 Realize hot standby of database

3.1 Principle that

explain : The precondition of high availability of database is Realize the master-slave backup .
Previous operations :
Main library :192.168.126.129
Slave Library : 192.168.126.130
Now the configuration :
Main library :192.168.126.130
Slave Library : 192.168.126.129
 Insert picture description here

3.2 Realize dual main mode configuration

1). Check the master database status
 Insert picture description here
2). Realize master-slave configuration

show MASTER status
#  I am a 129  It used to be the main library , Today when from the 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=700;
# Start the master-slave service 
start slave    
# Check status 
show slave status;

4 High availability of database

4.1 modify schema.xml The configuration file

<?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    Use a single line comment  -->
        <!--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>

4.2 Upload files

1). stop it mycat service
 Insert picture description here
2). Delete profile
 Insert picture description here
3). Upload files
Upload the configuration file to the specified directory
 Insert picture description here
4). start-up Mycat
 Insert picture description here

1.4.3 Database high availability test

1. First of all, we should 129 The database is down ( close )
2. Start the program , Check whether the access is correct through the database agent ( check ). Whether the storage is normal ( Write )
3. restart 129 The database of . Check that the data is really synchronized .

版权声明
本文为[Xiao Han]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/12/20201207175651264i.html