第十四章:管理与维护
本节目标:
了解权限表的分类及其作用。
掌握账户管理中创建用户、删除用户的方法。
一、MYSQL的用户管理
MYSQL用户可以分为普通用户和root用户。
root用户是超级管理员,拥有所有权限。
普通用户只拥有被授予的各种权限。
二、MYSQL用户管理的相关内容
1、权限表
MYSQL服务器通过权限表来控制用户对数据库的访问,权限表存放在MYSQL数据库中。
存储账户的权限信息表主要有: user、db、host、tables_priv、columns_priv和procs_priv。
2、各个权限表的作用
user表是MySQL中最重要的一个权限表,记录允许连接到服务器的账户信息,里面的权限是全局级的;
db表和host表是MySQL数据中非常重要的权限表;
db表中存储了用户对某个数据库的操作权限;
host表中存储了某个主机对数据库的操作权限。
tables_priv表用来对表设置操作权限。
columns_priv表用来对表的某一列设置权限。
procs_priv表可以对存储过程和存储函数设置操作权限。
3、账户管理
1)、新建普通用户
创建新用户,必须有相应的权限来执行创建操作。
在MYSQL数据库中,可以使用CREATE USER或GRANT语句。
①、使用CREATE USER语句创建新用户
▶基本语法格式如下︰
CREATE USER 'user'@'host'
IDENTIFIED BY [PASSWORD] 'password'
案例:使用CREATE USER创建一个用户,用户名是rose,密码是rosepwd,主机名是localhost。
-- 创建用户,指定明文密码 create user 'rose'@'localhost' identified by 'rosepwd'; -- 查看用户是否创建成功 select user,host from mysql.user; -- 创建用户,不设置密码 create user 'rose01'@'localhost'; select user,host from mysql.user; -- 查看密码的哈希值 select password('rosepwd'); -- 创建用户,使用哈希值的密码 create user 'rose02'@'localhost' identified by password '*15151B36B8E49FD6A6222C4AF15758661CFCE654';
②、使用GRANT语句创建新用户
create user语句可以用来创建账户,通过该语句可以在user表中添加一条新的记录,但是create user语句创建的新用户没有任何权限,还需要使用grant语句赋予用户权限。
而grant语句不仅可以创建新用户,还可以在创建的同时对用户授。
▶基本语法格式如下∶
GRANT privileges ON db.table
To 'user'@'host'[IDENTIFIED BY 'password'];
案例:使用GRANT语句创建一个新的用户testuser,密码为testpwd,并授予用户对所有数据表的SELECT和UPDATE权限。
-- 创建用户,并授予用户查询、更新数据库所有表的权限 grant select,update on *.* to 'testuser'@'localhost' identified by 'testpwd'; select user,host from mysql.user;
2)、删除普通用户
在MYSQL数据库中,可以使用DROP USER语句删除用户,也可以直接通过DELETE语句从MYSQL.user表中删除对应的记录来删除用户。
①、使用DROP USER语句删除用户
语法如下∶
DROP USER user[,user];
例︰使用DROP USER删除账户‘testuser'@'localhost':
DROP USER‘testuser'@'localhost';
-- 删除用户 drop user 'testuser'@'localhost'; select user,host from mysql.user;
②、使用DELETE语句删除用户
语法格式如下︰
DELETE FROM mysql.user WHERE host='hostname' anduser='username';
-- 删除用户 delete from mysql.user where user = 'rose02'and host='localhost'; select user,host from mysql.user;
本节目标:掌握权限管理中给用户授权、回收权限以及查看权限的方法。
一、权限管理
权限管理主要是对登录到MYSQL的用户进行权限验证,所有用户的权限都存储在MYSQL的权限表中。
MYSQL权限系统的主要功能是证实连接到一台主机的用户,并且赋予该用户在数据库上的各种权限。
二、授权
授权就是为某个用户授予权限。
MYSQL中可以使用GRANT语句为用户授予权限。
1、给用户授予权限
基本语法:
GRANT priv_type ON db.table to 'user' @'host'[IDENTIFIED BY [PASSWORD] 'password'];
案例:用grant语句给已经创建好的用户rose授予对book数据库所有表插入、查询的权限。
-- 授予rose用户插入、查询book库所有表的权限 grant insert,select on book.* to 'rose'@'localhost'; -- 刷新系统权限表 flush privileges; -- 授予rose01用户作用于所有库的所有表的所有权限 grant all privileges on *.* to 'rose01'@'localhost';
2、查看用户的授权
基本语法:
SHOW GRANTS FOR'user'@'host';
-- 查看用户的权限信息 show grants for 'rose'@'localhost'; show grants for 'rose01'@'localhost';
3、收回权限
收回权限就是撤销已经赋予用户的某些权限。
语法格式如下:
REVOKE privilege ON db.table FROM‘user'@'host';
-- 回收rose用户的作用于book库所有表的插入的权限 revoke insert on book.* from 'rose'@'localhost'; flush privileges; show grants for 'rose'@'localhost';
本节目标
- 理解什么是MYSQL的日志;
- 了解日志的分类;
- 掌握错误日志的启动和设置、查看以及删除的操作;
一、MYSQL的日志
MYSQL的日志记录了MYSQL数据库的运行情况、用户操作、错误信息等,可以为MYSQL管理和优化提供必要的信息。
二、MYSQL日志的分类
- 错误日志:记录MYSQL服务的启动、运行或停止MYSQL服务时出现的问题。
- 查询日志:记录了MYSQL的所有用户操作,包括启动和关闭服务、执行查询和更新语句等。
- 二进制日志:记录所有更改数据的语句。
- 慢查询日志:记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
三、错误日志
MYSQL会将服务的启动、运行或停止时出现的一些错误信息记录到错误日志文件中。
四、启动和设置错误日志
- 在默认情况下,错误日志会记录到数据库的数据目录下。如果没有在配置文仲中指定文件名,则文件名默认为hostname.err。
- 错误日志的启动和停止以及指定日志文件名,都可以通过修改my.ini来配置。错误日志的配置项是log-error。
- 如果需要指定文件名,则配置项如下∶
- [mysqld]
- log-error=file_name
五、查看错误日志
- MYSQL的错误日志是以文本形式存储的,可以使用文本编辑器直接查看MYSQL的错误日志。
- 如果不知道日志文件的仔储哈丘,可以文力no vn查询错误日志的存储路径。
- SHOW VARIABLES LIKE 'log_error';
六、删除错误日志
- 若数据库运行时间较长,错误日志文件可能比较大,那么可以将日志文件删除,再重新创建新的错误日志文件。
- MYSQL的错误日志是以文本文件形式存储在文件系统中的, 可以直接删除。
- 在删除错误日志之后,如果需要重建日志文件,需要在服务器端执行以下命令∶
- mysqladmin-uroot -p flush-logs
- 或者登录MYSQL,然后执行flush logs语句︰
- mysql>flush logs;
- 在日志文件不存在的情况下,执行flush logs语句会创建新的日志文件。
- 如果日志文件存在的情况下,并不做日志备份和创建的操作。
案例:
show variables like 'log_error';
-- 创建新的日志信息表 flush logs; -- 创建新的日志信息表 mysqladmin -uroot -p flush-logs
本节目标:掌握数据备份与恢复的操作。
一、数据的备份与恢复
保证数据安全的最重要的一个措施是确保对数据进行定期备份,如果数据库中的数据丢失或者出现错误,可以使用备份的数据进行恢复,这样就尽可能地降低了意外原因导致的损失。
二、使用MYSQLdump命令备份
- MYSQLdump是MYSQL提供的一个非常有用的数据库备份工具。
- MYSQLdump命令执行时,可以将数据库备份成一个文本文件。
- MYsQLdump备份数据库语句的基本语法格式如下︰
- mysqldump -u user -h host -p password dbname[tbname,[tbname...]]>filename.sql
案例
1、使用MYSQLdump命令备份数据库图书管理系统中的所有表。
2、备份book数据库中的readerinfo表。
3、使用MYSQLdump备份多个数据库,比如备份book和mytest数据库。
-- 备份book库 mysqldump -uroot -p book >C:\mysqlbackup\book_20180120.sql -- 备份book库中的readerinfo表 mysqldump -uroot -p book readerinfo>C:\mysqlbackup\book_readerinfo_20180120.sql -- 备份book和mytest库 mysqldump -uroot -p --databases book mytest>C:\mysqlbackup\book_mytest_20180120.sql -- 备份所有的数据库 mysqldump -uroot -p --all-databases>C:\mysqlbackup\dball_20180120.sql
三、数据恢复
使用MYSQL命令恢复,语法格式如下︰
mysql -u user -p [dbname] < filename.sql
案例:使用MYSQL命令将C:\mysqlbackup\books_readerinfo_20100120.sql文件中的备份导入到数据库中。
-- 恢复book库下的readerinfo表 mysql -uroot -p book<C:\mysqlbackup\book_readerinfo_20180120.sql
如果已经登录MYSQL服务器,还可以使用source命令导入SQL文件,语法如下∶
source filename
-- 恢复book库下的readerinfo表 use book; source C:\mysqlbackup\book_readerinfo_20180120.sql
本节目标:掌握数据表的导入和导出的操作
一、数据表的导入和导出
导出:就是将MYSQL数据库中的数据导出到外部存储文件中。
导入:就是将外部存储文件中的数据导入到MYSQL数据库中。
二、使用SELECT...INTO OUTFILE导出文本文件
语法格式∶
SELECT columnlist FROM table WHERE condition INTO OUTFILE'filename' [OPTIONS]
[OPTIONS]为可选参数选项,比如︰
参数FIELDS TERMINATED BY 'value':设置字段之间的分隔字符,可以为单个或多个字符,默认情况下为制表符‘\t'。
参数LINES TERMINATED BY 'value':设置每行数据结尾的字符,可以为单个或多个字符,默认值为‘\n'。
案例1:将图书管理系统中的readerinfo表中的记录导出到文本文件。
-- 使用select...into outfile导出readerinfo表的数据到readerinfo.txt这个文件 select * from book.readerinfo into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt'; -- 使用select...into outfile导出readerinfo表的数据到readerinfo.txt这个文件,并设置导出文件的格式 select * from book.readerinfo into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo1.txt' fields terminated by ',' lines terminated by '\r\n';
三、使用MYSQLdump命令导出文本文件
MYSQLdump创建一包含创建表的CREATE TABLE语句的tablename.sql文件和一个包含其数据的tablename.txt文件。
语法格式如下:
mysqldump -T path -u root -p dbname [tables][OPTIONS][
OPTIONS]为可选参数选项,比如︰
--fileds-terminated-by=value:设置字段之间的分隔字符,可以为单个或多个字符.默认情况下为表符“\t”。
--lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为"\n";
案例2:使用MYSQLdump将book库中的bookcategory表中的记录导出到文本文件。
-- 使用mysqldump命令导出book库下的bookcategory表 mysqldump -T "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" -uroot -p book bookcategory
四、使用MYSQL命令导出文本文件
语法格式∶
mysql -u root -p --execute="SELECT语句" dbname > filename.txt
案例:3:使用mysql命令将图书管理系统中的readerinfo表中的记录导出到文本文件
-- 使用mysql命令导出book库下的readerinfo表到readerinfo2.txt mysql -uroot -p --execute="select * from readerinfo;" book > "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo2.txt"
一、使用LOAD DATA INFILE方式导入文本文件
语法格式:
LOAD DATAINFILE 'filename.txt' INTO TABLE tablename[OPTIONS][IGNORE number LINES]
[OPTIONS]为可选参数选项, 比如∶
参数FIELDS TERMINATED BY 'value':设置字段之间的分隔字符,可以为单个或多个字符,默认情况下为制表符'\t'。
参数LINES TERMINATED BY 'value'︰设置每行数据结尾的字符,可以为单个或多个字符,默认值为‘\n'。
案例4:使用LOAD DATA命令将readerinfo.txt文件中的数据导入到book数据库中的readerinfo表。
-- 使用load data infile命令导入readerinfo表中的数据 load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt" into table book.readerinfo;
二、使用MYSQLimport命令导入文本文件
语法格式:
mysqlimport -u root -p dbname filename.txt [OPTIONS]
[OPTIONS]为可选参数选项,比如︰
--fileds-terminated-by=value:设置字段之间的分隔字符,可以为单个或多个字符,默认情况下为表符“\t”。
--lines-terminated-by=value:设置每行数据结尾的字符,可以为单个或多个字符,默认值为"'\n";
-- 使用mysqlimport命令导入readerinfo表中的数据 mysqlimport -uroot -p book "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/readerinfo.txt"