http://hi.baidu.com/silentseason/blog/item/5ca082ef6d5b0aefcf1b3e2b.html

Chapter 3. 数据库管理
Table of Contents

3.1. 数据目录
3.2. MySQL数据表在系统中表现形式
3.3. 数据表最大尺寸限制
3.4. 状态文件和日志文件
3.5. 调整MySQL数据目录位置
数据库是一个复杂而又关键的系统，为确保系统安全、高效运行，需熟悉数据库内部的运作机制，掌握各种维护工具，并做好日常的管理工作。下面列举几项主要工作职责：

服务器的关闭和启动；

管理用户帐号；

管理日志文件；

数据库备份恢复；

数据库优化；

确保数据库数据安全；

数据库软件升级。

3.1. 数据目录
数据目录是用来存放数据表和相关信息的地方，是数据库的核心。在MySQL中的数据目录根据不同平台的有一些差异：

在UNIX/Linux系统上，如果用源码编译安装，数据目录的位置默认是在/usr/local/mysql/var中；

在UNIX/Linux系统上，如果用二进制发行版安装，数据目录的位置默认是在/usr/local/mysql/data中；

在WINDOWS系统上，数据目录的位置默认是在c:/mysql/data中；

在服务器启动时，可用–datadir=dir_name来指定数据目录，也可把它写到配置文件中。

我们还可用命令向服务器查询数据目录的位置，数据目录的变量名是datadir，如：

% mysqladmin variables
如果在一台机器上同时运行多个服务器，则可根据端口的不时来查询每个服务器的数据目录，如：

% mysqladmin –host=127.0.0.1 –port=port_number variables
如果–host是localhost，系统则会用一个UNIX套接字去连接数据库服务器，这时要使用–socket选项，所以查询语句变成：

% mysqladmin –host=localhost –socket=/path/to/socket variables
mysql> SHOW VARIABLES LIKE ‘datadir’;
在windows NT平台上可以使用“.”作为一条命名管道连接的主机名，用–socket选项给出命名管道的名字，如：

c:\ mysqladmin –host=. –socket=pipe_name variables
配置文件的中[mysqld]段中的datadir=/path/to/datadir设置也可查询到数据目录。

在mysqld程序的帮助信息里也有程序编译时默认的数据目录信息，可用以下命令显示：

% mysqld –help
数据目录是存放数据文件的地方，每个数据库对应目录的不同文件。InnoDB数据表由于用表空间来管理数据库，所以就没这种对应关系。但也是保存在数据目录中的，在数据目录除保存数据库文件外，还可能会保存以下几类文件：

服务器的配置文件，my.cnf；

服务器的进程ID(PID)文件；

服务器的日志文件和状态文件，这些文件对管理数据库有重要的价值；

DES密钥文件或服务器的SSL证书与密钥文件。

数据目录中的所有数据库全部由服务器(mysqld)来管理，客户端不直接操作数据。服务器是客户使用数据的唯一通道。

在MySQL中，每个数据库其实就是在数据目录下一个子目录，show databases命令相当于列出数据目录中的目录清单。create database db_name命令会在数据目录下新建一个db_name的目录，以存放数据库的数据文件。所以我们也可下面的shell命令方式来建立一个空数据库：

% cd datadir
% mkdir db_name
% chmod u=rwx,go-rwx db_name

同理，删除数据库drop database db_name也就是删除数据目录中一个名为db_name的目录及目录中的数据表文件。我们也可用shell这进行操作：

% cd datadir
% rm -rf db_name

比较shell方式与drop database方式，drop database db_name命令不能删除db_name目录中创建的其它非数据表文件；由于InnoDB是表空间来管理数据表，所以不能用rm或del命令删除InnoDB的数据表。

3.2. MySQL数据表在系统中表现形式
MySQL数据表类型有：ISAM、MyISAM、MERGE、BDB、InnoDB和HEAP。每种数据表在文件系统中都有不同的表示方式，有一个共同点就是每种数据表至少有一个存放数据表结构定义的.frm文件。下面介绍每种数据表文件：

ISAM数据表是最原始的数据表，有三个文件，分别是：

.frm，存放数据表的结构定义；

.ISD，数据文件，存放数据表中的各个数据行的内空；

.ISM，索引文件，存放数据表的所有索引信息。

MyISAM数据表是ISAM数据表的继承者，也有三个文件，分别是：

.frm，结构定义文件；

.MYD，数据文件；

.MYI，索引文件。

MERGE数据表是一个逻辑结构，代表一组结构完全相同的MyISAM数据表构成的集合。它在文件系统中有二个文件，分别是：

.frm，结构定义文件；

.MRG，构成MERGE表的MyISAM数据表清单，每个MyISAM数据表名占一行。也就是说可通过改变该表的内容来改变MERGE数据表的结构。修改前请先刷新缓存(flush tables)，但不建议这样修改MERGE数据表。

BDB数据表用两个文件来表示，分别是：

.frm，结构定义文件；

.db，数据表数据和索引文件

InnoDB由于采用表空间的概念来管理数据表，所以它只有一个与数据表对应.frm文件，同一目录下的其它文件表示为表空间，存储数据表的数据和索引。

HEAP数据表是一个存在于内存中的表，所以它的数据和索引都存在于内存中，文件系统中只有一个.frm文件，以定义结构。

了解MySQL数据表在文件系统中表现形式后，我们可知道，创建、修改或删除数据表，其实就是对这些文件进行操作。例如一些数据表(除InnoDB和HEAP数据表外)，我们可直接在文件系统中删除相应的文件来删除数据表。

% cd datadir
% rm -f mydb/mydb.*

以上命令可删除mydb数据库中的mydb数据表。

3.3. 数据表最大尺寸限制
在MySQL中影响数据表尺寸的因素有很多，下面分别进行介绍：

MySQL数据表类型的不同对数据表尺寸的限制：

ISAM数据表中单个.ISD和.ISM文件的最大尺寸为4G；

MyISAM数据表中单个.MYD和.MYI文件的默认最大尺寸也是4G，但可在创建数据表时用AVG_ROW_LENGTH和MAX_ROWS选项把这个最值扩大到800万TB。

MERGE数据表的最大尺寸是它的各组成MyISAM数据表的最大尺寸之和。

BDB数据表的尺寸受限于BDB处理程序所允许的.db文件的最大尺寸。这个最大尺寸随着数据表页面尺寸(编译时确定)而变化，但即使是最小的页面尺寸(512字节)，.db文件的最大尺寸也可达2TB。

InnoDB数据表的表空间的最大尺寸是40亿个页面，默认的页面尺寸是16K，该值可在8K到64K之间，在编译时确定。InnoDB数据表的最大尺寸也就是表空间的最大尺寸。

操作系统对文件的尺寸限制，一般文件系统都对单个文件不得超过2G的限制。该约束会对数据库文件造成限制。InnoDB数据表可通过利用未格式化硬盘作为表空间来绕过该限制。

对于数据和索引分开两个文件存放的数据表，其中任何一个文件达到操作系统文件的最大限制，数据库表也就达到最大尺寸。

包含AUTO_INCREMENT数据列的表受到该数据列类型最大上限值的限制。

由于InnoDB数据表用表空间来管理，一个表空间可同时空纳多个数据表，所以数据表的最大尺寸受系统文件和同一表空间中数据表空间的约束。

3.4. 状态文件和日志文件
在MySQL数据目录中还包含着许多状态文件和日志文件，这些文件的文件名都是以主机名加上相关后缀来命名的。下面是这些文件的一个说明列表：

Table 3.1. 状态文件和日志文件

文件类型 默认名 文件内容
进程ID文件 hostname.pid MySQL服务器进程的ID
常规查询日志 hostname.log 连接/断开连接事件和查询信息
慢查询日志 hostname-slow.log 记录查询时间很长的命令信息
变更日志 hostname.nnn 创建或修改数据表结构和内容的查询命令信息
二进制变更日志 hostname-bin.nnn 创建或修改数据表结构和内容的查询命令的二进制表示法
二进制变更日志的索引文件 hostname-bin.index 使用中的“二进制变更日志”列表
错误日志 hostname.err 记录“启动/关闭”事件和异常情况

变更日志和二进制变更日志主要用于MySQL数据库服务器的崩溃恢复中，由于变更日志记录了数据库的所有变更操作，所以可以进行事件重放。具体操作请参考相关数据库备份恢复章节。对于变更日志，我们可用–log-long-format选项来让它以扩展方式记录有关事件。扩展方式可记录谁发出查询和什么时候发出查询的信息。可使我们更好地掌握客户端的操作情况。日志记录着查询命令的所有操作，里面可能会有一些敏感信息。所以我们要确保日志文件的安全。

3.5. 调整MySQL数据目录位置
MySQL数据库的数据目录位置，包括目录里的各种文件的位置)可根据实际情况进行调整。调整的方法有两种，一种是使用符号链接；一种用服务器启动选项。下面一个列表说明了数据目录及目录中文件各自适宜采用的方法：

Table 3.2. MySQL数据目录及目录中文件位置的调整方法

调整对象 适用方法
整个数据目录 启动选项和符号链接
数据库目录 符号链接
数据表 符号链接
InnoDB数据表空间 启动选项
PID文件 启动选项
日志文件 启动选项

下面是各种调整方法的具体操作过程：

在调整MySQL的数据目录时，要先停止服务器，再把数据目录移动到新的位置。接着，我们可选择在原来目录下创建一个符号链接指向新的位置，或者用启动选择–datadir指向新的数据目录。推荐用创建符号链接的方法，因为如果那个数据目录中有my.cnf文件，相应的服务器还能找到它。

数据库只能存在于MySQL数据目录中，所以只能使用符号链接的方法调整它的位置。在Linux系统的操作步骤如：

关闭服务器；

把数据库目录拷贝到新的位置；

删除原来的数据库目录；

在原来的MySQL数据目录中创建一个同名符号链接指向新的位置；

重新启动服务器。

在windows下的操作方法不些不同，操作方法如下：

关闭服务器；

把数据库目录移动新的位置；

删除原来的数据库目录；

在原来数据目录下建一个同名的.sym文件，在文件中输入数据库新目录的全路径，如c:\mysql\newdir\mydb。这个文件就相当于Linux下的符号链接；

重启服务器。

为了支持符号链接功能，必须用–use-symbolic-links选项启动服务器；或在选项文件的[mysqld]节中添加use-symbolic-links选项。

MySQL必须是3.23.16以上版本且是max服务器(mysqld-max或mysqld-max-nt)。

要移动数据表，必须满足以下所有条件才行：

MySQL的版本必须是4.0或以上的版本；

操作系统必须有一个可用的realpath()调用；

移动的数据表必须是MyISAM类型的数据表。

在满足以上所有条件后，我们就可把.MYD数据文件和MYI索引文件移到新位置，再在原来位置创建两个同名符号链接指定新的位置。注意，.frm定义文件仍需留在原来的数据库目录中。

如以上条件不能全部满足，最好不要移动数据表文件。否则一旦你运行ALTER TABLE、OPTIMIZE TABLE、REPAIR TABLE语句对移动过的数据表进行优化或修改，这样数据表就会回到原来的位置，使移动操作失效。因为这些命令的执行过程是这样的：它会先在数据目录中创建一个临时数据表并对这个临时数据表进行优化或修改，然后删除原来的数据表(这里是你为了移动数据表而创建的一个符号链接)，再把临时数据表更名为原来的数据表名称。这样一来，你移走的数据表就和这个数据库完全没有关系了。基于以下的不稳定因素，如无特殊必要，不建议移动数据表。

InnoDB表空间是通过在选项文件中使用innodb_data_home_dir和innodb_data_file_path选项列出InnoDB表空间组成文件清单的方法来配置的，所以我们可通过修改这些选项来重新安置InnoDB表空间的组成文件。步骤如下：

关闭服务器；

移动组成表空间的文件；

修改选项文件，指出组成表空间的文件的新位置；

重启服务器。

状态文件和日志文件的位置可通过选项文件或启动服务器时指定。