1、用Keepalived实现nginx与lvs的高可用集群；
lvs+keepalived：
1）后端两台rs上安装web服务并创建探测页面
~]# yum install nginx -y ~]# systemctl start nginx.service ~]# vim /usr/share/nginx/html/index.html <h1>RS1 Server</h1> ~]# vim /usr/share/nginx/html/index.html <h1>RS2 Server</h1>
2）在两台rs上创建脚本，用于启停DR模式下的网卡相关参数修改
~]# vim skp.sh
#!/bin/bash
VIP=192.168.0.20
case $1 in
start)
ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP
/sbin/route add -host $VIP dev lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
sysctl -p &>/dev/null
echo "LVS-DR Configuration is OK!"
;;
stop)
ifconfig lo:0 down
route del $VIP >/dev/null 2>&1
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "Configuration Cleared!"
;;
*)
echo "Usage $0 {start|stop}"
exit 1
esac
~]# chmod +x skp.sh
~]# ./skp.sh start
~]# ifconfig
...
lo:0: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 192.168.0.20 netmask 255.255.255.255
loop txqueuelen 0 (Local Loopback)
3)配置keepalived主节点
~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from kaadmin@magedu.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node1 #设置lvs的id，确保主备一致
vrrp_mcast_group4 224.0.100.18 #设置组播网段，避免广播
}
vrrp_instance VI_1 {
state MASTER #服务器角色为主
interface ens33
virtual_router_id 171 #虚拟路由id，确保主备一致
priority 100 #优先级，数值越大，优先级越高，反之优先级越小
advert_int 1 #心跳检查周期，默认为1s
authentication {
auth_type PASS
auth_pass magedu
}
virtual_ipaddress {
192.168.0.20 dev ens33 label ens33:0 }
}
###配置虚拟主机###
virtual_server 192.168.0.20 80 {
delay_loop 6 #设置健康检查时间
lb_algo wrr #设置负载均衡调度算法
lb_kind DR #设置LVS实现负载的机制，有NAT、TUN、DR三个模式
nat_mask 255.255.255.0
persistence_timeout 0
protocol TCP
sorry_server 127.0.0.1 80
real_server 192.168.0.23 80 { #设置realserver1相关配置
weight 1
HTTP_GET {
url {
path /
status_code 200
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.0.24 80 { #设置realserver2相关配置
weight 1
HTTP_GET {
url {
path /
status_code 200
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
4）配置keepalived备节点
~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from kaadmin@magedu.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node1
vrrp_mcast_group4 224.0.100.18
}
vrrp_instance VI_1 {
state BACKUP #服务器角色为备
interface ens33
virtual_router_id 171
priority 98 #从服务器优先级要小于主服务器
advert_int 1
authentication {
auth_type PASS
auth_pass magedu
}
virtual_ipaddress {
192.168.0.20 dev ens33 label ens33:0 }
}
virtual_server 192.168.0.20 80 {
delay_loop 6
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 0
protocol TCP
sorry_server 127.0.0.1 80
real_server 192.168.0.23 80 {
weight 1
HTTP_GET {
url {
path /
status_code 200
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
real_server 192.168.0.24 80 {
weight 1
HTTP_GET {
url {
path /
status_code 200
}
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
4）启动keepalived服务
~]# systemctl start keepalived.service
~]# ifconfig #查看主服务器的虚IP是否启用
...
ens33:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.0.20 netmask 255.255.255.255 broadcast 0.0.0.0
ether 00:0c:29:9e:ee:23 txqueuelen 1000 (Ethernet)
~]# ipvsadm -Ln #使用ipvsadm工具查看lvs策略
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 192.168.0.20:80 wrr
-> 192.168.0.23:80 Route 1 0 0
-> 192.168.0.24:80 Route 1 0 0
5）客户端页面访问测试
###主服务器正常提供服务时### ~]# for i in `seq 100`;do curl 192.168.0.20;sleep 1;done <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> ###主服务器宕机时，备机可正常提供服务，访问正常### ~]# for i in `seq 100`;do curl 192.168.0.20;sleep 1;done <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> ###后端节点1宕机是时，节点2仍可以正常提供服务### ~]# for i in `seq 100`;do curl 192.168.0.20;sleep 1;done <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> curl: (7) couldn't connect to host <h1>RS2</h1> curl: (7) couldn't connect to host #重试3次仍然失败后，不再负载均衡至RS1 <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1>
nginx+keepalived：
1）在proxy1和proxy2上安装并配置nginx作为代理工具
~]# yum install nginx -y
vim /etc/nginx/nginx.conf
http {
...
...
upstream webservs {
server 192.168.0.23 weight=1;
server 192.168.0.24 weight=1;
}
server {
listen 192.168.0.20; #此处监听地址为VIP
location / {
proxy_pass http://webservs;
}
...
...
~]# systemctl start nginx.service
2）配置keepalived主节点
~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from kaadmin@magedu.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node1
vrrp_mcast_group4 224.0.100.18
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 171
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass magedu
}
virtual_ipaddress {
192.168.0.20 dev ens33 label ens33:0
}
}
3）配置keepalived从节点
! Configuration File for keepalived
global_defs {
notification_email {
root@localhost
}
notification_email_from kaadmin@magedu.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id node1
vrrp_mcast_group4 224.0.100.18
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 171
priority 98
advert_int 1
authentication {
auth_type PASS
auth_pass magedu
}
virtual_ipaddress {
192.168.0.20 dev ens33 label ens33:0
}
}
4）客户端页面访问测试
###keepalived主节点正常时### ~]# for i in `seq 100`;do curl 192.168.0.20;sleep 1;done <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> ###keepalived主节点宕机后，备节点仍然能够提供服务### ~]# for i in `seq 100`;do curl 192.168.0.20;sleep 1;done <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> <h1>RS1</h1> <h1>RS2</h1> ###后端web节点1宕机后，节点1不再被调度，只使用节点2提供服务### <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1> <h1>RS2</h1>
2、授权test用户通过任意主机连接当前mysqld，但每小时最大查询次数不得超过5次；此账户的同时连接次数不得超过3次；
> GRANT USAGE ON *.* TO test@'%' WITH MAX_QUERIES_PER_HOUR 5; > GRANT USAGE ON *.* TO test@'%' WITH MAX_USER_CONNECTIONS 3;
3、导入hellodb.sql生成数据库，完成以下练习：
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；
> SELECT Name,Age from students WHERE Age>25 AND Gender='M'; +--------------+-----+ | Name | Age | +--------------+-----+ | Xie Yanke | 53 | | Ding Dian | 32 | | Yu Yutong | 26 | | Shi Qing | 46 | | Tian Boguang | 33 | | Xu Xian | 27 | | Sun Dasheng | 100 | +--------------+-----+
(2) 以ClassID为分组依据，显示每组的平均年龄；
> SELECT ClassID,avg(age) FROM students GROUP BY ClassID; +---------+----------+ | ClassID | avg(age) | +---------+----------+ | NULL | 63.5000 | | 1 | 20.5000 | | 2 | 36.0000 | | 3 | 20.2500 | | 4 | 24.7500 | | 5 | 46.0000 | | 6 | 20.7500 | | 7 | 19.6667 | +---------+----------+
(3) 显示第2题中平均年龄大于30的分组及平均年龄；
> SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(age)>30; +---------+----------+ | ClassID | avg(age) | +---------+----------+ | NULL | 63.5000 | | 2 | 36.0000 | | 5 | 46.0000 | +---------+----------+
(4) 显示以L开头的名字的同学的信息；
> SELECT * FROM students WHERE Name LIKE 'L%'; +-------+-------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | +-------+-------------+-----+--------+---------+-----------+
(5) 显示TeacherID非空的同学的相关信息；
> SELECT * FROM students WHERE TeacherID IS NOT NULL; +-------+-------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+-------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | +-------+-------------+-----+--------+---------+-----------+
(6) 以年龄排序后，显示年龄最大的前10位同学的信息；
> SELECT * FROM students ORDER BY Age DESC LIMIT 10; +-------+--------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+--------------+-----+--------+---------+-----------+ | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | | 24 | Xu Xian | 27 | M | NULL | NULL | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 17 | Lin Chong | 25 | M | 4 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | +-------+--------------+-----+--------+---------+-----------+
(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；
方法一： > SELECT * FROM students WHERE Age>=20 AND Age<=25; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+ 方法二： > SELECT * FROM students WHERE Age BETWEEN 20 AND 25; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+ 方法三： > SELECT * FROM students WHERE Age IN (20,21,22,23,24,25); +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | +-------+---------------+-----+--------+---------+-----------+
(8) 显示前5位同学的姓名、课程及成绩；
> SELECT st.Name,co.Course,sc.Score FROM students st,courses co,scores sc WHERE st.StuID=sc.StuID and sc.CourseID=co.CourseID LIMIT 5; +-------------+----------------+-------+ | Name | Course | Score | +-------------+----------------+-------+ | Shi Zhongyu | Kuihua Baodian | 77 | | Shi Zhongyu | Weituo Zhang | 93 | | Shi Potian | Kuihua Baodian | 47 | | Shi Potian | Daiyu Zanghua | 97 | | Xie Yanke | Kuihua Baodian | 88 | +-------------+----------------+-------+
(9) 显示其成绩高于80的同学的名称及课程；
> SELECT st.Name,co.Course FROM students st,courses co,scores sc WHERE st.StuID=sc.StuID and sc.CourseID=co.CourseID AND sc.Score>80; +-------------+----------------+ | Name | Course | +-------------+----------------+ | Shi Zhongyu | Weituo Zhang | | Shi Potian | Daiyu Zanghua | | Xie Yanke | Kuihua Baodian | | Ding Dian | Kuihua Baodian | | Shi Qing | Hamo Gong | | Xi Ren | Hamo Gong | | Xi Ren | Dagou Bangfa | | Lin Daiyu | Jinshe Jianfa | +-------------+----------------+
(10) 求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
> SELECT st.Name,sc2.avg_score FROM (SELECT StuID,avg(Score) AS avg_score FROM scores GROUP BY StuID) AS sc2,students st where st.StuID=sc2.StuID ORDER BY avg_score DESC LIMIT 8; +-------------+-----------+ | Name | avg_score | +-------------+-----------+ | Shi Qing | 96.0000 | | Shi Zhongyu | 85.0000 | | Xi Ren | 84.5000 | | Xie Yanke | 81.5000 | | Ding Dian | 80.0000 | | Lin Daiyu | 75.0000 | | Shi Potian | 72.0000 | | Yu Yutong | 51.0000 | +-------------+-----------+
(11) 显示每门课程课程名称及学习了这门课的同学的个数；
> SELECT co2.Course,count(co2.StuID) FROM (SELECT co.Course,sc.StuID FROM courses co,scores sc WHERE co.CourseID=sc.CourseID) AS co2 GROUP BY co2.Course; +----------------+------------------+ | Course | count(co2.StuID) | +----------------+------------------+ | Dagou Bangfa | 2 | | Daiyu Zanghua | 2 | | Hamo Gong | 3 | | Jinshe Jianfa | 1 | | Kuihua Baodian | 4 | | Taiji Quan | 1 | | Weituo Zhang | 2 | +----------------+------------------+
原创文章，作者：N26-西安-方老喵，如若转载，请注明出处：http://www.178linux.com/78433
评论列表（1条）
1题可以加上对架构图的解释，便于他人的理解，加油！！