How to Implement Database Multitenancy in MySQL

Database multitenancy is an architectural approach that allows multiple tenants (clients or customers) to share the same database instance while maintaining logical data separation. In this blog, we'll explore how to implement database multitenancy in MySQL using different strategies, along with practical examples.

What is Database Multitenancy?

Database multitenancy involves running a single database instance that serves multiple tenants. Each tenant's data is logically isolated, ensuring security and privacy while maximizing resource efficiency. The three primary strategies for implementing multitenancy in MySQL are:

1. Single Database, Shared Schema

2. Single Database, Separate Schemas

3. Multiple Databases

Strategies for Implementing Multitenancy in MySQL

1. Single Database, Shared Schema

In this approach, all tenants share the same database and schema. Tenant data is differentiated by a tenant identifier (Tenant ID) in each table.

Example

1. Create the shared schema:

    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT,
        username VARCHAR(50),
        email VARCHAR(100),
        password VARCHAR(100)
    );
   

2. Insert data for multiple tenants:

    INSERT INTO users (tenant_id, username, email, password) VALUES
    (1, 'user1_tenant1', 'user1@example.com', 'password1'),
    (1, 'user2_tenant1', 'user2@example.com', 'password2'),
    (2, 'user1_tenant2', 'user3@example.com', 'password3'),
    (2, 'user2_tenant2', 'user4@example.com', 'password4');
   

3. Query data for a specific tenant:

    SELECT * FROM users WHERE tenant_id = 1;
   

Advantages of Single Database, Shared Schema:

1. Resource Efficiency: Since all tenants share the same database and schema, resource usage is optimized, resulting in lower costs and better performance.

2. Simplified Management: Schema updates and changes can be applied universally, simplifying maintenance and reducing the risk of inconsistencies.

3. Easier Development: Developing the application can be more straightforward as all tenants share the same data structures.

Disadvantages of Single Database, Shared Schema:

1. Limited Isolation: Data for different tenants resides in the same tables, making it challenging to ensure strict data isolation. This can lead to potential data leakage or security issues.

2. Scalability Challenges: As the number of tenants increases, the single schema can become a bottleneck, leading to performance degradation.

3. Complex Data Queries: Queries need to include tenant-specific filters (e.g., WHERE tenant_id = ?), adding complexity to data access and increasing the risk of errors.

2. Single Database, Separate Schemas

In this approach, all tenants share the same database, but each tenant has a separate schema within that database.

Example

1. Create separate schemas for each tenant:

    CREATE SCHEMA tenant1;
    CREATE SCHEMA tenant2;
   

2. Create tables within each schema:

    CREATE TABLE tenant1.users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100),
        password VARCHAR(100)
    );
   
    CREATE TABLE tenant2.users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100),
        password VARCHAR(100)
    );
   

3. Insert data into tenant-specific schemas:

    INSERT INTO tenant1.users (username, email, password) VALUES
    ('user1_tenant1', 'user1@example.com', 'password1'),
    ('user2_tenant1', 'user2@example.com', 'password2');
   
    INSERT INTO tenant2.users (username, email, password) VALUES
    ('user1_tenant2', 'user3@example.com', 'password3'),
    ('user2_tenant2', 'user4@example.com', 'password4');
   

4. Query data from a specific schema:

    SELECT * FROM tenant1.users;
   

Advantages of Single Database, Separate Schemas:

1. Improved Isolation: Each tenant's data is stored in a separate schema, providing better logical separation and reducing the risk of data leakage.

2. Customizability: Tenant-specific customizations can be implemented within their respective schemas without affecting other tenants.

3. Simplified Querying: Tenant-specific data can be queried without needing to filter by tenant ID, simplifying data access.

Disadvantages of Single Database, Separate Schemas:

1. Increased Complexity: Managing multiple schemas within a single database adds complexity, especially when applying updates or schema changes.

2. Resource Overhead: Slightly higher resource usage compared to a shared schema due to the need to maintain separate schemas.

3. Scalability Limits: While more scalable than a shared schema, this approach still faces limits as the number of schemas grows.

3. Database Per Tenant

In this approach, each tenant has a dedicated database. This provides the highest level of isolation but may lead to increased operational complexity.

Example

1. Create separate databases for each tenant:

    CREATE DATABASE tenant1_db;
    CREATE DATABASE tenant2_db;
   

2. Create tables within each database:

    USE tenant1_db;
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100),
        password VARCHAR(100)
    );
   
    USE tenant2_db;
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50),
        email VARCHAR(100),
        password VARCHAR(100)
    );
   

3. Insert data into tenant-specific databases:

    USE tenant1_db;
    INSERT INTO users (username, email, password) VALUES
    ('user1_tenant1', 'user1@example.com', 'password1'),
    ('user2_tenant1', 'user2@example.com', 'password2');
   
    USE tenant2_db;
    INSERT INTO users (username, email, password) VALUES
    ('user1_tenant2', 'user3@example.com', 'password3'),
    ('user2_tenant2', 'user4@example.com', 'password4');
   

4. Query data from a specific database:

    USE tenant1_db;
    SELECT * FROM users;
   

Advantages of Database Per Tenant:

1. Highest Isolation: Each tenant's data is completely isolated in a separate database, enhancing security and privacy.

2. Scalability: Easier to scale horizontally by adding more databases as needed. Performance issues in one database do not affect others.

3. Customizability: Allows for extensive customizations per tenant without impacting other tenants.

Disadvantages of Database Per Tenant:

1. Operational Complexity: Managing and maintaining multiple databases is complex and requires robust automation tools and practices.

2. Higher Costs: Increased infrastructure and maintenance costs due to the overhead of running and managing multiple databases.

3. Deployment Complexity: Deploying changes across multiple databases can be more complicated, requiring careful orchestration to avoid inconsistencies.

4. Multiple Databases, Multiple Tenants Per Database, Shared Schema

This strategy involves using multiple databases, each containing multiple tenants. Within each database, tenants share the same schema. This approach provides a balance between the isolation of data and the efficient use of resources.

Example

Here’s an example of how you might implement this strategy in MySQL:

1. Create Multiple Databases:

    CREATE DATABASE tenant_db1;
    CREATE DATABASE tenant_db2;
   

2. Create a Shared Schema in Each Database:

    USE tenant_db1;
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT,
        name VARCHAR(255),
        email VARCHAR(255),
        password VARCHAR(255)
    );
   
    USE tenant_db2;
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT,
        name VARCHAR(255),
        email VARCHAR(255),
        password VARCHAR(255)
    );
   

3. Insert Tenant Data:

    USE tenant_db1;
    INSERT INTO users (tenant_id, name, email, password) VALUES (1, 'John Doe', 'john@example.com', 'password123');
    INSERT INTO users (tenant_id, name, email, password) VALUES (2, 'Jane Smith', 'jane@example.com', 'password123');
   
    USE tenant_db2;
    INSERT INTO users (tenant_id, name, email, password) VALUES (3, 'Jim Brown', 'jim@example.com', 'password123');
    INSERT INTO users (tenant_id, name, email, password) VALUES (4, 'Jake White', 'jake@example.com', 'password123');
   

4. Access Tenant Data:

   To access data for a specific tenant, use the appropriate database:

    USE tenant_db1;
    SELECT * FROM users WHERE tenant_id = 1;
   
    USE tenant_db2;
    SELECT * FROM users WHERE tenant_id = 3;
   

Advantages of Multiple Databases, Multiple Tenants Per Database, Shared Schema:

1. Enhanced Isolation: By distributing tenants across multiple databases, you achieve better isolation compared to a single database. If one database encounters issues, it does not affect all tenants.

2. Improved Scalability: This approach allows horizontal scaling by adding more databases as needed. Each database can handle a subset of tenants, reducing the load on any single database.

3. Resource Optimization: Sharing schemas within a database ensures efficient use of resources. The overhead of maintaining separate schemas is avoided while still achieving a level of isolation.

4. Simplified Maintenance: Schema updates can be applied to all tenants within a database simultaneously, simplifying maintenance compared to managing separate schemas for each tenant.

5. Customizability: Allows for some degree of customization for tenants within each database without impacting others in different databases.

Disadvantages of Multiple Databases, Multiple Tenants Per Database, Shared Schema:

1. Complexity in Management: Managing multiple databases with multiple tenants each adds a layer of complexity. This requires robust database management and monitoring tools.

2. Potential for Uneven Load Distribution: If tenant distribution is not balanced, some databases may become overloaded while others remain underutilized.

3. Limited Customization: Since tenants share the same schema within each database, customizations that require schema changes may be challenging to implement.

4. Data Migration Challenges: Moving tenants between databases for load balancing or other reasons can be complex and may require downtime.

5. Security Risks: While better than a single database, security risks still exist due to shared schemas. Proper access controls and data isolation mechanisms must be in place to mitigate these risks.

Whether you are building a new application or refactoring an existing one, embracing database multitenancy can help you achieve better resource utilization, simplified maintenance, and improved scalability. Careful planning and consideration of your specific requirements will lead to the best results.