《数据库应用技术项目化教程》第九章数据库高级管理

数据备份恢复用户管理权限管理第九章数据库高级管理MySQL提供了一个mysqldump命令，它可以实现数据的备份。mysqldump命令可以备份单个数据库、多个数据库、所有数据库或指定数据库中的指定1个（或多个）数据表。

1）备份单个数据库mysqldump命令备份数据库的语法格式如下：mysqldump–uusername–ppassword数据库名称[数据表1名称[数据表2名称…]]>filename.sql9.1数据库的备份与恢复9.1.1数据的备份备份数据库

jwxt,备份文件名c:\jwxtbak.sql，SQL语句如下：mysqldump–uroot–p123456

jwxt

>

c:\jwxtbak.sql其中：登录用户名root，登录密码123456，需要备份的数据库名称jwxt需要注意的是，在使用mysqldump命令备份数据库时，直接在DOS命令行窗口执行该命令即可，不需要登录到MySQL数据库。9.1数据库的备份与恢复9.1.1数据的备份实例9-1备份结束后，打开c:\jwxtbak.sql文件（可用记事本等程序打开），内容如下：9.1数据库的备份与恢复9.1.1数据的备份备份数据库jwxt中的数据表

tb_bjdmb和tb_xsxxb,备份文件名c:\jwxt_bjdmb_bak.sql，SQL语句如下：mysqldump–uroot–p123456

jwxttb_bjdmbtb_xsxxb

>

c:\jwxt_bjdmb_bak.sql其中：登录用户名root，登录密码123456，需要备份的数据库名称jwxt，需要备份的数据表名称tb_bjdmb、tb_xsxxb。9.1数据库的备份与恢复9.1.1数据的备份实例9-23）备份所有数据库使用mysqldump命令备份所有数据库时，只需在该命令后使用“--all-databases”参数即可，其语法格式如下：mysqldump–uusername–ppassword--all-databases

>filename.sql需要注意的是，如果使用“--all-databases”参数备份了所有的数据库，那么在恢复数据库时，不需要创建数据库并指定要操作的数据库，因为，对应的备份文件中包含了CREATEDATABASE语句和USE语句。9.1数据库的备份与恢复9.1.1数据的备份mysql–uusername–ppassword[dbname]<filename.sqlmysql命令恢复数据数据库的语法格式如下：上述语法格式中，username表示登录用户名，password表示用户密码，dbname表示要恢复的数据库名称，如果使用mysqldump命令备份的filename.sql文件中包含创建数据库的语句，则不需要指定数据库；反之，则需要先创建需恢复的数据库后，再进行恢复。9.1数据库的备份与恢复9.1.2数据的恢复正常情况下，数据库名称是不能自动恢复的，因此在恢复数据之前必须先创建数据库。接下来通过一个案例来学习数据的恢复，具体操作步骤如下：1）删除数据库jwxtDROPDATABASEjwxt;9.1数据库的备份与恢复9.1.2数据的恢复实例9-3数据库删除完成，使用SHOWDATABASES语句查询数据库，查询结果如下：9.1数据库的备份与恢复9.1.2数据的恢复2）创建数据库由于数据库库名称是不能恢复的，因此先要创建一个数据库jwxt，具体语句如下：CREATEDATABASEjwxt;上述语句执行成功后，接下来就可以恢复数据库中的数据。9.1数据库的备份与恢复9.1.2数据的恢复3）恢复数据mysql-uroot-pjwxt<c:/jwxtbak.sqlEnterpassword:******使用mysql语句恢复c:\jwxtbak.sql文件，具体语句如下：上述语句执行成功后，数据库中的数据就会被恢复。此处没有指定登录用户密码，所以在登录时需要输入密码（显示的密码以*代替）9.1数据库的备份与恢复9.1.2数据的恢复

4）查看数据为了验证数据已经恢复成功，可以使用SELECT语句查询数据库jwxt中学生信息表tb_xsxxb的数据，查询结果如下：9.1数据库的备份与恢复9.1.2数据的恢复以上恢复方式只是其中的一种，我们还可以登录到MySQL数据库，使用source命令来恢复数据，source命令恢复数据的语法格式如下：source命令的语法格式比较简单，只需要在导人文件的时候指定文件名字为全路径名称即可。按照该语句执行，效果和在命令行执行mysql命令是一样的，这里不再详述。9.1数据库的备份与恢复use数据库名称;source[盘符:\]filename.sql注意：执行此语句时，不要加结束符如;9.1.2数据的恢复在安装MySQL时，会自动安装一个名为mysql的数据库，该数据库中的表都是权限表，如user、db、host、tables_priv、column_priv和procs_priv，其中user表是最重要的一个权限表，它记录了允许连接到服务器的账号信息以及一些全局级的权限信息，通过操作该表就可以对这些信息进行修改。9.2用户管理9.2.1user表使用如下SQL语句，可以查看已有用户信息：usemysql;select

*fromuser;提示：user表的主要字段有：Host（主机）、User（用户名）、Select_priv（查询权限）、Insert_priv

（插入权限）、Update_priv

（更新权限）、Delete_priv

（删除权限）、Create_priv（创建库或表权限）、Drop_priv（删除库或表权限）、Create_view_priv（创建视图权限）、Show_view_priv（查看视图权限）、Trigger_priv（触发器权限）、authentication_string（密码）、password_expired（密码是否过期）、account_locked（用户是否锁定）等9.2用户管理9.2.1user表

1）使用CREATEUSER语句创建用户

使用CREATEUSER语句创建新用户时，服务器会自动修改相应的授权表，但需要注意的是，该语句创建的新用户是没有任何权限的。CREATEUSER语句创建用户的语法格式如下：CREATEUSER'username'@'hostname'[IDENTIFIEDBY[password]'password'][,'username'@'hostname'[IDENTIFIEDBY[password]'password']]…创建新用户有三种方式：9.2用户管理9.2.2创建新用户使用语句CREATEUSER创建一个数据库jwxt的新用户，用户名为user1，密码为abc1,首先使用USE语句，选择数据库jwxt后，再执行语句如下:CREATEUSER'user1'@'localhost'IDENTIFIEDBY'abc1';语句执行成功后，使用USE

mysql切换到系统数据库mysql，执行如下语句查看用户是否创建成功（authentication_string字段为用户密码加密后的结果）：9.2用户管理实例9-4

2）使用GRANT语句创建用户GRANT语句不仅可以创建新用户，还可以对用户进行授权（将在后面讲解），该语句会自动加载权限表，不需要手动刷新，而且安全、准确、错误少，因此，使用GRANT语句是创建用户最常用的方法。GRANT语句创建用户的语法格式如下：9.2用户管理9.2.2创建新用户GRANTpriv_typeONdatabase.tableTOuser[IDENTIFIEDBY[PASSWORD]'password']使用GRANT语句创建一个新用户，用户名为user2，密码为abc2，并授予该用户对学生信息表tb_xsxxb表有查询权限（SELECT权限），GRANT语句如下：GRANTSELECTONjwxt.tb_xsxxbTO'user2'@'localhost'IDENTIFIEDBY'abc2';语句执行成功后，可以查看用户是否创建成功：9.2用户管理实例9-5

3）使用INSERT语句创建用户

不管是CREATEUSER语句还是GRANT语句，在创建用户时，实际上都是在user表中添加一条新的记录，因此，也可以使用INSERT语句直接在该表中添加一个用户。INSERT语句创建用户的语法格式如下：9.2用户管理9.2.2创建新用户INSERTINTOmysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES(hostname,username,PASSWORD(password),'','','');使用INSERT语句在mysql.user表中创建一个新用户，用户名为user3，密码为abc3,INSERT语句如下:INSERTINTOmysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost','user3',PASSWORD('abc3'),'','','');语句执行成功后，可以查看用户是否创建成功：9.2用户管理实例9-6由于INSERT语句没有刷新权限表的功能，因此，mythird用户暂时是不能使用的，为了让当前用户生效，还需要手动刷新当前的权限表或重新启动MySQL服务，刷新权限表的语句如下：注意:FLUSHPRIVILEGES;执行成功后，就可以使用user3用户登录MySQL数据库。9.2用户管理

1）使用DROPUSER语句删除用户

如果要删除某个用户，只需在DROPUSER后面指定要删除的用户信息即可。DROPUSER语句删除用户的语法格式如下：删除用户有两种方式，接下来将针对这两种方式进行详细地讲解。上述语法格式中，username标示要删除的用户hostname表示主机名，DROPUSER语句可以同时删除一个或多个用户，多个用户之间用逗号隔开。值得注意的是，使用DROPUSER语句来删除用户时，必须拥有DROPUSER的权限。9.2用户管理9.2.3删除普通用户

DROPUSER'username'@'hostname'[,'username'@'hostname'];DROPUSER'user3'@'localhost';root用户登陆数据库服务器，使用DROPUSER语句删除用户user3，SQL语句如下:

可以通过SELECT语句验证用户是否删除成功：9.2用户管理实例9-7

2）使用DELETE语句删除用户DELETE语句不仅可以删除普通表中的数据，还可以删除user表中的数据，使用该语句删除user表中的数据时，只需指定表名为mysql.user，以及要删除的用户信息即可。同样的，在使用DELETE语句时必须拥有对mysql.user表的DELETE权限。DELETE语句的语法格式如下：上述语法格式中，mysql.user参数指定要操作的表，WHERE指定条件语句，Host和User都是mysql.user表的字段，这两个字段可以确定唯一的一条记录。9.2用户管理DELETEFROMmysql.userWHEREHOST='hostname'ANDUSER='username';DELETEFROMmysql.userWHEREHost='localhost'ANDUser='user2';root用户登陆数据库服务器，使用DELETE语句删除用户user2，SQL语句如下：可以通过SELECT语句验证用户是否删除成功：9.2用户管理实例9-81）修改用户名

修改已有用户user1的用户名称为user1_1，SQL语句如下：RENAMEUSER'user1'@'localhost'TO'user1_1'@'localhost';9.2用户管理9.2.4修改用户信息实例9-9mysqladmin–u用户名–p原密码password新密码;2）使用mysqladmin命令修改用户密码在命令行窗口，使用mysqladmin命令，将root用户的密码修改为ABC，SQL语句如下:该语句执行完毕，root用户的密码被修改，下次登录时使用新的密码。注意：MySQL密码区别英文大小写。9.2用户管理9.2.4修改用户信息

3）使用UPDATE语句修改用户密码由于所有的用户信息都存放在mysql.user表中，因此，只要root用户登录到MySQL服务器，使用UPDATE语句就可以直接用户密码。UPDATE语句修改密码的语法格式如下：UPDATEmysql.usersetPassword=PASSWORD('new_password')WHEREUser='username'andHost='hostname';9.2用户管理9.2.4修改用户信息

4）使用SET语句修改用户的密码

用户登陆到MySQL服务器后，还可以通过SET语句来修改自己的密码。SET语句修改密码的语法格式如下：SETPASSWORD=PASSWORD('new_password');使用user1用户登录到MySQL服务器，使用SET语句将user1用户的密码修改为123456，SET语句如下:9.2用户管理9.2.4修改用户信息

实例9-10

MySqL数据库中有多种类型的权限，这些权限信息被存储在MySQL数据库的user、db、host、tables_priv、column_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库中的权限信息读取到内存中。9.3权限管理9.3.1MySQL的权限类型SHOWGRANTSFOR'username'@'hostname';

从上述语法格式可以看出，SHOWGRANTS语法格式比较简单，只需要指定查询的用户名和主机名即可。为了方便查询用户的权限信息，MySQL还提供了一个SHOWGRANTS语句。SHOWGRANTS的语法格式如下：9.3权限管理9.3.2权限查询使用SHOWGRANTS语句查看用户root的权限信息，语句执行结果如下：root用户拥有所有权限，并且可以为其他用户赋予权限。9.3权限管理实例9-11

数据库中的用户拥有不同的权限，合理的授权可以保证数据库的安全。在MySQL中提供了一个GRANT语句，该语句可以为用户授权。上述语法格式中，privileges表示权限类型，columns参数表示权限作用于某一列，该参数可以省略不写，此时权限作用于整个表，username表示用户名，hostname表示主机名，IDENTIFIEDBY参数为用户设置密码，PASSWORD参数为关键字，password为用户的新密码。WITH关键字后面可以带有多个参数with_option。9.3权限管理9.3.3权限授予使用GRANT语句赋予用户user