如何从 mysqldump 还原一个转储文件？

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

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

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

How do I get this working?

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.

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.

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:

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:

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

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

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

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.gif enlarge image

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

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

5. Open Windows Command Prompt (cmd)

   Windows Command Prompt http://img206.imageshack.us/img206/941/startef7.th.gif enlarge 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.gif enlarge image

8. Enjoy!

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.

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;

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.

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

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

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

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

source file.sql

approach from the mysql prompt than with the

mysql  < file.sql

approach on the command line, that caused some Error 2006 "server has gone away" (on 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.

My values are

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

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

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

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

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.

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:

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

One-liner command to restore the generated SQL from mysqldump

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

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

# 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