Purpose
You can use this statement to create a tenant.
You can also create a standby tenant in OceanBase Database. The statement for creating a standby tenant is a synchronization statement executed in the session of the sys tenant, and uses the DDL timeout.
Note
To execute this statement, you must log on to the sys tenant as the root user.
Syntax
The syntax for creating a tenant is as follows:
CREATE TENANT [IF NOT EXISTS] tenant_name
[tenant_characteristic_list] [opt_set_sys_var];
tenant_characteristic_list:
tenant_characteristic [, tenant_characteristic...]
tenant_characteristic:
COMMENT 'string'
| {CHARACTER SET | CHARSET} [=] charset_name
| COLLATE [=] collation_name
| ZONE_LIST [=] (zone_name [, zone_name...])
| PRIMARY_ZONE [=] zone_name
| DEFAULT TABLEGROUP [=] {NULL | tablegroup_name}
| RESOURCE_POOL_LIST [=](pool_name [, pool_name...])
| LOCALITY [=] 'locality description'
opt_set_sys_var:
{SET | SET VARIABLES | VARIABLES} system_var_name = expr [,system_var_name = expr] ...
The syntax for creating a standby tenant is as follows:
CREATE STANDBY TENANT [IF NOT EXISTS] tenant_name
LOG_RESTORE_SOURCE [=] string_value
[tenant_characteristic_list];
tenant_characteristic_list:
tenant_characteristic [, tenant_characteristic...]
tenant_characteristic:
COMMENT 'string'
| PRIMARY_ZONE [=] zone_name
| RESOURCE_POOL_LIST [=](pool_name [, pool_name...])
| LOCALITY [=] 'locality description'
Parameters
| Parameter | Description |
|---|---|
| tenant_name | The name of the tenant. The name cannot exceed 128 characters in length. It must contain only uppercase and lowercase letters, digits, and underscores. The name must start with a letter or an underscore (_) and must not contain reserved keywords of OceanBase Database. |
| IF NOT EXISTS | If the tenant name already exists and IF NOT EXISTS is not specified, an error is returned. |
| RESOURCE_POOL_LIST | The list of resource pools. This parameter is required when you create a tenant. |
| DEFAULT TABLEGROUP | The default table group of the tenant. When you set it to NULL, the system disables the default table group. If this parameter is not specified, the default value is NULL. |
| COMMENT | The comment. |
| CHARACTER SET | CHARSET | The character set. |
| COLLATE | The collation. |
| PRIMARY_ZONE | The name of the primary zone of the tenant. |
| 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. |
| system_var_name | The system variables of the tenant. The ob_compatibility_mode variable specifies the compatibility mode (MySQL or Oracle) of the tenant. You can specify the compatibility mode only when you create the tenant. If you do not specify this parameter, the compatibility mode is MySQL by default. The lower_case_table_names variable specifies whether object names are case-sensitive. The variable takes effect only when you use the CREATE TENANT statement to create a MySQL tenant and cannot be modified through an SQL statement after the tenant is created. |
| STANDBY | Creates a standby tenant. After a standby tenant is created, it will automatically enter the continuous synchronization state. |
| LOG_RESTORE_SOURCE | Configures the log restore source. For more information, see LOG RESTORE SOURCE. |
Examples
Create a MySQL tenant with three replicas.
obclient> CREATE TENANT IF NOT EXISTS test_tenant CHARSET='utf8mb4', ZONE_LIST=('zone1','zone2','zone3'), PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1');Create an Oracle tenant.
obclient> CREATE TENANT IF NOT EXISTS tenant1 ZONE_LIST=('zone1'), PRIMARY_ZONE='zone1', RESOURCE_POOL_LIST=('pool1') SET ob_compatibility_mode='oracle', set ob_tcp_invited_nodes='%';Enable case sensitivity when you create a tenant. Then, table names are stored in the specified letter case and compared in a case-sensitive manner.
obclient> CREATE TENANT oblog_tt replica_num = 1, resource_pool_list=('oblog_pool') set ob_tcp_invited_nodes='%', lower_case_table_names=0;Create a standby tenant named
standby_tenantin the sys tenant.Create a unit named
1C2G.obclient> CREATE RESOURCE UNIT 1C2G MAX_CPU 1, MEMORY_SIZE = '2G'; Query OK, 0 rows affectedCreate a resource pool named
pool_for_standby.obclient> CREATE RESOURCE POOL pool_for_standby UNIT = '1C2G', UNIT_NUM = 1, ZONE_LIST = ('zone1', 'zone2', 'zone3'); Query OK, 0 rows affectedObtain the value of
LOG_RESTORE_SOURCEand create an empty tenant.obclient> CREATE STANDBY TENANT IF NOT EXISTS standby_tenant LOG_RESTORE_SOURCE = 'SERVICE=11.xxx.xxx.xxx:17855;11.xxx.xxx.xxx:17857;11.xxx.xxx.xxx:17859 USER=test@mysql PASSWORD=******' RESOURCE_POOL_LIST=('pool_for_standby'); Query OK, 0 rows affectedView the tenant status in the
DBA_OB_TENANTSview. IfTENANT_ROLEisSTANDBYandSTATUSisNORMAL, the tenant status is normal and is created successfully.obclient> SELECT TENANT_NAME, TENANT_TYPE, CREATE_TIME, STATUS, TENANT_ROLE,SCN_TO_TIMESTAMP(SYNC_SCN) FROM DBA_OB_TENANTS WHERE TENANT_NAME = 'standby_tenant'; +----------------+-------------+----------------------------+--------+-------------+----------------------------+ | TENANT_NAME | TENANT_TYPE | CREATE_TIME | STATUS | TENANT_ROLE | SCN_TO_TIMESTAMP(SYNC_SCN) | +----------------+-------------+----------------------------+--------+-------------+----------------------------+ | standby_tenant | USER | 2023-04-14 21:06:48.787550 | NORMAL | STANDBY | 2023-04-14 21:12:59.183293 | +----------------+-------------+----------------------------+--------+-------------+----------------------------+ 1 row in set