mysql读写分离
一、 mysql读写分离
1、 配置mysql读写分离
1)
设置hosts文件
[root@centos01 ~]# vim /etc/hosts
192.168.100.10 centos01
192.168.100.20 centos02
192.168.100.30 centos03
192.168.100.40 centos04
192.168.100.50 centos05
2)
复制hosts文件到其他节点
[root@centos01 ~]# scp /etc/hosts root@192.168.100.20:/etc/
[root@centos01 ~]# scp /etc/hosts root@192.168.100.30:/etc/
[root@centos01 ~]# scp /etc/hosts root@192.168.100.40:/etc/
[root@centos01 ~]# scp /etc/hosts root@192.168.100.50:/etc/
3)
amoeba服务器同步时间
[root@centos05 ~]# ntpdate 192.168.100.10
26 Mar 15:42:04 ntpdate[39976]: step time server 192.168.100.10 offset -28800.726371 sec
2、 安装并配置JDK和amoeba
1)
安装jdk
[root@centos05 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos05 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos05 ~]# cp /mnt/jdk-6u14-linux-x64.bin /usr/src/
[root@centos05 ~]# cd /usr/src/
[root@centos05 src]# ./jdk-6u14-linux-x64.bin
Do you agree to the above license terms? [yes or no]
Yes
[root@centos05 ]# mv /usr/src/jdk1.6.0_14/ /usr/local/jdk
2)
安装amoeba
[root@centos05 ~]# mkdir /usr/local/amoeba
[root@centos05 ~]# tar zxvf /mnt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
3)
配置jdk环境变量
[root@centos05 ~]# vim /etc/profile.d/java
export JAVA_HOME=/usr/local/jdk/
export CLASSPATH=
C
L
A
S
S
P
A
T
H
:
CLASSPATH:
CLASSPATH:JAVA_HOME/lib:
J
A
V
A
H
O
M
E
/
j
r
e
/
l
i
b
/
e
x
p
o
r
t
P
A
T
H
=
JAVA_HOME/jre/lib/ export PATH=
JAVAHOME/jre/lib/exportPATH=JAVA_HOME/lib:
J
A
V
A
H
O
M
E
/
j
r
e
/
b
i
n
:
JAVA_HOME/jre/bin:
JAVAHOME/jre/bin:PATH:
H
O
M
E
/
b
i
n
e
x
p
o
r
t
A
M
O
E
B
A
H
O
M
E
=
/
u
s
r
/
l
o
c
a
l
/
a
m
o
e
b
a
/
e
x
p
o
r
t
P
A
T
H
=
HOME/bin export AMOEBA_HOME=/usr/local/amoeba/ export PATH=
HOME/binexportAMOEBAHOME=/usr/local/amoeba/exportPATH=PATH:$AMOEBA_HOME/bin
[root@centos05 ~]# source /etc/profile.d/java
4)
检查jdk版本
[root@centos05 ~]# java -version
5)
授权amoeba账户和密码访问mysql数据库
[root@centos01 ~]# mysql -uroot -ppwd@123
mysql> grant all on . to ‘bob’@‘192.168.100.%’ identified by ‘pwd@123’;
Query OK, 0 rows affected (0.01 sec)
6)
配置修改amoeba主配置文件连接账户密码
[root@centos05 ~]# vim /usr/local/amoeba/conf/amoeba.xml
30 amoeba
32 pwd@123
115 centos01
117 centos01
118 slaves
7)
添加主从服务器到创建的计算机池中
[root@centos05 ~]# vim /usr/local/amoeba/conf/dbServers.xml
26 bob
28 pwd@123
43
44
45 192.168.100.10
46
47
48
49
50
51 192.168.100.30
52
53
54
55
56
57 192.168.100.40
58
59
60
61
62 1
63
64 centos03,centos04
65
66
67
68 </amoeba:dbServers>
8)
启动amoeba服务,监听8600
[root@centos05 ~]# /usr/local/amoeba/bin/amoeba start&
[root@centos05 ~]# netstat -anptu | grep 8066
3、 客户端验证读写分离
1)
安装mysql客户端
[root@centos06 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos06 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos06 ~]# yum -y install mysql
2)
登陆amoeba服务器,账户amoeba密码pwd@123
[root@centos06 ~]# mysql -uamoeba -ppwd@123 -h 192.168.100.50 -P8066
3)
在qaq库中创建aaa表并写入数据
MySQL [(none)]> show databases;
MySQL [(none)]> create table qaq.aaa (姓名 char(4),电话 char(11));
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> insert into qaq.aaa values (‘王二’,‘11111111111’);
Query OK, 1 row affected (0.01 sec)
4)
主和从查看
[root@centos01 ~]# mysql -uroot -ppwd@123
mysql> select * from qaq.aaa;
[root@centos03 ~]# mysql -uroot -ppwd@123
mysql> select * from qaq.aaa;
5)
停掉两个从节点,mysql客户端在插入数据
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> insert into qaq.aaa values (‘李四’,‘22222222222’);
Query OK, 1 row affected (0.00 sec)
6)
主、从节点和mysql客户端查看
7)
开启两个从节点mysql在查看
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)