CREATE TENANT

Description

The CREATE TENANT statement creates a user tenant. OceanBase Database only supports creating user tenants. When a cluster is created, a system tenant is automatically created for the cluster. Meta tenants are internal, self-managed tenants created for each user tenant upon their creation. The life cycle of a meta tenant is the same as that of the corresponding user tenant.

Tenants created by using the CREATE TENANT statement default to the PRIMARY tenant role.

Limitations and considerations

• OceanBase Database supports MySQL- and Oracle-compatible tenants. When you create a tenant, you must specify the appropriate tenant type. After the tenant is created, you cannot change its type.

• When you create a tenant, you must assign a resource pool to the tenant. You must complete resource planning and create a resource pool before you create a tenant. For more information, see Create a tenant.

• For more information about the limitations on cloning a tenant, see Clone a tenant.

Privilege requirements

Only the root user of the sys tenant (root@sys) can 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]} | {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

Parameter description

var_name

The following are the common values of var_name:

• ob_compatibility_mode: specifies the compatibility mode (MySQL or Oracle) of a tenant. It can be specified only when the tenant is created. If ob_compatibility_mode is not specified, the default compatibility mode is MySQL.
• ob_tcp_invited_nodes: specifies the allowlist for connecting to a tenant, that is, the client IP addresses that are allowed to connect to the tenant. In the example, % indicates all client IP addresses. If ob_tcp_invited_nodes is not specified, the default allowlist for connecting to the tenant allows only the local IP address.
• lower_case_table_names: controls case sensitivity. It can be specified only when the tenant is created, and it takes effect only in MySQL mode. After the tenant is created, you cannot modify it through an SQL statement.
• ob_compatibility_control: controls the behavior of features when a MySQL compatibility behavior conflict occurs. The current values are MYSQL5.7 and MYSQL8.0, indicating the behavior is consistent with MySQL 5.7 or MySQL 8.0 respectively. When you create a tenant in MySQL mode, you must specify this variable, and it can be specified only when the tenant is created. After the tenant is created, you cannot modify it. If you do not explicitly specify ob_compatibility_control, the default behavior is consistent with MySQL 5.7.

Examples

• Create a MySQL-compatible tenant named test_tenant, set the primary zone to zone1;zone2,zone3, the resource pool to pool1, the character set to utf8mb4, and allow access from all client IP addresses.

  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, set the locality to F@zone1,F@zone2,C@zone3, the primary zone to zone1;zone2,zone3, the resource pool to pool1, and allow access from all client IP addresses.

  Notice

  After you create a tenant with columnar replicas, you need to deploy an ODP and perform a series of configurations to route connections to the columnar replicas. For more information, see Columnar 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, set the primary zone to zone1, the resource pool to pool1, and allow access from all client IP addresses.

  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 and set the standby tenant name to mysql001. The resource pool name of the new tenant is set to clone_mysql001_resource_pool, and the resource unit is set to unit001.

  Notice

  The tenant cloning feature depends on log archiving. To clone a tenant, log archiving must be enabled for the source tenant, and log archiving cannot be disabled during the execution of the tenant cloning statement. For more information about log archiving, see Overview of log archive.

  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, a cloning task is generated. You can query the DBA_OB_CLONE_PROGRESS and DBA_OB_CLONE_HISTORY views for the status of the cloning task.

  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 the columns in 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 cloning task