Solved

export from MYSQL, command needed

Posted on 2011-02-12
17
568 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 24

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
 
LVL 24

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 24

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 24

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 24

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 24

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now