MySQL体系结构

发表于 | 分类于

MySQL学习笔记（Day004：权限拾遗/Role模拟/Workbench/体系结构）

@(MySQL学习)

[TOC]

##一. 权限拾遗

1. GRANT与创建用户

1
2
3
4
5
6
mysql> grant select on sys.* to 'perf'@'127.0.0.1' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec) -- 这里有一个warning

mysql> show warnings;
-- 输入warning的Message如下：
-- Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.

上面的这个例子使用GRANT赋权限的同时创建了'perf'@'127.0.0.1'这个用户，但是出现了warning，从给出的提示看来，以后的MySQL版本会废弃掉这种方式

  • 正确的创建用户并赋权的方式
    1
    2
    3
    4
    mysql> create user 'pref'@'127.0.0.1' identified by '123';
    Query OK, 0 rows affected (0.00sec)
    mysql> grant select on sys.* to 'perf'@'127.0.0.1';
    Query OK, 0 rows affected (0.00sec)

2. 查看某一个用户的权限

1
2
3
4
5
6
7
8
mysql> show grants for 'perf'@'127.0.0.1';   
+-----------------------------------------------+
| Grants for perf@127.0.0.1                     |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'perf'@'127.0.0.1'      | -- USAGE表示用户可以登录
| GRANT SELECT ON `sys`.* TO 'perf'@'127.0.0.1' | -- 对sys库的所有表有select权限
+-----------------------------------------------+
2 rows in set (0.00 sec)

3. 删除某一个用户

1
2
mysql> drop user 'perf'@'127.0.0.1';
Query OK, 0 rows affected (0.00sec)

4. 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
mysql> select * from mysql.user where user='perf'\G
*************************** 1. row ***************************
                  Host: 127.0.0.1
                  User: perf
           Select_priv: N   ---由于perf用户是对sys库有权限，所以这里（USER）全是N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
      password_expired: N
 password_last_changed: 2015-11-18 12:20:13
     password_lifetime: NULL
        account_locked: N    -- 如果这里为Y，该用户就无法使用了
1 row in set (0.00 sec)

------------------------我是分割线-----------------------------

mysql> select * from mysql.db where user='perf'\G    
*************************** 1. row ***************************
                 Host: 127.0.0.1
                   Db: sys  -- sys 数据库
                 User: perf
          Select_priv: Y    -- 有select权限，和我们赋予perf的权限一致
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.00 sec)

注意：
不建议使用INSERT或者GRANT对元数据表进行修改，来达到修改权限的目的

5. information_schema

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> select user();
+----------------+
| user()         |
+----------------+
| perf@127.0.0.1 |
+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |  -- 这是一个特殊的数据库，我们虽然可以看见，但其实没有权限
| sys                |
+--------------------+
2 rows in set (0.00 sec)

mysql> use information_schema;
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
mysql> select * from  INNODB_CMP;
ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

二. MySQL模拟角色

1. 角色的定义：

角色(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。
MySQL5.7.X以后可以模拟角色(Role)的功能，通过mysql.proxies_priv模拟实现。
mysql.proxies_priv在5.5.X和5.6.X的时候就存在，但是无法模拟角色(Role)功能。

2. 模拟角色操作：

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- 感谢 @M062 郭释 同学 对这里提出的修正
-- 查看当前proxy是否代开
mysql> show variables like "%proxy%";
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| check_proxy_users                 | OFF   |
| mysql_native_password_proxy_users | OFF   |
| proxy_user                        |       |
| sha256_password_proxy_users       | OFF   |
+-----------------------------------+-------+
4 rows in set (0.00 sec)

mysql> set global check_proxy_users=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global mysql_native_password_proxy_users=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> exit; -- 以上设置参数，对当前会话无效，需要退出后重新登录
             -- 如果有需要，可以设置到my.cnf中去

mysql> show variables like "%proxy%";
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| check_proxy_users                 | ON    |
| mysql_native_password_proxy_users | ON    |
| proxy_user                        |       |
| sha256_password_proxy_users       | OFF   |
+-----------------------------------+-------+
4 rows in set (0.00 sec)

-- ---------------至此，下面的权限映射才有意义---------------

mysql> create user 'junior_dba'@'127.0.0.1';  -- 相当于定于一个 角色(Role),省略密码，仅为演示
                                              -- 但这只是个普通的用户，名字比较有(Role)的感觉
                                              -- 有点类似用户组
Query OK, 0 rows affected (0.00sec)

mysql> create user 'tom'@'127.0.0.1';         -- 用户1，省略密码，仅为演示
Query OK, 0 rows affected (0.02sec)

mysql> create user 'jim'@'127.0.0.1';         -- 用户2，省略密码，仅为演示
Query OK, 0 rows affected (0.02sec)

mysql> grant proxy on 'junior_dba'@'127.0.0.1' to 'tom'@'127.0.0.1';  -- 将junior_dba的权限映射(map)到tom
Query OK, 0 rows affected (0.02sec)

mysql> grant proxy on 'junior_dba'@'127.0.0.1' to 'jim'@'127.0.0.1';  -- 然后映射(map)给jim
Query OK, 0 rows affected (0.01sec)

mysql> grant select on *.* to 'junior_dba'@'127.0.0.1';  -- 给junior_dba（模拟的Role）赋予实际权限
Query OK, 0 rows affected (0.01 sec)


mysql> show grants for 'junior_dba'@'127.0.0.1';        -- 查看 junior_dba的权限
+-------------------------------------------------+
| Grants for junior_dba@127.0.0.1                 |
+-------------------------------------------------+
| GRANT SELECT ON *.* TO 'junior_dba'@'127.0.0.1' |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'jim'@'127.0.0.1';               -- 查看jim的权限
+--------------------------------------------------------------+
| Grants for jim@127.0.0.1                                     |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'127.0.0.1'                      |
| GRANT PROXY ON 'junior_dba'@'127.0.0.1' TO 'jim'@'127.0.0.1' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'tom'@'127.0.0.1';               -- 查看tom的权限 
+--------------------------------------------------------------+
| Grants for tom@127.0.0.1                                     |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'127.0.0.1'                      |
| GRANT PROXY ON 'junior_dba'@'127.0.0.1' TO 'tom'@'127.0.0.1' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.proxies_priv;    --  查看 proxies_priv的权限
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| Host      | User | Proxied_host | Proxied_user | With_grant | Grantor              | Timestamp           |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root |              |              |          1 | boot@connecting host | 0000-00-00 00:00:00 |
| 127.0.0.1 | tom  | 127.0.0.1    | junior_dba   |          0 | root@localhost       | 0000-00-00 00:00:00 |
| 127.0.0.1 | jim  | 127.0.0.1    | junior_dba   |          0 | root@localhost       | 0000-00-00 00:00:00 |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
3 rows in set (0.00 sec)

-- 测试jim的权限
-- 如果不按照@M062 郭释同学给出的修正，
-- 即便映射了junior_dba的select权限，但是jim仍然无法select
-- 甚至show databases; 都无法得到所有数据库信息。
mysql> select user();
+---------------+
| user()        |
+---------------+
| jim@127.0.0.1 |
+---------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> select host,user from mysql.user;
+-----------+------------+
| host      | user       |
+-----------+------------+
| 127.0.0.1 | jim        |
| 127.0.0.1 | junior_dba |
| 127.0.0.1 | perf       |
| 127.0.0.1 | tom        |
| localhost | mysql.sys  |
| localhost | root       |
+-----------+------------+
6 rows in set (0.00 sec)

mysql> create database aaa;
ERROR 1044 (42000): Access denied for user 'junior_dba'@'127.0.0.1' to database 'aaa'

mysql.proxies_priv仅仅是对Role的模拟，和Oracle的角色还是有所不同.官方称呼为Role like

MySQL5.6.X 模拟Role功能需要安装插件，具体方法请参考论坛帖子 官方文档1 官方文档2

三. Workbench与Utilities介绍

1. 下载

2. Workbench功能概述

  • SQL语句格式化
  • SQL关键字upcase
  • MySQL Dashboard
  • SQL语法提示
  • ER图
  • Forward Engine //ER图 --> DB表结构
  • Reverse //DB表结构 --> ER图

3. Utilities安装

1
shell> python setup.py install  # 如果安装不成功，查看一下python的版本。推荐2.7.X

四. MySQL体系结构

1. 数据库

数据库（数据库文件）是一个或者一组二进制文件，通常来说存在与文件系统之上。

2. 数据库实例

由数据库后台进程/线程以及一个共享区域组成(程序的概念),数据库实例是用来操作数据库文件的

注意：MySQL中，数据库实例和数据库是一一对应的。没有Oracle的一对多(RAC)的机制。

3. MySQL体系结构

  1. 单进程多线程结构
    • 不会影响MySQL的性能，看程序如何写。（多进程程序，进程间通信开销大于多线程）
  1. 存储引擎的概念
    • 可以理解成文件系统，例如FAT32, NTFS, EXT4。 一个表是一个分区，引擎就是分区的文件系统
    • 存储引擎的对象就是表
    • show tables; 可以看到每个表对应的是上面引擎(Engine)
    • 除了特殊情况，我们现在就只考虑INNODB

  1. 体系结构图
    MySQL体系结构提

  2. 逻辑存储结构

    |MySQL逻辑存储结构|
    |:—————:|
    |instance|
    |database|
    |schema|
    |table|
    |view|

  • 一个DB对应一个schema
  • 一个DB对应一个文件夹
  • 一个表对应一组文件
1
2
3
                                                 |--> table1 --- | view1 |
MySQL Instance -----> Database ----> Schema ---> |--> table2 --- | view2 |
                                                 |--> table3 --- | View3 |

注意：
MySQL中一个Database对应一个Schema,之所以要有这个schema, 是为了兼容其他数据库
information_schema数据库不是文件夹，存在于内存中，在启动时创建

4. MySQL物理存储结构

  1. MySQL配置文件
  • datadir
    - 存储数据二进制文件的路径

  1. 表结构的组成

    • frm：表结构定义文件
    • MYI：索引文件
    • MYD：数据文件
    • 可以用hexdump -c XXX.frm查看二进制文件(意义不大)
    • show create table tablename;

    • mysqlfrm (utilities工具包)

      1
      shell> mysqlfrm --diagnostic /data/mysql_data/aaa/.a.frm  #可将frm文件转成create table的语句
  1. 错误日志文件
  • log_err
    - 建议配置成统一的名字
  • 方便定位错误
  1. 慢查询日志文件
  • 将运行超过某一个时间阈(yu四声)值的SQL语句记录到文件
    - MySQL < 5.1 ：以秒为单位
- MySQL >= 5.1 : 以毫秒为单位
- MySQL >= 5.5 : 可以将慢查询日志记录到表
- MySQL >= 5.6 : 以更细的粒度记录慢查询
- MySQL >= 5.7 : 增加timestamps支持
  • slow_query_log_file
    - 建议配置成统一的名字
  • 用于优化查询
感谢你对我的支持 让我继续努力分享有用的技术和知识点.