基于数据库的主从同步展开下面的实验
数据库读写分离背景
- 当数据库写入的时候操作比较耗时,而读的时候很快,当大量写入操作在前,导致不能实现读的功能,就会影响查询效率,所以引入了读写分离。写是在主库进行操作,读实在从库上进行操作。
- 通过amoeba来实现读写分离,amoeba视为代理服务器。使用的是Java,采用轮询调度算法
读写分离实验
实验环境:虚拟机linux平台
实验机器:master:12.0.0.8
slave1:12.0.0.13
slave2:12.0.0.7
amoeba服务器(php):12.0.0.3
客户端(nginx):12.0.0.10
先安装java环境
- 将jdk文件和amoeba包上传到opt下
[root@php ~]# ls /opt/
amoeba-mysql-binary-2.2.0.tar.gz apr-util-1.6.0.tar.gz httpd-2.4.29 jdk-6u14-linux-x64.bin mysql-5.6.26.tar.gz php-7.1.10.tar.bz2
apr-1.6.2.tar.gz Discuz_X2.5_SC_UTF8.zip httpd-2.4.29.tar.bz2 mysql-5.6.26 php-7.1.10 rh
- 将jdk文件复制到usr/local下并执行
[root@php opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@php local]# ./jdk-6u14-linux-x64.bin
- 得到一个jdk目录并重命名
[root@php local]# mv jdk1.6.0_14 /usr/local/jdk1.6
- 然后添加环境变量
vim /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/bin
[root@php local]# source /etc/profile
- 查看版本是否改过来
[root@php local]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
安装amoeba
- 先创建一个目录存放amoeba文件,将amoeba解压放进去
[root@php local]# mkdir /usr/local/amoeba/
[root@php amoeba]# ls
benchmark bin changelogs.txt conf lib LICENSE.txt logs README.html
- 然后给目录权限
[root@php amoeba]# chmod -R 755 /usr/local/amoeba/
[root@php amoeba]# amoeba
amoeba start|stop
- 在主从上给amoeba一个用户访问数据库,三台都写
mysql> grant all on *.* to 'test'@'12.0.0.%' identified by '123.com';
- 然后去配置amoeba的配置文件,先备份一下
- 进入amoeba.xml里修改
vim amoeba.xml
30 <property name="user">amoeba</property> //修改账户名,客户端访问amoeba的账户
32 <property name="password">123123</property> //客户端访问amoeba的密码
115 <property name="defaultPool">master</property> //修改默认池
118 <property name="writePool">master</property>
119 <property name="readPool">slaves</property> //取消注释,修改读池和写池
- 进入dbServers.xml
23 <property name="schema">mysql</property> //修改为mysql库
26 <property name="user">test</property> //amoeba访问数据库的账户
29 <property name="password">123.com</property> //amoeba访问数据库的密码
45 <dbServer name="master" parent="abstractServer"> //修改数据库主服务器名
48 <property name="ipAddress">12.0.0.8</property> //修改主服务器地址
52 <dbServer name="slave1" parent="abstractServer"> //修改数据库从服务器名
53 <factoryConfig>
54 <!-- mysql ip -->
55 <property name="ipAddress">12.0.0.13</property> //修改从服务器地址
56 </factoryConfig>
57 </dbServer>
58 <dbServer name="slave2" parent="abstractServer"> //修改第二台从服务器名
59 <factoryConfig>
60 <!-- mysql ip -->
61 <property name="ipAddress">12.0.0.7</property> //第二台从服务器地址
62 </factoryConfig>
63 </dbServer>
65 <dbServer name="slaves" virtual="true"> //修改多个服务器池的名称
71 <property name="poolNames">slave1,slave2</property> //添加两个从服务器的服务器名
- 然后开启amoeba,并查看是否启动成功
[root@php conf]# amoeba start &
[1] 42073
[root@php conf]# remote application= .Amoeba:18223 response OK
amoeba server is running with port=18223
[root@php conf]# netstat -antp | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 71501/java
测试是否读写分离成功
- 下载mariadb客户端
[root@nginx ~]# yum -y install mariadb
- 登入数据库
[root@nginx ~]# mysql -u amoeba -p123123 -h 12.0.0.3 -P8066 //指定ip和端口号登入
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1719451110
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
- 测试是否主从同步
MySQL [info]> create table qq(id int,name varchar(30));
Query OK, 0 rows affected (0.92 sec)
mysql> show tables;
+----------------+
| Tables_in_info |
+----------------+
| qq |
| test |
+----------------+
2 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_info |
+----------------+
| qq |
| test |
+----------------+
2 rows in set (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_info |
+----------------+
| qq |
| test |
+----------------+
2 rows in set (0.00 sec)
- 测试是否读写分离
//两台从上关闭同步
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
MySQL [info]> insert into qq values(1,'zhangsan'); //在客户端上操作qq表插入一条数据
Query OK, 1 row affected (0.01 sec)
mysql> mysql> * from qq; //主库能看到插入的数据
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> select * from qq; //从库看不到插入的数据
Empty set (0.00 sec)
mysql> select * from qq;
Empty set (0.00 sec)
测试是否从库轮询
- 两个从库在同一个表插入不同的一条记录,然后用客户端上的数据库查询表
mysql> insert into test values(2,'lisi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
MySQL [info]> select * from test;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
MySQL [info]> select * from test;
+------+------+
| id | name |
+------+------+
| 2 | lisi |
+------+------+
1 row in set (0.01 sec)
MySQL [info]> select * from test;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.11 sec)