具有独立数据库的 Laravel 5 多租户应用程序 - 用户可以访问多个安装

Laravel 5 Multi-Tenancy App with separate databases - users have access to multiple installations

提问by Nicko Brooko

Over the past couple of years I have developed a very customised PHP/MySQL application that is used for a number of clients. I have been creating a new database and new installation for each client up to this point.

在过去的几年里，我开发了一个非常定制的 PHP/MySQL 应用程序，用于许多客户端。到目前为止，我一直在为每个客户端创建一个新的数据库和新的安装。

The first obvious problem here is keeping multiple installations up to date with any code changes; a second problem is that each installation has a large amount of users; and for most clients; some of these users are the same - and they have to have a number of seperate user accounts and urls to remember.

这里第一个明显的问题是保持多个安装与任何代码更改保持同步；第二个问题是每次安装都有大量的用户；对于大多数客户；其中一些用户是相同的 - 他们必须有许多单独的用户帐户和 URL 来记住。

I am moving the application over to Laravel 5 at the moment and looking into the best implementation for multi-tenancy; so looking for a little advice on the best implementation. I've used Laravel before but am by no means an expert.

我目前正在将应用程序转移到 Laravel 5 并研究多租户的最佳实现；所以寻找一些关于最佳实施的建议。我以前使用过 Laravel，但绝不是专家。

This is what I am thinking as far as setup.

就设置而言，这就是我的想法。

1 Master Database that holds tables for:

1 个主数据库，其中包含以下表：

1. All User account information
2. Access Control Table - which installations the users can access; what their user level on that installation is.
3. Configuration table for each installation - database connection info, basic configuration etc.

1. 所有用户帐户信息
2. 访问控制表 - 用户可以访问哪些安装；他们对该安装的用户级别是什么。
3. 每个安装的配置表 - 数据库连接信息、基本配置等。

Then a seperate database for each installation that contains all the information that is needed for, and submitted to, that installation.

然后是每个安装的单独数据库，其中包含该安装所需并提交给该安装的所有信息。

The ideal setup is that a user can go to a subdomain i.e installationname.appname.com; sign in with their master login details and automatically go to the required installation; OR go to appname.com, sign in and then select which installation to connect to.

理想的设置是用户可以访问子域，即 installationname.appname.com；使用他们的主登录详细信息登录并自动转到所需的安装；或转到 appname.com，登录，然后选择要连接的安装。

My questions are:

我的问题是：

1. Is this the best arrangement to achieve what I am looking for.
2. What's the best method for storing which installation the user is looking at (session variable)
3. Can I define a model between 2 databases - perhaps define one connection as master connection, then dynamically define another connection using the database connection information in the master database to connect to the correct installation. - the system will often need to check the user info for access level etc.

1. 这是实现我所寻找的最佳安排吗？
2. 存储用户正在查看的安装的最佳方法是什么（会话变量）
3. 我可以在2个数据库之间定义一个模型吗——也许将一个连接定义为主连接，然后使用主数据库中的数据库连接信息动态定义另一个连接以连接到正确的安装。- 系统经常需要检查用户信息以获取访问级别等。

I'm sure there's a lot of issues that I have not thought of; but if anyone has any links or guidance that may help that would be great. First time asking a question on SO but have found a huge amount of research help here in the past so thanks to the community!

我敢肯定，有很多问题是我没有想到的；但如果有人有任何可能有帮助的链接或指导，那就太好了。第一次在 SO 上提问，但过去在这里找到了大量的研究帮助，所以感谢社区！

---

---

UPDATE - So I think I have a way to make this work now; using seperate databases as above; set

更新 - 所以我想我现在有办法完成这项工作；如上所述使用单独的数据库；放

protected $connection = 'tenant_connection'

in the models relating to tenant-specific database content.

在与租户特定的数据库内容相关的模型中。

Then somewhere in a header file set the tenant_connection that is wanted based on a session variable which has been set on login/by subdomain.

然后在头文件中的某处设置基于登录/子域设置的会话变量所需的tenant_connection。

$tenant = Installation::where('installation', '=', $session['installation'])->first();
Config::set('database.connections.tenant_connection', array('driver' => 'mysql', 'host' => $tenant->db_hostname, 'username' => $tenant->db_username)... etc.

Assuming relationships will work across connections; I don't see why this would not work; just need to work out best place to set the tenant connection.

假设关系将跨连接工作；我不明白为什么这行不通；只需要找出设置租户连接的最佳位置。

回答by Nicko Brooko

Ok so what I ended up doing was having all user info, names of installations and mappings of what users can access what installations in one database, and all tenant info in seperate databases.

好的，所以我最终做的是拥有所有用户信息、安装名称和用户可以访问一个数据库中哪些安装的映射，以及单独数据库中的所有租户信息。

I then had two connections, mysql and mysql_tenant; where mysql_tenant database is not pre-set but dynamic.

然后我有两个连接，mysql 和 mysql_tenant；其中 mysql_tenant 数据库不是预先设置的而是动态的。

The User, Installations and mappings model use the mysql connection, all others use mysql_tenant

用户、安装和映射模型使用 mysql 连接，所有其他模型使用 mysql_tenant

Created a code for each installation, and used this as the name of the tenant database; storing this code in the session.

为每个安装创建一个代码，并将其用作租户数据库的名称；将此代码存储在会话中。

Used a middleware MultiTenant, to control the switching between installations using these key lines:

使用中间件 MultiTenant，使用以下关键行来控制安装之间的切换：

$tenant_id = session()->get('tenant');

\Config::set('database.connections.mysql_tenant.database', $dbname);
\DB::setDefaultConnection('mysql_tenant');

There's a lot more to it for building the method to switch etc, but this is the gist.

构建切换方法的方法还有很多，但这是要点。

回答by Laurence

It is difficult to answer most of your question - as it is specific to your application and opinion based.

很难回答您的大部分问题 - 因为它特定于您的应用程序和基于意见的。

But the one bit I can answer is different models can have different database connections. So your usermodel uses the normal default connection - but your other models can use another connection:

但我可以回答的一点是不同的模型可以有不同的数据库连接。所以您的user模型使用正常的默认连接 - 但您的其他模型可以使用另一个连接：

class Example extends Model {

    protected $connection= 'second_db_connection';

}

Then in your DB connection file - you would have something like this:

然后在你的数据库连接文件中 - 你会有这样的东西：

return array(
    'connections' => array(
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        'second_db_connection' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),

回答by stephenthedev

Laravel 5 is advanced enough that you should be able to have simply one installation along with a strategic database, with well defined relations and keys. There is rarely ever a need for multiple databases.

Laravel 5 已经足够先进了，您应该能够简单地安装一个战略数据库，并具有明确定义的关系和密钥。很少需要多个数据库。

If you can be more specific about your requirements I can provide a more specific answer.

如果您可以更具体地说明您的要求，我可以提供更具体的答案。