MySQL 如何在没有密码提示的情况下执行 mysqldump？

How to perform a mysqldump without a password prompt?

提问by Prakash Raman

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

我想知道在不提示输入密码的情况下执行数据库 mysqldump 的命令。

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.

原因：我想运行一个 cron 作业，它每天对数据库进行一次 mysqldump。因此，我将无法在出现提示时插入密码。

How could I solve this ?

我怎么能解决这个问题？

回答by Frankline

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

由于您使用的是 Ubuntu，您需要做的只是在您的主目录中添加一个文件，它将禁用 mysqldump 密码提示。这是通过创建文件来完成的~/.my.cnf（权限需要为 600）。

Add this to the .my.cnf file

将此添加到 .my.cnf 文件

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

这使您可以作为需要密码的 MySQL 用户进行连接，而无需实际输入密码。您甚至不需要 -p 或 --password。

Very handy for scripting mysql & mysqldump commands.

对于编写 mysql 和 mysqldump 命令的脚本非常方便。

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

但请注意，它本质上是不安全的，因为在转储运行时，系统上的任何其他用户都可以使用简单的ps ax命令查看整个命令（包括密码）。

回答by Nelu

Adding to @Frankline's answer:

添加到@Frankline 的答案：

The -poption must be excludedfrom the command in order to use the password in the config file.

必须从命令中排除该-p选项才能使用配置文件中的密码。

Correct:
mysqldump –u my_username my_db > my_db.sql

正确的：
mysqldump –u my_username my_db > my_db.sql

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

错误的：
mysqldump –u my_username -p my_db > my_db.sql

---

---

.my.cnfcan omit the username.

.my.cnf可以省略用户名。

[mysqldump]
password=my_password

---

---

If your .my.cnffile is not in a default locationand mysqldumpdoesn't see it, specify it using --defaults-file.

如果您的.my.cnf文件不在默认位置并且mysqldump看不到它，请使用--defaults-file.

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

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

回答by chutz

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

一些答案提到将密码放在配置文件中。

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

或者，从您的脚本中，您可以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 downsideto this method.

有没有缺点给此方法。

The password is notvisible 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.

该密码对系统上的其他用户不可见（如果它在命令行上则可见）。环境变量仅对运行 mysql 命令的用户和 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.

任何可以阅读脚本本身的人都可以看到密码，因此请确保脚本本身受到保护。这与保护配置文件没有任何区别。如果您想让脚本公开可读（export MYSQL_PWD=$(cat /root/mysql_password)例如），您仍然可以从单独的文件中获取密码。导出变量仍然比构建配置文件更容易。

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

回答by FreeSoftwareServers

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

要使用 OS 内任何位置的文件，请使用--defaults-extra-file例如：

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

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

注意：.sqlpwd只是一个示例文件名。你可以使用任何你想要的。

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

注意：MySQL 会自动检查~/.my.cnf哪些可以代替--defaults-extra-file

If your using CRON like me, try this!

如果你像我一样使用 CRON，试试这个！

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

.sqlpwdcontents:

.sqlpwd内容：

[mysqldump]
user=username
password=password

Other examples to pass in .cnfor .sqlpwd

传入.cnf或的其他示例.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.

例如，如果您想自动登录数据库，则需要该[mysql]条目。

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

您也可以只将密码放入其中.sqlpwd并通过脚本/cli 传递用户名。我不确定这是否会提高安全性，这将是一个完全不同的问题。

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.

注意： -p 和密码之间没有空格。

Eg -pPassWordis correct while -p Passwordis incorrect.

例如-pPassWord，正确而-p Password错误。

回答by Tarek Kalaji

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

是啊这是很容易....只是在一个神奇的命令行中没有更多的

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

and done :)

并做了 ：）

回答by Michael Johansen

For me, using MariaDB I had to do this: Add the file ~/.my.cnfand 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:

对我来说，使用 MariaDB 我必须这样做：添加文件~/.my.cnf并通过执行chmod 600 ~/.my.cnf. 然后将您的凭据添加到文件中。我错过的神奇部分是密码需要在客户端块下（参考：docs），如下所示：

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

如果您碰巧来到这里寻找如何使用 MariaDB 进行 mysqldump。将密码放在 [client] 块下，然后将用户放在 [mysqldump] 块下。

回答by pcmanprogrammeur

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

这是脚本 /bin/sh 中的 Docker 解决方案：

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_PASSWORDis set in your container.

替换[MYSQL_CONTAINER_NAME]并确保MYSQL_ROOT_PASSWORD在您的容器中设置了环境变量。

Hope it will help you like it could help me !

希望它能帮助你喜欢它可以帮助我！

回答by hawx

I have the following.

我有以下内容。

/etc/mysqlpwd

/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]

回答by Joe Velez

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

在 5.1.51 上运行的 --password="" 对我有用吗

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

回答by kante

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

绝对我认为将完整的 cmd 行放在根 crontab 中会更好，更安全，并带有凭据。至少 crontab 编辑被限制（可读）给已经知道密码的人......所以不用担心以纯文本显示......

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

如果需要的不仅仅是一个简单的 mysqldump ......只需放置一个 bash 脚本，该脚本接受凭据作为参数并在内部执行所有便利......

The bas file in simple

简单的bas文件

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

In the Crontab:

在 Crontab 中：

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