Cgb2005 - Jingtao 11 (master-slave configuration of database, tomact server script, read-write separation of MYCAT, MySQL dual host mode, introduction to redis)

Cool breeze AAA 2022-02-13 07:43:38 阅读数:79

cgb2005 cgb jingtao master-slave master

matters needing attention

1. Database master-slave configuration
Reset binary log file
2.tomact Server scripts
Online deployment command Log command linux Script
3. Database agent
mycat Read and write separation
mycat High availability
4.redis explain : install To configure Getting started command

1. Realize master-slave configuration of database

1.1 Configure the main library

1.1.1 Open the main library binary file

1). Modify the core configuration file of the main database my.cnf

 vim /etc/my.cnf

 Insert picture description here 2). add to 2 Line configuration ( Different databases have different numbers

server-id=1
log-bin=mysql-bin

 Insert picture description here

1.1.2 restart mysql database

explain : When the revision is good my.cnf After the document , Need to restart the database , Make the binaries work , And check whether the binary log file exists .
You can see :000001, Represents that the number of database restarts is once .
 Insert picture description here

1.2 Configure slave

explain : The steps are the same as above , Be careful server-id The number of cannot be repeated .
 Insert picture description here

1.3 Realize the mount of master-slave ( Configure master-slave relationship )

1.3.1 Mount analysis

Main library : 192.168.126.129 master
Slave Library : 192.168.126.130 slave

1.3.2 Check the master database status information ( Select execute )

File: Binary file name .
Position: From where data was last read ( It can't be read from the beginning ).
 Insert picture description here

1.3.3 Realize the master-slave mount ( stay 130 mount )

Be careful :
(1) 1 ,2, 3 Perform these steps separately , Do not select the last piece to execute , If an error occurs, execute 4,5.
(2) This is written from the library .

/*1. Realize the mount of master-slave Main library :ip user name password II. Documents entering Japan Location */
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 master-slave service */
START SLAVE
/*3. Verification of master-slave synchronization */
SHOW SLAVE STATUS
/*4. If you make a mistake Reconfigure */
STOP SLAVE
/*5. Rebuild the master-slave and execute the above command again */

effect :
appear yes On behalf of success .
 Insert picture description here

1.3.4 Master slave synchronization test

Modify the data in the master database , Check whether the slave database data is synchronized .
 Insert picture description here
explain , Because only master-slave synchronization is configured at this time , Now the master database is modified, and you can see that the data of the slave database also changes , Indicating successful configuration .
 Insert picture description here

1.3.5 Instructions for resetting binary log files

Be careful : It doesn't matter how many binary files .
Problem description : Sometimes after restarting the host many times , Will generate multiple binary file information . As shown in the figure
 Insert picture description here
Solution : Delete extra binary log files , Then restart the database , Then import and export sql Document guarantee 2 The data of two databases are the same , Finally, hang it again .
 Insert picture description here

2. Homework

Now it's a mainframe with 3 platform tomact The server , A primary connection library , One from the library . The following is to make load balancing more obvious . You can not configure .
1. Realization linux Project deployment
129: 8081 8082 8083
130: 8081 8082 8083
All required tomact All servers are connected to the main library Be careful ip Address ( Because now there is 2 individual linux System , namely 2 individual ip Address , If you write localhost There is no way to connect to the host , So they are all connected to the main database ip(129))
2. Deploy 2 From the database 129 host 130/131 from The structure of one master and two slaves
3. Yes bug About pictures Temporary neglect Think about why this phenomenon occurs .

3 About tomcat Server scripts

3.1 Problem specification

If by order : java -jar xxx.war When starting the server in the way of , If the remote terminal is shut down , that tomcat The server will shut down as well . Affect the use of users . ( The above command indicates that the foreground is running ).

3.2 Online deployment commands

explain : Generally in Linux The deployment server in the system generally adopts the back-end operation mode ( Even if the terminal is turned off , The program won't close ): start-up tomcat The server , And specify the log file output .

command : nohup java -jar 8081.war -> 8081.log &
explain : Perform project 8081.war, And write the log file to 8081.log Log files , If there are follow-up operations in writing &.( The usual way of writing before , The log is output directly , This will occupy the terminal .)
 Insert picture description here

3.3 Description of log command

cat: Output all the contents of the file ( Small file retrieval can be used , It's best not to use large files cat, Because too much is loaded into memory , It may cause the computer to jam )
more : Output all the contents of the document , Paging output , Space for the next screen ,q sign out
less: Usage and more identical , Only by PgUp、PgOn Key to control q sign out ( such as : less 8081.log)
tail : Used to display the number after the file , Frequently used
tail -10 nginx.conf: see nginx.conf Last 10 That's ok
tail –f nginx.conf: Dynamic view log , It is convenient to view the new information in the log
ctrl+c : End of view
for example :
 Insert picture description here

3.4 Linux Script description

explain : Linux Medium " Script "( plugin - The wilderness act ) The suffix of is .sh.(window Inside is called .bat)
demand : Want to start multiple computers at the same time tomact The server .
1). create a file : vim start.sh(start It's a name. Write it casually , The suffix must be sh)
 Insert picture description here
2). Compile the script
 Insert picture description here
3). Execute the script :
 Insert picture description here

4 Database agent description

4.1 Optimization of database structure

explain : Due to the need for users to link at the same time 2 Agents need to be introduced when there are more than one database , So we have the following deployment .
matters needing attention : The port number of user connection proxy server is generally :8066 port ( Conventional proxy server port number )
explain : Most users read more data , You can put Write for master database , Read data from the library , In the future, arranging multiple slave databases can reduce the pressure on the database . however Database connection can only connect to one server , solve : Instead, connect to the proxy server for distribution ( namely : User and agent , The proxy connects to the real server ).
 Insert picture description here

4.2 Mycat Introduce

Written by Ali .
 Insert picture description here
What we use here is the separation function of database .
 Insert picture description here

4.3 Implement the database read-write separation mechanism

4.3.1 Upload mycat Installation package

Be careful : The information before class is linux edition .
step
1.) Upload — decompression :tar -zxvf Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz
 Insert picture description here
2). Move the installation package to the specified directory for unified management .
 Insert picture description here

4.3.2 Check JDK Whether to install

explain : It is used by Alibaba java Written language , Therefore, you need to install jdk , We have installed... In the main library here jdk So upload to the main database .
Check :java -version
 Insert picture description here

4.3.3 Edit profile -server.xml

Note 1 :bak Documents are not important , It's okay to delete it .
 Insert picture description here
Note 2 : stay server.xml The user name, password and operation database information are defined in the configuration file , Must be with YML Consistent profile .
 Insert picture description here

<property name="serverPort">8066</property>
<!-- User label User name and password of the connection server agent -->
<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 Indicates which databases users can see in the future Just manage multiple ',' Division -->
<property name="schemas">jtdb</property>
</user>
<!-- read-only operation -->
<user name="user">
<property name="password">user</property>
<property name="schemas">jtdb</property>
<property name="readOnly">true</property>
</user>

2). modify YML The configuration file , Now you are connected to the proxy server .
 Insert picture description here

4.3.4 edit schema The configuration file

explain :schemas The main function of the file is to configure the database read-write strategy .
Be careful : Comment out the high availability configuration first .( Because we haven't talked about )

<!-- Write database 1-->
<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>

 Insert picture description here

4.3.5 Replace the configuration file

explain : The first conf In the catalog 2 Delete a file ( Select and click the cross to delete ), Then change the good 2 individual xml File replaces the file in the main library . Insert picture description here

4.3.6 start-up mycat Service Testing

explain :
1) Enter into mycat–>bin Directory execute command :./mycat
2) Green is the script file , If there is a suffix, use .sh Use without suffix ./

 Insert picture description here

start-up :./mycat start( According to the prompt, you can choose different commands )
Check the status of startup to see if it starts :./mycat status( etc. 5 Start several more times after seconds, and each time it indicates success . database 5 If the second connection is not successful, an error will be reported .)
 Insert picture description here
test : Because with linux You also need to repackage and publish the project , So for the time being windows The system test .
 Insert picture description here
url:localhost:8081
 Insert picture description here

4.4 Mysql High availability

4.4.1 High availability instructions (HA)

explain : According to the rules, when the server goes down , It can automatically realize the degree of fault migration, which is called high availability .
Problem description : Main library down , Data is read and written from the library , When the backup is continued after the main database is repaired , The slave database has more data than the master database , here io Will hang up in synchronizing data , The write operation is in the master library and the read operation is in the slave library , This will lead to more and more data in the main database, which cannot be consistent , This high availability doesn't work anymore , How to solve it ??? Dual host mode

4.4.2 Database dual master mode description

explain : In general, the dual working mode is adopted to realize the high availability in the main database . In essence 2 All databases are hosts . It's just an artificial division of master and slave ,2 The database data are synchronized with each other .

 Insert picture description here

4.4.3 Realize two-way binding of database .

Morning configuration : By the main library 129 Synchronize data to slave Library 130
Afternoon configuration : By Library 130 Synchronize the data to the main library 129
Be careful : Here is to mount in the main library .

1). Check the status of the main library ( Here is the 130 As the master library )
 Insert picture description here
2). Realize the construction of master-slave ( stay 129 Mount inside )

/* Realize the mount of master-slave Log files , Write the port number. You can write whatever you find in the main database */
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=477
/* Start the master-slave service */
START SLAVE
/* Check the master-slave status */
SHOW SLAVE STATUS;

 Insert picture description here

4.4.4 Mycat High availability of database configuration

1). Release the comments before the configuration file .
 Insert picture description here
2). close mycat service , And upload the modified configuration file ( First delete the original )

 Insert picture description here
 Insert picture description here

3). restart mycat To configure
 Insert picture description here

4.4.5 Database high availability test

explain : Generally, the port number of the database is 3306 tomact The default is 8080 The database proxy server defaults to 8066
Be careful : Temporary use windows Test it , Because I changed the code linux It needs to be republished and packaged. It's too cumbersome .
testing procedure :
1. Shut down the main database , And then retrieve the data , Check whether the user's access is restricted .
 Insert picture description here
At this point, the primary database is closed , Queries can only look up data from the library . The page echo correctly indicates that the data can be queried from the library again .
 Insert picture description here
2. Modify database records , Check whether the user's operation is normal . ( The operation is from the library )
At this time, the data to be modified is from the database .
 Insert picture description here

3. Restart the main database Then check whether the data from the database is synchronized .
You can see the after restart 129 The data in the database also changes , The configuration is correct .
 Insert picture description here

5 Description of service cache

5.1 Restore program ( Revert to Windows System )

5.1.1 modify YML The configuration file

 Insert picture description here

5.1.2 modify images.properties File image path

 Insert picture description here

5.1.3 modify Nginx The configuration file

 Insert picture description here

5.1.4 modify hosts file

 Insert picture description here

5.2 Description of caching mechanism

explain : Using cache can effectively reduce the frequency of users accessing physical devices . Fast access to data from memory , And then back to the user , At the same time, we need to ensure that the data in memory is database data .

Think about what direction you need to consider when developing cache ???
1. The running environment of the cache should be in memory .( fast )
2. Use C Language development cache
3. What data structure should the cache use --------K-V structure It is generally used String Most of them are types key Must be unique . v:JSON Format
4. The memory environment is erased when it is powered down , So the memory data should be persisted ( Write disk operation )
5. If the memory size is not maintained , It is easy to lead to Memory data overflow . use LRU Algorithm optimization !!!
 Insert picture description here

5.3 Redis Introduce

Official website :redis.io
 Insert picture description here
Chinese net :redis.cn

 Insert picture description here

redis It's a key-value The storage system . and Memcached similar , It supports storage value There are more types , Include string( character string )、list( Linked list )、set( aggregate )、zset(sorted set -- Ordered set ) and hash( Hash type ). These data types support push/pop、add/remove And take intersection, union and difference sets and more abundant operations , And these operations are atomic . On this basis ,redis Support various sorts of sorting . And memcached equally , To ensure efficiency , The data is cached in memory . The difference is redis Periodically, updated data is written to disk or changes are written to an appended log file , And on this basis to achieve master-slave( Master-slave ) Sync .

Redis It's open source (BSD The license ) Of , Data structure storage system in memory , It can Use as database cache and Message middleware . It supports multiple types of data structures , Such as character string (strings), hash (hashes), list (lists), aggregate (sets), Ordered set (sorted sets) And scope query , bitmaps, hyperloglogs and Geographical space (geospatial) Index radius query . Redis Built in Copy (replication),LUA Script (Lua scripting), LRU Driving events (LRU eviction), Business (transactions) And different levels of Disk persistence (persistence), And pass Redis sentry (Sentinel) And automatic Partition (Cluster) Provide high availability (high availability).

Interview questions : Redis Support 5 Type of data .
Fast : read 11.2 Ten thousand times / second Write 8.6 Ten thousand times / second The average is about 10 Ten thousand times / second

Atomicity States : Redis Is a single process, single thread operation , So there is no thread concurrency security issue . In the way of queue, one operation at a time .
Redis Common use :
1.Redis It can be used as a cache
2.Redis It can be used as a database Verification Code ( For example, the mobile phone sends a verification code , The verification code exists Redis in )
3.Redis Message middleware can use Bank transfer, etc
Redis Default port number :6379

A term is used to explain :
redis As a non relational database : One dimensional table , Rows and columns are the same .
mysql Is a relational database : Two-dimensional table , With rows and columns .
 Insert picture description here

Message middleware :
Database connections up to 1000 individual , Now there is 2000 Data needs operation . The database can only 1000 individual 1000 The handling of a , But generally more than 5 If seconds are not processed, it will report timeout, and the user can't do other things in the process of processing . Therefore, in order to solve the problems caused by the back-end server, middleware is introduced , It can be used as a queue , Queue function 2 End opening , First put the data in the queue , Users can perform some operations through asynchronous operations on the server . Then the data in the queue is processed one by one , After processing, the background server will return a message confirmation to the customer , It's usually ACk. The client sees the returned ACK It means that the program is finished , Then carry the correct data to the client for the next operation .
Note that this is asynchronous and Ajax Asynchronous is different . The advantage is that it can effectively protect the back-end server .
 Insert picture description here

5.4 Redis install

5.4.1 Upload installation package

.tar.gz It can be seen that linux System .
 Insert picture description here

5.4.2 decompression Redis

1). decompression redis file
 Insert picture description here
2). Moving files
 Insert picture description here

5.4.3 install Redis

requirement : stay redis In the root directory of .
command 1: make
 Insert picture description here
Effect after execution .
 Insert picture description here

command 2: make install
 Insert picture description here

5.4.4 modify Redis The configuration file

command : vim redis.conf;
 Insert picture description here
According to the line Numbers ::set nu, Move up and down through the direction keys .
 Insert picture description here
modify 1): take IP Binding comments , That is this. Redis The server only allows local access , So comment it out .
(c Language use # notes .)
 Insert picture description here
modify 2): Turn off protection mode ( Similar to database access ) ,6379 redis The default port number of .
 Insert picture description here
modify 3): Turn on background operation .
 Insert picture description here

5.4.5 About Redis command

1). Start command : redis-server redis.conf
 Insert picture description here
2). Check whether the service is running normally : Universal search command , Just change the last name Such as mycat(ps -ef | grep redis
 Insert picture description here

3). Enter the client command : redis-cli -p 6379
explain :
-p Is the connected client .
Because the default port number is 6379 , So you can write :redis-cli.
Save the data :set k v( The access value is k–v structure )
Take the data :get k( according to k obtain v)
Jump out of the client :ctrl +c、 exit、quit
 Insert picture description here
close Redis command : Exit the client first .
The way 1: First query the process number , It's killing .
 Insert picture description here
The way 2. redis-cli -p 6379 shutdown( Empathy :redis-cli shutdown)
 Insert picture description here
The order inside In the data You can also query more complete... On the official website .
 Insert picture description here

copyright:author[Cool breeze AAA],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130743339319.html