从PHP创建MySQL用户和数据库 [英] Create MySQL user and database from PHP

问题描述

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

EDIT - 应该指出这是从一个服务器运行到另一个服务器，因此服务器A试图在服务器B上安装一个DB /用户

得到这个：

  $ con = mysql_connect（REMOTE.IP.ADDRESS，root，pass）; 
 mysql_query（CREATE DATABASE。$ db。，$ con）或die（mysql_error（））; 
 mysql_query（GRANT ALL ON。$ db。。* to。$ user。由$ dbpass。'，$ con标识）或die（mysql_error 
  

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

 用户'root'@'MY.REMOTE.SERVER.HOST.NAME拒绝访问数据库'dbname'
  
 p> 
 
解决方案
 此答案已根据OP提供的新信息进行了多次编辑 
 
 
 
实际上是否允许root用户从您连接的主机连接到服务器？如果错误字符串返回服务器的规范名称，localhost没有指向127.0.0.1的机会很大：
 
 
拒绝用户的访问
'root'@'MY.SERVER.HOST.NAME'到
数据库'dbname'
 
 
这应该回应类似访问被拒绝用户'root'@ localhost'，而不是服务器的名称。
 
 
 
 / p> 
 
 
  $ con = mysql_connect（127.0.0.1，root，pass）; 
  
 编辑（在注释中提供更多信息后）
 
 
 
如果你从一个完全不同的主机连接，你必须告诉MySQL  user @ remote_hostname_or_ip 是允许连接，并有适当的权限
 
 
 
您可以使用phpmyadmin（在MySQL服务器上），或者像下面这样的查询轻松地完成这些操作：
  CREATE USER'root'@'192.168.1.1'IDENTIFIED BY PASSWORD'secret'; 
 
授予所有特权*。 * TO'root'@'192.168.1.1'密码由密码'secret'与授予选项MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; 
  
我建议不要命名这个用户的root，只要创建一个拥有所有全局权限的用户需要。在示例中，我使用192.168.1.1，这可以很容易是一个主机名，只是确保DNS设置正确。 
 
 
 
您可能还需要调整限制以适应您的偏好。有关 CREATE USER 语法的更多信息，可以是在此处找到， GRANT  此处。
 
 
 
 编辑 
 
如果使用MySQL 4  -  CREATE不是一个选项。您只需使用GRANT（ 4.1文档用户管理）
 
 
 
 编辑 
 
 
 
如果使用C-Panel， a href =http://forums.cpanel.net/f42/xml-api-php-class-version-1-0-a-136449.html =nofollow noreferrer>使用API​​  。虽然是，它确实有它的怪癖，它更容易维护的东西，使用它，而不是临时工作。很多成功的应用程序使用它没有问题。像任何其他API一样，您需要在使用时保持更改。
 
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?

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

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'"

解决方案
This answer has been edited several times based on new info provided by the 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 :

  
"Access denied for user
  'root'@'MY.SERVER.HOST.NAME' to
  database 'dbname'"
That should echo something like "Access denied for user 'root'@localhost'", not the name of the server.

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.

You can do this rather easily using phpmyadmin (on the MySQL server), or a query like:
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.

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

Edit

If using MySQL 4 - CREATE is not an option. You would just use 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.

                        
这篇关于从PHP创建MySQL用户和数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！