Solved

export from MYSQL, command needed

Posted on 2011-02-12
17
582 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☠
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 27

Author Comment

by:☠MAS☠
ID: 34888850
It did not work for me.
0
 
LVL 51

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 27

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 27

Author Comment

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

0
 
LVL 51

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 51

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 27

Author Comment

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

0
 
LVL 51

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 27

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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

624 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