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


Translate

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 ?


All Answers
  • Translate

    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.


  • Translate

    Adding to @Frankline's answer:

    The -p option must be excluded from the command in order to use the password in the config file.

    Correct:
    mysqldump –u my_username my_db > my_db.sql

    Wrong:
    mysqldump –u my_username -p my_db > my_db.sql



    .my.cnf can omit the username.

    [mysqldump]
    password=my_password
    

    If your .my.cnf file is not in a default location and mysqldump doesn't see it, specify it using --defaults-file.

    mysqldump --defaults-file=/path-to-file/.my.cnf –u my_username my_db > my_db.sql


  • Translate

    A few answers mention putting the password in a configuration file.

    Alternatively, from your script you can export MYSQL_PWD=yourverysecretpassword.

    The upside of this method over using a configuration file is that you do not need a separate configuration file to keep in sync with your script. You only have the script to maintain.

    There is no downside to this method.

    The password is not visible to other users on the system (it would be visible if it is on the command line). The environment variables are only visible to the user running the mysql command, and root.

    The password will also be visible to anyone who can read the script itself, so make sure the script itself is protected. This is in no way different than protecting a configuration file. You can still source the password from a separate file if you want to have the script publicly readable (export MYSQL_PWD=$(cat /root/mysql_password) for example). It is still easier to export a variable than to build a configuration file.

    E.g.,

    $ export MYSQL_PWD=$(>&2 read -s -p "Input password (will not echo): "; echo "$REPLY")
    $ mysqldump -u root mysql | head
    -- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
    --
    -- Host: localhost    Database: mysql
    -- ------------------------------------------------------
    -- Server version   5.6.23
    /*!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 */;
    $ mysqldump -u root mysql | head
    -- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
    --
    -- Host: localhost    Database: mysql
    -- ------------------------------------------------------
    -- Server version   5.6.23
    /*!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 */;
    

  • Translate

    To use a file that is anywhere inside of OS, use --defaults-extra-file eg:

    mysqldump --defaults-extra-file=/path/.sqlpwd [database] > [desiredoutput].sql
    

    Note: .sqlpwd is just an example filename. You can use whatever you desire.

    Note: MySQL will automatically check for ~/.my.cnf which can be used instead of --defaults-extra-file

    If your using CRON like me, try this!

    mysqldump --defaults-extra-file=/path/.sqlpwd [database] > "$(date '+%F').sql"
    

    Required Permission and Recommended Ownership

    sudo chmod 600 /path/.sqlpwd && sudo chown $USER:nogroup /path/.sqlpwd
    

    .sqlpwd contents:

    [mysqldump]
    user=username
    password=password
    

    Other examples to pass in .cnf or .sqlpwd

    [mysql]
    user=username
    password=password
    
    [mysqldiff]
    user=username
    password=password
    
    [client]
    user=username
    password=password
    

    If you wanted to log into a database automatically, you would need the [mysql] entry for instance.

    You could now make an alias that auto connects you to DB

    alias whateveryouwant="mysql --defaults-extra-file=/path/.sqlpwd [database]"
    

    You can also only put the password inside .sqlpwd and pass the username via the script/cli. I'm not sure if this would improve security or not, that would be a different question all-together.

    For completeness sake I will state you can do the following, but is extremely insecure and should never be used in a production environment:

    mysqldump -u [user_name] -p[password] [database] > [desiredoutput].sql
    

    Note: There is NO SPACE between -p and the password.

    Eg -pPassWord is correct while -p Password is incorrect.


  • Translate

    Yeah it is very easy .... just in one magical command line no more

    mysqldump --user='myusername' --password='mypassword' -h MyUrlOrIPAddress databasename > myfile.sql
    

    and done :)


  • Translate

    For me, using MariaDB I had to do this: Add the file ~/.my.cnf and change permissions by doing chmod 600 ~/.my.cnf. Then add your credentials to the file. The magic piece I was missing was that the password needs to be under the client block (ref: docs), like so:

    [client]
    password = "my_password"
    
    [mysqldump]
    user = root
    host = localhost
    

    If you happen to come here looking for how to do a mysqldump with MariaDB. Place the password under a [client] block, and then the user under a [mysqldump] block.


  • Translate

    Here is a solution for Docker in a script /bin/sh :

    docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "[client]" > /root/mysql-credentials.cnf'
    
    docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "user=root" >> /root/mysql-credentials.cnf'
    
    docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "password=$MYSQL_ROOT_PASSWORD" >> /root/mysql-credentials.cnf'
    
    docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec mysqldump --defaults-extra-file=/root/mysql-credentials.cnf --all-databases'
    

    Replace [MYSQL_CONTAINER_NAME] and be sure that the environment variable MYSQL_ROOT_PASSWORD is set in your container.

    Hope it will help you like it could help me !


  • Translate

    I have the following.

    /etc/mysqlpwd

    [mysql]
    user=root
    password=password
    

    With the following alias.

    alias 'mysql -p'='mysql --defaults-extra-file=/etc/mysqlpwd'
    

    To do a restore I simply use:

    mysql -p [database] [file.sql]
    

  • Translate

    what about --password="" worked for me running on 5.1.51

    mysqldump -h localhost -u <user> --password="<password>"
    

  • Translate

    Definitely I think it would be better and safer to place the full cmd line in the root crontab , with credentails. At least the crontab edit is restricred (readable) to someone who already knows the password.. so no worries to show it in plain text...

    If needed more than a simple mysqldump... just place a bash script that accepts credentails as params and performs all amenities inside...

    The bas file in simple

    #!/bin/bash
    mysqldump -u$1 -p$2 yourdbname > /your/path/save.sql
    

    In the Crontab:

    0 0 * * * bash /path/to/above/bash/file.sh root secretpwd 2>&1 /var/log/mycustomMysqlDump.log
    

  • Philip Lee
    Translate

    You can specify the password on the command line as follows:

    mysqldump -h <host> -u <user> -p<password> dumpfile
    

    The options for mysqldump are Case Sensitive!