数据控制

发表于 更新于 分类于 ， 阅读次数： Waline： 本文字数： 2.8k 阅读时长 ≈ 10 分钟

实验目的

  1. 熟悉通过 SQL 对数据库进行数据控制，包括安全性和完整性。
  2. 熟悉数据库的备份与恢复操作。

实验环境

本次实验使用 MySQL 软件，使用其图形化界面完成权限的全套管理工作。备份与还原数据部分使用命令行工具完成。

所用数据库内容为计算机产品数据库，内容同 实验二，实验环境与前两次实验完全相同。

实验内容和完成情况

实验准备

  1. 从 MySQL 界面中选择 root 用户登录

  2. 在左侧管理栏中点击 Administration，然后点击 Users and privileges，在其中添加用户。

    或者在命令行输入 create user 命令创建用户。

将创建以下用户

  • 2017522133（root + 所有权限）
  • U1~U7（创建用户时默认对其赋予 Connect 权限）

数据的安全性

  1. 完成 DBA 方面的授权。包括：
    • 将查询 PC 表的权利授给用户 U1； 
      1
      grant select on table PC to U1;
    • 把对 PC 表和 Laptop 表的全部操作权限赋予用户 U2 和 U3； 
      1
      2
      3
      4
      grant all privileges on table PC to U2;
            grant all privileges on table Laptop to U2;
            grant all privileges on table PC to U3;
            grant all privileges on table Laptop to U3;
    • 把对 Product 表的查询权限赋予全体用户； 
      1
      grant select on table Product to public;
    • 把查询 Laptop 表和修改笔记本价格的权限赋予用户 U4； 
      1
      grant update(price),select on table Laptop to U4;
    • 把对表 Product 的 INSERT 权限授予 U5 用户，并允许将此权限再授予其他用户。 
      1
      grant insert on table Product to U5 with grant option;
  2. U5 将他获得的 INSERT 权限转给 U6。
    1. 以 U5 身份登录数据库。
    2. 命令窗口中输入 grant insert on table Product to U6;。
  3. U6 试图将 Product 表的 Insert 权限传播给 U7。
    1. 以 U6 身份登录数据库。
    2. 命令窗口中输入 grant insert on table Product to U7;。
    3. 运行失败，出现 1142 错误。其意义为：”U6 不可使用 GRANT 命令传播 product 表的 INSERT 权限”。
  4. 收回用户 U3 对于 PC 表的查询权限。
    1. 以 2017522133 用户身份登录数据库，输入 revoke select on table PC from U3;。
    2. 以 U3 用户身份登录，试图在 PC 表中进行 SELECT 操作。
    3. 操作失败，显示 1142 错误 “U3 对于 PC 表的 SELECT 操作被禁止”。
  5. 收回用户 U5 对于 Product 表的 INSERT 权限。

    1. 以 root 身份登录，输入 revoke insert on table Product from U5;。

    2. 以 U5 身份登录，试图进行插入操作，结果操作失败。

    3. 以 U6 身份登录，试图进行插入操作，结果操作成功。说明 MySQL 对于 REVOKE 语句的默认值为 RESTRICT。

      注意 U6 没有查询权限！需要登录 root 用户才能查询到下图数据。

数据的完整性

已知三张表格的 DDL 语言如下：

  1. [实体完整性 I] 在对 pc 表添加数据时没有指定 model。
  2. [实体完整性 II] 在对 product 表添加数据时没有指定 model 和 maker（这两个字段为 product 表主码）。
    先完成表级设定。 
    1
    2
    3
    4
    ALTER TABLE `computer_products`.`product` 
    CHANGE COLUMN `maker` `maker` CHAR(2) NOT NULL,
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`model`, `maker`);

    要先 drop 掉当前的 primary key 再添加，不然会出现重复定义错误。

    然后试图添加数据，失败。错误信息与前述相同。
  3. [参照完整性 I] 试图从 product 表中删除数据或在 laptop 表中插入数据，参照完整性定义为 RESTRICT。
    插入操作错误信息： 
    1
    Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`computer_products`.`laptop`, CONSTRAINT `laptop_ibfk_1` FOREIGN KEY (`model`) REFERENCES `product` (`model`))
    删除操作错误信息： 
    1
    Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`computer_products`.`laptop`, CONSTRAINT `laptop_ibfk_1` FOREIGN KEY (`model`) REFERENCES `product` (`model`))
  4. [参照完整性 II] 试图从 product 表中删除数据或在 pc 表中插入数据，参照完整性定义为 CASCADE。
    插入操作错误信息： 
    1
    Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`computer_products`.`pc`, CONSTRAINT `pc_ibfk_1` FOREIGN KEY (`model`) REFERENCES `product` (`model`) ON DELETE CASCADE ON UPDATE CASCADE)
    可以删除，且 pc 表中的相关数据也被直接删除。
  5. [参照完整性 III] 试图从 product 表中删除数据或在 laptop 表中插入数据，参照完整性定义为 SET NULL。
    • 本例无法完成，因为 product 表的主码和 laptop 表的主码一致且不能为空。
  6. [用户定义完整性] Product 表的形式定义语言如下

    1. Model 被设置为列值唯一，不能再插入相同的值。

    2. (CHECK 短语) 临时表 Computers 定义如下

      1
      2
      3
      4
      5
      6
      7
         create table Computers
      (maker char(2) check(maker in('A','B','C')), //厂商只有A、B、C三家
      model int(4),
      price int(5) check(price>=1000 and price<=10000), //所有电脑的价格在1000元到10000元之间
      primary key(model),
      check (maker='B' or price<=5000)   //A、C两厂家的电脑价格都在5000元以下
      );

      试图进行插入操作。

      这些值均插入成功，MySQL 表格定义的 CHECK 字句没能起作用！

    3. (CONSTRAINT 短语) 临时表 Computers 重新定义如下

      代码无法通过编译，应该是 MySQL 的语法与 SQL 语言标准不同的原因导致不能完全按课本示例来创建。

数据的备份与恢复

请检查 binlog 是否开启，开启方法见本文最后。

  1. 创建备份设备，本例利用 mysqldump 命令将 computer_products 数据库放在了 E 盘根目录中。此操作需要输入密码。

    1
    mysqldump -u 2017522133 -p computer_products>E:\computer_products.sql

    这个 SQL 文件可以用 MySQL 图形界面打开，内容大概如下。

  2. 对表格做插入操作。

  3. 先执行 show master status，然后执行 flush logs，将 master status 显示的文件复制到其他地方。执行 flush logs 意义在于切换为新的日志文件，将问题段集中在一个日志文件中。

    binlog.000011 文件内容如下，可以看到，从 pos=4 到 pos=795 经历了两次 commit 操作，分别为两条数据的新建操作，经分析，若要恢复数据新建的状态，只需恢复这个 log 即可。

  4. 执行 source E:/computer_products.sql。经过一段操作后，登录数据库，查看表格。

    下图表明数据库成功恢复到了数据插入前的状态。

  5. 接下来使用 mysqlbinlog 命令恢复插入两条数据后的状态，此命令需要在 Windows cmd 命令行下进行。

    1
    mysqlbinlog E:/back_binlog.000011 | mysql -u <我的用户名> -p <我的密码> --database=computer_products

    根据提示输入管理员密码。

  6. 登录数据库查看表格情况。下图显示表明，数据库已恢复至插入数据后的状态。

遇到的问题和解决方法

  1. 授权出错，显示 You are not allowed to create a user with GRANT。

    原因：在网上有很多教程说当出现 The user specified as a definer ('root'@'%') does not exist 时表示 root 用户权限不足，只需要执行 GRANT ALL ON *.* TO 'root'@'%'; 就可以了，但是往往又会出现 You are not allowed to create a user with GRANT 的错误提示。这是因为 GRANT ALL ON *.* TO 'root'@'%'; 这条语句中 @'%' 中的百分号其实是 root 用户对应 host 的名称，很多人并没有注意到他的 root 用户对应的其实是 localhost，直接就执行了上面的语句，所以才会报错。

    解决方案：只要将 GRANT ALL ON *.* TO 'root'@'%'; 中的 % 改为对应的 host 名称即可，最后还要刷新一下权限 FLUSH PRIVILEGES;。

  2. MySQL 中的角色（Roles）概念？不是没有角色吗？

    在 MySQL8.0 版本后，有了 Roles 概念的引入。MySQL 的官方文本对此作了介绍。其中明确提出 ——

    1. 可以用 CREATE ROLE 命令建立角色；
    2. 可以用 GRANT 命令授予这些角色以权限；
    3. 可以用 SHOW GRANTS FOR 命令检查某用户拥有的权限；
    4. 可以用 REVOKE 命令收回用户的权限；
    5. 可以用 DROP ROLE 命令删除角色。

  3. MySQL 开启 binlog 的方法。

    • 打开 MySQL 配置文件 my.cnf，在 [mysqld] 下面增加 log-bin=mysql-bin（UNIX）
    • 修改 MySQL 配置文件 my.ini ，添加配置：log-bin=log-bin=<日志存储实际路径>（Windows）
    • 把前面的#去掉。

  4. MySQL 中备份与恢复命令语法

    • mysqldump：用于做数据库的全量备份。
      格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql

    • source：用于从全量备份中恢复。
      用 use 进入到某个数据库，mysql>source d:\test.sql（刚备份的文件）

    • mysqlbinlog：用于查看 binlog 以及从日志文件恢复。

    • 查看日志：

      1. 查看所有 binlog 日志列表

        1
        mysql> show master logs;

      2. 查看 master 状态，即最后 (最新) 一个 binlog 日志的编号名称，及其最后一个操作事件 pos 结束点 (Position) 值

        1
        mysql> show master status;

      3. 刷新 log 日志，自此刻开始产生一个新编号的 binlog 日志文件

        1
        mysql> flush logs;

        每当 mysqld 服务重启时，会自动执行此命令，刷新 binlog 日志；在 mysqldump 备份数据时加 -F 选项也会刷新 binlog 日志。

      4. 重置 (清空) 所有 binlog 日志

        1
        mysql> reset master;

      5. 从日志中恢复：

        1
        mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
          
        1
        2
        3
        4
        5
        --start-position=953                   起始pos点
        --stop-position=1437                   结束pos点
        --start-datetime="start_date" 起始时间点
        --stop-datetime="stop_date"  结束时间点
        --database=x                   指定只恢复x数据库(一台主机上往往有多个数据库，只限本地log日志)
        

        1
        2
        3
        4
           -u --user=name              Connect to the remote server as username.连接到远程主机的用户名
        -p --password[=name]        Password to connect to remote server.连接到远程主机的密码
        -h --host=name              Get the binlog from server.从远程主机上获取binlog日志
        --read-from-remote-server   Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

资料来源