LAMP快速部署及LAMP組合深入講解-2

lamp

SQL

• 數據管理模型：層次模型、網狀模型、關系模型
  數據分類：結構化數據、半結構化數據、非結構化數據

  • 關系模型

    • 數據庫：一個方案、一個項目

    • 二維關系：

      • 表：row, column
        索引：index
        視圖：view

    • SQL接口：Structured Query Language；類似于OS的shell接口；也提供編程功能；

      • ANSI： SQL標準，SQL-86, SQL-89, SQL-92, SQL-99, SQL-03, …
        PL-SQL，T-SQL，…
      • DDL：Data Defined Language
        CREATE, ALTER, DROP、SHOW
      • DML: Data Manapulating Language
        INSERT, DELETE, UPDATE, SELECT
      • 編程接口：選擇、循環；
      • SQL代碼：
        • 存儲過程：procedure
          存儲函數：function
          觸發器：trigger
          事件調度器：event scheduler
      • 用戶和權限：
        用戶：用戶名和密碼；
        權限：管理類、程序類、數據庫、表、字段
    • DBMS：DataBase Management System
      RDBMS：Relational DataBase Management System

• 事務(Transaction)：組織多個操作為一個整體，要么全部都成功執行，要么失敗回滾；
  “回滾”， rollback

  • 一個存儲系統是否支持事務，測試標準：
    ACID：
    • A：原子性；
      C：一致性；
      I：隔離性；
      D：持久性；
  • SQL接口：分析器和優化器
    存儲引擎

• 補充材料：RDMBS設計范式基礎概念

  設計關系數據庫時，遵從不同的規范要求，設計出合理的關系型數據庫，這些不同的規范要求被稱為不同的范式，各種范式呈遞次規范，越高的范式數據庫冗余越小。
  
    目前關系數據庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又稱完美范式）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎上進一步滿足更多規范要求的稱為第二范式（2NF），其余范式以次類推。一般說來，數據庫只需滿足第三范式(3NF）就行了。
  
    (1) 第一范式（1NF）
  
    所謂第一范式（1NF）是指在關系模型中，對域添加的一個規范要求，所有的域都應該是原子性的，即數據庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。即實體中的某個屬性有多個值時，必須拆分為不同的屬性。在符合第一范式（1NF）表中的每個域值只能是實體的一個屬性或一個屬性的一部分。簡而言之，第一范式就是無重復的域。
  
    說明：在任何一個關系數據庫中，第一范式（1NF）是對關系模式的設計基本要求，一般設計中都必須滿足第一范式（1NF）。不過有些關系模型中突破了1NF的限制，這種稱為非1NF的關系模型。換句話說，是否必須滿足1NF的最低要求，主要依賴于所使用的關系模型。
  
    (2) 第二范式(2NF)
  
    第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數據庫表中的每個實例或記錄必須可以被唯一地區分。選取一個能區分每個實體的屬性或屬性組，作為實體的唯一標識。
  
    第二范式（2NF）要求實體的屬性完全依賴于主關鍵字。所謂完全依賴是指不能存在僅依賴主關鍵字一部分的屬性，如果存在，那么這個屬性和主關鍵字的這一部分應該分離出來形成一個新的實體，新實體與原實體之間是一對多的關系。為實現區分通常需要為表加上一個列，以存儲各個實例的唯一標識。簡而言之，第二范式就是在第一范式的基礎上屬性完全依賴于主鍵。
  
    (3) 第三范式（3NF）
  
    第三范式（3NF）是第二范式（2NF）的一個子集，即滿足第三范式（3NF）必須滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個關系中不能包含已在其它關系已包含的非主關鍵字信息。簡而言之，第三范式就是屬性不依賴于其它非主屬性，也就是在滿足2NF的基礎上，任何非主屬性不得傳遞依賴于主屬性。

• 數據庫：數據集合

  • 表：為了滿足范式設計要求，將一個數據集分拆為多個；

  • 約束：constraint，向數據表插入的數據要遵守的限制規則；

    • 主鍵：一個或多個字段的組合，填入主鍵中的數據，必須不同于已存在的數據；而且不能為空；一個表只能有一個Primary KEY；
    • 外鍵：一個表中某字段中能插入的數據，取決于另外一張表的主鍵中的數據；
    • 惟一鍵：一個或多個字段的組合，填入惟一鍵中的數據，必須不同于已存在的數據；可以為空；一個表可存在多個Unique Key；
    • 檢查性約束：取決于表達式的要求；

  • 索引：將表中的某一個或某些字段抽取出來，單獨將其組織一個獨特的數據結構中；

    • 常用的索引類型：
      • B+ Tree：Balance Tree
        hash

    注意：有助于讀請求，但不利于寫請求；

  • 關系運算：

    • 選擇：挑選出符合條件的行；
      投影：挑選出符合需要的列；
      連接：將多張表關聯起來；
  • 數據抽象：
    • 物理層：決定數據的存儲格式，即如何將數據組織成為物理文件；
      邏輯層：描述DB存儲什么數據，以及數據間存在什么樣的關系；
      視圖層：描述DB中的部分數據；
  • 關系模型的分類：
    • 關系模型
      實體-關系模型
      基于對象的關系模型
      半結構化關系模型

MariaDB

• 可用版本：
  • MySQL：
    • MariaDB
      Percona-Server
      AliSQL
      TiDB

• MariaDB的特性：

  • 插件式存儲引擎：存儲管理器有多種實現版本，彼此間的功能和特性可能略有區別；用戶可根據需要靈活選擇；

  • 存儲引擎也稱為“表類型”；

    1. 更多的存儲引擎；
       MyISAM：不支持事務，表級鎖，崩潰后不保證安全恢復；
       MyISAM –> Aria
       InnoDB –> XtraDB ：支持事務，行級鎖，外鍵，熱備；
    2. 諸多擴展和新特性；
    3. 提供了較多的測試組件；
    4. truly open source；

• 安裝和使用MariaDB：

  1. rpm包；
     (a) 由OS的發行商提供；
     (b) 程序官方提供；
  2. 源碼包；
  3. 通用二進制格式的程序包；

• MariaDB程序的組成：C/S

  • MariaDB：主要有兩類程序文件

    • Client: mysql, mysqldump, mysqladmin
      Server：mysqld, mysqld_safe, mysqld_multi

  • C：Client –> mysql protocol –> Server
    mysql：CLI交互式客戶端程序；
    mysqldump：備份工具；
    mysqladmin：管理工具；
    mysqlbinlog：
    …

  • S：Server
    mysqld
    mysqld_safe：建議運行服務端程序；
    mysqld_multi：多實例；

    • 三類套接字地址：
      • IPv4|IPv6, 3306/tcp
      • Unix Sock：/var/lib/mysql/mysql.sock, /tmp/mysql.sock

  • 配置文件：ini風格，用一個文件為多個程序提供配置；

    • [mysql]：配置客戶端
      [mysqld]：配置服務端
      [mysqld_safe]：
      [server]
      [client]
      [mysqldump]
      …

    • mysql的各類程序啟動都讀取不止一個配置文件，按順序讀取，且最后讀取的為最終生效；

      # my_print_defaults
      Default options are read from the following files in the given order:
      /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
      
      /etc/my.cnf + /etc/my.cnf.d/*.cnf

命令行交互式客戶端程序：mysql

• mysql [OPTIONS] [database]

  • 常用選項：

    • -u, –user=username：用戶名，默認為root；
      -h, –host=hostname：遠程主機（即mysql服務器）地址，默認為localhost; 客戶端連接服務端，服務器會反解客戶的IP為主機名，關閉此功能(skip_name_resolve=ON)；
      -p, –password[=PASSWORD]：USERNAME所表示的用戶的密碼； 默認為空；

      注意：mysql的用戶賬號由兩部分組成：'USERNAME'@'HOST'; 其中HOST用于限制此用戶可通過哪些遠程主機連接當前的mysql服務；
      
      HOST的表示方式，支持使用通配符：
        %：匹配任意長度的任意字符；
            172.16.%.%,  172.16.0.0/16
        _：匹配任意單個字符；

      -P, –port=#：mysql服務器監聽的端口；默認為3306/tcp；
      -S, –socket=/PATH/TO/mysql.sock：套按字文件路徑；
      -D, –database=DB_name：指定連接到服務器端之后，其指明的數據庫為默認數據庫；
      -e, –execute=’SQL STATEMENT’：連接至服務器并讓其執行此命令后直接返回；

  • 命令：

    • 客戶端命令：本地執行

      mysql> help   獲取幫助；
        \u db_name：設定哪個庫為默認數據庫
        \q：退出；
        \d CHAR：修改新的語句結束符；
        \g：語句結束標記；
        \G：語句結束標記，結果豎排方式顯式；
        \s：查看狀態
        \!: 執行shell命令；
        \.: 裝載并運行sql腳本；
        \c: 終止命令，不寫分號

    • 服務端命令：通過mysql連接發往服務器執行并取回結果（SQL語句）；
      DDL， DML， DCL
      注意：每個語句必須有語句結束符，默認為分號(;)

      獲取幫助：
        mysql> help contents 
            Administration     
            Account Management
            Data Definition
            Data Manipulation
            Data Types
        myslq> help '命令類別'

  • 數據類型：

    • 表：行和列
      創建表：定義表中的字段；

    • 定義字段時，關鍵的一步即為確定其數據類型；
      用于確定：數據存儲格式、能參與運算種類、可表示的有效的數據范圍；

    • 字符型：字符集
      碼表：在字符和二進制數字之間建立映射關系；

      mysql> SHOW CHARACTER SET;  查看支持的字符集
        mysql> SHOW COLLATION;   查看排序規則

    • 種類：
      • 字符型：
        定長字符型：

        CHAR(#)：不區分字符大小寫
        BINARY(#)：區分字符大小寫

        變長字符型：

        VARCHAR(#)：多占一個或兩個字符空間；
        VARBINARY(#)

        對象存儲：

        TEXT：不區分字符大小寫；TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT
        BLOB：Binary Large OBject（二進制大對象），區分字符大小寫；TINYBLOB，SMALLBLOB，MEDIUMBLOB，BLOB，LONGBLOB；

        內置類型：

        SET 
        ENUM

      • 數值型：
        精確數值型：

        INT（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT） 
          UNSIGNED
        DECIMAL

        近似數值型：

        FLOAT
        DOBULE

      • 日期時間型：

        日期型：DATE
        時間型：TIME
        日期時間型：DATETIME
        時間戳：TIMESTAMP
        年份：YEAR(2), YEAR(4)

    • 字段數據修飾符：

      NOT NULL：非空；
      AUTO_INCREMENT：自動增長；
      NULL：
      DEFAULT  value：默認值；
      PRIMARY KEY：主鍵；
      UNIQUE KEY：惟一鍵；

服務器端命令

• DDL：數據定義語言，主要用于管理數據庫組件，例如數據庫、表、索引、視圖、用戶、存儲過程
  CREATE、ALTER、DROP

• DML：數據操縱語言，主要用管理表中的數據，實現數據的增、刪、改、查；
  INSERT， DELETE， UPDATE， SELECT

• 獲取命令幫助：

  mysql> help  KEYWORD

• 數據庫管理（DDL）：

  • 創建：

    CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;
          [DEFAULT]  CHARACTER SET [=] charset_name
          [DEFAULT]  COLLATE [=] collation_name
    
      查看支持的所有字符集：SHOW CHARACTER SET 
      查看支持的所有排序規則：SHOW  COLLATION

  • 修改：

    ALTER {DATABASE | SCHEMA}  [db_name]
          [DEFAULT]  CHARACTER SET [=] charset_name
          [DEFAULT]  COLLATE [=] collation_name

  • 刪除：

    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

  • 查看：

    SHOW DATABASES LIKE  ’‘;

• 表管理：

  • 創建：

    CREATE TABLE  [IF NOT EXISTS]  [db_name.]tbl_name  (create_defination)  [table_options]
    
    create_defination:
      字段：col_name  data_type
      鍵：
          PRIMARY KEY (col1, col2, ...)
          UNIQUE KEY  (col1, col2,...)
          FOREIGN KEY (column)
      索引：
          KEY|INDEX  [index_name]  (col1, col2,...)
    
    table_options：
      ENGINE [=] engine_name
      CHARACTER SET [=] charset_name
      COLLATE [=] collation_name

    • 查看數據庫支持的所有存儲引擎類型：

      mysql> SHOW  ENGINES;

    • 查看某表的狀態信息：

      mysql> SHOW  TABLES  STATUS  [LIKE  'tbl_name'][WHERE clause]

  • 修改：

    ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name  [alter_specification [, alter_specification] ...]
    
    alter_specification:
      字段：
          添加：ADD  [COLUMN]  col_name  data_type  [FIRST | AFTER col_name ]
          刪除：DROP  [COLUMN] col_name 
          修改：
              CHANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name]    
              MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]
      鍵：
          添加：ADD  {PRIMARY|UNIQUE|FOREIGN}  KEY (col1, col2,...)
          刪除：
              主鍵：DROP PRIMARY KEY
              外鍵：DROP FOREIGN KEY fk_symbol
      索引：
          添加：ADD {INDEX|KEY} [index_name]  (col1, col2,...)
          刪除：DROP {INDEX|KEY}  index_name
      表選項：
          ENGINE [=] engine_name

    • 查看表上的索引的信息：

      mysql> SHOW INDEXES FROM tbl_name;

  • 刪除：

    DROP  TABLE  [IF EXISTS]   tbl_name [, tbl_name] ...

  • 表的引用方式：

    tbl_name
      db_name.tbl_name

  • 第二種創建方式：

    復制表結構；
    
      CREATE TABLE tbl_name LIKE other_table_name

  • 第三種創建方式：

    復制表數據；
    
      CREATE TABLE tbl_name () SELECT clause

• 索引管理：
  索引是特殊的數據結構；

  • 索引：要有索引名稱；

  • 創建：

    CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX  index_name  [BTREE|HASH]  ON tbl_name (col1, col2,,...)

  • 刪除：

    DROP  INDEX index_name ON tbl_name

• DML：INSERT， DELETE， UPDATE， SELECT

  • INSERT INTO：

    INSERT  [INTO]  tbl_name  [(col1,...)]  {VALUES|VALUE}  (val1, ...),(...),...

    注意：字符型：引號；數值型：不能用引號；

  • SELECT：

    1. SELECT * FROM tbl_name[, tbl_name_2];
       返回指定表的所有數據；慎用；
    2. SELECT col1, col2, … FROM tbl_name;
       顯示時，字段可以顯示為別名；
       col_name AS col_alias

    3. SELECT col1, … FROM tbl_name WHERE clause;

       WHERE clause：用于指明挑選條件；
        WHERE col_name 操作符 value; 
       
            操作符(1) ：
                >, <, >=, <=, ==, !=
       
            組合條件：
                and 
                or
                not
       
            操作符(2) ：
                BETWEEN ...  AND ...
                LIKE 'PATTERN'
                    通配符：
                        %：任意長度的任意字符；
                        _：任意單個字符；
       
                RLIKE  'PATTERN'
                    正則表達式對字符串做模式匹配；
       
                IS NULL     顯示為空的
                IS NOT NULL    顯示不為空的

    4. SELECT col1, … FROM tbl_name [WHERE clause] ORDER BY col_name, col_name2, … [ASC|DESC];
       ASC: 升序；
       DESC： 降序；

    5. 分組：
       GROUP BY，為了聚合；

       count(), sum(), avg(), max(), min()

       HAVING：對聚合的結果做條件過濾；

  • DELETE：刪除行；

    DELETE   FROM  tbl_name  [WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]

    (1) DELETE  FROM  tbl_name  WHERE where_condition 
      (2) DELETE  FROM  tbl_name  [ORDER BY ...]  [LIMIT row_count]

  • UPDATE：

    UPDATE [LOW_PRIORITY] [IGNORE] table_reference  SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition]  [ORDER BY ...] [LIMIT row_count]

• 用戶賬號及權限管理：

  • 用戶賬號：’username’@’host’

    • host：此用戶訪問當前mysql服務器時，允許其通過哪些主機遠程創建連接；
      表示方式：IP，網絡地址、主機名、通配符(%和_)；

    • 禁止檢查主機名：my.cnf

      [mysqld]
        skip_name_resolve = ON

  • 創建用戶賬號：

    CREATE  USER   'username'@'host'  [IDENTIFIED BY  'password'];

  • 刪除用戶賬號：

    DROP USER  ’user‘@’host' [, user@host] ...

  • 授權：
    權限級別：管理權限、數據庫、表、字段、存儲例程；

    GRANT  priv_type,...  ON  [object_type]  db_name.tbl_name  TO  'user'@'host'  [IDENTIFIED BY  'password'];
    
          priv_type： ALL  [PRIVILEGES]
          db_name.tbl_name：
              *.*：所有庫的所有表；
              db_name.*：指定庫的所有表；
              db_name.tbl_name：指定庫的特定表；
              db_name.routine_name：指定庫上的存儲過程或存儲函數；
    
          [object_type]
              TABLE
              FUNCTION
              PROCEDURE

    • 查看指定用戶所獲得的授權：

      SHOW GRANTS FOR  'user'@'host'
      
        SHOW GRANTS FOR CURRENT_USER;

    • 回收權限：

      REVOKE  priv_type, ...  ON  db_name.tbl_name  FROM  'user'@'host';

    • 注意：MariaDB服務進程啟動時，會讀取mysql庫的所有授權表至內存中；
      1. GRANT或REVOKE命令等執行的權限操作會保存于表中，MariaDB此時一般會自動重讀授權表，權限修改會立即生效；
      2. 其它方式實現的權限修改，要想生效，必須手動運行FLUSH PRIVILEGES命令方可；

• 加固mysql服務器，在安裝完成后，運行mysql_secure_installation命令；

• 圖形管理組件：
  phpMyAdmin；運行于lamp；
  Navicat
  Mysql-Front
  ToadForMySQL
  SQLyog

httpd+php結合的方式：php-fpm

• module: php
  fastcgi : php-fpm

• php-fpm：

  • CentOS 6：
    PHP-5.3.2-：默認不支持fpm機制；需要自行打補丁并編譯安裝；
    httpd-2.2：默認不支持fcgi協議，需要自行編譯此模塊；

    • 解決方案：編譯安裝httpd-2.4, php-5.3.3+；

  • CentOS 7：
    httpd-2.4：rpm包默認編譯支持了fcgi模塊；
    php-fpm包：專用于將php運行于fpm模式；

    配置文件：

    • 服務配置文件：/etc/php-fpm.conf, /etc/php-fpm.d/*.conf

    • php環境配置文件：/etc/php.ini, /etc/php.d/*.ini

    • 連接池：
      pm = static|dynamic

      • static：固定數量的子進程；pm.max_children；
      • dynamic：子進程數據以動態模式管理；

        pm.start_servers
          pm.min_spare_servers
          pm.max_spare_servers
          ;pm.max_requests = 500

    • 創建session目錄，并確保運行php-fpm進程的用戶對此目錄有讀寫權限；

      # mkdir  /var/lib/php/session
        # chown apache.apache /var/lib/php/session

    • 配置httpd，添加/etc/httpd/conf.d/fcgi.conf配置文件，內容類似：

      DirectoryIndex index.php
            ProxyRequests Off
            ProxyPassMatch ^/(.*\.php)$  fcgi://127.0.0.1:9000/var/www/html/$1

    • 虛擬主機配置

      DirectoryIndex index.php
      
            <VirtualHost *:80>
                ServerName www.b.net
                DocumentRoot /apps/vhosts/b.net
                ProxyRequests Off
                ProxyPassMatch ^/(.*\.php)$  fcgi://127.0.0.1:9000/apps/vhosts/b.net/$1
      
                <Directory "/apps/vhosts/b.net">
                    Options None
                    AllowOverride None
                    Require all granted
                </Directory>
            </VirtualHost>

      • 假設fpm的status頁面輸出URL為/pmstatus，測試接口的輸出位置為/ping
        ProxyPassMatch ^/(ping|pmstatus)$ fcgi://127.0.0.1:9000/$1