configuring MySQL master-slave synchronous read-write separation using amoeba

configuring MySQL master-slave synchronous read-write separation using amoeba

Lab Environment:
Requires three manually compiled and installed MySQL, one master, two from
Master: 192.168.88.100
From 1:192.168.88.103
From 2:192.168.88.104
Previous server: 192.168.88.101

-----Do---------------Configure the NTP server on the primary server 192.168.88.100----------//The NTP service on the primary server to provide time synchronization

Yum Install-y NTP

Vi/etc/ntp.conf

Restrict 192.168.88.0 mask 255.255.255.0 nomodify notrap
Server 127.127.1.0
Fudge 127.127.1.0 stratum 8//Set time server at level 8, top 0

Service NTPD Start
Service Firewalld Stop
Setenforce 0

To configure the Master master server for MySQL:
VI/ETC/MY.CNF//configure under [Mysqld]

server_id = 11
Log_bin=master_bin//This is the binary log file that records the operation to master
Log_slave_updates=true//Allow synchronization from server

Service mysqld Restart

Mysql-u root-p

GRANT REPLICATION SLAVE on . To ' myslave ' @ ' 192.168.88.% ' identified by ' 123456 '; Authorizing all databases for all slave servers

FLUSH privileges;
Show master status; To note the value of the Position column (mine is 603) and the value of file

! []

--------the following to configure NTP services from server 192.168.88.103 and 192.168.88.104 and from MySQL------------------

Service Firewalld Stop
Setenforce 0

Yum Install-y ntpdate
Ntpdate 192.168.88.100

! []
When offset is present, the synchronization is successful. Offset value

Echo '/30 */usr/sbin/ntpdate 192.168.88.100 ' >>/var/spool/cron/root//Scheduled tasks, synchronized with primary server every 30 minutes
Crontab-l

Then configure MySQL from the server:
VI/ETC/MY.CNF//[mysqld] Below
server_id = 22//Note The ID value cannot be the same as the ID of master, and neither of the two can be the same ID from MySQL
Relay_log=relay-log-bin
Relay_log_index=slave-relay-bin.index

Service mysqld Restart
-------NoteThe value of Master_log pos=, to use show master status in the Lord; Check, note-and ----

Mysql-u Root
Change Master to master_host= ' 192.168.88.100 ', master_user= ' myslave ', master_password= ' 123456 ', master_log_file= ' Master_bin.000001 ', master_log_pos=900; Trace the binaries of the primary MySQL server
Start slave;
Show slave status \g//The following two options need to be yes

......
Slave_io_running:yes
Slave_sql_running:yes
......

Then verify the master-slave synchronization:
------Create a new library on the primary server and verify the synchronization------------
Mysql-u root-p

Create DATABASE Hello;
show databases; The master server creates a database that is viewed from the server and is synchronized from the server.

----------------------------------configuring MySQL read-write separation---------

-----configured on the front-end server (192.168.88.101)------------

Service Firewalld Stop
Setenforce 0

Yum Install-y ntpdate
Ntpdate 192.168.88.100

Echo '/30 */usr/sbin/ntpdate 192.168.88.100 ' >>/var/spool/cron/root
Crontab-l
-----------Install JDK-------//amoeba is Java-based, so the installation requires a Java environment

Tar XF jdk-8u144-linux-x64.tar.gz
CP-RV jdk1.8.0_144//usr/local/java

Vi/etc/profile//Setting environment variables

Export Java_home=/usr/local/java
Export JRE_HOME=/USR/LOCAL/JAVA/JRE
Export path= $PATH:/usr/local/java/bin
Export Classpath=./:/usr/local/java/lib:/usr/local/java/jre/lib

Source/etc/profile

#java-version

Java version "1.8.0_144"//above appears, the Java environment is built successfully
......
......

--------------Installing amoeba------https://sourceforge.net/projects/amoeba/files/--

Unzip amoeba-mysql-3.0.5-rc-distribution.zip-d/usr/local/
mv/usr/local/amoeba-mysql-3.0.5-rc//usr/local/amoeba
Chmod-r 755/usr/local/amoeba/

Vi/usr/local/amoeba/jvm.properties

jvm_options= "-server-xms256m-xmx1024m-xss196k-xx:permsize=16m-xx:maxpermsize=96m"//Modify 32 lines, for the following parameters
Jvm_options= "-server-xms1024m-xmx1024m-xss256k"

------Make amoeba Management scripts-------
Vi/etc/init.d/amoeba

#!/bin/bash
#chkconfig: 35 62 62
#
Export Java_home=/usr/local/java
Export path= $JAVA _home/bin: $JAVA _home/jre/bin: $PATH
Name=amoeba
Amoeba_bin=/usr/local/amoeba/bin/launcher
Shutdown_bin=/usr/local/amoeba/bin/shutdown
Pidfile=/usr/local/amoeba/amoeba-mysql.pid
Scriptname=/etc/init.d/amoeba

Case "$" in
Start
Echo-n "Starting $NAME ... "
$AMOEBA _bin
echo "Done"
;;
Stop
Echo-n "stoping $NAME ... "
$SHUTDOWN _bin
echo "Done"
;;
Restart
$SHUTDOWN _bin
Sleep 1
$AMOEBA _bin
;;
*)
echo "Usage: $SCRIPTNAME {Start|stop|restart}"
Exit 1
;;
Esac

chmod +x/etc/init.d/amoeba
Chkconfig--add Amoeba

Service Amoeba Start
NETSTAT-ANPT | grep 8066//default listener on 8066 port

----------------are authorized for amoeba in three MySQL databases-------

GRANT all on . to [e-mail protected] ' 192.168.88.% ' identified by ' 123.com ';

FLUSH privileges;

--------on the front-end server----

Cd/usr/local/amoeba

VI Conf/amoeba.xml
---28 rows-----Set the user name and password to use when the client connects to the amoeba front-end server----
<property name= "User" >amoeba</property>
----30 Rows---------
<property name= "Password" >123456</property>
------------------------------------above configured for client connection user name password-------------

---83-Remove the comment-change the default Server1 to master and change the default servers to slaves
<property name= "Defaultpool" >master</property>
<property name= "Writepool" >master</property>
<property name= "Readpool" >slaves</property>

VI Conf/dbservers.xml

--26-29--Remove Annotations--
<property name= "User" >test</property>

<property name= "Password" >123.com</property>

------The primary server address---
<dbserver name= "Master" parent= "Abstractserver" >
<property name= "IpAddress" >192.168.80.181</property>

--50-from the server host name-
<dbserver name= "slave1" parent= "Abstractserver" >
--53-from server address-
<property name= "IpAddress" >192.168.80.182</property>

----Copy the relevant code---
<dbserver name= "slave1" parent= "Abstractserver" >
<factoryConfig>

192.168.88.103 192.168.88.104 ---The following two lines to modify the---- -- End-- slave1,slave2 -----------------------Note---------------------- Test The database is defined in the database, otherwise the client will report the following error when connecting: Error 1044 (42000): Could not create a validated object, Cause:validateobject failed---- -----------------------------------Service Amoeba RESTARTNETSTAT-ANPT | grep java! [] (http://i2.51cto.com/images/blog/201804/23/acb8f13aa85e173dbae8199c60f91af8.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ! []amoeba start successfully------------------------test with client------//can do service Firewalld Stopsetenforce 0yum install-y on main MySQL Mysqlmysql-u amoeba-p123456-h 192.168.88.101-p8066//amoeba address! [] (http://i2.51cto.com/images/blog/201804/23/53afe67ac2b72e876ef893b21d336112.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) The connection amoeba succeeds----the newly created database on master or the table inside, the two are synchronized from the server-the---------operated by Amoeba Mysql-u Amoeba-p123456-h 192.168.88.101- P8066use test;create table Zhang (id int, name varchar), address varchar (20));Show tables; Viewing on three database servers will be the same result---stop from the service-----Stop slave After two stops,----the content inserted on the primary server is not synchronized---insert into Zhang values (' 1 ', ' Hahahha ', ' this_is_master ');----Create a new content from server 1 at this point----directly from the top operation use Test;insert into Zhang values (' 2 ', ' Zhang ', ' This_is_ Slave1 ');----new content from server 2----use Test;insert into Zhang values (' 3 ', ' Zhang ', ' this_is_slave2 ');------ Test----on client amoeba the first time the data is read from server 1-the second time will be read from 2 to select * FROM zhang;! [] (http://i2.51cto.com/images/blog/201804/23/87ea190c8592f9fdaa44f56101142a12.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) -------------the data written after the client is connected to the database is only recorded by the master, and then synchronized to the slave-slave server is not logged, thus achieving a read-write separation. ----INSERT into Zhang Values (' 4 ', ' Zhang ', ' write_test ');-----cannot see the newly inserted data on the client amoeba-because the synchronization is not turned on-----only the data can be seen on the primary server. SELECT * from Zhang;-----After the client turns on synchronization, the data on the primary server is synchronized to each slave server, but the data added from the server is not synchronized and only locally saved---select * from Zhang; The client view will see the data that the Lord synchronizes, as well as the local additions to the data, but see no other additions from the

Configuring MySQL Master-slave synchronous read-write separation using amoeba