什么是 sql-dump？

What is sql-dump for?

提问by Yang

I know that a SQL dump is a series of insert SQL statements which reflect all the records inside the database. But what is it used for? Why should we dump the database records? Does every database support a dumping function?

我知道 SQL 转储是一系列插入 SQL 语句，它们反映了数据库中的所有记录。但是它有什么用呢？为什么要转储数据库记录？每个数据库都支持转储功能吗？

回答by intuited

Somewhat strangely, this is actually the usual way to back up a database. Copying the files themselves that actually hold the data is not the usual backup method, for various complicated reasons.

有点奇怪，这实际上是备份数据库的常用方法。由于各种复杂的原因，复制实际保存数据的文件本身并不是通常的备份方法。

All relational databases work this way, or at least I've never heard of one that doesn't: they all have a facility to export a bunch of SQL code that, when executed, will recreate the database in the same state it was in when the dump was started.

所有关系数据库都以这种方式工作，或者至少我从未听说过一个不这样做的：它们都有一种工具可以导出一堆 SQL 代码，这些代码在执行时会以相同的状态重新创建数据库当转储开始时。

However these various formats are generally incompatible, due to subtle differences between the various dialects of SQL used by the different database systems. There are utilities that can convert between some of them, but I'm not aware of any 'Rosetta Stone' that handles every possible case.

然而，由于不同数据库系统使用的各种 SQL 方言之间存在细微差别，因此这些不同的格式通常是不兼容的。有一些实用程序可以在其中一些之间进行转换，但我不知道有任何“罗塞塔石碑”可以处理所有可能的情况。

As well as being the primary method of backing up a database, this technique is also useful when staging the data of db apps between different servers, ie from development to testing to production.

除了作为备份数据库的主要方法外，这种技术在不同服务器之间暂存数据库应用程序的数据时也很有用，即从开发到测试再到生产。

回答by rjh

mysqldumpproduces an SQL representation of the data for one or more tables or databases. As the format is SQL, it will run on any other MySQL server, regardless of architecture or major/minor version (obviously, views won't work on 4.x etc. but it is mostly forwards compatible).

mysqldump为一个或多个表或数据库生成数据的 SQL 表示。由于格式是 SQL，它可以在任何其他 MySQL 服务器上运行，无论架构或主要/次要版本如何（显然，视图不适用于 4.x 等，但它主要是向前兼容的）。

There is another tool, mysqlhotcopy, but as this tool produces binary files, they are tied to the machine they have been generated on, and cannot be used elsewhere. SQL has the advantage of running on any MySQL server, and being independent of the underlying file storage mechanism of the database(s).

还有另一个工具mysqlhotcopy，但由于该工具生成二进制文件，因此它们与生成它们的机器相关联，不能在其他地方使用。SQL 的优点是可以在任何 MySQL 服务器上运行，并且独立于数据库的底层文件存储机制。

The two main use cases for dumping SQL are:

转储 SQL 的两个主要用例是：

• Backing up the database data. The SQL can be read in ("played back") to an empty database server and it will re-create the tables and populate them with rows.
• Migrating the data to another server. Say you are upgrading from MySQL 5.0 to 5.1. You have two machines. You use mysqldump to produce an SQL dump on the 5.0 machine, and feed it into the 5.1.

• 备份数据库数据。可以将 SQL 读入（“回放”）到一个空的数据库服务器，它将重新创建表并用行填充它们。
• 将数据迁移到另一台服务器。假设您要从 MySQL 5.0 升级到 5.1。你有两台机器。您使用 mysqldump 在 5.0 机器上生成 SQL 转储，并将其提供给 5.1。

There are some less common uses. For example, SQL snapshot of your application's database could be taken for unit testing against a known state. It is also possible to transform SQL code into another dialect, e.g. PostgeSQL or SQLite, to port your data to another database.

有一些不太常见的用途。例如，您的应用程序数据库的 SQL 快照可以用于针对已知状态的单元测试。也可以将 SQL 代码转换为另一种方言，例如 PostgeSQL 或 SQLite，以将您的数据移植到另一个数据库。

You asked if other databases provide SQL dump functionality. The answer is yes in almost all cases. PostgreSQL provides pg_dump, SQLite has a .dumpcommand, etc.

您询问其他数据库是否提供 SQL 转储功能。几乎在所有情况下，答案都是肯定的。PostgreSQL 提供了pg_dump，SQLite 有一个.dump命令等。