《PHP和MySQL Web开发》学习笔记（十四）

上一篇 / 下一篇 2007-09-07 14:26:48 / 天气: 晴朗 / 心情: 平静 / 个人分类：PHP & MySQL

第12章 MySQL高级管理

12.1 深入理解权限系统
如果要管理一个MySQL数据库，那么准确地理解GRANT命令的作用及其工作原理是非常有意义的。
当执行一条GRANT语句的时候，它会影响一个名为mysql的特殊数据库。权限信息就是保存在这个数据库的5个表中。这样，当对数据库授予权限的时候，就应该注意是否授予了访问mysql数据库的访问权限。
需要注意的一点是，GRANT命令只能在MySQL 3.22.11版本及更高版本中使用。
以管理员身份登录，键入如下命令，我们就可以看到mysql数据库的内容：use mysql;
执行上述命令后，如果想查看该数据库中的表，可以输入如下命令：show tables;
保存权限信息的5个表是：user、host、db、tables_priv和columns_priv。有时，这些表也称为授权表(grant tables)。这些表的具体用途各不相同，都它们的基本用途都是相同的，都可以用来确定用户身份以及是否允许执行特定操作。这些表都包含两种类型的字段：范围字段，它可以指定用户、主机和数据库的一部分；权限字段，它可以指定该用户在该范围内可以执行哪些操作。
user表和host表用于确定一个用户是否可以连接MySQL服务器，以及该用户是否具有任何管理员权限。db表和host表确定用户可以访问哪些数据库。tables_priv表确定用户可以使用数据库中哪些表，而columns_priv表确定用户可以访问表中的哪些列。

12.1.1 user表
该表包含全局用户权限的详细信息。它可以确定一个用户是否有权连接MySQL数据库，是否具有全局级别的权限；即是否有使用系统中每个数据库的权限。
我们可以通过执行describe user;语句来浏览该表的结构。
如果一个用户具有特定的权限，该列中的值就应该是Y。相反，如果用户没有被授予权限，其值就应该是N。
user表中列出的所有权限都是全局的，也就是说，它们适用于系统中（包括mysql数据库）所有的数据库。因此，管理员的一些权限为Y，但是大部分用户应该全部为N。普通用户应该有一些访问适当数据库但不是所有表的权限。

12.1.2 db表和host表
普通用户的权限大多数都保存在db表和host表中。
db表可以确定哪些用户可以访问哪些主机和哪些数据库。此表中给出的权限应用于在一个特定行中指定的任意数据库。
host表是db表的补充。如果一个用户从多个主机连接到数据库，在db表中将不会列出该用户的主机名。而与此项对应，该用户将在host表中获得一组记录，每个记录都指定了每个用户—主机对的权限。

12.1.3 tables_priv表和columns_priv表
这两个表分别用于存储表级别的权限和列级别的权限。这与db表类似，但是它们分别为在指定数据库中的表授予权限和为在指定表中的列授予权限。
这些表的结构与user、db和host表结构有所不同。
tables_priv表的Grantor列用来存储被授予权限的用户。两个表中的Timestamp列用来存储授权日期与时间。

12.1.4 访问控制：MySQL如何使用Grant表
MySQL使用grant表通过两个步骤来确定允许用户做什么：
1)连接验证。在这里，MysQL基于如上所示的user表中的信息检查用户是否有权连接数据库。这是基于用户名、主机名和密码进行的验证。如果用户名为空，它将匹配所有的用户。主机名可以用通配符%指定。通配符%可以用作整个主机名（也就是说，“%”符号匹配所有的主机），或者用作主机名的一部分，例如，%.tangledweb.com.au匹配所有以.tangledweb.com.au结尾的主机。如果密码字段为空，则不要求密码。在主机名称中避免使用通配符，使用没有密码的用户名，避免空用户名，这样做会更安全。如果主机名为空，MySQL将引用host表，找到匹配的user和host对。
2)请求验证。当建立一个连接之后，对于所发送每一个请求，MySQL都会检查是否有执行该请求的权限级别。系统首先将检查全局权限（在user表中），如果这些还不够，系统将再检查db表和host表。如果仍然没有足够的权限，MySQL将检查tables_priv表，如果权限还不够，最后将检查columns_priv表。

12.1.5 更新权限：修改时候生效
MySQL服务器启动的时候以及使用GRANT和REVOKE语句的时候，服务器会自动读取grant表。但是，既然我们知道这些权限保存在什么地方以及它们是如何保存的，就可以手动修改它们。当手动更新它们的时候，MySQL服务器将不会注意到它们已经被修改了。
我们必须向服务器指出已经对权限进行了修改，有3种方法可以实现这个任务。可以在MySQL提示符中（必须以管理员的身份登录进入）键入：flush privileges;这是更新权限最常用的方法。
或者，还可以在操作系统中运行：mysqladmin flush-privileges
或者：mysqladmin reload
此后，当用户下次再连接时，系统将检查全局级别权限；当下一个命令被执行时，将检查数据库级别的权限；而表级别和列级别权限将在用户下次请求的时候被检查。

12.2 提高MySQL数据库的安全性

12.2.1 从操作系统角度来保护MySQL
如果正在运行类似于UNIX的操作系统，那么以root用户的身份运行MysQL服务器(mysqld)是一个糟糕的主意。因为，这可能赋予了一个MySQL普通用户读定操作系统任何地方的文件的权限。
创建一个专门用来运行mysqld的特定MySQL用户是一个好主意。此外，还可建立只能够由MySQL用户访问的目录（保存物理数据的地方）。在许多安装方式中，服务器都是设置为以MySQL用户组中的userid为mysql的用户来运行服务器端的。
然而，更理想的办法是，应该将MySQL服务器建立于防火墙后。这样，MySQL服务器就可以终止来自未授权机器上的连接；检查一下是否可从服务器之外，以端口号为3306的方式连接MySQL。这是MySQL运行的默认端口，可以在防火墙中关闭它。

12.2.2 密码
必须确认所有用户都有密码（特别是root用户！）并且要选好这些密码，定期修改，与使用操作系统密码一样。这里要记住的基本原则是不要使用字典的单词作为密码，用字典的单词做密码是非常糟糕的想法。数字和字母的结合是最好的方案。
如果要在脚本文件中保存密码，请确认只有密码保存在该脚本中的用户可以阅读该脚本。
用户数据库的PHP脚本需要能够访问那个用户的密码。将登录名和密码保存在一个PHP脚本可能是安全的，例如dbconnect.php，这样可以在需要的时候包含这个文件。这个脚本必须小心地保存在Web文档树结构以外，并且只能由特定的用户进行访问。
请记住，如果要将这些细节信息保存在网络文档树中以.inc或其他一些扩展名结尾的文件中，请务必小心，检查Web服务器是否知道这一点，也就是说，这些文件必须解释为PHP，从而防止这些细节在另一个Web浏览器中被看到。
不要以纯文全的形式将密码保存于数据库中。MySQL密码不是这样保存的，但是在Web应用程序中通常也要保存网站用户的注册名和密码。可以用MySQL的SHA1()函数将密码加密以后（单向）再进行保存。请注意，如果在运行SELECT（试图登录一个用户）的时候，使用INSERT插入一个密码到这些格式之一，必须再次使用相同的函数来检查用户键入的密码。

12.2.3 用户权限
俗话说，知识就是力量。请确认你已经理解了MySQL的权限系统，以及授予特定权限的后果。请不要授予任何用户不必要的权限。应该查看grant表来确认这一点。
特别地，如果不是绝对需要，请不要将PROCESS、FILE、SHUTDOWN和RELOAD等权限授予任何非管理员的用户。PROCESS权限可用于观察其他用户正在做什么，输入了什么，包括他们键入的密码。FILE权限可以用来读定操作系统中的文件（例如，包括UNIX系统中的/etc/password）。
GRANT权限也要在授予的时候非常小心，因为它准许用户将他们的权限分享给其他用户。
请确认在建立用户的时候，只授予他们从当前连接的主机访问数据库的权限。我们也应该避免在主机名中使用通配符。
可以通过在host表中使用IP地址而不是域名来提高安全性能。这可以在DNS位置避免错误问题或者黑客侵入。
此外，还应该防止非管理员用户访问Web服务器中的mysqladmin程序。因为这是从命令行运行的，它是操作系统权限的问题。

12.2.4 Web问题
建立一个专门用于网络连接的用户并不是一个坏主意。这样可以授予他们必要、最少的权限，从而不给用户授予以下一些权限，例如DROP、ALTER或CREATE权限。我们可能只在类型表中授予SELECT权限，而只在订单表中授予INSERT权限。此外，这还是如何应用最少权限原则的例证。
提示：我们在上一章讨论了如何使用PHP的addslashes()函数和stipslashes()函数以去掉任何可能产生问题的字符。记住这样做和在将作任何数据发送到MySQL数据库之间要执行一个常规的数据清理都是非常重要的。你可能记得，我们曾经使用了doubleeval()函数来检查数字类型的数据是否真正是数字类型的数据。我们经常容易犯的一个错误就是会忘记它——人们往往记得使用addslashes()但是忘记检查数字类型数据。
应该经常检查来自用户的所有数据。即使HTML表单中由选项框和按钮组成，一些人还是可能通过企图修改URL以进入脚本。此外，还应该检查用户数据的大小。
如果用户输入的密码或机密数据需要保存在数据库中，请注意，如果不使用SSL(Secure Sockets Layer)，这些数据将以纯文本的方式从浏览器传递到服务器。

12.3 获取更多关于数据库的信息

12.3.1 使用SHOW获取信息
前面，我们已经使用了：show tables;语句来获取数据库中所有表。
如下语句：show databases;可以显示所有的数据库。
还可使用SHOW TABLES语句查看其中一个数据库的表：show tables from books;
当使用SHOW TABLES的时候，如果没有指定特定的数据库，默认数据库为当前使用的数据库。
当知道数据库中都有哪些表的时候，可能获取该表的所有列：show columns from orders from books;
如果没有给出数据库参数，在默认情况下，SHOW COLUMNS语句所操作的数据库是当前使用的数据库。此外，也可以使用table.column符号：show columns from books.orders;
SHOW语句另一个非常有用的变化是查看用户所拥有的权限。例如，运行下列语句：show grants for bookorama;
结果所示的GRANT语句未必是被执行并授予特定用户权限的所必需的语句，而更像是产生用户当前级别权限的等价语句。
提示：SHOW GRANTS语句是在MySQL 3.23.4版本中新增加的。
SHOW还有许多其他变化形式，内容略。

12.3.2 使用DESCRIBE获取关于弄的信息
作为SHOW COLUMNS命令的替换，可以使用DESCRIBE语句，它类似于Oracle（另一个RDBMS）中的DESCRIBE语句。其基本语法如下所示：DESCRIBE table [column];
这将给出表中所有列的信息，或者如果付薱olumn，将给出该列的信息。也可以在列名中使用通配符。

12.3.3 用EXPLAIN理解查询操作的工作过程
有两种方式可以调用EXPLAIN语句。第一种，可以使用：EXPLAIN table;
上述语句的输出结果非常类似于DESCRIBE table或SHOW COLUMNS FROM table语句的输出结果。
第二种，也是更有趣的方式，可以使用EXPLAIN语句来查看MySQL是如何来解释并执行一个SELECT查询。要使用这种方式，只要在SELECT语句前面加上单词EXPLAIN即可。
当试图使一个复杂的查询能够正常工作起来而查询语句写得不是很正确的时候，或者当一个查询的执行时间大大超出它应该需要的时候时，可以使用EXPLAIN语句。如果编写一个复杂的查询，可以在实际运行查询之前执行EXPLAIN命令以提前检查它，根据该语句的结果，如果有必要的话，可以修改SQL语句，从而对其进行优化。这也是方便的学习工具。
EXPLAIN举例内容略。
可以用多种方法解决在EXPLAIN的结果中出现的问题。首先，可以检查列类型并且确认它们相同。这特别适用于列宽度。如果它们的列宽度不同，索引不能用于匹配这些列。可以通过修改列类型以使其匹配，或者在设计的开始就建立类型匹配的列，从而解决这个问题。
其次，可以让关联优化器来检查关键字的分布，因此使用myisamchk或ANALYZE TABLE语句对关联进行优化，使得它效率更高。

12.4 使用索引提高查询速度
如果EXPLAIN结果possible_keys列包含一些NULL值，可能需要对正在被讨论的表添加一个索引来提高查询性能。如果在WHERE子句中使用的列适合作为索引，可以使用ALTER TABLE语句为它创建一个新索引，例如：ALTER TABLE table ADD INDEX (column);

12.5 优化数据库

12.5.1 估计优化
一般说，我们可能会希望数据库中每一个数据越小越好。因此，可以使用最小化冗余的设计思想来实现此目的。当然，也可以通过使用最小的列数据类型来实现它。也应该尽可能使NULL最少，使主键尽可能短。
如果可能，尽量避免使用可变长度列（像VARCHAR、TEXT和BLOB）。如果字段长度固定，它们用起来将更快，但是要占用多一点的空间。

12.5.2 权限
除了使用前面所述的关于EXPLAIN的建议，还可以通过简化权限来提高查询速度。前面，我们讨论了在查询执行之前通过权限系统检查该查询的过程，该过程越简单，查询速度越快。

12.5.3 表的优化
如果一个表已经用了一段时间，随着更新和删除操作的发生，数据将会变得支离破碎。这样同样会增加在该表中查询所花的时间。可以使用如下语句修复它：OPTIMIZE TABLE tablename;
或者在命令提示符下键入：myisamchk -r table
也可以使用myisamchk工具根据索引对该表索引和数据进行排序，如下所示：myisamchk --sort-index --sort-records=1 pathtomysqldatadirectory/*/*.MYI

12.5.4 使用索引
可以在需要提高查询速度的地方使用索引。简化索引，不要创建查询不使用的索引。运行前面所介绍的EXPLAIN命令可以检查该索引是否正在使用中。

12.5.5 使用默认值
在尽可能的地方使用列的默认值，只在与默认值不同的时候才插入数据。这样可以减少执行INSERT语句所花的时间。

12.5.6 其他技巧

12.6 备份MySQL数据库
在MySQL中，可以通过几种不同的方法来执行数据库的备份。第一种方法是在复制数据文件时使用LOCK TABLES命令锁定这些表。该命令语法如下所示：LOCK TABLES table lock_type [, talbe lock_type ...]
每一个表必须是表的名称，而锁定类型可以是READ或WRITE。对于备份来说，只需要READ锁。在执行备份之前，必须执行FLUSH TABLES；命令来确保对索引所做的任何修改将写入到磁盘。
在执行备份时，用户和脚本还可以运行只读查询。如果有大量可以修改数据库的查询，例如客户订单，这种解决方案并不实际。
第二种方法，也是比较好的方法是使用mysql_dump命令。该命令是在操作系统的命令行下使用的，该命令的典型用法如下所示：mysqldump --opt --all-databases > all.sql
上述命令将所有用来重新构建一个SQL数据库所需的内容都导出到一个名为all.sql的文件中。
稍后，可以停止mysqld进程，并且通过--log-bin[=logfile]命令选项重新启动它。保存在日志文件中的更新将上次备份后数据库发生的变化。（很明显，在任何常规文件的备份中，还应该备份所有的日志文件。）
第三种方法是使用mysqlhotcopy脚本。可以使用如下命令调用：mysqlhotcopy database /path/for/backup
然后，必须遵照前面介绍的启动和停止数据库的操作。
备份（故障转移）的最后一个方法是维护数据库的一个拷贝。

12.7 恢复MySQL数据库
如果需要恢复MySQL数据库，也有很多方法可以实现。如果出现了一个破坏了的表，可以运行带有-r（修复）选项的myisamchk命令。
如果使用了第一种方法执行备份，可以将数据库文件重新复得到到安装MySQL的相同位置。
如果使用了第二种方法执行备份，需要执行一些操作。首先，必须在导出文件中运行查询。这可以将数据库重新构建至导出该文件时的状态。接着，还应该将数据库更新至保存在二进制日志文件中的状态。可以运行如下所示的命令：mysqlbinlog hostname-bin.[0-9]* | mysql

12.8 实现复制
复制是一个允许提供相同数据库的多个数据库服务器的技术。这样，可以载入共享并提高系统可靠性；如果有一个服务器停止运行，其他服务器还能继续工作。复制一旦设置成功。它也可以用作备份。
其基本思想就是拥有一个主服务器，并且为其添加几个从服务器。每一个从服务器都镜像了主服务器。当最初设置了从服务器后，可以在任何时候在主服务器上复制所有数据的快照。这样，从服务器将请求来自主服务器的更新。主服务器将传输通过其二进制日志而执行的查询细节，而从服务器可以重新将这些查询应用于它们的数据。
使用这种设置的常规方法是在主服务器应用写查询，而对从服务器应用读查询。这是通过应用程序逻辑来实现的。更复杂的架构也是可能的，例如具有多个主服务器，但是我们只介绍典型示例的设置。
我们必须意识到，通常从服务器的数据都没有主服务器的数据新。这发生在任何分布式数据库中。
要开始设置主服务器和从服务器架构，必须确认主服务器上启用了二进制日志记录。

12.8.1 设置主服务器
在主服务器上，必须为从服务器创建一个用来连接主服务器的用户。从服务器的这个特殊的权限级别称作复制从服务器。根据如何策划实现初始数据传输的不同，可以临时授予某些额外的权限。
在大多数情况下，可以使用数据库快照来传输数据，而在这个例子中，只需要特殊的复制从服务器权限。根据最少权限原则，在系统设置成功并运起起来后，必须撤销额外的权限。

12.8.2 执行初始的数据传输
将数据从主服务器传输到从服务器有几种方法。最简单的方法是设置从服务器，然后运行LOAD DATA FROM MASTER语句。这种方法的问题是在数据传输过程中，它将锁定主服务器上的表，而且这种传输需要一定的时间，因此我们并不建议使用这种方法（只有使用MyISAM表时，可以使用这个选项）。
通常，在当前时间获得数据库的一个快照是一个更好的办法。可以使用本章其他地方介绍的备份过程来获得快照。必须首先使用如下语句来清空表：flush tables with read lock;
使用读锁定的原因是必须在获得快照时记录服务器在二进制日志中的位置。可以通过执行如下所示的语句来实现：show master status;
请注意在输入结果中的File和Position的值，需要这些信息来设置从服务器。
现在，使用如下所示的语句获得快照并去除表的读锁定。

12.8.3 设置一个/多个从服务器
设置一个/多个从服务器有两个选项。如果获得数据库的快照，可以在从服务器上安装它。
接下来，在从服务器上运行如下所示查询：
change master to
master-host='server',
master-user='user',
master-password='password',
master-log-file='logfile',
master-log-pos=logpos;
start slave;
现在，你应该已经设置并运行了从服务器。
如果没有获得快照，可以执行如下所示的语句在运行了以上查询后载入数据：load data from master;

12.9 进一步学习

12.10 下一章