Fun with Linux system to easily build Mysql read-write separation cluster

Insert picture description here

Case study

Reminder: Mysql read-write separation is based on master-slave replication. For this, please refer to the previous tutorial to set up a Mysql master-slave replication cluster.
Experimental environment, turn off the firewall, setenfoce to tolerant mode, and set up a master-slave replication cluster with three servers.

Insert picture description here

Recommended steps

Reminder: amoeba recommends that the java environment is 1.5 or 1.6, the default is 1.8 for centos7, and the demo is reduced to version 1.6.
For example:
1. Install the java environment on the amoeba server

将下载的jdk包上传到/usr/local,进入目录
[[email protected] ~]# cd /usr/local/
设置执行权限
[[email protected] ~]# chmod +x /usr/local/jdk-6u14-linux-x64.bin 
执行安装(阅读条款,一直确定即可)
[[email protected] local]# ./jdk-6u14-linux-x64.bin  //根据提示按回车和yes即可

Execute the ./jdk-6u14-linux-x64.bin command as shown in the figure below:

Insert picture description here


Insert picture description here


Translation: Do you agree to the above license terms? , Input: yes and

Insert picture description here


press Enter to continue, and finally appears done to indicate that it is OK.

Optimization
Description: CLASSPATH specifies the search path of java classes (small programs). To use the classes that have been written in java, the premise is of course that they can be found. JVM searches for classes through CLASSPATH.

[[email protected] local]# vim  /etc/profile
export  JAVA_HOME=/usr/local/jdk1.6  //指向jdk的安装路径
export 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
[[email protected] local]# mv jdk1.6.0_14/  /usr/local/jdk1.6
[[email protected] local]# source /etc/profile

Finish, check the version information

Insert picture description here


2. Install and configure the amoeba software

创建amoeba专用目录
[[email protected] local]# mkdir /usr/local/amoeba
解压到此目录
[[email protected] local]# tar zxf  /mnt/amoeba-mysql-binary-2.2.0.tar.gz  -C  /usr/local/amoeba
为目录设置权限
[[email protected] local]# chmod -R 755 /usr/local/amoeba/
验证amoeba有没安装成功
[[email protected] local]# amoeba

Verify that the installation of amoeba is successful. If you see the prompt as shown in the figure, it means that the installation of amoeba is successful:

Insert picture description here


3. Configure the read-write separation of amoeba, and load balance between two slaves.

①Open permissions to amoeba in master, slave1 and slave2 respectively

mysql> grant all on . to'test'@'192.168.2.%' identified by '123.com';

②Amoeba proxy server configuration

(1) Edit the amoeba.xml configuration file (note: the number of lines in the configuration file may be different for different hosts, change the underlined part)

vim /usr/local/amoeba/conf/amoeba.xml

Insert picture description here


Insert picture description here


(2) Edit the dbServer.xml configuration file

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

Insert picture description here


Insert picture description here


Insert picture description here


(3) Start the amoeba software (add & run in the background) to

Insert picture description here


view its listening port

Insert picture description here


4. Test


Install the mysql client program yum -y install mariadb on the client host of the Linux system to
access mysql through the proxy mysql -u amoeba -p123456 -h 192.168.2.1 -P8066 (-P is uppercase)

②Create a table on the master, synchronize it to each slave server, then turn off the slave function of each slave server, and then insert a different statement

Insert picture description here

③ respectively from the two servers

Insert picture description here


④ and insert the data on the master server

Insert picture description here


⑤ synced from the server table, else manually inserted
Slave1

Insert picture description here


slave2

Insert picture description here


⑥ read test
results of a query for the first client host as follows:

Insert picture description here

The results of the second query are as follows:

Insert picture description here

The results of the third query are as follows:

Insert picture description here

Test the write operation on the master,

Insert picture description here


but it cannot
be queried on the client. In the end, the content of this statement can only be seen on the master server, indicating that the write operation is on the master server.

If you have any questions, you can refer to the video "How to Play with Linux System" to easily build a Mysql read-write separation cluster video