11

Hello I'm trying to take backup from mysql command line client. I'm using mysqldump to take backup with username and password. Following is the command I'm using for backing up the database.

mysql> mysqldump -u username -p password databasename > backup.sql;

I'm getting following 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 -p password fms > backup.sql' at line 1

Though the command seems to be correct, still i'm getting error. Please let me know is there any other way taking backup from mysql command line.

Thanks in advance.

Improve this question
0

6 Answers 6

Reset to default
23

mysqldump is not a MySQL command, it is a command line utility. You must call it from your shell command line.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The same way you are accessing it to run mysql -u username -ppassword command - via SSH
3

The problem is you are executing the command from a MySQL prompt instead of a Linux shell. Exit the mysql command line and run the command from a OS shell (remove the semicolon at the end)

Improve this answer

Comments

3

In your command, you can't have a space between -p and the password. Also, mysqldump has to be run on the command line, not in a mysql shell.

Try this on your command line

mysqldump -u username -ppassword databasename > backup.sql
Improve this answer

1 Comment

I've tried that also, but of no use same error I'm getting. How do I run mysqldump in command line? Please let me know. Thanks for replaying.
2

I didn't understood what the others was trying to say until i saw this question.

The thing is, you can't access the "mysql.exe" and put the mysqldump in it. The "mysqldump.exe" is another file, so you have to execute it from the command line of the OS passing parameters to the execution.

So, in the DOS (on Windows, of course), supose that you're in the directory: "C:\xampp\mysql\bin", then you can call the following command:

mysqldump -u root -p test > test.sql

You could also call it like this, so you can explicitly see that you're executing a file:

.\mysqldump.exe -u root -p test > test.sql

If it can be more crystal clean, you would see that line in the DOS:

c:\xampp\mysql\bin>.\mysqldump.exe -u root -p test > test.sql

Ps: in that code you would be asked to inform the password after executing. That is, indeed, the recomendation that the "mysqldump.exe" gives you if you put the password directly in the dump line.

Ps 2: if you're using the default settings of the root user (i.e. with empty password), you need to just press "Enter" when asked to inform the password.

Ps 3: the "test.sql" will be created in the same directory of the "mysqldump.exe". In this example, in: "C:\xampp\mysql\bin".

Improve this answer

Comments

2

Type this in your command line interface NOT in MYSQL command line:

mysqldump -u username -ppassword databasename > backup.sql

For example, if username is 'root', password is 'abcdefg', and the database name is 'mydatabase', then the syntax is: 

mysqldump -u root -pabcdefg mydatabase > backup.sql

backup.sql is the name of the file in which your backup will be stored so you can have any name.

Improve this answer

2 Comments

doesn't add anything to existing answers.
This actually solved the problem for me. Using --password=PASSWORD made all the difference and worked perfectly...
0

FOR WINDOWS open cmd and cd to

*cd C:\Program Files\MySQL\MySQL Server 8.0\bin

mysqldump -u root -p DBNAME > D:/DB_backup/DBNAME.sql*

boom guys

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.