?
Solved

export from MYSQL, command needed

Posted on 2011-02-12
17
Medium Priority
?
586 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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
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 1332 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 1332 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 668 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

719 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