Solved

export from MYSQL, command needed

Posted on 2011-02-12
17
572 Views
Last Modified: 2013-11-23
I have been trying to export the database using the commnd in MYSQL5.1
If I type 'mysqldump -u username -ppassword –all-databases –single-transaction > dump.sql'

I am getting an error.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'mysql
dump -u username -ppassword -all-databases -single-transaction > dump.sql' at li

Anyone can help
0
Comment
Question by:-MAS
  • 6
  • 4
  • 4
  • +1
17 Comments
 
LVL 18

Expert Comment

by:Sudaraka Wijesinghe
ID: 34878979
Are you running this command from within MySQL? It should be executed in the command line of the server shell/console.
0
 
LVL 25

Author Comment

by:-MAS
ID: 34878994
I tried both
0
 
LVL 18

Expert Comment

by:Sudaraka Wijesinghe
ID: 34879017
Not sure of you are using the same code as you posted here, but just noticed that two parameters all-databases and single-transaction have the wrong dashes in front of them. Other than that it works fine for me.

mysqldump -u username -ppassword --all-databases --single-transaction > dump.sql

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 25

Author Comment

by:-MAS
ID: 34888850
It did not work for me.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34915847
Post a screen-shot or copy/paste of your attempt.
0
 
LVL 27

Expert Comment

by:Nopius
ID: 34916466
Hi.

'mysqldump' is a single word, not 2 separate.

Hope this helps.
Regards,
Arty
0
 
LVL 27

Expert Comment

by:Nopius
ID: 34916517
Message about incorrect SQL syntax and 'mysql dump' (2 words) warned me.
0
 
LVL 25

Author Comment

by:-MAS
ID: 34926039
---->routinet
Screenshot attached

--->Nopius
you can the command in the error, I typed as a single word.

I am new to MYSQL.

Please help
Doc1.docx
0
 
LVL 18

Assisted Solution

by:Sudaraka Wijesinghe
Sudaraka Wijesinghe earned 333 total points
ID: 34926526
As I mentioned earlier, you are running it from the mysql prompt, which is incorrect.
mysqldump is a separate tool that comes with Mysql client bundle and you need to run it from System command prompt (or DOS prompt as it used to be called...  C:\> )

from mysql prompt you can only execute SQL statements.
0
 
LVL 18

Assisted Solution

by:Sudaraka Wijesinghe
Sudaraka Wijesinghe earned 333 total points
ID: 34926544
0
 
LVL 25

Author Comment

by:-MAS
ID: 34927093
I tried, but it is ending up in 'access denied'

0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 167 total points
ID: 34931728
Please see the attached code sections.  The first is from bash, the second is from Windows command line.


[root@server:/home]
#> mysql -u admin -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 323225
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database stuff;
Query OK, 1 row affected (0.00 sec)

mysql> use stuff;
Database changed
mysql> create table blahblah (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into blahblah (id) values (1),(2),(3),(4),(6);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> quit
Bye

[root@server:/home]
#> cat stuff.sql
cat: stuff.sql: No such file or directory

[root@server:/home]
#> mysqldump -u admin -p stuff > stuff.sql
Enter password:

[root@server:/home]
#> cat stuff.sql
-- MySQL dump 10.11
--
-- Host: localhost    Database: stuff
-- ------------------------------------------------------
-- Server version       5.0.67-0ubuntu6

/*!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 `blahblah`
--

DROP TABLE IF EXISTS `blahblah`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `blahblah` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `blahblah`
--

LOCK TABLES `blahblah` WRITE;
/*!40000 ALTER TABLE `blahblah` DISABLE KEYS */;
INSERT INTO `blahblah` VALUES (1),(2),(3),(4),(6);
/*!40000 ALTER TABLE `blahblah` 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 2011-02-19  6:37:52

Open in new window


D:\mysql5\bin>mysql -u admin -p
Enter password: *****************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1708206
Server version: 5.0.51b-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database stuff;
Query OK, 1 row affected (0.00 sec)

mysql> use stuff;
Database changed
mysql> create table blahblah (id int);
Query OK, 0 rows affected (0.42 sec)

mysql> insert into blahblah (id) values (1),(2),(3),(4),(6);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> quit
Bye

D:\mysql5\bin>type stuff.sql
The system cannot find the file specified.

D:\mysql5\bin>mysqldump -u admin -p stuff > stuff.sql
Enter password: *****************

D:\mysql5\bin>type stuff.sql
-- MySQL dump 10.11
--
-- Host: localhost    Database: stuff
-- ------------------------------------------------------
-- Server version       5.0.51b-community-nt-log

/*!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 `blahblah`
--

DROP TABLE IF EXISTS `blahblah`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `blahblah` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `blahblah`
--

LOCK TABLES `blahblah` WRITE;
/*!40000 ALTER TABLE `blahblah` DISABLE KEYS */;
INSERT INTO `blahblah` VALUES (1),(2),(3),(4),(6);
/*!40000 ALTER TABLE `blahblah` 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 2011-02-19  6:46:16

Open in new window

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34931729
If you are getting an access denied message, it is either because you are using the wrong MySQL credentials, or you do not have write access to the directory to which you are redirecting the mysqldump output.
0
 
LVL 25

Author Comment

by:-MAS
ID: 34936781
now the error is 'access denied @localhost...'

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34937061
See my last comment.  If you continue to have issues, post a capture of the attempt, as I posted an example for you to follow.
0
 
LVL 25

Author Closing Comment

by:-MAS
ID: 34945587
You need to have access to the folder which you are trying to export to. Many thanks to all
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

770 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