Unable to restore a Mysql database using an sql dump file

Question

I am setting up a new project on my development system and am stuck with creating the database. I have a large .sql file to create the database with.

At first I created an empty database called adpsnet_Directory and executed this command -

mysqldump -uroot -p  adpsnet_Directory < adpsnet_Directory.sql 

But it displays this and nothing actually happens -

-- MySQL dump 10.13  Distrib 5.1.41, for pc-linux-gnu (i686)
--
-- Host: localhost    Database: adpsnet_Directory
-- ------------------------------------------------------
-- Server version       5.1.41

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2011-02-28 20:48:56

Then I read here that the above command works only if the database does not exist. I was having an empty database. So, I dropped that database but got this error -

-- MySQL dump 10.13  Distrib 5.1.41, for pc-linux-gnu (i686)
--
-- Host: localhost    Database: adpsnet_Directory
-- ------------------------------------------------------
-- Server version       5.1.41
 ...
...
...

mysqldump: Got error: 1049: Unknown database 'adpsnet_Directory' when selecting the database

Then I tried the following command in the presence and absence of the empty database -

mysqlimport -uroot adpsnet_Directory adpsnet_Directory.sql

And the results -

Presence -

mysqlimport: Error: 1146, Table 'adpsnet_Directory.adpsnet_Directory' doesn't exist, when using table: adpsnet_Directory

Absence -

mysqlimport: Error: 1049 Unknown database 'adpsnet_Directory'

It is pretty late here and I am not getting what is happening. Any pointers please... What am I missing?

Thanks

Solution

You need to replace the "mysqldump" with just "mysql" - otherwise it all looks right. You probably do want the empty database to exist though. A MySQL dump file contains all of the instructions for building tables etc, so you don't need to use mysqlimport.

mysql -uroot -p  adpsnet_Directory < adpsnet_Directory.sql

This is assuming you used mysqldump to create the dump file in the first place, that is.

Comments

[gokva]

its confusing that mysqlimport is not the compliment of mysqldump. In fact mysqlimport is used for importing delimited data files such as CSV or TSV rather than the SQL file you have.

[Sandeepan Nath]

Okay thanks, but why then using mysqldump displays dump completed .... Doesn't it make things confusing for those like me who are not sure about the command? Also I saw that mysql -uroot -p adpsnet_Directory < adpsnet_Directory.sql works correctly for dump files which were not created using mysqldump. I had created the dump using PhpMyAdmin's export (which does not use the mysqldump command) and it worked correctly

[Dan]

PHPMyAdmin doesn't use the mysqldump command, but it does replicate its actions, so the output is the same - this is for convenience. A MySQL dump is basically just a great big SQL statement that creates the tables and inserts the data into a database, and so can be used by a range of different MySQL clients.

[Dan]

As for what mysqldump is doing - mysqldump's one purpose is to create a dump file. When you entered that first command, as you typed it in the question, it would dump the data out to the console. This is why when you created the dump, you used a ">" (greater than sign) to direct the output into the .sql file. When you use the "<" instead, as you did, the linux shell will try to read the file you specified in as input to the command - mysqldump doesn't accept any input as far as I'm aware, so it will just display its normal output.

[nickgrim]

That's for SQL Server, not MySQL, and so not going to be useful here.