第二十周作業

N26-西安-方老喵 ? 2017-07-03 21:07 ? 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-西安-方老喵

1

tomcat-session server實現

上一篇 2017-07-03 19:32

下一篇 2017-07-03 21:09

###N25-Bazinga-第一周作業

###N25-Bazinga-第一周作業— 1，描述計算機的組成及其功能計算機基本組成有五部分：計算機的主要組成部分可以歸納為以下五個部分：控制器、運算器、存儲器、輸入設備、和輸出…

Linux干貨 2016-12-06
LAMP 基于httpd 2.4

一安裝前準備說明操作系統：CentOS 7 64位 Apache 版本：httpd-2.4.6-40.el7.centos.x86_64 httpd服務器ip:192.168.1.4 1. 配置好IP、DNS 、網關，確保使用遠程連接工具能夠連接服務器 2. &nbsp…

Linux干貨 2016-04-05
任務計劃和本地系統的默認郵件

一、前言二、本地mail的使用方法三、at /batch 四、cron 五、小結一、前言 linux 中執行任務計劃有三種命令：at ，batch ，cron.其中，at，batch 是一次性執行，而crontab，則是周期性執行；作業執行完成后，會通過郵件通知給作業提交者；因此，以下就從郵件說起。二、郵件服務 Centos ,默認會…

Linux干貨 2015-05-13
MySQL or MariaDB 簡介

DBMS：數據庫管理系統 RDBMS：關系型數據庫管理系統總之：他們都是一個數據管理程序；大多都是CS架構，都有專門的通信協議進行數據交換關系模型：表（行或者列）：二維關系設計范式： &…

Linux干貨 2017-01-12
dd命令詳解及實戰應用

dd簡述 dd在linux中是一個強大的命令，常用于拷貝大量數據，測試讀寫效能，清空硬盤數據（慎用），不僅如此，由于dd 允許以二進制方式讀寫，所以特別適合在原始設備上輸入、輸出。 dd與cp的區別這里有必要說明一下，兩個命令都可用于拷貝，但是級別卻完全不一樣：cp是文件級別的讀取方式，而dd更底層，可以數據塊級別去讀取硬盤，由此可看出在效率…

Linux干貨 2017-04-04
基于Redis的開源分布式服務Codis

Redis在豌豆莢的使用歷程——單實例==》多實例，業務代碼中做sharding==》單個Twemproxy==》多個Twemproxy==》Codis，豌豆莢自己開發的分布式Redis服務。在大規模的Redis使用過程中，他們發現Redis受限于多個方面：單機內存有限、帶寬壓力、單點問題、不能動態擴容以及磁盤損壞時的數據搶救。 Redis通常有3個使用途徑…

Linux干貨 2015-02-25

評論列表（1條）

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

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

欧美性久久久久