MySQL master-slave replication and read/write separation

In the actual production environment, a single MySQL as a separate database and read and write on the above is completely unable to meet the actual needs, whether in security, high availability and high concurrency and other aspects. Therefore, in general, through the master-slave Replication (Master-slave) to synchronize the data, and then through the read-write separation (mysql-proxy) to improve the database's concurrency load of such a scheme to deploy and implement.

Works as shown in the following:

Based on the intermediary agent layer implementation: The agent is generally located between the client and the server, the proxy server received a client request through the judgment and forwarded to the backend database, there are two representative programs:

(1) Mysql-proxy. Mysql-proxy is the MySQL open source project, with its own LUA script for SQL judgment, although it is the official MySQL product, but the official MySQL is not recommended to use the Mysql-proxy production environment.

(2) Amoeba. This software is dedicated to MySQL's distributed database front-end agent layer, which serves as an SQL route for the application layer when it accesses MySQL and has load balancing, high availability, SQL filtering, read/write separation, routing to the relevant target database, and concurrent requests for multiple databases. It does not support transactions and stored procedures.

MySQL read-write separation through program code is a good choice, but not all applications are suitable for the application code to achieve the separation of read and write, like some large and complex Java applications, generally consider the use of proxy layer to achieve.

Lab Environment:

Host	Operating System	IP Address	main software
Master	CentOS7.4 x86_64	192.168.113.164	Mysql-5.7.17.tar.gz
Slave1	CentOS7.4 x86_64	192.168.113.171	Mysql-5.7.17.tar.gz
Slave2	CentOS7.4 x86_64	192.168.113.172	Mysql-5.7.17.tar.gz
Amoeba	CentOS7.4 x86_64	192.168.113.173	Jdk-6u14-linux-x64.bin/amoeba-mysql-binary-2.2.0.tar.gz
Client	Redhat6.5 x86_64	192.168.113.174

Experiment steps: 1. Build MySQL master-slave replication (1) Set up a time synchronization environment to build a time synchronization server on the master node. 1) Install NTP.

yum -y install ntp

2) Configure NTP.

vim /etc/ntp.conf       #添加如下两行   server 127.127.113.0                    #本地是时钟源   

3) Restart Service

service ntpd start

(2) Time synchronization is performed on the slave node.

yum install ntp ntpdate -yservice ntpd start/usr/sbin/ntpdate 192.168.113

(3) Turn off iptables on each server, and turn off security policy.

systemctl stop firewalld.servicesetenforce 0

(4) Install MySQL database. Installed on master, Slave 1, Slave 2.

Installation detailed steps refer to installing the new version of the MySQL service on the Linux platform mysql5.7.

(5) Configure MySQL Master master server 1) in the/ETC/MY.CNF to modify or add the following content.

vim /etc/my.cnf  server-id       = 11                       //修改  log-bin=master-bin                         //主服务器日志文件  log-slave-updates=true                     

2) Restart MySQL service

systemctl restrat mysqld.service

3) Log in to the MySQL program, give authorization from the server, and view logs and offsets.

[[email protected] ~]# mysql -uroot -pabc123mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.113.%‘ IDENTIFIED BY ‘123456‘;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec)mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |      604 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

Where the file column displays the log name, the position column shows the offset, and these two values are used later in the configuration from the server. Slave should make a new update on master from this point.

(6) Configure the slave server. 1) Modify or add the following in the/ETC/MY.CNF.

vim /etc/my.cnf    server-id       = 22                 //修改    relay-log=relay-log-bin                         //从主服务器上同步日志文件记录到本地//    relay-log-index=slave-relay-bin.index           //定义relay-log的位置和名称//

Note here that Server-id cannot be the same as the primary server, and the Server-id of each server cannot be the same.

2) Restart MySQL service

systemctl restrat mysqld.service

3) Log in to the MySQL program and configure synchronization.

[[email protected] ~]# mysql -uroot -pabc123mysql> change master to        -> master_host=‘192.168.113.164‘,        -> master_user=‘myslave‘,        -> master_password=‘123456‘,        -> master_log_file=‘master-bin.000001‘,        -> master_log_pos=604;Query OK, 0 rows affected, 2 warnings (0.01 sec)

Follow the main server results to change the parameters of Master_log_file and Master_log_pos in the following command.

4) Start the synchronization.

mysql> start slave;

5) Check the slave status to ensure that the following two values are yes.

(7) Verify the master-slave copy effect. 1) Create a new DBA for the database on the primary server.

mysql> create database dba;Query OK, 1 row affected (0.11 sec)

2) in the main, from the server to view the database, the same as the database, the master-slave replication succeeds.

2. Build MySQL read-write separation (1) Install the Java environment on the host amoeba.

Because Amoeba is developed based on jdk1.5, it is recommended to use the jdk1.5 or 1.6 version, and the high version is not recommended.

[[email protected] ~]# cp jdk-6u14-linux-x64.bin /usr/local/[[email protected] ~]# cd /usr/local[[email protected] local]# ./jdk-6u14-linux-x64.bin  //安装jdk ，根据提示按enter键完成即可[[email protected] local]# mv jdk1.6.0_14/ /usr/local/jdk1.6

Configure the Java environment variables in the/etc/profile file:

vim /etc/profile  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  

Effective environment variable.

source /etc/profile

See if the Java environment is configured successfully.

[[email protected] local]# java -versionjava version "1.6.0_14"Java(TM) SE Runtime Environment (build 1.6.0_14-b08)Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

(2) Install and configure the Amoeba software.

[[email protected] ~]# mkdir /usr/local/amoeba/[[email protected] ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ //解压amoeba软件至 /usr/local/amoeba/目录下[[email protected] ~]# chmod -R 755 /usr/local/amoeba/ //设置权限[[email protected] ~]# /usr/local/amoeba/bin/amoebaamoeba start|stop   //显示此内容说明Amoeba安装成功

(3) Configure amoeba read/write separation, two slave read load balancer. 1) Master, Slave1, Slave2 Open permissions to Amoeba access.

grant all on *.* to [email protected]‘192.168.113.%‘ identified by ‘123.com‘;

2) Edit the Dbservers.xml configuration file.

abstractserver Configuration :

---23修改---- <property name="schema">mysql</property>--26-29--去掉注释-- <property name="user">test</property> <property name="password">123.com</property>

master-slave database definition :

 -----42-主服务器主机名和地址---<dbServer name="master"  parent="abstractServer"> <property name="ipAddress">192.168.113.164</property>--52-从服务器主机名----<dbServer name="slave1"  parent="abstractServer">--55-从服务器地址---- <property name="ipAddress">192.168.113.171</property> <dbServer name="slaves" virtual="true"> <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">--末尾--<property name="poolNames">slave1,slave2</property> </poolConfig>

3) Edit the Amoeba.xml configuration file.

Amoeba Connection Verification configuration :

[[email protected] ~]# cd /usr/local/amoeba/[[email protected] amoeba]# vim conf/amoeba.xml  ----30行----- <property name="user">amoeba</property> ----32行--------- <property name="password">123456</property>---117-去掉注释--- <property name="defaultPool">master</property> <property name="writePool">master</property> <property name="readPool">slaves</property>

4) After the configuration is correct, you can start the Amoeba software with the default port of tcp8066.

[[email protected] amoeba]#/usr/local/amoeba/bin/amoeba start&[1] 3257[[email protected] amoeba]# Log4j:warn log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2018-07-16 14:52:37,783 INFO context. Mysqlruntimecontext-amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:warn IP access config load Co mpleted from file:/usr/local/amoeba/conf/access_list.conf2018-07-16 14:52:38,023 INFO net. Serverableconnectionmanager-amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.2018-07-16 14:52:38,026 INFO net. Serverableconnectionmanager-amoeba Monitor Server listening on/127.0.0.1:9337.^c[[email protected] amoeba]# Netstat-ntap |           grep java//See if Java port is started TCP6 0 0 127.0.0.1:9337:::* LISTEN 3257/java      TCP6 0 0::: 8066:::* LISTEN 3257/java TCP6 0 0 192.168.113.173:57378 192.168.113.171:3306 established 3257/java tcp6 0 0 192.168.113.173:35876 192.168.113.172:3306 established 3257/java TCP      6 0 0 192.168.113.173:58654 192.168.113.164:3306 established 3257/java

(4) test. 1) Install MySQL on the client host.

You can access MySQL by proxy:

2) Create a table on master, synchronize to each slave server, then close the slave function of each slave server, and then insert the difference statement.

mysql> use dba;Database changedmysql> create table zang (id int(10),name varchar(10),address varchar(20));Query OK, 0 rows affected (0.01 sec)

Turn off the slave feature on two separate slave servers:

mysql> stop slave;Query OK, 0 rows affected (0.00 sec)

Then insert the difference statement on the primary server:

mysql> insert into zang values(‘1‘,‘zhang‘,‘this_is_master‘);Query OK, 1 row affected (0.00 sec)

3) Synchronize the table from the server and insert additional content manually.

slave1:

mysql> use dba;Database changedmysql> insert into zang values(‘2‘,‘zhang‘,‘this_is_slave1‘);Query OK, 1 row affected (0.35 sec)

slave2:

mysql> use dba;Database changedmysql> insert into zang values(‘3‘,‘zhang‘,‘this_is_slave2‘);Query OK, 1 row affected (0.01 sec)

4) test read operation

The results of the first query on the client host are as follows:

The results of the second query are as follows:

The results of the third query are as follows:

5) Test write operation:

Insert a statement on the client host:

mysql> insert into zang values(‘5‘,‘zhang‘,‘write_test‘);Query OK, 1 row affected (0.02 sec)

However, in the client can not be queried, and ultimately only on master to find this statement content, the write operation on the master server.


This verifies that MySQL realizes the read and write separation, all of the current write operations on the Master master server, to avoid the data of different steps, all read operations are allocated to slave from the server, share the pressure of the database.

MySQL master-slave replication and read/write separation