MySQL cron启动的mysqldump和密码安全

Mysqldump launched by cron and password security

提问by kheraud

I wrote a script to backup my MySQL databases using:

我写了一个脚本来备份我的 MySQL 数据库：

mysqldump --opt --all-databases -u user -pmypassword > myDump.sql

A cron launches it every night and scp the result to another server. mypasswordappears in clear in my script, everyone can see it with the appropriate rights. I have been told about /proc issues too (where the cmd run can be seen).

一个 cron 每晚启动它并将结果 scp 到另一台服务器。 mypassword清晰地出现在我的脚本中，每个人都可以用适当的权限看到它。我也被告知 /proc 问题（可以看到 cmd 运行的地方）。

MySQL documentation says:

MySQL 文档说：

Specifying a password on the command line should be considered insecure. See Section 7.6, "Keeping Your Password Secure".

在命令行上指定密码应该被认为是不安全的。请参阅第 7.6 节“保持密码安全”。

I have not found this magic 7.6 sections anywhere.

我还没有在任何地方找到这个神奇的 7.6 部分。

What is the good practice to deal with automatic mysqldump and password security?

处理自动 mysqldump 和密码安全的好做法是什么？

回答by Sahil Muthoo

Quoting the MySQL docs(http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html):

引用 MySQL 文档（http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html）：

Store your password in an option file. For example, on Unix you can list your password in the [client] section of the .my.cnf file in your home directory:

[client]
password=your_pass

To keep the password safe, the file should not be accessible to anyone but yourself. To ensure this, set the file access mode to 400 or 600. For example:

shell> chmod 600 .my.cnf

To name from the command line a specific option file containing the password, use the --defaults-file=file_nameoption, where file_nameis the full path name to the file.

将您的密码存储在选项文件中。例如，在 Unix 上，您可以在主目录中 .my.cnf 文件的 [client] 部分列出您的密码：

[client]
password=your_pass

为确保密码安全，除了您自己之外，任何人都不应访问该文件。为确保这一点，请将文件访问模式设置为 400 或 600。例如：

shell> chmod 600 .my.cnf

要从命令行命名包含密码的特定选项文件，请使用该--defaults-file=file_name选项，其中file_name是文件的完整路径名。

回答by troseman

to add to Sahil's answer above, use --defaults-extra-file

要添加到上面 Sahil 的答案，请使用 --defaults-extra-file

--defaults-extra-file is used to tell a program to read a single specific option file in addition to the standard option files.

--defaults-extra-file 用于告诉程序除了标准选项文件之外还读取单个特定选项文件。

whereas --defaults-file is read instead of the default my.cnf file.

而读取 --defaults-file 而不是默认的 my.cnf 文件。

回答by blendenzo

The accepted answer stores the password in a plain text file, which could be read by anyone with administrative (root) access. If your database is in a shared hosting environment, this is undesirable.

接受的答案将密码存储在纯文本文件中，任何具有管理（root）访问权限的人都可以读取该文件。如果您的数据库在共享托管环境中，这是不可取的。

A better option would be to use mysql_config_editorto create an encrypted login path named mysqldump. According to the MySQL documentation:

更好的选择是使用mysql_config_editor创建一个名为 的加密登录路径mysqldump。根据MySQL 文档：

mysql_config_editorencrypts the .mylogin.cnf file so it cannot be read as cleartext, and its contents when decrypted by client programs are used only in memory. In this way, passwords can be stored in a file in non-cleartext format and used later without ever needing to be exposed on the command line or in an environment variable.

mysql_config_editor加密 .mylogin.cnf 文件，因此它不能作为明文读取，并且其内容在被客户端程序解密时仅在内存中使用。通过这种方式，密码可以以非明文格式存储在文件中并在以后使用，而无需在命令行或环境变量中公开。

The following command will create your mysqldumplogin path:

以下命令将创建您的mysqldump登录路径：

mysql_config_editor set --login-path=mysqldump --host=your_hostname --user=your_username --password

You will be prompted to enter your password, and the login path you created will be stored in encrypted format. mysqldumpwill automatically use this login path whenever you call it in the future, unless you specify a different login path with the --login-pathcommand line option.

系统会提示您输入密码，您创建的登录路径将以加密格式存储。mysqldump以后每次调用时都会自动使用此登录路径，除非您使用--login-path命令行选项指定不同的登录路径。

Here is how you would invoke mysqldumpafter creating an encrypted login path:

以下是mysqldump创建加密登录路径后的调用方式：

mysqldump database_name > output_file

回答by Kuldeep Dangi

All answers here are in pieces so sharing a complete commandwhich will do the required and must be used if database are heavy in size, --single-transactionand --lock-tablesare very important here

这里的所有答案都是分段的，因此共享一个完整的命令，该命令将执行所需的操作，并且如果数据库很大，则必须使用该命令，--single-transaction并且--lock-tables在这里非常重要

mysqldump --defaults-extra-file=/home/dangi/.my.cnf -u root --single-transaction --quick --lock-tables=false --all-databases (or) DATABASE | gzip > OUTPUT.gz;

Note: Answer is in add of Avibodha and sahil answer, they have already made the point. I am just putting their answer in single piece of code with important measure should be taken at time of backing up live database

注：答案是在无明和无量的答案中添加的，他们已经指出了这一点。我只是将他们的答案放在一段代码中important measure should be taken at time of backing up live database

回答by Zinan Xing

The following method works for me on a Windows machine, if you have 2 versions of MySQL installed, and you are not sure which my.ini is used when you run mysqldump, this will also help:

以下方法适用于我在 Windows 机器上，如果您安装了 2 个版本的 MySQL，并且您不确定在运行 mysqldump 时使用的是哪个 my.ini，这也将有所帮助：

1, C:\ProgramData\MySQL\MySQL Server 5.6\my.ini, fine [client], replace it to:

1、C:\ProgramData\MySQL\MySQL Server 5.6\my.ini，好的【客户端】，替换为：

[client]
user=my_user
password=my_password

2, Use this command:

2、使用这个命令：

C:\Program Files\MySQL Server 5.6\bin>mysqldump --default-extra-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" -u my_user db_to_export > db_to_export.sql

C:\Program Files\MySQL Server 5.6\bin>mysqldump --default-extra-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" -u my_user db_to_export > db_to_export.sql

回答by Jesse

Check out Keeping Passwords Securefor a good answer. You can store your password in the my.cnf file changing the permissions on that file to keep the password secure.

查看保持密码安全以获得一个好的答案。您可以将密码存储在 my.cnf 文件中，更改该文件的权限以确保密码安全。

You can also check the last comment on this page too:

您也可以查看此页面上的最后一条评论：

MYSQL_PWD="tinkerbell" mysqldump -ubackup --all-databases > dump.sql

MYSQL_PWD="tinkerbell" mysqldump -ubackup --all-databases > dump.sql