RHEL5中MySQL数据库的配置与管理 三

(5)创建用户

   首次安装MySQL时，MySQL自动授予任何用户都可以从本地连接MySQL服务器，但是，只有MySQL管理员(root用户)能够完全访

   问系统中所有数据库，而其他用户只能访问test数据库。这是因为MySQL安装程序在mysql这个数据库中设置了5个授权表，这5

   个授权表决定用户连接服务器及操作的权限。

                                       mysql数据库中5个授权表的功能

表名	说明
user	定义了能够连接到数据库服务器的用户和主机，以及这些用户的密码和访问权限
db	定义了连接到数据库服务器上的用户可以使用的数据库，以及在这些数据库中能够执行的操作
host	当表db中的Host字段为空时，由表 host定义用户可以从哪些主机连接到数据库服务器，并且不同的主机对指定的数据库可以具有不同的权限
tables_priv	定义了连接到数据库服务器上的用户可以访问的表，以及在这些表中可以执行的操作
columns_priv	定义了连接到数据库服务器上的用户可以访问的字段，以及在这些字段中可以执行的操作

   查看user表的内容，可以使用如下命令：

         mysql>use  mysql；

         mysql>select  host ，user，password，select_priv  from  user；

                +-----------------------+------+------------------+-----------------+

                 | host                  | user | password         | select_priv |

                +-----------------------+------+------------------+-----------------+

                 | localhost             | root | 565491d704013245 | Y         |

                 | localhost.localdomain | root |                     | Y          |

                 | localhost.localdomain |      |                     | N         |

                 | localhost             |      |                     | N         |

                 +-----------------------+------+------------------+----------------+

                 4 rows in set (0.00 sec)

    从查询结果可以看到：第1，2条记录表明，MySQL允许用户root可以从本地连接到数据库服务器，并对服务器中的所有数据库都具

    有完全控制权限，从结果记录中第4个字段开始，所有的字段值均为“Y”，表示用户具有完全控制权限；第3，4条记录表明，任何用

    户都可以从本地连接到数据库服务器中，但对所有数据库都没有访问权限，记录中user字段的值为空表示所有用户，而记录中的第4

    个字段开始，所有的字段值均为“N”，表示对所有数据库都没有访问权限。

   查看db表中的内容，可以使用如下命令：

          mysql>select  host，db，user，select_priv  from  mysql.db

                 +------+---------+------+-------------+

                 | host | db      | user | select_priv |

                 +------+---------+------+-------------+

                 | %    | test    |      | Y           |

                 | %    | test\_% |      | Y          |

                 +------+---------+------+-------------+

                 2 rows in set (0.01 sec)

    从查询结果可以看到：db表中定义了任何用户都可以从任何主机访问test数据库，或以test开头的数据库，并且对该数据库拥有完全

    控制权限。因为host字段的值为%通配符，即代表所有主机，db字段的值为test和test\_%表示test数据库或以test开头的数据

    库(“\_”是转义字符，“%”是通配符)，而从表中第4个字段开始，所有的字段值均为“Y”，表示具有完全控制权限。

    注意：尽管在db表中定义了任何用户都可以从任何主机访问test数据库，但在user表中已经做了限制，要求任何用户只可以从本地

    连接到数据库服务器，因此，两个表的共同作用使得MySQL最终的设置是对于任何用户只能从本地连接数据库服务器。

  根据以上分析，可以利用mysql数据库中的授权表在MsSQL中进行用户的管理：

  【创建新用户】

    例如：创建一个test用户，并为其设置密码，使其能够从任何一台主机上连接到数据库服务器,可以使用以下SQL语句实现：

          mysql>use  mysql；              //选择mysql数据库//

          mysql>insert  into  user  (host，user，password) values (‘%’，‘test’，password(‘test’))； //添加新记录//

          mysql>flush  privileges；         //对授权表进行重载//

          再让test用户从其他任何主机上连接到数据库服务器后，对e_learning数据库能完全控制，可以使用以下SQL语句：

          mysql>insert  into  db (host，db，user，select_priv)values(‘%’，‘e_learning’，‘test’，‘Y’)；

          mysql>flush  privileges；

  【更改用户密码】

    更改用户密码实际就是用update语句对授权表进行更新，例如，将test用户的密码改为123456，可以使用以下语句：

          mysql>update  user  set  password=password(‘123456’)；

          mysql>flush  privileges；

  【删除用户】

    删除用户的语句是delete，例如，删除test用户可以使用以下语句：

          mysql>delete  from  user  where  user=‘test’；

          mysql>flush  privileges；

                                                                 

 (6)用户权限设置

    若要进行用户权限的设置，首先需要了解在数据库mysql的以下5个授权表中，具体哪些字段是用来设置权限的：

                            user，db，host表中可设置的权限

字段名	权限名
select_priv	select查询
insert_priv	insert插入
update_priv	update更新
delete_priv	delete删除
create_priv	create创建
drop_priv	drop删除
reload_priv	reload重载
shutdown_priv	shutdown关闭
process_priv	process跟踪
file_priv	file文件读写
grant_priv	grant授权
references_priv	references引用
index_priv	index索引
alter_priv	alter修改
show_db_priv	show databases查看数据库
super_priv	super执行管理命令
create_tmp_table_priv	create temporary tables创建临时表
lock_tables_priv	lock tables锁定表
execute_priv	execute执行被保护程序
repl_slave_priv	replication slave 备份slave
repl_client_priv	replication client 备份client

                       tables_priv，columns_priv表中的权限字段和可设置的权限

表名	字段名	权限集合
columns_priv	column_priv	Insert，Reference，Select，Update
tables_priv	columv_priv	Insert，Reference，Select，Update
	table_priv	Alter，Create，Delete，Drop，Grant，Index，Insert，Reference，Select，Update

    MySQL提供了两种修改授权表中访问权限的方法：一种是使用insert，update等SQL语句直接修改授权表中的信息；另一种是使

    用GRANT和REVOKE语句。相比起来，GRANT和REVOKE语句要比前一种方法更为简单，清晰。

  【Grant语句】

    Grant语句的基本格式：

      mysql>grant 权限列表 [(字段列表)]  on  数据库名.表名  to  用户名@域名或IP地址  [identified  by  '密码值']

              [with  grant  option]；

   ·简单的授权

     简单的授权可以设置哪些用户可以从哪些主机连接到数据库服务器。

     例如：可以使用以下命令为用户test设置从任意主机连接到服务器的权限，并能完全访问news数据库。

           mysql>grant  all  on  news.＊  to  test@'%'  identified  by  'test'；

    需要注意的是：

   ·不同级别的授权

     不同级别的授权可以为同一个用户在对不同数据库操作时授予不同的权限。

     例如：可以使用以下命令创建一个新用户Kelly,使其能够从192.168.10.0子网中任何一台主机连接到服务器,具有读取e_learning

           数据库，并能修改teacher表中name字段的值：

           mysql>grant  select  on  e_learning.＊  to  Kelly@'192.168.10.%'  indentified  by  '123456'；

           mysql>grant  update  (name)  on  e_learning.teacher  to  Kelly@'192.168.10.%'；

   ·管理权限的授权

     管理权限的授权可以为用户设置某个数据库的完全访问权限，能够管理数据库中的所有表。

     例如：授予用户Sullivan能够从本地连接到服务器，并对e_learning数据库具有完全访问权限，并可以将其拥有的权限授予其他用

           户，可以使用以下语句：

           mysql>grant  all  on  e_learning.＊ to Sullivan@localhost  identified  by  '123456'  with  grant  option；

  【Revoke语句】

    Revoke语句的基本格式：

      mysql>revoke  权限列表  [(字段列表)]  on  数据库.表名  from  用户名@域名或IP地址；

    例如：MySQL管理员撤销用户Kelly@localhost对数据库e_learning的create，delete数据库和表的权限，并撤销该用户将自己             

          所拥有的权限授予他人的权限，可以使用以下语句：

          mysql>revoke  create，drop  on  e_learning.＊  from  Kelly@localhost；

          mysql>revoke  grant  option  on  e_learning.＊  from  Kelly@localhost；

 

 

 

 

 

三.MySQL数据库的备份和修复

   数据库的备份和修复是确保数据完整性，安全性的重要功能，MySQL数据库提供了以下备份和修复的工具。

 1.MySQL数据库备份和恢复

   在数据库数据丢失或损坏的情况下，备份数据库是很重要的。当发生系统崩溃时，需要尽可能使损失的数据最少，并且把数据库恢复到

   崩溃前的状态。根据备份时是否停止MySQL数据库服务，备份数据库分为在线备份与离线备份：

  (1)在线备份与恢复的命令与用法

　　【select  into  outfile | dumpfile】

      利用select语句的into outfile或dumpfile子句，可以将查询的结果导出到一个文件。其语法格式为：

     mysql>select  ...  into  {outfile | dumpfile}  'file_name'  [fields  [terminated by '\t']  [optionally]

             enclosed  by ''] [lines  terminated  by  '\n'

    【load  data  infile恢复文件】

      其语法格式为：

     mysql>load  data  infile  'file_name.txt'  [replace| ignore]  into  table  tbl_name  [fields  

             [terminated  by '\t'] [optionally] enclosed by ''] [lines  terminated  by  '\n']

    【mysqldump备份】

        由于利用select into语句导出数据时，只能导出数据的值，导出之后，就失去了数据与数据结构之间的关联。再导入数据时，只

      能依据自然序将各数据项(字段)对应起来，如果用户在导出数据之后修改了数据结构或数据项的位置，那么导入的数据就会发生错误。

        使用mysqldump能实现带结构的备份，从而克服了上述问题。mysqldump是一个MySQL自带的标准在线导出工具，导出后的

      数据将以SQL语句的形式存在，可以选择导出数据结构(建表SQL语句)或数据(数据插入SQL语句)。

        利用mysqldump(带-c或-complete_insert选项)可避免使用select-into/load data结构与数据分离所带来的问题

      mysqldump存在于/usr/bin/目录下(通过rpm包安装mysql)。其使用方法有三种：

      1导出指定数据库的表，如果不指定tables，将导出整个数据库的所有表：

         #mysqldump  [options]  database  [tables]           

         例如：导出e_learning数据库的teacher表到文本文件teacher.sql中

             #mysqldump  e_learning  teacher  -h localhost -u root -p >teacher.sql

      2同时导出多个数据库：

         #mysqldump  [options]  --database  [options]  db1  [db2  db3....]

      3导出当前服务器内的所有数据库：

         #mysqldump  [options]  -all-database  [options]

      通过#mysqldump -help可以得到当前mysqldump版本所支持的所有选项。

    【mysqlhotcopy备份】

        对于MyISAM型数据表，mysqlhotcopy是备份数据库或单个表的最快的途径，但只能运行在数据库目录所在的机器上。

      mysqlhotcopy是一个Perl脚本，使用LOCK TABLES，FLUSH TABLES和cp或scp来快速备份数据库。

        mysqlhotcopy可方便地将某个数据库复制为另外一个数据库，而不中止服务。其语法格式为：

         #mysqlhotcopy  db_name

      例如：执行以下命令

             #mysqlhotcopy  e_learning  -h localhost -u root -p 123456

              mysql>show databases;

      可以看到备份后多出了一个mysql_copy数据库，说明mysqlhotcopy命令执行成功。利用这种方法可以很快地进行整个数据库的

      在线备份。

  (2).离线备份    

    除了在线备份以外，还可以通过离线方式备份数据库，就是将MySQL数据库关闭后，直接利用命令行复制数据。这样做的优点在于一

    定会保持备份前的数据的一致性。可用如下命令将/var/lib/mysql目录下的数据库采用gzip方式压缩到/tmp/mysql.tgz文件中进行

    备份：   #tar  cvfz  /tmp/mysql.tgz  /var/lib/mysql

   

 2.MySQL数据库的故障修复

    数据库文件的损坏有很多原因，而且损坏的程度不同，但无论数据库损坏的原因如何，都应当在损坏后进行故障的修复。MySQL提供

    了myisamchk语句用以检查和修复MyISAM表，isamchk语句则用来检查和修复ISAM表。为防止myisamchk恢复数据库时

    产生不一致，在使用myisamchk命令时最好停止mysqld服务。

    Myisamchk基本语法如下：

         #myisamchk  [OPTIONS]  tables[.MYI]

    tables[.MYI]是指数据表所在的文件名，可以采用相对路径或绝对路径和采用通配符指示，例如：

             #myisamchk  f＊.MYI

    使用以下命令可以查看myisamchk的选项内容：

             #myisamckh  --help

    使用myisamchk命令还可以查看表的信息。通过以下命令可以以描述模式运行myisamchk，生成表的描述：

         #myisachk  -d  [-v]  表名

    以下是一个myisamchk  -d的输入例子：

             #myisamchk  -d  -v  func.MYI

    而如果只需要显示表的重要信息，则可使用以下命令：

         #myisamchk  -ei[s|v]  表名

     例如：  #myisamchk  -eiv  func.MYI         

    在故障恢复的过程中，myisamchk一行一行地创建一个MYD(数据)文件的副本，通过删除目的MYD文件，并且用原来的文件名重命

    名新文件。

    表文件发生损坏，通常会体现在发生查询意外中断的情况。同时，还可能出现以下错误，如：

        ·.FRM文件被锁定 

        ·无法找到.MYI文件

        ·文件意外结束

        ·记录文件毁坏等

    若遇到以上情况，则需要进行表修复的过程。通过myisamchk命令通常可以修复大多数错误，修复的一般步骤包括：

     ·确保MySQL用户及当前用户有权访问，修改需要修复的数据表文件，并能进入相关的数据库文件目录。

     ·运行myisamchk  *.MYI检查表，并根据检查表的结果作出不同的处理。

    若检查结果错误情况简单，则可以使用-q 快速恢复命令：

          #myisamchk  -r  -q  tbl_name

    若快速恢复命令不能恢复，则采用直接恢复，命令为：

          #myisamchk  -r  tal_name

    若仍然失效，则可采用安全恢复，命令为：

          #myisamchk  --safe-recover  tbl_name

导出表

mysqldump --opt school > school.sql

导入表

mysql

mysql>create database school;

mysql>use school;

mysql>source school.sql;

导出数据库

mysqldump --databases db1 db2 > db1.db2.sql

导入数据库

mysql < db1.db2.sql