认识数据库(mariadb初步)

(Updated: )

mariadb, mysql的基础认识和应用.

认识数据库

我们现在已经知道数据库有很多种类型的, 比如关系型数据库, NoSQL数据库, 分布式关系型数据库. 而mysql,mariadb只是数量众多的数据库中关系型数据库的一种开源实现. 数据存储在文本文件中在数据量很大中是很麻烦的, 所以数据库就是为了组织数据. 所以采用何种组织数据的方式, 我们就把他成为数据组织模型. 像文件系统组织文件的模型就是树状的. 对于单个文件就是一行行的流.

数据结构模型一开始有: 层次模型. 这种模型使用时就发现了问题, 因为他很难表示两个节点之间的关系, 多个节点就更加困难了. 于是后来就有了网状结构 .

而网状结构却发现, 这个模型和应用程序的耦合度过大. 一旦应用程序的发生改变, 这个结构也要发生变化.

因此最后提出了关系结构模型, 每一个数据被组成行和列, 一个二维交叉的平面结构上. 那么当进行一个大的数据集的设计的时候, 为了达到降低数据冗余, 达到更改方便的时候, 提出设计数据库的几个范式, 有关数据库的设计在之前我曾经简单的在慕课网上学习了一些: 数据库设计

之前说过, 为了满足范式我们经常要进行表的拆分, 这个时候如果要进行查询就要先进行表的连接(join), 而这个连接就是关系性数据库要解决的一个大问题, 表的连接是十分消耗资源的. 这个合并操作对于内存是一个大的消耗. 因此就精心设计算法来避免产生过多中间数据.

那么列举一些典型的RDBMS:
首当其冲的就是MySQL了, 其下有三个重要的分支: MySQL, MariaDB, Percona-Server(反正我是没听说过)

还有很著名的开源数据库: PostgreSQL: pgSQL. 再来一个Oracle的oracle, 还有MS的MSSQL.

接下来就要来看看一个关系型数据库的组成结构了.

sql

不管怎么说, 最终都是要存储在磁盘上的, 所以也可以简单的说MySQL只是一个文件系统上的能够理解数据库源数据的一个软件管理程序罢了. 所有的数据都是现在内存中进行的缓冲 接着在PUSH到磁盘上的. 如一个文件正在被一个进程进行写操作, 那么就会上锁, 这就是为了在资源竞争保护资源. 对于一个关系型数据库来说, 事务是一个必不可少的组件. 多个写操作被认定成一个整体操作. 如何认定一个关系型数据库满足事务功能? 只要满足ACID测试就行, ACID是事务的一个标准, 也就是(原子性, 一致性, 隔离性, 持久性)

回复管理器就是为了保证事务的运行, 他会把事物产生的数据连续存储两次, 一次存储在事务日志中, 一次存储在事务文件中, 这样就可以进行一致性检测.

上面的那一大块东西就是SQL引擎, 或者SQL解释器. 以上就是一个标准的RDBMS的组成结构.

那么我怎么和数据库进行交互呢 ?

一般数据库都会把自己作为一个服务器通过监听套接字来监听客户端发送来的请求, 基于一个而应用层的协议, 基于C/S架构, 每一个软件设计都会设计自己的协议. 那么我们怎么去对接这样的协议, 合成满足对方协议的报文呢? 这就需要数据库的开发者提供客户端的接口, 其中有两种: 程序接口, 以及应用程序接口.

一个通用的接口就是ODBC, 但是过于底层, 所以在此基础上再封装得到对应的程序编程语言的数据库编程接口, 再度封装和抽象, 甚至得到一个编程语言通用的数据库抽象层.

数据库的基本概念

约束(constraint) 我们来举个例子吧, 比如现在想表里填一个学生的年龄, 但是填写了200, 如果数据库对于这个数值的大小有限制, 它可以检测数据的合法性, 这就是约束.

约束有这些常见的类型: 主键 一个或者多个字段的组合, 简单的说就是在本表中唯一标识一个行, 而且必须提供数据, 即NOT NULL.

唯一键 一个或者多个字段的组合, 填入的数据必须能在本表唯一标识本行, 允许为空. 一个表只能有一个主键, 但是唯一键可以出现多次.

外键 一个表中可以填入的数据取决于另一个表的主键已有的数据.

检查性 check约束. 就是上面的那个例子

索引 将表中的一个或者多个字段中的数据复制一份另存并且这些数据需要按需排序存储. 分稀疏索引, 稠密索引

关系计算:

选择: 挑选出符合需要的行

投影: 挑选出需要的字段, 和选择可以同时进行.

连接: 笛卡尔积, 内外连接…

而在组织数据之前, 我们现需要将数据进行抽象. 站在不同的角度来看, 数据是不一样的表现形式. 在物理层上, 决定这数据的存储格式即如何在磁盘上进行文件组织. 而逻辑层上, 描述DB中如何存储, 以及数据间的关系如何处理. 最后在视图层上, 描述DB的部分数据.

( 过于学术化, 不管了

MariaDB

先来说说MariaDB的特性吧:

首先和MySQL一样的, 他们都支持插件式的存储引擎, 这个组件就是既能够和磁盘打交道, 还可以为逻辑层提供保证的这么一个组件. 我们就把他成为存储引擎. 对于Oracle等, 他们的存储引擎都是不可变的, 但是MySQL和MariaDB是可以进行插件式的接口设计的, 只要整合到core上就可以使用. 存储引擎有多重实现版本, 功能和性能都有差异, 用户可以根据需要进行选择.

存储引擎也被成为”表类型” 相比MySQL, MariaDB提供更多的存储引擎.

原先是MyISAM这个引擎, 后来出现了InnoDB, 也是InnoDB得到了广泛使用, 而MyISAM后来推出了针对MariaDB的Aria引擎, 能够进行数据恢复. 其实InnoDB在mariaDB上也不叫做这个名字, 而是XtraDB .都是升级版.

MariaDB提供了诸多扩展和测试组件, 而且他是真正开源的.

在CentOS7上这个mariaDB是默认安装的. 我们来说说他的配置文件吧.

MySQL的配置文件查找方式很有趣, 他是基于ini格式, 为各个程序提供配置信息: [prog_name]

配置文件存在查找次序:

/etc/my.cnf –> /etc/mysql/my.cnf –> –default-extra-file=/PATH/TO/CONF_FILE –> ~/.my.cnf

会匹配最后一个查找到的配置文件

MySQL默认提供了几个配置文件, 这些文件分别对应不同等级的内存或者服务器所使用的配置, small, medium, large, huge, heavy-4G.

MariaDB有两种程序组成一种就是客户端, 一种就是服务端, 服务器端监听两种套接字: ip socket 和 unix socket

监听在TCP的3306端口, 支持远程通信. 而unix socket监听在一个socket文件, 仅支持本地通信. 我们的命令行交互式客户端程序就叫做: mysql

常用的属性是: -u 用户名 -h 主机地址 -p 密码

要注意的是, MySQL的用户账号有两个部分组成, ‘USERNAME‘@’HOST’ 这里的host四为了限制用户可通过哪些主机来进行远程连接MySQL服务, 支持使用通配符: % 匹配任意长度的任意字符, _ 匹配任意单个字符.

在我们刚刚安装完成之后, mysql会预置几个用户, 这是十分危险的, 我们来看一下:

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
29
[root@WWW ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select User,Host,Password from user;
+------+-----------+----------+
| User | Host      | Password |
+------+-----------+----------+
| root | localhost |          |
| root | www       |          |
| root | 127.0.0.1 |          |
| root | ::1       |          |
|      | localhost |          |
|      | www       |          |
+------+-----------+----------+
6 rows in set (0.00 sec)

MariaDB [mysql]>

有两个匿名用户, 而且都是没有密码的. 这可不行 所以我们要先进行安全的初始化. 由一个程序可以帮助我们(你的路径可能和我不一样)

1
2
3
4
5
6
7
8
9
10
11
[root@WWW ~]# /usr/bin/mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):

在回车之后会有一大堆的安全初始化流程:

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y        
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

这里我故意没有禁用管理员远程登录, 其实这个应该是禁用掉的.

现在登陆进去再看:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select User,Host,Password from user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| root | www       | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| root | 127.0.0.1 | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| root | ::1       | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

就已经加密了, 而且匿名用户已经删除了.

mysql的命令有两种, 一种是客户端命令, 一种是客户端命令.

客户端命令是在本地执行的, 例如status这个命令, 每一个客户端命令都有其缩写形式, 以/开头.使用help可以查看所有的客户端命令, 而服务器端命令是需要通过MySQL协议发往服务器端执行之后才获得结果, 每一个命令都需要使用;(默认)来标记结束.

使用show character set;来查看所有支持的字符集, 使用show collation;查看所有的排序规则 , 使用show engines; 查看所有存储引擎支持.

最后再说一下, 如何进行MariaDB的权限管理吧:

先来说怎么进行用户的创建:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [mysql]> create user 'justin'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select User,Host,Password from user;
+--------+-----------+-------------------------------------------+
| User   | Host      | Password                                  |
+--------+-----------+-------------------------------------------+
| root   | localhost | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| root   | www       | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| root   | 127.0.0.1 | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| root   | ::1       | *D79FA10BB5A035FC32C7106AE068E4772D8FF081 |
| justin | %         | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+--------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

删除用户就变得简单了, 直接drop user就行了.

如果这个时候进行登录 会发现justin用户无法看到mysql数据库, 这是因为我们呢还没有给justin用户进行授权.

授权的命令是:

1
2
3
4
5
6
7
8
9
10
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

这个命令也可以进行用户的创建. 后面的省略号甚至可以进行查询的数量进行限制, 把这个help稍微整理一下:

1
GRANT priv_type,... ON [TABLE|...] db_name.tb_name TO 'user'@'host' [IDENTIFIED BY 'password']

可以查看指定用户的权限设定:

1
2
3
4
5
6
7
8
MariaDB [(none)]> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D79FA10BB5A035FC32C7106AE068E4772D8FF081' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

另外, 如果是想知道当前登录用户的权限设定的话, 可以使用内置的函数来:

1
2
3
4
5
6
7
8
MariaDB [(none)]> show grants for current_user();
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D79FA10BB5A035FC32C7106AE068E4772D8FF081' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

而如果想要撤销授权, 就使用revoke指令就行. 格式是:

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

在我们启动MariaDB服务进程的时候,会读取mysql库中的所有授权表到内存中, 所以每当有用户进行登录的时候都会到内存中进行检查. 而当我们进行权限改变, 或者用户更改的时候, 这些操作机会保存在表中, MariaDB会自动进行重读授权表. 而对于不能或者不能及时重读授权表的时候, 我们就使用FLUSH PRIVILEGES 来强制重读.