V

19

669

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?

Vulcanize answered 19/9, 2008 at 21:27 Comment(1)

Great question, thanks for asking this before me, upvoted! Ended up going with a non-accepted solution that worked great, but every method is good. Cheers. – Spar 10/11, 2021 at 20:5

H

681

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.

Homophonic answered 19/9, 2008 at 21:29 Comment(8)

Do I use "MySQL Command Line Client"? I've never used MySQL before. – Vulcanize 19/9, 2008 at 21:31

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. – Homophonic 19/9, 2008 at 21:34

Of course the database that you put in the "using" statement will have to exist first. – Homophonic 19/9, 2008 at 21:35

see vogs answer. The syntax is mysql -u<user> -p mydatabasename < db_backup.dump no need for a USE statement at the beginning of the file – Guilty 17/1, 2014 at 14:29

This flat-out flies in the MySQL command line interface. Note, however, that, to avoid a harmless, but disconcerting series of error messages, Windows installations must replace backslashes in path strings with forward slashes. I also used tee to attach a log file, and ran SHOW TABLES behind each restore to demonstrate that it completed. – Hotel 23/1, 2018 at 8:6

"Open up the dump file in a text editor" suggestion seems strange. That might work with small databases or a buffered text editor like EditPad, but with everything else it will choke. It would be nice if you didn't have to take this step. – Epigeous 14/2, 2018 at 21:54

my sql dump file is on my local computer, what should be the path of the dump file then. Is it ok to use the local computer path cause it isn't working for me. Should i have to first upload the dump file to remote server disk space? – Goldi 29/4, 2019 at 9:55

As mentioned by @Guilty you don't need the USE <database-name>; at the top of the file. For a single database dump you can provide the database name in the mysql statement as a parameter. The answer chosen as correct should be edited with this information as not everyone notice useful comments. – Menses 30/1, 2020 at 1:0

H

859

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;

Headline answered 9/6, 2009 at 18:58 Comment(9)

i personally like this method. it shows progress of the dump and also exactly how much time different sql is taking. – Wingding 13/1, 2010 at 13:31

@Phrogz Actually you can supply password on the command line using mysql -u root -psecret without the space, but has the disadvantage that your password shows up in cleartext in process lists and log files. It's better, as you suggest, to use empty -p and type it in the prompt. – Hizar 13/9, 2011 at 2:52

This method has the benefit of allowing session-based variables (e.g. FOREIGN_KEY_CHECKS) to be set without having to edit the dump file – Glauce 27/1, 2012 at 17:23

this is all well and great when doing this sort of thing by hand, but doesnt work from a bash script. – Tonl 5/1, 2015 at 4:14

also - if you don't know the database name, then run: "head -n 30 db_backup.dump" and you should see the database name there – Mulley 22/8, 2015 at 16:49

I needed to restore a backup so I could import 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 just 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: mysql> create database temp mysql> use temp mysql> source c:\code\dump.sql (I added it as an answer below that has the better space formatting.) – Contrivance 13/1, 2017 at 22:8

this is better as you don't have to modify the dump file if it is from a single database. – Musca 25/9, 2017 at 3:53

One drawback: it cannot load gzipped dump (e.g. generated with mysqldump db | gzip -9 > dumpfile.sql.gz) – Superabound 3/10, 2018 at 18:16

Worked like a charm! For some reason, I could not run the restore from the command line and needed to log into mysql and use your method. THANK YOU! – Pater 3/9, 2023 at 2:4

H

681

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