MySQL master-slave replication and read/write separation

MySQL master-slave copy and read/write separate MySQL read and write separation principle

• Read-write separation is only written on the primary server and is read only from the server. This is based on the principle that the primary database processes transactional queries, while processing select queries from the server. Database replication is used to synchronize changes caused by transactional queries to the slave servers in the cluster.
• Amoeba (amoeba) software is dedicated to MySQL's Distributed agent Layer database front-end, he mainly for the application layer to access MySQL as a SQL route, and has load balancing, high availability, SQL filtering, read and write separation, can be routed related to the target database, can request multiple databases concurrently. The ability of high availability, load balancing and data slicing of multiple data sources can be accomplished through amoeba.

Experiment extension and complement diagram

Experimental environment

Host	IP Address	main software
Primary server	192.168.27.128	mysql5.7.17
From server 1	192.168.27.139	mysql5.7.17
From server 2	192.168.27.142	mysql5.7.17
Amoeba	192.168.27.145	Amoeba-mysql-binary-2.2.0.tar.gz;jdk-6u14-linux-x64.bin
Customer Service side	192.168.27.143

Build MySQL master-slave replication

• Prior to the construction of MySQL master-slave replication, the experiment will not be introduced, detailed configuration process can be viewed in the previous article;
• Open permissions to amoeba access in three MySQL servers:

Mysql-u root-p #进入MySQL

Grant on . to [e-mail protected] ' 192.168.27.% ' identified by ' 123.com ';

Build MySQL read-write separation 1, install the Java environment on the amoeba host

Mkdir/opt/abc

Mount.cifs//129.168.27.1/MYSQL/OPT/ABC #挂载

Cp/opt/abc/jdk-6u14-linux-x64.bin/usr/local

Cd/use/local

chmod +x Jdk-6u14-linux-x64.bin #执行脚本提权

./jdk-6u14-linux-x64.bin #执行安装脚本

MV Jdk1.6.0_14 jdk1.6 #重命名

Vim/etc/profile #编辑环境变量

export JAVA_HOME=/usr/local/jdk1.6        #JAVA家目录export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib   #指定运行库export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin  #识别JAVA命令export AMOEBA_HOME=/usr/local/amoeba        #Amoeba家目录export PATH=$PATH:$AMOEBA_HOME/bin          #识别Amoeba命令

Source/etc/profire #刷新环境变量

2. Install and configure Amoeba software

Mkdir/usr/local/amoeba

Cd/opt/abc

Tar zxvf Amoeba-mysql-binary-2.2.0.tar.gz/usr/local/amoeba #解压缩

Chmod-755/usr/local/amoeba

/usr/local/amoeba/bin/amoeba

amoeba start|stop           #显示此内容说明Amoeba安装成功

Vim/usr/local/amoeba/cnf/amoeba.xml

#--30行--<property name="user">amoeba</property>#--32行--<property name="password">123456</property>#--115行--<property name="defaultPool">master</property>#--117行去掉注释--<property name="writePool">master</property><property name="readPool">slaves</property>

Vim/usr/local/amoeba/cnf/dbservers.xml

#--23行注释掉--（默认进入test库，以防MySQL中没有test库时会报错）<!-- <property name="schema">test</property> -->#--26-29--去掉注释--<prope77rty name="user">test</property><property name="password">123.com</property>#--42-主服务器地址---<dbServer name="master"  parent="abstractServer"><property name="ipAddress">192.168.27.128</property>#--52-从服务器1-<dbServer name="slave1"  parent="abstractServer"><property name="ipAddress">192.168.27.139</property>#--59-从服务器2-<dbServer name="slave2"  parent="abstractServer"><property name="ipAddress">192.168.27.142</property>#--66行--<dbServer name="slaves" virtual="true">#--72行--<property name="poolNames">slave1,slave2</property>

/usr/local/amoeba/bin/amoeba Start #开启Amoeba

3. Client

Yum Install Mqsql-y

Mysql-u amoeba-p123456-h 192.168.27.145-p8066 #远程登录

4. Testing

• 1), test read operation, when the synchronization is turned off from the server, and the two from the server to write data, using the client login query, multiple queries will appear two from the server to write data.
• 2), test write operation, when the synchronization is turned off from the server, write data on the client, the client will not query to write data, only on the primary server query to write data.

MySQL master-slave replication and read/write separation