mySQL - databases and VM - some advice please

[Weenis]

So I'm looking to develop a business with a compatriot of mine.

Essentially the structure will be something like this for our product:

We'll have multiple customers being separate businesses, we want to use vagrant to easily set up and tear down VMs running ubuntu + apache + mysql + php5.5. Vagrant was a bit of a bitch to set up (I'm newish to unix, completely new to apache/mysql/php.. but not OOP nor web design). Once I got everything set up the syncing of folders to my windows machine was kinda wonky, I got it working fine with php files.. until I tried to setup a database because I wanted to build user management modules first.. since it would make things easier. Well apparently mySQL dir in var/lib/mysql cannot be shared.. as the sync wiped it out. I found this out after an inordinate amount of bullshit dealing with sudo disappearing to mysql.sock not working/existing etc..

So I figure I'll point the datafile for mySQL to another location so I don't have this issue.. except I find out that the .frm files are the only thing being generated.. and that mySQL uses an InnoDB which is all databases sit in one file.... URGH - I won't be able to separate them if I want to make VMs from them.. so I have to design ONE customer setup and disregard plans to build the login during dev until we replicate another VM..otherwise how will we test it?

Anyway.. I'm trying to make sure there are no huge concerns and will be like OH MAN that was stupid when designing this thing.

The workflow looks as such:

Customer hits a web page where they login (companyname.username) and password are checked against a backend database (mysql), which database it is checked against (and which VM) are determined based on the companyname portion of the companyname.username.

From here the user hits a portal with a bunch of forms that have functions based on a role. Those php pages (forms) read and write against the mySQL database.


I want to make sure this design of front end to page to database isn't retarded and I won't run into any issues.

The whole concept is the functions and pages will be very similar between customers, if not exactly the same.

I don't have enough experience to know if its smarter to have separate vm + database on that VM for each customer.

The scalability could run up to the point where each database is a few gigs, so we're trying to build it that we can bring environments up pretty rapidly.

Also don't want to re-invent the wheel.


Any advice would be greatly appreciated.

 

[ripken204]

a database that is a few gigs is a small database.
have you considered just having one large database for everything?
why exactly do you need a vm for each customer?
or is privacy a big concern to the point that they must be separated?

 

[Weenis]

a database that is a few gigs is a small database.
have you considered just having one large database for everything?
why exactly do you need a vm for each customer?
or is privacy a big concern to the point that they must be separated?

privacy is sort of a concern, in that each customer would be a business.

Some businesses might have sales data in the db.

Id imagine one large database for everything how would you remove portions easily if a customer decides to no longer use your service?

 

[ripken204]

just run a query targeting their IDs..
or if you wanted to, just have a single mysql server and have a separate database for each client, then in the database config for each website just have it connect to the different database name

 

[Zangmonkey]

Is performance important?
Data instances on VMs are tricky to run performantly.

 

[Weenis]

Is performance important?
Data instances on VMs are tricky to run performantly.

yeah, it should be.. there would be metrics being run against those dbs

 

[mwarps]

I'd find someone who knows something about VMs, LAMP and DBA, and fast.

 

[Schro]

For the user authentication side, what about using a form of LDAP, such as OpenLDAP or Active directory to manage your authentication and authorization?

Then if you continue down the one VM for each customer route, you can essentially have a VM image that you then stand up, and tell it which LDAP groups should have access to what and you're off to the races.

However, many of my clients building this sort of architecture tend to go with a unified platform (i.e. everyone in the same database, separated programmatically) unless the customer insists on having fully separate infrastructure (and they are then charged a higher price for that). Of course, you have risk from a development perspective that if your coding isn't secure and well tested, that customers could see other customer's data, and at that point you're bankrupt...

 

[ToddW2]

I'd find someone who knows something about VMs, LAMP and DBA, and fast.

THis is what I suggest too.

Right now YOU are the danger to your clients security.

 

[mikeblas]

except I find out that the .frm files are the only thing being generated.. and that mySQL uses an InnoDB which is all databases sit in one file....

You can have tables in their own files, but MySQL is still a pretty crummy RDBMS.

I want to make sure this design of front end to page to database isn't retarded and I won't run into any issues.

It's not "retarded", and almost any multi-tennant system works this way. But I'm not sure you've done an accurate or complete job of describing the system.

I don't have enough experience to know if its smarter to have separate vm + database on that VM for each customer.

Why are you using a VM in the first place? Why must there be a database per VM? Why not one database for the whole system? vagrant is mostly about setting up (and replicating) development environments. It sounds like you intend to use it in production for some reason. Is that the case? Was your intention to use one VM per customer? Why?

The scalability could run up to the point where each database is a few gigs, so we're trying to build it that we can bring environments up pretty rapidly.

Scaling database size is more about hardware than VMs. It's not too hard to make an architecture where you can add hardware cheaply and scale out, but that'll depend on your requirements -- which you haven't stated. "A few gigs" really isn't a very big database, and size isn't really the governing factor of database capacity estimation... it's transactions per second and IOPS.

Any advice would be greatly appreciated.

Do you have some specific questions?