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;

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.

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.

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.

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

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.

If you are already inside mysql prompt and assume your dump file dump.sql, then we can also use command as below to restore the dump

mysql> \. dump.sql

If your dump size is larger set max_allowed_packet value to higher. Setting this value will help you to faster restoring of dump.

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!