数据库安全与保护

本文共--字 阅读约--分钟 | 浏览: -- Last Updated: 2021-04-18

DBMS 对数据库的安全保护功能是通过 完整性控制、安全性控制、并发控制和数据库备份与恢复 四个方面来实现的。

一、数据库完整性

1.概念

数据库完整性：指数据库中数据的 正确性 和 相容性 。

数据完整性约束是为了防止数据库中存在不符合语义的数据，为了维护数据的完整性， DBMS 必须提供一种机制来检查数据库中的数据，以判断其是否满足语义规定的条件。

DBMS检查数据是否满足完整性约束条件的机制称为 完整性检查。

在 MySQL 中，各种完整性约束可以通过 CREATE TABLE 或 ALTER TABLE 语句定义。

2.完整性约束条件的作用对象

完整性约束条件的作用对象可以是 列、元组和表。

  • 列级约束：主要是指对列的类型、取值范围、精度等的约束。
  • 元组约束：指元组中各个字段之间的相互约束。
  • 表级约束：指若干元组之间、关系之间的联系的约束。

3.完整性约束的类型

关系模型中可以有三类完整性约束，分别是 实体完整性约束、参照完整性约束 和 用户定义的完整性。

  • 实体完整性：是通过主键约束和候选键约束来实现的。

  • 参照完整性：通过创建表 CREATE TABLE 或 更新表 ALTER TABLE 的同时 定义一个外键声明 来实现。外码来自被参照关系的主码，且可以为空，但作为被参考的关系的主码不能为空(外码可以为空时，外码通常是非主属性)

  • 用户定义的完整性：比如，非空约束、CHECK约束（可以对列或表实施，语法 CHECK(expr)）、 触发器。

4.主键约束

  • 每一个表只能定义一个主键；

  • 主键的值（键值）必须能够唯一标志表中的每一行记录，且不能为 NULL

  • 复合主键不能包括不必要的多余列；

  • 一个列名在复合主键的列表中只能出现一次。

使用 PRIMARY KEY 定义主键约束，有两种实现方式：

  • 列的完整性约束；
  • 表的完整性约束；

5.候选键与主键之间的区别

  • 一个表中只能创建一个主键，但可以定义若干个候选键；

  • 定义主键约束时，系统会自动产生 PRIMARY KEY 索引，而定义候选键约束时，系统自动产生 UNIQUE 索引。

6.外键声明

  • 在表中某个列的属性定义后直接加上 reference_definition 语法项；

  • 在表中所有列的属性定义后添加 FOREIGN KEY index_col_name reference_definition 子句的语法项；

reference_definition 的语法定义：

REFERENCES tbl_name (index_col_name, ...)
  [ON DELETE reference_option]
  [ON UPDATE reference_option]

-- 举例 创建表 student 主键学号，外键班级号
-- 注意：要使用 REFERENCES 指明关系 表名(列名)
CREATE TABLE student
(
  sno CHAR(10) NOT NULL,
  sname CHAR(10) NOT NULL,
  gender CHAR(1) NOT NULL,
  cno CHAT(10),
  PRIMARY KEY(sno),
  FOREIGN KEY(cno) REFERENCES school_class(cno)
)

FOREIGN KEY(列名) REFERENCES 表名(列名) [参照动作 实施策略]

其中，

index_col_name的语法格式： col_name [(length)] [ASC|DESC]。

reference_option的语法格式： RESTRICT|CASCADE|SET NULL|NO ACTION

参照动作的的实施策略

  • RESTRICT：默认，表示限制，即当要删除或更新被参照表中被参照列上，并在外键中出现的值时，系统拒绝对被参照表的删除或更新操作。

  • CASCADE：表示级联，即从被参照表中删除或更新记录行时，自动删除或更新参照表中匹配的记录行。

  • SET NULL：表示置空，即当从被参照表中删除或更新记录行时，设置参照表中与之对应的外键列的值为 NULL。

  • NO ACTION：表示不采取实施策略，同RESTRICT 。

7.外键约束

  • 被参照表必须已经用一条 CREATE TABLE 语句创建了，或者必须是当前正在创建的表（自参照表）；

  • 必须为被参照表定义主键；

  • 必须在被参照表的表名后面指定列名或列名的组合，这个列或列的组合必须是这个被参照表的主键或候选键；

  • 尽管主键是不能够包含空值的，但允许在外键中出现空值；

  • 外键中的列的数目必须和被参照表的主键中的列的数目相同。

8.参照关系

例如：学生（学号，姓名，性别，年龄，专业号），专业（专业号，专业名）

专业号 是 学生表的一个属性，但不是码，同时专业号 是 专业表 的主码， 则 专业号 是 学生表的 外码。
学生表为参照关系，专业表为被参照关系。

  • 设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S的主码Ks相对应（有一个一一对应的关系，不一定同名），则称F是R的外码。

  • 基本关系R称为参照关系

  • 基本关系S称为被参照关系 或目标关系

  • 关系R和S不一定是不同的关系

  • 目标关系S的主码Ks 和参照关系的外码F必须定义在同一个（或一组）域上

  • 外码并不一定要与相应的主码同名

9.命名完整性约束

命名完整性约束的方法是在各种完整性约束的定义说明之前加上关键字 CONSTRAINT。

语法格式：CONSTRAINT [symbol]

  • symbol ：指定的约束名字，必须是唯一的如果没有明确给出约束的名字， MySQL 会自动创建一个约束名字；

  • 只能给基于表的完整性约束指定名字，无法给基于列的完整性约束指定名字。

10.ALTER TABLE

可以使用 ALTER TABLE 语句来更新与列或表有关的各种约束。

比如可在 ALTER TABLE 语句中使用 ADD CONSTRAINT 子句添加约束。

（1）完整性约束不能直接被修改，若要修改要用 ALTER TABLE 先删除约束， 再增加一个与该约束同名的新约束；
（2）使用 ALTER TABLE 语句，可以独立地删除完整性约束，而不会删除表本身，若使用 DROP TABLE 删除一个表，则表中所有的完整性约束都会自动被删除。

二、触发器

触发器：是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。

触发器的主要作用：实现主键和外键不能保证的复杂的参照完整性和数据的一致性，从而有效地保护表中的数据。

1.CREATE TRIGGER

使用 CREATE TRIGGER 语句创建触发器

语法格式：

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON tbl_name FOR EACH ROW trigger_body
  • trigger_name：指定触发器的名称。
  • trigger_time：指定触发器被触发的时刻，有两个选项：BEFORE 和 AFTER ；
  • trigger_event: 指定触发事件，即指定激活触发器的语句的种类：INSERT、UPDATE 和 DELETE。
  • tbl_name：指定与触发器相关联的表名，必须引用永久性表；
  • FOR EACH ROW：指定对于受触发事件影响的每一行都要激活触发器的动作；
  • trigger_body：指定触发器动作主体，即包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构；

在触发器的创建中，每个表每个事件每次只允许一个触发器。因此，每个表最多支持 6 个 触发器 (INSERT前后、UPDATE前后 和 DELETE前后)。

举例：在数据库 mysql_test 的表 customers 中创建一个触发器 customers_insert_trigger，用于每次向表 customers 插入一行数据时，将用户变量 str 的值设置为 one customer added! 。

CREATE TRIGGER mysql_test.customers_insert_trigger AFTER INSERT
 ON mysql_test.customers FOR EACH ROW SET @str='one customer added!';

2.DROP TRIGGER

使用 DROP 语句将触发器从数据库中删除

语法格式：DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

DROP TRIGGER IF EXISTS mysql_test.customers_insert_trigger

需要注意的是这里删除使用的 schema_name 数据库名，并且为可选。

3.触发器的种类

3.1、INSERT

  • 在 INSERT触发器代码内，可引用一个名为 NEW （不区分大小写）的虚拟表来访问被插入的行；
  • 在 BEFORE INSERT 的触发器中，NEW 中的值可以被更新；
  • 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含的值是 0 ，在 INSERT 执行之后将包含新的自动生成值。

3.2、DELETE

  • 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行；
  • OLD 中的值全部是只读的，不能被更新。

3.3、UPDATE

  • 在 UPDATE 触发器代码内，可以引用一个名为 OLD 不区分大小写）的虚拟表来访问以前的值，也可引用一个名为 NEW（不区分大小写）的虚拟表来访问新更新的值；
  • 在 BEFORE UPDATE 触发器中，NEW 中的值可以被更新；
  • OLD 中的值全部是只读的，不能被更新；
  • 当触发器涉及触发表自身的更新操作时，只能使用 BEFORE UPDATE ，不能使用 AFTER UPDATE，因为这会造成死循环。

安全性与访问控制

数据库的安全性：指保护数据库以防止不合法的使用而造成数据泄露、更改或破坏。

数据库系统对数据的安全管理是使用 身份验证 、 数据库用户权限确认 等访问控制措施，来保护数据库中的信息资源，以防止这些数据遭受破坏。

MySQL 的用户账号及相关信息都存储在一个名为 mysql 的 数据库中，这个数据库里有一个名为 user 的数据表，root 用户拥有对整个 MySQL 服务器完全控制的能力。

1.CREATE USER

使用 CREATE USER 创建 MySQL 账户

语法格式：CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

  • user：指定创建用户账户，其格式为user_name@host_name。其中 user_name 表示用户名，host_name表示主机名。如果没有指定主机名，则主机名会默认为%，表示一组主机。
  • IDENTIFIED BY：指定用户账户对应的口令。
  • PASSWORD：指定散列口令（散列口令可通过 PASSWORD 函数获得）。
  • password：指定用户账号的口令（明文或散列口令）。

举例：在 MySQL 服务器中添加两个新的用户，其用户名分别为 zhangsan 和 lisi ，他们的主机名均为 localhost ，用户 zhangsan 的口令设置为明文 123 ，用户 lisi 的口令设置为对明文 456 使用 PASSWORD 函数返回的散列值。

-- 先取出 456 的散列值，假设为'*789xxx' 
SELECT PASSWORD(456); 

CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123',
'lishi'@'localhost' IDENTIFIED BY PASSWORD '*789xxx';
  • 要使用 CREATE USER 语句，必须拥有存储用户信息的 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限；
  • 使用 CREATE USER 语句创建一个用户账户后，会在系统自身的 mysql 数据库的 user 表中添加一条新记录；
  • 如果两个用户具有相同的用户名和不同的主机名 MySQL 会将它们视为不同的用户，并允许为这两个用户分配不同的权限集合；
  • 如果在 CREATE USER 语句的使用中，没有为用户指定口令，那么 MySQL 允许该用户可以不使用口令登录系统；
  • 新创建的用户拥有的权限很少。

2.DROP USER

使用 DROP USER 删除用户账户以及相关权限

语法格式： DROP USER user[,user, ...]。

  • DROP USER 语句可用于删除一个或多个 MySQL 账户，并消除其权限；
  • 要使用 DROP USER 语句，必须拥有 MySQL 中 mysql 数据库的 DELETE 权限 或全局 CREATE USER 权限；
  • 在 DROP USER 语句的使用中，如果没有明确地给出账户的主机名，则该主机名会默认为 %。
  • 用户的删除不会影响到他们之前所创建的表、索引或其他数据库对象

3.RENAME USER

使用 RENAME USER 语句修改已经存在的用户账户

语法格式：RENAME USER old_user TO new_user [,old_user TO new_user] ...

  • RENAME USER 语句用于对原有 MySQL 账户进行重命名；
  • 要使用 RENAME USER 语句，必须拥有 MySQL 中 mysql 数据库的 UPDATE 权限或全局 CREATE USER 权限；
  • 如果系统中旧账户不存在或新账户已存在，则语句执行会出现错误。

4.SET PASSWORD

使用 SET PASSWORD修改登录口令

语法格式：

SET PASSWORD [FOR user] =
{
 PASSWORD('new_password')| 'encrypted password'
}
  • FOR：指定欲修改口令的用户；
  • PASSWORD('new_password')：使用函数 PASSWORD() 设置新口令 new_password。
  • encrypted password：表示已被函数 PASSWORD() 加密的口令值。

5.SHOW GRANTS FOR

使用 SHOW GRANTS FOR 语句查看用户的权限

语法格式：SHOW GRANTS FOR user;

新创建的用户仅有一个权限：USAGE ON *.*，表示该用户在任何数据库和任何表上对任何内容没有权限。

6.GRANT ON TO

使用 GRANT 语句为用户授权

语法格式：

GRANT 
  priv_type [(column_list)][, priv_type [(column_list)]] ...
  ON [object_type] priv_level
  TO user_specification [, user_specification]...
  [WITH GRANT OPTION]
  • priv_type：指定权限的名称；
  • column_list：指定权限要授予给表中哪些具体的列；
  • ON：指定权限授予的对象和级别；
  • object_type：指定权限授予的对象类型，包括表(TABLE)、函数 (FUNCTION) 和存储过程 (PROCEDURE)
  • priv_level：指定权限的级别，可以授予的权限有：列权限、表权限、数据库权限和用户权限。相应地，在 GRANT 语句中用于指定权限级别的值有这几类格式：
    • *表示当前数据库中的所有表；
    • *.*表示所有数据库中的所有表；
    • db_name.*表示某个数据库中的所有表；
    • db_name.tbl_name表示某个数据库中的某个表或视图；
    • db_name.routine_name表示某个数据库中的某个存储过程或函数。
  • TO：设定用户的口令，以及指定被授予权限的用户 user。如果 user 不存在， MySQL 会自动执行一条 CREATE USER 语句创建这个用户，因此 GRANT 亦可以用于创建用户账号；
  • user_specification： 常用语法格式 user [IDENTIFIED BY [PASSWORD] 'password']，与CREATE USER语句后的一样。
  • WITH：用于实现权限的转移或限制。

举例：授予用户 zhangsan 在数据库 mysql_test 的表 customers 上拥有对列 cust_id 和列 cust_name 的 SELECT 权限。

GRANT SELECT (cust_id, cust_name)
 ON mysql_test.customers
 TO 'zhangsan'@'localhost';

6.1、priv_type在不同的 priv_level下可以使用的值：

  • 授予表权限时，可以指定以下值：SELECT、INSERT、DELETE、UPDATE、REFERENCES(可以创建外键)、CREATE(可以创建表)、ALTER(可以修改表)、INDEX(可以定义索引)、DROP(可以删除表)、ALL 或 ALL PRIVILEGES(所有的权限)；

  • 授予列权限时只能指定SELECT、INSERT和UPDATE，同时权限的后面需要加上列名列表column_list。

  • 授予数据库权限时，可以指定除授予表权限时的所有值外，还有下列值：CREATE TEMPORARY TABLES(可以创建临时表)、CREATE VIEW(可以创建视图)、SHOW VIEW(可以查看视图定义)、CREATE ROUTINE(可以创建存储过程或存储函数)、ALTER ROUTINE(可以更新或删除存储过程或存储函数)、EXECUTE ROUTINE(可以调用存储过程或存储函数)、LOCK TABLES(可以锁定表)。

  • 最有效率的权限是用户权限，授予用户权限时，可以指定为授予数据库权限时的所有值之外，还可以指定以下值： CREATE USER(可以创建用户和删除用户)、SHOW DATABASES(可以查看所有已有数据库的定义)。

6.2、权限的转移 WITH GRANT OPTION

举例：授予当前系统中一个不存在的用户 zhou 在数据库 mysql_test 的表 customers 上拥有 SELECT 和 UPDATE 权限，并允许其可以将自身的这个权限授予给其他用户。

-- 不存在的用户需要指定password
-- 允许其可以将自身的这个权限授予给其他用户。
GRANT SELECT,UPDATE
ON mysql_test.customers
TO 'zhou'@'localhost' IDENTIFIED BY '123'
WITH GRANT OPTION;

5.REVOKE ON FROM

当需要撤销一个用户的权限、而又不希望将该用户从系统中删除时，可以使用 REVOKE；

语法格式：

REVOKE 
  priv_type [(column_list)][, priv_type [(column_list)]] ...
  ON [object_type] priv_level
  FROM user [, ...]

-- 回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, ...]

要使用 REVOKE 语句，必须拥有 mysql 数据库的全局 CREATE USER 权限 或 UPDATE 权限。

举例：回收系统中已存在用户 zhou 在数据库 mysql_test 的表 customers 上的 SELECT 权限。

REVOKE SELECT
ON mysql_test.customers
FROM 'zhou'@'localhost'

三、事务与并发控制

并发控制：DBMS 对并发操作提供一定的控制，以防止它们彼此干扰，从而保证数据库的正确性不被破坏，避免数据库的不一致性。

1.事务

事务的概念：事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单位，要么全部执行，要么全部不执行，是一个不可分割的工作单位。

事务与程序的区别：

  • 程序是静止的，事务是动态的，是程序的执行而不是程序本身；
  • 同一个程序的多个独立执行可以同时进行，而每一步执行则是一个不同的事务。

2.定义事务的语句

  • BEGIN TRANSACTION: 事务通常以该语句开始；
  • COMMIT: 表示提交，即提交事务的所有操作，具体地说就是将事务中所有对数据库的更新写回磁盘上的物理数据库中去，事务正常结束；
  • ROLLBACK: 表示回滚，即在事务运行的过程中若发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。

3.事务的特征

为了保证数据的一致性和正确性，数据库系统必须保证事务具有四个特征（ACID）原子性、一致性、隔离性、持续性。

  • 原子性(Atomicity): 保证事务包含的一组更新操作是原子不可分的，即事务是不可分割的最小工作单位，所包含的这些操作是一个整体。

  • 一致性(Consistency): 要求事务必须满足数据库的完整性约束，且事务执行完毕后将数据库由一个一致性状态转变为另一个一致性状态。

  • 隔离性(Isolation): 要求事务是彼此独立的、隔离的，即一个事务的执行不能被其他事务所干扰，一个事务对数据库变更的结果必须在它 COMMIT 后，另一个事务才能存取。多个事务并发执行的结果等价于它们的一种顺序执行结果，这一特性称为可串行性。

  • 持续性(Durability): 也称为永久性，是指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

举例：依据事务的 ACID 特征，编写银行数据库系统中的转账事务T，从账户 A 转账 S 金额资金到账户 B 。

BEGIN TRANSACTION
  read(A);
  A=A-S;
  write(A);
if (A<0) ROLLBACK;
else {
  read(B);
  B=B+S;
  write(B);
  COMMIT;
}

以上并非 MySQL 的写法，只是为了便于理解。

4.并发操作问题

事务是并发控制的基本单位，保证事务的 ACID 特征是事务处理的重要任务。但当多个事务交错执行时，可能出现不一致的问题，这也称为并发操作问题；

典型的并发操作问题有：丢失更新、不可重复读、读脏数据。

4.1、丢失更新

丢失更新：设有两个事务 T1 和 T2, 当它们同时读入同一个数据并加以修改时，事务 T2 的提交结果会破坏事务 T1 提交的结果，由此导致事务 T1 的修改被丢失。

丢失更新即前面的修改被后面修改覆盖

时间 更新事务T1 数据库中A的值 更新事务T2
t0 100
t1 FIND A
t2 FIND A
t3 A=A-30
t4 A=A*2
t5 UPD A
t6 70 UPD A
t7 200

上例中，T1 的修改被丢失；

4.2、不可重复读

不可重复读：设有两个事务 T1 和 T2, 不可重复读是指事务 T1 读取数据后，事务 T2 执行更新操作，使事务 T1 无法再现前一次读取结果。通常有三种情况：

  • 被修改：事务 T1 读取某一数据后，事务 T2 对其做了修改，当事务 T1 再次读该数据时，得到与前一次不同的值；

  • 被删除：事务 T1 按一定条件从数据库中读取了某些数据记录后，事务 T2 删除了其中部分记录，当事务 T1 再次按相同条件读取数据时，发现某些记录神秘地消失了；

  • 被增加：事务 T1 按一定条件从数据库中读取了某些数据记录后，事务 T2 插入了一些记录，当事务 T1 再次按相同条件读取数据时，发现多了一些记录了。

不可重复读即再次读取数据时，数据被其他事务修改/增加/删除了

时间 读事务T1 数据库中A的值 更新事务T2
t0 100
t1 FIND A
t2 XFIND A
t3 A=A*2
t4 UPD A
t5 200 COMMIT
t6 FIND A

上例中，T1 再次读取的时候，值被修改。

4.3、读脏数据

读脏数据：设有两个事务 T1 和 T2, 读脏数据是指，事务 T1 修改某一数据，并将其写回磁盘，事务 T2 读取同一数据后，事务 T1 由于某种原因被撤销，这时事务T1 已被修改过的数据恢复原值，事务 T2 读到的数据就与数据库中的数据不一致，则事务 T2 读到的数据就为脏数据。

读脏数据即某个事务T1操作之后被撤销，其他事务读取了T1操作之后撤销之前的值。

时间 更新事务T1 数据库中A的值 读事务T2
t0 100
t1 FIND A
t2 A=A-30
t3 UPD A
t4 70 FIND A
t5 *ROLLBACK*
t6 100

上例中，T2 读取到的值就是脏数据，因为之后被撤销该值又变回了100。

5.锁

一个锁实质就是允许或阻止一个事务对一个数据对象的存取特权。

5.1、封锁类型

  • 排他锁(Exclusive Lock, X锁)
  • 共享锁(Share Lock, S锁)

一般写操作要求 X 锁，读操作要求 S 锁。

5.2、封锁的工作原理

  • 若事务 T 对数据 D 加了 X 锁，则所有别的事务对数据 D 的锁请求都必须等待直到事务 T 释放锁；

  • 若事务 T 对数据 D 加了 S 锁，则别的事务还可对数据 D 请求 S 锁，而对数据 D 的 X 锁请求必须等待直到事务 T 释放锁；

  • 事务执行数据库操作时都要先请求相应的锁，即对读请求 S 锁，对更新（插入、删除、修改）请求 X 锁；

  • 事务一直占有获得的锁直到结束（ COMMIT 或 ROLLBACK ）时释放。

因此，利用封锁机制可以解决上述并发操作所带来的三个不一致问题。

6.粒度

通常以 粒度 来描述封锁的数据单元的大小。

粒度越细，并发性就越大，但软件复杂性和系统开销也就越大。锁住整个数据库，DBMS 的管理与控制最简单，系统开销也最小。

7.封锁的级别

封锁的级别又称为一致性级别或隔离度。由各种锁的类型与其封锁期限组合可形成不同的封锁级别：

封锁级别 说明 一致性保证
0级 封锁的事务不重写其他非 0 级封锁事务的未提交的更新数据。实用价值不大。
1级 被封锁的事务不允许重写未提交的更新数据。（别的事务在写则不写，就不会覆盖） 丢失更新
2级 被封锁的事务既不重写也不读未提交的更新数据。（别的事务在写则不读也不写，写的操作可能被撤销，则不读） 丢失更新、读脏数据
3级 被封锁的事务不读未提交的更新数据，也不写（包括读操作的）任何未提交数据。（别的事务在写时不读也不写，别的事务在读的时候也不写） 丢失更新、读脏数据、不可重复读

8.死锁

封锁带来的一个重要问题是可能引起 “活锁” 与 “死锁 ”。

活锁：在并发事务处理过程中，由于锁会使一事务处于等待状态而调度其他事务处理，因而该事务可能会因为 优先级低而永远等待下去。活锁问题的解决与调度算法有关，一种最简单的办法是先来先服务。

死锁：两个以上事务循环等待被同组中另一事务锁住的数据单元的情形。 DBMS 提供死锁预防、死锁检测和死锁发生后的处理技术与方法。

8.1、预防死锁的方法

  • 一次性锁请求；
  • 锁请求排序；
  • 序列化处理；
  • 资源剥夺；

8.2、死锁检测

对待死锁的另一种办法是不去防止，而让其发生并随时进行检测。 死锁检测可以用 图论 的方法实现，并以正在执行的事务为结点。

9.可串行化

若在一个调度中，对于任意两个事务 T1 和 T2，要么 T1 的所有操作都在 T2 所有操作之前，要么反之，则该调度是串行的，因而是正确的。若一个调度等价于某一串行调度，即它所产生的结果与某一串行调度的结果一样，则说调度是可串行化。

当且仅当一组事务的并发执行调度是可串行化的，才认为它们是正确的。

10.两端封锁法

采用 两段封锁法(Two Phase Locking, 2PL)是一种最简单而有效的保障封锁其调度是可串行性的方法。

两段封锁法是事务遵循两段锁协议的调度方法。

两段锁协议: 所有事务都必须遵循的关于基本操作执行顺序的一种限制。规定在任何一个事务中，所有加锁操作都必须在所有释放锁操作之前。

事务划分成两个阶段：加锁(发展)阶段、释放锁(收缩)阶段。

遵循两段锁协议的事务的任何并发调度都是可串行化的。

四、备份与恢复

可能会造成数据库运行事务异常中断的因素：计算机硬件故障、计算机软件故障、病毒、人为误操作、自然灾害、盗窃。

面对这些可能的因素会造成数据丢失或被破坏的风险，数据库系统提供了 备份和恢复 策略来保证数据库中数据的可靠性和完整性。

数据库备份：通过 导出数据 或者 复制表文件 的方式来制作数据库的复本。

数据库恢复：当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

数据库的恢复是以 备份 为基础的，它是与备份相对应的系统维护和管理操作。

1.SELECT INTO … OUTFILE

语法格式：

SELECT * INTO OUTFILE 'file_name' export_options 
        |INTO DUMPFILE 'file_name'

其中，export_options 的格式是：

[FIELDS
  [TERMINATED BY 'string'] 
  [[OPTIONALLY] ENCLOSED BY 'char']
  [ESCAPED BY 'char']
]
[LINES TERMINATED BY 'string']

  • file_name:指定数据备份文件的名称。在文件中，导出的数据行会以一定的形式存储，其中空值用\N表示；

  • 导出语句使用关键字 OUTFILE 时，可以在语法项 export_options 中加入以下两个自选的子句：FIELDS 和 LINES决定数据行在备份文件中存储的格式。默认是 FIELDS

  • FIELDS 子句中三个亚子句。

    • TERMINATED BY 指定字段值之间的符号；TERMINATED BY '\t' tab键
    • [OPTIONALLY] ENCLOSED BY 指定包裹文件中字符值的符号；ENCLOSED BY '\'' 单引号
    • ESCAPED BY:指定转义字符；ESCAPED BY '\\' 斜杠

  • 在 LINES 子句使用关键字 TERMINATED BY 指定一个数据行结束的标志。TERMINATED BY '\n'

  • 导出语句中使用的关键字 DUMPFILE 时，导出的备份文件里面所有数据行都会彼此紧挨着放置，即值和行之间没有任何标记。

2.LOAD DATA … INFILE

语法格式：

LOAD DATA INFILE 'file_name' INTO TABLE tbl_name
[FIELDS
  [TERMINATED BY 'string'] 
  [[OPTIONALLY] ENCLOSED BY 'char']
  [ESCAPED BY 'char']
]
[LINES
  [STARTING BY 'string']
  [TERMINATED BY 'string']
]

LINES 子句中的 TERMINATED BY亚子句用来指定一行结束的标志；STARTING BY 亚子句则指定一个前缀，导入数据行时，忽略数据行中的该前缀和前缀之前的内容。如果某行不包括该前缀，则整个数据行被跳过。

3.LOCK TABLES & UNLOCK TABLES

在多个用户同时使用 MySQL 数据库的情况下，为了得到一个一致的备份，需要在指定的表上使用 LOCK TABLES table_name READ 语句做一个读锁定，以防止在备份过程中表被其他用户更新；而恢复数据时，则需要使用 LOCK TABLES table_name WRITE 语句做一个写锁定，以避免发生数据冲突。

备份时，做读锁定；恢复时，做写锁定。

在数据库备份或恢复完毕之后需要使用UNLOCK TABLES 语句对该表进行解锁。