views:

23538

answers:

9

I was given a .dump 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?

+22  A: 

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

Justin Bennett
Do I use "MySQL Command Line Client"? I've never used MySQL before.
Zack Peterson
I'm not sure how many databases it contains. I assume one.
Zack Peterson
So they put a guy who's never used mysql before in charge of admin of the mysql server? sounds like a recipe for disaster :P
davr
Open up the dump file in a text editor, it's fairly easy to pick through, if there are any "using" statements, then its of multiple databases, if there are none, you'll have to add one at the top before you can run that command.
Justin Bennett
Of course the database that you put in the "using" statement will have to exist first.
Justin Bennett
Yeah, Davr, go figure! I need to get this up and running so that I can re-engineer it (including a comfortable language and database).
Zack Peterson
I believe "USE" is the correct Keyword.
Atømix
A: 

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.

pdc
+2  A: 

You simply need to run this:

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

If the dump contains multiple databases you should obmit 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.

vog
+4  A: 
Zack Peterson
A: 

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.

+7  A: 

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 secret

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;
Don
i personally like this method. it shows progress of the dump and also exactly how much time different sql is taking.
ToughPal
This should be marked as proper answer.
dpc.ucore.info
A: 

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.

A: 

Hats off to Zack Peterson and his solution worked perfectly for me.

Thank you so much.

regards

JohnVolta

Case History: Step 1. mysql database dump was taken into .sql format from linux machine Step 2. this was brought into windows server 2003 machine, having windows version of mysql installed with mysql gui admin tools Step 3.followed zack peterson instructions as stated in his post Step 4.it worked flawlessly, without any errors, 100% sucessful