How do I restore a dump file from mysqldump?
MysqlSqlDatabaseMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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;
Solution 2 - Mysql
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.
Solution 3 - Mysql
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.
Solution 4 - Mysql
mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
look here - step 3: this way you dont need the USE statement
Solution 5 - Mysql
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.
Solution 6 - Mysql
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
Solution 7 - Mysql
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.
Solution 8 - Mysql
I got it to work following these steps…
-
Open MySQL Administrator and connect to server
-
Select "Catalogs" on the left
-
Right click in the lower-left box and choose "Create New Schema"
-
Name the new schema (example: "dbn")
-
Open Windows Command Prompt (cmd)
-
Change directory to MySQL installation folder
-
Execute command:
mysql -u root -p dbn < C:\dbn_20080912.dump
…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]
0. Enjoy!
Solution 9 - Mysql
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
Solution 10 - Mysql
You can try SQLyog 'Execute SQL script' tool to import sql/dump files.
Solution 11 - Mysql
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
Solution 12 - Mysql
./mysql -u <username> -p <password> -h <host-name like localhost> <database-name> < db_dump-file
Solution 13 - Mysql
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.
Solution 14 - Mysql
One-liner command to restore the generated SQL from mysqldump
mysql -u <username> -p<password> -e "source <path to sql file>;"
Solution 15 - Mysql
Assuming you already have the blank database created, you can also restore a database from the command line like this:
mysql databasename < backup.sql
Solution 16 - Mysql
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.
Solution 17 - Mysql
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.
Solution 18 - Mysql
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
- Click the Open an SQL script in a new query tab icon and choose your db dump file.
- Then Click Run SQL Script...
- It will then let you preview the first lines of the SQL dump script.
- You will then choose the Default Schema Name
- 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.
- Click Run
- Be patient for large DB restore scripts and watch as your drive space melts away!