PHP、MySQL教程：MySQL管理
对于内容驱动的网站，设计好坏的关键是关系型数据库。在这个教程中，我们已经使用了MySQL关系型数据库管理系统(RDBMS)建立了我们的数据库。对于网站的开发者来说，MySQL是一个较受欢迎的选择，这不仅是因为它对于任何平台上的非商业应用都是免费的，而且也因为它的架设和使用非常的简单。正如我们在第一章中所看到的那样，根据正确的指导，一个新的用户可以在不超过30分钟的时间内架设好一个MySQL服务，并将其运行起来(对于一个有经验的用户甚至只要10分钟！)。 如果你想做的仅仅是架设一个MySQL服务环境，以用来做一些例子和练习，那么我们在第一章安装时所使用的初始化设置对你已经足够了。但是，如果你是想建立一个真正的用于Web站点的数据库--也许这个站点对于你的公司很重要--那么你还需要学习一些有关MySQL的知识。 对于作为基于Internet的企业的一部分的商业事务来说，数据的备份是很重要的。不幸的是，因为备份的工作对于一个管理员来说往往是不太感兴趣的，人们总是无法认清它的重要性，于是这方面的工作对于一个应用程序往往不能做得“足够好”。如果直到现在你还不明白“我们是不是需要备份我们的数据库”，或者是你认为“数据库会和其它东西一起被备份”，那么你得好好看看这一章的内容了。我们将会说明为什么普通的文件备份方案对于许多MySQL服务是远远不够的，然后我们会介绍备份和恢复一个MySQL数据库的“正确的方法”。 在第一章中，我们设置了一个MySQL服务并通过一个有口令的‘root’来连接这个数据库。MySQL的‘root’用户(顺便提一下，不要与Unix的‘root’用户混淆)对于所有的库和表都有读/写的权限。在许多情况下，我们需要建立其它的只能访问某些数据库和数据表的用户，我们还需要对这种访问进行限制(例如，对指定的表只能直接只读访问)。在这一章中，我们将学习如果使用两个新的MySQL的命令：GRANT和REVOKE来完成这些工作。 在一些情况下，例如由于电源的问题，MySQL数据库可能被损坏。这样的损坏并不总是意味着必须使用备份来恢复。我们将会学习到如果利用MySQL数据库的检查和修复功能来解决简单的数据库损坏。 为什么标准的备份是不够的 和Web服务器一样，绝大多数的MySQL服务器也必须不间断地在线。这使得MySQL数据库的备份显得很重要。因为MySQL服务使用cache和缓冲区来提高对存储在磁盘上的数据库文件更新的效率，所以文件的内容和当前数据库的内容可能并不完全一致。而标准的备份程序仅仅包括对系统和数据文件的拷贝，这种对MySQL数据文件的备份并不能完全满足我们的需要，因为它们不能保证拷贝的文件可以在系统崩溃时的确能够正常地使用。 此外，因为许多的数据库必须整天地接受信息，标准的备份只能提供数据库数据的“瞬间的”映象。如果MySQL数据库文件被损坏，或是变得不可用，在最后一次备份之后添加的信息将会被丢失。在许多情况下，例如对于一个电子商务网站的处理用户订单的数据库，这样的丢失是不可容忍的。 MySQL中的工具可以对数据进行实时的备份，而在备份进行时，不会影响服务的效率。不幸的是，这需要你 为你的MySQL数据配置一个特殊的备份系统。而完全与你已制定的其它数据备份方案无关。然而，和任何一个好的备份系统一样，在你真正用到它的时候，你会发现现在的麻烦是值得的。 在这一章中，我们提供的指导是用于一个运行Linux或其它基于Unix的操作系统的计算机上的。如果你使用的是Windows，方法也基本上一样，只是其中的有些命令必须改动。 使用mysqldump进行数据库备份 除了mysqld以外，MySQL服务器以及mysql(MySQL的客户端)，在安装时还会产生很多有用的程序。例如，在前面，我们已经看到的mysqladmin，就是负责控制和搜集有关正在运行的MySQL服务的信息的程序。 mysqldump是另一个这样的程序。当它运行时，它会连接到一个MySQL服务(就和mysql程序和PHP语言所做的一样)并下载指定的数据库的全部内容。然后它会输出一系列的SQL的CREATE TABLE命令和INSERT命令，在一个空的MySQL数据库中运行这些命令，就可以建立与当初的数据库内容完全一样的一个MySQL数据库。 通过重定向mysqldump的输出到一个文件，你可以存储一个数据库的“镜像”以作为备份。下面的命令是用一个口令为mypass的root用户连接到一个运行在myhost上的MySQL服务，并将名为dbname的数据库的备份存储到dbname_backup.sql文件中：
要恢复这样的一个数据库，只需要运行下面的命令：
第一个命令使用mysqladmin程序建立一个数据库。第二个命令连接到MySQL服务并使用通常的mysql程序，并将刚才得到的备份文件作为其中执行的命令。 通过这种方法，我们可以使用mysqldump建立我们数据库的备份。因为mysqldump通过与MySQL服务的连接产生这个备份，这肯定要比直接访问MySQL数据目录下的数据库文件来得更为安全，因为这样的备份可以确保是数据库的一个有效的拷贝，而不仅仅是数据库文件的拷贝。 剩下来的问题就是如何解决这个“镜像”与一个不断更新的数据库之间的同步。要做到这一点，你需要命令服务保持一个变更日志。 利用变更日志进行增量备份 正如我们前面提到的，在很多情况下，我们使用的MySQL数据库会造成数据的丢失--甚至有的时候会丢失很重要的数据。在这样的情况下，我们必须找到一种方法保持我们使用上面介绍的方法用mysqldump制作的备份与当前数据库之间的同步。而解决方案就是让MySQL服务维持一个更新日志。一个更新日志是一个关于所有数据库接受到的可能改变数据库内容的查询的记录。这将包括INSERT、UPDATE和CREATE 通常的想法是维持一个变更日志，这样当数据库崩溃时，你的恢复过程应该是这样的：首先使用备份(使用mysqldump命令产生)，然后使用备份之后的变量日志。 你也可以使用变更日志撤消错误操作。例如，如果一个合作者告诉你他错误地使用了一个DROP TABLE命令，你可以对变更日志进行编辑以删除这个命令，然后使用备份和修改过的变更日志进行恢复。通过这种方法，你甚至可以保持这次意外事故之后其它表的变化。作为预防措施，你也许还要收回你的合作者的 告诉MySQL服务器维持一个变更日志是非常简单的，你只需要在服务的命令行中增加一个选项：
上面的命令启动MySQL服务，并告诉它在服务器的数据目录下(如果你依照第一章中指导配置你的服务器的话，这个目录将是/usr/local/mysql/var)建立名为update.001、update.002……的文件。一个新的这样的文件将在服务器每一次刷新它的日志文件时被建立(通常，这是指服务每一次重启动时)。如果你想将你的变更日志存储到另一个地方(通常这是一个好主意--如果包含你的数据目录的磁盘出了问题，你肯定不能指望它还能好好保存你的备份！)，你可以指定变更日志的路径。 但是，如果你的MySQL服务器是不间断地工作的，在启动MySQL服务时你也许还需要一些系统配置。在这种情况下，增加一个命令行选择可能变得很困难。建立变更日志的另一个简单的方法是在MySQL配置文件中增加相应的选项。 如果你还不清楚"什么是MySQL配置文件"，不要担心。事实上，在此之前，我们一直没用到过这样的配置文件。要建立这个文件，以我们在第一章中建立的MySQL用户(如果你是完全根据指导做的，这应该是mysqlusr)登录到Linux。使用你习惯的文本编辑器，在你的MySQL数据目录下(除非你选择了其它地方安装MySQL，这应该是指/usr/local/mysql/var)建立一个名为my.cnf的文件。在这个文件中，输入下面一行：
当然，你可以自由地指定你的日志文件所写入的位置。保存这个文件并重启你的MySQL服务。从现在开始，MySQL服务运行的情况将和你在命令行中使用了--log-update选项一样。 很明显，对于一个服务来说，变更日志可能占用大量的空间。因为这个原因以及MySQL不能自动地在建立新的日志文件删除旧的日志文件，你需要自己管理你的变更日志文件。例如，下面的Unix
如果当前的日志文件被删除，最后一步(刷新日志文件)将建立一个新的变更日志，这意味着MySQL服务一直在线，而且在过去的一周中，没有收到任何改变数据库内容的查询。 如果你是一个有经验的用户，使用“时钟守护程序”设置一个脚本来定期(比方说，每周一次)执行数据库的备份并删除旧的变更日志应该是相当简单的。如果你还需要一点帮助，请教你当地的Unix权威。'MySQL' 假定你已经有了一个备份以及在此之后的变更日志的一个拷贝，恢复你的数据库将是非常简单的。在建立一个空数据库后应用我们在上一节中讨论的方法导入备份，然后使用带--one-database命令行选项的mysql命令导入变更日志。这会指示服务器仅仅运行变更日志中与我们想要恢复的数据库(在这个例子中是指dbname)相关的查询：
MySQL访问控制 在这个教程的早些时候，我们曾经提到一个叫做mysql的数据库，在每一个MySQL服务中都包含这个数据库，它是用来保存用户的相关信息、他们的口令以及他们的权限的。但是，在此之前，我们一直使用root用户登录到MySQL服务，这个用户可以访问所有的数据库和数据表。 如果你的MySQL服务仅仅被通过PHP访问，而用你对于将root用户的口令告诉什么人很小心，那么root帐号可能已经足够了。但是，如果一个MySQL服务是被许多人共享的，(例如，一个Web主机希望对它的每一个用户提供同一个MySQL服务)，为不同的用户设置相应的访问权限就显得很重要了。 在MySQL参考手册的第六章中详细介绍了MySQL的访问控制系统。从原理上来说，用户的访问是由mysql数据库中的五个数据表来管理的：user、db、host、tables_priv和columns_priv。如果你想直接使用INSERT、UPDATE和DELETE语句来编辑这些表，我建议你先阅读一下MySQL指南中的相关章节。而从3.22.11版本开始，MySQL提供了简单的方法来管理用户的访问。使用MySQL提供的非标准的命令GRANT和REVOKE，你可以建立用户并赋予其相应的权限，而不必关心它在前面提到的五个表中的存储形式。 使用GRANT GRANT命令用来建立新用户，指定用户口令并增加用户权限。其格式如下：
正如你看到的，在这个命令中有许多待填的内容。让我们逐一地对它们进行介绍，并最终给出一些例子以让你对它们的协同工作有一个了解。 数据库/数据表/数据列权限： 这些权限所涉及到的MySQL的特征，其中的一些我们至今还没看到，而其中的绝大部分是你所熟悉的。 这个命令中可选的WITH GRANT OPTION部分指定了用户可以使用GRANT/REVOKE命令将他拥有的权限赋予其他用户。请小心使用这项功能--虽然这个问题可能不是那么明显！例如，两个都拥有这个功能的用户可能会相互共享他们的权限，这也许不是你当初想看到的。 让我们来看两个例子。建立一个名为dbmanager的用户，他可以使用口令managedb从server.host.net连接MySQL，并仅仅可以访问名为db的数据库的全部内容(并可以将此权限赋予其他用户)，这可以使用下面的GRANT命令：
现在改变这个用户的口令为funkychicken，命令格式如下： 请注意我们没有赋予任何另外的权限(the USAGE权限只能允许用户登录)，但是用户已经存在的权限不会被改变。 现在让我们建立一个新的名为jessica的用户，他可以从host.net域的任意机器连接到MySQL。他可以更新数据库中用户的姓名和email地址，但是不需要查阅其它数据库的信息。也就是说他对db数据库具有只读的权限(例如，SELECT)，但是他可以对Users表的name列和email列执行UPDATE操作。命令如下：
请注意在第一个命令中我们在指定Jessica可以用来连接的主机名时使用了%(通配符)符号。此外，我们也没有给他向其他用户传递他的权限的能力，因为我们在命令的最后没有带上WITH GRANT OPTION。第二个命令示范了如何通过在赋予的权限后面的圆括号中用逗号分隔的列的列表对特定的数据列赋予权限。 使用REVOKE 正如你所预期的那样，REVOKE命令是用来去除一个用户以前被赋予的权限的。命令的语法如下：
这个命令中各部分的功能和在上面的GRANT命令中时一样。要去除Jessica的合作者的DROP权限(例如，如果他经常错误地删除数据库和表)，你可以使用下面的命令：
去除一个用户的登录权限大概是唯一不能使用REVOKE的。REVOKE ALL ON *.*会去除用户的所有权限，但是他还可以登录，要完全地删除一个用户，你需要在user表中删除相应的记录：
访问控制技巧 由于MySQL中访问控制系统工作的方法的影响，在建立你的用户之前你必须知道两个特征。 当建立的用户只能从MySQL服务运行的计算机上登录到MySQL服务(也就是说，你需要他们telnet到服务器并在那里运行MySQL的客户端程序，或者是使用象PHP这样的服务器端脚本语言进行通信)，你大概会问自己GRANT命令的 答案是，你不能依赖其中的任何一种来处理任何连接。从理论上来说，如果用户在连接时(无论是使用mysql客户端还是使用PHP的mysql_connect函数)指定了主机名，这个主机名必须与访问控制系统中的记录匹配。但是因为你也许不想强迫你的用户指定主机名(事实上，mysql客户端的用户也许根本不会指定主机名)，你最好使用下面这种工作环境。 对于用户需要能够从MySQL服务在其上运行的机器上连接MySQL的情况，在MySQL访问控制系统中建立两个用户记录：一个使用实际的主机名(例如，username@www.host.net)，另一个使用localhost(例如，username@localhost)，当然，你需要为两个用户分别grant/revoke所有的权限。 MySQL管理者所要面对的另一个带有普通性的问题是一个其中的主机名使用了通配符的用户记录(例如，前面提到jessica@%.host.net)没起作用。发生这种情况，一般是由于MySQL访问控制系统中记录的优先级的问题。具体地说，越具体的主机名优先级越高(例如，www.host.net是最具体的，%.host.net是比较具体的，而%是最不具体的)。 在一个新安装后，MySQL访问控制系统包含两个匿名用户记录(它允许在当前主机上使用任何用户名进行连接--这两个记录分别支持从localhost连接以及从服务器的实现的主机名进行连接)，以及两个root用户目录。我们上面讨论的情况发生时是由于匿名用户目录的优先级比我们的新记录高，因为他们的主机名更具体。 让我们看看www.host.net上user表的内容，我们假定已经添加了Jessica的记录。数据行是按照MySQL服务在确认连接时的优先级排列的： 正如你看到的，因为Jessica的记录的主机名最不具体，它的优先级最低。当Jessica试图从www.host.net连接时，MySQL服务将他的连接匹配为一个匿名用户记录(空白的User值与任何人匹配)。因为这些匿名记录不需要口令，而也许Jessica输入了他的口令，MySQL将拒绝这个连接。即使Jessica没有输入口令，他可能也只被给予了匿名用户的权限(非常有限)，而不是他原来被赋予的权限。 解决这个问题的方法是，要么你删除匿名用户的记录(DELETE FROM user WHERE User="")，要么再为所有的可能从localhost连接的用户指定两条记录(例如，相对于localhost以及相对于服务器的实际主机名)： 因为要为每个用户维护三个用户记录(以及相应的三套权限)会很麻烦，所以我们推荐你删除匿名用户，除非你需要用他们来完成什么特殊的应用：
被锁在外面？ 就象把钥匙丢失在车上一样，在花费了一个小时安装并调试好一个新的MySQL服务器之后忘记了口令的确是件麻烦事。幸运的是，如果你有访问MySQL运行的计算机的root权限，或者你能够使用运行MySQL服务的用户登录(如果你按照第一章中的指导，这是指mysqlusr)，那么不会出什么问题。按照下面的步骤，你可以获得服务的控制权。 首先，你必须关闭MySQL服务。因为通常使用的mysqladmin需要用到你忘了的口令，你只能通过杀掉服务的进程来完成这项工作。使用ps命令或者看看服务的PID文件(在MySQL数据目录下)，确定MySQL服务的进程的ID，然后使用下面的命令终止它：
这里 关闭了服务之后，你可以通过运行带--skip-grant-tables命令行选项的safe-mysqld (在Windows下使用mysqld或mysqld-nt)命令重启它。这将指示MySQL服务允许自由的访问，明显的，我们应该尽可能地短时间的使用这种模式运行服务，以避免固有的安全风险。 连接成功后，改变你的root口令：
最后，断开连接并指示MySQL服务重载授权表以接收新的口令：
现在一切都好了--甚至不会有人知道你干了什么。就象你虽然将钥匙丢在了车上，而你自己也在车上一样。 检查和修复MySQL数据文件 由于临时断电，使用kill -9中止MySQL服务进程，或者是Jessica的朋友idiot@%.host.net又犯了一个错误，所有的这些都可能会毁坏MySQL的数据文件。如果在被干扰时，服务正在改变文件，文件可能会留下错误的或不一致的状态。因为这样的毁坏有时是不容易被发现的，当你发现这个错误时可能是很久以后的事了。于是，当你发现这个问题时，也许所有的备份都有同样的错误。 MySQL参考手册的第十五章讲述了MySQL自带的myisamchk的功能，以及如何使用它检查和修复你的MySQL数据文件。虽然这一章对于每个想要搭建一个强壮的MySQL服务的人都是推荐阅读的，我们还是有必要在这里对其中的要点进行讨论。 在我们继续之前，你必须意识到myisamchk程序对用来检查和修改的MySQL数据文件的访问应该是唯一的。如果MySQL服务正在使用某一文件，并对myisamchk正在检查的文件进行修改，myisamchk会误以为发生了错误，并会试图进行修复--这将导致MySQL服务的崩溃！这样，要避免这种情况的发生，通常我们需要在工作时关闭MySQL服务。作为选择，你也可以暂时关闭服务以制作一个文件的拷贝，然后在这个拷贝上工作。当你做完了以后，重新关闭服务并使用新的文件取代原来的文件(也许你还需要使用期间的变更日志)。 MySQL数据目录不是太难理解的。每一个数据库对应一个子目录，每个子目录中包含了对应于这个数据库中的数据表的文件。每一个数据表对应三个文件，它们和表名相同，但是具有不同的扩展名。tblName.frm文件是表的定义，它保存了表中包含的数据列的内容和类型。tblName.MYD文件包含了表中的数据。tblName.MYI文件包含了表的索引(例如，它可能包含lookup表以帮助提高对表的主键列的查询)。 要检查一个表的错误，只需要运行myisamchk(在MySQL的bin目录下)并提供文件的位置和表名，或者是表的索引文件名：
上面的两个命令都可以执行对指定表的检查。要检查数据库中所有的表，可以使用通配符：
要检查所有数据库中的所有表，可以使用两个通配符：
如果不带任何选项，myisamchk将对表文件执行普通的检查。如果你对一个表有怀疑，但是普通的检查不能发现任何错误，你可以执行更彻底的检查(但是也更慢！)，这需要使用--extend-check选项：
对错误的检查是没有破坏性的，这意味着你不必担心执行对你的数据文件的检查会使已经存在的问题变得更糟。另一方面，修复选项，虽然通常也是安全的，但是它对你的数据文件的更改是无法撤消的。因为这个原因，我们强烈推荐你试图修复一个被破坏的表文件时首先做个备份，并确保在制作这个备份之前你的MySQL服务是关闭的。 当你试图修复一个被破坏的表的问题时，有三种修复类型。如果你得到一个错误信息指出一个临时文件不能建立，删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的。 这三种修复方法如下所示：
第一种是最快的，用来修复最普通的问题；而最后一种是最慢的，用来修复一些其它方法所不能修复的问题。 检查和修复MySQL数据文件 如果上面的方法无法修复一个被损坏的表，在你放弃之前，你还可以试试下面这两个技巧： 如果你怀疑表的索引文件(*.MYI)发生了不可修复的错误，甚至是丢失了这个文件，你可以使用数据文件(*.MYD)和数据格式文件(*.frm)重新生成它。首先制作一个数据文件(tblName.MYD)的拷贝。重启你的MySQL服务并连接到这个服务上，使用下面的命令删除表的内容： mysql> DELETE FROM tblName; 在删除表的内容的同时，会建立一个新的索引文件。退出登录并重新关闭服务，然后用你刚才保存的数据文件(tblName.MYD)覆盖新的(空)数据文件。最后，使用myisamchk执行标准的修复(上面的第二种方法)，根据表的数据的内容和表的格式文件重新生成索引数据。 如果你的表的格式文件(tblName.frm)丢失了或者是发生了不可修复的错误，但是你清楚如何使用相应的CREATE TABLE语句来重新生成这张表，你可以重新生成一个新的.frm文件并和你的数据文件和索引文件(如果索引文件有问题，使用上面的方法重建一个新的)一起使用。首先制作一个数据和索引文件的拷贝，然后删除原来的文件(删除数据目录下有关这个表的所有记录)。 启动MySQL服务并使用当初的CREATE TABLE文件建立一个新的表。新的.frm文件应该可以正常工作了，但是最好你还是执行一下标准的修复(上面的第二种方法)。 结语 OK，的确这一章没有多少我们通常所习惯的可以具体执行某项实际工作的代码。但是所有的这些工作--备份和恢复数据库，管理MySQL的访问控制系统，数据表的检查和修复--都将有助于我们建立一个经得住时间考验的MySQL数据库服务器。 在本教程的倒数第二章――第九章中，我们会学习一些更复杂的SQL技术以使得我们的关系型数据库服务器可以完成一些你之前也许从未想到过的工作。 |
- 上一篇：MySQL数据库入门学习-安装篇入门篇 (1)
- 下一篇：MYSQL基本数据库术语