7章备份和恢复

是有用的，必须定期备份。一个完整的备份（在一个时间点的快照数据）可以在几个工具MySQL做。例如,MySQL企业备份可以执行物理备份一个完整的实例，以优化减少开销和避免倒车时InnoDB数据文件；mysqldump提供在线逻辑备份。讨论使用mysqldump

假设我们把我们所有的全备份InnoDB使用以下命令在星期日晚上1点，所有数据库中的表，当负荷低：

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

由此产生的.sql文件由mysqldump包含一组SQLINSERT语句可以用来加载甩表在以后的时间。

这种备份操作获取所有表全局读锁在转储开始（使用FLUSH TABLES WITH READ LOCK）。只要这个锁已获得的二进制日志坐标读锁被释放。如果长时间的更新语句运行时FLUSH声明发出后，备份操作可能失速直到那些语句完成。之后，垃圾变成无锁和不打扰的读和写在桌子上。

这是假设前备份表InnoDB表，所以--single-transaction使用一个一致的阅读并保证数据被mysqldump不改变。（由其他客户的变化InnoDB表不见了mysqldump过程。）如果备份操作包括非事务表，一致要求他们不要在备份期间的变化。例如，对于MyISAM表中的MySQL数据库，必须在备份mysql帐户没有管理的变化。

数据备份是必要的，但它并不总是方便的创建。他们生产了大量的备份文件，需要时间来产生。他们不是最佳在这个意义上，每个连续的完整备份，包括所有的数据，甚至部分没有自上次完全备份了。更有效的是做一个初步的完整备份，然后进行增量备份。增量备份是较小的和生产需要更少的时间。权衡的是，在恢复的时间，你不能仅仅通过重装完整备份还原数据。你还必须处理增量备份恢复的增量变化。

做增量备份，我们需要保存的增量变化。在MySQL数据库中，这些变化是在二进制日志中的代表，所以MySQL服务器应该开始用--log-bin选项来启用日志。二进制日志功能，服务器写数据到一个文件的每一个变化而更新数据。看着一个MySQL服务器，并开始与数据目录--log-bin选择，已经运行了几天，我们发现这些MySQL二进制日志文件：

RW光碟——1月10 guilhem guilhem 1277324 23时59 gbichot2-bin.000001-rw-rw--- 1 guilhem guilhem 10 11月4日23时59 gbichot2-bin.000002-rw-rw--- guilhem guilhem 79 1 11 11：06 1 508 gbichot2-bin.000003-rw-rw--- guilhem guilhem 11 11 1 guilhem gbichot2-bin.000004-rw-rw---：08年11月12日16时47分guilhem 220047446 gbichot2-bin.000005-rw-rw--- -1 11月14日guilhem guilhem 998412 10 1 guilhem gbichot2-bin.000006-rw-rw---：08年11月14日10时07分guilhem gbichot2-bin.index 361

每次重新启动时，MySQL服务器使用序列中的下一个数创建一个新的二进制日志文件。当服务器在运行，你也可以告诉它关闭当前的二进制日志文件，并开始一个新的手动发布FLUSH LOGSSQL语句或一个mysqladmin刷新日志命令mysqldump也有一个选项来刷新日志。这个.index在数据目录文件包含所有MySQL二进制日志目录中的列表。

MySQL的二进制日志是重要的因为他们的形式恢复增量备份集。如果你一定要把日志当你把你的完整备份之后创建的二进制日志文件，包含所有的数据备份后所做的更改。让我们修改以前mysqldump命令一点让它刷新MySQL二进制日志的全备份的时刻，因此，转储文件包含新的当前的二进制日志的名称：

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

执行此命令后，数据目录中包含一个新的二进制日志文件，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命令做了一个全备份，那些线意味着两件事：

星期一下午1点，我们可以通过刷新日志来开始一个新的二进制日志文件创建一个增量备份。例如，执行mysqladmin刷新日志命令创建gbichot2-bin.000008。星期日下午1点全备份和星期一下午1点之间的所有变化都会在gbichot2-bin.000007文件增量备份是很重要的，所以把它复制到一个安全的地方，是一个好主意。（例如，备份磁带或DVD，或复制到另一台机器。）星期二下午1时，执行另一个mysqladmin刷新日志命令。所有的变化在星期一下午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

现在，假设我们在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

我们现在已经恢复了数据的状态作为日下午一点，但仍然失踪，日期的变化到崩溃的日期。没有他们，我们就需要MySQL服务器存储MySQL二进制日志到一个安全的位置（磁盘、SAN、…）不同，它存储数据文件的地方，所以，这些记录是不被破坏的磁盘。（那是，我们可以用启动服务器--log-bin选项指定一个位置，在不同的物理设备上一个数据目录所在。这样，即使含有目录设备丢失的日志是安全的。）如果我们这样做，我们就gbichot2-bin.000009文件（以及随后的任何文件）在手，我们可以将它们使用mysqlbinlog和MySQL在不损失了飞机坠毁的时候恢复最近的数据变化：

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

有关使用更多的信息mysqlbinlog处理二进制日志文件，看看7.5节，“时间点（增量）使用二进制日志恢复”

如果操作系统崩溃或断电，InnoDB本身所有的数据恢复工作。但要确保你能睡个好觉，遵循下列准则：

本节介绍了如何使用mysqldump创建SQL格式的转储文件。关于重装这样的转储文件的信息，参见7.4.2节，“重装SQL格式备份”

默认情况下，mysqldump将信息写入到标准输出的SQL语句。你可以在一个文件中保存输出：

shell> mysqldump [arguments] > file_name

把所有的数据库，调用mysqldump与--all-databases选项

内核>mysqldump --all-databases > dump.sql

将特定的数据库，他们的名字在命令行上使用--databases选项

内核>mysqldump --databases db1 db2 db3 > dump.sql

这个--databases选项使所有的名字在命令行上被视为数据库名称。没有这个选项，mysqldump把名字作为数据库名称和表名称如下。

与--all-databases或--databases，mysqldump写CREATE DATABASE和USE到转储输出之前为每个数据库报表。这确保了当转储文件加载，它创建每个数据库，如果它不存在，使得它的默认数据库，数据库的内容加载到同一个数据库由他们来。如果你想使转储文件给力滴每个数据库之前创建它，使用--add-drop-database选项以及。在这种情况下，mysqldump写一个DROP DATABASE声明前每个CREATE DATABASE声明

把一个数据库，它的名字在命令行：

shell> mysqldump --databases test > dump.sql

在单一数据库的情况下，可以省略--databases选项

内核>mysqldump test > dump.sql

前两个命令之间的区别是，没有--databases，转储输出不包含CREATE DATABASE或USE声明.这有几方面的含义：

把特定的表从一个数据库中，他们的名字在命令行下面的数据库名称：

shell> mysqldump test t1 t3 t7 > dump.sql

重载转储文件写的mysqldump由SQL语句，使用它作为输入的MySQL客户如果转储文件是由mysqldump与--all-databases或--databases选项，它包含CREATE DATABASE和USE报表，不必指定加载数据的默认数据库为：

内核&#62;mysql < dump.sql

另外，从内部MySQL，使用source命令：

MySQL的>source dump.sql

如果文件是一个单一的数据库不包含CREATE DATABASE和USE报表，首先创建数据库（如果需要）：

内核>mysqladmin create db1

然后指定数据库名称，当你加载转储文件：

shell> mysql db1 < dump.sql

另外，从内部MySQL，创建数据库，选择它作为默认的数据库，并加载转储文件：

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

本节介绍了如何使用mysqldump创建带分隔符的文本转储文件。关于重装这样的转储文件的信息，参见第7.4.4，“重装分隔符的文本格式备份”

如果你调用mysqldump与--tab=dir_name选项，它使用dir_name作为输出目录和转储表单独使用两个文件目录中的每个表。表名是这些文件的基名称。为表名T1文件的命名，t1.sql和TXTXT。这个.sql文件集装箱CREATE TABLE台词的语句。的txt文件包含数据的表，每个表行一行。

下面的命令转储的内容db1在对文件的数据库/tmp数据库：

shell> mysqldump --tab=/tmp db1

这个.txt包含表数据文件由服务器写的，所以他们所拥有的系统帐户用于运行服务器。服务器使用SELECT ... INTO OUTFILE写文件，所以你必须有FILE权限来执行此操作，而发生错误，如果一个给定的txt文件已经存在

服务器发送CREATE为把表定义mysqldump写他们，.sql文件这些文件是由谁执行，因此用户自备mysqldump

这是最好的，--tab只能用于倾销本地服务器。如果你使用一个远程服务器的--tab目录必须在本地和远程主机的存在，和txt文件将被写入远程目录服务器（服务器主机上），而.sql文件将被写入的mysqldump在本地目录（在客户端）。

为mysqldump -表，默认情况下，服务器写数据到表.txt文件一行每行与列值的标签，没有引号的列值，作为行结束符和换行符。（这些都是相同的默认值为SELECT ... INTO OUTFILE。）

为了使数据文件被写入使用不同的格式，mysqldump支持这些选项：

这取决于你的任何这些选项指定的值，它可能需要在命令行中引用或逃避价值适当你的命令解释器。另外，用十六进制表示法指定的值。假设你想mysqldump引用列的值用双引号。这样做，说明双引号为价值--fields-enclosed-by选项但这种性格往往是特殊的命令，必须做特殊处理。例如，在UNIX系统下，你可以引用双引号这样：

--fields-enclosed-by='"'

在任何平台上，你可以在六指定值：

--fields-enclosed-by=0x22

这是通常使用的几种数据格式选项。例如，将表中的逗号分隔值格式线终止回车/换行符对（\r\n），使用该命令（进入这一行）：

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

如果你使用任何的数据格式设置选项将表中的数据，你需要指定相同的格式，当你重新加载数据文件，确保文件内容的正确解读。

备份产生的mysqldump -表，每个表是表示在输出目录中的一个.sql文件包含CREATE TABLE表的声明，和txt包含表数据文件。要加载一个表，首先改变位置到输出目录。然后过程.sql文件MySQL创建一个空的表和过程.txt文件加载数据到表：

内核&#62;mysql db1 < t1.sql内核&#62;mysqlimport db1 t1.txt

一个可选择使用mysqlimport加载数据文件使用LOAD DATA INFILE在声明MySQL客户：

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

如果你用任何数据格格不入mysqldump当你开始把表，你必须使用相同的选项mysqlimport或LOAD DATA INFILE为了保证数据文件内容的正确解读：

内核>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解决具体问题：

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

shell> mysqldump --databases db1 > dump.sql

shell> mysql < dump.sql

shell> mysqldump db1 > dump.sql

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

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

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

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

表示开始和结束的时候恢复，指定--start-datetime和--stop-datetime选项mysqlbinlog，在DATETIME格式作为一个例子，假设是在2005年4月20日上午十点SQL语句被执行，删除一个大表。恢复表和数据，你可以恢复以前的备份，然后执行下面的命令：

内核>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

在这个命令，SQL语句记录从10:01上午将重新执行。对前一晚的转储文件和两个恢复相结合mysqlbinlog命令恢复一切直到前一秒10:00从10:01点。

利用这个时间点恢复的方法，你应该检查日志以确保准确的时间到指定的命令。显示日志文件内容不执行，使用此命令：

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

然后打开/tmp/mysql_restore.sql用文本编辑器查看文件。

不包括具体的变化，通过指定的时间mysqlbinlog如果没有多个语句执行时间为一个同样被排除在外的工作。

而不是指定日期和时间的--start-position和--stop-position选项mysqlbinlog可用于指定日志的位置。他们工作的同时，为开始和停止日期选项，除非您指定的日志位置编号而不是日期。使用位置可以使你更精确的关于这部分的日志恢复，尤其是许多交易发生时间为有害的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语句的时候，有害的SQL语句被执行。用文本编辑器打开此文件，并寻找的声明，你不想重复。决定停止和恢复恢复的二进制日志的位置，记下他们。位置标记为log_pos其次是一些。恢复以前的备份文件后，使用位置编号处理二进制日志文件。例如，你可以使用命令这样的东西：

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包括SET TIMESTAMP在每个SQL语句记录报表，恢复的数据和相关的MySQL日志将反映原始时代的交易执行。

本节描述如何检查与MySQL数据库数据损坏的处理。如果你的表会损坏频繁，你应该尝试找出原因。看到第b.5.3.3，“做什么如果MySQL总是死机”

一个说明MyISAM表可以被损坏，看第16.2.4，“MyISAM表的问题”

如果你运行mysqld与外部锁定残疾（这是默认的），你不能可靠地使用myisamchk检查表的时候mysqld使用相同的表。如果你可以肯定的是，没有人会通过访问表mysqld当你运行myisamchk，你只需要执行mysqladmin flush-tables在你开始之前检查表。如果你不能保证，你必须停止mysqld当你检查表。如果你运行myisamchk检查表mysqld更新的同时，你可能会得到一个警告，一个表被破坏甚至是不。

如果服务器运行外部锁定功能，您可以使用myisamchk随时检查表。在这种情况下，如果服务器试图更新一个表，myisamchk是以，服务器将等待myisamchk然后再继续完成

如果你使用myisamchk修复或优化的表，你必须始终确保mysqld服务器不使用表（如果外部锁定功能这也适用）。如果你不停止mysqld，你至少应该做的mysqladmin flush-tables在你运行myisamchk。你的表可能会损坏如果服务器和myisamchk访问表

执行崩溃恢复时，这是为了了解每个重要MyISAM表tbl_name在一个数据库中对应于下表中显示的数据库目录的文件。

这三个文件类型是受各种腐败问题，但最常发生在数据文件和索引文件。

myisamchk通过创建一个复制的作品.MYD一排排的数据文件。结束维修阶段，将旧的MVD重命名文件，新文件的原文件名。如果你使用--quick，myisamchk不创建一个临时的.MYD文件，而假设MVD文件是正确的，只产生一个新的索引文件，不碰.MYD文件这是安全的，因为myisamchk自动检测.MYD文件已损坏，中止修复如果是。你也可以指定--quick选择两次myisamchk。在这种情况下，myisamchk不放弃对一些错误（如重复键错误）而是试图解决通过修改.MYD文件通常使用两--quick如果你有太多的空闲磁盘空间进行正常的修复选项是有用的。在这种情况下，你至少应该在跑步前作表的备份myisamchk

检查MyISAM表，使用下面的命令：

在大多数情况下，一个简单的myisamchk不带参数比其他的表名的命令是足够的检查表。

本节的讨论描述如何使用myisamchk打开（放）MyISAM排气表。我和.MYD）

你也可以使用CHECK TABLE和REPAIR TABLE语句来检查和修复MyISAM表看到第13.7.3.2，“检查表语法”，和第13.7.3.5，修表的语法”

损坏的表症状包括查询终止意外和观察到的像这样的错误：

要获得有关错误的更多信息，运行perrornnn，在那里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（记录文件没有更多的空间）和错误136（索引文件中没有更多的空间）是不是可以用一个简单的修复固定误差。在这种情况下，你必须使用ALTER TABLE增加max_rows和AVG_ROW_LENGTH表选项值：

修改表tbl_nameMAX_ROWS=xxxAVG_ROW_LENGTH=yyy；

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

对于其他的错误，你要修你的表。myisamchk通常可以检测和修复大多数问题的发生。

维修过程中涉及到的三个阶段，这里描述的。在你开始之前，你应该改变位置数据库目录和文件的权限检查表。在Unix上，确保它们的用户可读mysqld运行（与你，因为你需要访问你检查文件）。如果你发现需要修改的文件，他们也必须由你写。

本节为例表检查失败（如所描述的那些第7.6.2，“如何检查错误”MyISAM表），或者你想使用的扩展功能myisamchk提供.

这个myisamchk用于表的维护与选择了4.6.4“，”myisamchk- MyISAM表维护工具”myisamchk也有变量，您可以设置控制内存分配，可以提高性能。看到部分4.6.4.6，“myisamchk内存使用”

如果你要从命令行修表，你必须先停止mysqld服务器注意，当你做关闭在远程服务器上，这mysqld服务器还提供了一会儿后mysqladmin返回，直到所有的语句处理已停止所有指标的变化已经刷新到磁盘。

实习1：抓住你的桌子

运行myisamchk *。我或myisamchk - E *。我如果你有更多的时间。使用-s（沉默）可以抑制不必要的信息。

如果mysqld服务器停止，你应该使用--update-state选项告诉myisamchk为了纪念表“选中的.“

你要修表，只有那些myisamchk宣布一个错误。这样的表，进行2阶段。

如果你得到意想不到的错误检查时（如out of memory错误），或如果myisamchk崩溃，去3期

第二阶段：简单安全的修复

首先，尝试myisamchk R Qtbl_name（·R·Q方法“快速恢复模式“）。本文试图索引文件的修复不接触数据文件。如果数据文件包含要一切和删除连接点在正确的位置的数据文件，这应该和桌子是固定的。开始修理下表。否则，使用下面的过程：

如果你得到意想不到的错误修复时（如out of memory错误），或如果myisamchk崩溃，去3期

阶段3：维修困难

你应该只有在索引文件中的第一16kb块破坏或包含不正确的信息，达到这个阶段，或者如果索引文件丢失。在这种情况下，有必要建立一个新的索引文件。这样做如下：

回到阶段2myisamchk R Q要工作。（这不应该是一个永无止境的循环。）

你也可以使用REPAIR TABLE tbl_name USE_FRMSQL语句，执行全过程自动。也有不可能的效用和服务器之间不必要的交互，因为服务器做所有的工作，当你使用REPAIR TABLE。看到第13.7.3.5，修表的语法”

凝聚分散的行和消除浪费的空间删除或更新行，结果，跑myisamchk在恢复模式：

shell> myisamchk -r tbl_name

你可以优化在同一方式表用OPTIMIZE TABLESQL语句OPTIMIZE TABLE一个修表和重点分析，并分类索引树，重点查找更快。也有不可能的效用和服务器之间不必要的交互，因为服务器做所有的工作，当你使用OPTIMIZE TABLE。看到第13.7.3.4，“优化表语法”

myisamchk还有一些其他的选项，你可以使用一个表的性能提高：

为了充分说明所有可用的选项，看4.6.4“，”myisamchk- MyISAM表维护工具”

这是一个好主意来执行表检查定期的基础上，而不是等待问题发生。检查和修复的一种方法MyISAM表与CHECK TABLE和REPAIR TABLE声明.看到第13.7.3，“表维护报表”

另一种方法是使用检查表myisamchk。为了维护的目的，你可以使用myisamchk S。这个-s选择（短裤）--silent原因myisamchk运行在静音模式，打印邮件时发生错误。

它使自动也是一个好主意MyISAM检查表。例如，当机器做了重新启动在更新中，你通常需要检查每个表可能已经在使用前进一步的影响。（这些都是“预计撞表“）导致服务器检查MyISAM表格自动的开始--myisam-recover-options选项看到第5.1.6、“服务器选项”

您还应该检查你的表经常在正常的系统操作。例如，您可以运行一个cron工作检查的重要表，每周一次，在使用这样的线crontab文件：

35 * 0 * 0/path/to/myisamchk-快-沉默/path/to/datadir/ * / *。我

这种打印出的信息撞表使您可以检查和修复是必要的。

首先，执行myisamchk S在所有的表已经在过去的24小时内更新每个夜晚。你看，问题不常发生，你可以回去检查频率为一星期左右一次。

通常情况下，MySQL表需要很少的维护。如果您正在执行更新MyISAM表动态大小的行（表VARCHAR，BLOB，或TEXT柱）或有许多已删除的行你可能想整理/回收空间从表时表。您可以通过使用OPTIMIZE TABLE对问题的表。或者，如果你能停止mysqld一时间服务器，更改位置到数据目录，当服务器停止使用此命令：

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