CREATE TENANT|V4.3.1| docs|Distributed Database

CREATE TENANT

Last Updated：2024-06-28 05:30:30 Updated

Purpose

You can use this statement to create a user tenant. OceanBase Database allows you to create only a user tenant. The sys tenant is automatically created when you create a cluster. Meta tenants are used for internal management in OceanBase Database. When you create a user tenant, a corresponding meta tenant is automatically created. The lifecycle of a meta tenant is the same as that of its user tenant.

The default role of a user tenant created by using the CREATE TENANT statement is PRIMARY.

Limitations and considerations

OceanBase Database supports two tenant modes: MySQL and Oracle. You must specify a tenant mode when you create a tenant. You cannot change the tenant mode of a created tenant.
You must allocate resource pools to a tenant when you create it. Therefore, you must plan resources and create resource pools in advance. For more information about how to create a resource pool, see Create a tenant.
For more information about limitations on tenant cloning, see Clone a tenant.

Required privileges

You can create a tenant only as the root user of the sys tenant (namely root@sys).

Syntax

The syntax for creating a tenant is as follows:

CREATE TENANT { {[IF NOT EXISTS] tenant_name [tenant_option_list] [set_sys_var]} | {clone_tenant_clause} };

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
    | {READ ONLY | READ WRITE}
    | COMMENT [=] 'string'

set_sys_var:
    {SET | SET VARIABLES | VARIABLES} var_name {TO | =} var_value [,var_name {TO | =} var_value...]

clone_tenant_clause:
    new_tenant_name FROM source_tenant_name WITH
      RESOURCE_POOL [=] resource_pool_name,
      UNIT [=] unit_config

Parameters

Parameter	Description
tenant_name	The name of the tenant. The name must start with a letter or an underscore (_) and cannot exceed 63 characters in length. It can contain letters, digits, and underscores (_), and cannot contain reserved keywords of OceanBase Database.
IF NOT EXISTS	If the tenant name already exists and `IF NOT EXISTS` is not specified, an error will be returned.
LOCALITY	The distribution of replicas across zones. For example, `F@z1,F@z2,F@z3` indicates that `z1`, `z2`, and `z3` are full-featured replicas.
PRIMARY_ZONE	The primary zone of the tenant. Valid values: `RANDOM`: specifies all zones where the tenant resides as its primary zones, which means that all the zones have the same priority. zone_name: specifies a specific zone as the primary zone. If you do not specify this parameter when you create a tenant, the default value `RANDOM` is used.
RESOURCE_POOL_LIST	The list of resource pools allocated to the tenant. This parameter is required when you create a tenant. If the tenant has multiple resource pools, they must have the same `UNIT_NUM` value, which indicates the number of resource units. Notice The zones of resource pools allocated to the same tenant must not overlap.
ENABLE_ARBITRATION_SERVICE	Specifies whether to enable the arbitration service for the tenant. If you do not specify this parameter, the default value `False` is used. If the cluster has an arbitration service and you do not enable the arbitration service for a tenant when you create the tenant, you can enable the arbitration service for the tenant later. For more information, see Enable the arbitration service for a tenant.
CHARACTER SET \| CHARSET	The character set for the tenant. For more information about character sets, see Character sets (MySQL mode) and Character sets (Oracle mode).
COLLATE	The collation for the tenant. For more information about collations, see Collations (MySQL mode) and Collations (Oracle mode).
READ ONLY \| READ WRITE	Specifies whether the tenant is a ready-only tenant or a read/write tenant. By default, if you do not specify this parameter, the tenant is a read/write tenant.
set_sys_var	The tenant-level system variables. The variables are described as follows: `ob_compatibility_mode`: the compatibility mode of the tenant. Valid values are `MySQL` and `Oracle`. You must specify this variable when you create a tenant. If you do not specify this variable, the default value `MySQL` is used. `ob_tcp_invited_nodes`: the allowlist of client IP addresses allowed to connect to the tenant. In the following example, the percent sign (`%`) indicates that all client IP addresses are allowed to connect to the tenant. If you do not specify this variable, only the IP address of the current client is allowed to connect to the tenant. `lower_case_table_names`: the case sensitivity. This variable can be specified only when you use the `CREATE TENANT` statement to create a tenant in MySQL mode. After the tenant is created, you cannot modify this variable by using a statement.
COMMENT	Optional. The comment.
clone_tenant_clause	The clause for cloning the tenant.
new_tenant_name	The name of the new tenant.
source_tenant_name	The name of the source tenant.
resource_pool_name	The name of the resource pool. When you clone a tenant, a resource pool is automatically created for the new tenant based on the resource distribution of the source tenant.
unit_config	The resource unit specifications of the resource pool for the new tenant.

Examples

Create a MySQL tenant named test_tenant. Set the primary zone to zone1;zone2,zone3, resource pool to pool1, and character set to utf8mb4. Allow all client IP addresses to connect to the tenant.
```
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 an Oracle tenant named tenant1. Set the primary zone to zone1 and resource pool to pool1. Allow all client IP addresses to connect to the tenant.

obclient [oceanbase]>  CREATE TENANT tenant1 PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('pool1') SET ob_compatibility_mode='oracle',  ob_tcp_invited_nodes='%';

Clone the mysql001 tenant and name the new tenant as clone_mysql001. Specify the resource pool name clone_mysql001_resource_pool and the resource unit name unit001 for the new tenant.

Notice

You must enable log archiving on the source tenant to clone the tenant. During the execution of the tenant cloning statement, you cannot disable log archiving for the source tenant. For more information about log archiving, see Overview.

CREATE TENANT clone_mysql001 FROM mysql001
      WITH
      RESOURCE_POOL = clone_mysql001_resource_pool,
      UNIT = unit001;

When you execute a tenant cloning statement in the sys tenant, a corresponding clone task is generated. You can view the execution status of the clone task in the DBA_OB_CLONE_PROGRESS and DBA_OB_CLONE_HISTORY views.

Here is an example:

obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_CLONE_HISTORY WHERE CLONE_TENANT_NAME = 'clone_mysql001'\G

The return result is as follows:

*************************** 1. row ***************************
        CLONE_JOB_ID: 1706248530701634201
            TRACE_ID: YB42AC1E87C9-00060F1AD6DF0264-0-0
    SOURCE_TENANT_ID: 1002
  SOURCE_TENANT_NAME: mysql001
    CLONE_TENANT_ID: 1012
  CLONE_TENANT_NAME: clone_mysql001
  TENANT_SNAPSHOT_ID: 1706248530759745885
TENANT_SNAPSHOT_NAME: _inner_snapshot$1706248530759740679
    RESOURCE_POOL_ID: 1006
  RESOURCE_POOL_NAME: clone_mysql001_resource_pool
    UNIT_CONFIG_NAME: unit001
        RESTORE_SCN: 1706248531829799503
              STATUS: CLONE_SYS_SUCCESS
      CLONE_JOB_TYPE: FORK
    CLONE_START_TIME: 2024-01-26 13:55:30.705699
CLONE_FINISHED_TIME: 2024-01-26 13:56:00.141434
            RET_CODE: 0
      ERROR_MESSAGE: NULL
1 row in set

For more information about columns in the views, see DBA_OB_CLONE_PROGRESS and DBA_OB_CLONE_HISTORY.

CREATE TENANT

Purpose

Limitations and considerations

Required privileges

Syntax

Parameters

Notice

Examples

Notice

References