7

Why doesn't my restore operation work?

I created a dumpfile for a database using:

mysqldump -u root -p databasename > /home/databasename_bkup.sql  

I then opened the dumfile and confirmed that it contains CREATE TABLE statements for each of the tables in the database. So I dropped the database and re-created an empty database of the same name before running the following restore command:

mysqldump -u root -p databasename < /home/databasename_bkup.sql  

This restore command resulted in the following printing in the terminal:

-- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost    Database: databasename
-- ------------------------------------------------------
-- Server version   5.6.23

/*!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 2015-03-05 17:56:29

But then when I log into mysql to check the contents of the database, I found out that the database is empty, as follows:

mysql> use databasename;
Database changed
mysql> show tables;
Empty set (0.00 sec)

So why is the database not being restored? What specific syntax can I use to ensure that the database does get restored properly?

16

You have to use the mysql client to reload

mysql -u root -p -Ddatabasename < /home/databasename_bkup.sql  

Another way to reload would be

mysql -u root -p -Ddatabasename

then from the MySQL prompt, do this

mysql> source /home/databasename_bkup.sql  

If you would like the mysqldump to drop and recreate the database for you, create the dump like this:

mysqldump -u root -p --add-drop-database -B databasename > /home/databasename_bkup.sql  

Then, running the script

mysql -u root -p < /home/databasename_bkup.sql  

does the DROP DATABASE command for you.

6

Basically there is a basic difference between the command to take backup and restore a mysql database on linux servers

To take a backup we have to use a command mysqldump like this on the command prompt

#mysqldump -u [username] -p[password] [databasename] > [backupfilename] 

but when you want to restore the same backup on the server, you should write a command like this

#mysql -u [username] -p[password] [databasename] < [backupfilename] 

most of the time people use mysqldump command to restore the database tables thus mysql is not able to create tables or insert rows

3

dont use "mysqldump" while restoring backup. Use "mysql". mysqldump is for taking backup, not for restoring.

-4

put "", like this mysqldump -u root -p databasename > "c:/home/databasename_bkup.sql"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.