MySQL 如何设计一个多租户的mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5570102/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to design a multi tenant mysql database
提问by gaurav
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.
假设我需要设计一个数据库来为多家公司托管数据。现在出于安全和管理目的，我需要确保正确隔离不同公司的数据，但我也不想启动 10 个 mysql 进程来在 10 台不同的服务器上托管 10 家公司的数据。使用 mysql 数据库执行此操作的最佳方法是什么。
回答by Mike Sherrill 'Cat Recall'
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.
MSDN对好文章的优点和每一个设计的利弊，以及实施方案的实例。
微软显然已经删除了我提到的页面，但它们在 archive.org 上。链接已更改为指向那里。
For reference, this is the original link for the second article
回答by tintinhoking
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.
简单的方法是：为每个共享表，添加一列表示SEGMENT_ID。为每个客户分配适当的 SEGMENT_ID。然后根据 SEGMENT_ID 为每个客户创建视图，这些视图将使数据与每个客户分开。通过这种方式，可以实现信息的共享，让运维和开发都变得简单（存储过程也可以共享）简单。
回答by Rob Jenks
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.
在 MySQL 中，我更喜欢为所有租户使用一个数据库。我通过为每个租户使用单独的数据库用户来限制对数据的访问，该用户只能访问仅显示属于该租户的行的视图。
This can be done by:
这可以通过以下方式完成：
- Add a tenant_id column to every table
- Use a trigger to populate the tenant_id with the current database username on insert
- Create a view for each table where tenant_id = current_database_username
- Only use the views in your application
- Connect to the database using the tenant specific username
- 向每个表添加一个 tenant_id 列
- 使用触发器在插入时使用当前数据库用户名填充tenant_id
- 为每个表创建一个视图，其中 tenant_id = current_database_username
- 仅使用应用程序中的视图
- 使用租户特定的用户名连接到数据库
I've fully documented this in a blog post: https://opensource.io/it/mysql-multi-tenant/
我已经在博客文章中完整记录了这一点：https: //opensource.io/it/mysql-multi-tenant/
回答by N.B.
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:
假设您在单个 MySQL 实例上运行一个 MySQL 数据库 - 有几种方法可以区分哪些内容属于谁。最明显的选择（至少对我而言）是创建一个复合主键，例如：
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.
然后通过更改主键的 companyId 部分来区分公司。这样您就可以在同一个表/数据库中拥有所有公司的所有数据，并且在应用程序级别，您可以控制哪个公司与哪个 companyId 相关联，并确定要为特定公司显示哪些数据。
If this wasn't what you were looking for - my apologies for misunderstanding your question.
如果这不是您要找的东西 - 我很抱歉误解了您的问题。
回答by Matthew
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 Companiestable, so just create a one-to-many relationship between Companiesand MySQLUsersor something similar.
我想你会有一张Companies桌子，所以只需在Companies和MySQLUsers或类似的东西之间创建一对多的关系。
Then, as a condition of all your queries, just match the CompanyIDbased on the UserID
然后，作为所有查询的条件，只需匹配CompanyID基于UserID
回答by p.marino
Have you considered creating a different schemafor 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
如果您想确保 A 公司的某个人无法看到属于 B 公司的数据，您可以按照 Matthew PK 答案在应用程序级别执行此操作，例如
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.
但是，如果您想确保有人设法破坏安全性并对数据库运行任意 SQL，您需要比这更强大的东西。
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.
如果您希望能够独立备份数据，以便您可以在星期一安全地备份 C 公司，在星期日安全地备份 A 公司，并且能够仅恢复 C 公司，那么同样，纯粹基于应用程序的解决方案将无济于事。
