Error while performing backup using mysqldump at mysql command line

Question

Hello, I am trying to back up from the mysql command line client. I use mysqldump for backup with username and password. The following is the command I use to back up the database.

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

I get the 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 

Although the command seems correct, I still get an error. Please let me know if there is another way to backup from mysql command line.

Thanks in advance.

Answer 1

mysqldump not a MySQL command; it is a command line utility. You must call it from the shell command line.

Answer 2

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

Answer 3

In your team, you cannot have a space between -p and password . In addition, mysqldump should be run on the command line, and not in the mysql shell.

Try this on the command line

 mysqldump -u username -ppassword databasename > backup.sql 

Answer 4

I did not understand what others were trying to say until I saw this question .

The fact is that you cannot access "mysql.exe" and put mysqldump in it. "Mysqldump.exe" is another file, so you have to execute it from the command line of the OS transfer options to execute.

So, in DOS (on Windows, of course), suppose you are in the directory: "C: \ xampp \ mysql \ bin", you can call the following command:

 mysqldump -u root -p test > test.sql 

You can also call it so that you can explicitly see that you are executing the file:

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

If it can be more crystal clear, you will see this line in DOS:

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

Ps: in this code you will be asked to provide a password after it is executed. That is, indeed, the recommendation "mysqldump.exe" provides if you put the password directly in the dump line.

Ps 2: if you use the default settings for the root user (that is, with an empty password), you just need to press "Enter" when you are asked to provide a password.

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

Answer 5

Type this in the command line interface NOT at the MYSQL command line:

 mysqldump -u username -ppassword databasename > backup.sql 

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

 mysqldump -u root -pabcdefg mydatabase > backup.sql 

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