一.实验思路及重点内容
1.实验思路:
第一步 | 安装主从数据库 |
第二步 | 安装时间服务 |
第三步 | 配置主从复制 |
第四步 | 配置读写分离 |
第五步 | 测试读主从复制及读写分离 |
2.实验环境:
主机 | OS | IP地址 | 软件 | 说明概述 |
Ctos6-1: client | Centos6.5 | 192.168.200.254 | Vsftpd+mysql(客户端) | 提供基础服务及mysql集群测试客户端 |
Ctos6-2: amoeba | Centos6.5 | 192.168.200.202 | Amoeba+JDK | 提供数据请求路由,实现读写分离 |
Ctos6-3: master | Centos6.5 | 192.168.200.203 | Mysql | Mysql数据库主服务器,负责处理写请求 |
Ctos6-4: Slave1 | Centos6.5 | 192.168.200.204 | MySQL | Mysql数据库从服务器1,负责处理读请求 |
Ctos6-5: Slave1 | Centos6.5 | 192.168.200.205 | MySQL | Mysql数据库从服务器2,负责处理读请求 |
3.重点内容:
重点内容1: | 为什么使用数据库集群: 1.单台服务器无法满足业务需求(对数据库读和写),所有LAMP均在同一个服务器,mysql数据库是最容易成为系统性能瓶颈,因为MySQL读写十分频繁,而系统IO(输入/输出)是有限的无法满足MySQL频繁读写,从而导致整个网站性能低下。 2.解决MySQL瓶颈的方法一:独立出MySQL服务器,通过授权实现数据访问。 3.解决MySQL瓶颈的方法二:随着业务量增加,独立MySQL也无法满足需求,可以使用MySQL集群。MySQL支持主从复制、读写分离实现集群,从而提高并发负载能力,进一步提升网站性能(提供访问又多又快)。 |
重点内容2: | 主从复制的原理: 1.MySQL主从复制(数据保持一直)是读写分离(数据请求路由)基础。 2.MySQL主从复制的类型:基于语句、基于行、混合复制。 3.主从复制的原理(工作过程): 1)主服务器将数据更改记录到二进制日志(binary log),二进制日志可以用来进行数据恢复(本地或远程)。 2)从服务器开启IO线程及SQL线程,IO线程读取主服务器的二进制日志并写入重放日志(relay log),SQL线程读取重放日志并重放使从数据库与主数据库保持一致。 3)主服务器数据一旦有更新,从服务会重复1)和2)的步骤,数据同步过程只能是从服务器同步主服务器。 |
重点内容3: | 读写分离原理: 1.读写分离实现过程:应用服务器(客户端)请求数据库(读写请求),代理服务器将读请求(select等)和写请求(update、insert、delete等)进行分离,写请求分发给主服务器(master),读请求分发给从服务器(slave);在数据库请求中总是读多写少,所以主一般为一个,从一般为两个及以上;最终实现提高并发访问处理能力同时提高数据库可用性。 2.实现读写分离的方式: 1)基于程序代码(如网站项目)内部实现,需要开发人员实现。 2)基于中间代理层实现,由运维人员实现;常见的数据库读写分离代理软件有MySQL-Proxy、amoeba、360altas。 |
重点内容4: | 主从复制类型: 1)基于语句的复制:将主库执行的SQL语句在从库也执行一遍,在MySQL默认采用该方式,效率比较高。 2)基于行的复制:将主库变化的内容,复制到从库。 混合类型的复制:默认采用基于语句的复制,当基于语句的复制无法精确复制时采用基于行的复制。 |
重点内容5: | 代理服务器(amoeba): 优点实现数据请求的路由(读写分离)及高可用、负载均衡;缺点是不支持事务及存储过程,不适合大量数据读写场合. |
二.实验步骤
修改主机名:
[root@amoeba ~]# vi /etc/sysconfig/network ##192.168.200.202
NETWORKING=yes
IPV6=no
HOSTNAME=amoeba.linuxfan.cn
:wq
[root@ master ~]# reboot
[root@amoeba ~]# vi /etc/sysconfig/network ##192.168.200.203
NETWORKING=yes
IPV6=no
HOSTNAME=master.linuxfan.cn
:wq
[root@ master ~]# reboot
[root@ slave1 ~]# vi /etc/sysconfig/network ##192.168.200.204
NETWORKING=yes
IPV6=no
HOSTNAME=slave1.linuxfan.cn
:wq
[root@ slave1 ~]# reboot
[root@ slave2 ~]# vi /etc/sysconfig/network ##192.168.200.205
NETWORKING=yes
IPV6=no
HOSTNAME=slave2.linuxfan.cn
:wq
[root@ slave2 ~]# reboot
配置DNS解析:修改如下
[root@localhost ~]# vi /var/named/chroot/etc/named.conf
options {
listen-on port 53 { any; };
forwarders { 202.106.0.20;114.114.114.114;8.8.8.8; };
directory "/var/named";
};
zone "linuxfan.cn." IN {
type master;
file "linuxfan.cn.zone";
};
zone "200.168.192.in-addr.arpa" IN { ##添加反向解析
type master;
file "192.168.200.arpa";
};
zone "linuxren.cn." IN {
type master;
file "linuxren.cn.zone";
};
:wq
[root@localhost ~]# vi /var/named/chroot/var/named/linuxfan.cn.zone ##添加数据库及代理的A记录
$TTL 86400
@ IN SOA linuxfan.cn. root.linuxfan.cn. (
20150630
1H
2M
3W
1D
)
@ IN NS ns.linuxfan.cn.
mail IN MX 10 mail.linuxfan.cn.
@ IN A 192.168.200.254
mail IN A 192.168.200.254
ns IN A 192.168.200.254
ftp IN A 192.168.200.254
ntp IN CNAME ns
amoeba IN A 192.168.200.202
master IN A 192.168.200.203
slave1 IN A 192.168.200.204
slave2 IN A 192.168.200.205
:wq
[root@localhost ~]# vi /var/named/chroot/var/named/192.168.200.arpa ##添加反向解析PTR记录
$TTL 86400
@ IN SOA linuxfan.cn. root.linuxfan.cn. (
20150630
1H
2M
3W
1D
)
@ IN NS ns.linuxfan.cn.
mail IN MX 10 mail.linuxfan.cn.
202 IN PTR amoeba.linuxfan.cn
203 IN PTR master.linuxfan.cn
204 IN PTR slave1.linuxfan.cn
205 IN PTR slave2.linuxfan.cn
:wq
[root@localhost ~]# /etc/init.d/named restart
1. 安装主从数据库:同时在192.168.200.203/204/205上完成
[root@localhost ~]# wget ftp://192.168.200.254/tools/lamp_install_publis-app-2015-07-16.tar.xz -P /root/ ##下载LAMP安装包及脚本
[root@ master ~]# ls
anaconda-ks.cfg install.log.syslog
install.log lamp_install_publis-app-2015-07-16.tar.xz
[root@ master ~]# tar Jxf lamp_install_publis-app-2015-07-16.tar.xz -C /root ##解压软件包
[root@ master ~]# cd bin/
[root@ master bin]# mysql_install.sh &&mysql_config.sh ##安装MySQL并配置
[root@ master bin]# cd
[root@master ~]# source /etc/profile ##使配置文件生效
[root@master ~]# mysql -uroot -p123123 -s ##登录验证
mysql> quit ##退出MySQL
[root@master ~]#
2. 安装时间服务:
1)安装时间服务:192.168.200.203
[root@master ~]# yum -y install ntp &>/dev/null ##安装时间服务器
[root@master ~]# sed -i '/server/s/^/#/g' /etc/ntp.conf ##注释掉server配置
[root@master ~]# vi /etc/ntp.conf ##配置文件在最后添加如下行
server 127.127.1.0 ##时间以本服务为准
fudge 127.127.1.0 stratum 8 ##指定时区东8区
restrict 192.168.200.0 mask 255.255.255.0 nomodify notrap ##允许同步的网段
:wq
[root@master ~]# /etc/init.d/ntpd restart ##重启服务
关闭 ntpd: [失败]
正在启动 ntpd: [确定]
[root@master ~]# chkconfig ntpd on
[root@master ~]# netstat -tupln |grep 123 ##UDP/123端口是ntp协议端口
2)从服务器同步主服务器时间:192.168.200.204/205
[root@slave1 ~]# yum -y install ntpdate &>/dev/null ##安装时间同步命令
[root@slave1 ~]# /usr/sbin/ntpdate 192.168.200.203 ##同步时间
21 Sep 19:16:11 ntpdate[23537]: adjust time server 192.168.200.203 offset -0.000022 sec
[root@slave1 ~]# echo "*/5 * * * * /usr/sbin/ntpdate 192.168.200.203" >>/var/spool/cron/root ##编写计划任务每隔5分钟同步
[root@slave1 ~]# crontab -l ##查看计划任务
*/5 * * * * /usr/sbin/ntpdate 192.168.200.203
[root@slave1 ~]#
3. 配置主从复制:
1)配置主服务器:192.168.200.203
[root@master ~]# vi /etc/my.cnf
修改日志文件名称
允许从服务器更新日志
58 server-id = 11 ##修改数据库ID,集群中id必须唯一的
:wq
[root@master ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@master ~]# mysql -uroot -p123123
mysql> grant replication slave on *.* to 'linuxfan'@'192.168.200.%' identified by '123123'; ##授权用户linuxfan使用密码123123进行复制所有数据库下的所有表
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; ##刷新权限
Query OK, 0 rows affected (0.00 sec)
mysql> show master status; ##查看master的状态
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 341 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> create database db_test; ##创建测试数据库
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
[root@master ~]#
2)配置从服务器:192.168.200.204
[root@slave1 ~]# vi /etc/my.cnf
57 server-id = 22 ##修改服务器ID
58 relay-log=relay-log-bin ##定义重放日志名
定义重放日志索引文件
:wq
[root@slave1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@slave1 ~]# mysql -uroot -p123123
mysql> change master to master_host='192.168.200.203',master_user='linuxfan',master_password='123123',master_log_file='master-bin.000001',master_log_pos=0; ##修改从数据库master,注意master_log_file必须和主的一致,master_log_pos=0表示完整复制
mysql> start slave; ##启动从、开启SQL及IO线程实现同步
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.203
Master_User: linuxfan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 430
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 577
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
。。。。。。。。。。。。省略部分信息。。。。。。。。。。。
Last_IO_Errno: 0 ##如果有错误会在此出提醒,复制错误百度找解决方案
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW DATABASES; ##查看数据库db_test是否同步
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| db_test |
+--------------------+
5 rows in set (0.01 sec)
mysql>quit
3)配置从服务器:192.168.200.205
[root@slave2 ~]# vi /etc/my.cnf
57 server-id = 33
58 relay-log=relay-log-bin
59 relay-log-index=slave-relay-bin.index
:wq
[root@slave2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@slave2 ~]# mysql -uroot -p123123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.200.203',master_user='linuxfan',master_password='123123',master_log_file='master-bin.000001',master_log_pos=0;
Query OK, 0 rows affected (0.09 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.203
Master_User: linuxfan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 430
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 577
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| db_test |
+--------------------+
5 rows in set (0.00 sec)
mysql>quit
4. 配置读写分离:192.168.200.202
1)安装JDK及amoeba:
[root@amoeba ~]# wget ftp://192.168.200.254/tools/jdk-6u14-linux-x64.bin -P /root
[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin
[root@amoeba ~]# ./jdk-6u14-linux-x64.bin ##安装jdk,空格—》yes—》回车
[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba ~]# vi /etc/profile
export JAVA_HOME=/usr/local/jdk1.6 ##定义JAVA_HOME环境变量
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib ##定义JAVA类变量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin ##修改PATH变量
export AMOEBA_HOME=/usr/local/amoeba/ ##AMOEBA_HOME变量
export PATH=$PATH:$AMOEBA_HOME/bin
:wq
[root@amoeba ~]#
[root@amoeba ~]# source /etc/profile ##变量生效
[root@amoeba ~]# java -version ##查看验证java版本为1.6
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)
[root@amoeba ~]# wget ftp://192.168.200.254/tools/amoeba-mysql-binary-2.2.0.tar.gz -P /root ##下载amoeba
[root@amoeba ~]# mkdir /usr/local/amoeba ##创建目录
[root@amoeba ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ ##解压
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba ##执行验证是否成功,有提示为成功
amoeba start|stop
[root@amoeba ~]#
2)配置amoeba读写分离授权:
[root@master ~]# mysql -uroot -p123123 ##192.168.200.203
mysql> grant all on *.* to 'linuxfan'@'192.168.200.%' identified by '123123'; ##授权linuxfan用户使用密码123123从192.168.200.%网段对所有数据库有所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'linuxfan'@'192.168.200.%'; ##查看权限
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for linuxfan@192.168.200.% |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'linuxfan'@'192.168.200.%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@master ~]#
[root@slave1 ~]# mysql -uroot -p123123 ##在192.168.200.204上查看验证,已经同步权限
mysql> show grants for 'linuxfan'@'192.168.200.%';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for linuxfan@192.168.200.% |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'linuxfan'@'192.168.200.%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@slave1 ~]#
[root@slave2 ~]# mysql -uroot -p123123 ##在192.168.200.205上验证权限同步
mysql> show grants for 'linuxfan'@'192.168.200.%';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for linuxfan@192.168.200.% |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'linuxfan'@'192.168.200.%' IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
[root@slave2 ~]#
3)修改amoeba配置文件实现读写分离:192.168.200.202
[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml##主配置文件,定义客户端连接的用户及读写分离的组
修改连接MySQL数据库用户
31
修改连接MySQL数据库密码
指定默认的池
116
指定数据库写入池
指定数据库读的池,注意删除<!-- -->位置在117及120行
119 <property name="needParse">true</property>
:wq
[root@amoeba ~]# vi /usr/local/amoeba/conf/dbServers.xml##数据库配置文件,定义连接数据库的用户和密码;定义主数据库、从数据库及从数据组的访问模式(1轮询)
25 <!-- mysql user -->
26 <property name="user">linuxfan</property> ##修改用户
27
添加-->然后删除30行-->
29 <property name="password">123123</property> ##修改密码
44 <dbServer name="master" parent="abstractServer"> ##定义主数据库
45 <factoryConfig>
46 <!-- mysql ip -->
47 <property name="ipAddress">192.168.200.203</property>
48 </factoryConfig>
49 </dbServer>
50
定义从数据库1
52 <factoryConfig>
53 <!-- mysql ip -->
54 <property name="ipAddress">192.168.200.204</property>
55 </factoryConfig>
56 </dbServer>
定义从数据库2
58 <factoryConfig>
59 <!-- mysql ip -->
60 <property name="ipAddress">192.168.200.205</property>
61 </factoryConfig>
62 </dbServer>
63
定义读池
65 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
66 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
67 <property name="loadbalance">1</property>
68
69 <!-- Separated by commas,such as: server1,server2,server1 -->
添加读池成员
71 </poolConfig>
72 </dbServer>
73
74 </amoeba:dbServers>
:wq
[root@amoeba ~]#
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba start &
[4] 2848
log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2017-09-21 20:37:45,547 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
[root@amoeba ~]#
[root@amoeba ~]# netstat -utpln |grep 8066
tcp 0 0 :::8066 :::* LISTEN 2848/java
5.测试读写分离
1)验证主从复制:192.168.200.254
yum -y install mysql
mysql -ulinuxfan -p123123-h 192.168.200.202-P 8066 ##登录db集群192.168.200.202
mysql>show databases;
mysql> use db_test;
mysql> create table linuxfan(id int(10),name varchar(10),address varchar(20));
在192.168.200.203-205上查看结果:
mysql -uroot -p123123
mysql> use db_test;
mysql> show tables; ##已然同步
2)关闭slave1,slave2的复制功能:192.168.100.204-205
mysql> stop slave;
3)分别在master,slave1,slave2上创建不同的数据:
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)应用客户端验证读:192.168.200.254
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)应用客户端上验证写:
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)
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)
总结:
app写入数据时,amoeba会将数据路由到master上进行存储,app读取数据时,amoeba会将读的请求一轮询的方式发给slaves组(slave1+slave2),实现读写分离。
master和slaves间配置了主从复制,保证了数据的一致性。
注意实际工作中测试完成后,必须开启主从复制。如下:
slave1和slave2:
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在客户端验证:
mysql> select * from linuxfan;
+------+------+----------------+
| id | name | addr |
+------+------+----------------+
| 3 | hehe | this is slave2 |
| 1 | hehe | this is master |
| 4 | hehe | app write test |
+------+------+----------------+
3 rows in set (0.00 sec)
mysql> select * from linuxfan;
+------+------+----------------+
| id | name | addr |
+------+------+----------------+
| 2 | hehe | this is slave1 |
| 1 | hehe | this is master |
| 4 | hehe | app write test |
+------+------+----------------+
3 rows in set (0.01 sec)
mysql> quit
Bye