Mysql & multi-tenant db: the best way to organize?

Question

I've never completed any multi-tenant project before...

What is the best way to organize multi-tenant db using mysql? and to seperate access between different companies?

sure it can be done in a different ways:

1. different database for each company
2. different tables for each company
3. one db each table with column tenant_id

i prefer 3rd option.

but: is it possible somehow to limit access between different companies data, for example using mysql schema (or something another) and not using back-end code?

i heard something, that in postgresql its possible using schema...

but is this possible in mysql? (for example tenant1 cannot view data of tenant2)

Answer 1

• If you have thousands of tables or thousands of databases, there could be performance problems.
• If you let customers connect on their own directly to mysql, then you must control their GRANTs, which can be managed at the database or table level. (This is one way to 'limit access'.)
• If you throw all customers into the same set of tables, then you must provide security via an application layer, and manage logins yourself, not via MySQL's GRANTs.
• You could try to use VIEWs, with permissions, to constrain users. Let users connect to the dataset, but limit them to views only. Build into the views a way to limit them to tenant_id.... (I have not thought through this approach; it may have fatal flaws.)
• A 4th option is to have separate instances of MySQL; each customer has nearly-complete control of his instance. (You maintain admin control.)
• With the 4th option, there are many choices - VMs, Docker, mysql_multi, etc. Be warned: those tend to work better on *nix, not Windows. (In spite of decades of development, Win still lags behind *nix when it comes to being "ready for prime time" in the 'server' arena.)
• With the 4th option, CGroups could be used to limit each customer's resource usage. This way one runaway customer cannot significantly hurt the other customers.
• Another consideration: How easy will it be to "shard"? That is, have multiple servers, each handling some of your customers. In particular, what will it take to migrate a customer from one shard to another. Once you have solved that, you have addressed the questions of how to upgrade the hardware, OS, MySQL version, etc.
• Do you provide specific client software (a la WordPress) (SaaS)? Or are you simply providing a database engine (DaaS)?
• Will there be client software on the same server?