数据库术语

数据库结构

• 数据源：多源数据继承
• 数据仓库继承工具：FTL工具、MapReduce
• 数据仓库服务器：列存储数据库引擎
• 数据即使：数据仓库的数据子集、聚集数据继
• OLAP服务器：提供多维数据视图
• 前台数据分析工具
  • 报表工具
  • 多维分析工具
  • 数据挖掘工具

数据库类型

• 传统关系型数据库
• MPP大规模并行处理数据库
• NoSQL数据库
• 图数据库
• NewSQL数据库
• GPU数据库

数据查询

• Project Pushdown：投影下推

  • 只读取、查询需要的列
  • 减少每次查询的IO数据量

• Predicate Pushdown：谓词下推

  • 将过滤条件尽快执行，跳过不满足条件行

数据压缩算法

• Run Length Encoding：重复数据
• Delta Encoding：有序数据集
• Dictionary Encoding：小规模数据集合
• Prefix Encoding：字符串版Delta Encoding

数据库调优

数据库调优：是数据库具有更高的吞吐量、更快响应

• 被调优对象是数据库整体
• 需要考虑很多资源、数据库配置

人工调优

• 依赖人工，效率低下
• 要求操作者理解查询原理，对应用、DBMS、操作系统、硬件有 一定理解

基于案例调优

• 总结典型应用案例情况中数据库参数推荐配置值、逻辑层设计等 情况，为用户调优工作提供参考、借鉴
• 忽略系统动态性、不同系统之间差异

自调优

• 为DBMS建立模型，根据“影响数据库性能效率的因素”，自动进行 参数配置
• 部分商业数据库实现了自调优技术

需求分析期

应用情况估算

• 应用使用方式
  • 将业务逻辑转换为读写分布逻辑，以读多写少、读写均衡 区分OLAP、OLTP
  • 应用对数据库的并发情况、并发是否可池化
• 数据量
• 对数据库压力、峰值压力

系统选型策略

• 确定适合的数据库：开源、商业；集群、单机
• 操作系统、中间件、硬件、网络选型

项目设计期

数据模型设计

根据业务逻辑，从以下角度考虑表结构

• E-R模型设计：遵循E-R模型设计原理，适当非规范化可以改善 系统查询性能
• 数据逻辑分布策略：减少数据请求中不必要的数据量
  • 分区
  • 利用E-R模型分表
• 数据物理存储策略：减少IO操作
  • 启用压缩
  • 分开存储索引、表数据
  • 不同表数据分布在不同表空间
  • 不同表空间分布在不同物理存储，尤其是读写量大的表空间 分布在不同物理存储上
  • 日志、索引、数据分布在不同物理存储上
• 索引：在查询频繁的对象上建立恰当索引

开发期

SQL设计

• 编写正确、查询效率高的SQL语句，依据“查询重写规则”
  • 有意识地保障SQL能利用到索引

数据库功能启用

• 查询重用
• 数据库参数设计

测试、试运行、上线、维护

模型系统预运行

• 在备用系统上模型实际运行环境，加大压力进行相似测试

系统监控分析

• 应用系统表示：收集用户使用意见、系统存在问题
• OS环境监控：实时监控CPU、内存、IO等，对比历史正常情况
• 数据库内部状态监控：系统表、视图、工具、锁的情况
• 日志分析：在数据库的日志、操作系统日志中找出异常

命令行参数

• -d/--define <key=value>：替换脚本中shell形式变量
• --hivevar <key=value>：替换脚本中shell形式变量
  • 结合hive脚本中设置shell变量使用
• -h <hostname>：hive服务器
• -p <port>：hive服务器端口
• -database <database>：连接数据库
• -e <quoted-query-string>：从命令行获取、执行hive脚本
• -f <filename>：从文件获取、执行hive脚本
• -i <filename>：初始化hive脚本
• --hiveconf <property=value>：设置hive参数
• -S/--slient：安静模式启动交互hive shell
• -v/--verbose：详细模式
• -H/--help：帮助

辅助语句

结果输出

• INSERT INTO/OVERWRITE：查询结果追加/覆盖在hive表中
• INSERT INTO/OVERWRITE [LOCAL] DIRECTORY：查询结果追加/ 覆盖本地/HDFS目录

• 有分区情况下，仅覆盖当前分区

内置函数

聚合函数

• collect_set()：配合group by合并、消除重复字段，返回 array
• concat_ws()：连接字符串
• if(<condition>, <true_value>, <false_value>)：判断条件
• size()：返回array长度
• length()：返回字符串大小

配置相关语句

文本分隔符

• 记录分隔：\n
• 字段分隔：\001（八进制）ASCII码1字符
• Array、Struct、Map等集合中元素分隔：\002ASCII码1字符
• Map中键值对分隔：\003ASCII码1字符

1
2
3
4

line terminated by `\n`
row format delimited fields terminated by `\001`
collection items terminated by `\002`
map keys terminated by `\003`

空值

• hive中空值一般有两种存储方式

  • NULL：底层存储NULL，查询显示为NULL
  • \N：底层存储\N，查询显示为NULL，查询输出为\N

• 空值查询：<field> is NULL

  • NULL：也可<field> = 'NULL'
  • \N：也可<field> = '\\N'（转义）

• 底层存储设置参见表存储
• 空字符串不是空值，需要用<field> = ''查询

表存储配置

分区

属性

serdeproperties

• 设置空值存储方式

  1	alter <table> SET serdeproperites('serialization.null.format' = '\N')

安装

Mysql

大部分系统常用源中包含mysql，直接使用自带的包管理工具安装 即可，对于源中无法找到mysql的系统可以访问官网获取 安装方法

1

$ sudo zypper install mysql-server mysql-client

CentOS7

CentOS7的常用源中即不含mysql，安装mysql则需要添加mysql源， 同样在官网中找到添加方式：

1. 下载的是RPM源rpm包
2. $ sudo yum localintall安装后即添加源
3. 使用yum直接安装mysql，需要注意的是默认情况下只有最新版本 mysql源是enabled，其他版本的需要--enablerepo指定或者 /etc/yum.repo.d下修改文件

Mariadb

mariadb和mysql大部分兼容，添加了一些新的特性，是mysql的一个 开源分支，由mysql的作者维护，避免mysql在被Oracle收购后闭源。

• 大部分情况下，mariadb可以完全看作是mysql
• 甚至在某些系统中，mariadb服务有别名mysql
• mariadb控制台命令也是mysql

配置

配置文件

• /etc/mysql/my.cnf:mysql主配置文件

  • mysql的此配置文件内包含有具体的配置
  • mariadb此文件中则不包含具体配置，而是导入配置文件

    1 2	!includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mariadb.cond.d/

• ~/.my.cnf

  • 一般不存在，需要自行建文件，注意需要设置文件权限， 只能root用户可写，否则mysql会忽略此配置文件
  • mysqld服务启动需要root权限，因此~目录下的配置文件 基本不可能影响mysql-server状态，即[server]下的配置 基本是无效的

数据位置

• 数据库存放数据位置：/var/lib/mysql/db_name/

Mysql-Client

登陆

@todo 一个问题，我安装的mariadb，默认的root用户无法在一般用户账户 登陆，必须sudo才能正常登陆

参数登陆

1

$ mysql -h host -P port -u user -p

mysql不带参数启动则是默认cur_user_name@localhost:3306， 表示使用当前用户名作为用户名登陆，如果该用户设置密码，-p 参数不能省略

文件

1

$ mysql --defaults-file=file_name

文件内容格式类似于配置文件

1
2
3
4
5

[client]
host=
user=
password=
database=（可选）

注意

• mysql中默认存在一个用户名为空的账户，只要在本地，可以 不用账户、密码登陆mysql，因为这个账号存在，使用新建用户 无法通过密码登陆，需要在

  1 2 3

  $ use mysql; $ delete from user where User=""; $ flush privileges;

Mysql交互命令

Show信息类

• SHOW DATABASES：列出MySQLServer数据库。
• SHOW TABLES [FROM db_name]：列出数据库数据表。
• SHOW TABLE STATUS [FROM db_name]：列出数据表及表状态信息。
• SHOW COLUMNS FROM tbl_name [FROM db_name]：列出资料表字段
  • DESC tbl_name：同
• SHOW FIELDS FROM tbl_name [FROM db_name]
• DESCRIBE tbl_name [col_name]
• SHOW FULL COLUMNS FROM tbl_name [FROM db_name]：列出字段及详情
• SHOW FULL FIELDS FROM tbl_name [FROM db_name]：列出字段完整属性
• SHOW INDEX FROM tbl_name [FROM db_name]：列出表索引
• SHOW STATUS：列出 DB Server 状态
• SHOW VARIABLES [like pattern]：列出 MySQL 系统环境变量
• SHOW PROCESSLIST：列出执行命令。
• SHOW GRANTS FOR user：列出某用户权限

User

• CREATE USER 'USER_NAME'@'ADDRESS' IDENTIFIED BY 'PASSWORD'

  • IDENTIFIED BY PASSWORD这个语法好像已经被丢弃了

• SET PASSWORD FOR 'USER_NAME'@'ADDRESS' = PASSWORD('NEW_PWD')

• SET PASSWORD = PASSWORD('NEW_PWD')：修改当前用户密码

• GRANT PRIVILEGES ON DB_NAME.TBL_NAME TO 'USER_NAME'@'ADDRESS' [WITH GRANT OPTION]

  • WITH GRANT OPTION：允许用户授权

• REVOKE PRIVILIEGES ON DB_NAME.TBL_NAME TO 'USER_NAME'@'ADDRES%'

• DROP 'USER_NAME'@'ADDRESS'

说明

• revoke和grant中的权限需要一致才能取消相应授权

  • grant select不能通过revoke all取消select
  • grant all也不能通过revoke select取消select

• 特殊符号

  • %：所有address，也可以是ip部分，如：111.111.111.%
    • 这个其实在sql语句中可以表示任意长度字符串
  • *：所有数据库、表

Priviledges

set global sql statements,    use mysqladmin
debug command, create an extra connection
even reach the maximum amount|

|update|update| |usage|connect without any specific priviliege|

执行Sql脚本

• shell内执行

  1	mysql -h host -D db_name -u user -p < file_name.sql;

• mysql命令行执行

  1	source file_name.sql;

导入、导出数据

导入数据

• shell内

• mysql命令行内

  1 2 3 4 5

  load data [local] infile '/path/to/file' into table tbl_name fields terminated by 'sep_char' optionally enclosed by 'closure_char' escaped by 'esc_char' lines terminated by `\r\n`;

  • 若/path/to/file不是绝对路径，则被认为是相对于当前 数据库存储数据目录的相对路径，而不是当前目录
  • 关键字local表示从客户端主机导入数据，否则从服务器 导入数据

导出数据

• shell内

• mysql命令行内

注意：远程登陆mysql时，两种方式导出数据不同，shell导出 数据可以导出至client，而mysql命令行内导出至server

Mysql-Server

数据库字符编码方式

查看

• 只查看数据库编码方式

  1	show variables like "character_set_database;

• 查看数据库相关的编码方式

  1	show variables like "character%";

  |variable_name|value| |——-|——-| |character_set_client|latin1| |character_set_connection|latin1| |character_set_database|latin1| |character_set_filesystem|binary| |character_set_results|latin1| |character_set_server|latin1| |character_set_system|utf8| |character_sets_dir|/usr/share/mysql/charsets/|

• 另一种查询数据库编码方式

  1	show variables like "collation%";

  |Variable_name|Value| |——-|——-| |collation_connection|utf8mb4_general_ci| |collation_database|utf8mb4_general_ci| |collation_server|utf8mb4_general_ci|

相关变量说明

• character_set_client：客户端编码方式
• character_set_connection：建立连接是所用编码
• character_set_database：数据库编码
• character_set_results：结果集编码
• character_set_server：服务器编码

保证以上编码方式相同则不会出现乱码问题，还需要注意其他连接 数据库的方式不一定同此编码方式，可能需要额外指定

修改编码方式

修改数据库默认编码方式

修改mysql配置文件（/etc/mysql/my.cnf）

#todo
mysql配置文件的优先级
utf8mb4意义

1
2
3
4
5

[client]
default-character-set=utf8mb4
[mysqld]
default-character-set=utf8mb4
init_connect="SET NAMES utf8mb4"

重启mysql即可

修改单个数据库

• 创建时

  1	create database db_name character set utf8 collate utf8_general_ci;

  1	create database if not exists db_name defualt charater set utf8;

脚本、窗口

1

set names gbk;

只修改character_set_client、character_set_connection、 character_set_results编码方式，且只对当前窗口、脚本有效， 不影响数据库底层编码方式

基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT			<field>, DISTINCT <field>
INTO			<new_tbl> [IN <other_db>]
FROM			<tbl>
WHERE			<field> <OP> <value>/<field>
ORDER BY		<field> [ASC/DESC];

INSERT INTO		<tbl>[<field>]
VALUES			(<value>);

UPDATE			<tbl>
SET				<field> = <value>
WHERE			<field> <OP> <value>/<field>;

DELETE
FROM			<tbl>
WHERE			<field> <OP> <value>/<field>;

数据库、表、索引、视图

创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

!-- 创建数据库
CREATE DATABASE <db_name>;

!-- 创建表
CREATE TABLE <tbl>(
	<field>		<dtype>,
	<field>		<dtype>		<CSTRT>,					!-- MSSQL、ORACLE
	<CSTRT>		(<field>,),								!-- MySQL
	CONSTRAINT [<cstrt_name>] <cstrt> (<field>,)		!-- common
)

!-- 创建索引
CREATE INDEX <index_name>
ON <tbl> (<field>);

!-- 创建视图
CREATE VIEW <view_name> AS
<select_expr>;

自增字段

1
2
3
4
5
6
7
8
9
10

!-- MSSQL
<field> <dtype> IDENTITY
!-- MySQL
<field> <dtype> AUTO_INCREMENT
!-- ORACLE：创建自增序列，调用`.nextval`方法获取下个自增值
CREATE SEQUENCE <seq>
MINVALUE <min>
START WITH <value>
INCREMENT BY <step>
CACHE <cache>				!-- 提高性能

丢弃

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

!-- 丢弃索引
!-- MSSQL
DROP INDEX <tbl>.<index_name>;
!-- ORACLE
DROP INDEX <index_name>;
!-- MySQL
ALTER TABLE <tbl>
DROP INDEX <index_name>;

!-- 丢弃表/数据
DROP TABLE <tbl>;
TRUNCATE TABLE <tbl>;

!-- 丢弃数据库
DROP DATABASE <db_name>;

!-- 丢弃视图
DROP VIEW <view>;

修改表

1
2
3
4
5
6
7
8
9
10
11

!-- 添加列
ALTER TABLE <tbl>
ADD <field> <dtype>;

!-- 删除列
ALTER TABLE <tbl>
DROP COLUMN <field>;

!-- 修改类型
ALTER TABLE <tbl>
ALTER COLUMN <field> <dtype>;

关键字

TOP

• MSSQL：SELECT TOP <num>/<num> PERCENT *
• MYSQL：LIMIT <num>
• ORACLE：WHERE ROWNUM <= <num>

Alias

• AS：指定行、列别名

Join

• [INNER] JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Union

• UNION：合并SELECT结果集
  • 要求结果集中列数量、类型必须相同

NULL

• IS [NOT] NULL：比较是否为NULL值

• 比较符无法测试NULL值

符号

运算符

• =：有些方言可以使用==
• <>：有些方言可以使用!=
• >
• <
• >=
• <=
• BETWEEN <value> AND <value>
• [NOT] IN (<value>)
• [NOT] LIKE <pattern>
  • %：匹配0个、多个字符
  • _：匹配一个字符
  • [<char>]：字符列中任意字符
  • ^[<char>]/[!<char>]：非字符列中任意字符

逻辑运算

• AND
• OR

符号

• '：SQL中使用单引号包括文本值
  • 大部分方言也支持"双引号

数据类型

MySQL

MSSQL

约束

• 建表时添加约束

  • MSSQL、ORACLE：可直接在字段声明后添加约束
  • MySQL：需独立指定约束

• 向已有表添加约束

  • 可以添加匿名、具名约束
  • MSSQL、ORACLE：有COLUMN关键字

• 删除约束

  • MySQL：使用约束关键字指定
  • MSSQL、ORACLE：使用CONSTRAINT关键字指定

NOT NULL

1

<field> <dtype> NOT NULL

DEFAULT

DEFAULT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

!-- 建表
<field> <dtype> DEFAULT <value>

!-- 已有表添加
!-- MySQL
ALTER TABLE <tbl>
ALTER <field> SET DEFAULT <value>;
!-- MSSQL、ORACLE
ALTER TABLE <tbl>
ALTER COLUMN <field> SET DEFAULT <value>;

!-- 删除
!-- MySQL
ALTER TABLE <tbl>
ALTER <field> DROP DEFAULT;
!-- MSSQL、ORACLE
ALTER TABLE <tbl>
ALTER COLUMN <field> DROP DEFAULT;

UNIQUE

UNIQUE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

!-- 建表
!-- MySQL、MSSQL、ORACLE
CONSTRAINT [<cstrt_name>] UNIQUE (<field>,)
!-- MySQL
UNIQUE [KEY] [<cstrt_name>] (<field>)
!-- MSSQL、ORACLE
<field> <dtype> UNIQUE

!-- 已有表添加
!-- MySQL、MSSQL、ORACLE
ALTER TABLE <tbl>
ADD UNIQUE(<field>);
ALTER TABLE <tbl>
ADD CONSTRAINT <cstrt_name> UNIQUE(<field>,);

!-- 删除
!-- MySQL
ALTER TABLE <tbl>
DROP INDEX <cstrt_name>;
!-- MSSQL、ORACLE
ALTER TABlE <tbl>
DROP CONSTRAINT <cstrt_name>;

PRIMARY KEY

PRIMARY KEY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

!-- 建表
!-- MySQL、MSSQL、ORACLE
CONSTRAINT [<cstrt_name>] PRIMARY KEY (<field>,)
!-- MYSQL
PRIMARY KEY (<field>,)
!-- MSSQL、ORACLE
<field> <dtype> PRIMARY KEY


!-- 已有表添加
!-- MySQL、MSSQL、ORACLE
ALTER TABLE <tbl>
ADD PRIMARY KEY (<field>,);
ALTER TABLE <tbl>
ADD CONSTRAINT <cstrt_name> PRIMARY KEY (<field>,);

!-- 删除
!-- MySQL
ALTER TABLE <tbl>
DROP PRIMARY KEY;
!-- MSSQL、ORACLE
ALTER TABLE <tbl>
DROP CONSTRAINT <cstrt_name>;

FOREIGN KEY

FOREIGN KEY

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

!-- 建表
!-- MySQL、MSSQL、ORACLE
CONSTRAINT [<cstrt_name>] FOREIGN KEY (<field>,)
REFERENCES <tbl>(<field>,)
!-- MYSQL
FOREIGN KEY (<field>,)
REFERENCES <tbl>(<field>,)
!-- MSSQL、ORACLE
<field> <dtype> FOREIGN KEY
REFERENCES <tbl>(<field>,)


!-- 已有表添加
!-- MySQL、MSSQL、ORACLE
ALTER TABLE <tbl>
ADD FOREIGN KEY (<field>,)
REFERENCES <tbl>(<field>,);
ALTER TABLE <tbl>
ADD CONSTRAINT <cstrt_name> FOREIGN KEY (<field>,)
REFERENCES <tbl>(<field>);

!-- 删除
!-  MySQL
ALTER TABLE <tbl>
DROP FOREIGN KEY <cstrt_name>;
!-- MSSQL、ORACLE
ALTER TABLE <tbl>
DROP CONSTRAINT <cstrt_name>;

CHECK

CHECK

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

!-- 建表
!-- MySQL、MSSQL、ORACLE
CONSTRAINT [<cstrt_name>] CHECK(<condition>)
!-- MySQL
CHECK (condition)
!-- MSSQL、ORACLE
<field> <dtype> CHECK(<condition>)

!-- 已有表添加
!-- MySQL、MSSQL、ORACLE
ALTER TABLE <tbl>
ADD CHECK (condition);
ALTER TABLE <tbl>
ADD CONSTRAINT <cstrt_name> CHECK (condition);

!-- 删除
!-- MySQL
ALTER TABLE <tbl>
DROP CHECK <cstrt_name>;
!-- MSSQL、ORACLE
ALTER TABLE <tbl>
DROP CONSTRAINT <cstrt_name>;

内建函数

Date

• MySQL

  • NOW()
  • CURDATE()
  • CURTIME()
  • DATE()
  • EXTRACT()
  • DATE_ADD()
  • DATE_SUB()
  • DATE_DIFF()
  • DATE_FORMAT()

• MSSQL

  • GETDATE()
  • DATEPART()
  • DATEADD()
  • DATEDIFF()
  • CONVERT()

NULL

• MSSQL

  • ISNULL(<field>, <replacement>)

• ORACLE

  • NVL(<field>, <repalcement>)

• MySQL

  • IFNULL(<field>, <replacement>)
  • COALESCE(<field>, <replacement>)

Aggregate聚集函数

Scalar

安装

• 交互式客户端：postgresql
• 服务器：postgres-server
• 额外功能：postgresql-contrib
• 开发工具：postgresql-devel

OpenSuSe

1
2

$ sudo zypper in postgresql postgresql-server \
	postgresql-contrib postgresql-devel

CentOS

1
2

$ sudo yum install postgresql postgresql-server \
	postgresql-contrib postgresql-devel

其他版本

• 从中选择合适版本下载：Postgres Yum repositories

  1	$ wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

• 安装下载的RPM（依赖EPEL repo）

  1	$ sudo yum install pgdg-centos96-9.6-3.noarch.rpm

• 更新Yum、安装指定PG版本

  1 2	$ sudo yum update $ sudo yum install postgresql96-sever postgresql96-contrib

• 安装的PG带有版本后缀，初始化、启动时注意

配置

• postgres安装完成后，默认创建Linux用户

  • 用户密码为空，要为其设置密码以切换到其

    1 2	$ sudo passwd postgres $ su - postgres

  • 用户目录默认是/var/lib/pgsql
  • 很多命令可以切换到用户postgres直接执行

• 初始化数据库簇后，默认创建数据库角色postgres、数据库 postgres

初始化

• 创建新PostgreSQL数据库簇

  1 2 3

  $ sudo postgresql-setup initdb # 或 $ sudo inidb -D /var/lib/pgsql/data

  • 默认数据库存储路径为/var/lib/pgsql/data

• 开启PG密码认证：修改host-based authentication设置

  1 2 3 4

  # /var/lib/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS MEHTOD host all all 127.0.0.1/32 md5 host all all ::1/128 md5

  • 替换默认ident为md5开启密码认证
  • 修改之后需要重启PG

• 修改postgres用户密码，以可以通过密码作为postgres连接 数据库

  1 2 3

  $ su - postgres $ psql -d template1 -c "ALTER USER postgres with password '<passwd>'" # 也可以在数据库prompt中执行

启动数据库

• 作为服务：start、enablePG

  1 2	$ sudo systemctl start postgresql $ sudo systemctl enable postgresql

• 作为普通程序启动：pg_ctl

  1 2	$ su - postgres $ pg_ctl start -D /var/lib/pgsql/data

Roles

PG使用概念roles处理认证、权限问题

• 角色相较于区分明显的用户、组概念更加灵活

• create user和create role几乎完全相同

  • create user：创建角色默认带LOGIN属性
  • create role：创建角色默认不带LOGIN属性

权限

• SUPERUSER/NOSUPERUSER：数据库超级用户
• CREATEDB/NOCREATEDB：创建数据库
• CREATEUSER/NOCREATEUSER
• CREATEROLE/NOCREATEROLE：创建、删除普通用户角色
• INHERIT/INHERIT：角色可以继承所属用户组权限
• LOGIN/NONLOGIN：作连接数据库初始角色名
• REPLICATION/NOREPLICATION：流复制时用到
• CONNECTION LIMIT connlimit
• [ENCRYPTED/UNENCRYPTED]PASSWORD '<passwd>'
• VALID UNTIL '<timestamp>'
• IN ROLE <role_name>[, ...]：角色所属用户组
• IN GROUP <role_name>[, ...]
• ROLE <role_name>[, ...]
• ADMIN <role_name>[, ...]
• USER <role_name>[, ...]
• SYSID uid

角色赋权

1
2
3
4
5

psql> create role/user <name> [[with] <option> [...]];
	# 创建角色时直接赋权
psql> alter role/user <name> [[with] <option> [...]];
psql> grant connect on database <db_name> to <name>;
	# 修改已创建角色权限

组赋权

• 把多个角色归为组，通过给组赋权、撤销权限实现权限管理
• PG中角色赋权是通过inherit方式实现的

1
2
3
4
5
6
7

psql> create role father login createdb createrole;
	# 创建组角色、赋权
psql> create role son1 inherit;
psql> grant father to son1;
	# 创建有`inherit`权限的用户、赋权
psql> create role son2 inherit in role father;
	# 创建用户时直接赋组权

认证方式

Peer Authentication

peer：从内核中获取操作系统中用户名，作为数据库角色名连接

• 默认连接同名数据库
• 信任Linux用户身份（认证），不询问密码
  • 即使-W强制输入密码，也不会检查密码正确性
• 只有local连接支持此方法

Trust Authentication

trust：允许任何数据库角色名的连接

• 信任任何连接、不询问密码

  • 只应该在操作系统层面上能提供足够保护下情况下使用
    • 文件系统权限：限制对Linux域套接字文件的访问
  • 适合单用户、本地连接

• 数据库、用户权限限制仍然存在

Ident Authentication

ident：从ident服务器中获取操作系统中用户名，用于连接数据库

• 仅在TCP/IP连接情况下支持

  • 若被指定给local连接，将使用peer认证

• 数据库服务器向客户端ident服务器询问“连接数据库的”用户， 据此判断连

  • 此流程依赖于客户端完整性，若客户端机器不可信，则 攻击者可以在113端口执行任何程序返回任何用户名
  • 故此认证方法只适合封闭网络，所以客户端机器都被严格 控制
  • 有些ident服务器开启非标准选项导致返回的加密用户名， 此选项应该关闭

  • 基本每个类Unix操作系统都带有ident服务器，用于监听 113端口TCP

涉及配置

• map：运行系统、数据库用户名之间映射

Password Authentication

Password认证：基于密码的认证方式

• password：明文传输密码验证
• md5：MD5-hashed传输密码o

• md5可以避免密码嗅探攻击

• password总应该尽量避免使用

  • 启用db_user_namespace特性时无法使用md5
  • SSL加密连接下password也能安全使用

• 每个数据库的密码存储在pg_authid系统表中

  • 若用户没有设置密码，则存储的密码为null，密码验证 也总是失败
  • 使用create user、alter role等SQL语句修改密码

GSSAPI Authentication

GSSAPI：定义在RFC 2743中的安全认证产业界标准协议

• GSSAPI为支持其的系统自动提供认证
  • 认证本身是安全的，但是通过数据库连接的数据默认没有 加密，除非使用SSL

• PG中GSSAPI需要编译时启用支持

SSPI Authentication

negotiate：windows的安全认证技术

• PG将尽可能使用Kerberos，并自动回滚为NTLM
• 仅服务器、客户端均在windows下或GSSAPI可用的情况下才能 工作

• 使用Kerberos情况下，SSPI、GSSAPI工作方式相同

涉及配置

• include_realm
• map
• krb_realm

Kerberos Authentication

Kerberos：适合公共网络上分布式计算的产业界标准安全认证系统

• Kerberos提供不加密的语句、数据安全认证，若需要加密则使用 SSL
• PG支持Kerberos第5版，需要在build时开启Kerberos支持

涉及配置

• map
• include_realm
• krb_realm
• krb_server_hostname

LDAP Authentication

LDAP：类似password，只是使用LDAP作为密码认证方法

涉及配置

• ldapserver
• ldapport
• ldaptls
• ldapprefix
• ldapsuffix
• ldapbasedn
• ldapbinddn
• ldapbindpasswd
• ldapsearchattribute

RADIUS Authentication

RADIUS：类似password，只是使用RADIUS作为密码认证方法

涉及配置

• radiusserver
• radiussecret
• radiusport
• radiusidentifier

Certificate Authentication

Certificate：使用SSL客户多证书进行认证

• 所以只在SSL连接中可用
• 服务器要求客户端提供有效证书，不会向客户端传递密码prompt
  • cn属性（common name）将回和目标数据库的用户名 比较
  • 可通过名称映射允许cn属性和数据库用户名不同

涉及配置

• map：允许系统、数据库用户名之间映射

PAM Authentication

PAM：类似password，只是使用 PAM(Pluggable Anthentication Modules)作为密码认证机制

涉及配置

• parmservice：PAM服务名
  • 默认postgresql

pg_ctl

pg_ctl：用于控制PostgreSQL服务器的工具，此工具基本需要在 postgres用户下才能使用

• 查看状态：$ pg_ctl status -D /var/lib/pgsql/data

psql

Shell

连接数据库

1

$ psql [-h <host>] [-p <port>] [-U <user_name>] [[-d] <db_name>]

• -h：缺省为local类型连接本地数据库

  • local、host连接类型对应不同认证方式
  • -h localhost和缺省对应不同hba.conf条目

• -p：缺省端口5432
• -U/--user_name=：缺省linux用户名
• [-d]/--database=：当前linux用户名
• -W：密码，peer、trust模式下无价值

Shell命令

• postgres不仅仅提供psql交互环境作为shell命令，还提供可以 直接在shell中运行的数据库命令

  • 当然前提是当前linux登陆用户在数据库中存在、有权限

1
2
3
4

$ createdb <db_name> [-O <user_name>]
	# 创建数据库，设置所有权为`user_name`
$ dropdb <db_name>
	# 删除数据库

元命令

元命令：反斜杠命令，\开头，由psql自己处理

• \后：跟命令动词、参数，其间使用空白字符分割

• 冒号:：不在引号中的冒号开头的参数会被当作psql变量

• 反点号：参数内容被当作命令传给shell， 输出（除去换行符）作为参数值

• 单引号'：参数包含空白时需用单引号包o，其中包含的参数 的内容会进行类C的替换

  • \n（换行）、\digits（八进制）
  • 包含单引号需要使用反斜杠转义

• 双引号\”<\code>

  • 遵循SQL语法规则，双引号保护字符不被强制转换为 小写，且允许其中使用空白
  • 双引号内的双引号使用双双引号""转义

访问其他数据库服务器

SQL默认阻止对组件Ad Hoc Distributed Queries的STATEMENT OpenRowSet/OpenDatasource的访问，需要使用sp_configure 启用Ad Hoc Distributed Queries

数据迁移

直接查询、插入

同库

1
2

insert into dst_tb select * from src_tb;
insert into dst_tb(field1, field2, ...) select (field_a, field_b, ...) from src_tb;

1
2
3
4
5
6

insert into db1.dst_db select * from db2.src_db;
	# 插入已有表
create table db1.dst_tb as select * from db2.src_tb;
	# 创建表并插入数据
rename table src_db.src_tb to dst_db.dst_tb;
	# 重命名迁移完整表

1
2
3
4
5

$ mysqldump [-u user] -p --single-transaction [--where=""] src_db src_tb > src_db.src_tb.sql
	# 导入数据
	# 加上`-d`仅导出表结构
$ mysql [-u user] -p dst_db < src_db.src_tb.sql
	# 导入数据

1

source src_db.src_tb.sql;

1

show global variables like `%secure%`;

1
2

[mysqld]
secure_file_priv=''

1
2
3
4
5
6
7
8
9
10
11
12
13
14

select * from src_tb into outfile file_name.csv
	fields terminated by ','
	optionally enclosed by '"'
	escaped by '"'
	lines terminated by '\r\n';
	# 导出至`.csv`

load data infile file_name.csv [replace] into table dst_tb(field1, field2, @dummy...)
	fields terminated by ','
	optionally enclosed by '"'
	escaped by '"'
	lines terminated by '\r\n';
	# 从`.csv`数据导入
	# 表结构不同时可以设置对应字段，多余字段`@dummy`表示丢弃

1
2
3
4

$ mysql -h host -u user -p src_db -N -e "select * from src_tb;" > file_name.csv
	# 只能通过*shell*查询并导出至文件
	# 需要`file`权限
	# `-N`：skip column names

1
2

load data local infile filename.csv;
	# 指定`local`则从*client*读取文件，否则从*server*读取