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 internally 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 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 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 tenants.
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 must start with a letter or an underscore. It can contain only uppercase and lowercase letters, digits, and underscores. It cannot be a keyword of OceanBase Database. |
| IF NOT EXISTS | If the tenant to be created 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 supported replica types in the current version are as follows:
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 supported values are as follows:
If the primary zone 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 this option is not specified. In a cluster with the arbitration service, if the arbitration service is not enabled when creating a tenant, you can enable it after the tenant is created. For more information, see Enable the arbitration service for a tenant. |
| CHARACTER SET | CHARSET | The character set for the tenant. For more information, see Character set (MySQL-compatible mode) and Character set (Oracle-compatible mode). |
| COLLATE | The collation for the tenant in MySQL-compatible mode. For more information, see Collation (MySQL-compatible mode).
NoteWhen creating a tenant in Oracle-compatible mode, you cannot specify the collation for 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 for the tenant, which 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-compatible mode). This parameter can only be set when the tenant is created. If you do not set this parameter, 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 you do not set this parameter, only the IP address of the local host is allowed to connect to the tenant.lower_case_table_names: specifies whether the database is case-sensitive. This parameter can only be set when the tenant is created, and only takes effect in MySQL-compatible mode. After the tenant is created, you cannot modify this parameter by executing an SQL statement.ob_compatibility_control: controls which MySQL-compatible behavior to use when a feature has conflicting compatibility behavior between MySQL 5.7 and MySQL 8.0. The value can beMYSQL5.7orMYSQL8.0, indicating whether to match the behavior of MySQL 5.7 or MySQL 8.0. You must specify this variable when creating a MySQL-compatible tenant. It can only be set at tenant creation and cannot be modified afterward. If you do not explicitly specifyob_compatibility_control, the default compatibility behavior is the same as 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 value is read/write mode.
Examples
Create a MySQL-compatible tenant named
test_tenantwith the primary zonezone1;zone2,zone3, resource poolpool1, and character setutf8mb4, allowing all client IP addresses 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 localityF@zone1,F@zone2,C@zone3, primary zonezone1;zone2,zone3, resource poolpool1, and allowing all client IP addresses to connect to the database.Notice
After you create a tenant that contains columnstore replicas, you must 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 zonezone1and resource poolpool1, allowing all client IP addresses 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='%';