数据库的管理和维护

数据的备份

日常生活中，经常会为自己家的房门多配几把钥匙，为自己的爱车准备一个备胎，这些事情其实都是在做备份。在数据库的维护过程中，数据也经常需要备份，以便在系统遭到破坏或其他情况下还原数据。为了完成数据库的备份，MySQL提供了一个mysqldump命令，mysqldump命令可以将数据库导出成SQL脚本，以实现数据的备份。

备份单个数据库

语法：

mysqldump -uusername -ppassword dbname [tbname1 [tbname2…]]>filename.sql

上述语法格式中，-u后面的参数username表示用户名，-p后面的参数password表示登录密码，dbname表示需要备份的数据库名称，tbname表示数据库中的表名，可以指定一个或多个表，多个表名之间用空格分隔。如果不指定数据表名，则备份整个数据库。filename.sql表示备份文件的名称，文件名前可以加上绝对路径。 在使用mysqldump命令备份数据库时，直接在命令行窗口执行该命令即可，不需要登录到MySQL数据库。

备份多个数据库

mysqldump -uusername –ppassword --databases dbname1 [dbname2 dbname3…]>filename.sql

上述语法格式中，“--databases”参数后面至少应指定一个数据库名称，如果有多个数据库，则名称之间用空格隔开。

备份所有数据库

mysqldump –uusername –ppassword --all-databases>filename.sql

需要注意的是，如果使用“--all-databases”参数备份了所有的数据库，那么在还原数据库时，不需要创建数据库并指定要操作的数据库，因为备份文件中包含了CREATE DATABASE语句和USE语句。

例如，使用mysqldump命令备份数据库ems到在D盘backup的文件夹下，语句如下所示。

mysqldump -uroot -p123456 ems>D:/backup/ems_20210610.sql

数据的还原

使用mysql命令还原数据

mysql –uusername –ppassword [dbname] <filename.sql

上述语法格式中，username表示登录的用户名，password表示用户的密码，dbname表示要还原的数据库名称。

下面通过一个案例演示数据的还原，具体操作步骤如下。

（1）创建数据库 由于之前备份的SQL脚本中没有创建数据库的语句，所以需要先创建一个数据库，用于还原数据时选择的数据库，例如，创建数据库emp_backup，创建数据库语句如下。

CREATE DATABASE ems_backup;

（2）还原数据 mysql命令可以读取D:/backup目录下的ems_20210610.sql文件还原数据。使用mysql命令还原数据时，不需要登录MySQL，直接在命令行窗口执行即可，具体语句如下。

mysql -uroot -p123456 ems_backup <D:/backup/ems_20210610.sql

使用source命令还原数据

source filename.sql

 下面通过案例演示使用source命令还原数据，具体步骤如下。

（1）创建用于接收还原数据的数据库，例如，创建数据库emp_backup2，创建数据库语句如下。

CREATE DATABASE ems_backup2;

 （2）使用USE语句选择数据库ems_backup2为默认操作的数据库。

（3）使用source命令将数据文件ems_20210610.sql的数据还原到数据库ems_backup2中，具体SQL语句如下所示。

source D:/backup/ems_20210610.sql

需要注意的是，在命令行窗口中使用mysql命令还原数据时，不需要登录数据库，而使用source命令还原数据时，需要先登录数据库。 

用户管理

MySQL是一个多用户数据库管理系统，MySQL的用户可以大致分为普通用户和root用户。root用户是超级管理员，拥有所有权限，如创建用户、删除用户、管理用户等。普通用户只拥有被授予的指定权限。在之前章节中，都是通过root用户登录数据库进行相关的操作，为了保证数据库的安全，需要对不同用户的操作权限进行合理的管理，让用户只能在指定的权限范围内操作。

user表

在安装MySQL时，会自动创建一个名称为mysql的数据库，该数据库主要用于维护数据库的用户及权限的控制和管理，其中包含的数据表有user、db、host等，其中user表中保存了所有用户信息，用户信息中包含了允许连接到服务器的账号信息以及一些全局级的权限信息，全局级的权限适用于给定服务器中的所有数据库。

本书使用的是MySQL Community Server 8.0.23，该版本的mysql数据库中user 表的字段根据功能大致可以分为4 类，分别是用户字段、权限字段、安全字段和资源控制字段，下面分别进行介绍。

1.用户字段

user表的用户字段存储了用户连接MySQL数据库时需要输入的信息。user表中的用户字段如下表所示。

字段名	数据类型	默认值	说明
Host	char(255)	-	主机名
User	char(32)	-	用户名
authentication_string	text	-	密码

当用户与MySQL服务端建立连接时，MySQL会将用户输入的用户名、主机名、密码和user表用户字段中存储的值进行匹配，只有这3个字段的值都匹配成功，才允许用户与MySQL服务端建立连接。

2.权限字段

user表的权限字段包括Select_priv、Insert_priv、Update_priv等以priv结尾的字段，这些字段决定了用户的权限，包括查询权限、修改权限、关闭服务等权限。user表中的权限字段如下表所示。（由于篇幅原因这里仅展示部分字段）

字段名	数据类型	默认值	说明
Select_priv	enum('N','Y')	'N'	用户是否可以通过SELECT命令查询数据
Insert_priv	enum('N','Y')	'N'	用户是否可以通过INSERT命令插入数据
Update_priv	enum('N','Y')	'N'	用户是否可以通过UPDATE命令修改现有数据

user表对应的权限是针对所有数据库的，并且这些权限字段的数据类型都是ENUM，取值只有'N'或'Y'，其中N表示该用户没有对应权限，Y表示该用户有对应权限，为了安全起见，这些字段的默认值都为N，如果需要更改权限，可以对字段值进行修改。

3.安全字段

user表的安全字段包含安全连接、身份验证和密码相关等字段，主要用于管理用户的安全信息。user表中的安全字段如下表所示。（由于篇幅原因这里仅展示部分字段） 

字段名	数据类型	默认值	说明
x509_subject	blob		包含主题的有效的X509证书
ssl_cipher	blob		ssl标准加密连接的特定密码
x509_issuer	blob		CA签发的有效的X509证书

4.资源控制字段

user表的资源控制字段包含以max_开头的4个字段，这些字段用于限制用户对服务器资源的使用，防止用户登录服务器后的不法操作或不合规范的操作，导致服务器资源的浪费。user表中的资源控制字段如下表所示。 

字段名	数据类型	默认值	说明
max_questions	int	0	每小时允许用户执行查询操作的次数
max_updates	int	0	每小时允许用户执行更新操作的次数
max_connections	int	0	每小时允许用户执行连接操作的次数
max_user_connections	int	0	允许单个用户同时建立连接的数量

创建用户

MySQL中所有用户的信息都保存在mysql.user表中，因此，可以直接利用root用户登录MySQL服务器，以向mysql.user表中插入用户信息的方式创建用户。但是，为了保证数据的安全，并不推荐使用此种方式创建用户。MySQL提供了更安全的CREATE USER语句用于创建用户，下面对使用CREATE USER语句创建用户进行讲解。

使用CREATE USER语句创建新用户时，每创建一个新用户，都会在mysql.user表中添加一条记录，并同时自动修改相应的授权表。

需要注意的是，CREATE USER语句创建的新用户默认情况下只有连接权限。

CREATE USER 'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password']
          [,'username'@'hostname'[IDENTIFIED BY [PASSWORD]'password']]…

username表示新创建用户的名称；

hostname表示主机名,使用'%'作为主机名表示允许用户从任何主机连接，这可能会带来安全风险，因此请确保你了解这样做的后果，并考虑使用更具体的主机名或IP地址来限制访问；

IDENTIFIED BY用于设置用户的密码；

PASSWORD关键字表示使用哈希值设置密码，是可选项，如果密码是一个普通的字符串，就不需要使用PASSWORD关键字；

password表示用户登录时使用的密码，需要用单引号括起来。

CREATE USER语句可以同时创建多个用户，多个用户之间用逗号分隔。

例如，使用CREATE USER语句创建2个新用户，用户名分别为test1和test2、密码分别为123和456，具体SQL语句如下所示。

CREATE USER 'test1'@'localhost' IDENTIFIED BY '123',
'test2'@'localhost' IDENTIFIED BY '456';

以查询用户test1为例，具体SQL语句及查询结果如下。

SELECT host,user,authentication_string ,plugin 
    FROM mysql.user
    WHERE user='test1'\G

 MySQL 8.0之前的版本中，可以使用GRANT语句创建用户，并且在创建用户时对用户授权（授权的相关知识会在下一节讲解）。下面，基于MySQL 5.7.33版本讲解使用GRANT语句创建用户。 GRANT语句创建用户的基本语法格式如下所示。

GRANT privileges ON database.table
      TO 'username'@hostname [IDENTIFIED BY [PASSWORD]'password']
      [ ,'username'@hostname [IDENTIFIED BY [PASSWORD]'password']] …

上述语法格式中，privileges参数表示该用户具有的权限信息，database.table表示新用户的权限范围表，可以在指定的数据库、数据表使用自己的权限，username参数是新用户的名称，hostname参数是主机名，password参数是新用户的密码。

例如，使用GRANT语句创建一个新用户，用户名为test3、密码为789，并授予该用户对数据库ems中的员工表emp有查询权限，具体SQL语句及执行结果如下所示。

GRANT SELECT ON ems.emp TO 'test3'@'localhost' IDENTIFIED BY '789';

 新增用户后，使用SELECT语句查询mysql.user表中的数据，具体SQL语句及执行结果如下。

SELECT host,user,authentication_string ,plugin FROM mysql.user
    WHERE user='test3'\G

删除用户

在MySQL中，通常会创建多个普通用户来管理数据库，但如果发现某些用户已经没必要再使用，就可以将其删除。删除用户可以通过DROP USER语句和DELETE语句完成。

1.使用DROP USER语句删除用户

DROP USER语句与DROP DATABASE语句类似，如果要删除某个用户，只需在DROP USER后面指定要删除的用户信息即可。 

DROP USER 'username'@'hostname'[,'username'@'hostname'];

 上述语法格式中，username表示要删除的用户，hostname表示主机名，DROP USER语句可以同时删除一个或多个用户，多个用户之间用逗号进行分隔。

例如，使用DROP USER语句删除用户test1：

DROP USER 'test1'@'localhost';

通过SELECT语句验证用户是否被删除：

SELECT host,user FROM mysql.user WHERE user='test1';

 2.使用DELETE语句删除用户

DELETE语句不仅可以删除普通表中的数据，还可以删除mysql.user表中的数据，使用该语句删除mysql.user表中的数据时，需要指定表名为mysql.user和要删除的用户信息。 

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

上述语法格式中，mysql.user参数指定要操作的表，WHERE指定条件语句，Host和User都是mysql.user表的字段，这两个字段可以确定唯一的一条记录。

使用DELETE语句删除用户test2：

delete from mysql.user where host = 'localhost' and user='test2';

 通过SELECT语句查询用户test2是否被删除:

SELECT host,user FROM mysql.user WHERE user='test2';

修改用户密码

MySQL中的用户都可以对数据库进行操作，因此管理好每个用户的密码是至关重要的，密码一旦丢失就需要及时修改。MySQL中修改密码的方法主要4种，具体如下。

使用mysqladmin命令修改用户密码

在MySQL的安装目录bin文件夹下，有一个mysqladmin.exe可执行程序，它对应的命令mysqladmin通常用于执行一些管理性的任务（如修改用户密码），以及显示服务器状态等。使用mysqladmin命令修改密码的基本语法格式如下所示。

mysqladmin –u username [–h hostname] –p password new_password

 上述语法格式中，username表示要修改密码的用户名，参数-h用于指定对应的主机名，可以省略不写，默认为localhost，-p后面的password为关键字，用于指定要修改的内容为密码，而不是修改后的密码，new_password为新设置的密码。

使用ALTER USER语句修改用户密码

ALTER USER 账户名IDENTIFIED By new_password;

上述语法格式中，账户名包括用户名和主机名，new_password表示新设置的密码。需要注意的是，使用这种方法修改用户密码，需要执行修改密码操作的用户有修改mysql.user数据表的权限。

使用SET语句修改用户密码

SET PASSWORD=new_password;

 使用UPDATE语句修改用户密码

这种方法就是通过UPDATE语句直接修改mysql.user的数据，需要root用户登录，使用UPDATE语句直接修改对应用户的密码:

UPDATE mysql.user 
SET authentication_string=PASSWORD('new_password')
WHERE User='username' and Host='hostname';

上述语法格式中，new_password为新设置的密码，username为要修改的用户名，hostname为对应的主机名。使用这种方法修改密码后，还需使用FLUSH PRIVILEGES重新加载权限表。需要注意的是，在MySQL 8.0及后续的版本中已经废弃PASSWORD()函数。

通过案例演示使用方法1~方法3修改普通用户的密码。

（1）首先使用root用户连接数据库后创建普通用户ems_test：

CREATE USER 'ems_test'@'localhost' IDENTIFIED BY '123';

 （2）使用mysqladmin命令修改ems_test用户密码：

D:\mysql-8.0.23-winx64\bin>mysqladmin -u ems_test -p password

结果如下： 

C:\Users\Administrator>mysqladmin -u ems_test -p password
Enter password: ***
New password: ***
Confirm new password: ***
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

 （3）使用ALTER USER语句修改用户ems_test的密码，修改之前需要先登录root账户，登陆后执行的SQL语句如下所示。

ALTER USER 'ems_test'@'localhost' IDENTIFIED BY '789';

（4）使用SET语句对用户密码进行修改。例如，修改ems_test用户的登录密码，先用该账户登录，然后再执行下面的sql：

SET PASSWORD='8910';

修改root用户的密码

root用户是超级管理员，拥有很多的权限，如果root用户的密码一旦丢失，就会造成很大的麻烦，针对这种情况，MySQL提供了对应的处理机制，可以通过特殊方法登录到MySQL服务器，然后重新为root用户设置密码，具体步骤如下。 

(1)停止MySQL服务

打开命令行窗口，在窗口中使用net命令停止MySQL服务，具体命令如下。

net stop MySQL80

上述语句中的MySQL80是MySQL服务的名称，即将MySQL安装为操作系统的服务时自定义的服务名称。

(2)使用--skip-grant-tables登录MySQL服务

MySQL服务器中有一个--skip-grant-tables选项，它可以停止MySQL的权限判断，也就是可以跳过密码的输入访问数据库，在命令行窗口中执行如下命令。

mysqld --shared-memory --skip-grant-tables

执行完上述命令后，命令行窗口会进入类似阻塞的状态，此时不要关闭命令行窗口。

(3)登录MySQL服务器

重新打开一个命令行窗口，在新打开的窗口中登录MySQL服务器，具体命令如下。 

mysql -u root

(4)加载权限表

免密登录后，使用命令重新加载权限表，具体语句如下。 

FLUSH PRIVILEGES;

(5)使用ALTER USER语句设置root用户密码

可以通过ALTER USER语句设置root用户的密码，具体语句如下。 

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

 上述步骤执行完，可以使用EXIT或\q命令退出服务器。

(6)启动MySQL服务

关闭刚才打开的2个命令行窗口，重新打开一个命令行窗口，在窗口中使用net命令启动MySQL服务，具体命令如下。

net start MySQL80

 启动MySQL服务后，可以使用root用户的新密码进行登录。至此，便完成了root用户的密码设置。

权限管理

MySQL的权限

在实际项目开发中，为了保证数据的安全，数据库管理员需要为不同层级的操作人员分配不同的权限，限制登录MySQL服务器的用户只能在其权限范围内操作。同时管理员还可以根据不同的情况为用户授予权限或收回权限，从而控制数据操作人员的权限。

MySQL中的权限信息根据其作用范围，分别存储在名称为mysql的数据库中的不同数据表中，当MySQL启动时，会自动加载这些权限信息，并将这些权限信息读取到内存中。mysql数据库中与权限相关的数据表如下表所示。

数据表	显示效果
user	保存用户被授予的全局权限
db	保存用户被授予的数据库权限
tables_priv	保存用户被授予的数据表权限
columns_priv	保存用户被授予的数据列权限
procs_priv	保存用户被授予的存储过程和存储函数权限
proxies_priv	保存用户被授予的代理权限

授予权限 

在前面的章节中，用户登录MySQL后，可以对数据进行增删改查的操作，是因为登录的用户拥有这些权限。MySQL提供了用于为用户授予权限的GRANT语句，其基本语法格式如下。

GRANT 权限类型[(字段列表)][,权限类型[(字段列表)]] 
ON 权限级别
              TO 'username'@'hostname' 
              [,'username'@'hostname'] …
              [WITH with_option]

上述语法格式中，WITH关键字后面的参数with_option的取值五个，具体如下。

（1）GRANT OPTION：将自己的权限授予其他用户。

（2）MAX_QUERIES_PER_HOUR count：设置每小时最多可以执行多少次查询。

（3）MAX_UPDATES_PER_HOUR count：设置每小时最多可以执行多少次更新。

（4）MAX_CONNECTIONS_PER_HOUR count：设置每小时最大的连接数量。

（5）MAX_USER_CONNECTIONS：设置每个用户最多可以同时建立连接的数量。 

例如，对emp_test用户授予数据库ems的员工表emp的SELECT权限，以及对empno和ename字段的插入权限，具体SQL语句如下所示。

GRANT SELECT,INSERT(empno,ename)  ON ems.emp TO 'ems_test'@'localhost';

查看权限 

授权语句执行成功后，可以对用户ems_test的授予权限进行查询，其中表权限可以在mysql.tables_priv中查看，列权限可以在mysql.columns_priv中查看，此处以用户ems_test为例，具体SQL语句及执行结果如下所示。

SELECT db,table_name,table_priv,column_priv
FROM mysql.tables_priv WHERE user='ems_test';

结果如下： 

mysql> SELECT db,table_name,table_priv,column_priv
    -> FROM mysql.tables_priv WHERE user='ems_test';
+-----+------------+------------+-------------+
| db  | table_name | table_priv | column_priv |
+-----+------------+------------+-------------+
| ems | emp        | Select     | Insert      |
+-----+------------+------------+-------------+
1 row in set (0.00 sec)

 查询列权限：

SELECT db,table_name,column_name,column_priv
FROM mysql.columns_priv WHERE user='ems_test';

结果如下：

mysql> SELECT db,table_name,column_name,column_priv
    -> FROM mysql.columns_priv WHERE user='ems_test';
+-----+------------+-------------+-------------+
| db  | table_name | column_name | column_priv |
+-----+------------+-------------+-------------+
| ems | emp        | empno       | Insert      |
| ems | emp        | ename       | Insert      |
+-----+------------+-------------+-------------+
2 rows in set (0.00 sec)

 从执行结果可以得出，ems_test用户对数据库ems的员工表emp有查询权限，对员工表emp中的empno和ename字段有插入权限，说明使用GRANT语句成功给用户授予了权限。

除了上述的权限查看方式，还可以使用SHOW GRANTS语句查看用户权限，其基本语法格式如下所示。

SHOW GRANTS FOR 'username'@'hostname';

使用SHOW GRANTS语句查看用户ems_test的权限信息：

SHOW GRANTS FOR 'ems_test'@'localhost';

 结果如下：

mysql> SHOW GRANTS FOR 'ems_test'@'localhost';
+----------------------------------------------------------------------------------+
| Grants for ems_test@localhost                                                    |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `ems_test`@`localhost`                                     |
| GRANT SELECT, INSERT (`empno`, `ename`) ON `ems`.`emp` TO `ems_test`@`localhost` |
+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

收回权限

为了保证数据库的安全，对于用户一些不必要的权限应该及时收回。MySQL提供了REVOKE语句用于收回指定用户的指定权限，其基本语法格式如下所示。

REVOKE  权限类型[(字段列表)][,权限类型[(字段列表)]]  
ON 权限级别 
FROM 'username'@'hostname'[,'username'@'hostname'] …

 上述语法格式中权限类型表示收回的权限类型，字段列表表示权限作用的字段，如果不指定字段，表示作用于整个数据表。

例如，对ems_test用户在ems.emp表中字段empno和ename的INSERT权限进行收回：

REVOKE INSERT(empno,ename) ON ems.emp FROM 'ems_test'@'localhost';

从mysql.columns_priv中查看ems_test用户的列权限，验证ems_test用户的相应权限是否收回成功：

SELECT db,table_name,column_name,column_priv
FROM mysql.columns_priv WHERE user='ems_test';

 当用户拥有的权限比较多，使用上述的收回方式就比较繁琐，为此MySQL提供了一次性收回所有权限的功能：

REVOKE ALL PRIVILEGES, GRANT OPTION 
FROM 'username'@'hostname'[,'username'@'hostname'] …

例如，收回用户ems_test的所有权限：

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'ems_test'@'localhost';

 使用SHOW GRANTS语句查看用户ems_test的权限信息：

mysql> SHOW GRANTS FOR 'ems_test'@'localhost';
+----------------------------------------------+
| Grants for ems_test@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `ems_test`@`localhost` |
+----------------------------------------------+
1 row in set (0.00 sec)

usage：该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。

上机实践：图书管理系统数据库的管理

实践需求1：对bms数据库中的所有数据进行备份。

动手实践1：使用mysqldump语句将bms数据库中的所有数据备份到D盘backup文件夹下，具体的SQL语句如下所示。

mysqldump -uroot -proot bms>D:/backup/bms_20210528.sql

 实践需求2：创建一个账户，并为该账户赋予以下权限：允许该账户对数据库bms中所有表执行插入、修改和查询操作。

动手实践2：

（1）创建名称为user2的账户，并设置账户的密码为user2pw，具体的SQL语句如下所示。

CREATE USER 'user2'@'localhost' IDENTIFIED BY 'user2pw';

 （2）为user2赋予对数据库bms所有表的INSERT、UPDATE、SELECT权限，具体的SQL语句如下所示。

GRANT INSERT,UPDATE,SELECT ON  bms.* TO 'user2'@'localhost';