?
Solved

MYSQL, Mysqldump didnt restore from backups file but "MySQL Administrator GUI" does

Posted on 2009-02-18
4
Medium Priority
?
653 Views
Last Modified: 2012-06-27
I did try multiple times to restore database from command line, but never get data being restored. but when I use GUI "MySQL Administrator GUI" it took me 5 minutes to complete restoration with success, can someone tel me why ? The way I did is create the database - create table shoe - restore old data from  backup set FFTS.sql, but didnt work


C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqldump -u root -p FFTS <F:\FFTS.sql
Enter password: ***********
-- MySQL dump 10.13  Distrib 5.1.29-rc, for Win32 (ia32)
--
-- Host: localhost    Database: FFTS
-- ------------------------------------------------------
-- Server version       5.1.29-rc-community

/*!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 */;

--
-- Table structure for table `shoe`
--

DROP TABLE IF EXISTS `shoe`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `shoe` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Size` varchar(45) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `shoe`
--

LOCK TABLES `shoe` WRITE;
/*!40000 ALTER TABLE `shoe` DISABLE KEYS */;
/*!40000 ALTER TABLE `shoe` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 2009-02-18  8:31:56
0
Comment
Question by:motioneye
  • 2
4 Comments
 
LVL 22

Assisted Solution

by:Ivo Stoykov
Ivo Stoykov earned 1200 total points
ID: 23668141
mysqldump is not a nool for restore but for export
try mysql instead... (but check syntaxes in doc or use mysql --help)
HTH
i
0
 

Author Comment

by:motioneye
ID: 23668218
I thought that

mysqldump -u root -p FFTS <F:\FFTS.sql -- this for restore whereas

mysqldump -u root -p FFTS >F:\FFTS.sql -- this for backups
0
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 1200 total points
ID: 23668259
OK, to restore the last full backup which is just a set of SQL statements:


shell> mysql < backup.sql

Open in new window

0
 
LVL 8

Assisted Solution

by:agamal
agamal earned 800 total points
ID: 23668282
use this


mysql -p -h DBSERVER dbname < dbname.sql

replace dbname.sql with your dumpfile name
replace DBSERVER with your host name
replce dbname with your database name


and tell me if this worked for you or what ?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question