mysql - How to perform a mysqldump without a password prompt?

Question

I would like to know the command to perform a mysqldump of a database without the prompt for the password.

REASON: I would like to run a cron job, which takes a mysqldump of the database once everyday. Therefore, I won't be able to insert the password when prompted.

How could I solve this ?

Answer 1

Since you are using Ubuntu, all you need to do is just to add a file in your home directory and it will disable the mysqldump password prompting. This is done by creating the file ~/.my.cnf (permissions need to be 600).

Add this to the .my.cnf file

[mysqldump]
user=mysqluser
password=secret

This lets you connect as a MySQL user who requires a password without having to actually enter the password. You don't even need the -p or --password.

Very handy for scripting mysql & mysqldump commands.

The steps to achieve this can be found in this link.

Alternatively, you could use the following command:

mysqldump -u [user name] -p[password] [database name] > [dump file]

but be aware that it is inherently insecure, as the entire command (including password) can be viewed by any other user on the system while the dump is running, with a simple ps ax command.