MySQL Mysql用户创建脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9529651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql user creation script
提问by ling
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 用户创建过程。我想创建一个包含 mysql 用户创建语句的临时文件，然后我会这样称呼它：
mysql -u root -proot < temp
mysql -u root -proot <临时
But I'm stuck with mysql syntax : here's the content of my temp file :
但我坚持使用 mysql 语法：这是我的临时文件的内容：
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 )
（密码哈希应该是一个 41 位的十六进制数）
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)
没有像我期望的那样解释。即使我删除了 @password 标签周围的单引号，我仍然有错误（语法错误）
How can I make this work ?
我怎样才能使这项工作？
回答by eyecatchUp
Just to answer why the error occurs and to show the differnce:
只是为了回答错误发生的原因并显示差异：
A) Expects
@passwordto be a hash string 1value:A) 期望@password是一个哈希字符串1值：GRANT ALL PRIVILEGES
ON `mydb` . * TO 'username'@'localhost' IDENTIFIED
BY
PASSWORD '@password';
Note the use of the PASSWORDkeyword!
注意PASSWORD关键字的使用！
B) Expects
@passwordto be a clear-text stringvalue:B) 期望@password是一个明文字符串值：GRANT ALL PRIVILEGES
ON `mydb` . * TO 'username'@'localhost' IDENTIFIED
BY
'@password';
Note the missingPASSWORDkeyword!
注意缺少的PASSWORD关键字！
1Where "hash string" is the result of
SELECT PASSWORD('clearTextPasswd');- see Snowman's answerfor an example.1其中“哈希字符串”是结果SELECT PASSWORD('clearTextPasswd');- 请参阅雪人的答案以获取示例。回答by Devart
If you do not want to store password in clear text, then save it in hashed format -
如果您不想以明文形式存储密码，则将其保存为散列格式 -
GRANT ALL PRIVILEGES ON mytestdatabase.* TO 'mytestdatabase'@'localhost'
IDENTIFIED BY PASSWORD '*7560636C2922C05954FE6A2446AA00C84708B57B';
Where hashed password is a result of this query -
散列密码是此查询的结果 -
SELECT PASSWORD('my password');
回答by abhishek
I have also got same problem, it is resolved just by setting the password by mentioned query below.
我也遇到了同样的问题，只需通过下面提到的查询设置密码即可解决。
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('MyNewPass');
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('MyNewPass');
回答by Snowman
Take a look at the following url and this will help you to fix the issue:
查看以下网址，这将帮助您解决问题：
http://linuxadministrator.pro/blog/?p=147
http://linuxadministrator.pro/blog/?p=147
mysql> select password('12345');
+-------------------------+
| password('123456') |
+-------------------------+
| 2ff898e158cd0311 |
+-------------------------+
1 row in set (0.00 sec)
mysql> create user test identified by password '2ff898e158cd0311';
Query OK, 0 rows affected (0.00 sec)
回答by alfasin
try:
尝试：
GRANT ALL PRIVILEGES ON mytestdatabase.*
TO mytestdatabase@localhost IDENTIFIED BY 'PASSWORD';
where PASSWORD is your password (in quotes).
其中 PASSWORD 是您的密码（在引号中）。
回答by jamesTheProgrammer
I opened phpMYAdmin and went to the the users tab, found the user i was trying to connect with clicked Edit Privileges, scrolled down to the Change Login Information / Copy User section and choose Any hostfor the Host option, it was set to local. When I clicked the Go button, it generated a script similar to the ones listed here.
我打开 phpMYAdmin 并转到用户选项卡，找到我尝试连接的用户，单击“编辑权限”，向下滚动到“更改登录信息/复制用户”部分并为“主机”选项选择“任何主机”，它被设置为本地。当我单击 Go 按钮时，它生成了一个类似于此处列出的脚本。
GRANT ALL PRIVILEGES ON * . * TO 'james'@'%' IDENTIFIED BY PASSWORD '*780E1D13F1C7713F341A117499C6D8644464C4CF' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
回答by Krishna
Well hat is error bcoz you have wrote the wrong way just you have wrote,
好吧，这是错误，因为你写错了，只是你写的，
GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' IDENTIFIED BY PASSWORD '@password';
and the true syntax is follows. look here.
真正的语法如下。看这里。
GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost' IDENTIFIED BY '@password';
because your password is causing that error. it must be in as shown.
因为您的密码导致了该错误。它必须如图所示。
