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?

Infernal

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.

Handsomely

downvoted the other answers passing -p on the command line, as any user can ps aux to see root or user's password. Using the file suggestion above is most secure – Freedafreedman

fantastic answer! I was looking for a way to not have my password just sitting in my script, and this does the trick :) +1 for security! – Daybreak

Is it completely safe to place the plain root's pwd in my.cnf file? and also... This will give permission to every user on the server to execute a dump without a password... ex. apache user too (e.g. www-data) this means if anyone finds an exploit to execute command to your machine will be free to dump and read all you data, coz of root permission? – Zetland

If a global setting is not an option (in case you have not only one mysql instance to connect to), you can set the config file via --defaults-file. Like ` mysqldump --defaults-file=my_other.cnf --print-defaults` – Doerr

@kante: it is safe. It's only available to users to whom the .my.cnf file belongs to. – Intervalometer

-1 for encouraging -p without clarification on security implications – Beer

I followed the 1st option and didn't feed in password to mysqldump, but it said the task failed because not using password – Markos

This isn't a good solution because ~/.my.conf overrides everything (it'll force you to use the same password for ALL servers), and only recent MySQL versions support --defaults-file. – Unbind

On Windows, the configuration file is not at ~/.my.cnf. See https://mcmap.net/q/41226/-what-is-the-location-of-mysql-client-quot-my-cnf-quot-in-xampp-for-windows. MySql expected mine to be at c:\wamp\bin\mysql\mysql5.5.24\my.cnf. So I created a file there. Restarting Mysql wasn't necessary; it worked immediately for my next mysqldump. – Incision

@Unbind Instead of --defaults-file use --defaults-extra-file to add configuration. Both options are supported since at least mysql 5.0 which was released in 2003. That was 12 years ago. – Assembler

what do you do when there are multiple databases and multiple users? Is there a way to use the -p option and supply it on the prompt? ie. Not -pPassword, but -p database. I tried a heredoc << and it does not work – Bough

however@Malancea's answer with a local defaults-file works great – Bough

If the password contains weird chars (like #) one must use doublequotes, like this password="weird#@)(" – Photokinesis

Please ensure that the .my.cnf is set to 0400 for security reasons – Laurellaurella

to add a level of security, you should use a dedicated, non database specific, readonly user, and in no case the root user. It can be done like this: GRANT LOCK TABLES, SELECT ON *.* TO 'BACKUPUSER'@'%' IDENTIFIED BY 'PASSWORD'; – Oakland

Just to note the password needs to be in " quotes, dev.mysql.com/doc/refman/8.0/en/option-files.html – Grappling

@YannSagon Hmm not exactly true. Depends on the permissions of the file. Yes you can depending on the permissions even view a file that is in a home directory if you know the path. Maybe not in Windows; I wouldn't know there but I somehow suspect it's not an issue there as well. Or 'issue' depending on how you look at it. Yes the user is important but so is the permissions of the file/directory. – Cleaves

In order to make the answer clearer: when you use mysqldump -u [user name] -p[password] [database name] > [dump file] database the database name option is the personalized database name and not an option database with the argument name – Worsted

The mysql_config_editor utility enables you to store authentication credentials in an obfuscated login path file named .mylogin.cnf – Rooseveltroost

Note, while MariaDB is highly compatible with MySQL, mysql_config_editor is not supported for MariaDB v10. Ref: mariadb.com/kb/en/mysql_config_editor-compatibility For great details on MariaDB option files: mariadb.com/kb/en/configuring-mariadb-with-option-files – Vandalism

Answer 2

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

Railroad

Damnit, a lot of XAMPP tutorials include the -p without explaining it. It doesn't NOT work for bypassing a blank password... – Tho

Answer 3

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 */;

MariaDB

MariaDB documents the use of MYSQL_PWD as:

Default password when connecting to mysqld. It is strongly recommended to use a more secure method of sending the password to the server.

The page has no mentions of what a "more secure" method may be, though I have put my interpretation in the "Final thoughts" at the end.

MySQL

This method is still supported in the latest documented version of MySQL: https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html though it comes with the following warning:

Use of MYSQL_PWD to specify a MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments.

The security of environment variables is covered in much details at https://security.stackexchange.com/a/14009/10002 and this answer also addresses the concerns mentioned in the comments. TL;DR Irrelevant for over a decade.

Having said that, the MySQL documentation also warns:

MYSQL_PWD is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL.

To which I'll leave you with maxschlepzig's comment from below:

funny though how Oracle doesn't deprecate passing the password on the command line which in fact is extremely insecure

Final thoughts

Connecting to a system using a single factor of authentication (password) is indeed insecure. If you are worried about security, you can consider adding mutual TLS on top of the regular connection so both the server and the client are properly identified as being authorized.

Thimerosal

Although this answer is not really "incorrect", it is not yet secure, the environment variables exported are still easily visible when the program is running... – Sarcoma

@MatheusOl: The environment variables are only visible to root and the user itself - the same users who would have access to a configuration file holding the password anyway. – Thimerosal

Upvoted: Easier to do when scripting, and instead of having to try to properly script an options file creation while being careful to have permissions where no one else can read it, a variable has the right "hiddeness" by default. – Satyr

This is not secure for the same reason as using the password directly is a problem. The export MYSQL_PWD= shows up for a split-second in the process list. Any non-priveleged user can monitor this and now has the password for later use. – Observe

How about the oneliner: MYSQL_PWD=xoF3mafn5Batxasdfuo mysqldump -u root mysql No export needed. That should do the trick? – Trinee

@DUzun, nice one. Upvoted. Glad to see that this environment variable topic causes as much discussion there as here :) – Thimerosal

@Observe You are wrong. The export MYSQL_PWD=... does not show up in the process list. Not even for a split second. This is because the export command is (and has to be) a shell builtin. Thus the shell doesn't fork/exec a process with the command's argument if you execute it in your shell. – Nastassia

@Nastassia your are right. In this case it doesn't matter because putting the password in ENV makes it visible to other users anyway (as warned against in the mysql documentation) – Observe

@Observe This is also incorrect. The mysql documentation on this subject is very poor and misleading. On Linux, the environment of a process is not readable by other non-root users. The same goes for most other UNIX/UNIX-like systems. An example for an OS where the environment is world-readable is Solaris (versions <= 9), whereas Solaris 9 was released in 2002 and thus this is hardly relevant anymore, by any measure. – Nastassia

@Nastassia "Use of MYSQL_PWD to specify a MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments." From the mysql docs. Also: the variable is deprecated. – Observe

@Observe As I've said the mysql documentation is very poor and misleading. 'must be considered extremely insecure and should not be used' is simply wrong and bad advice when using Linux and other systems. And this doesn't support your claim which was: 'because putting the password in ENV makes it visible to other users anyway'. Side note: funny though how Oracle doesn't deprecate passing the password on the command line which in fact is extremely insecure. – Nastassia

This question is still attracting attention so updated with some external information. @Nastassia I hope you don't mind that I included your "side note" to the answer. It made my day when I saw it. – Thimerosal

@WOUNDEDStevenJones just an indication of the time... let me reword it. – Thimerosal

Apparently not deprecated in mariadb, though it recommends against using it. – Gingergingerbread

Answer 4

You can store the credentials in a file and then utilize that in your scripts.

MySQL will automatically search the current users home directory for a file named .my.cnf

mysqldump [database] > [desiredoutput].sql

If you don't want to utilize the default location/filename of ~/.my.cnf then you can use

--defaults-extra-file

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

Note: .sqlpwd is just an example filename.

Required Permission

sudo chmod 600 /path/.sqlpwd 

Recommended Ownership

sudo chown $USER:nogroup /path/.sqlpwd

.sqlpwd/.my.cnf contents:

[mysqldump]
user=username
password=password

You can also add credentials for other MySQL utilities as below:

[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]"

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.

Abroad

This is the best answer, especially when considering multiple databases and users/passwords. – Floaty

BTW, long options (e.g. --defaults-file) should be placed before short options (like -u). Tested on mysqldump version 5.7.17. – Andriette

@Andriette An option argument begins with one dash or two dashes, depending on whether it is a short form or long form of the option name. Many options have both short and long forms. For example, -? and --help are the short and long forms of the option that instructs a MySQL program to display its help message. dev.mysql.com/doc/refman/8.0/en/command-line-options.html – Abroad

Answer 5

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 :)

Pachisi

Warning: Using a password on the command line interface can be insecure. – Blevins

@Yottatron It can be insecure, especially back in the day when ancient Unix systems easily had 10-15 people logged in for most of the day and vi bogged. On a modern VPS, while you can have 10-15 people shelled in and using IRC, it is common for only administrator(s) to have shell access. – Overblown

Thanks for this, I'm just using it in a Docker devbox. So security not an issue. – Arvonio

It's unsafe. Some one can see the password from history. – Brambly

I agree with you, it's unsafe to pass password through command line, however you need to clean history through command history -c – Pachisi

Just add a space in front of the command, the command will not appear in history – Kissinger

Answer 6

You can achieve this in 4 easy steps

1. create directory to store script and DB_backups
2. create ~/.my.cnf
3. create a ~/.script.sh shell script to run the mysqldump
4. Add a cronjob to run the mysql dump.

Below are the detailed steps

Step 1

create a directory on your home directory using sudo mkdir ~/backup

Step 2

In your home directory run sudo nano ~/.my.cnf and add the text below and save

[mysqldump]
#use this if your password has special characters (!@#$%^&..etc) in it
 password="YourPasswordWithSpecialCharactersInIt"
  
 #use this if it has no special characters
 password=myPassword

Step 3

cd into ~/backup and create another file script.sh add the following text to it

SQLFILE=/path/to/where/you/want/to/dump/dbname.sql
DATABASE=dbname
USER=myUsername

mysqldump --defaults-file=~/.my.cnf -u ${USER} ${DATABASE}|gzip > dbName_$(date +\%Y\%m\%d_\%H\%M).sql.gz

Step 4

In your console, type crontab -e to open up the cron file where the auto-backup job will be executed from

add the text below to the bottom of the file

0 0 * * * ./backup/script.sh

The text added to the bottom of the cron file assumes that your back up shall run daily at midnight.

That's all you need folk ;)

Preliminary

Hey it looks like you have two Step 2. Is that a mistake or is that a choice between one or the other? – Chill

@AlexisWilke Thank you for the keen eye. It was a mistake. the second Step 2 was meant to be Step 3 and Step 3 was meant to be Step 4. I've corrected it. Cheers! – Preliminary

Answer 7

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.

Wiggle

I'm using MariaDB as well (10.4 to be specific) and I simply put password under the [mysqldump] section and it worked without any issues. Ubuntu 18.04 LTS – Rotogravure

Answer 8

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 !

Neighborly

Answer 9

Check your password!

• Took me a while to notice that I was not using the correct user name and password in ~/.my.cnf

• Check the user/pass basics before adding in extra options to crontab backup entries

• If specifying --defaults-extra-file in mysqldump then this has to be the first option

• A cron job works fine with .my.cnf in the home folder so there is no need to specify --defaults-extra-file

• If using mysqlpump (not mysqldump) amend .my.cnf accordingly

• The ~/.my.cnf needs permissions set so only the owner has read/write access with:

  chmod 600 ~/.my.cnf

Here is an example .my.cnf:

[mysql]
host = localhost
port = 3306
user = BACKUP_USER
password = CORRECTBATTERYHORSESTAPLE
[mysqldump]
host = localhost
port = 3306
user = BACKUP_USER
password = CORRECTBATTERYHORSESTAPLE
[mysqlpump]
host = localhost
port = 3306
user = BACKUP_USER
password = CORRECTBATTERYHORSESTAPLE

• The host and port entries are not required for localhost

• If your user name in linux is the same name as used for your backup purposes then user is not required

Another tip, whilst you are doing a cronjob entry for mysqldump is that you can set it to be a low priority task with ionice -c 3 nice 19. Combined with the --single-transaction option for InnoDB you can run backups that will not lock tables or lock out resources that might be needed elsewhere.

Endothecium

Answer 10

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]

Savate

Why don't you use the default filename /etc/my.cnf or $HOME/.my.cnf? – Philippeville

Answer 11

This is how I'm backing-up a MariaDB database using an expanding variable.

I'm using a "secrets" file in a Docker-Compose setup to keep passwords out of Git, so I just cat that in an expanding variable in the script.

NOTE: The below command is executed from the Docker host itself:

mysqldump -h192.168.1.2 -p"$(cat /docker-compose-directory/mariadb_root_password.txt)" -uroot DB-Name > /backupsDir/DB-Name_`date +%Y%m%d-%H:%M:%S`.sql

This is tested and known to work correctly in Ubuntu 20.04 LTS with mariadb-client.

Figge

Note that OP specifically says how to pass the password in a way other than the command line. Doing a cat ... will add it to the command line anyway. – Chill

Answer 12

I'm doing mine a different way, using Plink(Putty command line) to connect to remotehost, then the below command is in the plink file that runs on the remote server, then I use RSYNC from windows to get it and backup to an onprem NAS.

sudo mysqldump -u root --all-databases --events --routines --single-transaction > dump.sql

I have keys setup on the remote host and using PowerShell that's scheduled via task scheduler to run weekly.

Wallachia

Answer 13

For example, you can export the schema and data of the tables of apple database to backup.sql without a password prompt by setting a password(e.g., banana) to -p(--password=) as shown below. *Don't put any space just after -p or --password= because there is error and my answer explains how to export the schema and data of the tables of a database and my answer explains how to import a database without a password prompt:

mysqldump -u john -pbanana apple > backup.sql

Or:

mysqldump -u john --password=banana apple > backup.sql

Or, you can export the schema and data of the tables of apple database to backup.sql without a password prompt by setting a password(e.g., banana) to MYSQL_PWD= as shown below. *The doc says MYSQL_PWD is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL.:

MYSQL_PWD=banana mysqldump -u john apple > backup.sql

Or on Windows, you can set the user john and the password banana under [client] in my.ini as shown below. *This way is more secure than the ways above and my answer explains [client] and my answer explains where my.ini is located on Windows:

# "my.ini"

[client]
user="john"
password="banana"

Then, you can export the schema and data of the tables of apple database to backup.sql without a password prompt by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below:

mysqldump --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' apple > backup.sql

Or:

mysqldump --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' apple > backup.sql

*Not setting my.ini's location to --defaults-file= or --defaults-extra-file= gets error as shown below:

mysqldump apple > backup.sql
mysqldump: Got error: 1045: Access denied for user 'ODBC'@'localhost' (using password: NO) when trying to connect

Decastyle

Answer 14

For example, you can import backup.sql into orange database without a password prompt by setting a password(e.g., banana) to -p(--password=) as shown below. *Don't put any space just after -p or --password= because there is error and my answer explains how to import the schema and data of the tables of a database and my answer explains how to export a database without a password prompt:

mysql -u john -pbanana orange < backup.sql

Or:

mysql -u john --password=banana orange < backup.sql

Or, you can import backup.sql into orange database without a password prompt by setting a password(e.g., banana) to MYSQL_PWD= as shown below. *The doc says MYSQL_PWD is deprecated as of MySQL 8.0; expect it to be removed in a future version of MySQL.:

MYSQL_PWD=banana mysql -u john orange < backup.sql

Or on Windows, you can set the user john and the password banana under [client] in my.ini as shown below. *This way is more secure than the ways above and my answer explains [client] and my answer explains where my.ini is located on Windows:

# "my.ini"

[client]
user="john"
password="banana"

Then, you can import backup.sql into orange database without a password prompt by setting my.ini's location to --defaults-file= or --defaults-extra-file= as shown below:

mysql --defaults-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' orange < backup.sql

Or:

mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini' orange < backup.sql

*Not setting my.ini's location to --defaults-file= or --defaults-extra-file= gets error as shown below:

mysql
ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)

Decastyle

Answer 15

One liner works:

mysqldump -u root -p'password' db > db.sql

Heterochromosome

Answer 16

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

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

Allister

including --password=xxx on the command line will make the password visible to anyone with ability to read proc (or do full ps) -- which is pretty default. – Hickok

Answer 17

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

Zetland

No, it would not be safer, when you add password to commandline it is visible to anyone with ability to read proc (or do full ps) -- which is pretty default. When you add .my.cnf file and set 600 rights it is visible only to YOU. – Indulgent

Answer 18

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!

Toothbrush

nope it does not work, I do not think it understands that -p is the password – Infernal

Not sure how this got 1 vote, I'm downvoting this. As seen in other answers here, there should be no space between the -p and the given password. Also you should redirect output to dumpfile, not specify it as you are doing, or it'll be assumed to be a table name. @buzypi said it best. – Thedrick

it should work (although is insecure in that its pretty easy for other users to see the password) you just need to make sure there is no space between -p and password eg mysqldump -u root -pmypassword – Unwell