Home / server / Questions / 241188
Accepted
Sandeepan Nath
Asked: 2011-03-01 07:33:31 +0800 CST

Unable to restore a Mysql database using an sql dump file

  • 772

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

linux mysql command-line-interface database-restore

2 Answers

  1. Best Answer

    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.

    • 4

  2. A product from Recovery Toolbox called SQL Server Repair Database launches under low system requirements, it's able to show the results of restoring databases. Tool doesn't modify source .sql files during recovery.

    • -4