CODEWITHSUNDEEP
mysqlsqldatabase
Zack Peterson
Zack Peterson

Reputation: 57373

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?

Upvotes: 690

Views: 1152543

Answers (19)

Zack Peterson
Zack Peterson

Reputation: 57373

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

&hellip;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][7] [enlarge image][8]

Upvotes: 15

zemil
zemil

Reputation: 5090

Local mysql:

mysql -u root --password=YOUR_PASS --database=YOUR_DB < ./dump.sql

And if you use docker:

docker exec -i DOCKER_NAME mysql -u root --password=YOUR_PASS --database=YOUR_DB < ./dump.sql

Upvotes: 5

Raja G
Raja G

Reputation: 6673

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.

Upvotes: 2

Suragch
Suragch

Reputation: 512516

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

mysql databasename < backup.sql

Upvotes: 6

vog
vog

Reputation: 25647

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.

Upvotes: 327

Hengjie
Hengjie

Reputation: 4672

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.

Upvotes: 22

CTS_AE
CTS_AE

Reputation: 14903

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

MySQLWorkbench open sql 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! 🎉

Upvotes: 1

Javeed Shakeel
Javeed Shakeel

Reputation: 3427

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

Upvotes: 30

Jossef Harush Kadouri
Jossef Harush Kadouri

Reputation: 34237

One-liner command to restore the generated SQL from mysqldump

mysql -u <username> -p<password> -e "source <path to sql file>;"

Upvotes: 5

Michael
Michael

Reputation: 502

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

Upvotes: 15

vkrishna17
vkrishna17

Reputation: 936

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

Upvotes: 10

Justin Bennett
Justin Bennett

Reputation: 9138

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.

Upvotes: 692

womd
womd

Reputation: 3453

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

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

Upvotes: 66

Jerome_B
Jerome_B

Reputation: 1097

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

Upvotes: 11

Ashwin A
Ashwin A

Reputation: 3867

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

enter image description here

Upvotes: 13

D&#243;nal
D&#243;nal

Reputation: 187379

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;

Upvotes: 889

jerotas
jerotas

Reputation:

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.

Upvotes: 4

user26087
user26087

Reputation: 839

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.

Upvotes: 1

pdc
pdc

Reputation: 2414

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.

Upvotes: 44

Related Questions