备份和拷贝MYSQL数据库

【 tulaoshi.com - 编程语言 】

重要的是在表丢失和毁坏时备份数据库。如果系统发生崩溃，您就能够将表恢复到崩溃时刻的状态，并尽可能不丢失数据。同样，错发DROP DATABASE 或DROP TABLE 命令的用户可能会向您请求进行数据恢复。有时，这是由MySQL管理员引起的破坏，管理员试图通过使用像vi 或emacs 这样的编辑器直接编辑表文件而毁坏了它们。这样做对表来说肯定是干了坏事。

备份数据库的两种主要方法是使用mysqldump 程序或直接拷贝数据库文件（如便用c p、tar 或c p i o）。每种方法都有自己的优点和缺点：

mysqldump 与MySQL服务器联合进行操作。直接拷贝方法与服务器相脱离，因此必须采取措施确保在进行拷贝时没有客户机在修改这些表。这个问题与利用文件系统备份来备份数据库的问题相同：如果数据库表在文件系统备份时进行更新，则进行备份的表文件处于非一致的状态，并且对于今后恢复该表没有意义。文件系统备份和直接拷贝文件的区别是：对于后者，您具有控制备份进度的权利，因此可以采取措施确保服务器使表处于静止状态。

mysqldump 比直接拷贝技术要慢

mysqldump 产生可移植到其他机器、甚至具有不同硬件结构的机器上的文本文件。直接拷贝文件不能够移植到其他机器上，除非要拷贝的表使用MyISAM 存储格式。ISAM 表只能在具有相同硬件结构的机器之间进行拷贝。例如，将文件从S PARC 的Solaris 机器拷贝到Intel 的Solaris 机器（或者相反）是行不通的。由MySQL3.23 引进的MyISAM 表存储格式可以解决这个问题，因为该格式与机器独立。因此，如果以下两个条件都满足的话，直接拷贝文件可以移植到具有不同硬件结构的机器上：即另一台机器上也必须运行MySQL3.23 以上的版本，并且文件必须表示成MyISAM 表，而不是ISAM 表。

不论选择哪种备份方法，都有某些原则，您必须坚持这些原则，才能确保在需要恢复数据库内容时得到最好的结果：

定期执行备份。设置一个时间表并坚持使用它。

告诉服务器运行更新日志。更新日志在您需要恢复崩溃后的数据库时给予帮助。在使用备份文件将数据库恢复到备份时刻的状态后，可以通过运行更新日志中的查询，重新运行备份之后所做的改变。这个操作将数据库中的表恢复到了崩溃时刻的状态。在文件系统备份语言中，数据库备份文件表示完全转储（ full dump），而更新日志则表示增量转储。

使用一致和可理解的备份文件命名模式。像b a c k up 1、backup2 等名字没有特殊的含义。当需要它执行恢复时，还得浪费时间去查看文件中的内容。您会发现使用数据库名和花时间去构造备份文件名是有好处的。例如：

% mysqldump samp_db /usr/archives/mysql/samp_db. 1999-10-02

% mysqldump menagerie /usr/archives/mysql/menagerie.1999-10-02

在产生备份文件后您可能需要将它们压缩。毕竟备份文件都比较大，所以您可能还需要终止备份文件以避免它们填满磁盘，这与终止日志文件类似。您可以用相同的技术终止备份文件：

用文件系统备份来备份您的备份文件。如果您遭受了一个完全崩溃，不仅毁坏了数据目录而且还破坏了包含数据库备份的磁盘驱动器，那将造成真正的麻烦。您还应该备份更新日志。

将备份文件放在与您的数据库不同的文件系统上。这将减少含有数据字典的文件系统被生成的备份文件填满的可能性。

创建备份的技术对于将数据库拷贝到另一个服务器上也是很有帮助的。将数据库转移到运行在另一个主机上的服务器是很平常的，但您还可以将数据转移到运行在相同主机上的另一个服务器。如果正为一个新版本的MySQL运行服务器，并且想用成品服务器上的某些真实数据来测试它时，可能会这样做。还有一种可能，那就是您得到了一台新的机器并要将所有的数据库移动到新机器上。

用mysqldump 备份和拷贝数据库

当使用mysqldump 程序产生数据库备份文件时，缺省设置是该文件的内容由C R E AT E TABLE 语句组成，这些语句创建被转储的表以及包含表中的行数据的INSERT 语句。换句话说，mysqldump 创建在今后可作为对mysql的输入使用的输出结果，以重建数据库。

可以将整个数据库按以下命令转储到单独的文本文件中：

该文件的其余部分由更多的INSERT 和CREATE TABLE 语句组成。如果想在生成备份时进行压缩，可替换成类似下列的命令：

% mysqldump samp_db | gzip /usr/archives/mysql/samp_db.1999.10.02.gz

如果您有一个超大数据库，则该输出文件也将是极大的且管理起来很困难。如果您喜欢的话，可以通过在mysqldump 命令的数据库名之后命名单个的表来转储这些表的内容。这个操作将该转储文件分成更小的、更多的可管理的文件。下面的例子将说明如何将samp_db 的一些表转储到单个文件中：

% mysqldump samp_db student score event absence gradebook.sql

% mysqldump samp_db member president hist-league.sql

如果您正在生成备份文件并打算用这些备份文件来定期刷新另一个数据库的内容，则可能要使用--add-drop-table 选项。此选项告诉mysqldump 将DROP TABLE IF EXISTS 语句写到备份文件中。然后，当您取出该备份文件并将其加载到第二个数据库时，如果表已经存在将不会出现错误信息。如果您正在运行第二个数据库，可使用此技术利用从第一个数据库中的数据拷贝来定期地加载它。

如果您正在转储数据库使该数据库可以转换到另一个服务器上，则无须创建备份文件。应确保该数据库存在于另一台主机上，然后用一个管道使mysql直接读取mysqldump 的输出结果来转储数据库。例如，如果想要将samp_db 数据库从p i t _ v i per.snake.net 拷贝到b o a . s n a k e . n e t，操作如下：

% mysqladmin -h boa.snake.netcreate samp_db

% mysqldump samp_db | mysql-h boa.snake.net samp_db

稍后，如果想要在boa.snake.net 中再次刷新该数据库，可跳过mysqladmin 命令，但要将--add-drop-table 增加到mysqldump 中，以避免得到有关“表已经存在”的错误：

% mysqldump --add-drop-table samp_db | mysql-h boa-snake.net samp_db

mysqldump 的其他选项包括如下所示的几个：

--flush-log 和--lock-tables 的结合有助于检查数据库。--lock-table 锁定所有正在转储的表，而--flush-log 关闭并重新打开更新日志文件。如果正在产生后续的更新日志，则新的更新日志将只包含从备份的那一点开始修改数据库的查询。这时检查对于该备份时间的更新日志的检查点（然而，锁定所有的表对于备份期间客户机访问来说不太好，如果您有需要执行更新操作的客户机的话）。

如果用--flush-logs 检查对于备份时间的更新日志检查点，最好转储整个数据库。如果转储单个文件，则将更新日志的检查点与备份文件同步是比较难的。在恢复操作中，您通常在总数据库（ per- d a t a b a s e）的基础上抽取更新日志的内容。对于抽取单个表的更新日志来说没有选项，因此您必须自己抽取它们。

缺省设置时，mysqldump 将表的全部内容在写之前读到内存中。这实际上不是必须的，事实上，如果您真的有大型表的话，这几乎是一个失败的方法。可以用--quick 选项告诉mysqldump 写每一行（只要是被检索的）。要想进一步优化该转储过程，可用- - o p t来代替- - q ui c k。-- opt 选项开启其他的选项，这些选项将加快转储数据和读回数据的速度。

由于快速备份的好处，使得用--opt 执行备份成为最常用的方法。但是，要当心， - - o p t 选项有一个代价： --opt 所优化的是您的备份过程，而不是由其他客户机对数据库的访问。--opt 选项可防止任何人更新被锁定的正在转储的任何表。您会很容易地发现在常

规数据库访问中在这一点上所做的努力。试着在一天中数据库通常最繁忙的时刻运行一个备份。这不会花费太多的时间。

与--opt 作用有点相反的选项是- d e l a y e d。该选项导致mysqldump 写INSERT D E L AYED 语句而非INSERT 语句。如果您将一个数据文件加载到另一个数据库中并且想要使该操作对其他查询（这些查询可能正在数据库中发生）造成的影响最小，则- -d e l a y e d将有助于达到这个目的。

--compress 选项有助于将数据库拷贝到另一台机器上，因为它可以减少网络传输中的字节数量。这里有一个例子，请注意，为了使程序与远程主机上的服务器进行通信（而不是与本地主机通信），给出了--compress 选项：

% mysqldump --opt samp_db | mysql--compress -h boa.snake.net samp_db

mysqldump 有许多选项，详细信息请参考附录E。

使用直接拷贝数据库备份和拷贝方法

不用mysqldump 来备份数据库或表的另一种方法是直接拷贝表文件。通常可利用像c p、tar 或cpio 这样的实用程序来进行。本节的例子使用的是c p。

使用直接拷贝备份（ direct-copy backup）方法时，必须确保没有使用这些表。如果在拷贝一个表的同时服务器正在修改它，则拷贝无效。

确保拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重新启动服务器。如果不想关闭服务器，则应参考第13 章，查阅有关在执行表检查点时锁定服务器的介绍。如果服务器在运行中，则相同的约束都适用于拷贝文件，您应该用同样的锁定协议使服务器保持静止状态。

假定服务器关闭，或者已经锁定了想要拷贝的表，下面的例子将说明怎样将整个samp_db 数据库备份到备份目录中（ DATADIR 代表服务器的数据目录）：

% cd DATADIR

% cp -r samp_db /usr/archive/mysql    单个表可按如下进行拷贝：

% cd DATADIR/samp_db

% cd member.* /usr/archive/mysql/samp_db

% cd score.* /usr/archive/mysql/samp_db

...

当完成备份时，可以重新启动服务器（如果已使它关闭），或者释放在表上施加的锁（如果保持服务器运行）。

要想用直接拷贝文件将数据库从一台机器拷贝到另一台机器，只要将这些文件拷贝到另一台服务器主机上的相应数据库上即可。应确保这些文件是对MyISAM 表的或者两台机器都有相同的硬件结构。否则这些表在第二个主机上看起来好象有很奇怪的内容。还应该确保第二台主机的服务器不会在您安装这些表时去访问它们。

复制数据库

术语“复制”的含义简单地说有点像“拷贝数据库到另一个服务器”，或者是包含在主数据库的内容发生变化时次数据库的有效更新（ live updating）的含义。如果想简单地将数据库拷贝到另一个服务器上，则可以使用在前面已经讨论的那些命令。自MySQL3.23 版本以来，就已经开始出现对基于有效更新的复制的支持。但它的功能仍未成熟，因此，在这方面笔者没有什么可讨论的，如果有兴趣，您可以注意一下当前的新版本，看看有些什么新的开发功能。