mysql, dump, database restore

Question

I have dumped my database with the following command:

mysqldump -uuser -ppassword db_name > file

then I completely removed my database:

drop database db_name;

then I created a new database:

create database db_name;

and then I tried to restore the db with the following command:

mysqldump -uuser -ppassword db_name < file

The problem is that dump does not create tables and loads data in them and so the database remains empty however it does show a message like dump completed "date time"

What could be the reason for this?

You simply need to use `mysql -uXXX -pXXX < dump_file.sql`. (i.e.: `mysqldump` is only for exporting data, not importing it. — John Parker, Feb 11 '11 at 19:58

score 28 · Accepted Answer · answered Feb 11 '11 at 19:54

28

mysqldump is for dumping the database. You've created a new empty database, and then dumped that empty database. Use mysql instead to reload your dump

mysqldump db > dump.sql
mysql drop/create
mysql db < dump.sql

would be the basic command sequence.

answered Feb 11 '11 at 19:54

Marc B

356,200
43
426
500

score 14 · Answer 2 · answered Feb 11 '11 at 21:13

14

I like to do the following to restore.

mysql -uuser -ppassword
create database db;
use db;
source dump.sql;

answered Feb 11 '11 at 21:13

ScArcher2

85,501
44
121
160

score 4 · Answer 3 · answered Dec 13 '17 at 12:28

I tried dump of my database with the following commands:

#mysqldump -u <username> -p <password> DB_name > <filename>.sql

Then login into the DB:

   #mysql -u <username> -p <password>
   >show databases;
   >drop database <DB_name>;

Then create a new database:

#create database <DB_name>;

DB_name is userdefined name we can have any name.

and then restore the DB with the following command:

#mysql -u <username> -p <password> DB_name < <filename>.sql

mysql, dump, database restore

3 Answers3

Linked