Purpose
The CREATE TENANT statement is used to create a user tenant. OceanBase Database only supports the creation of 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. For each user tenant created, a corresponding Meta tenant is created, and its lifecycle is consistent with that of the user tenant.
The CREATE TENANT statement creates a tenant with the default tenant role of PRIMARY.
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 tenant type. After a tenant is created, its type cannot be modified.
When creating a tenant, you must allocate 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 cannot create a tenant.
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 can be up to 63 bytes in length and can contain only uppercase and lowercase letters, digits, and underscores. It must start with a letter or an underscore and cannot be a reserved 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 | The distribution of replicas in 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 valid values are as follows:
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 all 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 the arbitration service 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 setting will be overridden by the default collation corresponding to the character set. |
| set_sys_var | The value of the system variable for the tenant. For more information about var_name, see var_name. |
| COMMENT | The comment. This parameter is optional. |
var_name
var_name can be set to the following values:
ob_compatibility_mode: specifies the compatibility mode of the tenant (MySQL or Oracle mode). This parameter can be specified only when the tenant is created. If this parameter is not specified, the default compatibility mode is MySQL.ob_tcp_invited_nodes: specifies the IP addresses of the clients that are allowed to connect to the tenant. In the example,%indicates that all clients are allowed to connect to the tenant. If this parameter is not specified, only the IP address of the local host is allowed to connect to the tenant.lower_case_table_names: specifies whether to use case-sensitive table names. This parameter can be specified only when the tenant is created. If this parameter is not specified, the default value isOFF. This parameter is effective only in MySQL mode.ob_compatibility_control: specifies the behavior of a feature that has conflicting behaviors in MySQL 5.7 and MySQL 8.0. The value can be set toMYSQL5.7orMYSQL8.0. If this parameter is not specified, the default value isMYSQL5.7.read_only: specifies whether the tenant is in read-only mode or read/write mode. If this parameter is not specified, the default value isOFF, indicating that the tenant is in read/write mode.
Examples
Create a MySQL compatible tenant named
test_tenantwith the primary zone set tozone1;zone2,zone3, resource poolpool1, and character setutf8mb4. Allow 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_cwith the locality set toF@zone1,F@zone2,C@zone3, primary zonezone1;zone2,zone3, resource poolpool1, and allow 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
tenant1with the primary zone set tozone1and resource poolpool1. Allow 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='%';