Linux基础教程之LAMP组合深入讲解（二）

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