从PHP创建MySQL用户和数据库

时间：2022-09-27 16:43:00

Is there a way to create a new MySQL database, a new MySQL user and give the new user privileges on the new database all using PHP?

有没有办法创建一个新的MySQL数据库，一个新的MySQL用户，并使用PHP为新数据库赋予新的用户权限？

EDIT - should be pointed out this is run from 1 server to another, so Server A trying to install a DB/user on Server B

编辑 - 应该指出这是从1台服务器运行到另一台服务器，因此服务器A试图在服务器B上安装数据库/用户

i've got this:

我有这个：

$con = mysql_connect("REMOTE.IP.ADDRESS","root","pass");
mysql_query("CREATE DATABASE ".$db."",$con)or die(mysql_error());
mysql_query("GRANT ALL ON ".$db.".* to  ".$user." identified by '".$dbpass."'",$con) or die(mysql_error());

but i'm getting an error on the grant query:

但我在授权查询中收到错误：

"Access denied for user 'root'@'MY.REMOTE.SERVER.HOST.NAME' to database 'dbname'"

4 个解决方案

#1

4  

This answer has been edited several times based on new info provided by the OP

该答案已根据OP提供的新信息进行了多次编辑

Is root actually allowed to connect to the server from the host that you are connecting from? If the error string is returning the canonical name of the server, there's a very good chance that 'localhost' is not pointing to 127.0.0.1 :

root实际上是否允许从您连接的主机连接到服务器？如果错误字符串返回服务器的规范名称，那么'localhost'很可能没有指向127.0.0.1：

"Access denied for user 'root'@'MY.SERVER.HOST.NAME' to database 'dbname'"

“用户'root'@'MY.SERVER.HOST.NAME'拒绝访问数据库'dbname'”

That should echo something like "Access denied for user 'root'@localhost'", not the name of the server.

这应该回应诸如“拒绝用户'root'@ localhost'”之类的内容，而不是服务器的名称。

Try:

尝试：

$con = mysql_connect("127.0.0.1","root","pass");

Edit (After more information provided in comments)

编辑（在评论中提供更多信息后）

If you are connecting from a totally different host, you have to tell MySQL user@remote_hostname_or_ip is allowed to connect, and has appropriate privileges to create a database and users.

如果从一个完全不同的主机连接，则必须告诉MySQL用户@ remote_hostname_or_ip是否允许连接，并具有创建数据库和用户的适当权限。

You can do this rather easily using phpmyadmin (on the MySQL server), or a query like:

您可以使用phpmyadmin（在MySQL服务器上）或类似的查询来轻松完成此操作：

CREATE USER 'root'@'192.168.1.1' IDENTIFIED BY PASSWORD 'secret';

GRANT ALL PRIVILEGES ON * . * TO  'root'@'192.168.1.1' IDENTIFIED BY PASSWORD 'secret' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

I would advise not naming this user 'root' , just create a user with all of the global privileges needed. In the example, I used 192.168.1.1, that could easily be a hostname, just make sure DNS is set up appropriately. Specify the host to match exactly as it appears in logs when you connect to the remote server.

我建议不要将此用户命名为“root”，只需创建一个具有所需全部权限的用户即可。在这个例子中，我使用192.168.1.1，它可以很容易地成为主机名，只需确保DNS设置得当。指定主机与连接到远程服务器时在日志中显示的完全匹配。

You may also want to adjust limits to taste. More information on the CREATE USER syntax can be found here, GRANT here.

您可能还想调整味道的限制。有关CREATE USER语法的更多信息，请参见此处GRANT。

Edit

编辑

If using MySQL 4 - CREATE is not an option. You would just use GRANT (4.1 Docs On User Management)

如果使用MySQL 4 - CREATE不是一个选项。您只需使用GRANT（4.1 Docs On User Management）

Edit

编辑

If using C-Panel, just use the API. While yes, it does have its quirks, its easier to maintain stuff that uses it rather than ad-hoc work arounds. A lot of successful applications use it without issue. Like any other API, you need to stay on top of changes when using it.

如果使用C-Panel，只需使用API​​。虽然是的，它确实有它的怪癖，它更容易维护使用它的东西而不是特别的工作。许多成功的应用程序使用它没有问题。与任何其他API一样，您需要在使用它时始终处于最佳状态。

#2

0  

I believe you'd have to create a connection (with the root user) to an existing database (like mysql) and then run the create query.

我相信您必须创建与现有数据库（如mysql）的连接（与root用户），然后运行create query。

#3

0  

You don't see a database connect in this example for the obvious reason: it is supposed that you learned already that any database action requires connect first.
It's the way the books being written: the things from the previous lessons being omitted in the next ones. Or every chapter will be 2 times bigger and you'll never finish the book.

您没有在此示例中看到数据库连接，原因很明显：假设您已经了解到任何数据库操作都需要先连接。这就是书籍的编写方式：以前课程中的内容在下一课中被省略。或者每章都要大2倍，你永远不会完成这本书。

so yes, you need to connect to the database first, using mysql_connect().

所以，是的，您需要首先使用mysql_connect（）连接到数据库。

to create a user you can use mysql GRANT query

创建用户可以使用mysql GRANT查询

though I am never done it from the script but from the shell only

虽然我从来没有从脚本中完成它，但只从shell中完成

#4

-1  

You would need to connect to the sql server first:

您需要先连接到sql server：

$conn=@mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)
    or die("Err:Conn");

Then the query will execute. A lot of shared hosting servers disable the creation of databases via PHP though.

然后查询将执行。许多共享托管服务器通过PHP禁用了数据库的创建。

#1

4  

This answer has been edited several times based on new info provided by the OP

该答案已根据OP提供的新信息进行了多次编辑

Is root actually allowed to connect to the server from the host that you are connecting from? If the error string is returning the canonical name of the server, there's a very good chance that 'localhost' is not pointing to 127.0.0.1 :

root实际上是否允许从您连接的主机连接到服务器？如果错误字符串返回服务器的规范名称，那么'localhost'很可能没有指向127.0.0.1：

"Access denied for user 'root'@'MY.SERVER.HOST.NAME' to database 'dbname'"

“用户'root'@'MY.SERVER.HOST.NAME'拒绝访问数据库'dbname'”

That should echo something like "Access denied for user 'root'@localhost'", not the name of the server.

这应该回应诸如“拒绝用户'root'@ localhost'”之类的内容，而不是服务器的名称。

Try:

尝试：

$con = mysql_connect("127.0.0.1","root","pass");

Edit (After more information provided in comments)

编辑（在评论中提供更多信息后）

If you are connecting from a totally different host, you have to tell MySQL user@remote_hostname_or_ip is allowed to connect, and has appropriate privileges to create a database and users.

如果从一个完全不同的主机连接，则必须告诉MySQL用户@ remote_hostname_or_ip是否允许连接，并具有创建数据库和用户的适当权限。

You can do this rather easily using phpmyadmin (on the MySQL server), or a query like:

您可以使用phpmyadmin（在MySQL服务器上）或类似的查询来轻松完成此操作：

CREATE USER 'root'@'192.168.1.1' IDENTIFIED BY PASSWORD 'secret';

GRANT ALL PRIVILEGES ON * . * TO  'root'@'192.168.1.1' IDENTIFIED BY PASSWORD 'secret' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

I would advise not naming this user 'root' , just create a user with all of the global privileges needed. In the example, I used 192.168.1.1, that could easily be a hostname, just make sure DNS is set up appropriately. Specify the host to match exactly as it appears in logs when you connect to the remote server.

我建议不要将此用户命名为“root”，只需创建一个具有所需全部权限的用户即可。在这个例子中，我使用192.168.1.1，它可以很容易地成为主机名，只需确保DNS设置得当。指定主机与连接到远程服务器时在日志中显示的完全匹配。

You may also want to adjust limits to taste. More information on the CREATE USER syntax can be found here, GRANT here.

您可能还想调整味道的限制。有关CREATE USER语法的更多信息，请参见此处GRANT。

Edit

编辑

If using MySQL 4 - CREATE is not an option. You would just use GRANT (4.1 Docs On User Management)

如果使用MySQL 4 - CREATE不是一个选项。您只需使用GRANT（4.1 Docs On User Management）

Edit

编辑

If using C-Panel, just use the API. While yes, it does have its quirks, its easier to maintain stuff that uses it rather than ad-hoc work arounds. A lot of successful applications use it without issue. Like any other API, you need to stay on top of changes when using it.

如果使用C-Panel，只需使用API​​。虽然是的，它确实有它的怪癖，它更容易维护使用它的东西而不是特别的工作。许多成功的应用程序使用它没有问题。与任何其他API一样，您需要在使用它时始终处于最佳状态。

#2

0  

I believe you'd have to create a connection (with the root user) to an existing database (like mysql) and then run the create query.

我相信您必须创建与现有数据库（如mysql）的连接（与root用户），然后运行create query。

#3

0  

You don't see a database connect in this example for the obvious reason: it is supposed that you learned already that any database action requires connect first.
It's the way the books being written: the things from the previous lessons being omitted in the next ones. Or every chapter will be 2 times bigger and you'll never finish the book.

您没有在此示例中看到数据库连接，原因很明显：假设您已经了解到任何数据库操作都需要先连接。这就是书籍的编写方式：以前课程中的内容在下一课中被省略。或者每章都要大2倍，你永远不会完成这本书。

so yes, you need to connect to the database first, using mysql_connect().

所以，是的，您需要首先使用mysql_connect（）连接到数据库。

to create a user you can use mysql GRANT query

创建用户可以使用mysql GRANT查询

though I am never done it from the script but from the shell only

虽然我从来没有从脚本中完成它，但只从shell中完成

#4

-1  

You would need to connect to the sql server first:

您需要先连接到sql server：

$conn=@mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)
    or die("Err:Conn");

Then the query will execute. A lot of shared hosting servers disable the creation of databases via PHP though.

然后查询将执行。许多共享托管服务器通过PHP禁用了数据库的创建。

标签：

相关文章