第二十周作業

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.www58058.com/78433

贊 (0)
N26-西安-方老喵N26-西安-方老喵
上一篇 2017-07-03
下一篇 2017-07-03

相關推薦

  • 淺談用戶名和權限

        小編已經寫了兩篇博客了，卻忘了自我介紹，咳咳咳（隆zhuang重qiang介zuo紹shi）一下：翠衣薄紗如花艷，柳眉鳳眼俏佳人說的就是我！不扯了其實，小編擁有一個四口之家，那么小編今天就借家獻佛給你們講講用戶組和權限。     所屬主（me）：一般為文件的創建者，誰創建了該文件，就天然的成…

    Linux干貨 2017-07-30

  • Linux運維之路基礎學習四

    當文件的權限不能滿足某個用戶時，ACL是一個好辦法

    Linux干貨 2017-12-03

  • ?文件服務器 – vsftpd

    文件服務器 – vsftpd 概述 FTP協議 vsftpd的配置文件 vsftpd + pam + file_db + virtual_vsftp_user vsftpd + pam + mysql + virtual_vsftp_user 登錄時錯誤類型 總結 文件服務器是一種專供其他電腦訪問文件和存儲的程序。文件服務器通常比一般的個人電腦擁…

    Linux干貨 2016-10-15

  • 初學Linux文件目錄結構

    /：根目錄 /bin（binary）：放置的是二進制文件。可執行的命令；非管理類的命令，通常是系統啟動就需要的文件，供所有用戶使用的基本命令；不能關聯至獨立分區， OS啟動即會用到的程序。 /sbin：管理類的命令，通常只有管理員可以使用。 /lib：存放的是系統級別的庫文件 /lib64：專用于x86_64系統上的輔助共享庫文件存放位置  /ro…

    Linux干貨 2017-07-13

  • N25-第四周作業

    第四周作業 1、復制/etc/skel目錄為/home/tuser1，要求/home/tuser1及其內部文件的屬組和其它用戶均沒有任何訪問權限。 ~]#cp -r /etc/skel /home/tuser1 ~]#chmor 700 /home/tuser1 2、編輯/etc/group文件，添加組hadoop。 ~]#echo "h…

    Linux干貨 2017-01-03

  • Linux syslog 系統日志管理

    Linux系統上面，系統可以記錄從開機到當前系統上面何時發生了哪些事情，在centos 上表現為rsylog，由三部分組成 1）syslogd：主要記錄系統和網絡等服務的日志信息 2）klogd：主要記錄內核產生的各項信息 3）logretate：主要用來對日志文件進行切割循環記錄等 特點： 1）多線程工作 2）支持以TCP,UDP,SSL,TSL,RELP…

    Linux干貨 2016-10-24

評論列表（1條）

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

    1題可以加上對架構圖的解釋，便于他人的理解，加油?。?/p>

欧美性久久久久