如何从mysqldump恢复转储文件?

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管理员，但我得到以下错误:

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

所选文件是由mysqldump生成的，不能由该应用程序还原。

How do I get this working?

我该怎么做?

15 个解决方案

#1

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 cd to the directory where the mysql.exe executable 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等包的一部分)。在该目录中之后，您应该能够像上面一样键入命令。

#2

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;

#3

You simply need to run this:

你只需要运行这个:

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

mysql -p -u[用户][数据库]< db_backup.dump .dump

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

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

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

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

To run these commands, open up a command prompt (in Windows) and cd to the directory where the mysql.exe executable 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等包的一部分)。在该目录中之后，应该可以只输入命令。

#4

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

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

看这里——步骤3:这样你就不需要使用语句了

#5

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 root is 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.

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

#6

I got it to work following these steps…

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

1. Open MySQL Administrator and connect to server

   打开MySQL管理员并连接到服务器。

2. Select "Catalogs" on the left

   选择左边的“Catalogs”

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

   在左下角右键单击并选择“创建新模式”

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

   MySQL Administrator http://img204.imageshack.us/img204/7528/adminsx9.th。gif图像放大

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

   命名新模式(例如:“dbn”)

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

   MySQL http://img262.imageshack.us/img262/4374/newwa4.th新模式。gif图像放大

5. Open Windows Command Prompt (cmd)

   打开Windows命令提示符(cmd)

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

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

6. Change directory to MySQL installation folder

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

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

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

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

   MySQL转储恢复命令行http://img388.imageshack.us/img388/2489/cmdjx0.th。gif图像放大

8. Enjoy!

   享受吧!

#7

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

您可以尝试SQLyog“执行SQL脚本”工具来导入SQL /转储文件。

#8

./mysql -u <username> -p <password> -h <host-name like localhost> <database-name> < db_dump-file

#9

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/转储| mysql -u用户名-p DATABASE_NAME。

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

你当然需要安装“pv”。这个命令只在*nix上运行。

#10

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。创建DB，然后使用完整路径运行source命令，如下所示:

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

#11

Using a 200MB dump file created on Linux to restore on Windows w/ mysql 5.5 , I had more success with the

使用在Linux上创建的200MB转储文件来在Windows w/ mysql 5.5上恢复，我在使用

source file.sql

approach from the mysql prompt than with the

从mysql提示符到

mysql  < file.sql

approach on the command line, that caused some Error 2006 "server has gone away" (on windows)

接近命令行，这导致了一些错误2006年“服务器已经消失”(在windows上)

Weirdly, the service created during (mysql) install refers to a my.ini file that did not exist. I copied the "large" example file to my.ini which I already had modified with the advised increases.

奇怪的是，在(mysql)安装期间创建的服务指的是my。不存在的ini文件。我将“大”示例文件复制到我的文件中。我已经修改过的ini增加了。

My values are

我的价值观是

[mysqld]
max_allowed_packet = 64M
interactive_timeout = 250
wait_timeout = 250

#12

You cannot use the Restore menu in MySQL Admin if the backup / dump wasn't created from there. It's worth a shot though. If you choose to "ignore errors" with the checkbox for that, it will say it completed successfully, although it clearly exits with only a fraction of rows imported...this is with a dump, mind you.

如果在MySQL Admin中没有创建备份/转储，则不能使用“恢复”菜单。不过值得一试。如果您选择使用复选框“忽略错误”，那么它会说它成功地完成了，尽管它很清楚地退出了，但只导入了一部分行……注意，这里有个垃圾场。

#13

Run the command to enter into the DB

运行命令进入DB

 # mysql -u root -p 

Enter the password for the user Then Create a New DB

输入用户的密码，然后创建一个新的DB

mysql> create database MynewDB;
mysql> exit

And make exit.Afetr that.Run this Command

退出。Afetr。运行这个命令

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

Then enter into the db and type

然后输入db并输入

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

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

也就是说........您的转储将从一个DB恢复到另一个DB

#14

One-liner command to restore the generated SQL from mysqldump

用一行命令从mysqldump恢复生成的SQL

mysql -u <username> -p<password> -e "source <path to sql file>;"

#15

You can also use the restore menu in MySQL Administrator. You just have to open the back-up file, and then click the restore button.

还可以在MySQL Administrator中使用“恢复”菜单。您只需打开备份文件，然后单击restore按钮。