【文档说明】MySQL数据库应用实战教程第9章-MySQL管理课件.pptx，共(67)页，1.007 MB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-6056.html

以下为本文档部分文字说明：

刘刚苑超影编著MySQL数据库应用实战教程（慕课版）第9章MySQL管理用户管理■权限管理■表空间管理（InnoDB）■备份与还原■主从同步配置■小结■本章介绍MySQL管理，包括用户管理、权限管理、表空间管理、备份与还原，以及主从同步配置。9.1用户管理MySQL数据库的用户管理包括创建用户、修

改用户、删除用户等操作。9.1.1创建用户#创建用户，任何ip的客户端都可以访问createuser'xiaogang'@'%'identifiedby'123456';#创建用户，只有本地的客户端才可以访问createuser'xiaogang'@'localh

ost'identifiedby'123456';#创建用户，只有指定的192.168.1.90这个ip才可以访问createuser'xiaogang'@'192.168.1.90'identifi

edby'123456';MySQL数据库使用create关键字来创建用户，在@符号前面是用户的名称，在@符号后面是限制访问数据库客户端的ip。（1）使用%，则说明任何ip的客户端都可以连接到这个数据库上进行相关操作。（2）使用l

ocalhost，则说明只能允许数据库服务器的本地客户端连接到这个数据库上进行相关操作。（3）使用指定的ip，如192.168.1.90，则只有这个ip的客户端才可以访问，为了安全起见，往往会设置指定的ip来连接数据库

。采用关键字identifiedby来指定密码，可以根据自己的需要设置密码的复杂度。创建的用户是允许重复的，如果要查询数据库有哪些用户以及访问数据库的客户端ip权限，则可以使用以下命令。SELECTuser,hostFROMmysql.user;9.1.2修改用户MySQL5.7版

本的数据库采用以下命令来修改用户的密码。updatemysql.usersetauthentication_string=password('新密码')whereuser='xiaogang'andhost='localhost';用户客户端访问数据库的ip限制，可以采用以下命令进行修改。

updatemysql.usersethost='192.168.1.100'whereuser='xiaoying';创建好的用户如果要修改密码，要更新MySQL的用户表，在5.7版本的MySQL中，密码存放在authenticatio

n_string中，需要对它进行修改。在修改密码的时候，用户需要获得reload权限，否则使用flushprivileges刷新MySQL的系统权限相关表会报错，可以使用以下命令进行授权。GRANTreloadON*.*to'root'@'%';如果不对用户授予reload权限，也可以在更

新密码之后重启MySQL服务，不使用flushprivileges刷新MySQL的系统权限相关表。9.1.3删除用户删除用户使用以下命令。DELETEFROMmysql.userWHEREuser='

xiaoying'andhost='localhost';9.2权限管理MySQL的用户权限管理主要包括以下几个方面。（1）设置用户拥有访问数据库、表的权限。（2）设置用户拥有的操作权限（SELECT、CREATE、UPDATE、DELETE等）。（3）设置用户使用指定的IP

访问权限。（4）设置用户是否可以给其他用户授权的权限。9.2.1权限授权语法授予权限：GRANTALLPRIVILEGESON*.*TO'xiaogang'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION

;撤销权限：REVOKEALLPRIVILEGESON*.*FROM'xiaogang'@'%';（1）GRANT：授予权限的关键字。（2）REVOKE：撤销权限的关键字。（3）ALLPRIVILEGES：将所有权限授予用户，也可指定具体的权限，如增删改查（SE

LECT、CREATE、DROP）等权限。（4）ON：权限对哪些数据库和表生效，格式为数据库名.表名，“*.*”是指对所有数据库和数据表授权。（5）TO：权限授予用户，格式“用户名”@“登录IP或域名”，%没有限制，在任何主机都可以登录，也可以指定ip或者ip段，“

192.168.1.%”，用户只能在192.168.1IP段登录。（6）FROM：从用户那里回收权限。（7）IDENTIFIEDBY：指定用户的登录密码。（8）WITHGRANTOPTION：允许用户将自己的权限授予其他用户。可

以使用GRANT给用户添加多个权限，给用户添加了一个SELECT权限后，又给用户添加了一个INSERT权限，该用户就同时拥有了SELECT和INSERT权限。MySQL数据库权限表如表9.1所示。表9.1MySQL数据库权限表续表对于数

据库表、数据库列以及存储过程应该给予什么权限，官方文档指导说明如表9.2所示。表9.2权限配置9.2.2账户权限体系账户权限体系分为服务级用户权限、数据库级用户权限、表级用户权限、字段级用户权限，不同的用户级别拥有不

同的权限。1．服务级用户权限服务级用户权限拥有对所有数据库进行操作的权限，如同root用户一样，可以删除所有数据库以及表，权限存储在mysql.user表中。GRANTALLON*.*和REVOKEALL

ON*.*只授予和撤销全局权限，*.*表示所有数据库、所有数据表。#授予用户xiaogang为服务级用户权限，具有grant权限，可以创建其他用户的权限GRANTALLPRIVILEGESON*.*TO'xiaogang'@'%'IDENTIFIEDBY'123456'WITHGR

ANTOPTION;2．数据库级用户权限在具有数据库级用户权限的数据库中可以进行增删改查等操作（依据分配的权限），权限存储在mysql.db和mysql.host表中，GRANTALLONdb_name.*和REVOKEALLO

Ndb_name.*只授予和撤销数据库级权限，db_name.*表示指定的数据库。#授予用户xiaogang数据库级用户权限，可以操作staff数据库的所有权限GRANTALLPRIVILEGESONstaff.*TO'xiaogang'@'%'IDENTIFIEDBY'1234

56'WITHGRANTOPTION;3．表级用户权限表级用户权限即对给定表中的所有列进行操作的权限（依据分配的权限），权限存储在mysql.tables_priv表中，GRANTALLONdb_name.tbl_name和REVOKEALLONdb_name.tbl_name

只授予和撤销表权限，db_name.tbl_name表示指定的数据库和指定的表。#授予用户xiaogang表级用户权限，可以对staff数据库中的employee表进行操作的所有权限GRANTALLPRIVILEGESONstaff.em

ployeeTO'xiaogang'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;4．字段级用户权限字段级用户权限是对给定表中的单一列操作的权限（依据分配的权限），权限

存储在mysql.columns_priv表中，当使用REVOKE时，必须指定与被授权列相同的列。字段级用户权限使用频率低，每次访问时都需要权限校验，效率低下。9.2.3权限授予原则在进行信息安全检查或者信息系统安全等级保护测评的过程中，需要对数据库进行安全扫

描，如果权限授予有问题，就会产生漏洞。下面列举一些常见的数据库漏洞。（1）super权限授予了除root外的用户。风险等级：中。产生原因：super权限授予了除root外的用户。漏洞描述：该策略检测super的权限是否授予了除root外的用户。拥有super权限的用户可以终止其他用户的m

ySQL进程。在一个安全的环境中，super权限只授予root用户。修复建议：回收授予的除root外的用户权限。命令为revokesuperon*.*from'{user}'@'{host}';其中参数user为需要修改的用户

名，host为用户所在主机名。（2）createuser权限授予了除root外的用户。风险等级：中。产生原因：createuser的权限授予了除root外的用户。漏洞描述：该策略检测createuser的权限是否授予了除root外的用户。拥有创建用户权限的用户可以在数据库

服务器中创建用户，并且可以把对数据库操作的权限赋予新创建的用户，如select、insert、update、delete，也就是说得到了创建用户权限的用户可以间接控制数据库服务器。在一个安全的环境中，createuser的权

限只授予root用户。修复建议：回收授予的除root外的用户权限。命令为revokecreateuseron*.*from'{user}'@'{host}';其中参数user为需要修改的用户名，host为用户所在主机名。（3）存在file全局权

限用户。风险等级：中。产生原因：存在file全局权限的普通用户。漏洞描述：该策略检测是否存在file全局权限的普通用户。如用户被授予file全局权限，则可以通过LOADDATAINFILE和SELECT...INTOOUTFILE语句读写服务器上的任何文件。从数据库安全

角度考虑，只有管理员用户才被授予file全局权限。修复建议：通过命令updatemysql.usersetfile_priv='N'whereuser='{user}'andhost='{host}'撤销用户的file全局权限；其中参数use

r为需要修改的用户名，host为用户所在主机名。（4）存在drop全局权限用户。风险等级：中。产生原因：存在drop全局权限的普通用户。漏洞描述：该策略检测是否存在drop全局权限的普通用户。如用户被授予drop全局权限，则可能会

有重要数据库或表被删除的危险。从数据库安全角度考虑，只有管理员用户才被授予drop全局权限。修复建议：通过命令dropuser'{user}'@'{host}'删除拥有drop全局权限的普通用户；或通过命令updatem

ysql.usersetdrop_priv='N'whereuser='{user}'andhost='{host}'撤销普通用户的drop全局权限；其中参数user为需要修改的用户名，host为用户所在主机名。（5）存在grant数据库级权限用户。风

险等级：中。产生原因：存在grant数据库级权限的普通用户。漏洞描述：该策略检测是否存在grant数据库级权限的普通用户。grant数据库级权限允许用户在全局范围内授予其他用户权限。在某些版本中，具有gra

nt权限的用户可以重置其他任何用户的密码，这可能会导致一个用户通过修改其他所有用户密码而控制整个数据。修复建议：通过命令updatemysql.dbsetgrant_priv='N'whereuser='{user}'andhost='{host}'撤销普通用户对grant的数据

库级权限；其中参数user为需要修改的用户名，host为用户所在主机名。（6）存在drop数据库级权限用户。风险等级：中。产生原因：存在drop数据库级权限的普通用户。漏洞描述：该策略检测是否存在drop数据库级权限的普通用户。如用户被授予drop数据库

级权限，则可能会有重要数据库或表被删除的危险。从数据库安全角度考虑，只有管理员用户才被授予drop数据库级权限。修复建议：通过命令updatemysql.dbsetdrop_priv='N'whereuser='{

user}'andhost='{host}'撤销普通用户对drop的数据库级权限；其中参数user为需要修改的用户名，host为用户所在主机名。（7）存在process全局权限用户。风险等级：中。产生原因：存在process权

限的用户。漏洞描述：该策略检测是否存在process权限的用户。process是管理权限，process权限能被用来查看当前执行查询的明文文本，包括设定或改变密码的查询。从数据库安全角度考虑，只有管理员用户才可以被授予process权限。修复建议：通过命令updatemysql

.usersetprocess_priv='N'whereuser='{user}'andhost='{host}'撤销用户的process权限；其中参数user为需要修改的用户名，host为用户所在主机名。

（8）从任意主机登录的用户。风险等级：中。产生原因：存在从任意主机都能登录数据库的用户。漏洞描述：该策略检测是否存在从任意主机都能登录数据库的用户。当用户请求连接时，服务器会用user表中的host、user、password3个字段进行身份验证，其中hos

t为用户所在主机，如果user表中某一用户的host字段值为空或者为通配符“%”，则意味着该用户可以从任意主机登录数据库，从安全角度来考虑这是不允许的。修复建议：通过命令updatemysql.usersethost='{newhost}'wh

ereuser='{username}'andhostin('','%');flushprivileges修改user的host字段；其中参数newhost表示指定的主机ip或者主机名，username表示需要修改的用户名。（9）存在拥有replications

lave权限的普通用户。风险等级：低。产生原因：存在拥有replicationslave权限的普通用户。漏洞描述：该策略检测是否存在拥有replicationslave权限的普通用户。如用户被授予replicationslave权限，则可以查看从服务器，从主服务器读取二进制

日志。因此，建议该权限只授权给真正需要该权限的用户。修复建议：通过命令：dropuser'{user}'@'{host}'删除拥有replicationslave权限的普通用户；或通过命令updatemysql.usersetrepl_slave_priv

='N'whereuser='{user}'andhost='{host}'撤销普通用户的replicationslave权限；其中参数user为需要修改的用户名，host为用户所在主机名。（10）存在拥有showdatabase权限的普通用户。风险等级：低。产生原因：存在拥有showda

tabase权限的普通用户。漏洞描述：该策略检测是否存在拥有showdatabase权限的普通用户。如用户被授予showdatabase权限，则可以查看数据库服务器中所有的数据库，得到数据库服务器的敏感信息。因此，建议该

权限只授权给真正需要该权限的用户。修复建议：通过命令dropuser'{user}'@'{host}'删除拥有showdatabase权限的普通用户，或通过命令updatemysql.usersetshow_db_priv='N'whereuser='{user}'andhost='{ho

st}'撤销普通用户的showdatabase权限；其中参数user为需要修改的用户名，host为用户所在主机名从以上10个MySQL数据库漏洞，可以总结出以下权限授予原则。（1）不要授予普通用户super权限、createuser权限、drop全局权

限、grant数据库级权限等这些管理员所拥有的权限。（2）授予普通用户最小权限，权限够用就行，如用户需要增删改查的权限，就不要授予其删除用户的权限。（3）数据库用户可以限制登录的主机，可以指定IP或者IP段。（4）用户的密码要足够复杂，可以用字母、数字、

特殊字符、大小写组成复杂密码。（5）清理不用的数据库和用户，减少数据库使用空间以及不用的用户。9.3表空间管理（InnoDB）MySQL数据库采用InnoDB存储引擎，就会有两种表空间可以使用：一种是共享表空间；另外

一种是独占表空间。（1）共享表空间。共享表空间是指一个数据库的所有表数据以及索引文件全部放在一个文件中。共享表空间的默认文件路径在data目录下，默认的文件名为ibdata1，初始化为10M，如图9.1所示。图9.1共享表空间文件共享表空间可以

将表空间分成多个文件存放到各个磁盘上，数据和文件放在一起方便管理，同时数据和文件都放到一个文件里会产生一个很大的文件，对一个表做了大量删除操作后其表空间中将会有大量的空隙，这样会浪费空间。（2）独占表空间。独占表空间是将每个表都以独立的文件方式来进行存储，每个表都有一个f

rm、ibd表文件。frm文件保存了每个表的元数据，包括表结构的定义等；ibd文件存放该表的数据和索引文件，如图9.2所示。图9.2独占表空间文件独占表空间使每一个数据库的每一个表都有自己独立的表空间，每个表的数据和索引都会存放在自己的表空间中，可以实现

单表在不同的数据库中移动。与共享表空间相比，使用独占表空间的效率以及性能会更高一点。（3）开启表空间。默认情况下，数据库使用的表空间为共享表空间，但是可以通过参数innodb_file_per_table来将其设置为独占表空间，如果值为OFF说明所使用的是独占表空间。

mysql>showvariableslike"innodb_file_per_table";+----------------+-----+|Variable_name|Value|+----------------+-----+|innodb_file_per_table|ON

|+----------------+-----+1rowinset,1warning(0.00sec)在配置文件my.ini或者my.cnf里配置innodb_file_per_table=1是否使用共享以及独占表空间，1为使用独占表空间，0为使用共享表空间。（4）查看

数据库共享表空间。使用showvariableslike'innodb_data%';查看共享表空间的大小，ibdata1的默认大小为10M，超过10M可以自动扩展。innodb_data_file_path可以进行多路径存储设

置，innodb_data_file_path=/data1/db1/ibdata1:100M:autoextend;/data2/db2/ibdata2:100M:autoextend放在不同的磁盘中，可以平均磁盘负载，提高数据库性能。mysql>showvariableslik

e'innodb_data%';+----------------+-------------------+|Variable_name|Value|+----------------+---------

----------+|innodb_data_file_path|ibdata1:12M:autoextend||innodb_data_home_dir||+----------------+-------------------+2rowsinset,

1warning(0.00sec)（5）查看所有数据库占用空间大小。可以查看各个数据库所占空间的大小以及索引文件的大小。通过统计数据库的占用大小，可以看到这个数据库的空间使用以及索引文件的大小，如员工管理系统数据

库staff的空间占用是300.89MB，索引文件是0.00MB。（6）查看指定数据库占用空间大小。可以查看指定数据库的各个表所占空间大小以及表索引文件的大小。通过查看指定数据库空间的使用，可以看到各个表的空间使用情况和索引情况，如员工管理系统数据库staff的员工表emplo

yee的空间占用是111.64MB，索引文件大小为0.00MB，而部门表dept的空间占用是0.01MB，索引文件大小为0.00MB。9.4备份与还原MySQL数据库备份与还原也是在项目上线后必须要做的一件

事。数据库运行过程中，很有可能遇到停电、磁盘损坏、数据库服务器宕机、自然灾害、黑客攻击等问题，这些都有可能使数据丢失，那么对数据库的备份就显得格外重要了，定期备份可以防止数据丢失。9.4.1备份数据MySQL数据库备份可以分为热备份、温备

份、冷备份。热备份是指当数据库进行备份时，数据库的读写操作不受影响；温备份是指当数据库进行备份时，数据库的读操作可以进行，但是不能进行写操作；冷备份是指当数据库进行备份时，数据库不可以进行读写操作。备份的方式可以分为物理备份和逻辑备份。物理备份是直接拷贝数据库文件，包括数据、二进制日志、Inno

DB事务日志、代码（存储过程、存储函数、触发器、事件调度器）、服务器配置文件。如果数据库是独占表空间，可以直接拷贝要备份的数据库文件，如备份员工管理系统staff，可以直接拷贝staff整个文件夹如图9.3所示；如果是共享表空间，需要拷贝数据文件ibdata1、日志文件ib_logfi

le，如图9.4所示。图9.3独占表空间数据库物理备份图9.4共享表空间数据库物理备份逻辑备份可以采用MySQL数据库自带的备份工具mysqldump，它可以备份成指定的文本文件或者可执行SQL脚本文件；或者用第三方的xtrabackup备份，它是一款非常强大的InnoDB/XtraDB热备工具

，支持完全备份、增量备份。9.4.2还原数据通过物理备份方式进行备份的数据库，可以将备份出来的文件拷贝到数据库安装的data目录下，如D:\ProgramFiles\MySQL\mysql-5.7.20-winx64\data，来还原数据库。图9.5数据库备份结果通过使用MySQ

L数据库自带的mysqldump备份工具进行备份的数据库，有多种方式进行还原，可以使用MySQL客户端还原备份出来的sql文件，也可以使用命令来进行还原，如下所示。mysql>USEstaffmysql>sourceE:/mysql_bak/201802/staff_20180201.sql;9.

5主从同步配置数据库在使用过程中，为了保证数据的安全性，往往会进行备份操作，这时就可以使用主从数据库同步配置，将主数据库中的数据同步到从数据库里，以达到备份的目的。当数据库读写操作频繁，用户访问量较大时，让主数据库进行写

入操作，从数据库进行读取操作，以实现数据库读写分离操作，可以缓解数据库的压力。主从同步配置需要两个MySQL数据库，它们最好分布在两台服务器上。下面学习在Windows操作系统上对数据库进行主从同步配置。9.5.1主数据库配置主数据库配置需要在my.ini配置文件中添加参数、分配用于同步的用户、查

找同步的二进制日志文件和位置。（1）打开MySQL数据库的my.ini配置文件，添加数据库唯一标识server-id（主数据库和从数据库需要不一致），开启log-bin日志以及需要同步的数据库binlog-do-db，binlog-ignore-db用来配置不需要同步的

数据库。（2）查看log-bin日志文件是否开启成功，只有log-bin等于ON，文件开启成功，才可以进行同步操作。（3）分配用于同步的账号backup，密码是123456。（4）查看同步的二进制日志文件名称mysql-bin.000002和位置437，用于在进行从数

据库同步配置时使用。9.5.2从数据库配置从数据库配置需要在my.ini配置文件中添加参数、配置同步的主数据库、开启同步的从数据库。（1）打开MySQL数据库的my.ini配置文件，添加数据库唯一标识server-

id（主数据库和从数据库需要不一致），开启log-bin日志以及需要同步的数据库replicate-do-db，并把从数据库设置为只读数据库。（2）配置同步的数据库，MASTER_HOST是主数据ip，MASTER_PORT是主数据库端口号，MASTER_USER是主数据库分配的同步账号，MAST

ER_LOG_FILE是主数据库同步的二进制日志文件，MASTER_LOG_POS是同步文件的位置。（3）开启从数据库同步STARTSLAVE。（4）查看从数据库同步状态SHOWSLAVESTATUS，查看同步的二进制日志

文件名称mysql_x0002_bin.000002和位置437，Slave_IO_Running等于Yes和Slave_SQL_Running等于Yes说明同步配置成功，可以用于主从数据库同步。（5）在主数据库上创建要

同步的数据库db_test，可以在从数据库中使用SHOWDATABASES命令查看到其已经同步到了从数据库里，进一步说明主从数据库同步配置成功。9.6小结本章讲解了MySQL数据库的管理功能，数据库的用户管理

，如何创建用户、修改用户以及删除用户；数据库的权限管理，权限授权语法、账户权限体系以及权限授予原则；表空间管理，共享表空间和独占表空间的使用；备份与还原管理，如何备份数据库以及还原数据库；主从同步配置，用

来进行数据库备份操作或者数据库读写分离操作。