如何创建一个没有密码的 MySQL 用户 - 远程登录需要 - 在 bash 插入脚本中使用？

How to create a MySQL user without password - needed for remote login - to be used in bash insert script?

提问by Chirayu

My requirement is to create a user for remote login but without a password. At my remote space I use a bash script to do inserts, which is something like:

我的要求是创建一个用于远程登录但没有密码的用户。在我的远程空间中，我使用 bash 脚本进行插入，类似于：

for i in {1..5000}; do
  mysql -h 11.40.3.169 -uUser -pPass -DDatabaseName <<<
    "insert into DatabaseTableName values('$i','dummy_$i');" &
  echo -n "$i  "
  sleep 1
  date
done

The problem is that each insert is taking almost 4 seconds, and I can not pinpoint the problem to anything but authentication at every insert. So, if I could create a user in MySQL with minimal authentication involved...Something like:

问题是每个插入都需要将近 4 秒，而且我无法将问题确定为每次插入时的身份验证。所以，如果我可以在 MySQL 中创建一个只涉及最少身份验证的用户......就像：

# I'm trying to remove this password
GRANT ALL PRIVILEGES TO 'user'@'%' IDENTIFIED BY 'password';

...Anything you can suggest.

...任何你可以提出的建议。

回答by Quassnoi

Just remove the IDENTIFIED BYpart:

只需删除IDENTIFIED BY部分：

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%'

Note that remote login from anywhere without a password is a very insecure thing. You better limit the allowed IPrange for this user:

请注意，在没有密码的情况下从任何地方远程登录是一件非常不安全的事情。您最好限制IP此用户的允许范围：

GRANT ALL PRIVILEGES  ON *.* TO 'user'@'allowed_remote_machine'

回答by cbz

You can do this by creating a user with a password and then placing a .my.cnf file in the home directory of the account which runs the bash script containing the following:

您可以通过创建一个具有密码的用户，然后将 .my.cnf 文件放在运行包含以下内容的 bash 脚本的帐户的主目录中来完成此操作：

[mysql]
user=user
password=pass
[mysqladmin]
user=user
password=pass

This might be better than creating a user with no password.

这可能比创建没有密码的用户要好。

回答by jim sims

I think your problem lies in the fact that you are starting the mysql client for each insert. You should be doing your inserts from a php, java, etc program - not from a shell script.

我认为您的问题在于您正在为每个插入启动 mysql 客户端。您应该从 php、java 等程序中进行插入，而不是从 shell 脚本中插入。

The startup time of the client (and connection to the host) is killing you. I routinely do 1000s of inserts per minute from a php or java program to a MySQL database with millions of records on a small (CPU/memory) machine.

客户端的启动时间（以及与主机的连接）正在扼杀您。我通常每分钟执行 1000 次从 php 或 java 程序到 MySQL 数据库的插入操作，在小型（CPU/内存）机器上有数百万条记录。

回答by PachinSV

It's not so good idea to have a user without password and all privileges. I suggest you to create a user without password but just with some privileges (insert to specific table or specific database).

拥有一个没有密码和所有权限的用户并不是一个好主意。我建议您创建一个没有密码但只有一些权限的用户（插入到特定表或特定数据库）。

回答by Neil Davis

First off, using a client cnf file on the remote machine running the script wont speed this up. MySQL client is still sending logon information and logging in for each insert, it's just reading.a file for uid/pw instead of using cmd line arguments. AFAIK The network and authentication overhead are identical. Even the network packet contents will be the same.

首先，在运行脚本的远程机器上使用客户端 cnf 文件不会加快速度。MySQL 客户端仍在发送登录信息并为每个插入登录，它只是读取 uid/pw 的文件，而不是使用 cmd 行参数。AFAIK 网络和身份验证开销是相同的。甚至网络数据包的内容也是一样的。

You should still use a cnf file..

您仍然应该使用 cnf 文件..

The way to.improve performance is to do multi-line linserts:

提高性能的方法是做多行 linserts：

MySQL --defaults-file=/some/uid/pw/etc/.client.cnf -e \ "Insert into tbl_name ('fld1','fld2') values ('r1-fld1','r1-fld2'), ('r2-fld2','r2-fld2'), ...and so on (up to max_allowed_packet_size) ('r500-fld2','r500-fld2');" Or READ DATA INFILE on server side after shipping over the data file

MySQL --defaults-file=/some/uid/pw/etc/.client.cnf -e \ "Insert into tbl_name ('fld1','fld2') values ('r1-fld1','r1-fld2'), ('r2-fld2','r2-fld2'), ...and so on (up to max_allowed_packet_size) ('r500-fld2','r500-fld2');" 或者在传送数据文件后在服务器端读取数据输入文件