MySql的数据目录

数据目录的位置

MySQL数据目录的默认位置已经被编译到MySQL服务器程序里了。

在启动服务器时，通过使用一个--datadir=dir_name选项可以明确指定数据目录位置。把MySQL数据目录安排到其他地方的另一种办法是把它列在MySQL服务器在启动时会读取的某个选项文件里。这样，就不必在每次启动MySQL服务器时都在命令行上写出其数据目录的路径了。

如果你运行多个服务器，你应该知道它们各自的数据目录在什么地方，但是如果你不知道确切的位置，可以用下面几种方法把它查出来：

1. mysqladmin -uroot -p123456 -P3306 -h127.0.0.1 variables | grep datadir

2. show variables like 'datadir';

3. ps axww | grep datadir

4. 查看服务器在启动时所读取的选项文件,通常可以在[mysqld]选项组里看到一个下面这样的datadir行：datadir=/path/to/data/directory

5. 使用find命令搜索数据库文件的办法来查明MySQL服务器的数据目录位置。寻找.frm（数据库定义）文件：find / -name "*.frm" -print

数据目录的结构

MySQL数据目录收录着MySQL服务器所管理的全部数据库和数据表，这些文件被组织成一个树状结构，通过利用Unix或Windows文件系统的层次结构直接实现，即：

（1）每个数据库对应于数据目录下的一个目录。

（2）同一个数据库里的数据表对应于数据库目录中的各有关文件。

这种以目录和文件来实现数据库和数据表的层次化的做法有一个例外，即InnoDB数据表处理程序把所有数据库里的所有InnoDB数据表全部存放在同一个公共表空间里。这个表空间是用一个或者多个非常大的文件而实现的，这些文件将被视为一个连接统一的数据结构，各InnoDB数据表的数据和索引都将存放在这个连接统一的数据结构中。在默认情况下，InnoDB表空间文件也都存放在MySQL数据目录里。

MySQL数据目录还可能包含有其他一些文件，例如：

（1）MySQL服务器的选项文件auto.cnf。

（2）MySQL服务器的进程ID（PID）文件。

（3）MySQL服务器所生成的状态和日志文件。

（4）把DES密钥文件或服务器的SSL证书与密钥文件存放在MySQL数据目录也是常见的做法。

MySQL数据库在文件系统里如何表示

MySQL服务器所管理的每一个数据库都有它自己的数据库目录，这个数据库目录其实是MySQL数据目录中的一个子目录，这个子目录的名字与它代表的数据库名字相同。例如，对应于数据库mydb的数据库目录就是DATADIR/my_db。这种表示方法使MySQL数据库系统中一些与数据库有关的语句实现起来相当简单。

例如，CREATE DATABASE db_name命令将在MySQL数据目录里创建一个名为db_name的空目录。在UNIX系统上，新创建的目录的属主就是启动MySQL服务器时使用的登录账户，并且只能通过该登录账户去访问它。换而言之，CREATE DATABASE命令相当于在使用那个账户登录到MySQL服务器主机上之后执行下列shell命令：

cd DATADIR

mkdir db_name

chmod u=rwx, go-rwx db_name

用一个空目录来代表一个新数据库可以说是最简单的做法了。在其他的数据库系统里，即使是创建一个“空的”数据库也需要创建好几个控制文件或系统文件。

DROP DATABASE语句实现同样简单。DROP DATABASE db_name删除数据库中的db_name目录和所有表文件，这几乎与下列命令一样：

cd DATADIR

rm-rf DATADIR/db_name

DROP DATABASE语句与shell命令之间的区别如下：

1. 如果使用的是DROP DATABASE命令，MySQL服务器仅通过查看有关文件的扩展名删除那些与数据表有关的文件。如果还在数据库目录里创建过其他文件，MySQL服务器将保留这些文件，而且目录本身也不会被删除。
2. InnoDB数据表及其索引的内容都存放在InnoDB表空间里，而不是被存放为MySQL数据目录中的文件。如果某个数据库里包含着InnoDB数据表，就必须使用DROP DATABASE语句才能让InnoDB处理程序从InnoDB表空间里把该数据表删掉，用rm或del命令删除数据库目录的方法对InnoDB数据表是无效的。

MySQL数据表在文件系统里如何表示

MySQL支持以下几种针对不同数据表类型的处理程序：ISAM、MyISAM、MERGE、BOB、InnoDB和HEAP。MySQL中的每一个数据表在磁盘上至少被表示为一个文件，即存放着该数据表的结构定义的.frm文件；大部分数据表类型还有其他几个用来存放数据行和索引信息的文件。这些文件会随着数据表类型的不同而变化。

ISAM数据表

MySQL中最原始的数据表类型就是ISAM类型。在MySQL里，每个ISAM数据表用包含着该数据表的数据库目录里的三个文件来代表。这些文件的基本名与数据表的名字相同，扩展名则分别表明了有关文件的具体用途。例如：名为mytb1的ISAM数据表将被表示为以下三个文件：

mytb1.frm——定义文件，存放着该数据表的格式（结果）定义。

mytb.ISD——ISAM数据文件，存放着该数据表中的各个数据行的内容。

mytb.ISM——ISAM索引文件，存放着该数据表的全部索引信息。

MyISAM数据表

MySQL3. 23版本引入了MyISAM数据表类型作为ISAM类型的后继者，MyISAM数据表类型也要使用三个文件来代表一个数据表，这三个文件的扩展名分别是.frm（结构定义文件）、.MYD（数据文件）和.MYI（索引文件）。

MERGE数据表

.MERGE数据表其实是一个逻辑结构。它代表着由一组结构完全相同的MyISAM数据表所构成的集合；有关的查询命令将把它当作一个大数据表来对待。在数据库目录里，每一个MERGE数据表将被表示为一个.frm文件和一个.MRG文件，.MRG文件其实就是一份由各MyISAM数据表的名单构成的MERGE数据表。

BDB数据表

BDB处理程序用两个文件来代表每个数据表，其一是用来存放数据表结构定义的.frm文件，其二是用来存放数据表的数据和索引信息的.db文件。

InnoDB数据表

上述几种数据表类型都是用多个文件来表示一个数据表的。InnoDB数据表与它们有所不同。与一个给定InnoDB数据表直接对应的文件只有一个，即数据表的.frm结构定义文件，这个文件存放在包含着数据表的数据库目录里。所有InnoDB数据表的数据和索引都被存放到同一个专用的表空间里统一管理。一般来说，这个表空间本身将被表示为MySQL数据目录里的一个或者多个大文件。构成表空间的这些文件将形成一个在逻辑上连续不断的存储区域，表空间的总长度等于各组成文件的长度之和。

HEAP数据表

HEAP数据表是创建在内存中的数据表。因为MySQL服务器把HEAP数据表的数据和索引都存放在内存里而不是存放在硬盘上，所以除相应的.frm文件外，HEAP数据表在文件系统里根本没有相应的代表文件。

SQL语句如何映射为数据表文件操作

每一种数据表类型都要使用一个.frm文件来保存数据表的结构定义，所以，应用show table my_name命令所得到的输出结果，与列出数据库目录db_name中所有.frm文件基本名所得到的结果是相同的。有些数据库系统使用一个注册表来记录某数据库里的所有数据表，但MySQL没有这样做，因为，系统不需要这样设置，MySQL数据目录的层次结构已经把“注册表”隐藏在其中了。

创建数据表

要想创建一个MySQL所支持的任意类型的数据表，需要发出一条CREATE TABLE语句定义数据表的结构。无论哪一种数据表类型，MySQL服务器都将创建一个.frm文件来保存数据表的结构定义的内部编码。MySQL服务器还会根据指定数据表的具体类型创建出其他必要的文件。例如，它将为一个MyISAM数据表创建出一个.MYD数据文件和一个.MYI索引文件；为一个BDB数据表创建出一个.db数据/索引文件。对于InnoDB数据表，InnoDB处理程序将在InnoDB表空间里为数据表初始化一些数据和索引信息。在UNIX系统上为新数据表而创建的各个文件的属主和存在模式将被设置为只允许用来运行MySQL服务器的账户进行访问。

更新数据表

当发出一条ALTER TABLE tbl_name语句时，MySQL服务器将对有关数据表的.frm文件重新进行编码以反映出这条语句所表明的结构性变化，还要对有关的数据文件和索引文件的内容进行相应的修改。CREATE INDEX和DROP INDEX也是一样能引起类似的动作，因为MySQL服务器在内部是把它们当作等效的ALTER TABLE语句来处理的。改变InnoDB数据表的结构会引起InnoDB处理程序修改InnoDB表空间中数据表的数据，同时也对索引做出相应的修改。

删除数据表

DROP TABLE语句是通过删除代表该数据表的各种有关文件而实现的。丢弃一个InnoDB数据表将是数据表在InnoDB表空间里占用的空间被标注为“未使用”。

对于某些数据表类型，可以通过在相应的数据库目录里删除与数据表有关的各个文件的办法来手动删除这个数据表，例如，假设mydb是当前数据库，mytb1是一个ISAM、MyISAM、BOB或MERGE数据表，那么drop table mytb1语句就大致等效于下面这两条UNIX命令：

cd DATADIR

rm -f mydb/mytb1.*

对于InnoDB或HEAP数据表，因为它们的某些组成部分在文件系统里没有实体性的文件来代表，所以针对这两种数据表类型的DROP TABLE语句没有等效的文件系统级命令。例如，InnoDB数据表在文件系统里只有一个相应的.frm文件，用文件系统级命令删除这个文件将使该数据表在InnoDB表空间数据和索引成为“流离失所的孤儿”。

操作系统对数据库和数据表命名的限制

MySQL对数据库和数据表的命名有其自己的一套命名规则，下面是命名中的几个要点：

（1）名字可以由当前字符集中的字母和数字字符以及下划线（_）和美元符号（＄）构成。

（2）名字的最大长度是64个字符。

从MySQL3.23.6版本开始，其他字符也可以出现在名字里，但它们必须用反引号引起来。如果想把保留字用做数据列的名字，那最好用反引号把它们引起来。可是，因为数据库和数据表的名字将被MySQL用做相应的目录和文件的基本文件名，所以数据库和数据表的名字往往还要遵守MySQL服务器在其运行的操作系统中的文件系统命名规则的限制，譬如（$在linux是特殊字符，大小写敏感）。

数据目录的结构对系统性能的影响

MySQL数据目录的结构很容易理解，因为它以一种相当自然的方式使用文件系统的层次结构。但这种结构对性能也有着隐含的影响。因为每打开一个数据表都需要打开一个或者多个与该数据表相关联的文件，所以这种层次结构对性能往往会产生很大的影响。

MySQL数据目录的结构繁多，这将导致数据表处理程序需要使用多个文件来代表一个数据表，而打开一个数据表需要用到多个而不是一个文件描述符。MySQL服务器智能化地将文件描述符缓存起来，但一个繁忙的MySQL服务器仍有可能因为需要同时向多个客户连接提供服务器，或者执行一些涉及多个数据表的复杂查询而耗尽文件描述符，这很可能会是一个问题。因为文件描述符在许多系统，尤其是那些把每个进程所能使用的文件描述符个数（即允许每个进程打开的文件个数）设置得相当低的系统上都是一种稀缺的资源。

用多个文件来代表一个数据表的做法还会导致数据表打开操作的时间随查询命令所涉及的数据表个数的增加而延长。因为MySQL里的数据表打开操作将被映射为操作系统中的无连接打开操作，所以系统目录搜索例程的执行效率就会影响到数据表打开操作的执行效率。这一般算不上是个问题，但如果数据库里有很多个数据表的话，这个因素就很值得考虑了。例如，我们知道，每个MyISAM数据表都将被表示为3个文件。假如数据库里有10000个MyISAM数据表，则数据库目录就将有30000个文件，用户就肯定会注意到文件打开操作明显地慢了下来。（Linux ext2和Solaris文件系统会遇到这个问题）。如果系统性能因此而下降到足以引起关注的程度，就应该考虑换用一种能够高效率地处理大量文件的文件系统。例如，ReiserFS文件系统就是一种在存在大量小文件的情况下，依然有着良好性能的文件系统。如果系统无法换用另一种文件系统，就应该根据应用软件的具体情况重新考虑那些数据表的结构，对数据表的结构做出必要的优化调整。例如，如果应用软件要为每个用户分别创建一个数据表，数据库里就会有很多结构完全相同的数据表。要想把这些数据表合并为一个数据表，可能需要增加一个数据列以表明各有关数据行都对应于哪一位用户。如果能大大减少数据表的数量，应用软件的性能就会有明显的改善。

用尽可能少的文件创建出多个数据表的办法是使用InnoDB数据表。每个InnoDB数据表只有一个与之相关联的.frm文件，所有InnoDB数据表的数据和索引信息都将存放在InnoDB表空间里。这就大大减少了用来表示InnoDB数据表的磁盘文件的数量，进而减少了打开一个数据表时需要用到的文件描述符的个数。当用到InnoDB数据表的时候，InnoDB处理程序会先打开构成InnoDB表空间的各个组成文件（InnoDB表空间由几个组成文件构成，就要用到几个文件描述符；但这个数字在服务器运转期间将是一个常数）；然后，每打开一个InnoDB数据表，InnoDB处理程序就要用一个文件描述符去打开该表的.frm文件，每关闭一个InnoDB数据表，与该数据表相关联的文件描述符就会被释放。

MySQL状态文件和日志文件

除数据库目录外，MySQL数据目录里还包含许多状态文件和日志文件，这些文件默认存放位置是相应的MySQL服务器的数据目录，其默认文件名是在服务器主机名上增加一些后缀而得到的。

1.进程ID文件

MySQL服务器会在启动时把自己的进程ID写入PID文件，等运行结束时又会删除该文件。PID文件是允许服务器本身被其他进程找到的工具。例如，如果运行mysql.server，在系统关闭时，关闭MySQL服务器的脚本检查PID文件以决定它需要向哪个进程发出一个终止信号。

2.MySQL日志文件

MySQL能够维护多个不同的日志文件。大多数日志功能都是可选的；不仅可以在启动MySQL服务器时利用各种启动选项来激活日志（对不常用的日志可以不启用），还可以指定日志文件的名字。

（1）常规日志记录服务器操作的综合性信息；哪些人正从哪些地方试图连接MySQL服务器、他们发出哪些查询命令等。

（2）变更日志记录查询命令信息，但它只记录那些对数据库内容做出可修改的查询命令。变更日志的内容是一些SQL语句，可以把这些语句提供给mysql客户程序作为输入以执行。

（3）二进制变更日志与变更日志作用相同，但其内容是用效率更高的二进制格式写出来的。附属的二进制日志索引文件列出了MySQL服务器当前正在维护着哪些二进制日志文件。

变更日志和二进制变更日志主要用于MySQL数据库系统的崩溃恢复工作中。在发生系统崩溃后，先用备份文件把数据库恢复到当初进行备份时的状态，再把变更日志或二进制变更日志的内容写入MySQL服务器，让它再次执行日志中记载的各种修改操作，把数据库恢复到崩溃发生时所处的状态。

例如：下面的日志记录了在test数据库中创建一个my_tabl表，插入一行数据，然后删除表的会话：

990509 7：37：09 492 Connect Paul@localhost on test

492 Query CREATE TABLE my_tbl（val INT）

492 Query INSERT INTO my_tbl values（1）

492 Query DROP TABLE my_tbl

492 Quit

常规日志中的信息由以下几项内容组成：日期/时间、服务器线程（连接）ID、事情的类型、事情的具体信息。如果后一事件与前一事件的发生日期/时间相同，则后一事件在常规日志中的记录项将省略日期/时间字段。

同一个会话出现在更新日志中看上去如下所示：

use test；

CREATE TABLE my_tbl（val int）；

INSERT INTO my_tbl VALUES（1）；

DROP TABLE my_tbl；

（4）对更新日志，用--log-long-format选项获得一个扩展形式的日志，扩展日志提供有关谁何时发出每一条查询，这使用更多的磁盘空间，但如果你想知道谁在做什么，而不用将更新日志对照一般日志的内容找到连接事件。对上面的会话，扩展更新日志产生这样的信息：

#Time：990507 7：32：42

#User@Host：paul[paul]@localhost[]

use test；

CREATE TABLE my_tbl（val int）；

#User@Host：paul[paul]@localhost[]

INSERT INTO my_tbl VALUES（1）；

#Time：990507 7：32：43

#User@Host：paul[paul]@localhost[]

DROP TABLE my_tbl；

ps:新增加的记载内容都写在了以“#”字符开头的行上。这样，当把变更日志馈入mysql客户程序以传递给MySQL服务器去执行时，它们将被解释为注释。

（5）错误日志记载着MySQL服务器在发生异常情况时生成的诊断信息。如果MySQL服务器启动失败或意外退出，通常可以从这个日志里了解到其原因。

日志文件的尺寸有可能变得非常大，一定要保证它们不至于填满文件系统。可以定期使一些日志文件失效以保证它们使用的空间总量不会超过一定的界限。

因为日志文件中记载的查询命令里可能会有口令之类的敏感信息，所以应该注意加强日志文件的安全保护工作，避免它们遭受意外破坏或者被无关用户读取。例如，下列日志项里就用root用户的口令，这一定不是想让任何人看到的信息：

990509 7：23：31 4 Query UPDATE user SET

Password=PASSWORD（"secret"）

WHERE user="root"

在默认的情况下，日志将被写到MySQL数据目录里，所以加强日志文件的安全保护工作的主要措施之一就是不让MySQL管理员的登录账户进入并看到MySQL数据目录里的内容。