mysqldatabasedumppassword-prompt

How to perform a mysqldump without a password prompt?


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?


Solution

  • 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.