PolarDB:Multi-tenant management

How it works

Key concepts

Tenant: A tenant resides below the database cluster level and above the database and user level. Tenants are classified as either a system tenant or a regular tenant.

• System tenant: The system tenant is designed to support existing users and databases for backward compatibility. Pre-existing databases and users in a cluster belong to the system tenant by default. A user from the system tenant can access the databases of all tenants, provided they have the necessary permissions.

• Regular tenant: A regular tenant must be created under the system tenant. Databases and users are completely isolated between regular tenants. Regular tenants also cannot access databases that belong to the system tenant. For CPU resource scheduling, regular tenants are classified as an exclusive tenant or a shared tenant based on whether the value of min_cpu is greater than 0.

  • Exclusive tenant: min_cpu > 0. The system guarantees that the CPU resources for this tenant will not fall below the min_cpu value at any time.

  • Shared tenant: The min_cpu value is 0.

  You can convert between an exclusive tenant and a shared tenant by adjusting the value of the min_cpu parameter.

Resource configuration: A resource configuration defines the resources that a tenant can use, enabling resource isolation and scheduling among tenants. Currently, only CPU resources can be isolated and scheduled.

Prerequisites

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

The database engine is MySQL 8.0.2 or later, the database edition is Enterprise Edition, the Edition is Cluster Edition, and all cluster nodes must have identical specifications.

Limitations

• Binlog synchronization for tenants is not supported.

• Partial permission revocation is not supported.

Quick start

1. Enable standalone multi-tenant mode.

   Note

   The multi-tenant mode is currently in canary release. To enable this feature, contact technical support through the specified DingTalk group. After the feature is enabled, you must restart the target cluster for the changes to take effect.

   DingTalk group number: 59535005981.

2. Connect to the database using a privileged account.

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

   • Set the min_cpu and max_cpu values based on the node specifications of your cluster.

   • The min_cpu value for any tenant cannot exceed the number of cores on a cluster node minus one. You must reserve at least one core for the system tenant. Otherwise, binding the tenant to the resource configuration will fail during tenant creation.

4. Create tenants tn1 and tn2. When you create the tenants, you must bind them to the resource configurations that you created.

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

   Note

   Ensure that the sum of the min_cpu values from the resource configurations of all tenants does not exceed the number of cores on a cluster node minus one.

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

   When using a privileged account to create a user or database, you must append 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 'u2@tn2'@'%' WITH GRANT OPTION;

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

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

   • Tenants: Connect to the database using a privileged account and run the SELECT statement to view tenants. Example:

     SELECT * FROM mysql.tenants;

   • Users: Go to the PolarDB console and open Settings and Management > Account Management.

     • Users with an Username that does not have the @ suffix belong to the system tenant.

     • Users with an Username that has the @ suffix belong to the corresponding regular tenant.

   • Databases: Go to the PolarDB console and open Settings and Management > Databases.

     • Databases with a Database Name that does not have the @ suffix belong to the system tenant.

     • Databases with a Database Name that has the @ suffix belong to the corresponding regular tenant.

Features

CREATE resource_config <resource_config_name> min_cpu <min_cpu> max_cpu <max_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 <max_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 '@' [IDENTIFIED BY ''];

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

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

DROP USER '@';

DROP USER 'u1@tn1';

CREATE USER '' [IDENTIFIED BY ''];

-- Without a password
CREATE USER 'u2';

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

DROP USER '';

DROP USER 'u2';

mysql -h  -P  -u @ -p

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

CREATE DATABASE `@`;

CREATE DATABASE `db1@tn1`;

DROP DATABASE `@`;

DROP DATABASE `db1@tn1`;

CREATE DATABASE ``;

CREATE DATABASE `db2`;

DROP DATABASE ``;

DROP DATABASE `db2`;