CREATE TENANT

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.

• For more information about the usage limitations of cloning a tenant, see Clone 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]} | {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
    | 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

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 specify ob_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 specify ob_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 be MYSQL5.7 or MYSQL8.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 specify ob_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 to zone1;zone2,zone3, resource pool pool1, character set utf8mb4, 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 to F@zone1,F@zone2,C@zone3, primary zone zone1;zone2,zone3, resource pool pool1, 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 to zone1, resource pool pool1, 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='%';
  

• Clone a tenant named clone_mysql001 from the standby tenant mysql001. Specify the resource pool name for the new tenant as clone_mysql001_resource_pool and the resource unit as unit001.

  Notice

  The tenant cloning feature depends on log archiving. You need to enable log archiving for the source tenant before you can execute the tenant cloning statement. Additionally, you cannot disable log archiving for the source tenant during the execution of the tenant cloning statement. For more information about log archiving, see Overview of log archiving

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

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

  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 the fields of the views, see DBA_OB_CLONE_PROGRESS and DBA_OB_CLONE_HISTORY.

References

• Overview of tenants

• Overview of user tenants

• Create a tenant

• Clone a tenant

• Cancel a tenant clone task