MySQL master-slave synchronization + read/write separation

MySQL master-slave synchronization + read/write separation

Experimental topology:
Three MySQL databases:
192.168.80.101 Primary server MySQL
192.168.80.102 from 1 server MySQL
192.168.80.103 from 2 server MySQL

192.168.80.104 Scheduler Amoeba server JDK, amoeba
192.168.80.105 MySQL client MySQL

The first part: three MySQL server master-slave synchronization
First, MySQL master server configuration: 192.168.80.101
1. Install and configure NTP time server:
Yum install-y NTP//Set up a time synchronization environment to build a time synchronization server on the master node
Vi/etc/ntp.conf
Restrict 192.168.80.0 mask 255.255.255.0 nomodify notrap//Remove # and modify its network segment
Server 127.127.1.0//The following two lines of new plus
Fudge 127.127.1.0 stratum 8//Set time server at level 8, top 0
: X
Service NTPD restart//restart NTP server
Service Firewalld Stop
Setenforce 0//Turn off firewall and secure SELinux
2, source code compiled to install MySQL:
Omitted
http://blog.51cto.com/13572519/2116742
3. Configure MySQL for Master master server:
VI/ETC/MY.CNF//configure under [Mysqld]

server_id = 11//Modify
Log_bin=master_bin//Opening binary log
Log_slave_updates=true//Modify add these three lines in the Mysqld zone, server_id master and slave are inconsistent, allowing the database to be updated from the server to the master server
: X

Service mysqld Restart

Mysql-u root-p//Login Database

GRANT REPLICATION SLAVE on . To ' myslave ' @ ' 192.168.80.% ' identified by ' 123456 '; Read the binary log permissions for all slave servers in 80.0 segments to the primary server
FLUSH privileges; Refresh Permissions
Show master status; To see the status of the current library, note the value of the Position column

Ii. mysql Slave server configuration: 192.168.80.102 and 192.168.80.103
Service Firewalld Stop
Setenforce 0
1. Install and configure NTP time server:
Yum Install-y ntpdate
Ntpdate 192.168.80.101//Manually synchronize with the primary server time node (this step of my operation, mysqld cannot be restarted)

Echo '/30 */usr/sbin/ntpdate 192.168.80.101 ' >>/var/spool/cron/root//write Scheduled tasks, every 30 minutes, auto sync time
CRONTAB-L//View Scheduled Tasks

2, source code compiled to install MySQL:
Omitted
http://blog.51cto.com/13572519/2116742
3. Configure two master MySQL from the server:
VI/ETC/MY.CNF//[mysqld] Below
server_id = 22//The value of server_id between servers cannot be the same from 2 settings 33
Relay_log=relay-log-bin
Relay_log_index=slave-relay-bin.index
: X
Service mysqld Restart

Mysql-u root-p//Login Database
Note thatthe value of Master_log pos= will change, to use show master status in the Lord;
Change Master to master_host= ' 192.168.80.101 ', master_user= ' myslave ', master_password= ' 123456 ', master_log_file= ' Master_bin.000002 ', master_log_pos=154;
Start slave;
Show slave status \g//The following two options need to be yes to correct

* * Second, verify MySQL master-slave synchronization:
Create a new library on the primary server and verify synchronization from the server
Mysql-u root-p
Create Database Master;
show databases; The primary server creates a database that is viewed from the server and is always synchronized from the server *


Master-Slave synchronization experiment success!!!

Part II: Configuring MySQL server read-write separation
First, on the Amoeba server configuration: 192.168.80.104**
Service Firewalld Stop
Setenforce 0

1. Install and configure NTP time server:
Yum Install-y ntpdate
Ntpdate 192.168.80.101

Echo '/30 */usr/sbin/ntpdate 192.168.80.181 ' >>/var/spool/cron/root
Crontab-l

2. Install the JDK:
Amoeba (amoeba) is a JDK-based development, so install the JDK before installing
Uploading JDK and amoeba installation packages to the server using WINSCP


Tar XF jdk-8u144-linux-x64.tar.gz-c/opt
cp-rv/opt/jdk1.8.0_144//usr/local/java

Vi/etc/profile//End NEW
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
: X

Source/etc/profile//Immediate effect

Java-version

3, Installation Amoeba:
https://sourceforge.net/projects/amoeba/files///

Yum Install-y Unzip
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
32 Line: jvm_options= "-server-xms256m-xmx1024m-xss196k-xx:permsize=16m-xx:maxpermsize=96m"
jvm_options= "-server-xms1024m-xmx1024m-xss256k"//Add this line
: X

4, make Amoeba management script
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
CTRL + Z//Amoeba boot slow, put in the background to pause running
BG//Background continue running this process

NETSTAT-ANPT | grep 8066//default listener on 8066 port

Second, the configuration read-write separation
1. The primary server creates the test database
Mysql-u root-p
Create DATABASE test;
show databases;

2. Authorization for amoeba in three MySQL databases
GRANT all on . to [e-mail protected] ' 192.168.80.% ' identified by ' 123 ';
FLUSH privileges;
3. Configure the configuration file on the dispatch server amoeba
Vi/usr/local/amoeba/conf/amoeba.xml
---28 rows-----Set the user name and password to use when the client connects to the amoeba server----
<property name= "User" >amoeba</property>
<property name= "Password" >123456</property>//above configuration for Client connection user name password
---83-88 lines-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/usr/local/amoeba/conf/dbservers.xml
---26-28 rows--------
<property name= "User" >test</property>
<property name= "Password" >123</property>

Primary server configuration:
<dbserver name= "Master" parent= "Abstractserver" >
<property name= "IpAddress" >192.168.80.101</property>

From server 1 configuration:
<dbserver name= "slave1" parent= "Abstractserver" >
--53-from server address-
<property name= "IpAddress" >192.168.80.102</property>

From server 2 configuration:
Copy slave1 related code, set from 2 server
<dbserver name= "Slave2" parent= "Abstractserver" >
<property name= "IpAddress" >192.168.80.103/property>
The following two lines modify
<dbserver name= "Slaves" virtual= "true" >
<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >

--The end--
<property name= "Poolnames" >slave1,slave2</property>
</poolConfig>
-------------------------------------------------------------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 restartctrl+z//stop and put in the background BG//Restart NETSTAT-ANPT | grep java! [] (http://i2.51cto.com/images/blog/201805/16/b3a1c5fa47c5be9ce50174757fb6a006.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) * * Part III: Test validation One, test client: 192.168.80.105**service firewalld stopsetenforce 0**1, source compilation installation mysql**: Omit http://blog.51cto.com/ 13572519/2116742 **2, test read/write separation: **mysql-u amoeba-p123456-h 192.168.80.104-p8066//amoeba address show databases;! [] (http://i2.51cto.com/images/blog/201805/16/094d3dd25b9182f787520c2e96cda858.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) New database on Master or insideTable, the two are synchronized from the server-the Use Test;create table Liu (id int (ten), name varchar (), address varchar) that is operated by amoeba; show tables; Viewing on three database servers will be the same result! [] (http://i2.51cto.com/images/blog/201805/16/280ba4ed7e4c110255d0f01d926ed16a.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ! [] (http://i2.51cto.com/images/blog/201805/16/55e7d8361a5e5c5f51ad25d80bd327c8.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ! [] (http://i2.51cto.com/images/blog/201805/16/202498e73c8bff1619540d57c9c527dc.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) After two stops from the service: stop slave; test one: the content inserted on the primary server is not synchronized-actions on the client via the amoeba operation: INSERT into Liu Values (' 1 ', ' hahahha ', ' This_is_master ') on the primary server: Use Test;select * from liu;! [] (http://i2.51cto.com/images/blog/201805/16/375c496042a5169730db59c3a36a866a.png?x-oss-process=image/watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_ 90,type_zmfuz3pozw5nagvpdgk=) * * Conclusion: Client write data write to primary server Test two: Create new content from server 1 **use test;insert into Liu Values (' 2 ', ' Liu ', ' this_is _slave1 ');----new content from server 2----use Test;insert into Liu values (' 3 ', ' Liu ', ' this_is_slave2 ');------ Test----on client amoeba for the first time to read data from server 1-the second time read from 2 to select * FROM liu;! [] (http://i2.51cto.com/images/blog/201805/16/626d7f1ddf715a8d35fcbcef9dd05ec4.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) Take turns reading from 1 from 2 * * Conclusion: The client reads data from the server read, the master server writes 1 data read not to * * * * Test three: After the client connects to the database, the data written only by the master will be recorded, and then synchronized to the slave-from the server will not record, thus achieving a read-write separation. * * Client write: INSERT into Liu Values (' 4 ', ' Liu ', ' write_test '); The newly inserted data is not visible on client amoeba-because synchronization is not turned on-----only the data can be seen on the primary server. select * from Liu; Client view, no 4 records! [] (http://i2.51cto.com/images/blog/201805/16/b25ab6978776023a32f588af6e21a97c.png?x-oss-process=image/ Watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) select * from Liu; Master server View, there are 4 records! [] (http://i2.51cto.com/images/blog/201805/16/3fbd35d6d8fba27593acbadf57773782.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) 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, only the start Slave;select * from Liu is saved locally from the server; Two operations from the server! [] (http://i2.51cto.com/images/blog/201805/16/d263d928ef1c10f81458805063412b09.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) ! [] (http://i2.51cto.com/images/blog/201805/16/acd167320fa876d00b3e119d8145f19a.png?x-oss-process=image/ watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) select * from Liu; In the client view you will see the data that our Lord has synced, as well as the data we have added locally, but I don't see any additional data from you! [] (http://i2.51cto.com/images/blog/201805/16/96e8506183df875503ee9f4862070011.png?x-oss-process=image/ Watermark,size_16,text_qduxq1rp5y2a5a6i,color_ffffff,t_100,g_se,x_10,y_10,shadow_90,type_zmfuz3pozw5nagvpdgk=) * * Conclusion: 1, Master-slave synchronization 2, When the master-slave synchronization is turned off, the data written by the client is written on the primary server and is read in turn from the server. 3, from the server can only read local data, as well as synchronization of the master above the data. Experimental success!!! **

MySQL master-slave synchronization + read-write separation