Mysql and multiple databases

  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

I want to have two websites, running the same program and would like to ask your opinion on how to organize the databases.

website1 has databases db_users,db_pictures and db_emails
website2 has databases db_users,db_pictures and db_emails

How do I seperate the databases in Mysql? Obviously if website2 loads db_users, it will get all the users from website1

- Am I forced to call the databases different names?
ie:
db_users1,db_pictures1 and db_emails1
db_users2,db_pictures2 and db_emails2

- Can I keep the same database name, and somehow change the login and password so it loads a different set ?
ie, database db_users for website1 has 20 entries while database db_users for website2 has a different set of users.

Ideas/links/tutorials?

John M
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

You could add a field to each table that tells it if it what webpage the row came from, then on your querys you just tell it to only select rows where database = website2.
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

SpooF wrote:
You could add a field to each table that tells it if it what webpage the row came from, then on your querys you just tell it to only select rows where database = website2.


I am trying to avoid that since I don't want to modify the program. I want to globally modify how the program gets its data. Modifing the queries means I have to go through each query and make sure it knows the setting etc. That is just asking for an oversight.
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

So basically, your ideal solution would be to grant access only to rows that the user has created.
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

I would like better, that users have access to databases. Rather than the same database where rows are seperated by user.

At the moment, I will probably name the databases:

website1_users
website1_pictures

website2_users
website2_pictures

But I don't like this idea. If it must be it must be...
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

So basically you want to make virtual database? Where the user can only access his/her information in the database?

I still don't quite understand your question.

You might already know this, but I'll share it anyways.


Code: [ Select ]
GRANT [priv_type] ON 'foo'.'bar\_*' TO 'bar'@'localhost';


This will give user 'bar' privileges to see only tables under the database named 'foo' with tables matching the partner 'bar_*'.

Row Level Security would restrict a user to only accessing information they have inserted into the database, just in case you want to research it abit.
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

I guess I'm not explaining it correctly.

- user bar_ should see all the tables and rows in his database and no database or tables of foo_.
- user foo_ should see all the tables and rows in his database and no database or tables of bar_.

They are different databases. Not priviliges on the same database.
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

w
  • SpooF
  • ٩๏̯͡๏۶
  • Bronze Member
  • User avatar
  • Posts: 3422
  • Loc: Richland, WA

Post 3+ Months Ago

Oh wow, okay. I was reading database as tables. Each database will require a unique name, but you can still do the permissions to restrict who can see what.
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

Yeah I was thinking that is how it needs to be done.

There doesn't seem to be a way in MySQL to "Layer" the databases. Imagine like in photoshop, where you could have a layer set, with 10 databases in it, and then just swap out layer sets (database sets).
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

What about setting up two different server instances to hold the info? The only thing that would change then would be the login info.
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

I would go the database name prefix route.

How to get there from where you are now might not be that hard, assuming you've been consistient in the way you write your SQL queries.

I'm guessing you include the database and table names when making queries. Have you been wrapping your table/database names in backticks ?

Code: [ Select ]
SELECT * FROM `users.field_a` WHERE ...


You could use sed or something to find and replace the database names with contsants so you have something along these lines.

Code: [ Select ]
SELECT * FROM `' . USERS_DATABASE . '.field_a WHERE
  • camperjohn
  • Guru
  • Guru
  • User avatar
  • Posts: 1127
  • Loc: San Diego

Post 3+ Months Ago

joebert wrote:
I would go the database name prefix route.

How to get there from where you are now might not be that hard, assuming you've been consistient in the way you write your SQL queries.

I'm guessing you include the database and table names when making queries. Have you been wrapping your table/database names in backticks ?

Code: [ Select ]
SELECT * FROM `users.field_a` WHERE ...


You could use sed or something to find and replace the database names with contsants so you have something along these lines.

Code: [ Select ]
SELECT * FROM `' . USERS_DATABASE . '.field_a WHERE


I dont need to include the database name when selecting, only when connecting. I have an array of servers and databases, and since I have one location in the entire code where the connection is made, all I need to do it change the array to get to a new database.

I just thought there would be a fancy way of doing it in MySQL where the same connection is made, but a different block of databases is accessed.

Think Photoshop layer sets. Each layer set has multiple layers, then you can hide and show different layer sets. I guess MySQL is not that advanced or was never meant to be used like that?
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

Probably the latter. Although, such functionality could make transitions between dev/qa/prod environments easier. Most of my php apps have config based in that select and build an array up front based on the URL. Allows one code base to be functional on three environments (saves on the SVN work).
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

I'm going to go out on a limb here.
http://en.wikipedia.org/wiki/BigTable

Post Information

  • Total Posts in this topic: 15 posts
  • Users browsing this forum: No registered users and 112 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.