从概念上讲大多数关系数据库系统是相似:它们有系列数据库组成每个数据库包含系列数据库表但每个系统有各自组织其管理数据方式MySQL也不例外


缺省地所有由MySQL服务器mysqld管理数据存储在个称为MySQL数据目录地方所有数据库都存放在哪儿也包括提供服务器操作信息状态文件如果你对个MySQl安装执行管理任务你应该熟知数据目录布局及用途


本文介绍下列专题:


如何确定数据目录位置

服务器如何组织并提供对数据库和它管理表访问

在哪里找到由服务器生成状态文件记忆它们包含什么内容

如何改变缺省地点或数据目录或单独数据库组织结构


1、数据目录位置


个缺省数据目录被编译进了服务器如果你从个源代码分发安装MySQL典型缺省目录为/usr/local/var如果从RPM文件安装则为/var/lib/mysql如果从个 2进制分发安装则是/usr/local/mysql/data


在你启动服务器通过使用个--datadir=/path/to/dir选项可以明确指定数据目录位置如果你想把数据目录置于其它缺省位置外某处这很有用


作为名MySQL管理员你应该知道你数据目录在哪里如果你运行多个服务器你应该是到所有数据目录在哪里但是如果你不知道确切位置由多种思路方法找到它:


使用mysqladmin variables从你服务器直接获得数据目录路径名查找datadir变量值在Unix上其输出类似于:
%mysqladmin variables

+----------------------+----------------------+
| variable_name | Value |
+----------------------+----------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | /var/local/ |
| datadir | /usr/local/var/ |
....


在Windows上输出可能看上去像这样:
c:\mysqladmin variables

+----------------------+----------------------+
| variable_name | Value |
+----------------------+----------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | c:\mysql\ |
| datadir | c:\mysql\data\ |
....


如果你有多个服务器在运行它们将在区别TCP/IP端口或套接字上监听通过提供连接服务器正在监听端口或套接字--port或--选项你可以轮流获得它们每个数据目录信息:


%msqladmin --port=port_name variables


%mysqladmin --=/path/to/ variables


mysqladmin命令可运行在任何你能从其连接服务器主机上如果你想在个远程主机连接服务器使用个--host=host_name选项:


%mysqladmin --host=host_name variables


在Windows上你可以通过使用--pipe强制个命令管道连接和--=pipe_name指定管道名来连接监听个命令管道NT服务器:


c:\mysqladmin --pipe --=pipe_name variables


你可以使用ps命令查看任何正在运行mysqld 进程命令行

试下下列命令的并寻找--datadir:


%ps axww | grep mysql BSD风格


%ps -ef | grep mysqld V风格


如果你系统运行多个服务器ps命令可能特别有用你能马上发现多个数据目录位置缺点是必须在服务器上运行而且可能没有有用信息产生除非在mysqld命令行上明确指定了--datadir选项


如果MySQL是从个源代码分发安装你可以检查其配置信息确定数据目录位置例如位置可从顶级Makefile中获得但是注意位置是Makefile中localstatedir值不是datadir而且如果分发位于个NFS挂载文件系统并用来为多个主机构建MySQL配置信息反映了分发被最新构建主机这可能不能提供你感兴趣主机数据目录信息

如果上述方式失败你可以用find寻找数据库文件下列命令寻找".frm"文件它是任何MySQL安装部分:

% find / -name ".frm" -pr

在下文各例中用DATADIR表示MySQL数据目录位置


2、数据目录结构


MySQL数据目录包含了服务器管理所有数据目录这些文件被组织成个树状结构通过利用Unix或Windows文件系统层次结构直接实现


每个数据库对应于数据目录下个目录

在个数据库中表对应于数据目录下文件


数据目录也包含由服务器产生几个状态文件如日志文件这些文件提供了有关服务器操作重要信息对管理特别在出了问题而试图确定问题原因时很有价值例如如果某个特定查询杀死服务器你可以通过检查日志文件判别捣乱查询


2.1 MySQL服务器怎样提供对数据访问


在数据目录下切由个单独实体-MySQL服务器mysqld管理客户绝不直接操作数据相反服务器提供数据可访问切入点它是客户和它们想使用数据的间中介


当服务器启动时如果有需要它打开日志文件然后通过监听网络连接位数据目录呈现个网络接口要访问数据客户建立对服务器个连接然后以MySQL查询传输请求来执行希望操作服务器执行每个操作并将结果发回用户服务器是多线程并能服务多个同时客户连接然而修改操作个执行个实际效果是顺序化请求以使两个客户决不能在同时刻改变同记录


在正常情况下让服务器作为数据库访问唯仲裁者提供了避免可从同时访问数据库表多个进程破坏保证管理员应该知道有时服务器没有对数据目录独裁控制


当你在个单个数据目录上运行多个服务器般倪云新个服务器管理主机上所有数据库但是有可能运行多个服务器如果这完成提供对多个独立数据目录访问没有相互影响问题但哟也能启动多个服务器并指向同个目录般地这不是个好主意如果你试图这样最好是你系统提供良好文件锁定功能否则服务器将不能正确协作如果你将多个服务器同时写入日志文件你也冒着你日志文件称为混乱根源风险

在你运行isamchk和myisamchk时isamchk和myisamchk实用用于表维护、诊错和修复就想你想那样这些可以修改表内容允许它们和服务器正在操作同时对表操作这样能导致表损坏理解如何限制这种相互影响是很重要这样你不会损坏你表


2.2 数据目表示


每个MySQL服务器管理数据库有自己数据库表它是数据目录下个子目录其名字和它表示数据库相同例如数据库my_db对应于数据库目录DATADIR/my_db


这种表示允许多个数据库级语句在其实现中十分简单create DATABASE db_name在数据目录中创建个db_name空目录具有只允许MySQL服务器用户(运行服务器Unix用户)属主和模式这等价于下列手工在服务器主机上创建数据库:

%mkdir DATADIR/db_name
%chmod 700 DADADIR/db_name


用个空目录表示个新数据库最简单思路方法和其它数据库甚至为个空数据库创建大量控制文件或系统文件正好相反


drop DATABASE语句实现同样简单drop DATABASE db_name删除数据库中db_name目录和所有表文件这几乎和下列命令样:

%rm -rf DATADIR/db_name


(差别是服务器只删除具有已知用于表后缀名文件如果你在数据库目录创建了其它文件则服务器保留它们而且目录本身不被删除


SHOW DATABASE基本上不做什么只是列出位于数据目录中目录名有些数据库系统保持个主表用于维护所有数据库但在MySQL无此构件由于赋予数据目录结构简洁性数据库列表隐含在数据目录内容中而且这样表不必有额外开销


2.3 数据库表表示


每个数据库在数据库目录中有3个文件:个样式(描述文件)、个数据文件和个索引文件每个文件基本名是表名文件名扩展名代表文件类型扩展名如下表数据和索引文件扩展名指出表使用老式IASM索引或新式MyISAM索引




表 MySQL文件类型


文件类型
文件名扩展名
文件内容


样式文件
.frm
描述表结构(它列、列类型、索引等)


数据文件
.ISD(ISAM)
或.MYD(MyISAM)
包含数据文件上所有索引索引树


索引文件
.ISM(ISAM)
或.MYI(MyISAM)
该索引文件依赖表是否有索引而存在


当你发出条create TABLE tbl_name时语句定义表结构时服务器创建个名为tbl_name.frm文件它包括该结构内部编码同时也创建个空数据和索引文件化为包含指出无记录和无索引信息(如果create TABLE语句包括索引指定索引文件反映出这些索引)对应于表文件属主和模式被设置为只允许MySQL服务器用户访问


当你发出条alter TABLE tbl_name语句时服务器重新编码tbl_name.frm并修改数据和索引文件内容以反映语句指定结构改变对于create INDEX和drop INDEX也是样它们被服务器视为和alter TABLE等价drop TABLE通过删除对应于表 3个文件来实现


虽然你可以通过删除数据库目录中对应于表 3个文件但不能手工创建或修改个表如如果my_db是当前数据库drop TABLE my_tbl大概等价于下列命令


% rm -rf DATADIR/my_db/my_tbl.*


SHOW TABLE my_db输出只是列出my_db数据库目录中.frm文件基文件名有些数据库系统个注册表列举所有包含在个数据库中表MySQL不是不必要"注册表"隐含在数据目录结构中


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


MySQL对命名数据库和表有个原则:


名字可以由当前集中任何字母数字组成下划线和美元符 $也可以

名字最长为64个


然而数据库和表名字对应于目录和文件名服务器运行操作系统可能强加额外限制


首先数据库和表名仅限于对文件名合法如 $在MySQL原则中是允许但是如果你操作系统不允许则你不能在目录或表名中使用它实际上这对Unix或Windows不是所担心最大难度是在执行数据库管理时直接在shell中引用名字例如如果你命名个数据库如 $my_db包含个美元符任何从shell中对该名字引用可能被shell解释为对个变量引用:


%ls  $my_db
my_db:und variable


对此你必须转义 $或用引号禁止其特殊含义:


%ls \ $my_db
%ls ' $my_db'


如果你用引号定要用单引号而双引号并不禁止变量解释


其次虽然MySQL允许数据库和表名最长到64个但名字长度受限于你操作系统限定长度般这不是个问题(虽然老 V强制14个)在这种情况下你数据库名上限为14个而表名上限为10个表示表文件名有个点(.)和 3个扩展名


第 3文件系统大小写敏感性影响到你如何命名和引用数据库和表名如果文件系统是大小写敏感(如Unix)两个名字my_tbl和MY_TBL是区别表如果文件系统不是大小写敏感(如Windows)这两个名字指是相同表如果你用个Unix服务器开发数据库并且如果你有可能转移到Windows你应该记住这点


2.5 MySQL状态文件


除了数据库目录MySQL数据目录还包含很多状态文件这些文件整理总结在下表中大多数文件缺省名从服务器主机名生成在下表中表示为HOSTNAME




表 MySQL状态文件


文件类型
缺省名
文件内容


进程ID
HOSTNAME.pid
服务器进程ID


出错日志
HOSTNAME.err
启动和关闭事件和出错情况


般日志
HOSTNAME.log
连接/断开事件和查询信息


更新日志
HOSTNAME.nnn
修改表结构级内容所有查询文本


当服务器启动时它将其进程ID写入进程ID(PID)文件中而在它关闭时删除该文件PID文件是允许服务器本身被其他进程找到工具例如如果你运行mysql.server在系统关闭时关闭MySQL服务器脚本检查PID文件以决定它需要向哪个进程发出个终止信号


出错日志由safe_mysqld创建作为服务器标准出错输出重定向它包含任何邪到stderr消息这意味着只有你通过safe_mysqld启动服务器出错文件才存在(无论如何它是个启动服务器最好思路方法如果它由于出错而退出safe_mysqld将重启服务器)


般日志和更新日志是可选你可以只开启你需要日志类型用--log和--log-update服务器选项
般日志提供服务器操作般信息:谁从哪里连接服务器和他们发出什么查询更新日志提供查询信息但只有修改数据库内容查询更新日志内容被写成SQL语句可以将它们提供给mysql客户来执行如果你遇上崩溃并且必须倒回备份文件更新日志就很有用你能重复执行自崩溃时更新通过将更新日志反馈给服务器这允许你将数据库恢复到崩溃发生时状态


下面是个简单例子信息出现在般日志中它是个创建个在数据库test中表插入行然后删除表会话:

990509 7:37:09 492 Connect Paul@localhost _disibledevent=>服务器进程ID、事件类型和事件信息栏目


同个会话出现在更新日志中看上去像这样:

use test;
create TABLE my_tbl (val );
insert INTO my_tbl VALUES(1);
drop TABLE my_tbl;


对更新日志用--log-long-format选项获得个扩展形式日志扩展日志提供有关谁何时发出每条查询这使用更多磁盘空间但如果你想知道谁在做什么而不用将更新日志对照般日志内容找到连接事件


对上面会话扩展更新日志产生这样信息:

# Time: 990507 7:32:42
# User@Host: paul [paul] @ localhost
use test;
create TABLE my_tbl (val );
# 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;　


保证你日志文件安全并且不让任意用户读取是个好主意般日志和更新日志都能包含诸如口令等敏感信息它们包含查询文本如:

990509 7:23:31 4 Query update user SET Password=PASSWORD("secret")
where user="root"


对于检查和设置数据目录权限请见MySQL安全性指南使数据目录安性指令包含下列命令:


% chmod 700 DATADIR


以拥有数据目录Unix用户运行此命令确保服务器也以此用户运行否则该命令不仅将其它人拒的门外它也阻止服务器访问你数据库


状态文件出现在数据目录顶级目录就象数据库目录所以你可能担心这些文件名是否和数据库名冲突或出错(如在服务器执行SHOW DATABASES语句时)答案是不状态和日志文件信息存储在文件中而数据库是目录所以可执行能用个简单stat区分它们如果你看下数据目录你可以区分状态文件和数据库目录用ls -l并检查模式第个是个"_"还是个"d"


你也可以简单地看下名字所有状态文件名包含个点(".")而数据库目录没有(.在数据库名中是无效)


3 重定位数据库目录


前面讨论数据目录结构是缺省配置所有数据库和状态文件均包含其中然而你有某些自由决定数据目录内容位置本节讨论为什么你可能移走部分数据目录(或甚至目录本身)、你能移走什么以及你如何做这些改变


MySQL允许你重定位数据目录或其中成员由几个原因你为什么要这样做:


你能将数据目录放在你缺省所在文件系统更大容量文件系统上

如果你数据目录在个繁忙硬盘上你可能把它放在不太忙磁盘上以均衡磁盘活动你可以把数据库和日志文件放在分开磁盘上或跨磁盘分布

你可能想运行多个服务器各自有自己数据目录这是解决每个进程文件描述符限制问题种思路方法特别是你不能重新配置内核以允许更高限制

有些系统在例如/var/run中保存服务器部分文件你可能想把MySQLPID文件也放在那儿为了系统操作致性


3.1 重定位思路方法


有两种思路方法重定位数据目录内容:


你可以在服务器启动时指定选项在命令行或在个选项文件[mysqld]中

你可以移走要重定位东西然后在原位置做个指向新位置符号连接


两种思路方法都不能解决你能重定位切下表整理总结了什么能重定位和用哪种思路方法重定位如果你使用选项文件有可能在全局选项文件/etc/my.cnf(Windows上c:\my.cnf)指定选项当前Windows版本也寻找系统目录(c:\Windows或c:\NT)




表 重定位思路方法


重定位思路方法
适用重定位思路方法


整个数据目录
启动选项或符号连接


单个数据库目录
符号连接


单个数据库表
符号连接


PID文件
启动选项


般日志
启动选项


更新日志
启动选项


你也可以使用缺省数据目录中选项文件my.cnf但不推荐使用该文件如果你想重定位数据目录本身你不得不让缺省数据目录可读以便使你能在这里放置选项文件指定服务器应该在哪里找到"真正"数据目录！这很混乱如果你想使用个选项文件指定服务器选项最好使用/etc/my.cnf


3.1 检验重定位效果


在试图重定位任何东西的前检验操作达到预期效果是个好主意借助于du、df和ls -l命令获得磁盘空间信息但这些依赖于你正确了解你文件系统布局


下面演示个在你检验个属目录重定位时设计陷阱假定你数据目录是/usr/local/var而你想把它移到/var/mysqldf显示/var文件系统有很多空闲空间:


%df /usr /var
Filesystem 1k-blocks Used Avail Capacity Mounted _disibledevent=>数据目录在/usr文件系统上有多少空闲空间呢？要知道它使用du -s找出该目录使用多少空间

%cd /usr/local/var
%du -s .
133426


这大约是130MB真实这样吗？在数据目录下试下df:

%df /usr/local/var
Filesystem 1k-blocks Used Avail Capacity Mounted _disibledevent=>数据目录已经被重定位于/var文件系统并用个指向那里符号连接代替通过将数据目录移到/var竟然释放了/usr上那么多空间！


3.2 重定位数据目录


要重定位数据目录关闭服务器并把数据目录移到新位置上然后你应该删除员数据目录并用指向新位置符号连接代替它或用明确指出新位置选项重启服务器下表列出指定位置命令行和选项




表 数据目录重定位语法


选项源
语法


命令行
--data-dir=/path/to/dir


选项文件
[mysqld]
datadir=/path/to/dir


3.3 重定位数据库


数据库能通过符号连接思路方法移走要重定位个数据库关闭服务器并移走数据库目录并删除原来数据库目录用指向新位置符号连接代替它然后重启服务器


下例显示你如何将个数据库bigdb移到个区别地方:

%mysqladmin -u root -p shutdown
Enter password: ******
%cd DATADIR
%tar cf - bigdb | (cd /var/db; tar xf -)
%mv bigdb bigdb.orig
ln -s /var/db/bigdb .
%safe_mysqld　


你应该以该数据目录拥有者执行这些命令为了安全起见原数据库目录改名为bigdb.orig在你验证了服务器工作正常后你可以删除原数据目录


%rm -rf bigdb.orig


3.4 重定位数据库表


重定位个单独表不是个好主意你可以通过把表文件移到个区别地方并在数据目录中创建指向这些文件符号连接进行然而如果你发出条alter TABLE或OPTIMIZE TABLE语句将不进行你修改


每个语句通过在数据库目录中创建个实现你修改或优化临时表然后删除原来表并将临时表更名为原来表来完成结果是你符号连接被删除而且新表又回到数据库目录这是你移走前原表文件位置更糟糕是你还没有意识到它们在那儿继续占据着空间而且符号连接已经被破坏这样以后当你意识到发生事情时如果你忘记你把它们移到什么地方你可能没有好办法追踪文件了
很难保证具有表访问权人不修改或优化表所以最好把表留在数据库目录中


3.5 重定位状态文件


你可以重中定位PID文件、般日志和更新日志出错日志由safe_mysqld用启动选项创建而不能被重定位(除非你编辑safe_mysqld)


要在个区别位置写入状态文件关闭服务器然后由指定新状态文件位置适当选项启动它下表列出每个文件命令行和选项文件语法




表 状态文件重定位语法


选项源
语法


命令行
--pid-file=pidfile
--log=lodfile
--log-update=updatefile


选项文件
[mysqld]
pid-file=pidfile
log=lodfile
log-update=updatefile


如果你用绝对路径名指定状态文件用该路径创建文件否则文件在数据目录下创建如如果你指定--pid-file=/var/run/mysqld.pidPID文件是/var/run/mysqld.pid如果你指定-pid-file=mysqld.pidPID文件是DATADIR/mysqld.pid


如果你指定无扩展名更新日志文件MySQL在它每次打开更新日志时产生顺序名这些名字用个扩展名.nnn这里.nnn是还没被现有更新日志使用第个数字(如update.000,update.001等)你可以通过明确指定扩展名来覆盖顺序名这时服务器将只使用指定名字

上篇文章: ASP中SQL语句

下篇文章: Javascript 两种 function 定义区别