Shulou(Shulou.com)06/01 Report--

This article mainly introduces the principle of MySQL master-slave replication, read-write separation and how to deploy it. I hope you can supplement and update some knowledge. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

I. Experimental environment

1. A CentOS 7 is used as a client test, and the corresponding address is: 192.168.80.120

two。 A CentOS 7 acts as an Amoeba front-end proxy cloud server with the corresponding address of 192.168.80.110

3. One CentOS 7 as the mysql master server, the corresponding address is: 192.168.80.100

4. The two CentOS 7 are used as mysql slave servers, and the corresponding addresses are: 192.168.80.140192.168.80.150

5. All virtual machines are bound to the same virtual network card vnet1:192.168.80.10

Second, the principle of master-slave replication and the separation of reading and writing

1. The principle of master-slave replication

There is a close relationship between master-slave replication and read-write separation in MySQL. First of all, master-slave replication must be deployed. Only when master-slave replication is completed can data read-write separation be carried out.

The replication types supported by MySQL are statement-based replication, row-based replication, and mixed-type replication.

Replication works as follows:

2. The principle of separation of reading and writing.

Simply put, read-write separation means writing only on the master server and reading only on the slave server. The basic principle is to have the master database handle transactional queries and the slave server handle select queries. Database replication is used to synchronize changes caused by transactional queries to the slave database.

Implementation based on the intermediate proxy layer: the agent is generally located between the client and the server, and the proxy server receives the request from the client segment and forwards it to the back-end database.

The process of read-write separation is as follows:

Third, set up MySQL master-slave replication

Compile and install the MySQL database with the source code of three CentOS 7 respectively. Specific operation: https://blog.51cto.com/13871362/2328667

1. Configure the primary server

1) establish a time synchronization environment

Yum install ntp-y / / install ntp time synchronization server vi / etc/ntp.conf / / Edit configuration file

17 lines added

Restrict 192.168.80.0 mask 255.255.255.0 nomodify notrap / / limits the synchronized network segment server 127.127.1.0fudge 127.127.1.0 stratum 8 / / sets the level of the time server to level 8, and the top level is 0

Systemctl start ntp / / enable service

2) configure the master MySQL

Vi / etc/my.cnf / / Edit database configuration file add the following lines under the [mysqld] module server_id = 11 / / master server number log_bin=master_bin / / specify binary log _ slave_updates=true / / allow slave server updates

Systemctl restart mysqld / / restart the service

3) access to database authorization

Mysql-u root-pabc123 / / enter the database GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.80.%' IDENTIFIED BY' abc123'; / / authorize all databases from the server FLUSH PRIVILEGES; / / refresh permissions show master status; / / to write down the value of the Position column (mine is 603)

2. Configure the slave server (the steps of the two slaves are the same, but the server-id is different)

1) configure slave MySQL

Vi / etc/my.cnf / / Edit database configuration file add the following lines under the [mysqld] module server_id = 22 / / from the server number relay_log=relay-log-bin / / specify the relay log of the slave server relay _ log_index=slave-relay-bin.index / / define the location and name of the intermediate log

Systemctl restart mysqld / / restart the service

2) time synchronization on the slave server

Yum install ntpdate-y / / install the time synchronization tool ntpdate 192.168.80.100 / / synchronize with the master server

Echo'* / 30 * / usr/sbin/ntpdate 192.168.80.183'> > / var/spool/cron/rootcrontab-1 / / create scheduled tasks that are synchronized every 30 seconds

3) access authorization from the database

Mysql-uroot-pabc123 / / enter database change master to master_host='192.168.80.100',master_user='mysyslave',master_password='abc123',master_log_file='master_bin.000001'',master_log_pos=603; / / specify master server start slave; / / enable slave server show slave status\ G

3. Verify master-slave replication

Access to the main server database

Mysql-u root-p123456create database test; / / Database building test

Verify on two slave servers

Show databases

Fourth, set up MySQL to separate read and write

1. Configure the front-end proxy server

1) install the JDK environment (developed by amoeba based on jdk)

Tar xf jdk-8u144-linux-x64.tar.gz / / extract the jdk installation package cp-rv jdk1.8.0_144/ / usr/local/javavi / etc/profile

Add Java environment

Export JAVA_HOME=/usr/local/javaexport JRE_HOME=/usr/local/java/jreexport PATH=$PATH:/usr/local/java/binexport CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/libsource / etc/profile / / Refresh environment java-version / / verify jdk environment

2) install Amoeba

Unzip amoeba-mysql-3.0.5-RC-distribution.zip-d / usr/local/ decompression package mv / usr/local/amoeba-mysql-3.0.5-RC/ / usr/local/amoeba/ / change the software location chmod-R 755 / usr/local/amoeba/ vi / usr/local/amoeba/jvm.properties / / Edit jvm configuration file optimization 32 lines add: # JVM_OPTIONS= " -server-Xms256m-Xmx1024m-Xss196k-XX:PermSize=16m-XX:MaxPermSize=96m "JVM_OPTIONS="-server-Xms1024m-Xmx1024m-Xss256k

3) make amoeba startup script

Vi / EtcUnip init. D bind amoebavirons. Binram: 35 62 62#export JAVA_HOME=/usr/local/javaexport PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATHNAME=AmoebaAMOEBA_BIN=/usr/local/amoeba/bin/launcherSHUTDOWN_BIN=/usr/local/amoeba/bin/shutdownPIDFILE=/usr/local/amoeba/Amoeba-MySQL.pidSCRIPTNAME=/etc/init.d/amoebacase "$1" instart) echo-n "Starting $NAME.... "$AMOEBA_BINecho" done ";; stop) echo-n" Stoping $NAME... "$SHUTDOWN_BINecho" done ";; restart) $SHUTDOWN_BINsleep 1 $AMOEBA_BIN;;*) echo" Usage: $SCRIPTNAME {start | stop | restart} "exit 1 scape esacchmod + x / etc/init.d/amoeba / / lift the weight of the amoeba startup script chkconfig-- add amoeba / / add the following port to the system configuration service amoeba start / / indicates that the startup is successful and ctrl+C exits directly

Netstat-anpt | grep 8066 / / default listening on port 8066

4) docking with the client

Vi / usr/local/amoeba/conf/amoeba.xml

28-30 lines

Line 83

5) interfacing with backend server

Vi / usr/local/amoeba/conf/dbServers.xml

26-29 lines

Lines 43-56

Lines 65-73

Service restart amoeba / / Service restart

6) authorize amoeba in three mysql databases (one master and two slaves)

GRANT ALL ON *. * TO test@'192.168.80.%' IDENTIFIED BY '123.compose; / / Link FLUSH PRIVILEGES; / / Refresh permissions for proxy authorization

2. Configure the client

Yum install-y mysql / / install the mysql client mysql-u amoeba-p123456-h 192.168.80.110-P8066 / / Log in to the database with the proxy address

3. Test the separation of read and write

1) if you create a database or tables in the MASTER, the two slave servers will synchronize

Use test;create table zhang (id int (10), name varchar (10), address varchar (20)); show tables; / / three database servers will get the same result

2) after two slave servers stop the slave service

Stop slave

Insert content on the primary server

Use testinsert into zhang values ('1century, last month, month, week, week, day, week, week

In and out of content from server 1

Use test;insert into zhang values ('2century, recording, etc.)

Entering and leaving content from server 2

Use test;insert into zhang values ('3century, recording, etc.)

3) Test read-only data from the server on the client

Select * from test.zhang

4) Test on the client that the master server only writes data

Insert into zhang values ('4times, recording, writing, writing, testing, etc.)

View on the client side

Select * from zhang

View on the main server

View on from Service 1

View it on server 2

4. Conclusion

The above experiments verify the read-write separation of MySQLd on the basis of master-slave synchronization, while Amoeba acts as a proxy server, which is responsible for forwarding customer requests and assigning them to the corresponding servers, and also verifies the principle.

Read the above about MySQL master-slave replication, read-write separation principle and how to deploy, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.