《操作系统与数据库搭建》课件-第8章 数据库高级管理

数据备份恢复用户管理权限管理第八章数据库高级管理MySQL提供了一个mysqldump命令，它可以实现数据的备份。mysqldump命令可以备份单个数据库、多个数据库和所有数据库，具体如下：（1）备份单个数据库mysqldump命令备份数据库的语法格式如下：mysqldump–uusername–ppassworddbname[tbname1[tbname2…]]>filename.sql8.1数据库的备份与恢复8.1.1数据的备份创建一个名称为“学生选课”的数据库，并在数据库中创建表teahcer，插人相应数据，SQL语句如下：需要注意的是，在使用mysqldump命令备份数据库时，直接在Dos命令行窗口执行该命令即可，不需要登录到MySQL数据库。登录服务器：mysql-hlocalhost-uroot-pEnterpassword:******创建数据库以及表：CREATEDATABASE学生选课;USE学生选课;createtableteacher(tnochar(4)notnullprimarykey,tnamevarchar(10)notnull,tgenderchar(1),teduvarchar(10),tprovarchar(8));8.1数据库的备份与恢复8.1.1数据的备份使用SELECT语句查询表teacher，查询结果如下：mysql>SELECT*FROMteacher;+------+--------+---------+------------+----------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------------+----------+|t001|吴亚飞|男|本科|讲师||t002|李琦|男|硕士研究生|副教授||t003|王艳红|女|硕士研究生|讲师||t004|马志超|男|博士研究生|教授||t005|万丽|女|硕士研究生|助理讲师|+------+--------+---------+------------+----------+5rowsinset(0.00sec)8.1数据库的备份与恢复8.1.1数据的备份首先在D盘创建一个文件夹，用来存储数据库的备份文件，该文件夹命名为bak，然后重新开启一个D0S命令行窗口，注意这里是不用登录到MySql数据库，命令行切换到MySql安装路径的bin包下面，使用mysqldump命令对数据库“学生选课”数据库进行备份，mysqldump语句如下：mysqldump-uroot-hlocalhost--default-character-set=gbk-p123456学生选课>D:/bak/学生选课_20170805.sql8.1数据库的备份与恢复8.1.1数据的备份实例8-1--MySQLdump10.13Distrib5.1.35,forWin32(ia32)--Host:localhostDatabase:学生选课--Serverversion 5.1.35-community

/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/;.......信息省略--Tablestructurefortable`teacher`DROPTABLEIFEXISTS`teacher`/*!40101SET@saved_cs_client=@@character_set_client*/;/*!40101SETcharacter_set_client=utf8*/;CREATETABLE`teacher`(`tno`char(4)NOTNULL,`tname`varchar(10)NOTNULL,`tgender`char(1)DEFAULTNULL,`tedu`varchar(10)DEFAULTNULL,`tpro`varchar(8)DEFAULTNULL,PRIMARYKEY(`tno`))ENGINE=InnoDBDEFAULTCHARSET=gbk;/*!40101SETcharacter_set_client=@saved_cs_client*/;--Dumpingdatafortable`teacher`LOCKTABLES`teacher`WRITE;/*!40000ALTERTABLE`teacher`DISABLEKEYS*/;INSERTINTO`teacher`VALUES('t001','吴亚飞','男','本科','讲师'),('t002','李琦','男','硕士研究生','副教授'),('t003','王艳红','女','硕士研究生','讲师'),('t004','马志超','男','博士研究生','教授'),('t005','万丽','女','硕士研究生','助理讲师');/*!40000ALTERTABLE`teacher`ENABLEKEYS*/;UNLOCKTABLES;/*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/;

/*!40101SETSQL_MODE=@OLD_SQL_MODE*/;.......信息省略--Dumpcompletedon2018-01-207:24:198.1数据库的备份与恢复用记事本打开备份文件，文件内容如下所示:（3）备份所有数据库使用mysqldump命令备份所有数据库时，只需在该命令后使用“--all-databases”参数即可，其语法格式如下：mysqldump–uusername–ppassword--all-databases>filename.sql需要注意的是，如果使用“--all-databases”参数备份了所有的数据库，那么在还原数据库时，不需要创建数据库并指定要操作的数据库，因为，对应的备份文件中包含了CREATEDATABASE语句和USE语句。8.1数据库的备份与恢复8.1.1数据的备份mysql–uusername–ppassword[dbname]<filename.sqlmysql命令还原数据的语法格式如下：上述语法格式中，username表示登录的用户名，password表示用户的密码，dbname表示要还原的数据库名称，如果使用mysqldump命令备份的filename.sql文件中包含创建数据库的语句，则不需要指定数据库。8.1数据库的备份与恢复8.1.2数据的恢复我们知道数据库中的库是不能被还原的，因此在还原数据之前必须先创建数据库。接下来通过一个案例来学习数据的还原，具体操作步骤如下：（1）删除数据库DROPDATABASE学生选课;8.1数据库的备份与恢复8.1.2数据的恢复+--------------------+|Database|+--------------------+|information_schema||address||master||mysql||person||student||test|+--------------------+7rowsinset(0.00sec)可以使用SHOWDATABASES语句查询数据库，查询结果如下：8.1数据库的备份与恢复8.1.2数据的恢复（2）创建数据库由于库是不能恢复的，因此先要创建一个数据库“学生选课”，具体语句如下：CREATEDATABASE学生选课;上述语句执行成功后，接下来就可以还原数据库中的数据。8.1数据库的备份与恢复8.1.2数据的恢复（3）恢复数据mysql-uroot-p学生选课<D:/bak/学生选课_20170805.sqlEnterpassword:******使用mysql语句恢复D:/bak目录下的“学生选课_20170805.sql“文件，具体语句如下：上述语句执行成功后，数据库中的数据就会被恢复。8.1数据库的备份与恢复8.1.2数据的恢复（4）查看数据为了验证数据已经恢复成功，可以使用SELECT语句查询“学生选课”中teacher表的数据，查询结果如下：mysql>SELECT*FROMteacher;+------+--------+---------+------------+----------+|tno|tname|tgender|tedu|tpro|+------+--------+---------+------------+----------+|t001|吴亚飞|男|本科|讲师||t002|李琦|男|硕士研究生|副教授||t003|王艳红|女|硕士研究生|讲师||t004|马志超|男|博士研究生|教授||t005|万丽|女|硕士研究生|助理讲师|+------+--------+---------+------------+----------+5rowsinset(0.00sec)8.1数据库的备份与恢复8.1.2数据的恢复以上恢复方式只是其中的一种，我们还可以登录到MySQL数据库，使用source命令来还原数据，source命令恢复数据的语法格式如下：sourcefilename.sqlsource命令的语法格式比较简单，只需要在导人文件的时候指定文件名字为全路径名称即可。按照该语句执行，效果和在命令行执行mysql命令是一样的，这里不再详述。8.1数据库的备份与恢复8.1.2数据的恢复在安装MySQL时，会自动安装一个名为mysql的数据库，该数据库中的表都是权限表，如user、db、host、tables_priv、column_priv和procs_priv，其中user表是最重要的一个权限表，它记录了允许连接到服务器的账号信息以及一些全局级的权限信息，通过操作该表就可以对这些信息进行修改。8.2用户管理8.2.1user表（1）使用CREATEUSER语句创建用户

使用CREATEUSER语句创建新用户时，服务器会自动修改相应的授权表，但需要注意的是，该语句创建的新用户是没有任何权限的。CREATEUSER语句创建用户的语法格式如下：创建新用户有三种方式：8.2用户管理8.2.2创建新用户使用语句CREATEUSER创建一个新用户，用户名为zdp，密码为123,首先使用USE语句，选择数据库mysql，然后键入CREATEUSER语句如下:CREATEUSER'zdp'@'localhost'IDENTIFIEDBY'123';语句执行成功后，可以通过SELECT语句验证用户是否创建成功：mysql>SELECThost,user,passwordFROMuser;+-----------+------+-------------------------------------------+|host|user|password|+-----------+------+-------------------------------------------+|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||localhost|zwz|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|zdp|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257|+-----------+------+-------------------------------------------+3rowsinset(0.00sec)8.2用户管理实例8-2（2）使用GRANT语句创建用户GRANT语句不仅可以创建新用户，还可以对用户进行授权（将在后面讲解），该语句会自动加载权限表，不需要手动刷新，而且安全、准确、错误少，因此，使用GRANT语句是创建用户最常用的方法。GRANT语句创建用户的语法格式如下：8.2用户管理8.2.2创建新用户使用GRANT语句创建一个新用户，用户名为hnzj，密码为123，并授予该用户对“学生选课.teacher”表有查询权限，GRANT语句如下：GRANTSELECTON学生选课.teacherTO'hnzj'@'localhost'IDENTIFIEDBY'123';mysql>SELECThost,user,passwordFROMuser;+-----------+------+-------------------------------------------+|host|user|password|+-----------+------+-------------------------------------------+|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||localhost|zwz|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|zdp|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|hnzj|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257|+-----------+------+------语句执行成功后，可以通过SELECT语句验证用户是否创建成功：8.2用户管理实例8-3（3）使用INSERT语句创建用户

不管是CREATEUSER语句还是GRANT语句，在创建用户时，实际上都是在user表中添加一条新的记录，因此，也可以使用INSERT语句直接在该表中添加一个用户。INSERT语句创建用户的语法格式如下：8.2用户管理8.2.2创建新用户使用INSERT语句在mysql.user表中创建一个新用户，用户名为mythird，密码为123,INSERT语句如下:mysql>INSERTINTOmysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject)VALUES('localhost','mythird',PASSWORD('123'),'','','');以通过SELECT语句验证用户是否创建成功，具体如下：mysql>SELECThost,user,passwordFROMuser;+-----------+---------+-------------------------------------------+|host|user|password|+-----------+---------+-------------------------------------------+|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||localhost|zwz|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|zdp|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|hnzj|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|mythird|*23AE809DDACAF96AF0FD78ED04B6A265E05AA2575rowsinset(0.00sec)8.2用户管理实例8-4由于INSERT语句没有刷新权限表的功能，因此，mythird用户暂时是不能使用的，为了让当前用户生效，还需要手动刷新当前的权限表或重新启动MySQL服务，刷新权限表的语句如下：注意:FLUSHPRIVILEGES;执行成功后，就可以使用mythird用户登录MySQL数据库。8.2用户管理（1）使用DROPUSER语句删除用户

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

DROPUSER'zwz'@'localhost';mysql>SELECThost,user,passwordFROMuser;+-----------+---------+-------------------------------------------+|host|user|password|+-----------+---------+-------------------------------------------+|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||localhost|zdp|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|hnzj|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|mythird|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257|+-----------+---------+-------------------------------------------+4rowsinset(0.00sec)root用户登陆数据库服务器，使用DROPUSER语句删除用户zwz,SQL语句如下:可以通过SELECT语句验证用户是否删除成功：8.2用户管理实例8-5（1）使用DELETE语句删除用户DELETE语句不仅可以删除普通表中的数据，还可以删除user表中的数据，使用该语句删除user表中的数据时，只需指定表名为mysql.user，以及要删除的用户信息即可。同样的，在使用DELETE语句时必须拥有对mysql.user表的DELETE权限。DELETE语句的语法格式如下：上述语法格式中，mysql.user参数指定要操作的表，WHERE指定条件语句，Host和User都是mysql.user表的字段，这两个字段可以确定唯一的一条记录。8.2用户管理mysql>SELECThost,user,passwordFROMuser;+-----------+------+-------------------------------------------+|host|user|password|+-----------+------+-------------------------------------------+|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||localhost|zdp|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|hnzj|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257|+-----------+------+-------------------------------------------+3rowsinset(0.00sec)DELETEFROMmysql.userWHEREHost='localhost'ANDUser='mythird';root用户登陆数据库服务器，使用DELETE语句删除用户mythird,SQL语句如下：可以通过SELECT语句验证用户是否删除成功：8.2用户管理实例8-6（1）修改用户名修改已有用户zdp的用户名称为king，SQL语句如下：RENAMEUSER‘zdp’@’localhost’TO‘king’@’localhost’;可以通过SELECT语句验证用户是否修改成功：mysql>SELECThost,user,passwordFROMuser;+-----------+------+-------------------------------------------+|host|user|password|+-----------+------+-------------------------------------------+|localhost|root|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||localhost|king|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257||localhost|hnzj|*23AE809DDACAF96AF0FD78ED04B6A265E05AA257|+-----------+------+-------------------------------------------+3rowsinset(0.00sec)8.2用户管理实例8-7（2）修改用户名密码1）使用mysqladmin命令修改root用户密码：mysqladmin命令通常用于执行一些管理性的工作，以及显示服务器状态等，在MySQL中可以使用该命令修改root用户的密码。mysqladmin命令修改密码的语法格式如下：mysqladmin–uusername[–hhostname]–ppasswordnew_password8.2用户管理C:\Users\lenovo>mysqladmin-uroot-ppasswordnewpwd;Enterpassword:******C:\Users\lenovo>mysql-hlocalhost-uroot-pnewpwd;WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis6Serverversion:5.1.35-communityMySQLCommunityServer(GPL)Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>在命令行窗口，使用mysqladmin命令，将root用户的密码修改为newpwd,SQL语句如下:修改为newpwd,SQL语句如下:密码为root用户的旧密码，密码输入后，该语句执行完毕，root用户的密码被修改，下次登录时使用新的密码。8.2用户管理8.2.4修改用户信息

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

使用root用户登录到MySQL服务器，通过UPDATE语句将自身的密码修改为newpwd2，UPDATE语句如下：UPDATEmysql.userSETPassword=PASSWORD('newpwd2')WHEREUser='root'andHost='localhost';注意：还需使用FLUSHPRIVILEGES重新加载权限表，然后就可以使用新密码登录MySQL数据库。C:\Users\lenovo>mysql-uroot-pnewpwd2WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis24Serverversion:5.1.35-communityMySQLCommunityServer(GPL)Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>8.2用户管理8.2.4修改用户信息

实例8-93）使用SET语句修改root用户的密码

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

实例8-10

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

从上述语法格式可以看出，SHOWGRANTS语法格式比较简单，只需要指定查询的用户名和主机名即可。为了方便查询用户的权限信息，MySQL还提供了一个SHOWGRANTS语句。SHOWGRANTS的语法格式如下：8.3权限管理8.3.2权限查询mysql>SHOWGRANTSFOR'root'@'localhost';+----------------------------------------------------------------------------------------------+|Grantsforroot@localhost+-------------------------------------------------------------------------------------+|GRANTALLPRIVILEGESON*.*TO'root'@'localhost'IDENTIFIEDBYPASSWORD'*633B5898492ADFB22FB272BD8F047574F383F043'WITHGRANTOPTION|+----------------------------------------------------------------------------------------------+使用SHOWGRANTS语句查看用户root的权限信息，语句执行结果如下：root用户拥有所有权限，并且可以为其他用户赋予权限。8.3权限管理实例8-11

数据库中的用户拥有不同的权限，合理的授权可以保证数据库的安全。在MySQL中提供了一个GRANT语句，该语句可以为用户授权。上述语法格式中，privileges表示权限类型，columns参数表示权限作用于某一列，该参数可以省略不写，此时权限作用于整个表，username表示用户名，hostname表示主机名，IDENTIFIEDBY参数为用户设置密码，PASSWORD参数为关键字，password为用户的新密码。WITH关键字后面可以带有多个参数with_option。8.3权限管理8.3.3权限授予使用GRANT语句创建一个新的用户hnzj，密码为123，hnzj用户对所有数据库有查询、插入权限，并使用WITHGRANTOPTION子句将自己的权限赋予其他用户。GRANTINSERT,SELECTON*.*TO'hnzj'@'localhost'IDENTIFIEDBY'123'WITHGRANTOPTION;mysql>USEmysql;Databasechangedmysql>SELECTHost,User,Password,insert_priv,select_priv,grant_privFROMmysql.userWHEREuser=‘hnzj’;|Host|User|Password|insert_priv|select_priv|grant_priv||localhost|hn