How do i restore an existing mysql database?

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?

vog

21.6k11 gold badges56 silver badges73 bronze badges

asked Sep 19, 2008 at 21:27

Zack PetersonZack Peterson

55.2k78 gold badges207 silver badges280 bronze badges

1

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;

answered Jun 9, 2009 at 18:58

8

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.

answered Sep 19, 2008 at 21:29

Justin BennettJustin Bennett

8,7282 gold badges26 silver badges29 bronze badges

8

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.

dur

14.5k22 gold badges78 silver badges115 bronze badges

answered Sep 19, 2008 at 21:45

vogvog

21.6k11 gold badges56 silver badges73 bronze badges

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

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

answered Jun 3, 2013 at 17:11

womdwomd

2,89425 silver badges17 bronze badges

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.

answered Sep 19, 2008 at 21:34

pdcpdc

2,24620 silver badges28 bronze badges

0

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

answered Mar 30, 2018 at 6:00

Javeed ShakeelJaveed Shakeel

2,5602 gold badges27 silver badges39 bronze badges

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.

answered Feb 6, 2012 at 8:20

HengjieHengjie

4,4442 gold badges29 silver badges35 bronze badges

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

answered Jan 13, 2017 at 22:16

MichaelMichael

4524 silver badges8 bronze badges

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

answered Aug 21, 2012 at 10:19

Ashwin AAshwin A

3,74922 silver badges28 bronze badges

0

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

answered Jan 3, 2013 at 16:59

Jerome_BJerome_B

1,0698 silver badges14 bronze badges

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

answered Mar 30, 2016 at 11:37

vkrishna17vkrishna17

89610 silver badges17 bronze badges

3

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.

answered Jun 15, 2009 at 21:11

One-liner command to restore the generated SQL from mysqldump

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

answered Dec 27, 2017 at 16:10

3

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

mysql databasename < backup.sql

answered Jun 13, 2019 at 22:40

SuragchSuragch

444k285 gold badges1309 silver badges1346 bronze badges

0

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.

answered Nov 9, 2008 at 3:53

user26087user26087

7792 gold badges11 silver badges21 bronze badges

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.

answered Oct 7, 2021 at 0:40

rɑːdʒɑrɑːdʒɑ

5,18713 gold badges42 silver badges77 bronze badges

1

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! 🎉

answered Feb 6, 2019 at 1:08

CTS_AECTS_AE

11.1k8 gold badges53 silver badges58 bronze badges

Can we restore database in MySQL?

How do I restore an old MySQL database?

How do I restore my entire database?

How do I manually restore a database?