CENTOS7 MySQL5.7 Service cluster (for master-slave replication, read-write separation function)

Based on the previous blog post we learned that the master-slave Replication service in MySQL cluster is detailed, this article will be combined with MySQL server master-slave replication Synchronization service to achieve read and write separation operations.

Why should I implement read-write separation?

• In the MySQL database, in the process of storing data, we need to refer to different storage engines for different data types and table types, such as the MyISAM and InnoDB storage engines. And these storage engine provides the read-write mechanism to make the row or table lock, when the user accesses the data, if the background worker is modifying the data, causing the row table lock, which causes the client service interruption, the loss is immeasurable, then need to implement the modification data and read data segmentation, without affecting the service.
• When the volume of business is very large, the performance of a server can not meet the requirements, you could configure the master-slave replication to achieve read-write separation to distribute the load, to avoid the load too high to prevent the timely response to requests.

MySQL Read and write separation principle

• In short, the function of read-write separation is to write data on the primary server and only read the data from the server. The rationale is to have the primary database process transactional queries and to process select queries from the database. Database replication is used to synchronize changes caused by transactional queries to the from database in the cluster.

Experimental deployment of an experimental environment topology

Experimental operating environment

Host	System Environment	IP Address	operating software
Amoeba	centos7.3 x86_64	192.168.100.101	Amoeba
MySQL Master	centos7.3 x86_64	192.168.100.102	NTP, MySQL 5.7
MySQL from 1	centos7.3 x86_64	192.168.100.103	NTP, MySQL 5.7
MySQL from 2	centos7.3 x86_64	192.168.100.104	NTP, MySQL 5.7
Mysql-client	Redhat6.5 x86_64	192.168.100.105	MySQL 5.5

Introduction to Software Features

• NTP time synchronization software

Used for time synchronization between master and slave servers to ensure that all databases are replicated and synchronized at the same point in time. Ensure that replication synchronization is timely and accurate.

• Amoeba Agent Service Software

Amoeba is a proxy that uses MySQL as the underlying data store and provides the MySQL protocol interface to the application. It responds centrally to the application's requests, sending SQL requests to a specific database based on the rules that the user has set beforehand. This allows for load balancing, read-write separation, and high availability.

master server settings Set time synchronization

• MySQL master server is a time source

Yum Install ntp-y//install NTP software, if not, please use Yum installation
Vim/etc/ntp.conf

• Last line add:

  server 127.127.100.0                    //本地是时钟源，请注意本机所在网段是100fudge 127.127.100.0 stratum 8          //设置时间层级为8（限制在15内）

• Turn off the firewall and turn on the service

Systemectl Stop Firewalld.service
Setenforce 0
Systemctl Start Ntpd.service

Install MySQL

• See MySQL5.7 installation

Master-Slave replication configuration for the primary server

• Open Binary Log

Vim/etc/my.cnf

server-id       = 11            //设置主服务器ID号log-bin=master-bin              //主服务器日志文件//log-slave-updates=true          //从服务器更新二进制日志//

• Restart Service

Systemctl Restart Mysqld.service

• Setting up master-slave replication account Verification

Mysql-u root-p

GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.100.%‘ IDENTIFIED BY ‘123456‘;  //赋予从服务器同步权限FLUSH PRIVILEGES;   //刷新MySQL命令，使立即生效show master status;     //查看主状态+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000002 |      339 |              |                  |+-------------------+----------+--------------+------------------+1 row in set (0.00 sec)

To this, the master server Setup is complete.

From server settings

No matter how many from the server, in addition to each server itself the IP address is not the same and in the MySQL configuration file Server-id different, the other configuration master-slave replication is identical.

Set up time synchronization

• Installing NTP software

Yum Install Ntp-y

• Turn off the firewall, turn on the service, and synchronize time with the master server

Systemctl Stop Firewalld.service
Setenforce 0
Systemctl Start Ntpd.service
/usr/sbin/ntpdate 192.168.100.102//Time synchronization

Install MySQL5.7 set slave status

Vim/etc/my.cnf

server-id       = 22        //修改ID号，切记！每一个MySQL服务器都不一样relay-log=relay-log-bin     //从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index   //定义relay-log的位置和名称

Mysql-u root-p

change master to master_host=‘192.168.100.101‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000002‘,master_log_pos=339;start slave;show slave status\G;             //查看状态//

• Another from the server configuration class.

Configuring the Amoeba service for read-write separation

Systemctl Stop Firewalld.service
Setenforce 0
CP jdk-6u14-linux-x64.bin/usr/local///Copy the JDK installation command to the system
./jdk-6u14-linux-x64.bin//Perform the installation

• After the installation is complete, you can see a JDK working directory under/usr/local, rename the directory, and make it easy to specify the following working directory

MV jdk1.6.0_14//usr/local/jdk1.6

• Adding the Java environment to environment variables

Vim/etc/profile

export JAVA_HOME=/usr/local/jdk1.6      //指定工作目录export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoeba    //指定amoeba工作目录export PATH=$PATH:$AMOEBA_HOME/bin

Source/etc/profile//Refresh environment variables

• Create Amoeba working directory

Mkdir/usr/local/amoeba

• Unpacking Packages

Tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-c/usr/local/amoeba/
Chmod-r 755/usr/local/amoeba/

/usr/local/amoeba/bin/amoeba
Display Amoeba Start|stop instructions for successful installation

Grant amoeba access on the MySQL server

• Each MySQL server enters the database

Mysql-u root-p
Grant all in . to ' test ' @ ' 192.168.100.% ' identified by ' 123.com ';

Amoeba Configuring read-write separation

Cd/usr/local/amoeba
Vim conf/amoeba.xml//amoeba master configuration file

---30行-- <property name="user">amoeba</property>----32行--------- <property name="password">123456</property>            //客户端登录amoeba验证---117-去掉注释- <property name="defaultPool">master</property>   //默认数据库池为master <property name="writePool">master</property>   //设置可写入数据池的服务器为master <property name="readPool">slaves</property>    //设置可读数据库服务器为slaves

• Configuring the Amoeba Access database configuration file

Vim Conf/dbservers.xml

--25 line----<!--mysql schema--><property name= "schema" >test</property>//amoeba default to find the test database, Note that test exists no, if it does not exist can be created on the master server, can also be annotated, can also be changed to MySQL database--26-29--uncomment-<property name= "user" >test</property> <   Property name= "Password" >123.com</property>//amoeba Access database Pool Validation-----42 rows-Primary server address---<dbserver name= "master" Parent= "Abstractserver" > <property name= "ipAddress" >192.168.100.102</property>--52 rows-from the server hostname-< DBServer name= "slave1" parent= "Abstractserver" > <factoryConfig> <!--my SQL IP--<property name= "ipAddress" >192.168.100.103</property> <                /factoryconfig> </dbServer> <dbserver name= "slave2" parent= "Abstractserver" > <factoryConfig> <!--mysql IP--<property name= "ipAddress" & Gt;192.168.100.104</property> </factoryconfig> </dbServer> <dbserver name= "Slaves" virtual= "true" >//Remember, from the server pool name slaves <poolcon Fig class= "Com.meidusa.amoeba.server.MultipleServerPool" >--end--<property name= "Poolnames" >slave1,slave2 </property> </poolConfig>

• Start the Amoeba service to view the startup status

/usr/local/amoeba/bin/amoeba start&
NETSTAT-ANPT | grep java

• The configuration is successful when you view the amoeba to listen to all MySQL servers.

tcp6       0      0 127.0.0.1:25138         :::*                    LISTEN      68768/java          tcp6       0      0 :::8066                 :::*                    LISTEN      68768/java          tcp6       0      0 192.168.100.101:38906   192.168.100.102:3306    ESTABLISHED 68768/java          tcp6       0      0 192.168.100.101:60784   192.168.100.103:3306    ESTABLISHED 68768/java          tcp6       0      0 192.168.100.101:57670   192.168.100.104:3306    ESTABLISHED 68768/java  

Client-side test read-write separation

• The client first MySQL, and then through the client access to the Amoeba server IP, note that port P is uppercase.

Yum Install Mysql-y
Mysql-u amoeba-p123456-h 192.168.100.101-p8066

On this, the Amoeba Proxy service software realizes the read and write separation of MySQL database service.

CENTOS7 MySQL5.7 Service cluster (for master-slave replication, read-write separation function)