备份和恢复

目录

 

备份和恢复类型

数据库备份方式

备份和恢复策略

建立备份 Policy

使用备份进行恢复

备份策略摘要

使用 mysqldump 进行备份

使用 mysqldump 以 SQL 格式转储数据

 重新加载 SQL-Format 备份

使用mysqldump转储文本定界格式的数据

重新加载 Delimited-Text 格式备份

mysqldump 提示

 制作数据库副本

将数据库从一个服务器复制到另一个服务器

转储存储的程序

分别转储表定义和内容

使用 mysqldump 测试升级不兼容性

使用二进制日志进行时间点（增量）恢复

使用事件时间进行时间点恢复

使用事件位置进行时间点恢复

MyISAM表维护和崩溃恢复

使用 myisamchk 进行崩溃恢复

如何检查MyISAM表中的错误

如何修复MyISAM表

MyISAM Table 优化

设置 MyISAM Table 维护计划

---

备份和恢复类型

物理（原始）与逻辑备份

物理备份由存储数据库内容的目录和文件的原始副本组成。这种类型的备份适用于大型的重要数据库，这些数据库在出现问题时需要快速恢复。

逻辑备份保存由逻辑数据库结构（CREATE DATABASE， CREATE TABLE语句）和内容（INSERT语句或定界文本文件和内容（INSERT语句或定界文本文件）的信息。）的信息。这种备份类型适用于少量数据，您可以在其中编辑数据值或表结构，或在其他计算机体系结构上重新创建数据。

物理备份方法具有以下特征：

• 备份由数据库目录和文件的精确副本组成。通常，这是全部或部分MySQL数据目录的副本。
• 物理备份方法比逻辑备份方法快，因为它们仅涉及文件复制而不进行转换。
• 输出比逻辑备份更紧凑。
• 由于备份速度和紧凑性对于繁忙的重要的数据库很重要，因此MySQL Enterprise Backup产品执行物理备份。
• 备份和还原的粒度范围从整个数据目录级别到单个文件级别。这可能会或可能不会提供表级粒度，具体取决于存储引擎。例如， InnoDB每个表可以位于单独的文件中，或与其他InnoDB表共享文件存储 ；例如，每个 MyISAM表唯一地对应于一组文件。
• 除了数据库之外，备份还可以包括任何相关文件，例如日志或配置文件。
• MEMORY表中的 数据很难以这种方式进行备份，因为它们的内容未存储在磁盘上。（MySQL Enterprise Backup产品具有一项功能，您可以在备份期间从MEMORY表中检索数据。）
• 备份只能移植到具有相同或相似硬件特性的其他计算机上。
• 可以在MySQL服务器未运行时执行备份。如果服务器正在运行，则必须执行适当的锁定，以使服务器在备份期间不会更改数据库内容。MySQL Enterprise Backup自动为需要锁定的表执行此锁定。
• 物理备份工具包括用于InnoDB或任何其他表的 MySQL 企业备份的mysqlbackup，或文件系统级 （file system-level）命令 (such as cp, scp, tar, rsync)
• 对于恢复：

1. MySQL Enterprise Backup恢复它备份的InnoDB和其他表
2. ndb_restore恢复 NDB tables.
3. 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。

逻辑备份方法具有以下特征：

• 通过查询MySQL服务器以获得数据库结构和内容信息来完成备份。
• 逻辑备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出是在客户端写的，则服务器还必须将其发送到备份程序。
• 输出大于物理备份的输出，尤其是以文本格式保存时。
• 在服务器级别（所有数据库），数据库级别（特定数据库中的所有表）或表级别都提供了备份和还原粒度。无论存储引擎如何，都是如此。
• 备份不包括日志或配置文件，或者不属于数据库的其他与数据库相关的文件。
• 以逻辑格式存储的备份与计算机无关并且具有高度的可移植性。
• 逻辑备份在运行MySQL服务器的情况下执行。服务器未脱机。
• 逻辑备份工具包括mysqldump 程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY。
• 要恢复逻辑备份，可以使用mysql客户端处理SQL格式的转储文件。要加载带分隔符的文本文件，请使用LOAD DATA语句或mysqlimport 客户端。

联机与脱机备份

在线备份在MySQL服务器运行时进行，以便可以从服务器获取数据库信息。停止服务器时，将进行脱机备份。这种区别也可以描述为“ 热 ”与 “ 冷 ”备份。“热”备份是服务器保持运行但在外部访问数据库 files 时锁定以防止修改数据的备份。

联机备份方法具有以下特征：

• 备份对其他客户端的干扰较小，其他客户端可以在备份期间连接到MySQL服务器，并且可以根据其需要执行的操作访问数据。
• 必须注意施加适当的锁定，以免发生会损害备份完整性的数据修改。MySQL Enterprise Backup产品会自动执行这种锁定。

脱机备份方法具有以下特征：

• 客户端可能会受到不利影响，因为备份期间服务器不可用。因此，此类备份通常是从复制从属服务器获取的，该复制从属服务器可以脱机使用而不会损害可用性。
• 备份过程更简单，因为不可能受到客户端活动的干扰。

在线和离线之间的类似区别适用于恢复操作，并且适用类似的特征。但是，对于在线恢复而言，clients 更有可能受到影响，因为恢复需要更强的锁定。在备份期间，clients 可能能够在备份数据时读取数据。恢复修改数据而不仅仅是读取数据，因此必须防止 clients 在恢复数据时访问数据。

本地与远程备份

本地备份是在运行MySQL服务器的同一主机上执行的，而远程备份是从其他主机执行的。对于某些类型的备份，即使输出是在本地服务器上写入，也可以从远程主机启动备份。

• mysqldump可以连接到本地或远程服务器。对于SQL输出（CREATE和 INSERT语句），可以完成本地或远程转储并在客户端上生成输出。对于文本分隔输出（使用 --tab选项），数据文件会在服务器主机上创建。
• SELECT ... INTO OUTFILE可以从本地或远程客户端主机启动，但是输出文件在服务器主机上创建。
• 物理备份方法通常在MySQL服务器主机上本地启动，以便使服务器脱机，尽管复制文件的目的地可能是远程的。

快照备份

一些文件系统实现可以使用“ 快照 ” 。它们在给定的时间点提供文件系统的逻辑副本，而无需整个文件系统的物理副本。（例如，该实现可以使用写时复制技术，以便仅复制在快照时间之后修改的文件系统的某些部分。）MySQL本身不提供获取文件系统快照的功能。它可以通过Veritas，LVM或ZFS等第三方解决方案获得。

完整备份与增量备份

完全备份包括在给定时间点由MySQL服务器管理的所有数据。增量备份包括在给定时间段（从一个时间点到另一个时间点）内对数据所做的更改。MySQL有执行完全备份的不同方法，例如本节前面介绍的方法。通过启用服务器的二进制日志，使增量备份成为可能，服务器用于记录数据更改。

完全与时间点（增量）恢复

完全恢复可从完全备份中还原所有数据。这会将服务器实例还原到进行备份时的状态。如果该状态不是最新的，，那么完全恢复之后可以恢复自完全备份以来进行的增量备份，以使服务器处于最新状态。

增量恢复是在给定时间范围内所做更改的恢复。这也称为时间点恢复，因为它使服务器的当前状态恢复到给定时间点。时间点恢复基于二进制日志，通常在从备份文件完全恢复后进行，该备份文件将服务器恢复到进行备份时的状态。然后，将写入二进制日志文件中的数据更改作为增量恢复应用，以重做数据修改并将服务器调到所需的时间点。

表维护

如果表损坏，数据完整性可能会受到影响。对于 InnoDB表，这不是一个典型的问题。有关检查MyISAM 表并在发现问题时进行修复的程序，请参见 第7.6节“ MyISAM表维护和崩溃恢复”。

备份计划，压缩和加密

备份计划对于自动执行备份过程非常重要。压缩备份输出可减少空间需求，并且输出的加密可提供更好的安全性，以防止未经授权访问备份数据。MySQL本身不提供这些功能。MySQL Enterprise Backup产品可以压缩InnoDB备份，并且可以使用文件系统实用程序来对备份输出进行压缩或加密。可能有其他第三方解决方案。

数据库备份方式

使用MySQL Enterprise Backup进行热备份

MySQL Enterprise Edition 的客户可以使用MySQL 企业备份产品对整个实例或选定的数据库，表或两者进行物理备份。此产品包含增加和压缩备份的 features。备份物理数据库 files 使得恢复比诸如mysqldump命令之类的逻辑技术快得多。使用热备份机制复制InnoDB表。 (理想情况下，InnoDB表应代表其他存储引擎的 data.)表的绝大部分使用热备份机制进行复制。

使用 mysqldump 进行备份

mysqldump程序可以进行备份。它可以备份各种表。

对于InnoDB表，可以执行在线备份，不需要锁表，用 mysqldump.命令的选项。

通过复制 Table Files 进行备份

对于代表每个表的存储引擎使用自己的文件，这些表的备份可以通过复制这些文件，比如 MyISAM tables以文件存储，所以通过复制文件备份很容易 (*.frm, *.MYD, and *.MYI files)要获得一致的备份，请停止服务器或锁定并刷新相关表：

FLUSH TABLES tbl_list WITH READ LOCK;

你只需要一个读锁;这使得其他 clients 可以在您在数据库目录中复制 files 时继续查询表。需要刷新以确保在开始备份之前将所有 active 索引页写入磁盘。见Section 13.3.5，“LOCK TABLES 和 UNLOCK TABLES Syntax”和第 13.7.6.3 节，“FLUSH 语法”。

您也可以通过复制所有 table files 来简单地创建二进制备份，只要服务器没有任何更新， (但请注意，如果您的数据库包含InnoDB表，则 table 文件复制方法不起作用.此外，即使服务器未主动更新数据，InnoDB 也可能仍将修改后的数据缓存在内存中并且未刷新到磁盘。）

进行分隔文本文件备份

要创建包含表数据的文本文件，可以使用 SELECT * INTO OUTFILE 'file_name' FROM tbl_name，该文件是在 MySQL 服务器 host 上创建的，而不是 client host。输出文件不能已存在，因为允许覆盖 files 会构成安全风险。此方法适用于任何类型的数据文件，但仅保存 table 数据，而不保存 table 结构。

另一种创建文本数据 files 的方法(以及包含备份表的创建 TABLE statements 的 files)是使用mysqldump和--tab选项。

要重新加载 delimited-text 数据文件，请使用 LOAD DATA或mysqlimport。

通过启用二进制 Log 进行增量备份

MySQL 支持增量备份：必须使用--log-bin选项启动服务器以启用二进制 logging;二进制日志文件为您提供了将更改复制到数据库的信息，这些更改是在执行备份之后进行的。目前，您要进行增量备份（包含自上次完全备份或增量备份以来发生的所有更改），应使用FLUSH LOGS。完成此操作后，您需要将所有二进制日志复制到备份位置，范围从上次完全备份或增量备份的那一刻到最后一次备份。这些二进制日志是增量备份。在还原时，您可以按照第7.5节“使用二进制日志进行时间点（增量）恢复”中的说明应用它们 。下次进行完全备份时，还应该使用FLUSH LOGS或mysqldump --flush-logs轮值二进制 日志。

使用复制从站进行备份

如果您在进行备份时遇到 master 服务器的 performance 问题，可以帮助的一个策略是设置复制并在从属设备而不是 master 上执行备份。

如果要备份从属复制服务器，则在备份从属数据库时，无论使用哪种备份方法，都应备份其主信息和中继日志信息存储库。无论您选择哪种备份方法。恢复从属数据后，始终需要这些信息 files 来恢复复制。无论您选择哪种备份方法。恢复从属数据后，始终需要这些信息 files 来恢复复制。如果从属服务器正在复制 LOAD DATA语句，您还应该备份 slave 用于此目的的目录中存在的任何SQL_LOAD-* files。从站需要这些文件来恢复任何中断的LOAD DATA操作的复制 。该目录的位置是 slave_load_tmpdir系统变量。如果未使用该变量集启动服务器，则目录位置是tmpdir系统变量的值 。

恢复损坏的表

如果必须还原MyISAM已损坏的表，请先尝试使用REPAIR TABLE或者myisamchk -r恢复它们 。这应该在所有情况下的99.9％中起作用。如果 myisamchk失败，请参见 第7.6节“ MyISAM表维护和崩溃恢复”。

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

如果使用的是Veritas文件系统，则可以进行如下备份：

1. 在客户端程序中，执行FLUSH TABLES WITH READ LOCK。
2. 从另一个shell执行mount vxfs snapshot。
3. 从第一个客户端执行 UNLOCK TABLES。
4. 从快照复制文件。
5. 卸载快照。

类似的快照功能可能在其他文件系统（例如LVM或ZFS）中可用。

备份和恢复策略

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

• 操作系统崩溃

• 电源（检测）失败

• 文件系统崩溃

• 硬件问题（硬盘驱动器，主板等）

示例命令不包括一些选项如： the mysqldump and mysql client programs的--user and --password 。您应该包括必要的选项，以使客户端程序能够连接到MySQL服务器。

假设数据存储在InnoDB 存储引擎中，该引擎支持事务和自动故障恢复。还假设崩溃时MySQL服务器正在加载。如果不是这样，将永远不需要恢复。

对于操作系统崩溃或电源故障的情况，我们可以假定重启后MySQL的磁盘数据可用。 。由于崩溃，InnoDB data files 可能不包含一致的数据，但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 数据，这意味着必须已经进行备份。为了确保这种情况，请设计并实现备份 policy。

建立备份 Policy

为了有用，必须定期安排备份。可以使用多种工具在MySQL中进行完整备份（时间点上的数据快照）。例如， MySQL Enterprise Backup可以执行整个实例的 物理备份，并进行了优化以最大程度地减少开销并避免在备份InnoDB数据文件时造成中断；mysqldump提供在线 逻辑备份。本节讨论使用mysqldump。 

假设我们在星期日的 1 p.m 时使用以下命令对所有数据库中的所有InnoDB表进行完全备份，当负载较低时：

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

mysqldump 生成的结果.sql文件包含一组SQLINSERT语句，可用于在以后重新加载转储的表。 

此备份操作在转储开始时（使用FLUSH TABLES WITH READ LOCK）在所有表上获取全局读取锁定。一旦获取了此锁，便会读取二进制日志坐标并释放该锁。如果在FLUSH发出该语句时正在运行长时间的更新语句 ，则备份操作可能会暂停，直到这些语句完成为止。之后，转储将变为无锁，并且不会干扰对表的读写。

早先假定要备份的表是 InnoDB表，因此 --single-transaction使用一致的读取并保证mysqldump看到的数据 不会更改。（ mysqldump process看不到其他客户端对 InnoDB tables所做的更改）如果备份操作包括非事务表，则一致性要求它们在备份期间不要更改。例如，对于mysql数据库中的MyISAM表，备份过程中不得对MySQL帐户进行管理更改。

完全备份是必需的，但创建备份并不总是很方便。它们会产生较大的备份文件，并且需要花费一些时间来生成。它们不是最优的，因为每个连续的完整备份都包含所有数据，即使是上一次完全备份后没有改变什么部分。进行初始完整备份，然后进行增量备份，效率更高。折衷方案是，在恢复时，您不能仅通过重新加载完整备份来恢复数据。您还必须处理增量备份以恢复增量更改。

要进行增量备份，我们需要保存增量更改。在MySQL中，这些更改在二进制日志中表示，因此，应始终使用--log-bin选项启动 MySQL 服务器以启用该 log。启用二进制日志记录后，服务器在更新数据时将每个数据更改写入文件。查看以该--log-bin选项启动并已运行了几天的MySQL服务器的数据目录 ，我们发现以下MySQL二进制日志文件：

-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服务器都会使用序列中的下一个数字创建一个新的二进制日志文件。当服务器运行时，您还可以告诉它关闭当前的二进制日志文件，并通过发出FLUSH LOGSSQL语句或使用mysqladmin flush-logs命令手动开始一个新的日志文件 。 mysqldump也有刷新日志的选项。该.index数据目录文件中包含该目录下所有MySQL二进制日志的清单。

MySQL 二进制日志对于恢复非常重要，因为它们构成了一组增量备份。如果确保在进行完全备份时刷新日志，则之后创建的二进制 log files 包含自备份以来所做的所有数据更改。让我们稍微修改之前的mysqldump命令，以便在完全备份时刷新MySQL二进制日志，并且转储文件包含新的当前二进制日志的名称：

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

执行此命令后，数据目录包含一个新的二进制 log 文件gbichot2-bin.000007，因为--flush-logs选项会导致服务器刷新其日志。 --master-data选项使 mysqldump将二进制日志信息写入其输出，因此生成的.sql转储文件包括以下几行：

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

因为mysqldump命令进行了完整备份，所以这些行表示两件事：

• 转储文件包含在将任何更改写入gbichot2-bin.000007 二进制日志文件或更高版本之前所做的所有更改。

• 备份后记录的所有数据更改都不存在于转储文件中，而是存在于 gbichot2-bin.000007二进制日志文件或更高版本中。

在星期一的下午1点，我们可以通过刷新日志以开始新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令create gbichot2-bin.000008。在星期日下午1点完整备份和星期一下午1点之间的所有更改将在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删除MySQL二进制日志可能很危险，因为从属服务器可能尚未完全处理二进制日志的内容。该PURGE BINARY LOGS语句的说明解释了删除MySQL二进制日志之前应验证的内容。

使用备份进行恢复

现在，假设我们在星期三上午8点发生了灾难性的崩溃，需要从备份中恢复。要恢复，首先我们要恢复上一次的完整备份（从星期日下午1点开始的完整备份）。完整的备份文件只是一组SQL语句，因此还原它非常容易：

shell> mysql < backup_sunday_1_PM.sql

此时，数据将恢复到星期日下午1点的状态。要恢复此后所做的更改，我们必须使用增量备份。即 gbichot2-bin.000007和 gbichot2-bin.000008二进制日志文件。如有必要，从备份位置获取文件，然后按以下方式处理其内容：

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

现在，我们已将数据恢复到星期二下午1点的状态，但仍缺少从该日期到崩溃日期的更改。为了不丢失它们，我们需要让MySQL服务器将其MySQL二进制日志存储在与其存储数据文件的位置不同的安全位置（RAID磁盘，SAN等）中，以使这些日志不会在损坏的磁盘上。（也就是说，我们可以使用一个--log-bin选项来启动服务器，该 选项指定与数据目录所在的物理设备不在同一物理设备上的位置。这样，即使包含该目录的设备丢失，日志也是安全的。）我们已经做到了，我们将拥有gbichot2-bin.000009 文件（以及所有后续文件），我们可以使用mysqlbinlog和mysql来应用它们， 以恢复最新的数据更改，不会发生丢失直到崩溃的时刻：

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

有关使用 mysqlbinlog来处理二进制 log files 的更多信息，请参阅部分 7.5，“Point-in-Time(增量)使用二进制 Log 恢复”。

备份策略摘要

如果操作系统崩溃或电源故障，InnoDB本身会执行恢复数据的所有 job。但为了确保您能够安然入睡，请遵守以下准则：

始终使用--log-bin选项甚至--log-bin=logname运行 MySQL 服务器，

• 其中 log 文件 name 位于与数据目录所在驱动器不同的某些安全介质上。如果您有这样安全的介质，这种技术也可以用于磁盘负载平衡(这可以提高性能)。

• 使用前面第 7.3.1 节，“建立备份 Policy”中显示的mysqldump命令进行定期完整备份，从而进行在线非阻塞备份。
• 通过使用FLUSH LOGS或mysqladmin flush-logs刷新日志来进行定期增量备份。

使用 mysqldump 进行备份

本节介绍如何使用mysqldump生成转储文件，以及如何重新加载转储文件。转储文件可以通过多种方式使用：

• 作为备份，以便在数据丢失的情况下恢复数据。
• 作为设置复制从站的数据源。
• 作为实验数据的来源：

1. 制作可在不更改原始数据的情况下使用的数据库副本。
2. 测试潜在的升级不兼容性。

mysqldump产生两种类型的输出，具体取决于是否指定了该--tab 选项：

• 没有--tab， mysqldump将SQL语句写入标准输出。该输出包含 CREATE语句用于创建转储对象（数据库，表，存储的例程等），以及INSERT语句用于将数据加载到表中。输出可以保存在文件中，稍后使用mysql重新加载，以重新创建转储的对象。选项可用于修改SQL语句的格式，并控制转储哪些对象。
• 使用--tab， mysqldump为每个转储的表产生两个输出文件。。服务器写入一个文件作为制表符分隔的文本，一行表示每表行。该文件在输出目录中以tbl_name.txt 命名 。服务器还将CREATE TABLE创建表的语句发送到mysqldump，在输出目录中将其写为tbl_name.sql 命名的文件 。

使用 mysqldump 以 SQL 格式转储数据

本节介绍如何使用mysqldump 创建SQL格式的转储文件。有关重新加载此类转储文件的信息，请参见 第7.4.2节“重新加载SQL格式备份”。

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

shell> mysqldump [arguments] > file_name

要转储所有数据库，请使用--all-databases选项调用mysqldump：

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

要仅转储特定数据库，请在命令 line 上为它们命名并使用--databases选项：

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

--databases选项使命令行上的所有名称都被视为数据库名称。如果没有该选项，mysqldump会将第一个名字当作数据库名称，将其后的名字当作表名称。

使用--all-databases或--databases，mysqldump在每个数据库的转储输出之前写入CREATE DATABASE and USE statements。这样可以确保在重新加载转储文件时，如果不存在转储文件，它将创建每个数据库并将其设置为默认数据库，以便将数据库内容加载到它们所来自的同一数据库中。如果要使转储文件在创建每个数据库之前强制删除每个数据库，请同时使用--add-drop-database选项。在这种情况下，mysqldump在每个CREATE DATABASE语句之前写入DROP DATABASE语句。

要转储单个数据库，请在命令 line 上将其命名为：

shell> mysqldump --databases test > dump.sql

在 single-database 情况下，允许省略--databases选项：

shell> mysqldump test > dump.sql

前两个命令之间的区别在于有没有--databases，转储输出不包含CREATE DATABASE或 USE statements。

• 重新加载转储文件时，必须指定默认数据库名称，以便服务器知道要重新加载的数据库。
• 对于重新加载，您可以指定一个与原始名称不同的数据库名称，这使您可以将数据重新加载到另一个数据库中。
• 如果要用于重新加载的数据库不存在，则必须先创建它。
• 由于输出不包含 CREATE DATABASE语句，因此--add-drop-database选项无效。如果使用它，它不会产生DROP DATABASE语句。

要仅从数据库转储特定表，请在数据库 name 后面的命令 line 上命名它们：

shell> mysqldump test t1 t3 t7 > dump.sql

 重新加载 SQL-Format 备份

要重新加载由mysqldump编写的 包含SQL语句的转储文件，请将其用作mysql客户端的输入 。如果转储文件是由mysqldump使用 --all-databases或 --databases选项创建的 ，则它包含CREATE DATABASE和 USE语句，并且没有必要指定要加载数据的默认数据库：

shell> mysql < dump.sql

或者，在MySQL中，使用source命令：

mysql> source dump.sql

如果文件是不包含CREATE DATABASE和 USE语句的单数据库转储 ，请首先创建数据库（如有必要）：

shell> mysqladmin create db1

然后在加载转储文件时指定数据库 name：

shell> mysql db1 < dump.sql

或者，在MySQL中，创建数据库，选择它作为默认数据库，然后加载转储文件：

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

注意
对于 Windows PowerShell 用户：由于“<”字符保留供将来在 PowerShell 中使用，因此需要一种替代方法，例如使用引号cmd.exe /c "mysql < dump.sql"。

使用mysqldump转储文本定界格式的数据

本节介绍如何使用mysqldump创建 delimited-text dump files。有关重新加载此类 dump files 的信息，请参阅第 7.4.4 节，“重新加载 Delimited-Text 格式备份”。

如果使用该 --tab=dir_name选项调用mysqldump，它将用dir_name作输出目录，并在该目录中单独转储表，每个表使用两个文件。表名是这些文件的基本名称。对于名为t1的 table，files 命名为t1.sql和t1.txt。.sql文件包含 table 的CREATE TABLE语句。 .txt文件包含 table 数据，一行表示每个表的一行。

以下命令将db1数据库的内容转储到/tmp数据库中的 files：

shell> mysqldump --tab=/tmp db1

包含 table 数据的.txt files 由服务器写入，因此它们由用于运行服务器的系统帐户拥有。服务器使用SELECT ... INTO OUTFILE来编写 files，因此您必须具有 FILE 特权才能执行此操作，并且如果给定的.txt文件已存在则会发生错误。

服务器将转储表的CREATE定义发送到mysqldump，将其写入.sql files。因此，这些 files 由执行mysqldump的用户拥有。

最好--tab仅用于转储本地服务器。如果与远程服务器一起使用，则该--tab目录必须同时存在于本地和远程主机上，并且 .txt文件将由服务器写入远程目录（位于服务器主机上），而 .sql文件将由mysqldump写入本地文件目录（在客户端主机上） 。

对于mysqldump --tab，服务器默认将表数据写入.txt文件，每行一行，在列值之间使用制表符，在列值之间没有引号，并使用换行符作为行终止符。（这些默认值与SELECT ... INTO OUTFILE相同 。）

为了使数据文件可以使用其他格式写入， mysqldump支持以下选项：

• --fields-terminated-by=str

用于分隔列值的字符串（默认值：制表符）。

• --fields-enclosed-by=char

包含列值的字符（默认值：无字符）。

• --fields-optionally-enclosed-by=char

包含非数字列值的字符（默认值：无字符）。

• --fields-escaped-by=char

用于转义特殊字符的字符（默认值：不转义）。

• --lines-terminated-by=str

行终止字符串（默认值：换行符）。

根据您为这些选项中的任何一个指定的值，在命令行上可能有必要为命令解释器适当地加引号或转义该值。或者，使用十六进制表示法指定值。假设您希望 mysqldump用双引号引起来的列值。为此，请指定双引号作为该--fields-enclosed-by 选项的值 。但是此字符通常是命令口译员所特有的，必须加以特殊对待。例如，在Unix上，您可以这样引用双引号：

--fields-enclosed-by='"'

在任何平台上，您都可以以十六进制指定值：

--fields-enclosed-by=0x22

通常将几个数据格式选项一起使用。例如，要转储以逗号分隔的值格式的表，并以回车/换行符对（\r\n）结尾的行，请使用以下命令（在单行中输入）：

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

如果您使用任何数据格式化选项来转储表数据，则在以后重新加载数据文件时，需要指定相同的格式，以确保正确解释文件内容。

重新加载 Delimited-Text 格式备份

对于使用mysqldump --tab生成的备份，每个表在输出目录中均由一个 .sql包含 CREATE TABLE表语句,一个.txt文件包含表数据的文件表示。要重新加载表，请首先将位置更改为输出目录。然后使用mysql处理该.sql文件以创建一个空表，并处理该.txt文件以将数据加载到表中：

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

使用mysqlimport加载数据文件的另一种方法是在mysql客户端中LOAD DATA使用以下语句 ：

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

 

如果在最初转储表时对mysqldump使用了任何数据格式化选项 ，则必须对mysqlimport（或者LOAD DATA）使用相同的选项， 确保对数据文件内容的正确解释：

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

要么：

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

 

mysqldump 提示

本节调查了使您能够使用 mysqldump解决特定问题的技术：

• 如何制作数据库副本

• 如何将数据库从一台服务器复制到另一台服务器

• 如何转储存储的程序（存储的过程和函数，触发器和事件）

• 如何分别转储定义和数据

 制作数据库副本

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

不要在mysqldump命令行上使用--databases，因为这会导致USE db1包含在转储文件中，这会覆盖在MySQL命令 line 上命名db2的效果。

将数据库从一个服务器复制到另一个服务器

在服务器 1 上：

shell> mysqldump --databases db1 > dump.sql

将转储文件从服务器 1 复制到服务器 2。

在服务器 2 上：

shell> mysql < dump.sql

将--databases与mysqldump命令 line 一起使用会导致转储文件包含创建数据库的 CREATE DATABASE和USE  statements(如果存在)，并使其成为重新加载数据的默认数据库。

或者，您可以从mysqldump命令中省略--databases。然后，您需要在服务器 2 上创建数据库(如果需要)，并在重新加载转储文件时将其指定为默认数据库。

在服务器 1 上：

shell> mysqldump db1 > dump.sql

在服务器 2 上：

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

在这种情况下，您可以指定其他数据库 name，因此从mysqldump命令中省略--databases可以从一个数据库转储数据并将其加载到另一个数据库中。

转储存储的程序

有几个选项控制mysqldump如何处理存储程序（存储过程和函数，触发器和事件）：

• --events：转储事件计划程序事件

• --routines：转储存储过程和函数

• --triggers：转储表的触发器

--triggers选项默认情况下处于启用状态，以便在转储表时，表将附带它们具有的任何触发器。其他选项默认情况下处于禁用状态，并且必须明确指定以转储相应对象。要显式禁用任何这些选项，请使用其跳过的形式：--skip-events，--skip-routines或--skip-triggers。

分别转储表定义和内容

--no-data选项告诉 mysqldump不要转储表数据，从而导致转储文件仅包含创建表的语句。相反，该 --no-create-info选项告诉mysqldump抑制 CREATE输出中的语句，以便转储文件仅包含表数据。

例如，要分别转储test数据库的表定义和数据，请使用以下命令：

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

对于仅定义的转储，添加--routines和--events选项以包括存储的例程和 event 定义：

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

使用 mysqldump 测试升级不兼容性

在考虑进行MySQL升级时，谨慎的做法是将新版本与当前的 production version 分开安装。然后，您可以从生产服务器中转储数据库和数据库对象定义，并将它们加载到新服务器中，以验证它们是否已正确处理。（这对于测试降级也很有用。）

在生产服务器上：

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

在升级的服务器上：

shell> mysql < dump-defs.sql

因为转储文件不包含表数据，所以可以对其进行快速处理。这使您可以发现潜在的不兼容性，而无需等待冗长的数据加载操作。处理转储文件时查找警告或错误。

验证定义是否正确处理后，转储数据并尝试将其加载到升级的服务器中。

在生产服务器上：

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

在升级的服务器上：

shell> mysql < dump-data.sql

现在检查 table 内容并运行一些测试查询。

使用二进制日志进行时间点（增量）恢复

时间点恢复是指恢复自给定时间点以来所做的数据更改。通常，这种类型的恢复是在还原完整备份后执行的，还原完整备份将使服务器到达进行备份时的状态。（可以通过几种方式进行完全备份，例如第7.2节“数据库备份方法”中列出的 方式。）然后，时间点恢复将使服务器从完全备份时起逐步更新到最新状态。最近的时间。

注意

这里的许多示例都使用mysql 客户端来处理mysqlbinlog生成的二进制日志输出 。如果您的二进制日志包含 \0（空）字符，则除非使用该--binary-mode选项调用它，否则 mysql无法解析该输出。

时间点恢复基于以下原则：

• 时间点恢复的信息源是一组增量备份，这些增量备份由在完全备份操作之后生成的二进制日志文件表示。因此，必须使用--log-bin启用二进制日志记录的选项来启动服务器

要从二进制日志还原数据，必须知道当前二进制日志文件的名称和位置。默认情况下，服务器在数据目录中创建二进制日志文件，但是可以使用--log-bin选项将路径名指定为其他位置。

要查看所有二进制日志文件的列表，请使用以下语句：

mysql> SHOW BINARY LOGS;

要确定当前二进制 log 文件的 name，请发出以下语句：

mysql> SHOW MASTER STATUS;

• mysqlbinlog可以将二进制日志文件中的事件由二进制格式转换为文本格式，以便他们可以执行或查看。mysqlbinlog 具有用于根据事件时间或事件在日志中的位置选择二进制日志的各个部分的选项。请参见 第4.6.7节“ mysqlbinlog-用于处理二进制日志文件的实用程序”。
• 从二进制日志执行事件会导致重做它们代表的数据修改。这样可以恢复给定时间范围内的数据更改。要从二进制日志执行事件，请使用mysql客户端处理 mysqlbinlog输出 ：

shell> mysqlbinlog binlog_files | mysql -u root -p

• 当需要在执行事件之前确定事件时间或位置以选择部分日志内容时，查看日志内容会很有用。要从日志中查看事件，请将mysqlbinlog输出发送 到分页程序中：

shell> mysqlbinlog binlog_files | more

或者，将输出保存在文件中并在文本编辑器中查看文件：

shell> mysqlbinlog binlog_files > tmpfile
shell> ... edit tmpfile ...

• 将输出保存在文件中对于在删除某些事件（例如意外事件DROP DATABASE）时执行日志内容的准备工作很有用。您可以从文件中删除任何不执行的语句，然后再执行其内容。编辑文件后，执行以下内容：

shell> mysql -u root -p < tmpfile

如果要在MySQL服务器上执行多个二进制日志，那么安全的方法是使用与服务器的单个连接来处理它们。这是一个示例，说明什么可能是不安全的：

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

如果第一个日志文件包含一条CREATE TEMPORARY TABLE语句，第二个日志包含使用临时表的语句，则使用与服务器的不同连接以这种方式处理二进制日志会导致问题 。当第一个 mysql进程终止时，服务器删除临时表。当第二个mysql进程尝试使用该表时，服务器报告“ 未知表”。”

为避免出现此类问题，请使用单个 连接执行要处理的所有二进制日志的内容。这是一种方法：

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

另一种方法是将所有日志写入单个文件，然后处理该文件：

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

在从包含GTID的二进制日志中读回时写入转储文件时（请参见第 16.1.3 节“使用全局事务标识符进行复制”），将--skip-gtids选项与 mysqlbinlog一起使用，如下所示：

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"

使用事件时间进行时间点恢复

为了指示恢复的开始时间和结束时间，请以 DATETIME格式指定mysqlbinlog的 --start-datetime和 --stop-datetime选项 。例如，假设恰好在2005年4月20日上午10:00执行了一条删除大表的SQL语句。要还原表和数据，可以还原前一晚的备份，然后执行以下命令：

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

该命令将恢复所有数据，直到该--stop-datetime 选项指定的日期和时间为止。如果直到数小时后才检测到输入的错误SQL语句，您可能还希望恢复之后发生的活动。基于此，您可以使用开始日期和时间再次运行mysqlbinlog，如下所示：

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

在此命令中，将重新执行从上午10:01开始记录的SQL语句。恢复前一天晚上的转储文件和两个 mysqlbinlog命令的组合将所有内容恢复到上午10:00之前的一秒，以及从上午10:01开始的所有内容。

要使用这种时间点恢复方法，您应该检查日志以确保为命令指定确切的时间。要显示日志文件内容而不执行它们，请使用以下命令：

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

然后使用文本编辑器打开/tmp/mysql_restore.sql文件以进行检查。

除了在特定的时间特定的更改外，如果同一时间多条语句执行，有一条被阻止，则 mysqlbinlog不能顺利的完成工作。

使用事件位置进行时间点恢复

mysqlbinlog的 --start-position和 --stop-position选项可用于指定日志位置，代替指定日期和时间。除了指定日志位置编号而不是日期以外，它们的工作方式与开始日期和停止日期选项相同。使用位置可以使您更精确地了解要恢复日志的哪一部分，尤其是在许多事务与损坏的SQL语句大约同时发生时。要确定位置号，请在执行不需要的事务时将mysqlbinlog运行 一段时间，但是将结果重定向到文本文件进行检查。可以这样完成：

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

此命令在/tmp目录中创建一个小文本文件，此文件包含有损害的语句被执行时间范围内的SQL语句 。使用文本编辑器打开此文件，然后查找您不想重复的语句。确定二进制日志中停止和重新开始恢复的位置，并记下它们。位置标记为日志位置，后跟一个数字。还原上一个备份文件后，使用位置号处理二进制日志文件。例如，您将使用以下命令：

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

第一条命令将恢复所有事务，直到给出停止位置为止。第二个命令从给定的起始位置恢复所有事务，直到二进制日志结束。由于mysqlbinlog的输出在每个SQL语句记录之前都包含 SET TIMESTAMP语句，因此恢复的数据和相关的MySQL日志将反映执行事务的原始时间。

MyISAM表维护和崩溃恢复

本节讨论如何使用myisamchk检查或修复MyISAM表(具有.MYD和.MYIfiles 用于存储数据和索引的表)。对于一般的myisamchk背景，请参阅第 4.6.3 节，“myisamchk - MyISAM Table-Maintenance Utility”。其他 table-repair 信息可以在第 2.11.12 节，“重建或修复表或索引”找到。

您可以使用myisamchk检查，修复或优化数据库表。以下各节描述如何执行这些操作以及如何设置表维护时间表。有关使用myisamchk 获取有关表的信息，请参见 第4.6.3.5节“使用myisamchk获取表信息”。

尽管使用myisamchk进行 table 修复是非常安全的，但在进行修复或任何可能对 table 进行大量更改的维护操作之前进行备份总是很好的。

影响索引的myisamchk操作可能导致使用与 MySQL 服务器使用的值不兼容的 full-text 参数重建MyISAM FULLTEXT索引。要避免此问题，请遵循第 4.6.3.1 节，“myisamchk 一般选项”中的准则。

MyISAM表维护也可以使用SQL语句完成，该语句执行与myisamchk可以执行的操作类似的 操作：

• 要检查MyISAM表，请使用 CHECK TABLE。

• 要修复MyISAM表，请使用 REPAIR TABLE。

• 要优化MyISAM表，请使用 OPTIMIZE TABLE。

• 要分析MyISAM表，请使用 ANALYZE TABLE。

这些语句可以直接使用，也可以通过 mysqlcheck客户程序使用。与myisamchk相比，这些语句的优势之一是服务器可以完成所有工作。使用myisamchk时，必须确保服务器不会同时使用表，以便myisamchk与服务器之间不会发生不必要的交互 。

使用 myisamchk 进行崩溃恢复

本节介绍如何检查和处理MySQL数据库中的数据损坏。如果表经常损坏，则应尝试查找原因。请参见 第B.4.3.3节“如果MySQL继续崩溃，该怎么办”。

有关MyISAM表如何损坏的说明，请参阅第 15.2.4 节，“MyISAM Table Problems”。

如果在禁用外部锁定的情况下运行mysqld（这是默认设置），则当mysqld使用同一表时，不能可靠地使用 myisamchk检查 表。如果可以确定在运行myisamchk时 没有人可以通过mysqld访问表 ，则只需在开始检查表之前执行 mysqladmin flush- tables。如果不能保证这一点，则在检查表时必须停止 mysqld。如果运行 myisamchk来检查mysqld正在同一时间更新的表，即使表没有损坏，您也可能会收到警告，指出该表已损坏。

如果服务器在启用了外部锁定的情况下运行，则可以随时使用 myisamchk检查表。在这种情况下，如果服务器尝试更新myisamchk正在使用的表 ，则服务器将等待 myisamchk完成后才能继续。

如果使用myisamchk修复或优化表，则必须始终确保 mysqld服务器未使用该表（如果禁用了外部锁定，这也适用）。如果不停止mysqld，则至少应在 运行myisamchk之前执行 mysqladmin flush-tables。如果服务器和myisamchk同时访问表，则 表可能已损坏。

执行崩溃恢复时，重要的是要了解数据库中的每个MyISAM表 tbl_name都与下表中显示的数据库目录中的三个文件相对应。

文件	目的
tbl_name.frm	定义（格式）文件
tbl_name.MYD	数据文件
tbl_name.MYI	索引文件

这三种文件类型中的每一种都以各种方式受到损坏，但是问题最常发生在数据文件和索引文件中。

myisamchk.MYD通过逐行创建数据文件的副本来工作 。它通过删除旧.MYD文件并将新文件重命名为原始文件名来结束修复阶段。如果使用 --quick，则 myisamchk不会创建临时 .MYD文件，而是假设该 .MYD文件正确，并且仅生成新索引文件而不接触该.MYD 文件。这是安全的，因为 myisamchk会 自动检测.MYD文件是否损坏，如果文件损坏，则中止修复。您也可以为 myisamchk指定两次该--quick选项 。在这种情况下， myisamchk不会因某些错误（例如重复键错误）而中止，而是尝试通过修改.MYD文件来解决它们。通常，只有在可用磁盘空间不足以进行正常修复时，才使用两个--quick选项。在这种情况下，至少应在运行 myisamchk之前对表进行备份。

如何检查MyISAM表中的错误

要检查MyISAM table，请使用以下命令：

• myisamchk tbl_name

发现所有错误的99.99％。它找不到的是仅涉及数据文件的损坏（非常不寻常）。如果要检查表，通常应运行不带选项或带有（静默）选项的myisamchk-s。

• myisamchk -m tbl_name

发现所有错误的99.999％。它首先检查所有索引条目是否有错误，然后读取所有行。它计算行中所有键值的校验和，并验证校验和与索引树中键的校验和是否匹配。

• myisamchk -e tbl_name

这将对所有数据进行完整而彻底的检查（-e即“ 扩展检查 ”）。它对每一行的每个键进行检查读取，以验证它们确实指向正确的行。对于具有许多索引的大型表，这可能需要很长时间。通常， myisamchk在找到第一个错误后停止。如果要获取更多信息，可以添加-v（详细）选项。这将导致 myisamchk继续运行，最多出现20个错误。

• myisamchk -e -i tbl_name

这类似于上一个命令，但是该 -i选项告诉 myisamchk打印其他统计信息。

在大多数情况下，一个简单的myisamchk命令，除了表名外，不带其他参数就足以检查一个表。

 

如何修复MyISAM表

本节中的讨论描述了如何在MyISAM表(扩展名 .MYI和.MYD)上使用myisamchk。

您也可以使用CHECK TABLE and REPAIR TABLE语句检查和修复MyISAM表。请参见 第13.7.2.2节“ CHECK TABLE语句”和 第13.7.2.5节“ REPAIR TABLE语句”。

损坏的表的症状包括意外中止的查询和可观察到的错误，例如：

• tbl_name.frm 不能改变

• 找不到文件 tbl_name.MYI （ERRCODE： nnn）

• 文件意外结束

• 记录文件已崩溃

• nnn表处理程序 出现错误

要获取有关错误的更多信息，请运行 perror nnn，其中 nnn错误号为。下面的示例演示如何使用perror查找最常见的错误编号的含义，这些错误编号指示表存在问题：

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

请注意，错误 135(record 文件中没有空间)和错误 136(索引文件中没有空间)不是可以通过简单修复修复的错误。在这种情况下，您必须使用ALTER TABLE来增加MAX_ROWS和AVG_ROW_LENGTH table 选项值：

ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

如果您不知道当前表选项的值，请使用 SHOW CREATE TABLE。

对于其他错误，您必须修复表。 myisamchk通常可以检测并修复发生的大多数问题。

维修过程涉及四个阶段，在此介绍。在开始之前，您应该将位置更改为数据库目录并检查表文件的权限。在Unix上，请确保mysqld运行的用户（以及您自己，因为您需要访问要检查的文件）对它们可读 。如果事实证明您需要修改文件，那么它们也必须是可写的。

本节适用于表检查失败的情况（如第7.6.2节“如何检查MyISAM表中的错误”中所述），或者您要使用myisamchk 提供的扩展功能。

第4.6.3节“ myisamchk -MyISAM表维护实用程序”中介绍了用于表维护 的myisamchk选项。 myisamchk还具有一些变量，您可以设置这些变量来控制内存分配，以提高性能。请参见 第4.6.3.6节“ myisamchk内存使用情况”。

如果要从命令行修复表，则必须首先停止mysqld服务器。请注意，当您在远程服务器上关闭mysqladmin时，在mysqladmin返回之后，mysqld服务器仍然可以使用一段时间，直到所有语句处理停止并且所有索引更改都已刷新到磁盘上为止。

阶段1：检查表格

如果您有更多时间，请 运行myisamchk * .MYI或myisamchk -e * .MYI。使用 -s（静默）选项可消除不必要的信息。

如果mysqld服务器已停止，则应使用该--update-state选项告诉myisamchk将表标记为 “已选中”。”

您只需要修复myisamchk宣布错误的那些表 。对于此类表，请转到阶段2。

如果在检查时遇到意外错误（例如out of memory错误），或者myisamchk 崩溃，请转到阶段3

阶段2：轻松安全维修

首先，尝试myisamchk -r -q tbl_name（-r -q表示“ 快速恢复模式 ”）。这将尝试修复索引文件而不接触数据文件。如果数据文件包含应有的所有内容，并且删除链接指向数据文件内的正确位置，则该文件应该起作用，并且表已修复。开始修复下表。否则，请使用以下过程：

1. 在继续操作之前，请备份数据文件。

2. 使用myisamchk -r tbl_name （-r表示“ 恢复模式 ”）。这将从数据文件中删除不正确的行和已删除的行，并重建索引文件。

3. 如果以上步骤失败，请使用myisamchk --safe-recover tbl_name。安全恢复模式使用了一种旧的恢复方法，该方法可以处理一些常规恢复模式无法解决的问题（但速度较慢）。

注意

如果希望修复操作快得多，则在运行myisamchk时，应将sort_buffer_size和 key_buffer_size变量的值分别设置为 可用内存的25％左右 。

如果在修复时遇到意外错误（例如 out of memory错误），或者 myisamchk崩溃，请转到阶段3。

阶段3：难以维修

仅当索引文件中的前16KB块被破坏或包含不正确的信息，或者缺少索引文件时，才应进入此阶段。在这种情况下，有必要创建一个新的索引文件。这样做如下：

1. 将数据文件移到安全的地方。

2. 使用表描述文件来创建新的（空）数据和索引文件：

shell> mysql db_name

mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit

将旧数据文件复制回新创建的数据文件。（不要只是将旧文件移回新文件。您要保留一个副本，以防出现问题。）

重要

如果使用的是复制，则应在执行上述过程之前将其停止，因为它涉及文件系统操作，并且这些操作不会由MySQL记录。

返回到阶段2。myisamchk -r -q应该可以工作。（这不应是一个无限循环。）

您还可以使用REPAIR TABLE tbl_name USE_FRM SQL 语句，该语句自动执行整个过程。实用程序和服务器之间也不可能发生不需要的交互，因为服务器在使用维修表时会完成所有工作。见第 13.7.2.5 节，“REPAIR TABLE 语法”。

第 4 阶段：非常难以修复

仅当.frm描述文件也崩溃时才应该到达此阶段。这应该永远不会发生，因为在创建 table 后，描述文件不会更改：

• 从备份还原描述文件并返回到阶段 3.您还可以还原索引文件并返回到阶段 2.在后一种情况下，您应该以myisamchk -r开头。

• 如果您没有备份但确切知道如何创建 table，请在另一个数据库中创建 table 的副本。删除新数据文件，然后将.frm description 和.MYI index files 从其他数据库移动到崩溃的数据库。这为您提供了新的描述和索引 files，但只留下了.MYD数据文件。返回第 2 阶段并尝试重建索引文件。

MyISAM Table 优化

要合并碎片行并消除因删除或更新行而导致的浪费空间，请在恢复模式下运行myisamchk：

shell> myisamchk -r tbl_name

您可以使用OPTIMIZE TABLESQL语句以相同的方式优化表 。 OPTIMIZE TABLE进行表修复和键分析，并对索引树进行排序，以便键查找更快。实用程序和服务器之间也不会发生不必要的交互，因为使用OPTIMIZE TABLE时服务器会完成所有工作。请参见第13.7.2.4节“ OPTIMIZE TABLE语句”。

myisamchk还有许多其他选项可用于改善表的性能：

• --analyze或 -a：执行密钥分配分析。通过使联接优化器更好地选择联接表的顺序和应使用的索引，可以提高联接性能。

• --sort-index或 -S：对索引块进行排序。这样可以优化查找，并使使用索引的表扫描更快。

• --sort-records=index_num或-R index_num：根据给定索引对数据行进行排序。这使您的数据更加本地化，并可能加快使用此索引的 range-based 选择和ORDER BY操作。

有关所有可用选项的完整说明，请参阅第 4.6.3 节，“myisamchk - MyISAM Table-Maintenance Utility”。

设置 MyISAM Table 维护计划

定期执行表检查而不是等待问题发生是一个好主意。检查和修复MyISAM表的一种方法是使用 CHECK TABLEand REPAIR TABLE语句。请参见 第13.7.2节“表维护声明”。

检查表的另一种方法是使用myisamchk。出于维护目的，您可以使用myisamchk -s。 -s选项(--silent的缩写)导致myisamchk以静默方式运行，仅在发生错误时才打印消息。

启用自动MyISAM表检查也是一个好主意 。例如，每当计算机在更新过程中完成重新启动时，通常需要检查每个可能已经受影响的表，然后才能进一步使用它。（这些是“ 预期崩溃的表。 ”）要使服务器MyISAM自动检查 表，请使用myisam_recover_options 系统变量集启动它。请参见 第5.1.7节“服务器系统变量”。

您还应该在正常系统操作期间定期检查您的表。例如，您可以 每周执行一次cron作业来检查重要表，在crontab文件中使用以下行：

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

这将打印出有关崩溃表的信息，以便您可以根据需要检查和修复它们。

首先，在过去 24 小时内更新的所有表上每晚执行myisamchk -s。如您所见，问题很少发生，您可以将检查频率提前一周左右。

通常，MySQL表几乎不需要维护。如果要对MyISAM具有动态大小的行的表（带有 VARCHAR， BLOB或 TEXT列的表）执行许多更新，或者具有删除了许多行的表，则可能需要不时对表进行碎片整理/回收。您可以通过OPTIMIZE TABLE在有关表上使用来执行此操作 。或者，如果可以暂时停止mysqld服务器，则在服务器停止 时将位置更改为数据目录并使用以下命令：

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