Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to design a multi tenant mysql database

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.

like image 624
stocked Avatar asked Apr 06 '11 16:04

stocked


People also ask

How do you build a multi-tenant platform?

There are three approaches on how to build a multi-tenant application: Database per tenant — each tenant has its database. Shared database, separate schema — all tenants are using the same database, but each tenant has his schema. Shared database, shared schema — all tenants are using the same schema.

What is multi-tenant design?

Multi-tenancy is an architecture in which a single instance of a software application serves multiple customers. Each customer is called a tenant.

What is multi-tenant DB architecture?

A multi-tenant OpenEdge database is a shared database with a shared schema and logically and physically isolated data storage on a per tenant or group basis. Each object (table, index, LOB) is stored in a partition. Partitions keep data physically separate for each tenant.

Can we create multiple schema in mysql?

You can have as many schemas as you like, e.g., as has been suggested, a development schema and a prodcution schema. They can both be stored in the same database server and can contain exactly the same content.


2 Answers

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

like image 101
Mike Sherrill 'Cat Recall' Avatar answered Sep 28 '22 02:09

Mike Sherrill 'Cat Recall'


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/

like image 43
Rob Jenks Avatar answered Sep 28 '22 03:09

Rob Jenks