Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

export from MYSQL, command needed

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
MAS (MVE)
Asked:
MAS (MVE)
  • 6
  • 4
  • 4
  • +1
3 Solutions
 
Sudaraka WijesingheWeb Application ProgrammerCommented:
Are you running this command from within MySQL? It should be executed in the command line of the server shell/console.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
I tried both
0
 
Sudaraka WijesingheWeb Application ProgrammerCommented:
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
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.

 
MAS (MVE)Technical Department HeadAuthor Commented:
It did not work for me.
0
 
Steve BinkCommented:
Post a screen-shot or copy/paste of your attempt.
0
 
NopiusCommented:
Hi.

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

Hope this helps.
Regards,
Arty
0
 
NopiusCommented:
Message about incorrect SQL syntax and 'mysql dump' (2 words) warned me.
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
---->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
 
Sudaraka WijesingheWeb Application ProgrammerCommented:
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
 
Sudaraka WijesingheWeb Application ProgrammerCommented:
0
 
MAS (MVE)Technical Department HeadAuthor Commented:
I tried, but it is ending up in 'access denied'

0
 
Steve BinkCommented:
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
 
Steve BinkCommented:
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
 
MAS (MVE)Technical Department HeadAuthor Commented:
now the error is 'access denied @localhost...'

0
 
Steve BinkCommented:
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
 
MAS (MVE)Technical Department HeadAuthor Commented:
You need to have access to the folder which you are trying to export to. Many thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now