Create MySQL user and database from PHP

Question

Hi,

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.