All pages
Powered by GitBook
1 of 11

Catalogs

Learn about catalogs in MariaDB Server 12 within the context of user account management. This section explains their role in organizing database objects and controlling access permissions.

Catalogs Overview

MariaDB starting with 12.0 — Catalog support is planned for 12.0.

Catalogs are an upcoming feature that will be included in a future release of MariaDB. The MariaDB catalogs will be a multi-tenancy feature where a single instance MariaDB server handles multiple independent tenants (customers), who have their own users, schemas etc. See MDEV-31542 "Add multi-tenancy catalogs to MariaDB" for details.

Background

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 they have on premise.

  • The user cannot use multiple schemas.

  • The user cannot take a backup of all their data (not even with mariadb-dump). 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 or error log.

The other option is to have a MariaDB container for each tenant. The problem with this is that, because of the memory needed per container, one can only have a very limited number of tenants per computer.

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

The following picture shows the change:

By each user having their own catalog, they will get very close to the same user experience as if they would have the MariaDB server for themselves.

Catalogs make 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 their catalog in their my.cnf file or as an argument to clients or when connecting to MariaDB server.

  • Users can mariadb-dump of all their tables (including the ‘mysql’ database) and apply it on their own on premise MariaDB or to another ‘MariaDB catalog’ to duplicate their setup.

  • Each catalog has its own privilege system. This allows a MariaDB admin to create users independently in their catalog to users in any other catalog. This also implies that the catalog has to be part of the connect information as otherwise the server does not know which user table to use.

  • If the user is using applications that don’t yet support catalogs, they can specify the catalog as part of the database when connecting to the server ('catalog.database') or by connecting to a specific port that is associated with a catalog.

  • After logging in, a normal user can only see the objects (databases, tables, users etc) from their database. They cannot access other catalogs or change catalogs.

  • A normal user cannot change the active catalog with a command. They need to logout from the current catalog and login to another.

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 STATUS will show status data for the whole server, not only for the active catalog.

  • 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 primaries and replicas etc.

  • Only the ‘catalog root user’ can change to another catalog with ‘set catalog catalog_name’.

  • A normal user can do ‘set catalog current-catalog’. This will be needed to be able to execute a mariadb-dump that includes 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

    • mariadb/

      • 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 their 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/Functions

  • USE CATALOG catalog_name;

  • CREATE CATALOG

  • DROP CATALOG

  • ALTER CATALOG

  • SHOW CATALOGS (and also information_schema.catalogs)

  • SHOW CREATE CATALOG catalog_name;

  • SELECT CATALOG();

Changes Needed in MariaDB Codebase

Client changes:

  • Add --catalog option to all standard MariaDB clients

  • Add support for looping over all existing catalogs to:

    • mariadb-dump

    • mariadb-backup

    • mariadb-upgrade

Changes to mariadb-install-db:

  • Allow one to create multiple catalogs at once: -–catalogs=”catalog1,catalog2”

  • Init MariaDB with catalog support: —use-catalogs

Changes to mariadb (mysql client):

  • Add support for 'USE CATALOG xxx’' (and later 'use database xxx').

Changes to mysql-test-run:

  • Add support of running tests with catalogs (normal tests are run without catalogs)

Changes to MariaDB server (See MDEV-31542):

  • Add support for 'catalog' in the connection string. For old clients, the user can specify the catalog as part of the database. If catalog is not specified, the 'def' catalog (like now) is assumed.

  • Add CATALOG() function that returns the current catalog.

  • Add ‘USE CATALOG xxx’

  • Add 'USE DATABASE xxx'

  • Create a global CATALOG object to hold all information related to the catalog.

  • Add the current catalog to the 'thd' object.

  • Add catalog argument to all functions that take 'database' as an argument.

  • Add SHOW CATALOGS and information_schema.catalogs

  • Move all relevant global variables (users, privileges, mdl-locks(?), open log files) to be stored in the CATALOG structure.

  • Add 'catalog privilege', for ‘catalog super users’ to allow them to access data in any catalog.

  • Add support for accessing tables with 'catalog.schema.table' (needed for catalog super users).

  • For normal users, only show processes for the current catalog in 'show processlist'.

  • Add loops over all catalogs for information schema for the 'catalog root user'.

  • Update performance schema to take catalogs into account.

  • Work with external connectors to get them to support connecting with a catalog.

  • Check/update all storage engines to ensure they work also with catalogs.

Notes:

  • The storage handler calls will probably not be changed. The storage engine will get the catalog name as part of the database name (catalog/database).

  • We don't need a 'catalog' column for tables in the 'mysql' schema (like mysql.proc) as these are stored per catalog.

Some Implementation Ideas

  • Instead of sending a catalog string to function, use a pointer to the global catalog object. Do the same later for databases. This allows use to precompute things like 'filename' for catalogs and databases and we don't have to do this for every table open. It also allows us to later support logging information at a catalog and database level.

  • Don't take a MDL lock for the catalog for each table. The metadata lock for the catalog will be taken when a user logs in or changes catalog.

  • Add system variables ‘current_catalog’ and ‘current_database’ and allow users to change these.

  • Add support for ‘catalog ports’ that are connected to catalog. This allows users to connect to a specific catalog from any client software.

Limitations (in addition to limitations listed in “User experience with catalogs”)

  • Database names cannot contain ‘.’ when connecting from clients without the new catalog connect option.

  • One cannot refer to other catalogs in triggers, stored procedures, events etc. This is because a transaction cannot span catalogs.

  • Only the catalog root user can use mariadb-backup. This is a normal restriction as one has to be system root to be able to use mariadb-backup.

  • Events are global (to save resources). Catalog users can enable/disable events for their catalog.

Stage 2 (not in first release)

  • Support usage statistics per catalog and whole server (the last for the ‘catalog root user’). This allows the DBA to see the number of queries, type of queries etc. Some ‘system’ and ‘global innodb’ statistics will only be shown globally (number of open files, number of sync calls etc).

  • Support a my.cnf file in each catalog directory to handle catalog (customer) unique defaults.

  • Add quotas per catalog for tables and temporary files.

  • Add more support to limit users from overusing resources (cpu, tables, databases, number of connections etc)

  • Support 'drop catalog'. (This is in Stage 2 as there may be some issues to drop already active CATALOG objects)

  • Add optional catalog support to the S3 engine

  • More things will be added later.

Stage 3

  • Allow users to manage their own replication stream (maybe?).

  • Allow users to have different options for the S3 engine

  • More things will be added later.

Appendix

Legacy Connector Support

SQLALchemy test:

In [1]: from sqlalchemy.engine import make_url
In [2]: u = make_url('mariadb+mariadbconnector://app_user:Password123!@127.0.0.1:3306/catalog/company')
In [3]: u.database
Out[3]: 'catalog/company'

The following tests ensured that inside the server (mysql_change_db), the “catalog/test” was picked up as the database.

PHP PDO test:

$ php -r '$db = new \PDO("mysql:host=localhost;user=dan;dbname=catalog/test;charset=utf8mb4;unix_socket=/tmp/build-mariadb-server-10.4.sock");'

PHP mysqli test:

php -r '$dbcon = mysqli_connect("localhost","dan","nopass","catalog/test",3306,"/tmp/build-mariadb-server-10.4.sock");'

Nodejs test:

var mysql = require('mysql')
var con = mysql.createConnection({
  socketPath: "/tmp/build-mariadb-server-10.4.sock",
  user: "dan",
  password: "yourpassword",
  database: "catalog/test",
})
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
})

(need to map out a few other connectors here to make sure it’s supported well in this form).

Ref: connection.md#handshake-response-packet

Migration of Existing MariaDB Original Mode to the New Catalog Layout

As shared hosting services have a naming scheme from user/schema to database name in MariaDB, to provide a migration to the new catalog layout, the following steps will be required:

  • Use mariadb-dump to dump the original data

  • On the new server execute:

  • mariadb-install-db –catalogs=’catalog_name’

  • mariadb –catalog catalog_name < dump_file

This is needed as InnoDB needs to know where the new files are located.

Migration of One Catalog User to Another MariaDB Server

Create a migration tool set / procedure that does the following

  • Execute FLUSH TABLES FOR EXPORT for all tables in a catalog.

  • Take a copy of the catalog directory

  • Copy the data to a new catalog directory to the new server

  • Run ALTER TABLE ... IMPORT TABLESPACE on each InnoDB table

Note that for partitioned tables the process will be a bit more complex, see above link.

This procedure will be a bit easier after an in-the-works patch for InnoDB related to IMPORT will be pushed. (Should happen before we start on the catalog project)

Other Things

  • Drizzle’s default catalog was called "local". MariaDB’s default will be called ‘def’, as this is what we already have as the default catalog in information_schema, in current connectors and other places.

  • CONNECT engine will need testing against catalogs and maybe a small code change to support them. It could also be a way to join from one catalog to another.

See Also

  • MDEV-31542 Add multi-tenancy catalogs to MariaDB

This page is licensed: CC BY-SA / Gnu FDL

Starting with Catalogs

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Background

mariadb-install-db initializes the MariaDB data directory and creates thesystem tables in the mysql database.

When used with the --catalog options it will initialize MariaDB server to use catalogs. The mariadbd server will automatically discover if catalogs are used or not.

Note that one cannot change a 'normal server' to a server with catalogs or a server with catalogs to a 'normal server'. In the future we will add tools that will allow one to easily move an existing server inside a catalog or move an server inside a catalog to a standalone server.

Initializing a New Server with Catalog Support

To initialize a server with 4 catalogs (the def catalog, that holds the catalog root user (CRU) is automatically created):

mariadb_install_db --catalogs="cat1 cat2 cat3" --datadir=/my/data/

The above will create a directory /my/data and the 4 directories under it, one for each catalog.

Adding More Catalogs to a Running Server

Creating Catalogs with CREATE CATALOG

One can create a new catalog with CREATE CATALOG catalog_name

Creating Catalogs with mariadb_install_db

When adding more catalogs to an existing server, mariadb_install_db will start the mariadb client to execute the needed commands on the running server. This is why one has to supply user and password to mariadb_install_db.

mariadb_install_db --catalogs="cat4 cat5 cat6" --datadir=/my/data --catalog-user=monty --catalog-password

One benefit of using mariadb_install_db is that it's possible to create many catalogs with one command.

See Also

  • CREATE CATALOG

  • DROP CATALOG

  • mariadb-install-db

This page is licensed: CC BY-SA / Gnu FDL

Catalog-Specific Functions and Variables

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Catalog Functions

catalog()

`catalog()

returns the name of the current catalog.`

MariaDB [def.test]> select catalog();
+-----------+
| catalog() |
+-----------+
| def       |
+-----------+

Catalog Variables

@@catalogs

One can check if a server supports catalogs with:

SELECT @@catalogs;
+------------+
| @@catalogs |
+------------+
|          1 |
+------------+

1 means that the server is configured for catalogs.

This page is licensed: CC BY-SA / Gnu FDL

Catalog Status Variables

MariaDB starting with 12.0

Catalog support is planned for 12.0.

When using a MariaDB Server with catalogs support, all status information is collected for the whole server, per catalog and per session.

SHOW SERVER STATUS;

shows the status for the whole server. Note that only the super user in the 'def' catalog has privileges for the above statement.

SHOW GLOBAL STATUS;
SHOW CATALOG STATUS;

Both commands show the status for the current catalog. The reason that GLOBAL shows catalog status is that because catalogs are 'multi-tenant', a catalog user should not be able to see the status from other users (for most things).

SHOW [SESSION] STATUS;

Shows the status for the current connection.

The main "new thing" is that catalogs enable SAS providers to see the status for a single tenant (catalog user). This makes it much easier to find 'bad neighbors' (tenants that cause problems for other tenants) so that they can be moved to other servers.

When the MariaDB server is not configured for catalogs, the following commands are equivalent:

SHOW GLOBAL STATUS
SHOW SERVER STATUS 
SHOW CATALOG STATUS

See Also

  • SHOW STATUS

This page is licensed: CC BY-SA / Gnu FDL

Connecting to a Server Configured for Catalogs

MariaDB starting with 12.0

Catalog support is planned for 12.0.

When connecting to a MariaDB server configured for catalogs, one has to provide the catalog to connect to. There are several ways to do this:

All new native MariaDB clients will support the --catalog option:

mariadb --catalog=mine test

New and old clients can use the 'catalog_name.database_name' syntax to connect:

mariadb mine.test

This will connect the user to the 'mine' catalog and the database 'test'.

Note that one consequence of this is that one should not have a database that contains '.' in the name. If such a database exists, one can still connect to it by using the --catalog= option or prefixing the database with the catalog, like in def.data.base.name.

One will also be able to configure the MariaDB server to automatically choose catalogs depending on the port or IP they are using to connect to the server. This is done by adding the following to the catalog specific my.cnf file, residing in the catalog directory:

[[mariadbd]]
--port=#
--connect-ip=

If catalogs is not specified either directly (---catalog=#) or indirectly (with port or ip) the catalog def will be used.

When connecting to a server not configured for catalogs, one can still use mariadb --catalog=def or mariadb def.datbase_name.

To check if a server supports catalogs:

SELECT @@catalogs;
+------------+
| @@catalogs |
+------------+
|          0 |
+------------+
MariaDB [test]> USE catalog foo;
ERROR 4193 (HY000): MariaDB is not configured to support catalogs

0 means that the server is not configured for catalogs.

This page is licensed: CC BY-SA / Gnu FDL

CREATE CATALOG

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Syntax

CREATE CATALOG [IF NOT EXISTS] catalog_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'comment'

Description

Creates a catalog and the mysql, sys and performance_schema schemas inside the catalog.

CREATE CATALOG can only be performed by a user in the def catalog with the CATALOG privilege.

Note that no users are created.

Example

CREATE CATALOG cat1;
USE CATALOG cat1;
CREATE USER root@localhost;
GRANT ALL PRIVILEGES ON *.* TO root@localhost;
CREATE DATABASE test;

Limitations

The catalog name is limited to 64 characters. All characters must be in the basic ASCII set: (A-Z, a-z, -, _) This limitations is to be able to run catalogs with engines like InnoDB which has limited space in their internal data dictionary.

Pre-Creating Catalog Directories

CREATE CATALOG works even if the catalog directory already exists (as long as there is no mysql sub directory). This is to allow a database administrator to pre-create the catalog directory and mount it to disk volume and optionally add a configuration file inside the catalog directory. The directory will not be recognized as a catalog or show up in SHOW CATALOGS until the mysql sub directory is created by CREATE CATALOG or mariadb-install-db.

See Also

  • marriadb-install-db can be used to create multiple catalogs with a default root user in one go.

  • DROP CATALOG

This page is licensed: CC BY-SA / Gnu FDL

SHOW CREATE CATALOG

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Syntax

SHOW CREATE CATALOG catalog_name

Description

Shows the CREATE CATALOG statement that creates the given catalog.

Examples

SHOW CREATE CATALOG def;
+---------+-------------------------------------------------------------------------------------------------------+
| Catalog | Create Catalog                                                                                        |
+---------+-------------------------------------------------------------------------------------------------------+
| def     | CREATE CATALOG `def` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT 'default catalog' |
+---------+-------------------------------------------------------------------------------------------------------+

See Also

  • CREATE CATALOG

  • Character Sets and Collations

This page is licensed: CC BY-SA / Gnu FDL

SHOW CATALOGS

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Syntax

SHOW CATALOGS
    [LIKE 'pattern' | WHERE expr]

Description

SHOW CATALOGS lists the catalogs on the MariaDB server host. The LIKE clause, if present on its own, indicates which catalog names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in Extended SHOW.

You see only use SHOW CATALOGS have the CATALOG privilege. Only users of the 'def' schema can have this privilege.

If the server was started with the --skip-show-database option, you cannot use this statement unless you have the SHOW DATABASES privilege.

The list of results returned by SHOW CATALOGS is based on directories in the data directory, which is how MariaDB implements catalogs. It only list directories that have a mysql directory.

The Information Schema Catalogs table also contains catalog information.

Examples

+---------+--------------------+
| Catalog | Comment            |
+---------+--------------------+
| c1      | This is catalog c1 |
| cat2    |                    |
| def     | default catalog    |
+---------+--------------------+
SHOW CATALOGS LIKE 'c%';
+--------------+--------------------+
| Catalog (c%) | Comment            |
+--------------+--------------------+
| c1           | This is catalog c1 |
| cat2         |                    |
+--------------+--------------------+

See Also

  • CREATE CATALOG

  • Character Sets and Collations

  • Information Schema CATALOG Table

This page is licensed: CC BY-SA / Gnu FDL

USE CATALOG

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Syntax

USE CATALOG catalog_name

Description

Changes to another catalog. Can only be done by a super user in the 'def' catalog. Changing catalog will update catalog status and reset all session status.

A tenant (a user in any other catalog than 'def') cannot change to another catalog. However tenants can execute USE CATALOG current_catalog. This is to allow the user to import SQL scripts that use USE CATALOG....

See Also

  • USE database. Changing database.

This page is licensed: CC BY-SA / Gnu FDL

DROP CATALOG

MariaDB starting with 12.0

Catalog support is planned for 12.0.

Syntax

DROP CATALOG catalog_name

Description

Deletes a catalog.

Limitations:

  • DROP CATALOG can only be performed by a super user in the 'def' catalog.

  • The current catalog cannot be dropped.

  • The 'def' catalog cannot be dropped.

When dropping a catalog, all databases and files within that catalog will be deleted.

See Also

  • CREATE CATALOG

This page is licensed: CC BY-SA / Gnu FDL