第六章 MySQL数据库维护.docx
《第六章 MySQL数据库维护.docx》由会员分享，可在线阅读，更多相关《第六章 MySQL数据库维护.docx（18页珍藏版）》请在冰豆网上搜索。
第六章MySQL数据库维护
本章要点：
v维护表的工具及使用
v如何对表进行由简到繁的检查
v如何对表进行由容易到困难的修复
v如何建立一个数据库的维护规范
你可能希望MySQL从安装以后就始终正常运行。
但是，因为各种原因，MySQL随时可能会出现问题。
各种意外情况都可能是服务器崩溃或者主机意外停机，都会导致数据库表的损坏。
这些错误通常是对表进行修改且未完全写入时引起的。
而且这些数据往往是最新的数据，通常不可能在备份数据中找到，通常你需要修复数据表，以尽可能的抢救重要数据。
本章的主要内容是检查和修复表的问题，对于表的检查和修复，最重要的工具是myisamchk和isamchk实用程序。
6.1数据库表的检查、修复与优化
表的故障检测和修正的一般过程如下：
●检查出错的表。
如果该表检查通过，则完成任务，否则必须修复出错的数据库表。
●在开始修复之前对表文件进行拷贝，以保证数据的安全。
●开始修复数据库表。
●如果修复失败，从数据库的备份或更新日志中恢复数据。
在使用myisamchk或isamchk检查或修复表之前，应该首先注意：
●建立数据库备份和使用更新日志，以防修复失败，丢失数据。
●仔细阅读本章内容以后再进行操作，尤其是不应该在阅读“避免与MySQL服务器交互作用”之前进行操作。
因为，在你没有足够的知识之前贸然操作，可能会引起严重的后果。
●如果你在Unix平台上对表进行维护时，应该首先注册到专用的帐户 mysql，以避免对表读写访问产生所有权的问题，以及破坏数据库目录的所有权限。
6.1.1数据库表的维护工具
MySQL的myisanchk和isamchk实用程序很类似，基本上它们具有同样的使用方法。
它们之间的主要区别时所使用的表的类型。
为了检查/修复MyISAM表（.MYI和.MYD），你应该使用myisamchk实用程序。
为了检查/修复ISAM表（.ISM和.ISD），你应该使用isamchk实用程序。
●为了使用任一个使用程序，应指明你要检查或修复的表，myisamchk和isamchk的使用方法为：
shell>myisamchkoptionstbl_name
shell>isamchkoptionstbl_name
如果你愿意，你可以在命令行命名几个表。
●你也能指定一个名字作为一个索引文件（用“.MYI”或“.ISM”后缀），它允许你通过使用模式“*.MYI”或“.ISM”指定在一个目录所有的表。
例如，如果你在一个数据库目录，你可以这样在目录下检查所有的表：
shell>myisamchk*.MYI
shell>isamchk*.ISM
●如果你不在数据库目录下，你可指定目录的路径：
shell>myisamchkoptions/path/to/database_dir/*.MYI
shell>isamchkoptions/path/to/database_dir/*.ISM
●你甚至可以通过为MySQL数据目录的路径指定一个通配符来作用于所有的数据库中的所有表：
shell>myisamchkoptions/path/to/datadir/*/*.MYI
shell>isamchkoptions/path/to/database_dir/*/*.ISM
这个方法无法在windows平台下使用。
注意不论是myisamchk还是isamchk都不对表所在的位置做任何判断，因此，应该或者在包含表文件的目录运行程序，或者指定表的路径名。
这允许你将表文件拷贝到另一个目录中并用该拷贝进行操作。
6.1.2检查数据库表
myisamchk和isamchk提供了表的检查方法，这些方法在彻底检查表的程度方面有差异。
标准的方法检查表
通常用标准的方法就足够了。
对表使用标准的方法进行检查，不使用任何选项直接调用即可，或用-s或--silent选项的任何一个。
：
myisamchktbl_name
isamchktbl_name
这能找出所有错误的99.99%。
它不能找出的是仅仅涉及数据文件的损坏（这很不常见）。
完全彻底的数据检查
为了执行扩充检查，使用--extend-check或-e选项，这个选项检查数据：
myisamchk-etbl_name
isamchk-etbl_name
它做一个完全彻底的数据检查（-e意思是“扩展检查”）。
它对每一行做每个键的读检查以证实他们确实指向正确的行。
这在一个有很多键的大表上可能花很长时间。
myisamchk通常将在它发现第一个错误以后停止。
如果你想要获得更多的信息，你能增加--verbose（-v）选项。
这使得myisamchk或isamchk继续一直到最多20个错误。
在一般使用中，一个简单的标准检查（没有除表名以外的参数）就足够了。
中等程度的检查
指定选项--medium-check或-m
myisamchk-mtbl_name
中的程度的检查不如扩展检查彻底，但速度快一些。
其意义不大，较少使用。
如果对于--extend-check检查不报告错误，则可以肯定表是完好的。
如果你仍然感觉表有问题，那原因肯定在其它地方。
应重新检查人和好像有问题的查询以验证查询是正确书写的。
如果你认为问题可能是MySQL服务器的原因，应该考虑整理一份故障报告或者升级到新的版本上。
可能有用的检查选项：
1、-i或—information打印统计信息,例如：
myisamchk-e-itbl_name
象前面的命令一样，但是-i选项告诉myisamchk还打印出一些统计信息。
2、-C,--check-only-changed
只检查上一次检查后被修改的表
6.1.3修复数据库表
一张损坏的表的症状通常是查询意外中断并且你能看到例如这些错误：
●“tbl_name.frm”被锁定不能改变。
●不能找到文件“tbl_name.MYI”（Errcode：
###）。
●从表处理器的得到错误###（此时，错误135是一个例外）。
●意外的文件结束。
●记录文件被毁坏。
在这些情况下，你必须修复表。
表的修复是一项非常困难的工作，很多情况下令人束手无策。
然而，有一些常规的知道思想和过程，可以遵循它们来增加修正表的机会。
通常，开始是可以用最快的修复方法，看看能否袖珍故障。
如果发现不成功，可以逐步升级到更彻底的但更慢的修复方法。
如果仍旧难以修复，就应该从备份中恢复了。
在上一章已经详细介绍了这一部分内容。
1、简单安全的修复
为了修复一个表执行下列步骤：
●首先，用--recover,-r选项修正表，并且用--quick，-q选项，来只根据索引文件的内容进行恢复。
这样不接触数据文件来修复索引文件。
（-r意味着“恢复模式”）
myisamchk-r-qtbl_name
isamchk-r-qtbl_name
●如果问题仍旧存在，则忽略--quick选项，允许修复程序修改数据文件，因为这可能存在问题。
下面的命令将从数据文件中删除不正确的记录和已被删除的记录并重建索引文件：
myisamchk-rtbl_name
isamchk-rtbl_name
●如果前面的步骤失败，使用。
安全恢复模式使用一个老的恢复方法，处理常规恢复模式不行的少数情况（但是更慢）。
myisamchk--safe-recovertbl_name
isamchk--safe-recovertbl_name
2、困难的修理
如果在索引文件的第一个16K块被破坏，或包含不正确的信息，或如果索引文件丢失，你只应该到这个阶段。
在这种情况下，创建一个新的索引文件是必要的。
按如下这样的步骤做：
●定位到包含崩溃表的数据库目录中
●把数据文件移更安全的地方。
●使用表描述文件创建新的（空）数据和索引文件：
shell>mysqldb_name
mysql>DELETEFROMtbl_name;
mysql>quit
上述语句将重新创建新的空表，并使用表的的描述文件tbl_name.frm重新生成新的数据和索引文件。
●将老的数据文件拷贝到新创建的数据文件之中。
（不要只是将老文件移回新文件之中；你要保留一个副本以防某些东西出错。
）
●在使用标准的修复方法。
现在myisamchk-r-q应该工作了。
（这不应该是一个无限循环）。
如果你拥有表的备份文件，那么一切过程就容易的多。
从备份文件中可以恢复表的描述文件，然后在检查表，有可能还要继续使用标准的修复方法，应该纠可以解决问题了。
3、非常困难的修复
只有描述文件也破坏了，你才应该到达这个阶段。
这应该从未发生过，因为在表被创建以后，描述文件就不再改变了。
从一个备份恢复描述文件并且回到阶段2。
你也可以恢复索引文件并且回到阶段1。
对于后者，你应该用myisamchk-r启动。
如果因为某种原因，数据的备份文件丢失或者没有备份文件，但是你还记得建立表的CREATETABLE语句，那么太好了，这样还是可以恢复索引文件：
●定位到包含崩溃表的数据库目录中
●把数据文件移更安全的地方。
再把数据库目录中的对应的目录删去.。
●调用mysql并发复CREATETABLE语句建立该表。
●退出mysql，将原始的数据文件和索引文件移回到数据库的目录中，替换刚才新建的文件。
●然后回到阶段2，修复表。
也可以只移回数据文件，这样保留新的描述和索引文件，然后回到阶段1，继续用标准的方法修复表。
6.1.4优化数据库表
修复表的方法，同样可以用来对数据表进行优化。
为了组合成碎片的记录并且消除由于删除或更新记录而浪费的空间，以恢复模式运行myisamchk和isamchk：
shell>myisamchk-rtbl_name
shell>myisamchk-rtbl_name
你可以用SQL的OPTIMIZETABLE语句使用的相同方式来优化一张表，OPTIMIZETABLE比较容易，但是myisamchk更快。
也没有在一个实用程序和服务器之间不必要的交互可能性，因为当你使用OPTIMIZETABLE时，服务器做所有的工作：
OPTIMIZETABLEtbl_name
6.1.5指定维护过程中使用的内存
myisamchk和isamchk的运行可能会花很长的时间，尤其是正在处理一个达标或者使用一个更广泛的检查和修复方法时。
当你运行myisamchk和isamchk时，内存分配很重要。
myisamchk和isamchk使用不超过你用-O选项指定的内存量。
如果你想在很大的文件上使用myisamchk和isamchk，你首先应该确定你想要它使用多少内存。
缺省仅使用大约3M来修复。
通过使用更大的值，你能使myisamchk和isamchk更快地操作。
下面列出最重的控制程序使用的缓冲区大小的变量：
表6-1控制缓冲区的变量
变量
含义
key_buffer_size
用于存放索引块的缓冲区大小
read_buff_size
读操作用的缓冲区大小
sort_buffer_size
排序用的缓冲区大小
write_buffer_size
写操作用的缓冲区大小
要想查看任一个程序使用的这些变量的缺省值，可用--help选项运行该程序。
要想使用其它的值，可在该命令上使用--set-variablevariable=value或–Ovariable=value。
有些材料上说，您可以将变量的名字简化成key,read,sort和write，但是不是所有的分发的维护程序都可以这样，例如，如果有两个变量sort_buffer_size和sort_key_size，sort不能唯一决定使用那个变量，会产生一个错误。
因此建议你用完整的值。
例如，如果你有多于32M内存，你能使用例如这些选项（除了任何你可能指定的选项）：
shell>myisamchk-Osort_buffer_size=16M-Okey_buffer_size=16M-Oread_buff_size=1M-Owrite_buffer_size=1M...
shell>myisamchk-Osort_buffer_size=16M-Okey_buffer_size=16M-Oread_buff_size=1M-Owrite_buffer_size=1M...
使用-Osort=16M应该可能对大多数情形就足够了。
--sort_buffer_size只能利用--recover选项来使用（而不是利用--safe_recover）,并且在这种情况下，key_buffer不能使用。
必须明白，myisamchk和isamchk使用在TMPDIR里面的临时文件。
如果TMPDIR指向一个内存文件系统，你可能很容易得到内存溢出的错误。
如果它发生，设定TMPDIR指向有更多空间的某个目录并且重启myisamchk和isamchk。
6.1.6总结
本节介绍了myisamchk和isamchk实用程序的基本用法。
本节主要介绍了如何一步步的检查表是否有问题，如何一步步修复有问题的表，以及如何使用这两个实用程序优化表。
如果你拥有大量表，检查和修复时将需要消耗大量内存，注意如何在使用过程中为维护程序指定使用的内存数量。
6.2避免与MySQL服务器交互作用
如果你同时运行表的检查/修复程序时，你或许不想让MySQL服务器和实用程序同时访问一个表。
如果两个程序都向表中写数据显然会造成很大的麻烦，甚至会有意外情况发生。
如果表正由一个程序写入，同时进行读取的另一个程序也会产生混乱的结果。
6.2.1锁定表的的方法
防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。
如果你关闭数据库，就可以保证服务器和myisamchk和isamchk之间没有交互作用。
但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用。
本节主要讨论的过程，是避免服务器和myisamchk或isamchk之间的交互作用。
实现这种功能的方法是对表进行锁定。
服务器由两种表的锁定方法。
1、内部锁定。
内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰。
也可以利用内部锁定机制防止服务器在利用myisamchk或isamchk检查或修复表时对表的访问。
语法：
锁定表：
LOCKTABLEStbl_name{READ|WRITE},[tbl_name{READ|WRITE},…]
解锁表：
UNLOCKTABLES
LOCKTABLES为当前线程锁定表。
UNLOCKTABLES释放被当前线程持有的任何锁。
当线程发出另外一个LOCKTABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。
如果一个线程获得在一个表上的一个READ锁，该线程（和所有其他线程）只能从表中读。
如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。
每个线程等待（没有超时）直到它获得它请求的所有锁。
WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。
这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁,随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。
显然对于检查，你只需要获得读锁。
再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。
对于修复，你必须获得些所以防止任何客户机在你对表进行操作时修改它。
2、外部锁定
服务器还可以使用外部锁定（文件级锁）来防止其它程序在服务器使用表时修改文件。
通常，在表的检查操作中服务器将外部锁定与myisamchk或isamchk作合使用。
但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。
对运行myisamchk或isamchk所选择的过程取决于服务器是否能使用外部锁定。
如果不使用，则必修使用内部锁定协议。
如果服务器用--skip-locking选项运行，则外部锁定禁用。
该选项在某些系统中是缺省的，如Linux。
可以通过运行mysqladminvariables命令确定服务器是否能够使用外部锁定。
检查skip_locking变量的值并按以下方法进行：
●如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。
服务器和实用程序将合作对表进行访问。
但是，运行任何一个实用程序之前，应该使用mysqladminflush-tables。
为了修复表，应该使用表的修复锁定协议。
●如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，最好关闭服务器。
如果坚持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。
必须使用卡党的锁定协议告诉服务器是该表不被其他客户机访问。
6.2.2检查表的锁定协议
本节只介绍如果使用表的内部锁定。
对于检查表的锁定协议，此过程只针对表的检查，不针对表的修复。
调用mysql发布下列语句：
$mysql–uroot–pdb_name
mysql>LOCKTABLEtbl_nameREAD;
mysql>FLUSHTABLES;
该锁防止其它客户机在检查时写入该表和修改该表。
FLUSH语句导致服务器关闭表的文件，它将刷新仍在告诉缓存中的任何为写入的改变。
2、执行检查过程
$myisamchktbl_name
$isamchktbl_name
3、释放表锁
mysql>UNLOCKTABLES;
如果myisamchk或isamchk指出发现该表的问题，将需要执行表的修复。
6.2.3修复表的锁定协议
本节只介绍如果使用表的内部锁定。
修复表的锁定过程类似于检查表的锁定过程，但有两个区别。
第一，你必须得到写锁而非读锁。
由于你需要修改表，因此根本不允许客户机对其进行访问。
第二，必须在执行修复之后发布FLUSHTABLE语句，因为myisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速缓存，否则服务器不会注意到这个改变。
本例同样适合优化表的过程。
1、调用mysql发布下列语句：
$mysql–uroot–pdb_name
mysql>LOCKTABLEtbl_nameWRITE;
mysql>FLUSHTABLES;
2、做数据表的拷贝，然后运行myisamchk和isamchk：
$cptbl_name.*/some/other/dir
$myisamchk--recovertbl_name
$isamchk--recovertbl_name
--recover选项只是针对安装而设置的。
这些特殊选项的选择将取决与你执行修复的类型。
3、再次刷新高速缓存，并释放表锁
mysql>FLUSHTABLES;
mysql>UNLOCKTABLES;
6.2.4总结
维护不是简单的运行myisamchk维护程序就可以的。
因为大多数情况下，管理员进行表的检查和修复时，服务器都要持续运行，因此如果方法不当，很可能维护程序会与服务器产生冲突。
本节对锁定表的方法做了详细的介绍，表的锁定包括内部锁定和外部锁定，本节主要介绍了内部锁定，对于检查和修复，使用的锁定协议也不同，分别为读锁定和写锁定。
通过表的锁定，可以避免维护过程中与服务器发生的交互作用。
6.3日志文件维护
由于日志文件是恢复数据库数据的重要参考，因此日志文件的维护也有十分重要的意义。
当MySQL与日志文件一起使用时，你有时想要删除/备份旧的日志文件并且告诉MySQL在新文件中开始记录。
6.3.1如何使用新的更新日志
如果你只使用一个更新日志，你只须清空日志文件，然后移走旧的更新日志文件到一个备份中，然后启用新的更新日志。
用下列方法可以强制服务器启用新的更新日志：
●mysqladminflush-logs
你一般需要在命令行提供使用的数据库用户：
mysqladmin–uroot–pflush-logs
●mysqladminrefresh
你一般需要在命令行提供使用的数据库用户：
mysqladmin–uroot–prefresh
如果你正在使用MySQL3.21或更早的版本，你必须使用mysqladminrefresh。
●SQL命令
FLUSHLOGS
●重启服务器
上述方法都具有这样的功能：
关闭并且再打开标准和更新记录文件。
如果你指定了一个没有扩展名的更新记录文件，新的更新记录文件的扩展数字将相对先前的文件加1。
mysql>FLUSHLOGS;
6.3.2如何使用新的常规日志
用上一小节的方法同样可以强制更新常规日志。
要准备备份常规日志，其步骤可能复杂一些：
$cdmysql-data-directory
$mvmysql.logmysql.old
$mysqladminflush-tables
然后做一个备份并删除“mysql.old”。
6.3.3总结
在维护表的过程中，经常涉及到日志文件的操作。
本节涉及如何启用新的日志文件，包括更新日志和常规日志。
这里所述的方法，同样也适用二进制日志，有关二进制日志的知识，请看前一章内建复制的部分。
6.4建立日常维护规范
在一个定期基础而非等到问题出现才实施数据库表的检查是一个好主意。
为维护目的，你能使用myisamchk-s检查桌子。
-s选项使myisamchk以沉默模式运行，当错误出现时，仅仅打印消息。
6.4.1建立一个数据库表维护规范
在一个定期基础而非等到问题出现才实施数据库表的检查是一个好主意。
应该考虑到建立一个预防性维护的时间表，以协助自动问题，是你可以采取措施进行修正：
执行常规的数据库备份并允许更新日志。
安排定期的常规表检查。
通过检查表，将减少使用备份的机会。
这个工作，在Windows下使用计划任务，Unix使用cron作业（一般从运行服务器所示用的该帐号的crontab文件中调用），并且很容易实现。
例如，你作为mysql用户运行服务器，则可以从mysql的crontab文件中建立定期检查。
如果你不知道如何使用cron，应使用下列命令查看相关的Unix手册页：
$mancron
$mancrontab
作为服务器启动前的系统引导期间检查数据库表。
及其可能会因早期的崩溃而重新启动。
如果这样的花，数据库表可能已被毁坏，应该对它进行彻底检查。
6.4.2创建一个适用于定期维护的脚本
为了运行自动的表检查，可以编写一个脚本，将目录改变为服务器数据目录并对所有的数据库表进行myisamchk和isamchk。
如果你只有MyISAM表或者只有ISAM表，则只需要其中一个程序，可以将无关的那个程序从脚本中注释掉。
该教本可以被cron调用，或者在系统启动期间被调用。
为维护目的，你能使用myisamchk-s检查桌子。
-s,--silent选项使myisamchk和isamchk以沉默模式运行，只有当错误出现时，才仅仅打印消息。
另外myisamchk支持--fast选项,该选项允许程序跳过自上次检查以来没有被修改过的人和表。
1、一个简单的脚本
例如，一个较为容易理解的简单脚本，它在服务器目录中检查所有表（DATADIR应该修改成对应你系统的合适的值）
升级会员 