Create MySQL user and database from PHP

Question

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

score 4 · Accepted Answer · 2010-05-02 19:28:14Z

4

+100

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.

edited May 2, 2010 at 19:28

answered May 2, 2010 at 16:03

user50049

Sign up to request clarification or add additional context in comments.

8 Comments

robjmills Over a year ago

Just noticed, i copied my code wrong above. This script is actually running on 1 server and trying to install the database on another so its not actually connecting to localhost anyway

robjmills Over a year ago

in case there was an issue with the user i removed the user and tried to recreate them with your code but i'm getting a syntax error?

robjmills Over a year ago

just noticed that this server is running MySQL 4 and it seems the create user command wasnt added until MySQL 5 - maybe the case also for the GRANT command?

user50049 Over a year ago

@snaken - GRANT exists in MySQL 4, Updating my question with a link to that syntax.

robjmills Over a year ago

the create user is apparently MySQL5 only dev.mysql.com/doc/refman/5.0/en/create-user.html - Anyway, the database and user are now being created but for some reason cPanel has not detected that the user has privileges on the database

|

Josh K · Accepted Answer · 2010-04-30 15:35:30Z

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.

answered Apr 30, 2010 at 15:35

Josh K

28.9k20 gold badges89 silver badges132 bronze badges

Comments

Your Common Sense · Accepted Answer · 2010-05-02 16:50:11Z

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.

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

to create a user you can use mysql GRANT query

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

edited May 2, 2010 at 16:50

answered Apr 30, 2010 at 15:37

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Comments

Gazler · Accepted Answer · 2010-04-30 15:37:22Z

-1

You would need to connect to the sql server first:

$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.

answered Apr 30, 2010 at 15:37

Gazler

84.4k18 gold badges284 silver badges245 bronze badges

1 Comment

Your Common Sense Over a year ago

better to remove @. and replace die() with something more reliable too

Collectives™ on Stack Overflow

Create MySQL user and database from PHP

4 Answers 4

8 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related