linux-centos7.4 MySQL Database master-slave synchronization, read and write separation

MySQL master-slave synchronization and read/write separation

Directory
The first part of the experimental environment
The second part configures master-slave synchronization
The third part configures read-write separation
Part IV Test Validation

The first part of the experimental environment

1. Experimental topology diagram:
   
2. Server Five:
   1) Client Server one:
   IP Address: 192.168.80.10 (client)
   Software Installation Required: mysql-boost-5.7.20.tar.gz
   
   2) Amoeba Dispatch server one:
   IP address: 192.168.80.20 (Amoeba)
   Software Installation Required: jdk-8u144-linux-x64.tar.gz
   Amoeba-mysql-3.0.5-rc-distribution.zip
   
   3) MySQL master server one:
   IP address: 192.168.80.30 (Master)
   Software Installation Required: mysql-boost-5.7.20.tar.gz
   4) MySQL from server two:
   IP address: 192.168.80.40 (SLAVE01)
   192.168.80.50 (SLAVE02)
   Software Installation Required: mysql-boost-5.7.20.tar.gz
3. System Information
   
   Note: This experiment MySQL is the source code compilation installation, this document involves the installation configuration MySQL database partial omission, please refer to the document: http://blog.51cto.com/12227558/2074113

The second part master-slave synchronization (need three MySQL server)
First step MySQL master server Setup configuration (192.168.80.30)
One: Build an NTP time server on the main MySQL server
[[email protected] ~]# Yum install-y NTP
[Email protected] ~]# vi/etc/ntp.conf
Restrict 192.168.80.0 mask 255.255.255.0 nomodify notrap//Modify, remove # #
Server 127.127.1.0//Add
Fudge 127.127.1.0 Stratum 8//Add, set time server to level 8, top 0

Save exit
[Email protected] ~]# service ntpd restart
[[Email protected] ~]# service FIREWALLD stop
[Email protected] ~]# Setenforce 0

Two: source code compiled to install MySQL
Here reference http://blog.51cto.com/12227558/2074113, source installation and configuration MySQL

Three: Configure MySQL for Master master server
[Email protected] ~]# VI/ETC/MY.CNF
Configure under [mysqld]
server_id = 11
Log_bin=master_bin
Log_slave_updates=true

Save exit
[Email protected] ~]# service mysqld restart
[[email protected] ~]# mysql-uroot-pabc123//Login Database
Mysql> GRANT REPLICATION SLAVE on . To ' myslave ' @ ' 192.168.80.% ' identified by ' 123456 ';
Authorizing all databases for all slave servers
mysql> FLUSH privileges; Refresh Permissions
Mysql> Show master status;

Second step configuration from Server Slave01 (192.168.80.40)
[[Email protected] ~]# service FIREWALLD stop
[Email protected] ~]# Setenforce 0
One: Set time synchronization (sync to master server)
[email protected] ~]# Yum install-y ntpdate
[[email protected] ~]# ntpdate 192.168.80.30//manual Sync Time
[[email protected] ~]# echo '/30 */usr/sbin/ntpdate 192.168.80.30 ' >>/var/spool/cron/root
Write scheduled tasks, every 30 minutes, automatically sync time
[[email protected] ~]# crontab–l//View Scheduled Tasks

Two: source code compiled to install MySQL
Here reference http://blog.51cto.com/12227558/2074113, source installation and configuration MySQL

Three: Configure SLAVE01 from the server for MySQL
[Email protected] ~]# VI/ETC/MY.CNF
server_id = 22
Relay_log=relay-log-bin
Relay_log_index=slave-relay-bin.index

Save exit
[Email protected] ~]# service mysqld restart
[[email protected] ~]# mysql-uroot-pabc123//Login Database
mysql> Change Master to master_host= ' 192.168.80.30 ', master_user= ' myslave ', master_password= ' 123456 ', Master_log_ File= ' master_bin.000001 ', master_log_pos=603;

mysql> start slave;
Mysql> Show Slave status \g

Step three configuration from Server Slave02 (192.168.80.50)
One: Set time synchronization (sync to master server)
[[Email protected] ~]# service FIREWALLD stop
[Email protected] ~]# Setenforce 0
[email protected] ~]# Yum install-y ntpdate
[Email protected] ~]# ntpdate 192.168.80.30
[[email protected] ~]# echo '/30 */usr/sbin/ntpdate 192.168.80.30 ' >>/var/spool/cron/root
[Email protected] ~]# crontab–l

Two: source code compiled to install MySQL
Here reference http://blog.51cto.com/12227558/2074113, source installation and configuration MySQL

Three: Configure SLAVE02 from the server for MySQL
[Email protected] ~]# VI/ETC/MY.CNF
server_id = 33
Relay_log=relay-log-bin
Relay_log_index=slave-relay-bin.index

Save exit
[Email protected] ~]# service mysqld restart
[Email protected] ~]# mysql-uroot-pabc123
mysql> Change Master to master_host= ' 192.168.80.30 ', master_user= ' myslave ', master_password= ' 123456 ', Master_log_ File= ' master_bin.000001 ', master_log_pos=603;

mysql> start slave;
Mysql> Show Slave status \g

Fourth Step verification
Create a new library on the primary server and verify synchronization
mysql> CREATE DATABASE Hello;
mysql> show databases;

Validating the results from the server
SLAVE01:
mysql> show databases;

SLAVE02:
mysql> show databases;

Validation successful

Part III read-write separation
The first step is configured on the Amoeba server (192.168.80.20)
[[Email protected] ~]# service FIREWALLD stop
[Email protected] ~]# Setenforce 0

One: Set the sync time
[email protected] ~]# Yum install-y ntpdate
[Email protected] ~]# ntpdate 192.168.80.181
[[email protected] ~]# echo '/30 */usr/sbin/ntpdate 192.168.80.30 ' >>/var/spool/cron/root
[Email protected] ~]# crontab–l

II: Install JDK
[Email protected] ~]# Tar XF jdk-8u144-linux-x64.tar.gz
[Email protected] ~]# CP-RV jdk1.8.0_144//usr/local/java
[Email protected] ~]# Vi/etc/profile
Finally add the following content:
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
Save exit
[[email protected] ~]# source/etc/profile//immediate effect
[Email protected] ~]# java–version

Three: Install Amoeba//:https://sourceforge.net/projects/amoeba/files/
[email protected] ~]# Yum install-y unzip
[Email protected] ~]# Unzip amoeba-mysql-3.0.5-rc-distribution.zip-d/usr/local/
[Email protected] ~]# mv/usr/local/amoeba-mysql-3.0.5-rc//usr/local/amoeba
[Email protected] ~]# chmod-r 755/usr/local/amoeba/
[Email protected] ~]# vi/usr/local/amoeba/jvm.properties
Modify the following content:
jvm_options= "-server-xms1024m-xmx1024m-xss256k"//32 Line
Save exit

Four: Making Amoeba management scripts
[Email protected] ~]# 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
Save exit
[Email protected] ~]# chmod +x/etc/init.d/amoeba
[Email protected] ~]# chkconfig--add Amoeba
[[Email protected] ~]# service amoeba start
Slow start-up process, CTRL + Z in the background to suspend operation,
[[email protected] ~]# BG//Background continue running

[Email protected] ~]# NETSTAT-ANPT | grep 8066

Second step to configure read-write separation
One: The master server creates the test database
mysql> CREATE DATABASE test;
mysql> show databases;

Two: Authorized for amoeba in three MySQL database servers
Mysql> GRANT all on . to [e-mail protected] ' 192.168.80.% ' identified by ' abc123 ';
mysql> FLUSH privileges;

Three: Edit the Amoeba profile on the amoeba server
[[[email protected] ~]# Cd/usr/local/amoeba
[[email protected] Amoeba ]# VI conf/amoeba.xml
//Edit the following
<property name= "user" >amoeba</property>//28 line
<property name = "Password" >123456</property>//30 line
//above sets the user name and password used by the client to connect amoeba front End server

<property name= " Defaultpool ">MASTER</PROPERTY>
<property name=" Writepool ">MASTER</PROPERTY>
< Property Name= "Readpool" >SLAVES</PROPERTY>
//83 lines start to modify, 85 lines and 88 lines are comments, delete

Save exit
[[email  Protected] amoeba]# VI conf/dbservers.xml
Modify the following:
<property name= "user" >test</property>

<property name= "Password" >abc123</property>

The following is the primary server configuration:
<dbserver name= "Master" parent= "Abstractserver" >
<property name= "IpAddress" >192.168.80.30</property>

SLAVE01 Configuration from server:
<dbserver name= "slave1" parent= "Abstractserver" >
<property name= "IpAddress" >192.168.80.40</property>

SLAVE02 Configuration from server:
Copy SLAVE01 related content (total six lines), paste directly down, and make changes
<dbserver name= "Slave2" parent= "Abstractserver" >
<property name= "IpAddress" >192.168.80.50</property>

The following changes are two lines of content:

Save exit
[Email protected] amoeba]# service Amoeba restart
Press CTRL+Z,BG, run back
[Email protected] amoeba]# NETSTAT-ANPT | grep 8066

Part IV Test Validation
First steps on the client (192.168.80.10)
[[Email protected] ~]# service FIREWALLD stop
[Email protected] ~]# Setenforce 0
One: source code compiled to install MySQL
Here reference http://blog.51cto.com/12227558/2074113, source installation and configuration MySQL

Second: test read-Write separation
A new database on master or a table inside, two from the server will be synchronized--through the amoeba operation

1. Client logon operations
   [Email protected] @client ~]# mysql-u amoeba-p123456-h 192.168.80.20-p8066
   mysql> show databases;
   
   mysql> use test;
   Mysql> CREATE TABLE Zhang (id int (ten), name varchar), address varchar (20));
   Mysql> Show tables;
   
   Viewing on three database servers will be the same result
   
   
   

2. Stop the master-slave synchronization service on two slave servers
   mysql> stop Slave;

Test one: The content inserted on the primary server is not synchronized-the amoeba operation
Operating on the client
Mysql> INSERT into Zhang values (' 1 ', ' hahahha ', ' this_is_master ');
Viewing on the primary server

Viewing on two slave servers
mysql> show databases;
mysql> use test;
Mysql> select * from Zhang;

Test Two: Create a new content from the server
SLAVE01:
mysql> use test;
Mysql> INSERT into Zhang values (' 2 ', ' Zhang ', ' this_is_slave1 ');

SLAVE02:
mysql> use test;
Mysql> INSERT into Zhang Values (' 3 ', ' Zhang ', ' this_is_slave2 ');

Test on the client, the first time the data is read from the server SLAVE01, the second will read to the SLAVE02

Test three: The data written after the client is connected to the database is only recorded by the master and then synchronized to the slave-from the server
Can be recorded, thus achieving a read-write separation
Operations on the client
Mysql> INSERT into Zhang Values (' 4 ', ' Zhang ', ' write_test ');
This record can only be seen on the master server.

The newly inserted data is not visible on the client, because synchronization is not enabled

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 itself is not synchronized and only locally saved
mysql> start slave; On two slave servers
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 ourselves:

Test complete

linux-centos7.4 MySQL Database master-slave synchronization, read and write separation