MySQL configuration master-slave copy, read/write separation (with Amoeba source package)

mysql5.5 is based on table lock read and write blocking, in order to solve this problem there is a master-slave replication, read and write separate cluster settings.

Three Certifications
1. master-Slave synchronous authentication
2, amoeba access to the database certification
3. Client Access Amoeba authentication

Write and read:
Client write data is written to the MySQL master server
Synchronizing the master server from the server
Reading is reading content from the server
For read and write separation

This experiment uses mysql5.5
Attached mysql5.5 source package and Installation script
Link: https://pan.baidu.com/s/1kA80VX67fXOBVChUt72__g Password: e42o

JDK file is using 1.6
Link: https://pan.baidu.com/s/1E80A82_1YTxG2BU5745o0w Password: xe6g
Amoeba
Link: https://pan.baidu.com/s/1S8OaSgYjj0aXcK5n5uoo3g Password: 16gh

This experiment uses 5 sets of CENTOS7
Same with REDHAT6 operation
Primary server address: 192.168.60.128
From server 1 Address: 192.168.60.136
From server 2 address: 192.168.60.143
Amoeba proxy Address: 192.168.60.144
Client Address: 192.168.60.129

The experiment requires us to turn off the firewall for each virtual machine, reinforcing the components of
Systemctl Stop Firewalld.service
Setenforce 0

一、使用ntp协议，时间同步

1. Primary server
Rpm-q NTP//is installed by default
vim/etc/ntp.conf//Modify NTP configuration file
Insert the following two statements in the last line
Server 127.127.60.0//Self 60 Network segment
Fudge 127.127.60.0 stratum 8//Time level
Systemctl start ntpd//Start NTP service

2, from the server (two sets of the same configuration)
Rpm-q ntpdate//The default is also installed
Systemctl start NTPD//Start service
/usr/sbin/ntpdate 192.168.60.128//Sync master server, display the following sync is successful

二、配置mysql安装mysql5.5（开头有安装包和脚本，不在赘述）

1. master server Master configuration file
Vim/etc/my.cnf
Server-id = 11//Service Unique identification
Log-bin=master-bin//Remove comments, open binary log
Log-slave-updates=true//Allow synchronization of the primary server from the server
Service MySQL restart//restart services

2. master server into MySQL to create user authorization from server
Grant replication Slave on . to ' myslave ' @ ' 192.168.60.% ' identified by ' 123123 ';
Flush privileges Refresh immediate effect
Show Master Status View Primary server state

3, from the server configuration file settings (except Server-id, two from the server settings)
Vim/etc/my.cnf
Server-id 22//Modify Server-id
Add the following two lines under Server-id
Relay-log=realy-log-bin
Relay-log-index=slave-relay-bin.index
Service mysqld Restart

4, from the server into the MySQL operation (two from the server operation the same)
Change Master to master_host= ' 192.168.60.128 ', master_user= ' myslave ', master_password= ' 123123 ', master_log_file= ' Master-bin.000001 ', master_log_pos=338;
Start slave; Turn on Copy from
Show slave status\g//view slave status

5. Master-slave replication test

    三、amoeba代理服务器

Systemctl Stop firewalld.service//Shut down firewall and enhanced components
Setenforce 0

1. Mount host, copy JDK files

2,./jdk-6u14-linux-x64.bin
Go straight to the line, there is a hint will tell you fill yes/no, fill in Yes, start the installation

3. Add Environment variables
Rename the JDK file first, so you can fill in the environment variables later
MV jdk1.6.0_14//usr/local/jdk1.6//renaming
Vim/etc/profile//Last line Add JDK environment variable, as follows

export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/bin

Scource/etc/profile//Refresh environment variables

4, Installation Amoeba
Mkdir/usr/local/amoeba//Create Amoeba Extract Directory
chmod 755/usr.local/amoeba must have 755 permissions
Tar zxf amoeba-mysql-binary-2.2.0.tar.gz-c/usr/local/amoeba///Unzip
/usr/local/amoeba/bin/amoeba//See if Amoeba is installed successfully

5. Each MySQL server creates permissions for amoeba
Grant all on the amoeba ' @ ' 192.168.60.% ' identified by ' 123456 ';
Create a user to amoeba and give permissions, amoeba will log in as this identity MySQL

6. Modify the Amoeba.xml configuration file
Vim/amoeba/conf/amoeba.xml

    <property name="user">amoeba</property>      30行修改用户为amoeba <property name="password">456789</property>   32行认证amoeba密码为456789117行<property name="defaultPool">master</property>     改成master<!--删掉            <property name="writePool">master</property>   改成master            <property name="readPool">slaves</property>    改成slaves       删掉   -->

7. Modify the Dbservers.xml configuration file

26行<property name="user">test</property>  改成test

29行<property name="password">123456</property>  去掉注释 改成认证mysql的密码

42行<dbServer name="master"  parent="abstractServer">    改成master             <factoryConfig>                    <!-- mysql ip -->                    <property name="ipAddress">192.168.60.128</property>   ip改成master的ip<dbServer name="slave2"  parent="abstractServer">   第二台slave2            <factoryConfig>                    <!-- mysql ip -->                    <property name="ipAddress">192.168.60.143</property>            </factoryConfig>    </dbServer>末尾<dbServer name="slaves" virtual="true">   池子名改为slaves<property name="poolNames">slave1,slave2</property>  池子中添加slave1,savel2

8. Open Amoeba Service
/usr/local/amoeba/bin/amoeba start& Open Service
NETSTAT-ANPT | grep java

四、客户端验证读写分离

1. Close the firewall, install MySQL and log in
Systemctl Stop Firewall
Setenforce 0
Yum Install Mysql-y
Mysql-u amoeba-p456789-h 192.168.60.144-p8066 remote via amoeba IP login

2, the master server to create a list table, from the server to stop slave
The list table created will be synchronized from the server, first we use the client to write some information to this table, to the master server to view, if the content is displayed on the primary server, the description of the write is for the primary server.
Then we separately write some information from the Server List table, in the client view, if the content is displayed from 1, from 2, the contents of the server, the description read function against from the server.
Satisfy the above two to realize the read-write separation.

3, the Client list table insert information, the master server view, proof that the write is written on the primary server

4, from the server to write data, client view, display data for data from the server, then prove that read is read from the server data

主从同步，读写分离成功！

MySQL configuration master-slave copy, read/write separation (with Amoeba source package)