第二十周作业

N26-西安-方老喵 • • Linux干货

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

赞 (0)
N26-西安-方老喵N26-西安-方老喵
1
上一篇 2017-07-03 19:32
下一篇 2017-07-03 21:09

相关推荐

  • 内部命令与外部命令 Linux干货

    内部命令与外部命令

    内部命令与外部命令 内部命令在系统启动时就调入内存，是常驻内存的，所以执行效率高。 外部命令是系统的软件功能，用户需要时才从硬盘中读入内存。 1.type type COMMAND 判断一个命令的类型即判断一个命令是否为内置命令。   2.enable（内部命令） enable既可以查看内部命令，同时也可以判断是否为内部命令 enable 显示所有…

    2017-07-15

  • 请使用命令行展开功能来完成以下练习

    (1)、创建/tmp目录下的：ac, ad, bc, bd ]# mkdir -p /tmp/{a,b}_{c,d} 或者： ]# touch /tmp/{a,b}_{c,d} (2)、创建/tmp/mylinux目录下的： ]# mkdir -pv /tmp/mylinux/{bin,boot/gtub,dev,etc/{rc.d/init.d,sysco…

    Linux干货 2016-11-06

  • Linux文件系统的最基本数据结构：inode和block

    Linux文件系统的最基本数据结构：inode和block 为什么有inode和block inode block 创建目录或文件 使用场景 查看本机的文件系统信息 Linux文件系统的最基本数据结构：inode和block 为什么有inode和block 由于Linux系统是多用户多的，所以文件系统类型多样化是在所难免的。从ext2开始，是将文件属性和文件…

    Linux干货 2016-09-06

  • N23_第1周博客作业

    1、描述计算机的组成及其功能。     根据冯·诺依曼体系计算机有中央处理器、主存储器、输入/输出三个子系统组成。 1.中央处理器 CPU（Central Processing Unit）用于数据运算。CPU有控制单元、算数逻辑单元、寄存器组组成。 （1）控制单元：控制各个子系统操作 （2）算数逻辑单元：对数据进行逻辑、移位和算数运算 逻…

    Linux干货 2016-09-19
  • shell脚本基础练习2 Linux干货

    shell脚本基础练习2

    1.编写一个脚本/root/bin/createuser.sh，脚本的执行语法必须是:createuser.sh -username -m password，选项与参数间可支持多空格，但不能顺序颠倒。当未指定正确的选项或参数时，以错误输出方式提示“createuser.sh -u username -m password ”后退出脚本。用…

    2017-08-12

  • CentOS系统启动流程

    概述：系统启动流程是Linux一个重要的内容，深入了解启动流程会对我们学习Linux起到一个顺水推舟的作用。因为CentOS 7改动较大，所以下面的内容只是针对CentOS 5和6来说的。下面进入正题。 启动流程：  第一步：POST加电自检    此过程的就是为了检测一下外界的硬件设备是否能够正常运行，如CPU，内存设备，硬盘…

    Linux干货 2016-09-13

评论列表（1条）

  • 马哥教育
    马哥教育 2017-07-13 17:20

    1题可以加上对架构图的解释，便于他人的理解，加油！！

电话咨询
在线咨询