Multi-tenancy Catalogs for MariaDB server

Get notified when we launch

Presentations

Background and motivation

For hosting providers, a common solution, to drive down cost, is to have one MariaDB server support several different customers by creating one named schema for each of them.

This has however a lot of limitations:

  • The user cannot have exactly the same schema(s) on the cloud as he has on premise.
  • The user cannot use multiple schemas.
  • The user cannot take a backup of all his data (not even with mysqldump). This is because the ‘mysql’ schema, which includes users, stored procedures etc cannot be copied as its data is shared among all server users.
  • The user cannot access the general, slow query or error log.

The suggested solutions to solve all of the above and thus create a better multi-tenant database is to add support for catalogs to MariaDB.

By having each customer have their own catalog, they will get very close to the same user experience as if they would have the MariaDB server for themselves.  Customers can create their own users, own databases etc.

Catalogs makes it possible for hosting providers to have 10-100x more ‘not that active database users’ on a server compared to having a container or MariaDB server per customer (which limits a 192G server to about 100 customers with a 1G InnoDB buffer each).

User experience with catalogs

  • Each user is assigned one catalog. The user can specify his catalog in his my.cnf file or as an argument to clients or when connecting to the MariaDB server.
  • Users can do mysqldump of all their tables (including the ‘mysql’ database) and apply it on his own on premise MariaDB or to another ‘MariaDB catalog’ to duplicate his setup.
  • Each catalog has its own privilege system. This allows a MariaDB admin to create users independently in his catalog to users in any other catalog.
  • If the user is using applications that don’t yet support catalogs, he must specify the catalog as part of the database when connecting to the server (‘catalog.database’). Alternatively he can connect to a port that is connected to his database.
  • After logging in, a normal user can only see his objects (databases, tables, users etc) from his database. He cannot access other catalogs or change catalog.
  • If a  normal user wants to  change his active catalog he has to logout from the server and login again.

For the end user, the MariaDB server will act as a normal a standalone server, with the following differences:

  • When connecting to the server, a normal user must specify the catalog. If the connector software does not support catalogs, then the catalog should be specified in the database string. If the catalog is not specified, the ‘def’ catalog is assumed.
  • LOAD DATA INFILE and SELECT … INTO OUTFILE can be configured to only be used with the catalog directory or a directory in it.
  • SHUTDOWN command is only for the ‘catalog root users’
  • Replication (MASTER and SLAVE commands) are only for ‘catalog root users’
  • Errors from background task (like write error) will be logged into the system error log, not the catalog error log.
  • ‘SHOW GLOBAL STATUS’ or ‘SHOW CATALOG STATUS’ will show status data for the active catalog.
  • ‘SHOW SERVER STATUS’, only available for the catalog root user, shows the status for the whole server.
  • The server will handle legacy applications by extending the default database in the connection to contain the catalog in the form “catalog/database”. See Appendix for details.
  • Tables that are only read from the ‘def.mysql’ schema:
    • plugin
    • help_* tables
    • time_zone* tables
    • gtid_slave_pos (replication state)
    • innodb_index_stats (innodb internal)
    • servers (federated)
    • transaction_registry (innodb internal)
    • func (udf)
    • performance_schema

New ‘catalog root user’

The ‘def’ catalog is reserved to store permissions for ‘catalog root users’, which can access any catalog.  These are meant for admin users that need to do tasks like shutdown, upgrade, create/drop catalogs, managing master and slaves etc.

Only the ‘catalog root user’ can change to another catalog with ‘USE CATALOG catalog_name’.

A normal user can execute ’USE CATALOG current-catalog’.  This will be needed to be able to execute a mysqldump that will include this command.

New storage layout

MariaDB server will be able to run either on ‘original mode’, where the data layout is exactly as it was before, or on ‘catalog’ mode, with a new data layout:

When running mariadb_install_db’ with –use-catalogs, it will create the following new data structure:

data_directory/

  • engine system data files
  • system files
  • replication files
  • general.log
  • error.log
  • def/
    • mysql/
    • privilege tables
  • catalog1/
    • general.log
    • error.log
    • mysql/
      • privilege tables
    • database1/
      • tables for database1
    • database2/
      • tables for database2
  • catalog2/
    • general.log
    • Error.log
    • mysql/
      • privilege tables
    • database1/
      • tables for database1
    • database2/
      • tables for database2

The disk structure when not using catalogs is:

data_directory/

  • engine system data files
  • system files
  • replication files
  • general.log
  • error.log
  • mysql/
    • privilege tables
  • database1/
    • tables for database1
  • database2/
    • tables for database2

The above shows:

  • There is a ‘mariadb’ catalog that stores admin users that can access all catalogs, shutdown servers, create new catalogs etc. The ‘system root’ user uses this when connecting.
  • Each catalog has its own users, privilege tables, databases, error log and general logs.

The MariaDB server will automatically start in catalog mode if it notices the new directory structure.

Catalog SQL commands

  • CREATE CATALOG
  • DROP CATALOG
  • ALTER CATALOG
  • SHOW CATALOGS (and also information_schema.catalogs)
  • SHOW CREATE CATALOG

Additional information

  • For additional technical information, see Catalogs Overview in the MariaDB Knowledge Base.

Team

  • Vicențiu Ciorbaru – Chief Development Officer MariaDB Foundation
  • Michael Widenius – Founder of MySQL and MariaDB

Contributors:

  • Andrew Hutchings – Chief Contribution Officer MariaDB Foundation
  • Diego Dupin – MariaDB Corporation – Connectors

Links