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