MySQL master-slave replication and read/write separation

MySQL master-slave replication and read/write separation
2016.3.16 by linuxfan.cn
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://FTP.LINUXFAN.CN/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 ftp.linuxfan.cn
&GT;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" >linuxfan</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 Linuxfan (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 Linuxfan values (1, ' hehe ', ' This is Master ');
SLAVE1:
mysql> INSERT INTO Linuxfan values (2, ' hehe ', ' This is slave1 ');
Slave2:
mysql> INSERT into Linuxfan values (3, ' hehe ', ' This is Slave2 ');

4) Apply client-side validation read: 192.168.100.157
Mysql> select * from Linuxfan; # #第一次查询
+------+------+----------------+
| ID | name | Address |
+------+------+----------------+
| 2 | hehe | This is Slave1 |
+------+------+----------------+
1 row in Set (0.02 sec)

Mysql> select * from Linuxfan; # #第二次查询
+------+------+----------------+
| ID | name | Address |
+------+------+----------------+
| 3 | hehe | This is Slave2 |
+------+------+----------------+
1 row in Set (0.01 sec)
Mysql> select * from Linuxfan; # #第三次查询
+------+------+----------------+
| 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 Linuxfan values (4, ' hehe ', ' app write Test '); # #写入数据
Query OK, 1 row affected (0.02 sec)

Mysql> select * from Linuxfan; # #查不到刚写入的数据
+------+------+----------------+
| ID | name | Address |
+------+------+----------------+
| 3 | hehe | This is Slave2 |
+------+------+----------------+
1 row in Set (0.01 sec)

Verify on Master:
Mysql> select * from Linuxfan; # #查到数据
+------+------+----------------+
| 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 "Concealing delicated" blog, be sure to keep this source http://stlong.blog.51cto.com/5144113/1788539

MySQL master-slave replication and read/write separation