How to design a multi tenant mysql database [closed]

Question

Let us say I need to design a database which will host data for multiple companies. Now for security and admin purposes I need to make sure that the data for different companies is properly isolated but I also do not want to start 10 mysql processes for hosting the data for 10 companies on 10 different servers. What are the best ways to do this with the mysql database.

Obidiah

Answer 1

There are several approaches to multi-tenant databases. For discussion, they're usually broken into three categories.

• One database per tenant.
• Shared database, one schema per tenant.
• Shared database, shared schema. A tenant identifier (tenant key) associates every row with the right tenant.

MSDN has a good article on the pros and cons of each design, and examples of implementations.

---

Microsoft has apparently taken down the pages I referred to, but they are on on archive.org. Links have been changed to point there.

For reference, this is the original link for the second article

Chelyabinsk

Can sharding help in this case, with each shard containing data for a separate client? – Obidiah

The spectrum runs from "shared nothing" (one database per tenant) to "shared everything" (tenant key in every table). Sharding is near the "shared everything" end of the spectrum. I'd hope it would make disaster recovery for a single tenant simpler. If you can imagine a "shared everything" system on a single server, recovering data for a single tenant means recovering just some of the rows in every shared table. That's not just dead simple. – Observatory

Do MySQL supports single DB multiple sachems ? – Woodberry

@Sagar: No. MySQL doesn't support create schema in the standard SQL sense. create schema in MySQL is just a synonym for create database. Docs – Observatory

Hi any one give me the correct example for tenant database. – Romanticize

@RajMohan: See Realizing Multi-Tenant Data Architecture – Observatory

@MikeSherrill'CatRecall' can you given me real time example, i can't find – Romanticize

@RajMohan: Are you asking me to write code for you? I'm not going to do that. Are you asking me where to find code for a multi-tenant database? I don't know where to find it. – Observatory

@MikeSherrill'CatRecall' I just need sample for each type, i'm not asking you code for me. Just asking the example. – Romanticize

The MSDN links given in answer are no more present. – Vasoconstrictor

@Kul03: Thanks. I've found the pages on archive.org. I'll edit my answer. – Observatory

Answer 2

In MySQL I prefer to use a single database for all tenants. I restrict access to the data by using a separate database user for each tenant that only has access to views that only show rows that belong to that tenant.

This can be done by:

1. Add a tenant_id column to every table
2. Use a trigger to populate the tenant_id with the current database username on insert
3. Create a view for each table where tenant_id = current_database_username
4. Only use the views in your application
5. Connect to the database using the tenant specific username

I've fully documented this in a blog post: https://opensource.io/it/mysql-multi-tenant/

Lebbie

Your article doesn't seem to exist anymore: Error 404. Do you have an updated link? Thanks – Miguel

medium.com/@tucq88/… This is a very good detailed blog which explains this same approach in mysql. – Ithaman

Answer 3

The simple way is: for each shared table, add a column says SEGMENT_ID. Assigned proper SEGMENT_ID to each customer. Then create views for each customer base on the SEGMENT_ID, These views will keep data separated from each customers. With this method, information can be shared, make it simple for both operation & development (stored procedure can also be shared) simple.

Kajdan

SEGMENT_ID is a terrible name – Disinterest

TENANT_ID is the perfect name – Knowall

Answer 4

Assuming you'd run one MySQL database on a single MySQL instance - there are several ways how to distinguish between what's belonging to whom. Most obvious choice (for me at least) would be creating a composite primary key such as:

CREATE TABLE some_table (
id int unsigned not null auto_increment,
companyId int unsigned not null,
..
..
..,
primary key(id, company_id)
) engine = innodb;

and then distinguishing between companies by changing the companyId part of the primary key. That way you can have all the data of all the companies in the same table / database and at application level you can control what company is tied to which companyId and determine which data to display for certain company.

If this wasn't what you were looking for - my apologies for misunderstanding your question.

Nabila

My thinking is to isolate the data to some extent so that there are unique login ids for each company database. So that lets say if some security compromise happened at Company A, the data for Company B should still be safe. – Obidiah

At the time I didn't understand ur answer . When I did understand it,it occurred to me that I even used the same pattern .I don't think it is nonsense u probably need to communicate with me for clarity and not to tag what I'm saying as nonsense .I think that's rude . – Attain

Answer 5

Have you considered creating a different schema for each company?

You should try to define more precisely what you want to achieve, though.

If you want to make sure that an HW failure doesn't compromise data for more than one company, for example, you have to create different instances and run them on different nodes.

If you want to make sure that someone from company A cannot see data that belong to company B you can do that at the application level as per Matthew PK answer, for example

If you want to be sure that someone who manages to compromise the security and run arbitrary SQL against the DB you need something more robust than that, though.

If you want to be able to backup data independently so that you can safely backup Company C on mondays and Company A on sundays and be able to restore just company C then, again, a purely application-based solution won't help.

Goulash

I want to do something like you mentioned in your last point. Do the backups independently, keep the data isolated to some extent so that if one company is receiving lots of traffic then move them to a completely new instance on a separate machine. – Obidiah

Then have a look at schemas, I don't have much experience with mySql so I may be missing some implementation-specific detail, but I think it's the best approach in your case. – Goulash

Answer 6

Given a specific DB User, you could give a user membership to group(s) indicating the companies whose data they are permitted to access.

I presume you're going to have a Companies table, so just create a one-to-many relationship between Companies and MySQLUsers or something similar.

Then, as a condition of all your queries, just match the CompanyID based on the UserID

Candracandy

Answer 7

in my file Generate_multiTanentMysql.php i do all steps with PHP script

https://github.com/ziedtuihri/SaaS_Application

A Solution Design Pattern :

• Creating a database user for each tenant

• Renaming every table to a different and unique name (e.g. using a prefix ‘someprefix_’)

• Adding a text column called ‘id_tenant’ to every table to store the name of the tenant the row belongs to

• Creating a trigger for each table to automatically store the current database username to the id_tenant column before inserting a new row

• Creating a view for each table with the original table name with all the columns except id_tenant. The view will only return rows where (id_tenant = current_database_username)

• Only grant permission to the views (not tables) to each tenant’s database user Then, the only part of the application that needs to change is the database connection logic. When someone connects to the SaaS, the application would need to:

• Connect to the database as that tenant-specific username

Saratov