第七章 备份和恢复

Table of Contents

7.1 备份和恢复类型
7.2 备份方式
7.3 备份和恢复策略示例
7.3.1 建立一个备份策略
7.3.2 使用备份做恢复
7.3.3 备份策略摘要
7.4 用mysqldump备份
7.4.1 mysqldump以SQL格式dump数据
7.4.2 加载SQL格式的备份
7.4.3 mysqldump以分隔符格式dump数据
7.4.4 加载分隔符格式的备份
7.4.5 mysqldump 技巧
7.5 使用二进制日志做Point-in-Time(增量)恢复
7.5.1 使用事件时间做Point-in-Time恢复
7.5.2 使用事件Positions做Point-in-Time恢复
7.6 MyISAM 表维护和故障恢复
7.6.1 使用myisamchk做故障恢复
7.6.2 如何检查MyISAM表中的错误
7.6.3 如何修复MyISAM表
7.6.4 MyISAM 表优化
7.6.5 建立MyISAM表维护计划

备份数据库非常重要,以至于发生问题时你可以恢复数据并且试制正常运行,例如:系统崩溃,硬件损坏或者数据误删除。有一个备份片同样是在升级MySQL版本前的基本安全保障,备份片也可以作为一个安装版的MySQL移植到其他系统上,或者搭建主从。

MySQL提供一系列的备份策略,你可以从中选择最符合需求的方式,本章主题将讨论几种你应该熟悉的备份和恢复方式。

其他资源信息

数据可用性相关的备份和维护包括一下内容:

7.1 备份和恢复的类型

本节描述不同类型备份的特点。

物理(Raw)备份与逻辑备份

物理备份存储数据库内容的拷贝,包含原始目录和文件。这种备份类型适合问题发生时,需要快速恢复数据量大和重要的数据库。

逻辑备份,是保存数据库结构(CREATE DATABASE, CREATE TABLE 语句) and 内容(INSERT 语句或带有分隔符的文本文件). 这种类型的备份方式适合数据量小,同时你可能编辑数据内容或表结构,或者在不同架构的机器上重建数据。

物理备份方式有以下几个特点:

  • 备份包括数据库目录和文件的精确副本。典型的就是拷贝全部或部分MySQL的数据目录。

  • 物理备份方式因为只是文件拷贝不涉及转换,所以快于逻辑备份。

  • 输出比逻辑备份更简单。

  • 因为MySQL企业备份产品进行物理备份是速度和紧凑性对于重要和繁忙的数据库来说是非常重要,详细内容请看MySQL企业备份产品 参阅 Section 29.2, “MySQL企业备份概述”.

  • 备份和恢复粒度范围从整个数据目录的水平降低到单个文件的级别,这是根据不同的存储引擎提供的粒度级别决定的。例如, InnoDB 表可以单个独立的文件,也可以与其他 InnoDB 表共享存储文件。; 每个MyISAM MyISAM表只对应唯一的一组文件。

  • 除了数据库,备份可包括其他相关文件,例如日志文件或配置文件。

  • 备份MEMORY表的数据比较麻烦。因为数据记录不是存储在磁盘上(MySQL企业备份产品有一个特性可以在备份 MEMORY 表时抽取数据。)

  • 具有相同或相近硬件特性的机器,备份集才具有可移植性。

  • 当MySQL服务器没有运行时,也可以执行备份。如果服务在运行,那么有必要锁住应用,以至于在备份过程中数据不会改变。MySQL企业备份会自动的加上所。

  • 物理备份工具包括针对 InnoDB的MySQL企业备份工具mysqlbackup 或其类型他表,或对于MyISAM表的文件系统命令(例如:cp, scp, tar, rsync)。

  • 对于恢复:

    • MySQL企业备份在备份的时候会修复InnoDB和其他表。

    • ndb_restore 修复NDB表。

    • 文件系统级别可以使用文件命令将文件拷贝回原来的位置

逻辑备份方式有以下特点:

  • 这种备份方式是通过查询MySQL服务器来获取数据结构和记录信息。

  • 逻辑备份比物理备份方式慢,因为必须访问服务器的数据库信息,同时将其转换为相应逻辑格式。如果是客户端发送的命令,服务器还必须将数据发送给备份程序。

  • 数据文件要比物理备份要大,尤其是以text格式保存。

  • 备份恢复的粒度,可以是服务器级别(所有的数据库)、数据库级别(个别库中所有的表)或者表级别,而且不区分存储引擎。

  • 备份不包括日志文件或配置文件,或其他相关文件。

  • 逻辑格式存储的备份与机器无关,且具有高度的可移植性。

  • 逻辑备份是在MySQL服务器运行的时候执行,服务不能离线。

  • 逻辑备份工具包括,mysqldump程序以及 SELECT ... INTO OUTFILE 语句,它们适用于任何存储引擎,甚至是MEMORY

  • 使用逻辑备份做恢复,SQL格式dump出来的文件可以在mysql客户端执行,使用LOAD DATA INFILE语句或mysqlimport客户端加载text分隔符文件。

在线与离线备份

在线备份是在MySQL服务器运行时进行,以至于数据库信息能从服务器获取。你先备份是当服务停止时进行。也可以用热被冷备 来描述。温备是指当从外部访问数据库文件时,服务器处于运行状态但是锁定了对数据的修改。

在线备份方式有这些特点:

  • 这种备份对其他的客户端影响较小,它可以在备份过程中连接到MySQL服务器,并且更具需要执行的选项来访问数据。

  • 必须注意的是,需要施加适当的锁,来保证数据不会被修改而影响备份的完整性。MySQL企业备份产品会自动锁定等。

离线备份方式有以下特点:

  • 因为服务器在备份过程中不可访问,所以客户端会受到影响。由于这个原因,这样的备份一般都是在复制结构的从机上执行,来取代离线备份,同时保证可用性。

  • 因为不受客户端干扰,所以备份过程比较简单。

在线和离线在恢复操作和应用类似的特性。然而,由于恢复需要强制锁,所以在线恢复相对与在线备份对客户端的影响更大。在备份期间,客户端可能一开始就会读数据,恢复不仅会读取数据,还会修改数据。所以当开始恢复的时需要避免客户端访问数据。

本地和远程备份

本地备份是在与MySQL服务所运行的主机上执行备份。而远程备份是从不同的主机上完成。对于某些类型的备份,可从远程主机发起,即使输出写是在本地服务器。

  • mysqldump mysqldump可以从本地或者远程连接服务器。对于SQL输出(CREATEINSERT语句)本地和远程都可以完成,同时在客户端输出生成。对于输出含有分隔符的文本(使用--tab选项)数据文件是在服务器主机上创建。

  • SELECT ... INTO OUTFILE 可在本地和远程的客户端主机发起,但是输出文件上你在服务器主机上创建。

  • 物理备份方式通常可以在MySQL服务器主机本地启动,以至于服务器能够离线。尽管文件可能是从远程拷贝过来的。

快照备份

有些文件系统在安装时开启了镜像功能,这些根据给出的时间点对文件系统提供逻辑拷贝,进而无需要对所有的文件系统进行物理拷贝,(例如,在安装时可以使用了写时复制技术,因此只需要复制快照时间之后被修改的那部分文件系统。)MySQL数据自身没有提供文件系统快照功能,但可以通过第三方解决方案,例如:Veritas,LVM或ZFS。

全备和增量备份

一个全量备份是在一个给出的时间点,包含一个MySQL服务器管理的所有文件。增量备份是由一个时间段内更改的数据组成(从一个时间点到另外一个时间点)。MySQL有不同的方式执行全量备份,像次小节之前描述的。增量备份可能通过服务器记录数据库变化的二进制日志来生成。

全量和时间点(增量)恢复

使用全量备份做全量恢复来恢复所有的数据,使得数据库实例恢复到备份时的状态。如果实例状态不是完全的当前,可以在全量恢复后,使用基于此全量备份之后的增量备份。使得数据库更接近于当前状态。

增量恢复是恢复一个给定的时间段内数据的改变。这同样也叫做时间点恢复,因为它记录的是一个服务器从当前状态到一个给定的时间。时间点恢复是更具二进制日志。通常情况是紧跟服务器使用备份文件进行全量恢复之后。这个备份文件是记录服务器备份时候的状态,之后的数据更改会记录到二进制日志中,二进制日志被增量恢复应用来修改数据,使得服务器恢复到需要恢复的时间点。

表维护

如果表受损那么数据的完整性就会被影响。例如:InnoDB表,对于 MyISAM表,程序会检查并修复出现的问题。参阅7.6节, “MyISAM表维护和灾难恢复”

备份调度、压缩和加密

备份调度对于自动化备份过程是很有价值的。对备份输出的压缩减少了空间需求,并且对输出的加密提供了更好的安全性,以防止对备份数据的未经授权的访问。MySQL本身不提供这些功能。MySQL企业备份产品可以压缩InnoDB备份,可以使用文件系统实用程序实现对备份输出的压缩或加密。其他可用的第三方解决方案。

7.2 数据库备份方式

本节概述几种常用的备份方式。

使用MySQL企业级备份进行热备

MySQL企业版的客户才能使用 MySQL Enterprise Backup产品对整个实例或选择的库、表,或两者一起,进行 物理备份。此产品提供的功能包括增量压缩 部分。 使用物理备份比逻辑备份做恢复更快。对InnoDB表的备份是使了 热备 原理。 (理想情况下, 大部分的数据都应该使用InnoDB表。) 而对其他存储引擎的表,测试使用 了温备 原理。对于MySQL企业级备份产品的概述,请参阅 29.2 节, “MySQL 企业级备份概述”

使用mysqldump备份

mysqldump 程序可以做备份,它可以备份所有类型 的表。(参阅 7.4 节, “使用mysqldump做备份”.)

对于InnoDB 表, 可以用 mysqldump使用 --single-transaction 选项来对表进行不锁表在线备份。 参阅7.3.1, “建立一个备份策略”

创建分隔符文本文件备份

创建一个含有表数据的文本文件,您可以使用 SELECT * INTO OUTFILE 'file_name' FROM tbl_name。文件创建在MySQL服务器主机上,而不再客户端主机。对于该语句,输出的文件不能已经存在,因为允许文件覆盖会构成安全分享。参阅 13.2.10 节, “SELECT 语法”。这种方法适用于任何类型的数据文件,但只保存表数据,而不是表结构。

另一个创建文本数据文件(含有备份表的 CREATE TABLE语句)的方法,就是使用mysqldump加上 --tab 选项。参阅 7.4.3 节, “使用mysqldump以分隔符格式dump数据”

加载一个分隔符文本数据文件。可以使用 LOAD DATA INFILEmysqlimport

开启二进制日志,创建增量备份

MySQL支持增量备份:您必须在启动服务器时,使用 --log-bin选项来启用二进制日志记录,参阅 5.4.4 节, “二进制日志”。 二进制文件提供了,数据库备份之后,数据库中的变化信息。 现在你想要做一个增量备份(包含自数据库上一次全量或增量备份以来的所有变化),您应该使用 by using FLUSH LOGS来切换二进制日志,做完后,您需要将从上一次全量或增量备份的所有二进制日志文件复制到备份位置,这些二进制日志是增量备份,在恢复的时候,使用 7.5 节, “使用二进制日志做Point-in-Time (增量)恢复”中介绍的应用它们。下一次做全量备份时,您应该使用 using FLUSH LOGS或 or mysqldump --flush-logs来切换日志,参阅 4.5.4 节, “mysqldump — 一个数据库备份程序”

使用复制从机创建备份

如果在进行备份的时候,主服务有性能问题,一种策略就是建立复制,在从机上执行备份而不是在主机上备份。参阅 18.3.1 节, “使用复制做备份”

如果您使用复制来备份服务器,那么当您在从机上备份数据库时,不管是使用的什么备份方式,都应该备份的主信息和中继日志资料库。(参阅 18.2.4 节, “复制的中继和状态日志”) 当你恢复了从机的数据,这些信息文件总是需要重新配置复制。如果你的从机是复制 LOAD DATA INFILE 语句,您还应该备份目录下给从机使用的所有SQL_LOAD-*文件。从机需要这些文件来来恢复 LOAD DATA INFILE 操作间任何被中断的复制记录, --slave-load-tmpdir选项的值就是这个目录的位置。如果服务器启动是没有使用这个选项,那么系统变量 tmpdir的值就是此目录的位置。

恢复损坏的表

如果您必须恢复已经损坏的MyISAM表,先尝试使用 REPAIR TABLEmyisamchk -r恢复他们。这在99.9%的情况下是有效的。如果 myisamchk 失败,参阅 7.6, “MyISAM表维护和故障修复”

使用文件系统快照进行备份

如果您使用的是Veritas文件系统,你可以做这样的备份:

  1. 在一个客户端程序的一个界面执行 FLUSH TABLES WITH READ LOCK

  2. 在另一个界面执行mount vxfs snapshot

  3. 回到第一个界面,执行 UNLOCK TABLES

  4. 复制快照文件。

  5. 卸载快照。

其他文件系统上可能有类似的快照功能,如,LVM或ZFS。

7.3 备份恢复策略实例

本节讨论执行备份的过程,该过程使您能够在几种类型的崩溃之后恢复数据:

  • 操作系统崩溃

  • 断电

  • 文件系统崩溃

  • 硬件问题(硬盘、主板等等)

示例命令中的 mysqldumpmysql 客户端工具,没有 --user--password的选项 。您应该将这些必要的选项包含在内,以便客户端程序连接到MySQL服务器。

假设数据存储在InnoDB存储引擎,同样还假设,MySQL服务器在加载数据的时候崩溃,如果不是这样,就不需要做任何恢复。

对于像操作系统崩溃或者断电的情况,我们可以假定在重新启动后,MySQL的磁盘数据是可用的。 InnoDB数据文件可能由于崩溃导致了数据不一致,但是 InnoDB 读取自己的日志, 并在列表中找到待提交,和已提交单未被刷新到数据文件的事务。 InnoDB 自动回滚这些没有提交的事务,并且将已提交的事务刷新到数据文件。 通过MySQL错误日志将这个恢复过程的信息传递给用户。下面是一个示例日志摘录:

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

对于文件系统崩溃或硬件问题,我们可以假定在重新启动之后MySQL磁盘数据是不可用的。这意味着MySQL无法成功启动,因为某些块的磁盘数据已不再可读。在这种情况下,有必要重新格式化磁盘,安装一个新的,或者纠正根本的问题。然后,有必要从备份中恢复MySQL数据,这意味着备份必须已经完成。为了确保这种情况的发生,那么需要设计并实现一个备份策略。

7.3.1 建立一个备份策略

为了备份具有可用性,需要定期的备份。在MySQL上,有几种工具可以进行全库备份(某个时间点数据的快照)。例如, To be useful, backups must be scheduled regularly. A full backup (a snapshot of the data at a point in time) can be done in MySQL with several tools. For example, MySQL 企业级别发可以对整个实例进行 物理备份,通过优化来最小化开销,并在备份InnoDB数据文件时避免中断; mysqldump提供在线 provides online 逻辑别发。本节我们讨论使用 mysqldump

假设在周日下午1点,此时负载低,使用下面的命令对所有库下的 InnoDB表进行一次全备。

shell> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql

mysqldump结果产生的 .sql 文件,是一组含有 INSERT语句的文件,可以在后面重载被dump的表时使用。

这个备份操作在开始进行dump的时候,在所有的表上需要一个全局的读锁 (使用 FLUSH TABLES WITH READ LOCK)。 一旦获得这个锁,就会读取二进制日志的坐标,并释放锁。如果在发出FLUSH 语句时运行耗时长的更新语句,那么备份操作可能会停止,直到这些语句结束。在那之后,转储文件会自动锁定,不会影响到表上的读写操作。

之前假设备份的都是 InnoDB表,所以 tables, so --single-transaction使用了一个一致性读,来保证 mysqldump读取的数据不会变化。(意思就是,其它端对 InnoDB 表的改变,不会被 mysqldump进程看到。)如果备份操作含有非事务性表,一致性的需求,需要他们在备份期间没有任何改变。例如,对于 mysql库中的MyISAM表,必须在备份期间,MySQL帐户对其不会进行任何管理更改。

全备是有必要的,但是创建他们并不总是方便,因为他们生成大型的备份文件,且需要花费大量的时间生产。 它们并不是最优的,因为每次连续的完整备份都包含了所有的数据,甚至是自上一次全备以来没有改变的部分。进行初始的全备更有效,然后进行增量备份。增量备份更小,生成的时间也更少。这样做的代价是,在恢复的时候,您不能仅仅通过重新加载完整的备份来恢复您的数据。您还必须处理增量备份的数据。

创建增量备份。就是保存增量变化。在MySQL中,这些变化都是反应在二进制日志中,所以在启动服务器时,通常需要总是使用 --log-bin选项来启用次日志。当二进制日志开启后,服务器会将 每次数据的更新写到一个文件中。在MySQL服务器的数据目录下,使用 --log-bin选项启用二进制日志,下面的已经运行了数天,我们可以看到二进制日志:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

每次服务器启动时,MySQL都会按照序列号增长创建一个新的二进制日志,当服务器运行时,还可以关闭当前的二进制日志文件,然后使用SQL语句 FLUSH LOGS或者mysqladmin flush-logs命令,手动的开始一个新的日志。 mysqldump也有选项来刷新这个日志。数据目录下的 .index文件会列举出目录下所有的MySQL二进制日志。

MySQL二进制日志对于恢复非常重要,因为它们是一组增量备份。如果在进行全备份时,确保刷新了日志,那么之后创建的二进制日志文件包含备份以来所做的所有数据更改。现在修改前面的mysqldump命令,这样它在做全备时刷新MySQL二进制日志,以便dump文件中包含新当前二进制日志的名称:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

执行这个命令后,由于--flush-logs选项可以引起服务器刷新自身的日志,所以数据目录下会含有一个新的二进制日志文件gbichot2-bin.000007--master-data 选项会导致 mysqldump 将二进制日志信息写入输出,其结果是,.sqldump文件含有这些行:

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

因为mysqldump 命令是执行一个全备,所以这些行有两个含义:

  • dump文件包含在对gbichot2-bin.000007二进制日志文件之前,所做的所有更改。

  • 备份后的所有数据更改,都不会出现在dump文件中,而是存在于gbichot2-bin.000007二进制日志文件或之后的文件中。

在周一的下午1点,我们创建一个增量备份,同时刷新日志以启用新的二进制日志文件。例如, 执行命令mysqladmin flush-logs来创建 gbichot2-bin.000008。直到周二的下午2点进行全备,期间所有的数据改变都会在 gbichot2-bin.000007 文件中。所以这个增量的备份非常重要,因为最好是将其复制到一个安全的地方(如,备份到磁带或DVD,或者是其他机器上)。在周三下午1点,再次执行 mysqladmin flush-logs命令,这样从周一下午1点到周二下午1点,期间所有的改变都在 gbichot2-bin.000008中。

MySQL二进制日志会占用磁盘空间,为了释放空间,可以不定时的清理它们。一种方法就是删掉不再需要的二进制日志。如,当我们进行全备时:

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql
注意

如果您的服务器是复制架构中的主机,使用mysqldump --delete-master-logs删除二进制日志会很危险。因为有可能从机还没有完全应用完日志中的内容。对 PURGE BINARY LOGS语句的描述,以及在删除MySQL二进制日志之前需要进行的验证,请参阅 13.4.1.1 节, “清理二进制日志语法”

7.3.2 使用备份做恢复

现在,设想我们在周三早上8点遇见了灾难性的崩溃。现在需要从备份中恢复。对于恢复,首先我们需要使用全备(周日下午1点的)进行恢复,全备文件仅是一组SQL语句,所以恢复它非常简单:

shell> mysql < backup_sunday_1_PM.sql

到这里,数据已经恢复到周日下午1点了。为了恢复从那之后的改变,我们必须使用增量备份,就是 gbichot2-bin.000007gbichot2-bin.000008 二进制文件。从存放它们的地方获取,然后,像这样处理它们:

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

现在我们将数据恢复到了周二下午1点的状态,但是仍然缺失从那是到崩溃时之间的改变。为了不丢失它们,我们需要让MySQL服务器将它的二进制日志保存在一个不同于数据文件的安全的位置(RAID磁盘、SAN、...),以至于这些日志不会在损坏的磁盘上。 (也就是, 在启动服务器时,使用--log-bin选项来指定一个不同于数据目录的物理磁盘,这样,即使数据目录的磁盘丢失了,日志还是安全的 .) ,如果已经这样做了,那么我们手上就会有 gbichot2-bin.000009文件(或其它后续的文件),然后我们可以使用 mysqlbinlogmysql来恢复最新的数据变化,直至崩溃的那一刻:

shell> mysqlbinlog gbichot2-bin.000009 ... | mysql

更多有关使用mysqlbinlog处理二进制日志文件的信息,请参阅 7.5 节, “使用二进制日志进行Point-in-Time(增量)恢复”

7.3.3 备份策略摘要

在操作系统崩溃或电源故障时,InnoDB 负责所有的数据恢复数据工作。但是为了确保你能睡得好,请遵守以下的指导方针:

7.4 使用mysqldump做备份

本节介绍如何使用mysqldump 生成dump文件,以及如何重载dump文件,一个dump文件有多个用法:

  • 作为备份,以便在数据丢失时进行数据恢复。

  • 作为数据源,建立复制从机。

  • 做为测试用的数据源

    • 在不改变原数据的情况下,复制一个你可以使用的数据库。

    • 测试升级时,潜在的不兼容性。

根据是否给定--tab选项, mysqldump可以产生两种类型的输出。

  • 没有 --tabmysqldump以SQL语句的形式,标准输出。输出包含用于创建dump对象(库、表、存储程序等等)的 CREATE语句 和用于加载数据到表中的 INSERT语句。输出可以保存在一个文件,以后使用 mysql 可以重建dump的对象。有些选项可用来修改SQL语句的格式,以及控制哪些对象被转储。

  • With --tab, mysqldump produces two output files for each dumped table. The server writes one file as tab-delimited text, one line per table row. This file is named tbl_name.txt in the output directory. The server also sends a CREATE TABLE statement for the table to mysqldump, which writes it as a file named tbl_name.sql in the output directory.

7.4.1 mysqldump以SQL格式dump数据

本节描述如果使用mysqldump创建SQL格式的dump文件。关于重载这些dump文件的信息,参阅 7.4.2, “重载SQL格式的备份”

默认情况下, mysqldump将信息作为SQL语句写入到标准输出。您可以将输出保存在一个文件中:

shell> mysqldump [arguments] > file_name

调用mysqldump使用 --all-databases 选项dump全库:

shell> mysqldump --all-databases > dump.sql

仅dump指定库,使用--databases并,在命令行上命名他们:

shell> mysqldump --databases db1 db2 db3 > dump.sql

--databases选项会将命令行上所有的名字作为库名对待,如果没有这个选项, mysqldump将第一个名字视为库名,之后的名字视为表名。

使用--all-databases--databasesmysqldump 在dump每个库之前都会加上CREATE DATABASEUSE 语。这是确保dump文件在重载时,如果库名不存在,就会创建库名,并将其作为默认库,以便数据重载到跟原来相同的库名下。 如果您想让dump文件在重新创建之前强制删除每个数据库,可以使用 --add-drop-database 选项,这种情况下, mysqldump 会在每个 each CREATE DATABASE语句前,生成一个 DROP DATABASE语句。

dump一个库:

shell> mysqldump --databases test > dump.sql

在单个库的情况下,可以省略 --databases 选项:

shell> mysqldump test > dump.sql

前面两个命令的不同之处就是没有 --databases,dump输出不含有 CREATE DATABASEUSE 语句,这样就有几个影响:

  • 当您重载dump文件时,名必须指定一个默认的库名,以便于服务器知道载入到那个库。

  • 对重载,可以指定一个不同于原来的库名,这样就可以将数据载入不同的库。

  • 如果想要重载的库名不存在,那么必须先创建它。

  • 因为输出不含有 CREATE DATABASE 语句,那么 --add-drop-database 选项就没有作用,如果您使用这个选项,就不会产生 DROP DATABASE 语句。

仅dump出一个库下的指定表,命令行上将表名卸载库名后:

shell> mysqldump test t1 t3 t7 > dump.sql

7.4.2 重载SQL格式的备份

To reload a dump file written by mysqldump that consists of SQL statements, use it as input to the mysql client. If the dump file was created by mysqldump with the --all-databases or --databases option, it contains CREATE DATABASE and USE statements and it is not necessary to specify a default database into which to load the data:

shell> mysql < dump.sql

Alternatively, from within mysql, use a source command:

mysql> source dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary):

shell> mysqladmin create db1

Then specify the database name when you load the dump file:

shell> mysql db1 < dump.sql

Alternatively, from within mysql, create the database, select it as the default database, and load the dump file:

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql
Note

For Windows PowerShell users: Because the "<" character is reserved for future use in PowerShell, an alternative approach is required, such as using quotes cmd.exe /c "mysql < dump.sql".

7.4.3 mysqldump以分隔符格式dump数据

This section describes how to use mysqldump to create delimited-text dump files. For information about reloading such dump files, see Section 7.4.4, “Reloading Delimited-Text Format Backups”.

If you invoke mysqldump with the --tab=dir_name option, it uses dir_name as the output directory and dumps tables individually in that directory using two files for each table. The table name is the base name for these files. For a table named t1, the files are named t1.sql and t1.txt. The .sql file contains a CREATE TABLE statement for the table. The .txt file contains the table data, one line per table row.

The following command dumps the contents of the db1 database to files in the /tmp database:

shell> mysqldump --tab=/tmp db1

The .txt files containing table data are written by the server, so they are owned by the system account used for running the server. The server uses SELECT ... INTO OUTFILE to write the files, so you must have the FILE privilege to perform this operation, and an error occurs if a given .txt file already exists.

The server sends the CREATE definitions for dumped tables to mysqldump, which writes them to .sql files. These files therefore are owned by the user who executes mysqldump.

It is best that --tab be used only for dumping a local server. If you use it with a remote server, the --tab directory must exist on both the local and remote hosts, and the .txt files will be written by the server in the remote directory (on the server host), whereas the .sql files will be written by mysqldump in the local directory (on the client host).

For mysqldump --tab, the server by default writes table data to .txt files one line per row with tabs between column values, no quotation marks around column values, and newline as the line terminator. (These are the same defaults as for SELECT ... INTO OUTFILE.)

To enable data files to be written using a different format, mysqldump supports these options:

Depending on the value you specify for any of these options, it might be necessary on the command line to quote or escape the value appropriately for your command interpreter. Alternatively, specify the value using hex notation. Suppose that you want mysqldump to quote column values within double quotation marks. To do so, specify double quote as the value for the --fields-enclosed-by option. But this character is often special to command interpreters and must be treated specially. For example, on Unix, you can quote the double quote like this:

--fields-enclosed-by='"'

On any platform, you can specify the value in hex:

--fields-enclosed-by=0x22

It is common to use several of the data-formatting options together. For example, to dump tables in comma-separated values format with lines terminated by carriage-return/newline pairs (\r\n), use this command (enter it on a single line):

shell> mysqldump --tab=/tmp --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

Should you use any of the data-formatting options to dump table data, you will need to specify the same format when you reload data files later, to ensure proper interpretation of the file contents.

7.4.4 重载分隔符格式的备份

For backups produced with mysqldump --tab, each table is represented in the output directory by an .sql file containing the CREATE TABLE statement for the table, and a .txt file containing the table data. To reload a table, first change location into the output directory. Then process the .sql file with mysql to create an empty table and process the .txt file to load the data into the table:

shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt

An alternative to using mysqlimport to load the data file is to use the LOAD DATA INFILE statement from within the mysql client:

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

If you used any data-formatting options with mysqldump when you initially dumped the table, you must use the same options with mysqlimport or LOAD DATA INFILE to ensure proper interpretation of the data file contents:

shell> mysqlimport --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

Or:

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
    -> FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

7.4.5 mysqldump技巧

This section surveys techniques that enable you to use mysqldump to solve specific problems:

  • How to make a copy a database

  • How to copy a database from one server to another

  • How to dump stored programs (stored procedures and functions, triggers, and events)

  • How to dump definitions and data separately

7.4.5.1 Making a Copy of a Database

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

Do not use --databases on the mysqldump command line because that causes USE db1 to be included in the dump file, which overrides the effect of naming db2 on the mysql command line.

7.4.5.2 Copy a Database from one Server to Another

On Server 1:

shell> mysqldump --databases db1 > dump.sql

Copy the dump file from Server 1 to Server 2.

On Server 2:

shell> mysql < dump.sql

Use of --databases with the mysqldump command line causes the dump file to include CREATE DATABASE and USE statements that create the database if it does exist and make it the default database for the reloaded data.

Alternatively, you can omit --databases from the mysqldump command. Then you will need to create the database on Server 2 (if necessary) and specify it as the default database when you reload the dump file.

On Server 1:

shell> mysqldump db1 > dump.sql

On Server 2:

shell> mysqladmin create db1
shell> mysql db1 < dump.sql

You can specify a different database name in this case, so omitting --databases from the mysqldump command enables you to dump data from one database and load it into another.

7.4.5.3 Dumping Stored Programs

Several options control how mysqldump handles stored programs (stored procedures and functions, triggers, and events):

The --triggers option is enabled by default so that when tables are dumped, they are accompanied by any triggers they have. The other options are disabled by default and must be specified explicitly to dump the corresponding objects. To disable any of these options explicitly, use its skip form: --skip-events, --skip-routines, or --skip-triggers.

7.4.5.4 Dumping Table Definitions and Content Separately

The --no-data option tells mysqldump not to dump table data, resulting in the dump file containing only statements to create the tables. Conversely, the --no-create-info option tells mysqldump to suppress CREATE statements from the output, so that the dump file contains only table data.

For example, to dump table definitions and data separately for the test database, use these commands:

shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql

For a definition-only dump, add the --routines and --events options to also include stored routine and event definitions:

shell> mysqldump --no-data --routines --events test > dump-defs.sql

7.4.5.5 Using mysqldump to Test for Upgrade Incompatibilities

When contemplating a MySQL upgrade, it is prudent to install the newer version separately from your current production version. Then you can dump the database and database object definitions from the production server and load them into the new server to verify that they are handled properly. (This is also useful for testing downgrades.)

On the production server:

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

On the upgraded server:

shell> mysql < dump-defs.sql

Because the dump file does not contain table data, it can be processed quickly. This enables you to spot potential incompatibilities without waiting for lengthy data-loading operations. Look for warnings or errors while the dump file is being processed.

After you have verified that the definitions are handled properly, dump the data and try to load it into the upgraded server.

On the production server:

shell> mysqldump --all-databases --no-create-info > dump-data.sql

On the upgraded server:

shell> mysql < dump-data.sql

Now check the table contents and run some test queries.

7.5 使用二进制日志做Point-in-Time (增量)恢复

Point-in-time recovery refers to recovery of data changes made since a given point in time. Typically, this type of recovery is performed after restoring a full backup that brings the server to its state as of the time the backup was made. (The full backup can be made in several ways, such as those listed in Section 7.2, “Database Backup Methods”.) Point-in-time recovery then brings the server up to date incrementally from the time of the full backup to a more recent time.

Note

Many of the examples here use the mysql client to process binary log output produced by mysqlbinlog. If your binary log contains \0 (null) characters, that output cannot be parsed by mysql unless you invoke it with the --binary-mode option.

Point-in-time recovery is based on these principles:

  • The source of information for point-in-time recovery is the set of incremental backups represented by the binary log files generated subsequent to the full backup operation. Therefore, the server must be started with the --log-bin option to enable binary logging (see Section 5.4.4, “The Binary Log”).

    To restore data from the binary log, you must know the name and location of the current binary log files. By default, the server creates binary log files in the data directory, but a path name can be specified with the --log-bin option to place the files in a different location. Section 5.4.4, “The Binary Log”.

    To see a listing of all binary log files, use this statement:

    mysql> SHOW BINARY LOGS;
    

    To determine the name of the current binary log file, issue the following statement:

    mysql> SHOW MASTER STATUS;
    
  • The mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be executed or viewed. mysqlbinlog has options for selecting sections of the binary log based on event times or position of events within the log. See Section 4.6.8, “mysqlbinlog — Utility for Processing Binary Log Files”.

  • Executing events from the binary log causes the data modifications they represent to be redone. This enables recovery of data changes for a given span of time. To execute events from the binary log, process mysqlbinlog output using the mysql client:

    shell> mysqlbinlog binlog_files | mysql -u root -p
    
  • Viewing log contents can be useful when you need to determine event times or positions to select partial log contents prior to executing events. To view events from the log, send mysqlbinlog output into a paging program:

    shell> mysqlbinlog binlog_files | more
    

    Alternatively, save the output in a file and view the file in a text editor:

    shell> mysqlbinlog binlog_files > tmpfile
    shell> ... edit tmpfile ...
    
  • Saving the output in a file is useful as a preliminary to executing the log contents with certain events removed, such as an accidental DROP DATABASE. You can delete from the file any statements not to be executed before executing its contents. After editing the file, execute the contents as follows:

    shell> mysql -u root -p < tmpfile
    

If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server. Here is an example that demonstrates what may be unsafe:

shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

Processing binary logs this way using different connections to the server causes problems if the first log file contains a CREATE TEMPORARY TABLE statement and the second log contains a statement that uses the temporary table. When the first mysql process terminates, the server drops the temporary table. When the second mysql process attempts to use the table, the server reports unknown table.

To avoid problems like this, use a single connection to execute the contents of all binary logs that you want to process. Here is one way to do so:

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

Another approach is to write all the logs to a single file and then process the file:

shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

When writing to a dump file while reading back from a binary log containing GTIDs (see Section 18.1.3, “Replication with Global Transaction Identifiers”), use the --skip-gtids option with mysqlbinlog, like this:

shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"

7.5.1 使用事件时间做Point-in-Time恢复

To indicate the start and end times for recovery, specify the --start-datetime and --stop-datetime options for mysqlbinlog, in DATETIME format. As an example, suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL statement was executed that deleted a large table. To restore the table and data, you could restore the previous night's backup, and then execute the following command:

shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \
         /var/log/mysql/bin.123456 | mysql -u root -p

This command recovers all of the data up until the date and time given by the --stop-datetime option. If you did not detect the erroneous SQL statement that was entered until hours later, you will probably also want to recover the activity that occurred afterward. Based on this, you could run mysqlbinlog again with a start date and time, like so:

shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
         /var/log/mysql/bin.123456 | mysql -u root -p

In this command, the SQL statements logged from 10:01 a.m. on will be re-executed. The combination of restoring of the previous night's dump file and the two mysqlbinlog commands restores everything up until one second before 10:00 a.m. and everything from 10:01 a.m. on.

To use this method of point-in-time recovery, you should examine the log to be sure of the exact times to specify for the commands. To display the log file contents without executing them, use this command:

shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

Then open the /tmp/mysql_restore.sql file with a text editor to examine it.

Excluding specific changes by specifying times for mysqlbinlog does not work well if multiple statements executed at the same time as the one to be excluded.

7.5.2 使用事件Positions做Point-in-Time恢复

Instead of specifying dates and times, the --start-position and --stop-position options for mysqlbinlog can be used for specifying log positions. They work the same as the start and stop date options, except that you specify log position numbers rather than dates. Using positions may enable you to be more precise about which part of the log to recover, especially if many transactions occurred around the same time as a damaging SQL statement. To determine the position numbers, run mysqlbinlog for a range of times near the time when the unwanted transaction was executed, but redirect the results to a text file for examination. This can be done like so:

shell> mysqlbinlog --start-datetime="2005-04-20 9:55:00" \
         --stop-datetime="2005-04-20 10:05:00" \
         /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

This command creates a small text file in the /tmp directory that contains the SQL statements around the time that the deleterious SQL statement was executed. Open this file with a text editor and look for the statement that you do not want to repeat. Determine the positions in the binary log for stopping and resuming the recovery and make note of them. Positions are labeled as log_pos followed by a number. After restoring the previous backup file, use the position numbers to process the binary log file. For example, you would use commands something like these:

shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
         | mysql -u root -p

shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
         | mysql -u root -p

The first command recovers all the transactions up until the stop position given. The second command recovers all transactions from the starting position given until the end of the binary log. Because the output of mysqlbinlog includes SET TIMESTAMP statements before each SQL statement recorded, the recovered data and related MySQL logs will reflect the original times at which the transactions were executed.

7.6 MyISAM Table维护和故障修复

This section discusses how to use myisamchk to check or repair MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes). For general myisamchk background, see Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”. Other table-repair information can be found at Section 2.10.3, “Rebuilding or Repairing Tables or Indexes”.

You can use myisamchk to check, repair, or optimize database tables. The following sections describe how to perform these operations and how to set up a table maintenance schedule. For information about using myisamchk to get information about your tables, see Section 4.6.4.5, “Obtaining Table Information with myisamchk”.

Even though table repair with myisamchk is quite secure, it is always a good idea to make a backup before doing a repair or any maintenance operation that could make a lot of changes to a table.

myisamchk operations that affect indexes can cause MyISAM FULLTEXT indexes to be rebuilt with full-text parameters that are incompatible with the values used by the MySQL server. To avoid this problem, follow the guidelines in Section 4.6.4.1, “myisamchk General Options”.

MyISAM table maintenance can also be done using the SQL statements that perform operations similar to what myisamchk can do:

For additional information about these statements, see Section 13.7.2, “Table Maintenance Statements”.

These statements can be used directly or by means of the mysqlcheck client program. One advantage of these statements over myisamchk is that the server does all the work. With myisamchk, you must make sure that the server does not use the tables at the same time so that there is no unwanted interaction between myisamchk and the server.

7.6.1 使用myisamchk做故障修复

This section describes how to check for and deal with data corruption in MySQL databases. If your tables become corrupted frequently, you should try to find the reason why. See Section B.5.3.3, “What to Do If MySQL Keeps Crashing”.

For an explanation of how MyISAM tables can become corrupted, see Section 16.2.4, “MyISAM Table Problems”.

If you run mysqld with external locking disabled (which is the default), you cannot reliably use myisamchk to check a table when mysqld is using the same table. If you can be certain that no one will access the tables through mysqld while you run myisamchk, you only have to execute mysqladmin flush-tables before you start checking the tables. If you cannot guarantee this, you must stop mysqld while you check the tables. If you run myisamchk to check tables that mysqld is updating at the same time, you may get a warning that a table is corrupt even when it is not.

If the server is run with external locking enabled, you can use myisamchk to check tables at any time. In this case, if the server tries to update a table that myisamchk is using, the server will wait for myisamchk to finish before it continues.

If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not using the table (this also applies if external locking is disabled). If you do not stop mysqld, you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously.

When performing crash recovery, it is important to understand that each MyISAM table tbl_name in a database corresponds to the three files in the database directory shown in the following table.

FilePurpose
tbl_name.MYDData file
tbl_name.MYIIndex file

Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.

myisamchk works by creating a copy of the .MYD data file row by row. It ends the repair stage by removing the old .MYD file and renaming the new file to the original file name. If you use --quick, myisamchk does not create a temporary .MYD file, but instead assumes that the .MYD file is correct and generates only a new index file without touching the .MYD file. This is safe, because myisamchk automatically detects whether the .MYD file is corrupt and aborts the repair if it is. You can also specify the --quick option twice to myisamchk. In this case, myisamchk does not abort on some errors (such as duplicate-key errors) but instead tries to resolve them by modifying the .MYD file. Normally the use of two --quick options is useful only if you have too little free disk space to perform a normal repair. In this case, you should at least make a backup of the table before running myisamchk.

7.6.2 如何检查MyISAM表中的错误

To check a MyISAM table, use the following commands:

  • myisamchk tbl_name

    This finds 99.99% of all errors. What it cannot find is corruption that involves only the data file (which is very unusual). If you want to check a table, you should normally run myisamchk without options or with the -s (silent) option.

  • myisamchk -m tbl_name

    This finds 99.999% of all errors. It first checks all index entries for errors and then reads through all rows. It calculates a checksum for all key values in the rows and verifies that the checksum matches the checksum for the keys in the index tree.

  • myisamchk -e tbl_name

    This does a complete and thorough check of all data (-e means extended check). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a long time for a large table that has many indexes. Normally, myisamchk stops after the first error it finds. If you want to obtain more information, you can add the -v (verbose) option. This causes myisamchk to keep going, up through a maximum of 20 errors.

  • myisamchk -e -i tbl_name

    This is like the previous command, but the -i option tells myisamchk to print additional statistical information.

In most cases, a simple myisamchk command with no arguments other than the table name is sufficient to check a table.

7.6.3 如何修复MyISAM表

The discussion in this section describes how to use myisamchk on MyISAM tables (extensions .MYI and .MYD).

You can also use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. See Section 13.7.2.2, “CHECK TABLE Syntax”, and Section 13.7.2.5, “REPAIR TABLE Syntax”.

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

  • Can't find file tbl_name.MYI (Errcode: nnn)

  • Unexpected end of file

  • Record file is crashed

  • Got error nnn from table handler

To get more information about the error, run perror nnn, where nnn is the error number. The following example shows how to use perror to find the meanings for the most common error numbers that indicate a problem with a table:

shell> perror 126 127 132 134 135 136 141 144 145
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired

Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you must use ALTER TABLE to increase the MAX_ROWS and AVG_ROW_LENGTH table option values:

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

If you do not know the current table option values, use SHOW CREATE TABLE.

For the other errors, you must repair your tables. myisamchk can usually detect and fix most problems that occur.

The repair process involves up to three stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that mysqld runs as (and to you, because you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

This section is for the cases where a table check fails (such as those described in Section 7.6.2, “How to Check MyISAM Tables for Errors”), or you want to use the extended features that myisamchk provides.

The myisamchk options used for table maintenance with are described in Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”. myisamchk also has variables that you can set to control memory allocation that may improve performance. See Section 4.6.4.6, “myisamchk Memory Usage”.

If you are going to repair a table from the command line, you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still available for a while after mysqladmin returns, until all statement-processing has stopped and all index changes have been flushed to disk.

Stage 1: Checking your tables

Run myisamchk *.MYI or myisamchk -e *.MYI if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is stopped, you should use the --update-state option to tell myisamchk to mark the table as checked.

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try myisamchk -r -q tbl_name (-r -q means quick recovery mode). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the data file before continuing.

  2. Use myisamchk -r tbl_name (-r means recovery mode). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.

  3. If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).

Note

If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

  1. Move the data file to a safe place.

  2. Use the table description file to create new (empty) data and index files:

    shell> mysql db_name
    mysql> SET autocommit=1;
    mysql> TRUNCATE TABLE tbl_name;
    mysql> quit
    
  3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)

Important

If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL.

Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See Section 13.7.2.5, “REPAIR TABLE Syntax”.

7.6.4 MyISAM表优化

To coalesce fragmented rows and eliminate wasted space that results from deleting or updating rows, run myisamchk in recovery mode:

shell> myisamchk -r tbl_name

You can optimize a table in the same way by using the OPTIMIZE TABLE SQL statement. OPTIMIZE TABLE does a table repair and a key analysis, and also sorts the index tree so that key lookups are faster. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use OPTIMIZE TABLE. See Section 13.7.2.4, “OPTIMIZE TABLE Syntax”.

myisamchk has a number of other options that you can use to improve the performance of a table:

  • --analyze or -a: Perform key distribution analysis. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which indexes it should use.

  • --sort-index or -S: Sort the index blocks. This optimizes seeks and makes table scans that use indexes faster.

  • --sort-records=index_num or -R index_num: Sort data rows according to a given index. This makes your data much more localized and may speed up range-based SELECT and ORDER BY operations that use this index.

For a full description of all available options, see Section 4.6.4, “myisamchk — MyISAM Table-Maintenance Utility”.

7.6.5 建立MyISAM 表维护计划

It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur. One way to check and repair MyISAM tables is with the CHECK TABLE and REPAIR TABLE statements. See Section 13.7.2, “Table Maintenance Statements”.

Another way to check tables is to use myisamchk. For maintenance purposes, you can use myisamchk -s. The -s option (short for --silent) causes myisamchk to run in silent mode, printing messages only when errors occur.

It is also a good idea to enable automatic MyISAM table checking. For example, whenever the machine has done a restart in the middle of an update, you usually need to check each table that could have been affected before it is used further. (These are expected crashed tables.) To cause the server to check MyISAM tables automatically, start it with the --myisam-recover-options option. See Section 5.1.4, “Server Command Options”.

You should also check your tables regularly during normal system operation. For example, you can run a cron job to check important tables once a week, using a line like this in a crontab file:

35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

This prints out information about crashed tables so that you can examine and repair them as necessary.

To start with, execute myisamchk -s each night on all tables that have been updated during the last 24 hours. As you see that problems occur infrequently, you can back off the checking frequency to once a week or so.

Normally, MySQL tables need little maintenance. If you are performing many updates to MyISAM tables with dynamic-sized rows (tables with VARCHAR, BLOB, or TEXT columns) or have tables with many deleted rows you may want to defragment/reclaim space from the tables from time to time. You can do this by using OPTIMIZE TABLE on the tables in question. Alternatively, if you can stop the mysqld server for a while, change location into the data directory and use this command while the server is stopped:

shell> myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI