MySQL -- read write separation

MySQL -- read write separation

1: Principle

  • Read write separation is to write only on the master server and read only on the slave server

  • The primary database processes transactional queries, while the secondary database processes select queries

  • Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster

2: Definition of Amoeba

  • Amoeba is a proxy that takes MySQL as the underlying data store and provides MySQL protocol interface for applications. It centrally responds to application requests and sends SQL requests to specific databases for execution according to the rules set by users in advance. Based on this, we can achieve load balancing, read-write separation, high availability and other requirements.
  • Amoeba is a router for SQL requests. Its purpose is to provide a mechanism for load balancing, read-write separation and high availability, rather than to fully implement them. It is necessary to use MySQL Replication and other mechanisms to achieve replica synchronization and other functions.

Download the installation package by yourself:

After copying this content, open Baidu online mobile App, which is more convenient to operate. Link: https://pan.baidu.com/s/1kRkbem91iPxeQ-bZUObTxg extraction code: bg88

3: Experimental configuration

1. Environmental description

  • Prepare three Mysql servers (one master and one slave)

  • An Amoeba to achieve read-write separation

  • One client as validation

Mysql master-slave replication is closely related to the read-write function. The master-slave replication is used to achieve data synchronization, and then the read-write separation is used to improve the concurrent load capacity of the database.

2. Experimental steps

  • Configure Amoeba server
[root@localhost ~]# hostnamectl set-hostname amoeba
[root@localhost ~]# su 'modify hostname'
[root@amoeba ~]# systemctl stop firewalld.service 
[root@amoeba ~]# setenforce 0 'turn off firewall'
[root@amoeba ~]# mkdir /abc
[root@amoeba ~]# Mount.cifs / / 192.168.10.24/share/abc "mount package"
  • Installation management jdk
[root@amoeba abc]# cp jdk-6u14-linux-x64.bin /usr/local / 'copy jdk-6u... To / usr/local'
[root@amoeba abc]# cd /usr/local / "see if you want to copy to"
[root@amoeba local]# . / jdk-6u14-linux-x64.bin "execute JDK" 

After entering yes, wait for a moment, and the installation is complete.

  • To facilitate management, rename the jdk package name
[root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6
  • Modify profile file
[root@amoeba local]# vim /etc/profile 'add at the end'

export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

  • Make environment variables effective
[root@amoeba local]# source /etc/profile
  • Create amoeba directory
[root@amoeba local]# mkdir /usr/local/amoeba
  • Decompress amoeba package
[root@amoeba abc]# tar zvxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
  • Give permission to verify whether the installation is successful
[root@amoeba abc]# chmod -R 755 /usr/local/amoeba/
[root@amoeba abc]# /usr/local/amoeba/bin/amoeba
amoeba start|stop       'Indicates successful installation'
  • Add permissions on three mysql servers to allow amoeba access
mysql> grant all on *.* to test@'192.168.34.%' identified by '123';
  • Configure read-write separation function on amoeba server
[root@amoeba abc]# cd /usr/local/amoeba/conf
[root@amoeba conf]# vim amoeba.xml

[root@amoeba conf]# vim dbServers.xml



  • Start amoeba, put the process in the background, long time to wait
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start &

4: Test on client

1. Modify the host name and turn off the firewall
[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname client
[root@localhost ~]# su
2. Install mysql service directly
[root@client ~]# yum install mysql -y
3. Connect to amoeba server
[root@client ~]# mysql -u amoeba -p123 -h 192.168.34.182 -P8066

4. View the info table on the master and slave servers

5. Shut down the two slave servers and check the status. There are two NO's

6. After shutting down the service, insert a piece of information into the info table of the client, which can be viewed in the main server

7. The table information can not be viewed from the server and the client, so the read-write separation is realized

8. Add one piece of data from each of the two services

9. View the content of info table on the client side and read the information in the table one by one

Only the master server records the data written after the client connects to the database, and the slave server does not record the data. When master-slave synchronization (replication) is turned on, the slave server synchronizes with the master server. (due to the master-slave synchronization, the data of each slave server is the same as that of the master server.). In the case of high concurrent requests, load balancing is realized to ensure data security and efficient operation of the server.

Published 60 original articles, won praise 11, visited 2086
Private letter follow

Tags: MySQL JDK Database vim

Posted on Mon, 13 Jan 2020 04:44:15 -0800 by playaz