MySQL master-slave replication and read/write separation

Environment Introduction:

Primary server (Master): 192.168.100.155

Slave Server (slave1,slave2): 192.168.100.153-154

Proxy Server (amoeba): 192.168.100.156

App Client (APP): 192.168.100.157

1. Build the time server:

The NTP Time service is installed on the master node: 192.168.100.155

Yum-y Install NTP

Sed-i '/^server/s/^/#/g '/etc/ntp.conf

Cat <<end >>/etc/ntp.conf

Server 127.127.1.0

Fudge 127.127.1.0 Stratum 8

END

/ETC/INIT.D/NTPD restart

NETSTAT-UTPLN |grep NTP

Synchronization time from node: 192.168.100.153-154

Yum-y Install Ntpdate

/usr/sbin/ntpdate 192.168.100.155

2. Installing mysql:192.168.100.153-155

wget FTP://192.168.100.100/TOOLS/LAMP_INSTALL_PUBLIS-APP-2015-07-16.TAR.XZ

Tar jxvf lamp_install_publis-app-2015-07-16.tar.xz

mysql_install.sh

mysql_config.sh

Reboot

Tips:

[Email protected] ~]# CD bin/

[email protected] bin]# cat mysql_install.sh

#!/bin/bash

# #第一配置yum, install ncurses dependency Pack

Yum-y Install ncurses-*

#解压cmake, install the base environment

Tar zxvf/root/cmake-2.8.6.tar.gz-c/usr/src/

cd/usr/src/cmake-2.8.6

#配置, compile and install CMake

./configure &&gmake &&gmake Install

# #解压mysql

Tar zxvf/root/mysql-5.5.22.tar.gz-c/usr/src/

cd/usr/src/mysql-5.5.22/

#cmake进行配置mysql

Cmake-dcmake_install_prefix=/usr/local/mysql #指定安装目录 \

-ddefault_charset=utf8 #指定字符集为utf8 \

-DDEFAULT_COLLATION=UTF8_GENERAL_CI # #指定字符校验 \

-dwith_extra_charsets=all # #支持额外字符集 \

-dsysconfdir=/etc/# #指定配置文件位置

Make &&make Install #编译安装

if [-e/usr/local/mysql];then

echo "MySQL install successfully."

Fi

[Email protected] bin]#

[email protected] bin]# cat mysql_config.sh

#!/bin/bash

#1. Copying a configuration file

Cp/usr/src/mysql-5.5.22/support-files/my-medium.cnf/etc/my.cnf

#2. Adding system Services

Cp/usr/src/mysql-5.5.22/support-files/mysql.server/etc/init.d/mysqld

chmod +x/etc/init.d/mysqld

Chkconfig--add mysqld

Chkconfig mysqld on

#3. Optimizing the path path, convenient execution of commands, double quotes in single quotes

grep mysql/etc/profile

If [$?-eq 0];then

echo "PATH is set."

Else

echo "Export path= $PATH:/usr/local/mysql/bin" >>/etc/profile

Source/etc/profile # #执行文件

Fi

#4. Initialize MySQL, create user, empower

Useradd-m-s/sbin/nologin MySQL

Chown-r Mysql:mysql/usr/local/mysql

/usr/local/mysql/scripts/mysql_install_db \

--basedir=/usr/local/mysql \

--datadir=/usr/local/mysql/data--user=mysql

#5. Start MySQL and set it to boot

if [-e/tmp/mysql.sock];then

/etc/init.d/mysqld restart

Else

/etc/init.d/mysqld start

Fi

Chkconfig mysqld on

#6. Change the password and prompt the password

mysqladmin-u root password ' 123123 ' &&echo "MySQL root password is 123123"

3. Configure MySQL Master-slave replication:

1) master server configuration: 192.168.100.155

Sed-i ' s/^log-bin=.*/log-bin=master-bin\nlog-slave-updates=ture/g '/etc/my.cnf

Sed-i '/^server-id/s/1/11/g '/etc/my.cnf

/etc/init.d/mysqld restart

Mysql-uroot-p123123

mysql> grant replication Slave on * * to ' myslave ' @ ' 192.168.100.% ' identified by ' 123123 ';

mysql> flush Privileges;

Mysql> Show master status; # #记住File的及Position的值, here for master-bin.000001 and 337

mysql> CREATE DATABASE Db_test; # #创建测试数据库

Mysql> quit

2) Configure 1:192.168.100.153 from server

Sed-i '/^server-id/s/1/22/g '/etc/my.cnf

Sed-i '/^server-id/arelay-log=relay-log-bin\nrelay-log-index=slave-relay-bin.index '/etc/my.cnf

/etc/init.d/mysqld restart

Mysql-uroot-p123123

mysql> Change Master to master_host= ' 192.168.100.155 ', master_user= ' myslave ', master_password= ' 123123 ', Master_log _file= ' master-bin.000001 ', master_log_pos=337;

mysql> start slave;

Mysql> show Slave status\g; # #查看无error即可

mysql> show databases; # #验证数据库是否同步

Mysql> quit

3) Configure 2:192.168.100.154 from server

Sed-i '/^server-id/s/1/33/g '/etc/my.cnf

Sed-i '/^server-id/arelay-log=relay-log-bin\nrelay-log-index=slave-relay-bin.index '/etc/my.cnf

/etc/init.d/mysqld restart

Mysql-uroot-p123123

mysql> Change Master to master_host= ' 192.168.100.155 ', master_user= ' myslave ', master_password= ' 123123 ', Master_log _file= ' master-bin.000001 ', master_log_pos=337;

mysql> start slave;

Mysql> show Slave status\g; # #查看无error即可

mysql> show databases; # #验证数据库是否同步

Mysql> quit

4. Build MySQL read/write separation:

1) Install the software: 192.168.100.156

Lftp 192.168.100.100

>CD tools/

>get amoeba-mysql-binary-2.2.0.tar.gz Jdk-6u14-linux-x64.bin

>bye

Yum-y Remove Java

chmod +x Jdk-6u14-linux-x64.bin

./jdk-6u14-linux-x64.bin

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

Vi/etc/profile

Export java_home=/usr/local/jdk1.6

Export classpath= $CLASSPATH: $JAVA _home/lib: $JAVA _home/jre/lib

Export path= $JAVA _home/lib: $JAVA _home/jre/bin/: $PATH: $HOME/bin

Export Amoeba_home=/usr/local/amoeba

Export path= $PATH: $AMOEBA _home

: Wq

Source/etc/profile

Java-version

Mkdir/usr/local/amoeba

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 # #验证

2) data authorization to amoeba read and Write permission:

Mysql> Grant All on * * to [e-mail protected] ' 192.168.100.% ' identified by ' 123123 '; # #在192 completed on 168.100.155.

Mysql> Show grants for [email protected] ' 192.168.100.% '; # #在192 to see if permissions are synced on the 168.100.153-154.

3) Modify configuration file: 192.168.100.156

Vim/usr/local/amoeba/conf/amoeba.xml

<property name= "User" >amoeba</property>

31

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

<property name= "Defaultpool" >master</property>

116

117 <property name= "Writepool" >master</property> # #注意删除 <!----> Comments

118 <property name= "Readpool" >slaves</property>

: Set Nu # #显示行号

: Wq

Vim/usr/local/amoeba/conf/dbservers.xml

<!--mysql User--

<property name= "User" >linuxyy</property> # #该用户必须是上一步授权的用户

27

<!--mysql password--# #修改

<property name= "Password" >123123</property> # #删除下一行的 "--"

<dbserver name= "Master" parent= "Abstractserver" > # #修改为master

<factoryConfig>

<!--mysql IP--

<property name= "ipAddress" >192.168.100.155</property> # #指定正确的master的ip

Wuyi <dbserver name= "slave1" parent= "Abstractserver" > # #修改为slave1

<factoryConfig>

<!--mysql IP--

<property name= "ipAddress" >192.168.100.153</property> # #指定slave1的ip地址

</factoryConfig>

</dbServer>

<dbserver name= "Slave2" parent= "Abstractserver" > # #添加如下6行, specifying SLAVE2 IP

<factoryConfig>

<!--mysql IP--

<property name= "IpAddress" >192.168.100.154</property>

</factoryConfig>

</dbServer>

<dbserver name= "Slaves" virtual= "true" > # #修改为slaves

<property name= "Poolnames" >slave1,slave2</property> # #修改集群的成员名称用逗号隔开

: Wq

/usr/local/amoeba/bin/amoeba start& # #启动代理服务

NETSTAT-UTPLN |grep 8066 # #验证

5. Test read/write separation

1) Verify Master-slave replication: 192.168.100.157

Yum-y install MySQL

Mysql-uamoeba-p123456-h 192.168.100.156-p 8066 # #登录db集群192.168.100.157

Mysql>show databases;

mysql> use db_test;

Mysql> CREATE TABLE LINUXYY (id int (), name varchar, address varchar (20));

To view the results on 192.168.100.153-155:

Mysql-uroot-p123123

mysql> use db_test;

Mysql> Show tables; # #已然同步

2) Turn off the slave1,slave2 copy function: 192.168.100.153-154

mysql> stop Slave;

3) Create different data on the master,slave1,slave2, respectively:

Master

mysql> INSERT INTO LINUXYY values (1, ' hehe ', ' This is Master ');

SLAVE1:

mysql> INSERT INTO LINUXYY values (2, ' hehe ', ' This is slave1 ');

Slave2:

mysql> INSERT into LINUXYY values (3, ' hehe ', ' This is Slave2 ');

4) Apply client-side validation read: 192.168.100.157

Mysql> select * from LINUXYY; # #第一次查询

+------+------+----------------+

| ID | name | Address |

+------+------+----------------+

| 2 | hehe | This is Slave1 |

+------+------+----------------+

1 row in Set (0.02 sec)

Mysql> select * from LINUXYY; # #第二次查询

+------+------+----------------+

| ID | name | Address |

+------+------+----------------+

| 3 | hehe | This is Slave2 |

+------+------+----------------+

1 row in Set (0.01 sec)

Mysql> select * from LINUXYY; # #第三次查询

+------+------+----------------+

| ID | name | Address |

+------+------+----------------+

| 2 | hehe | This is Slave1 |

+------+------+----------------+

1 row in Set (0.00 sec)

5) Apply the authentication write on the client:

mysql> INSERT INTO LINUXYY values (4, ' hehe ', ' app write Test '); # #写入数据

Query OK, 1 row affected (0.02 sec)

Mysql> select * from LINUXYY; # #查不到刚写入的数据

+------+------+----------------+

| ID | name | Address |

+------+------+----------------+

| 3 | hehe | This is Slave2 |

+------+------+----------------+

1 row in Set (0.01 sec)

Verify on Master:

Mysql> select * from LINUXYY; # #查到数据

+------+------+----------------+

| ID | name | Address |

+------+------+----------------+

| 1 | hehe | This is Master |

| 4 | hehe | App Write Test |

+------+------+----------------+

2 rows in Set (0.00 sec)

Summarize:

When the app writes data, amoeba routes the data to master for storage, and when the app reads the data, amoeba sends the read request to the Slaves group (SLAVE1+SLAVE2) in a round-robin manner for read-write separation.

Master and slaves are configured with master-slave replication to ensure data consistency.

This article is from the "11628205" blog, please be sure to keep this source http://11638205.blog.51cto.com/11628205/1982339

MySQL master-slave replication and read/write separation