MySQL数据目录构造[MySQL防范]

从概念上讲,大大都关系数据库系统是类似的:它们有一系列数据库构成,每个数据库包含一系列数据库表,但每个系统有各自组织其管理的数据方法,MySQL也不例外.

缺省地,全部由MySQL服务器mysqld管理的数据存储在一个称为MySQL数据目录的地方,全部数据库都存放在哪儿,也包含供应服务器操作信息的状况文件.假如你对一个MySQl安装履行管理任务,你应当熟知数据目录的筹划及用处.

本文介绍下列专题:

• 若何肯定数据目录的位置.
• 服务器若何组织并供应对数据库和它管理的表的拜候.
• 在那边找到由服务器生成的状况文件记忆它们包含什么内容.
• 若何改变缺省地址或数据目录或单独数据库的组织构造.

1、数据目录的位置

一个缺省数据目录被编译进了服务器,假如你从一个源代码分发安装MySQL,典型的缺省目录为/usr/local/var,假如从RPM文件安装则为/var/lib/mysql,假如从一个二进制分发安装则是/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或--socket选项,你可以轮番得到它们每一个的数据目录信息:

  %msqladmin --port=port_name variables

  %mysqladmin --socket=/path/to/socket variables

  mysqladmin号令可运行在任何你能从其衔接服务器的主机上,假如你想在一个远程主机衔接服务器,利用一个--host=host_name选项:

  %mysqladmin --host=host_name variables

  在Windows上,你可以通过利用--pipe强迫一个号令管道衔接和--socket=pipe_name指定管道名来衔接监听一个号令管道的NT服务器:

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

• 你可以利用ps号令查看任何正在运行mysqld 进程的号令行.

  试一下下列号令之一并探求--datadir:

  %ps axww | grep mysql        BSD气势

  %ps -ef | grep mysqld            System V气势

  假如你的系统运行多个服务器,ps号令大概分外有效,因为你能即刻发现多个数据目录位置,缺陷是必须在服务器上运行,并且大概没有有效的信息产生,除非在mysqld号令行上明确指定了--datadir选项.

• 假如MySQL是从一个源代码分发安装的,你可以查抄其配置信息肯定数据目录位置.比方,位置可从顶级Makefile中得到,但是注意,位置是Makefile中的localstatedir值,不是datadir,并且,假如分发位于一个NFS挂载的文件系统并用来为多个主机构建MySQL,配置信息反映了分发被最新构建的主机,这大概不能供应你感爱好的主机的数据目录信息.
• 假如上述方法失利,你可以用find探求数据库文件,下列号令探求".frm"文件,它是任何MySQL安装的一部份:
  
  % find / -name ".frm" -print
  
  在下文各例中,用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索引.

当你发出一条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通过删除对应于表的三个文件来实现.

固然你可以通过删除数据库目录中对应于表的三个文件,但不妙手工成立或改正一个表,如,假如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:undefined variable

对此,你必须转义$字符或用引号禁止其特别含义:

%ls \$my_db

%ls '$my_db'

假如你用引号,一定要用单引号,而双引号并不由止变量注释.

其次,固然MySQL答应数据库和表名最长到64个字符,但名字的长度受限于你的操作系统限定的长度,普通这不是一个问题（固然老的System V强迫14个字符）.在这种情形下,你数据库名的上限为14个字符,而表名上限为10个字符,因为表示表的文件名有一个点（.）和三个字符的扩大名.

第三,文件系统的大小写敏感性影响到你若何命名和引用数据库和表名.假如文件系统是大小写敏感的（如Unix）,两个名字my_tbl和MY_TBL是差别的表.假如文件系统不是大小写敏感的（如Windows）,这两个名字指的是相同的表.假如你用一个Unix服务器开辟数据库,并且假如你有大概转移到Windows,你应当记着这一点.

2.5 MySQL状况文件

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

当服务器启动时,它将其进程ID写入进程ID（PID）文件中,而在它关闭时,删除该文件.PID文件是答应服务器本身被其他进程找到的工具.比方,假如你运行mysql.server,在系统关闭时,关闭MySQL服务器的脚本查抄PID文件以决意它需求向哪个进程发出一个终止信号.

出错日记由safe_mysqld成立,作为服务器尺度出错输出的重定向,它包含任何邪到stderr的消息.这意味着只有你通过调用safe_mysqld启动服务器,出错文件才存在（无论若何,它是一个启动服务器的最好办法,因为假如它由于出错而退出,safe_mysqld将重启服务器.）.

普通日记和更新日记是可选的.你可以只开启你需求的日记范例,用--log和--log-update服务器选项.

普通日记供应服务器操作的普通信息:谁从那边衔接服务器和他们发出什么查询.更新日记供应查询信息,但只有改正数据库内容的查询.更新日记内容被写成SQL语句,可以将它们供应应mysql客户程序来履行.假如你赶上崩溃,并且必须倒回备份文件,更新日记就很有效,因为你能反复履行自崩溃时的更新,通过将更新日记反馈给服务器,这答应你将数据库恢复到崩溃发生时的状况.

下面是一个简单的例子,信息呈目前普通日记中,它是一个成立一个在数据库test中表,插入一行,然后删除表的会话:

9905097:37:09492 ConnectPaul@localhost on test
492 Queryshow databases
492 Queryshow tables
492 Field List tbl_1
492 Field List tbl_2
...
9905097:34:22492 QueryCREATE TABLE my_tbl (val INT)
9905097:34:34492 QueryINSERT INTO my_tbl values (1)
9905097:34:38492 QueryDROP TABLE my_tbl 
9905097:34:40492 Quit

普通日记包含日期和时间、服务器进程ID、事件范例和事件信息栏目.

同一个会话呈目前更新日记中看上去像这样:

use test;
CREATE TABLE my_tbl (val int);
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 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;　

保证你的日记文件安全并且不让肆意用户读取是个好主张.普通日记和更新日记都能包含诸如口令等的敏感信息,因为它们包含查询文本.如:

9905097:23:314 QueryUPDATE user SET Password=PASSWORD("secret")
WHERE user="root"

关于查抄和设置数据目录的权限,请见《MySQL安全性指南》.使数据目录安性的指令包含下列号令:

% chmod 700 DATADIR

以拥有数据目录的Unix用户运行此号令.确保服务器也以此用户运行,不然该号令不但将别的人拒之门外,它也禁止服务器拜候你的数据库.

状况文件呈目前数据目录的顶级目录,就象数据库目录,所以你大概耽忧这些文件名能否与数据库名冲突或出错（如在服务器履行SHOW DATABASES语句时）.答案是不.状况和日记文件信息存储在文件中,而数据库是目录,所以可执路程序能用一个简单的stat()调用辨别它们.假如你看一下数据目录,你可以辨别状况文件和数据库目录,用ls -l并查抄情势的第一个字符是一个"_"还是一个"d".

你也可以简单地看一下名字,全部状况文件名包含一个点("."),而数据库目录没有（.在数据库名中是无效字符）.

3 重定位数据库目录

前面谈论的数据目录构造是缺省配置,全部数据库和状况文件均包含此中,但是,你有某些安闲决意数据目录内容的位置,本节谈论为什么你大概移走部份数据目录（或乃至目录本身）、你能移走什么以及你若何做这些改变.

MySQL答应你重定位数据目录或此中的成员,由几个缘由你为什么要这样做:

• 你能将数据目录放在你缺省所在的文件系统更大容量的文件系统上.
• 假如你的数据目录在一个繁忙的硬盘上,你大概把它放在不太忙的磁盘上以均衡磁盘活动.你可以把数据库和日记文件放在脱离的磁盘上或跨磁盘分布.
• 你大概想运行多个服务器,各自有自己的数据目录,这是办理每个进程文件描写符限制问题的一种办法,分外是你不能重新配置内核以答应更高的限制.
• 有些系统在比方/var/run中保存服务器的部份文件,你大概想把MySQL的PID文件也放在那儿,为了系统操作的一致性.
  
  

3.1 重定位办法

有两种办法重定位数据目录的内容:

• 你可以在服务器启动时指定选项,在号令行或在一个选项文件的[mysqld]中.
• 你可以移走要重定位的东西,然后在原位置做一个指向新位置的标记衔接.

两种办法都不能办理你能重定位的一切,下表总结了什么能重定位和用哪类办法重定位.假如你利用选项文件,有大概在全局选项文件/etc/my.cnf（Windows上的c:\my.cnf）指定选项.当前的Windows版本也探求系统目录（c:\Windows或c:\NT）.

你也可以利用缺省数据目录中的选项文件my.cnf,但不举荐利用该文件.假如你想重定位数据目录本身,你不得不让缺省数据目录可读以便使你能在这里安排选项文件指定服务器应当在那边找到"真正"的数据目录!这很混乱.假如你想利用一个选项文件指定服务器选项,最好利用/etc/my.cnf.

3.1 查验重定位的效果

在试图重定位任何东西之前,查验操作到达预期效果是个好主张.借助于du、df和ls -l号令得到磁盘空间的信息,但这些依靠于你精确理解你的文件系统的筹划.

下面演示一个在你查验一个属目录重定位时的计划陷阱.假定你的数据目录是/usr/local/var,而你想把它移到/var/mysql,因为df显示/var文件系统有很多的闲暇空间:

%df /usr /var
Filesystem  1k-blocks     Used   Avail  Capacity  Mounted on
/dev/wd0s3e    396895   292126   73018    80%     /usr
/dev/wd0s3f   1189359  1111924  162287    15%     /var
 　

重定位的数据目录在/usr文件系统上有多少闲暇空间呢?要知道它,利用du -s找出该目录利用多少空间.

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

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

%df /usr/local/var
Filesystem  1k-blocks     Used   Avail  Capacity  Mounted on
/dev/wd0s3f   1189359  1111924  162287    15%     /var　

这就奇特了.假如我们为包含/usr/local/var的文件系统申请闲暇空间,为什么却报告var上的空间呢?这里ls -l供应了答案:

%ls -l /usr/local
....
lrwxrwxrwx  1  root  wheel  10 Dec 11 23:33 var -> /var/mysql
....　

输出显示/usr/local/var是对/var/mysql的标记衔接,换句话说,数据目录已经被重定位于/var文件系统,并用一个指向那边的标记连代替换.通过将数据目录移到/var竟然释放了/usr上那么多空间!

3.2 重定位数据目录

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

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=/var/run/mysqld.pid,PID文件是/var/run/mysqld.pid.假如你指定-pid-file=mysqld.pid,PID文件是DATADIR/mysqld.pid.

假如你指定无扩大名的更新日记文件,MySQL在它每次翻开更新日记时产生次序名.这些名字用一个扩大名.nnn,这里.nnn是还没被现有更新日记利用的第一个数字（如update.000,update.001等）.你可以通过明确指定扩大名来覆盖次序名,这时服务器将只利用指定的名字.