8.2　MySQL管理

包含在MySQL发行版中的一些有用的工具程序使管理工作变得相当容易。它们中最常用的是mysqladmin程序。我们将在本节中介绍这个程序以及其他一些工具。

8.2.1　命令

除mysqlshow命令以外，所有的MySQL命令都接受表8-1所示的3个标准参数。

表　8-1

我们再次建议你不要把密码放在命令行上，因为它可以被ps命令看到。

1．myisamchk命令

myisamchk工具是设计用来检查和修复使用默认MYISAM表格式的任何数据表，MYISAM表格式由MySQL自身支持。通常情况下，myisamchk应该以安装时创建的mysql用户身份来运行，并且运行该命令时应该位于数据表所处的目录中。为了检查数据库，首先执行命令su mysql，然后改变目录到与数据库名称对应的目录下，使用表8-2中推荐的一个或多个选项来运行myisamchk。例如：

myisamchk -e -r *.MYI

myisamchk最常见的命令选项见表8-2。

表　8-2

为获得更多信息，我们可以不带任何参数的调用myisamchk命令以查看更多的帮助信息。这个工具对InnoDB类型的数据表没有效果。

2．mysql命令

这是MySQL一个主要的且功能非常强大的命令行工具。几乎每个管理或用户级别的任务都可以在这里执行。你可以从命令行启动mysql，通过在命令行的最后添加数据库名称作为参数，你就无需在MySQL的控制台中使用use <database>命令。例如，以用户名rick、提示输入密码（注意-p参数后面有一个空格）、默认使用数据库foo来启动控制台的命令如下所示：

$ mysql -u rick -p foo

你可以使用mysql -help | less命令来逐页查看mysql控制台的其他命令行选项列表。

如果在启动MySQL时未指定数据库，你可以在MySQL中使用use <databasename>选项来选择一个数据库，正如表8-3的命令列表显示的那样。

表　8-3

另外，你还可以通过非交互模式来运行mysql，只需捆绑命令到一个输入文件中并从命令行读取它即可。在这种情况下，你必须在命令行上指定密码：

$ mysql -u rick —password=secretpassword foo < sqlcommands.sql

一旦mysql读取并处理完命令，它就将返回到命令提示符。

当mysql客户端连接到服务器后，除了标准的SQL92命令集以外，还有一些特定的命令也会被mysql支持，如表8-3所示。

这个命令集中一个非常重要的命令是use。mysqld服务器支持同时拥有许多不同的数据库这一想法，所有的数据库都由同一个服务器进程来服务和管理。许多其他数据库服务器，如Oracle和Sybase，使用术语schema（方案），而MySQL最经常使用的术语是database（MySQL查询浏览器使用的是术语schema）。每个数据库（在MySQL的术语中）都是一个基本独立的表格集。这使得你可以针对不同的目的建立不同的数据库，并为每个数据库指定不同的用户，而只需要使用同一个数据库服务器就可以有效地管理它们了。只要拥有适当的权限，你就可以通过使用use命令在不同的数据库之间进行切换。

特定数据库mysql是由MySQL安装自动创建的，它用于保存如用户和权限这样的数据。

SQL92是使用最广泛的一个ANSI SQL标准版本。它为SQL数据库的工作方式、不同数据库产品之间的互操作和通信创建一致的标准。

3．mysqladmin

这是快速进行MySQL数据库管理的主要工具。除了常见的参数以外，它还支持如表8-4所示的命令。

表　8-4

如果不带参数调用mysqladmin命令，我们就可以从命令提示符下看到完整的选项列表。你也许想使用| less来分页显示。

4．mysqlbug

如果运气好的话，你将不会有机会使用这个命令。顾名思义，这个工具生成一个用于发送给MySQL维护者的错误报告。在发送它之前，你可能希望编辑生成的文件以提供对开发者可能有用的其他信息。

5．mysqldump

这是一个极其有用的工具，它允许你以SQL命令集的形式将部分或整个数据库导出到一个单独文件中，该文件能被重新导入MySQL或其他的SQL RDBMS。它接受标准用户和密码信息作为参数，也接受数据库名和表名作为参数。表8-5中列出的其他选项大大扩展了这个工具的功能。

表　8-5

默认情况下，mysqldump将数据发送到标准输出，而你一般都是希望把它重定向到文件。

这个工具对于迁移数据或快速备份非常有用。此外，由于MySQL的客户端服务器实现方式，通过使用一个安装在不同机器上的mysqldump客户端，它甚至可以用来实现远程备份。下面这个例子显示了通过用户名rick进行连接，转储数据库myplaydb的例子：

在我们的系统上，myplaydb数据库中只有一个表，结果文件如下所示：

6．mysqlimport

mysqlimport命令用于批量将数据导入到一个表中。通过使用mysqlimport，你可以从一个输入文件中读取大量的文本数据。这个命令唯一的参数需求是一个文件名和一个数据库名。mysqlimport将把数据导入到数据库中与文件名（不包括任何文件扩展名）相同的表中。你必须确认文本文件与将要填入数据的表拥有相同的列数，并且数据类型是兼容的。在默认情况下，数据应以tab分隔符分开。

正如我们前面提到的那样，我们也可以通过一个文本文件来执行SQL命令，只需运行mysql命令，并将输入重定向到一个文件即可。

7．mysqlshow

这个小工具能够让你快速了解MySQL安装及其组成数据库的信息。

❑　不提供参数，它列出所有可用的数据库。

❑　以一个数据库为参数，它列出该数据库中的表。

❑　以数据库和表名为参数，它列出表中的列。

❑　以数据库、表和列为参数，它列出指定列的详细信息。

8.2.2　创建用户并赋予权限

作为MySQL管理员，最常见的工作就是维护用户信息——在MySQL中添加和删除用户并管理他们的权限。从MySQL 3.22开始，我们可以通过在MySQL控制台中使用grant和revoke命令来管理用户权限——与在以前版本中必须通过直接编辑特权表来管理用户相比，这项任务变得轻松了很多。

1．grant命令

MySQL的grant命令几乎完全遵循SQL92的语法，尽管不是非常严格。它的常规格式是：

可以授予的特权值如表8-6所示。

表　8-6

一些命令还有其他选项。例如，create view授予用户创建视图的权限。要想了解最权威的权限列表，请查阅MySQL版本的文档，因为每一个新的MySQL版本都会对这一领域进行扩展。还有一些特殊的管理权限，但我们在这里并不关注它们。

授予特权的对象被标识为：

databasename.tablename

在Linux传统中，代表的是通配符，因此.代表每个数据库中的每个对象，而foo.代表数据库foo中的每个表。

如果指定的用户已经存在，他的特权会被编辑以反映你所做的修改。如果该用户不存在，他就会以指定的特权被创建。正如你前面看到的那样，用户可以被指定为来自某个特定的主机。你应该在同一个命令中同时指定用户和主机，以便灵活获得MySQL权限配置。

在SQL语法中，特殊字符%代表通配符，它与shell环境中*号的作用完全一样。你当然可以为每个期望的特权使用单独的命令，但是如果你想授予用户rick从wiley.com域中任何主机访问的权限，可以把rick描述为：

rick@'%.wiley.com'

任何时候使用%通配符都必须把它放在引号中，以与其他文本分开。

你还可以使用IP/网络掩码标识（N.N.N.N/M.M.M.M）来为访问控制设置一个网络地址。

正如我们之前使用rick@'192.168.0.0/255.255.255.0'来授予rick从本地网络中任何机器连接的特权那样，我们也可以指定rick@'192.168.0.1'来将rick的访问限制到一台工作站，或指定rick@'192.0.0.0/255.0.0.0'来扩大范围以包括192这个A类网络中的所有机器。

下面是另外一个例子：

mysql> GRANT ALL ON foo.* TO rick@ '%' IDENTIFIED BY 'bar';

这将创建用户rick，他拥有对数据库foo的所有权限，并能以初始密码bar从任何机器进行连接。

如果数据库foo尚未存在，那么用户rick现在将拥有使用SQL命令create database来创建该数据库的权限。

IDENTIFIED BY子句是可选的，但在创建用户的同时最好确保他们都设置有密码。

你需要格外小心在用户名、主机名或数据库名中包含下划线的情况，因为SQL中的下划线是一种匹配任意单个字符的模式，这与%匹配一个字符串非常类似。因此只要有可能，请尽量不要在用户名和数据库名中包含下划线。

一般来说，with grant option只会用于创建二级管理员。但是，它也可以用于允许一个新创建的用户将授予他的特权赠予其他用户。所以请始终谨慎地使用with grant option。

2．revoke命令

当然，管理员不仅可以授予用户权限，同样也能够剥夺用户权限。这是通过revoke命令来完成的：

revoke <a_privilege> on <an_object> from <a_user>

这与grant命令的格式极其相似。例如，

mysql> REVOKE INSERT ON foo.* FROM rick@'%';

但是，revoke命令不能删除用户。如果想要完全删除一个用户，不要只是修改他们的权限，而应用revoke来删除他们的权限。然后，你就可以切换到内部的mysql数据库，通过从user表中删除相应的行来完全删除一个用户：

因为未指定主机，所以我们就可以确保删除了我们想要删除的MySQL用户（在本例中是rick）的每个实例。在完成了这个之后，请一定要返回你自己的数据库（使用use命令），否则你仍然在MySQL自己的内部数据库中。

请理解delete与grant和revoke并不属于同一范畴。由于MySQL处理权限方式的需要，这里的SQL语法是必需的。你是通过直接更新MySQL的权限表（因此首先调用命令use mysql）来有效地完成修改的。

在更新表之后，你必须使用命令FLUSH PRIVILEGES来告诉MySQL服务器，它需要重载它的权限表，正如上面例子中显示的那样。

8.2.3　密码

如果想为尚未拥有密码的用户指定密码，或者希望改变自己或别人的密码，你就需要以root用户身份连接到MySQL服务器，然后直接更新用户信息。例如：

你会得到如下的一个列表：

如果想给用户foo指定密码bar，则可以这样做：

mysql> UPDATE user SET password = password('bar')WHERE user = 'foo';

再次显示user表中的相关列：

很显然，用户foo现在有一个密码了。请不要忘记返回你原先的数据库。

从MySQL 4.1开始，密码机制已经被更新过了。但是，考虑到向后兼容性，你仍然可以使用函数OLD_PASSWORD（‘要设置的密码’）来通过老的算法设定密码。

8.2.4　创建数据库

下一步工作就是创建数据库。假设你想要一个名为rick的数据库，还记得你已用同样的名字创建了一个用户。首先，需要授予用户rick广泛的权限以允许他创建新的数据库。这样做对一个开发系统尤其有用，因为它可以让用户有更大的灵活性。

mysql> GRANT ALL ON . TO rick@localhost IDENTIFIED BY 'secretpassword';

现在以rick用户身份登录并创建数据库来测试权限设置：

告诉MySQL我们想使用新的数据库：

mysql> use rick

现在，你可以向数据库中添加你想要的表和信息了。在以后的登录中，你可以在命令行的结尾指定数据库，而不需要再使用use命令了：

$ mysql -u rick -p rick

在按照提示输入密码之后，作为连接过程的一部分，在默认情况下，你将自动切换到使用数据库rick。

8.2.5　数据类型

现在，你有了一个可以运行的MySQL服务器、一个安全的用户登录和一个准备好使用的数据库。接下来需要做什么呢？你需要创建一些包含列的表来保存数据。但是，在此之前，你需要了解MySQL支持的数据类型。

MySQL的数据类型非常标准，因此在这里我们将仅仅简要地浏览主要的类型。一如往常，MySQL网站上的MySQL手册对此进行了更为详细的讨论。

1．布尔类型

可以用关键字BOOL来定义布尔列。正如你所期望的那样，它将持有TRUE和FALSE值。它也可以持有特殊的数据库“未知”值NULL。

2．字符类型

如表8-7所示，有多种字符类型可供选择。前3个是标准的，后3个是MySQL特有的。我们建议在满足实际使用要求的前提下，尽量坚持使用标准类型。

表　8-7

3．数值类型

数值类型分为整型和浮点型，如表8-8所示。

表　8-8

一般情况下，我们建议你坚持使用INT、DOUBLE和NUMERIC类型，因为它们最接近于标准的SQL类型。其他类型是非标准的，如果你将来需要移动数据，其他数据库系统中可能不支持这些类型。

4．时间类型

有5种时间数据类型可供使用，如表8-9所示。

表　8-9

请注意，当比较DATE和DATETIME值以了解时间部分是如何处理的时候，你需要格外小心，你可能会看到非期望的结果。详细信息请查阅MySQL的手册，因为不同版本的MySQL其行为稍有不同。

8.2.6　创建表

至此，你已运行了数据库服务器，了解了如何分配用户权限以及如何创建数据库和一些基本的数据库类型，现在你可以开始创建表了。

一个数据库表只不过是一系列的行，而每行又由固定数目的列组成。它非常像电子表格，除了每行都必须包含相同数目和类型的列，而且每行必须以某种方式不同于表中的其他行。

只要合乎情理，一个数据库可以包含的表格数是不受限制的。但是，很少有数据库需要100个以上的表，对于大多数小系统来说，25个左右的表通常就足够了。

创建数据库对象的完整SQL语法被称为DDL（data definition language，数据定义语言）。它相当复杂，要想在一章的内容中全面介绍该语法是很困难的，关于它的详细内容可以在MySQL网站的文档区找到。

创建表的基本语法是：

你可以用DROP TABLE语法来删除表，这非常简单：

DROP TABLE <table_name>

就目前而言，你仅需要了解少数几个关键字就可以完成表的快速创建了，这几个关键字如表8-10所示。

表　8-10

实　验　创建表并添加数据

观看实践中表的创建要比学习基本语法简单得多，所以现在让我们来创建一个名为children的表。它将为每个孩子存储一个唯一的数字、名和年龄。我们把孩子的编号作为主键。

（1）你需要的SQL命令是：

注意，与大多数程序设计语言不同，列名（childno）出现在列数据类型（INTEGER）之前。

（2）你还可以使用另外一种语法将列定义和主键定义分开，下面的交互式会话显示了这一语法：

请注意我们是如何跨越多行输入SQL语句的，MySQL用->提示符来表示我们位于延续的行上。同样请注意，正如我们之前提到的那样，我们使用分号结束SQL命令，表示我们已经完成输入并准备好让数据库处理请求了。

如果出现了错误，MySQL允许回退到之前的命令，编辑它并通过按下回车键重新输入它。

（3）现在可以向表中添加数据了。我们使用SQL命令INSERT来添加数据。因为我们定义childno列为AUTO_INCREMENT列，所以不需要为此列提供数据，我们只需让MySQL分配一个唯一的数字。

我们可以使用SELECT从表中提取数据来检查数据是否被正确添加了：

与明确的列出我们想选择的列相比，你也可以使用星号（*）代表列，这将列出表中的所有列。这对交互式的使用会很方便，但在产品代码中，你应该始终明确地指定你想要选择的列。

实验解析

你启动了一个对数据库服务器的交互式会话，并切换到rick数据库。然后，你输入SQL命令创建表，使用满足需要的行来创建列。一旦使用分号结束了SQL命令，MySQL就将创建表。使用INSERT语句添加数据到新表中，允许childno列被自动分配数字。最后，使用SELECT来显示表中的数据。

我们在本章中没有足够的篇幅来介绍SQL的所有细节，更不用说讨论数据库设计了。关于SQL的更多信息请访问www.mysql.com。

8.2.7　图形化工具

在命令行中操作表和数据是很好，但是如今很多人更喜欢使用图形化工具。

MySQL有两个主要的图形化工具：MySQL管理器（MySQL Administrator）和MySQL查询浏览器（MySQL Query Browser）。这些工具的具体软件包名称取决于你所使用的Linux发行版。例如，RedHat发行版中对应的软件包名称是mysql-gui-tools和mysql-administrator。对Ubuntu来说，你可能需要首先启用Universe库，然后再查找mysql-admin。

1．MySQL查询浏览器

查询浏览器是一个相当简单、但又很有效的工具。安装它之后，你可以通过GUI菜单调用它。执行它之后，你会看到一个登录窗口要求你提供连接的详细信息，如图8-4所示。

图　8-4

如果你是在和服务器同一台的机器上运行它，你只需在Server Hostname处输入localhost即可。

一旦连接上服务器，你将看到一个简单的GUI界面，如图8-5所示。它允许你在一个GUI shell中执行查询命令、提供图形化编辑的所有优越性、一个图形化的编辑表格中数据的方式和一些针对SQL语法的帮助屏幕。

图　8-5

2．MySQL管理器

我们强烈建议你尝试一下MySQL管理器。它是一个针对MySQL的功能强大、稳定和易于使用的图形化接口。它针对Linux和Windows都提供了预编译的版本（如果你需要的话，它甚至还提供了源代码）。它允许你通过一个GUI界面同时完成管理MySQL服务器和执行SQL命令的工作。

执行MySQL管理器时，你将看到一个与MySQL查询浏览器的连接窗口非常相似的窗口。在输入详细信息之后，你将看到一个主控页面，如图8-6所示。

图　8-6

如果想要通过Windows客户端来管理MySQL服务器，你可以从MySQL网站上的GUI工具部分下载Windows版本的MySQL管理器。在撰写本书的时候，该网站的下载页面包含管理器、查询浏览器和一个数据库迁移工具。Windows版本的状态窗口见图8-7，你可以看到，它几乎和Linux版本完全一样。

图　8-7

请记住，如果你一直按照本章中的要求在做，那么你已对MySQL服务器的安全进行了加固，root用户只能从localhost进行连接，而不能从网络中的任何其他机器进行连接。

一旦MySQL管理器已运行了，你就可以浏览一下它的不同配置和监控选项。它是一个非常易于使用的工具，但我们在本章中没有足够的篇幅来详细介绍它了。