PolarDB:Multi-tenant management

How it works

Terms

Tenant: The tenant concept is introduced to support a multi-tenant architecture within a database cluster and serves as a hierarchical layer between the cluster and the database or user level. Tenants are classified into two types: system tenant and regular tenant.

• System tenant: designed to accommodate users in the original mode. By default, pre-existing databases and users in a cluster belong to the system tenant. When a user of the system tenant connects to the cluster, the user can access the databases of all tenants if the user has the required database permissions.

• Regular tenant: Regular tenants are created under the system tenant. Databases and users among regular tenants are completely isolated and cannot access each other. Regular tenants also cannot access the databases of the system tenant. In the context of CPU resource scheduling, regular tenants are classified into exclusive tenants and shared tenants based on the value of the min_cpu parameter.

  • Exclusive tenant: The value of the min_cpu parameter is greater than 0. The system ensures that exclusive tenants always have CPU resources that are not less than the value of the min_cpu parameter.

  • Shared tenant: The value of the min_cpu parameter is 0.

  You can change the value of the min_cpu parameter to change the tenant type between exclusive and shared.

Resource configuration: defines the resources that a tenant can use. This concept is introduced to manage resource isolation and allocation among tenants. Only CPU resources are supported for isolation and scheduling.

Prerequisites

To enable the multi-tenant mode, your cluster must meet the following requirements:

The Database Engine parameter is set to MySQL 8.0.2or later. The Database Edition parameter is set to Enterprise Edition. The Edition parameter is set to Cluster Edition. The nodes in the cluster have the same specifications.

Precautions

• You cannot synchronize data by using binlogs in multi-tenant mode.

• You cannot partially revoke user permissions in multi-tenant mode.

Quick start

1. Enable the standalone multi-tenant mode on the cluster.

   Note

   The multi-tenant mode is in canary release. If you want to use the feature, you can join the DingTalk group 59535005981 to obtain technical support. After you enable the feature, you must restart the cluster for the feature to take effect.

2. Use the privileged account to connect to the cluster.

3. Create resource configurations r1 and r2.

   CREATE resource_config r1 min_cpu 1 max_cpu 2;
   CREATE resource_config r2 min_cpu 1 max_cpu 4;

   Note

   • Configure the min_cpu and max_cpu parameters based on the node specifications of your cluster.

   • Make sure that the value of the min_cpu parameter of each tenant does not exceed the number of CPU cores of the cluster node specifications minus 1. You must reserve at least one CPU core for the system tenant. Otherwise, tenants may fail to be bound to the resource configurations.

4. Create tenants tn1 and tn2. When you create a tenant, bind one of the preceding resource configurations to the tenant.

   CREATE tenant tn1 resource_config r1;
   CREATE tenant tn2 resource_config r2;

   Note

   Make sure that the sum of the values of the min_cpu parameter in the resource configurations of all tenants does not exceed the number of CPU cores of the cluster node specifications minus 1.

5. Create users u1 and u2 and databases db1 and db2.

   -- Create user u1 in tenant tn1.
   CREATE USER 'u1@tn1' IDENTIFIED BY 'password';
   
   -- Create database db1 in tenant tn1.
   CREATE DATABASE `db1@tn1`;
   
   -- Create user u2 in tenant tn2.
   CREATE USER 'u2@tn2' IDENTIFIED BY 'password';
   
   -- Create database db2 in tenant tn2.
   CREATE DATABASE `db2@tn2`;

   Note

   If you use the privileged account to create a user and a database, you must add the @ tenant name suffix to the username or database name.

6. Grant permissions to users u1 and u2.

   -- Grant permissions on tenant tn1 to user u1. 
   GRANT ALL PRIVILEGES ON `%@tn1`.* TO 'u1@tn1'@'%' WITH GRANT OPTION;
   
   -- Grant permissions on tenant tn2 to user u2. 
   GRANT ALL PRIVILEGES ON `%@tn2`.* TO 'u1@tn2'@'%' WITH GRANT OPTION;

7. You can use users u1@tn1 and u2@tn2 to verify data and resource isolation between the tenants tn1 and tn2.

8. (Optional) View the tenants, users, and databases that you created.

   • Tenants: Use the privileged account to connect to the cluster and then execute the SELECT statement to view information about tenants. Example:

     SELECT * FROM mysql.tenants;

   • Users: Log on to the PolarDB console. Find the cluster on the Clusters page. Go to the Settings and Management > Accounts page of the cluster to view information about the created users.

     • Account names that do not have a tenant name appended after an @ symbol are the users of the system tenant.

     • Account names that include a tenant name appended after an @ symbol are are the users of the corresponding tenant.

   • Databases: Log on to the PolarDB console. Find the cluster on the Clusters page. Go to the Settings and Management > Databases page of the cluster to view information about the databases.

     • Database names that do not have a tenant name appended after an @ symbol are the databases of the system tenant.

     • Database names that include a tenant name appended after an @ symbol are the databases of the corresponding tenant.

Features

CREATE resource_config <resource_config_name> min_cpu <min_cpu> max_cpu <min_cpu>;

CREATE resource_config r1 min_cpu 0 max_cpu 1;
CREATE resource_config r2 min_cpu 1 max_cpu 4;

SELECT * FROM mysql.tenant_resource_config;

+----------------------+-------------------------+-------------------------+
| resource_config_name | resource_config_min_cpu | resource_config_max_cpu |
+----------------------+-------------------------+-------------------------+
| r1                   |                       0 |                       1 |
| r2                   |                       1 |                       4 |
+----------------------+-------------------------+-------------------------+

ALTER resource_config <resource_config_name> min_cpu <min_cpu> max_cpu <min_cpu>;

ALTER resource_config r2 min_cpu 1 max_cpu 2;

DROP resource_config <resource_config_name>;

DROP resource_config r1;

CREATE tenant <tenant_name> resource_config <resource_config_name>;

CREATE tenant tn1 resource_config r1;

SELECT * FROM mysql.tenants;

+-------------+----------------------+
| tenant_name | resource_config_name |
+-------------+----------------------+
| tn1         | r1                   |
+-------------+----------------------+

ALTER tenant <tenant_name> resource_config <resource_config_name>;

ALTER tenant tn1 resource_config r2;

DROP tenant <tenant_name>;

DROP tenant tn1;

CREATE USER <user_name>@<tenant_name> [IDENTIFIED BY <password>];

-- without a password
CREATE USER 'u1@tn1';

-- with a password
CREATE USER 'u1@tn1' IDENTIFIED BY 'password';

DROP USER <user_name>@<tenant_name>;

DROP USER 'u1@tn1';

CREATE USER <user_name> [IDENTIFIED BY <password>];

-- without password
CREATE USER 'u2';

-- with a password
CREATE USER 'u2' IDENTIFIED BY 'password';

DROP USER <user_name>;

DROP USER 'u2';

mysql -h <host> -P <port> -u <user_name>@<tenant_name> -p

mysql -h pc... -P 3306 -u u1@tn1 -p
Enter password: 

CREATE DATABASE <database_name>@<tenant_name>;

CREATE DATABASE `db1@tn1`;

DROP DATABASE <database_name>@<tenant_name>;

DROP DATABASE `db1@tn1`;

CREATE DATABASE <database_name>;

CREATE DATABASE `db2`;

DROP DATABASE <database_name>;

DROP DATABASE `db2`;