Mysql用户创建脚本 [英] Mysql user creation script

问题描述

我试图自动化MySQL用户创建过程。
我想到创建一个临时文件，它将包含mysql用户创建语句，
然后我会像这样调用：

mysql -u根 - temp

但是我遇到了mysql语法：
这里是我的临时文件的内容：

< hr>

  DROP DATABASE IF EXISTS mytestdatabase; 
 CREATE DATABASE mytestdatabase; 
 SELECT @password：=my password; 
 DELETE FROM mysql.user WHERE Host ='localhost'AND User ='mytestdatabase'; 
授予所有权限在mytestdatabase。* TO'mytestdatabase'@'localhost'IDENTIFIED BY PASSWORD'@password'; 
 FLUSH PRIVILEGES; 
  

---

但行

 授予所有权限在mytestdatabase。* TO'mytestdatabase'@'localhost'IDENTIFIED BY PASSWORD'@password'; 
  

（密码散列应为41位十六进制数字）

不会被解释为我预期的。即使我删除@password标签周围的单引号仍然有错误（语法错误）

如何使这项工作？

$只是为了回答错误发生的原因并显示差异：

A）Expects @password 为哈希字符串 ¹ value：

 授予所有特权
 ON`mydb`。 * TO'username'@'localhost'IDENTIFIED 
 BY 
 PASSWORD'@password'; 
  

^{注意使用 PASSWORD 关键字！}

---

B）预期 @password em> clear-text string value：

 授予所有权限
 ON`mydb `。 * TO'username'@'localhost'IDENTIFIED 
 BY 
'@password'; 
  

^{注意缺失 PASSWORD 关键字！}

SELECT PASSWORD（'clearTextPasswd'）; 的结果 - 请参阅雪人答案< a>为例。

I'm trying to automate MySQL user creation procedure. I thought of creating a temp file that would contain mysql user creation statements, then I would have call it like this :

mysql -u root -proot < temp

But I'm stuck with mysql syntax : here's the content of my temp file :

---

DROP DATABASE IF EXISTS mytestdatabase;
CREATE DATABASE mytestdatabase;
SELECT @password:="my password";
DELETE FROM mysql.user WHERE Host='localhost' AND User='mytestdatabase';
GRANT ALL PRIVILEGES ON mytestdatabase.* TO 'mytestdatabase'@'localhost' IDENTIFIED BY PASSWORD '@password';
FLUSH PRIVILEGES;

---

But the line

GRANT ALL PRIVILEGES ON mytestdatabase.* TO 'mytestdatabase'@'localhost' IDENTIFIED BY PASSWORD '@password';

(Password hash should be a 41-digit hexadecimal number )

is not interpreted as I would expect it to be. Even if I remove single quotes around the @password tag I still have errors (syntax error)

How can I make this work ?

解决方案

Just to answer why the error occurs and to show the differnce:

---

A) Expects @password to be a hash string ¹ value:

GRANT ALL PRIVILEGES 
    ON `mydb` . * TO 'username'@'localhost' IDENTIFIED 
BY
PASSWORD '@password';

^{Note the use of the PASSWORD keyword!}

---

B) Expects @password to be a clear-text string value:

GRANT ALL PRIVILEGES 
    ON `mydb` . * TO 'username'@'localhost' IDENTIFIED 
BY
'@password';

^{Note the missing PASSWORD keyword!}

---

¹ Where "hash string" is the result of SELECT PASSWORD('clearTextPasswd'); - see Snowman's answer for an example.

这篇关于Mysql用户创建脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！