[mysql] How do I restore a dump file from 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?

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.

How to Restore MySQL Database with MySQLWorkbench

You can run the drop and create commands in a query tab.

Drop the Schema if it Currently Exists

DROP DATABASE `your_db_name`;

Create a New Schema

CREATE SCHEMA `your_db_name`;

Open Your Dump File

1. Click the Open an SQL script in a new query tab icon and choose your db dump file.
2. Then Click Run SQL Script...
3. It will then let you preview the first lines of the SQL dump script.
4. You will then choose the Default Schema Name
5. Next choose the Default Character Set utf8 is normally a safe bet, but you may be able to discern it from looking at the preview lines for something like character_set.
6. Click Run
7. Be patient for large DB restore scripts and watch as your drive space melts away!

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

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

Assuming you already have the blank database created, you can also restore a database from the command line like this:

mysql databasename < backup.sql

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.

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

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>;"

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.

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!

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

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.

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.

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

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

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