《MySQL技术》学习笔记——MySQL数据目录

MySQL数据目录

默认下，MYSQL服务器mysqld会把它管理的所有信息存储在一个被称为MySQL数据目录的地方。
数据目录存储着所有的数据库，及提供服务器运作信息的状态文件和日志。
１．数据目录位置。
２．服务器如何组织和提供对其管理的那些数据库和表的访问。
３．服务器会生成哪些状态文件和日志，及它们包括的内容。
４．如何改变数据库目录的默认位置或组织结构。

数据目录位置

对Unix，二进制或源代码发行版，通常为 /usr/local/mysql/data。

如果想在启动服务器时指定数据目录位置，则可用–datadir=dir_name选项。或修改选项文件。

在Unix上查看/etc/my.cnf 文件时，若有：

[mysqld]
datadir=xxx

如果你还不清楚服务器在什么地方寻找这些选项文件，那么可以调用下面这条命令，并查看帮助消息，在其中的开头部分边列出了选项文件的各个存放位置：

% mysqld --verbose --help

如果服务器正在运行，那么可以连接到它，向它询问数据目录的位置。

使用如下语句查看变量值。

% SHOW VARIABLES LIKE 'datadir';

% mysqladmin variables

数据目录结构

MySQL的数据目录存放着服务器管理的所有数据库。
一般情况下，它是一个树形结构，是直接利用Unix/Windows的文件系统层次结构实现的。

• 每个数据库在数据目录下都对应有一个数据库目录。
• 数据库里的表，视图和触发器都对应于数据库目录中的文件。
  某种给定的存储引擎完全可使用另一种不同的存储结构——它可以不同于常见的基于目录和文件实现的数据库层次结构。
  例如，InnoDB可把所有数据库的InnoDB表存储在一个公共表空间里。
  该表空间可包含一个或多个大文件，这些文件会被当成一个单独统一的数据结构，其中还表示了表和索引。
  默认下，InnoDB会把表空间文件存储在数据目录里。

数据目录也可包含其他文件。

• 服务器进程ID（PID）文件。
  服务器启动时，将自己的进程ID写入文件，以便其他程序在需向服务器发信号时可找到这个值。
• 服务器生成的状态文件和日志文件。
  这些文件提供了与服务器运作有关的重要信息，对管理员来讲很有价值。
  如果服务器的配置是把日志信息记录到数据库表，而非记录到文件，则这些日志表将位于mysql数据库里。
• 服务器相关的文件，如DES秘钥文件，服务器的SSL证书和秘钥文件。
  大部分的管理员会把数据目录作为这些文件的存放位置。

MySQL服务器提供的数据访问方式

以常见的"客户端／服务器"结构使用MySQL时，数据目录下的所有数据库都会由一个实体（即MySQL服务器mysqld）来管理。

客户端程序不会直接操作数据。
服务器是访问数据库的唯一联络点。

当服务器启动时，会打开你请求它维护的日志文件，然后监听各类型的网络连接，对外提供一个通向数据目录的网络接口。

客户端需建立一个与服务器的连接，然后用SQL语句传递请求，执行所想要的操作，如创建表、查询行或更新行。
服务器处理请求，向客户端返回结果。

服务器支持多线程，可同时为多个客户端提供连接服务。
服务器实际会把各个请求排列起来，依次执行。

对MyISAM表的维护，故障诊断，修复，压缩操作，可使用像myisamchk那样的直接访问维护工具。
这些工具会更改表的内容，在服务器修改表的同时使用，可能导致表的损坏。
在运行这些表处理工具程序前，先停止服务器。

数据库在文件系统里的表示

MySQL服务器管理的每个数据库都有其自己的数据库目录。
数据库目录是以数据库目录的子目录形式存在的，而且每一个目录的名称与它所表示的数据库名相同。
SHOW DATABASES 语句可以列出位于数据目录里的目录名称，
CREATE DATABASE db_name 语句会在数据目录下创建一个名为 db_name 的数据库目录。

它还会在数据目录下创建一个名为db_name的数据库目录，还会在这个数据库目录里创建一个名为db.opt的文件，其中列出了数据库的各种属性，如默认字符集和排序规则。

Unix系统里，数据库目录只隶属于运行服务器的那个登录账户，且只能被它访问。

DROP DATABASE db_name会删除数据目录里的db_name目录，及该目录里的所有表文件和其他数据库对象（如视图或触发器）文件。

DROP DATABASE与文件系统命令的区别：

• DROP DATABASE只会根据表和其他数据库对象所使用的文件扩展名来删除文件。
  如果你在数据库目录里创建了其他文件或子目录，则服务器不会删除它们。
  此时，数据库目录不会被删除，并且DROP DATABASE会返回一个错误。
  这种情况的一种后果是，该数据库的名字仍会显示在SHOW DATABASE语句的输出结果里。
  可先手动删除多余的文件和子目录，然后再次执行DROP DATABASE语句。
• 删除数据库目录的办法无法安全地删除数据库里的InnoDB表。
  对于每一个InnoDB表，InnoDB存储引擎会在其系统表空间里维护一个数据字典条目，并且也顺便会将该表的内容存储在那里。
  如果数据库含InnoDB表，则需用DROP DATABASE来删除，这样才能确保InnoDB存储引擎会更新其数据字典，并从表空间删除该表的内容。

表在文件系统里的表示

对每一个表，MySQL在磁盘上至少会使用一个文件来表示，即为.frm格式文件，其中含表结构的描述。
服务器会负责创建.frm文件，而各个存储引擎会创建一些附加文件，用于保存数据行和索引信息。

InnoDB是默认存储引擎。
在数据库目录里，每个InnoDB表有一个.frm文件，其中含了表结构的定义。
对表的内容，InnoDB提供两种表示方式，都基于表空间实现的。

• 系统表空间。
  表空间由数据目录里一个或多个大文件构成。
  表空间的这些组成文件共同形成了一个在逻辑上连续不断的存储区域，其大小为各个组成文件大小之和。
  默认情况下，InnoDB会将其表存储到此系统表空间里。
  对此类表，唯一与表特定相关的文件是.frm文件。
• 独立表空间。
  通过配置，InnoDB可实现每个表一个表空间的存储模式。
  每个InnoDB表在数据库目录里会有两个与表特定相关的文件，即.frm文件和包含表数据和索引的.idb文件。

InnoDB在其内部维护着一个数据字典，其中包括了与每个表有关的信息。
这个字典会保存在系统表空间里，因此，即使你用独立表空间来存储表的内容，系统表空间也是必需的。

MySQL会在包含该表的那个数据库所在的目录里，为每个MyISAM表创建３个文件，每个文件的基本名称都等同于表名，其扩展名则表明了该文件的用途。
例如，一个名为mytbl的MyISAM表将有以下３个文件。

• mytbl.frm
  格式文件，包含对该表结构的描述。
• mytbl.MYD
  数据文件，存储了该表各行的内容。
• mytbl.MYI
  索引文件，包含该表的所有索引信息。

视图和触发器在文件系统里的表示

在包含视图和触发器对象的数据库目录里，有与对象相关联的文件。
每个视图只有一个.frm文件，其中包含着该视图的定义和其他相关属性。
该文件的基本名和视图的名字一样，因此一个名为myview的视图由一个名为myview.frm的文件表示的。

每个触发器存储在一个.TRG文件里，其中含触发器的定义和其他相关属性。
触发器文件的基本名与触发器所属的那个表的名字一样。
例如，一个名为mytrig的触发器，与一个名为mytbl的表关联在一起，则存储它的文件将是mytbl.TRG。
如果一个表有多个触发器，则服务器会把它们的所有定义集中存储在同一个.TRG文件里。
每个触发器还有一个根据触发器来命名的.TRN文件，其中含与这个触发器相关联的那个表的名字。

SQL语句与表文件操作的对应关系

每种存储引擎都使用一个.frm文件来存储表格式（定义），因此SHOW TABLE FROM db_name的输出结果与db_name的数据库目录里的那个.frm文件的基本名列表相同。

服务器会为新表创建一个.frm文件，在其中存储该表定义的内部编码，并且会告知相应的存储引擎去创建与该表相关联的其他文件。

例如，InnoDB会创建一个数据字典条目，且在相应的InnoDB表空间里对其数据和索引信息进行初始化。
MyISAM会创建一个.MYD数据文件和一个.MYI索引文件。

当执行ALTER TABLE语句时，服务器会对表的.frm文件重新编码，以反映出这条语句对其结构的更改；服务器还会修改表的内容（即数据和索引）。

在执行CREATE INDEX和DROP INDEX时，也发生此情况。
如果ALTER TABLE改了表的存储引擎，则表的内容会按新的引擎转换。

MYSQL实现DROP TABLE方式是，删除那些表示表的文件。
如果删除InnoDB表，则InnoDB存储引擎还会更新它的数据字典，并在InnoDB系统表空间里把与该表相关联的空间标记为空闲。

操作系统对数据库对象名字的约束

数据库转储：

• （1）用mysqldump 工具将各个数据库转储出来：

% mysqldump --database db_name > db_name.sql

• （2）用 DROP DATABASE删除数据库。
• （3）关闭服务器，重新配置它，将lower_case_table_names设置为１，然后重启服务器。
• （4）用mysql程序重新加载所有的转储文件：

% mysql < db_name.sql

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

MySQL数据目录的结构易于理解，它以一种自然的方式使用了文件系统的分层结构。
这种结构也隐含有一些性能方面的问题。

MySQL状态文件和日志文件

这些文件的默认位置为服务器的数据目录，其中许多默认名是从在表中表示为HOSTNAME的服务器主机名继承来的。
二进制日志和中继日志会被创建为一组带编号的文件。

以下表格只列出了服务器级的状态文件和日志文件。

文件类型	默认名	文件内容
进程ID文件	HOSTNAME.pid	服务器进程ID
错误日志	HOSTNAME.err	启动／关闭事件和错误条件
一般查询日志	HOSTNAME.log	连接／断开事件和查询信息
二进制日志	HOSTNAME-bin.nnnnnn	修改数据的语句的二进制表示
二进制日志索引	HOSTNAME-bin.index	当前二进制日志文件名的列表
中继日志	HOSTNAME-relay-bin.nnnnnn	当从服务器自主服务器接收到的数据修改信息
中继日志索引	HOSTNAME-relay-bin.index	当前中继日志文件名的列表
主服务器信息文件	master.info	用于连接主服务器的参数
中继信息文件	relay-log.info	中继日志处理的状态
慢查询日志	HOSTNAME-slow.log	处理起来较耗时的语句文本

进程ID文件

MySQL服务器会在启动时把它的进程ID写入PID文件，且在关闭时会删除该文件。
其他进程可利用此文件来确定MySQL服务器是否在运行，及其进程ID是什么。

MySQL日志

MySQL能维护多种类型的日志文件。
大部分日志功能是可选的，可用服务器启动选项来启用需要的日志，可指定它们的名字。

错误日志包含的是，服务器在系统发生意外时，生成的诊断信息。
如果服务器启动失败，或意外退出，则此日志有用。

常规查询日志包含的是与服务器操作有关的常规信息，包括谁在连接服务器，从什么地方连接，及都调用哪些语句。
二进制日志还包括语句信息，但仅限于那些修改数据库内容的语句。
当这台服务器在复制结构里属于主服务器时，还会包含其他的信息。

二进制日志的内容是一些以二进制格式记录的"事件"，这些"事件"可作为mysql客户端程序的输入来执行。
配套的二进制日志索引文件会列出服务器当前正在维护的二进制日志文件。

二进制日志对系统崩溃后的数据库恢复工作有重要意义，在备份后，可通过把二进制日志文件送至服务器的方式重复这些更新操作。
以便把数据库恢复到发生崩溃前的一刻。
即便建立了复制服务器，二进制日志也能发挥作用，因为它可把那些必须从主服务器传递到从服务器的更新操作记录下来。

默认情况下，服务器会把日志文件写到数据目录里，因此，确保日志文件安全的一种措施是，只允许MySQL系统管理员所使用的登录账户访问服务器主机上的数据目录。

迁移数据目录的内容

MySQL允许你迁移数据目录本身或其中的某些元素。
理由：

• 包含数据目录的那个文件系统已满，你需将其移动到某个更大容量的文件系统上。
• 如果数据目录位于一个很忙的磁盘驱动器上，则你可把它放在活动不太频繁的驱动器上，以平衡跨物理设备的磁盘活动。
• 把数据库和日志放在固态磁盘上。

迁移方法

迁移数据目录或其中各个部分的方法有两种。

• 第一种，在任何平台，可在服务器启动时，通过命令行或选项文件，指定一个选项。
  例如，指定数据目录位置，启动时命令行指定–datadir=dir_name或选项文件里：

[mysqld]
datadir=dir_name

• 第二种，在Unix上，可先移动要迁移的文件或目录，然后在原位置生成一个符号链接，指向新位置。

迁移方法总结：

迁移实体	适用的迁移方法
整个数据目录	启动选项或符号链接
各个数据库目录	符号链接
各个数据库表	符号链接
InnoDB表空间文件	启动选项
服务器PID文件	启动选项
日志文件	启动选项

迁移注意事项

迁移前，先备份数据，以便在迁移操作搞砸时进行恢复。
迁移前，停止MySQL服务器，迁移完成后再重启。

移动数据库前，应执行FLUSH TABLES以关闭所有打开的表文件。

评估迁移带来的影响

Unix上，可用du, df, ls -l查看磁盘空间信息。

假设，数据目录为/usr/local/mysql/data，你想把它转移到/var/mysql，因为 df 命令的输出表明 /var 文件系统有更多的可用空间：

% df -k /usr /var

为弄清数据目录迁移之后， /usr 文件系统上能是否多少空间，可以使用 du -s 命令：

% du -s /usr/local/mysql/data

未查明真相，可以对数据目录使用 df 命令。

% df -k /usr/local/mysql/data

迁移整个数据目录

在迁移数据目录时，先停止MySQL服务器，并把数据目录移动到新的位置。

移动后用–datadir以新目录启动。

Unix上可在原来数据目录里创建一个符号链接，指向新位置。

迁移单个数据库

Unix上，迁移数据库的过程如下：

• （1）停止正在运行的服务器.
• （2）把数据库移至新的位置，或将其复制过去，删除原来那个。
• （3）在数据目录中创建一个符号链接，让其具有原来那个数据库的名字，并指向那个新的数据库位置。
• （4）重启服务器。

例：
把bigdb数据库从/usr/local/mysql/data目录迁移至/var/db

% mysqladmin -p -u root shutdown
% cd /usr/local/mysql/data
% tar cf - bigdb | (cd /var/db; tar xf -)
% rm -rf bigdb
% ln -s /var/db/bigdb bigdb
% mysqld_safe &

迁移单个表

需满足条件。

• 必须使用Unix，并且要迁移的表需为MyISAM表。
• 操作系统必须具有一个可工作的realpath()系统调用。

mysql> SHOW VARIABLES LIKE 'have_symlink';

迁移 InnoDB 系统表空间

初次配置InnoDB系统表空间时，可用innodb_data_home_dir和innodb_data_file_path 系统变量，将InnoDB系统表空间的各个组成文件罗列在某个选项文件里。

如果你已创建了表空间，就可迁移组成它的那些常规文件。
需按以下步骤来迁移部分或全部的表空间文件。

• （1）如果服务器在运行，要停止它。
• （2）移动需迁移的表空间文件。
• （3）修改定义InnoDB配置的那个选项文件，反映出文件迁移后的新位置。
• （4）重启服务器。

迁移状态文件和日志文件

如果要迁移PID文件或日志文件，则需先停止MySQL服务器，然后用一个可指定文件新位置的选项来重启它。

学习参考资料：

《MySQL技术内幕》第5版