Purpose
The CREATE TENANT statement is used to create a user tenant. OceanBase Database only supports creating user tenants. The system tenant is automatically created when the cluster is created. The Meta tenant is an internal self-managed tenant of OceanBase Database. Each user tenant corresponds to a Meta tenant, and the lifecycle of the Meta tenant is consistent with that of the user tenant.
The CREATE TENANT statement creates a user tenant with the PRIMARY role by default.
Limitations and considerations
OceanBase Database supports two types of tenants: MySQL compatible mode and Oracle compatible mode. When creating a tenant, you must specify the appropriate type. After a tenant is created, its type cannot be modified.
When creating a tenant, you must assign a resource pool to it. Therefore, you must plan resources in advance and create the resource pool. For more information about how to create a resource pool, see Create a tenant.
Privilege requirements
Only the root user of the sys tenant (root@sys) can create a tenant. Other tenants do not support tenant creation.
Syntax
The syntax for creating a tenant is as follows:
CREATE TENANT [IF NOT EXISTS] tenant_name [tenant_option_list] [set_sys_var];
tenant_option_list:
tenant_option [, tenant_option...]
tenant_option:
LOCALITY [=] 'locality_description'
| PRIMARY_ZONE [=] primary_zone_name
| RESOURCE_POOL_LIST [=] (pool_name [, pool_name...])
| ENABLE_ARBITRATION_SERVICE [=] {True | False}
| {CHARACTER SET | CHARSET} [=] charset_name
| COLLATE [=] collation_name
| COMMENT [=] 'string'
set_sys_var:
{SET | SET VARIABLES | VARIABLES} var_name {TO | =} var_value [,var_name {TO | =} var_value...]
Parameters
Parameter |
Description |
|---|---|
| tenant_name | The name of the tenant. It must be 63 bytes in length and can contain only uppercase and lowercase letters, digits, and underscores. It must start with a letter or underscore and cannot be a keyword of OceanBase Database. |
| IF NOT EXISTS | If the specified tenant name already exists and the IF NOT EXISTS option is not specified, an error will be returned. |
| LOCALITY | Describes the distribution of replicas across zones. The following replica types are supported in the current version:
Example: LOCALITY = 'F@z1,F@z2,F@z3', LOCALITY = 'F@z1,R@z2,C@z3', or LOCALITY = 'F@z1,R@z2,COLUMNSTORE@z3' |
| PRIMARY_ZONE | The primary zone of the tenant. The following values are supported:
If the PRIMARY_ZONE parameter is not specified when creating a tenant, the default value is RANDOM. |
| RESOURCE_POOL_LIST | The list of resource pools allocated to the tenant. This parameter is required when creating a tenant. If multiple resource pools are specified, the UNIT_NUM values of these resource pools must be the same.
NoticeThe |
| ENABLE_ARBITRATION_SERVICE | Specifies whether to enable the arbitration service for the tenant. The default value is False. If the arbitration service is enabled in the cluster, you can enable it for the tenant after the tenant is created. For more information, see Enable the arbitration service for a tenant. |
| CHARACTER SET | CHARSET | The character set of the tenant. For more information, see Character set (MySQL mode) and Character set (Oracle mode). |
| COLLATE | The collation of the tenant in MySQL compatible mode. For more information, see Collation (MySQL mode).
NoteWhen you create a tenant in Oracle compatible mode, you cannot specify the collation of the tenant. If you specify a collation, the syntax will not return an error, but the collation will be overridden with the default collation corresponding to the character set. |
| set_sys_var | The value of the system variable of the tenant. For more information about var_name, see var_name. |
| COMMENT | The comment, which is optional. |
var_name
var_name can be set to the following values:
ob_compatibility_mode: specifies the compatibility mode of the tenant (optional, MySQL or Oracle mode). This parameter can only be set when the tenant is created. If you do not specifyob_compatibility_mode, the default compatibility mode is MySQL.ob_tcp_invited_nodes: specifies the IP address list of the clients that can connect to the tenant. In the example,%indicates that all clients can log in. If you do not specifyob_tcp_invited_nodes, only the IP address of the local host can connect to the tenant.lower_case_table_names: specifies whether the system is case-sensitive. This parameter can only be set when the tenant is created, and only takes effect in MySQL mode. After the tenant is created, you cannot modify this parameter by executing an SQL statement.ob_compatibility_control: specifies the behavior of a feature that has conflicting compatibility behaviors with MySQL. The value can beMYSQL5.7orMYSQL8.0, indicating whether the feature behaves like MySQL 5.7 or MySQL 8.0. When you create a tenant in MySQL mode, you must specify this parameter. This parameter can only be set when the tenant is created. After the tenant is created, you cannot modify this parameter. If you do not explicitly specifyob_compatibility_control, the default behavior is consistent with MySQL 5.7.read_only: specifies whether the tenant is in read-only mode or read/write mode. If you do not set this parameter, the default mode is read/write.
Examples
Create a MySQL compatible tenant named
test_tenant, with the primary zone set tozone1;zone2,zone3, resource poolpool1, character setutf8mb4, and allowing all client IPs to connect to the database.obclient [oceanbase]> CREATE TENANT IF NOT EXISTS test_tenant PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1'), CHARSET='utf8mb4' SET ob_tcp_invited_nodes TO '%';Create a MySQL compatible tenant named
tenant_c, with the locality set toF@zone1,F@zone2,C@zone3, primary zonezone1;zone2,zone3, resource poolpool1, and allowing all client IPs to connect to the database.Notice
After creating a tenant with columnstore replicas, you need to deploy ODP and configure it to route connections to the columnstore replicas. For more information, see Columnstore replicas.
obclient [oceanbase]> CREATE TENANT tenant_c LOCALITY = 'F@zone1,F@zone2,C@zone3', primary_zone='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1') SET ob_tcp_invited_nodes = '%';Create an Oracle compatible tenant named
tenant1, with the primary zone set tozone1, resource poolpool1, and allowing all client IPs to connect to the database.obclient [oceanbase]> CREATE TENANT tenant1 PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('pool1') SET ob_compatibility_mode='oracle', ob_tcp_invited_nodes='%';