How to create databases on mySQL using script (PHP, etc)

Question

Hi, I am thinking of running a hosted service using Amazon Services (PHP + mySQL). What I like to do is have a site where someone registers, pays PayPal and returns to site where they will get an automated information to their mySQL account. So this mySQL account would only be used by them. They can create tables, etc.

Some questions are:

1) Is there a way to create a new mySQL account and table using a script? (PHP?)
2) Is there any open-source scripts out there that already does this that I can take apart and use?
3) If the above is not a good idea, any other suggestions.

Basically I want them to put files on their local web hosting (Hostgator, Dreamhost, etc) but be able to grab data from my database in Amazon. So I want each user to be limited to their own database and not mess with others. And they need ability to update tables.

Any suggestions & thoughts appreciated!

Answer 1

This may be a job that phpMyAdmin can help with?

Answer 2

CREATE DATABASE, CREAT USER and GRANT commands needs a lot of privileges. You should be wary of using such an account in a web script. For one thing if a user is able to figure out your paypal call back URL, there is a possibility that he can post data directly to that url and cause mischief.

What I would suggest is for you to enter the details of the database and user accounts that need to be created into a queue (maybe held in a mysql table), and to run a scheduled task (maybe cron) to create them in the background. When you do that the script does not need to have access to an account with a high level of privileges.

As to how exactly a database and user account can be created; well you can treat them like any other queries.

CREATE USER: http://dev.mysql.com/doc/refman/5.1/en/create-user.html

CREATE DATABASE: http://dev.mysql.com/doc/refman/5.1/en/create-user.html

GRANT: http://dev.mysql.com/doc/refman/5.1/en/grant.html