MySQL 如何从 mysqldump 恢复转储文件？

How do I restore a dump file from mysqldump?

提问by Zack Peterson

I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.

我得到了一个 MySQL 数据库文件，我需要将它恢复为 Windows Server 2008 机器上的数据库。

I tried using MySQL Administrator, but I got the following error:

我尝试使用 MySQL Administrator，但出现以下错误：

The selected file was generated by mysqldump and cannot be restored by this application.

所选文件是由 mysqldump 生成的，此应用程序无法恢复。

How do I get this working?

我如何让这个工作？

回答by Dónal

If the database you want to restore doesn't already exist, you need to create it first.

如果要还原的数据库不存在，则需要先创建它。

On the command-line, if you're in the same directory that contains the dumped file, use these commands (with appropriate substitutions):

在命令行上，如果您位于包含转储文件的同一目录中，请使用以下命令（使用适当的替换）：

C:\> mysql -u root -p

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;

回答by Justin Bennett

It should be as simple as running this:

它应该像运行这个一样简单：

mysql -u <user> -p < db_backup.dump

If the dump is of a single database you may have to add a line at the top of the file:

如果转储是单个数据库，您可能需要在文件顶部添加一行：

USE <database-name-here>;

If it was a dump of many databases, the use statements are already in there.

如果它是许多数据库的转储，则 use 语句已经在那里了。

To run these commands, open up a command prompt (in Windows) and cdto the directory where the mysql.exeexecutable is (you may have to look around a bit for it, it'll depend on how you installed mysql, i.e. standalone or as part of a package like WAMP). Once you're in that directory, you should be able to just type the command as I have it above.

要运行这些命令，请打开命令提示符（在 Windows 中）并cd打开mysql.exe可执行文件所在的目录（您可能需要四处看看，这取决于您如何安装 mysql，即独立安装还是作为其一部分）像 WAMP 这样的包）。进入该目录后，您应该可以像上面一样输入命令。

回答by vog

You simply need to run this:

你只需要运行这个：

mysql -p -u[user] [database] < db_backup.dump

If the dump contains multiple databases you should omit the database name:

如果转储包含多个数据库，则应省略数据库名称：

mysql -p -u[user] < db_backup.dump

To run these commands, open up a command prompt (in Windows) and cdto the directory where the mysql.exeexecutable is (you may have to look around a bit for it, it'll depend on how you installed mysql, i.e. standalone or as part of a package like WAMP). Once you're in that directory, you should be able to just type the command.

要运行这些命令，请打开命令提示符（在 Windows 中）和可执行文件cd所在的目录mysql.exe（您可能需要四处看看，这取决于您安装 mysql 的方式，即独立安装还是作为其一部分像 WAMP 这样的包）。进入该目录后，您应该只需键入命令即可。

回答by womd

mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

look here - step 3: this way you dont need the USE statement

看这里 - 第 3 步：这样你就不需要 USE 语句

回答by pdc

When we make a dump file with mysqldump, what it contains is a big SQL script for recreating the databse contents. So we restore it by using starting up MySQL's command-line client:

当我们用 制作转储文件时mysqldump，它包含的是一个用于重新创建数据库内容的大型 SQL 脚本。所以我们通过启动 MySQL 的命令行客户端来恢复它：

mysql -uroot -p 

(where rootis our admin user name for MySQL), and once connected to the database we need commands to create the database and read the file in to it:

（root我们的 MySQL 管理员用户名在哪里），一旦连接到数据库，我们需要命令来创建数据库并将文件读入其中：

create database new_db;
use new_db;
\. dumpfile.sql

Details will vary according to which options were used when creating the dump file.

详细信息将根据创建转储文件时使用的选项而有所不同。

回答by Javeed Shakeel

Run the command to enter into the DB

运行命令进入数据库

 # mysql -u root -p 

Enter the password for the user Then Create a New DB

输入用户的密码然后创建一个新的数据库

mysql> create database MynewDB;
mysql> exit

And make exit.Afetr that.Run this Command

并让 exit.Afetr that.Run 这个命令

# mysql -u root -p  MynewDB < MynewDB.sql

Then enter into the db and type

然后进入数据库并输入

mysql> show databases;
mysql> use MynewDB;
mysql> show tables;
mysql> exit

Thats it ........ Your dump will be restored from one DB to another DB

就是这样......您的转储将从一个数据库恢复到另一个数据库

Or else there is an Alternate way for dump restore

否则有另一种转储还原方式

# mysql -u root -p 

Then enter into the db and type

然后进入数据库并输入

mysql> create database MynewDB;
mysql> show databases;
mysql> use MynewDB;
mysql> source MynewDB.sql;
mysql> show tables;
mysql> exit

回答by Zack Peterson

I got it to work following these steps…

我按照这些步骤让它工作......

1. Open MySQL Administrator and connect to server

2. Select "Catalogs" on the left

3. Right click in the lower-left box and choose "Create New Schema"

   MySQL Administrator http://img204.imageshack.us/img204/7528/adminsx9.th.gifenlarge image

4. Name the new schema (example: "dbn")

   MySQL New Schema http://img262.imageshack.us/img262/4374/newwa4.th.gifenlarge image

5. Open Windows Command Prompt (cmd)

   Windows Command Prompt http://img206.imageshack.us/img206/941/startef7.th.gifenlarge image

6. Change directory to MySQL installation folder

7. Execute command:

   mysql -u root -p dbn < C:\dbn_20080912.dump
   

   …where "root" is the name of the user, "dbn" is the database name, and "C:\dbn_20080912.dump" is the path/filename of the mysqldump .dump file

   MySQL dump restore command line http://img388.imageshack.us/img388/2489/cmdjx0.th.gifenlarge image

8. Enjoy!

1. 打开 MySQL Administrator 并连接到服务器

2. 选择左侧的“目录”

3. 右键单击左下角的框并选择“创建新架构”

   MySQL 管理员 http://img204.imageshack.us/img204/7528/adminsx9.th.gif放大图片

4. 命名新模式（例如：“dbn”）

   MySQL 新架构 http://img262.imageshack.us/img262/4374/newwa4.th.gif放大图

5. 打开 Windows 命令提示符 (cmd)

   Windows 命令提示符 http://img206.imageshack.us/img206/941/startef7.th.gif放大图像

6. 将目录更改为 MySQL 安装文件夹

7. 执行命令：

   mysql -u root -p dbn < C:\dbn_20080912.dump
   

   ...其中“root”是用户名，“dbn”是数据库名称，“C:\dbn_20080912.dump”是mysqldump .dump文件的路径/文件名

   MySQL dump 恢复命令行 http://img388.imageshack.us/img388/2489/cmdjx0.th.gif放大图片

8. 享受！

回答by Ashwin A

You can try SQLyog'Execute SQL script' tool to import sql/dump files.

您可以尝试使用SQLyog'执行 SQL 脚本'工具来导入 sql/dump 文件。

回答by Hengjie

If you want to view the progress of the dump try this:

如果要查看转储的进度，请尝试以下操作：

pv -i 1 -p -t -e /path/to/sql/dump | mysql -u USERNAME -p DATABASE_NAME

pv -i 1 -p -t -e /path/to/sql/dump | mysql -u USERNAME -p DATABASE_NAME

You'll of course need 'pv' installed. This command works only on *nix.

您当然需要安装“pv”。此命令仅适用于 *nix。

回答by Michael

As a specific example of a previous answer:

作为先前答案的具体示例：

I needed to restore a backup so I could import/migrate it into SQL Server. I installed MySql only, but did not register it as a service or add it to my path as I don't have the need to keep it running.

我需要恢复备份，以便我可以将它导入/迁移到 SQL Server。我只安装了 MySql，但没有将其注册为服务或将其添加到我的路径中，因为我不需要让它继续运行。

I used windows explorer to put my dump file in C:\code\dump.sql. Then opened MySql from the start menu item. Created the DB, then ran the source command with the full path like so:

我使用 Windows 资源管理器将我的转储文件放在 C:\code\dump.sql 中。然后从开始菜单项打开MySql。创建数据库，然后使用完整路径运行源命令，如下所示：

mysql> create database temp
mysql> use temp
mysql> source c:\code\dump.sql