CREATE TENANT

Purpose

You can use the CREATE TENANT 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

var_name

Common values of var_name 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 examples, the percent sign (%) indicates that all clients can connect to this tenant. If ob_tcp_invited_nodes is not specified, by default, the tenant is accessible only to the clients on the server where the tenant is hosted.
• 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.
• ob_compatibility_control: specifies whether OceanBase Database is to keep compatible with MySQL 5.7 or MySQL 8.0 when a MySQL compatibility behavior conflict occurs. You must specify this variable when you create a tenant in MySQL mode. It cannot be modified after the tenant is created. If ob_compatibility_control is not explicitly specified, OceanBase Database keeps compatible with MySQL 5.7 by default.

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 job is generated. You can view the execution status of the clone job 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.

References

• Tenant introduction

• User tenant introduction

• Create a tenant

• Clone a tenant

• Cancel tenant cloning