如何在shell脚本中自动登录mysql？

I have a mysql server with empty root password. I want to execute some sql statements in shell scripts without specifying the password like this:

我有一个空root密码的mysql服务器。我想在shell脚本中执行一些sql语句而不指定如下的密码：

config.sh:

config.sh：

MYSQL_ROOT="root"
MYSQL_PASS=""

mysql.sh

mysql.sh

source config.sh
mysql -u$MYSQL_ROOT -p$MYSQL_PASS -e "SHOW DATABASES"

How do I auto execute that sql in shell with the -p option and an empty password?

如何使用-p选项和空密码在shell中自动执行该sql？

5 个解决方案

#1

Try this:

尝试这个：

if [ $MYSQL_PASS ]
then
  mysql -u "$MYSQL_ROOT" -p"$MYSQL_PASS" -e "SHOW DATABASES"
else
  mysql -u "$MYSQL_ROOT" -e "SHOW DATABASES"
fi

#2

Alternative ways to write these options.

You can write

你可以写

mysql -u "$MYSQL_ROOT" -p "$MYSQL_PASS" -e "SHOW DATABASES"

to pass empty strings as separate arguments. Your comment below indicates that the client will still ask for a password, though. Probably it interprets the empty argument as a database name and not as the password. So you could try the following instead:

将空字符串作为单独的参数传递。您的下面的评论表明客户端仍然会要求输入密码。可能它将空参数解释为数据库名称而不是密码。所以你可以尝试以下代码：

mysql --user="$MYSQL_ROOT" --password="$MYSQL_PASS" -e "SHOW DATABASES"

At least on my system, however, setting MYSQL_PASSto "" in this case leads the client attempting to connect without any password at all. So if there is a difference between an empty password and no password at all, then I don't know a way to pass the former on the command line.

但是，至少在我的系统上，在这种情况下将MYSQL_PASS设置为“”会导致客户端尝试连接时根本没有任何密码。因此，如果空密码和完全没有密码之间存在差异，那么我不知道在命令行上传递前者的方法。

.my.cnf file

But even if there is a way, I'd still suggest you use a ~/.my.cnf file instead. Arguments on the command line are likely included in a process listing generated by ps -A -ocmd, so other users can see them. The .my.cnf file, on the other hand, can (and should) be made readable only by you (using chmod 0600 ~/.my.cnf), and will be used automatically. Have that file include the following lines:

但即使有办法，我仍然建议您使用〜/ .my.cnf文件。命令行上的参数可能包含在ps -A -ocmd生成的进程列表中，因此其他用户可以看到它们。另一方面，.my.cnf文件可以（并且应该）只能由您（使用chmod 0600~ / .my.cnf）读取，并将自动使用。该文件包含以下行：

[client]
user=root
password=

Then a simple mysql -e "SHOW DATABASES" will suffice, as the client will obtain its credentials from that file.

然后一个简单的mysql -e“SHOW DATABASES”就足够了，因为客户端将从该文件中获取其凭据。

See 6.1.2.1. End-User Guidelines for Password Security for the various ways in which you can provide a password, and their respective benefits and drawbacks. See 4.2.3.3. Using Option Files for general information on this .my.cnf file

见6.1.2.1。密码安全最终用户指南，提供密码的各种方式，以及各自的优点和缺点。见4.2.3.3。使用选项文件获取有关此.my.cnf文件的一般信息

#3

As MvG suggested (recommended in the MySQL manual 4.2.2 connecting and 6.1.2.1 security guidelines) you should use a file. The password on the command line may be unsafe since ps may show it to other users. The file does not have to be .my.cnf, can be an ad-hoc option file for your script in a temporary file:

正如MvG建议的（在MySQL手册4.2.2连接和6.1.2.1安全指南中推荐），你应该使用一个文件。命令行上的密码可能不安全，因为ps可能会向其他用户显示密码。该文件不必是.my.cnf，可以是临时文件中脚本的临时选项文件：

OPTFILE="$(mktemp -q --tmpdir "${inname}.XXXXXX")$"
trap 'rm -f "$OPTFILE"' EXIT
chmod 0600 "$OPTFILE"
cat >"$OPTFILE" <<EOF
[client]
password="${MYSQL_PASS}"
EOF
mysql --user="$MYSQL_ROOT" --defaults-extra-file="$OPTFILE" -e "SHOW DATABASES"

The first lines create a safe temp file, then put the options then use it. trap will protect you form OPTFILE lying around in case of interrupts.

第一行创建一个安全的临时文件，然后放入选项然后使用它。陷阱将保护你在中断的情况下形成OPTFILE。

#4

Here is a little bash script you can use to get a shell really quickly

It opens up a shell so you can manually execute queries. Very convenient.

它打开了一个shell，因此您可以手动执行查询。很方便。

  #!/bin/bash
  DB_USER='your_db_username'
  DB_PASS='your_password'
  DB='database_name'
  echo 'logging into db $DB as $DB_USER'
  mysql -u "$DB_USER" --password="$DB_PASS" --database="$DB"

#5

In Debian GNU/Linux distributions there is a file called /etc/mysql/debian.cnf. You have not to create another file with MySQL root credentials.

在Debian GNU / Linux发行版中，有一个名为/etc/mysql/debian.cnf的文件。您不必使用MySQL根凭据创建另一个文件。

# Put in your .bashrc
alias mysql='mysql --defaults-file=/etc/mysql/debian.cnf'
alias mysqldump='mysqldump --defaults-file=/etc/mysql/debian.cnf'