Create a tenant

You can create a tenant by using SQL statements.

Create a tenant by using SQL statements

OceanBase Database supports two types of tenants: MySQL and Oracle. To create a tenant by using the CREATE TENANT command, access the sys tenant as the root user (root@sys). After you create a tenant, you can specify the type and whitelist for the tenant.

The following is the syntax for creating a tenant:

CREATE TENANT [IF NOT EXISTS] tenantname
     [tenant_characteristic_list]
     [tenant_variables_list]

tenant_characteristic_list:
    tenant_characteristic [, tenant_characteristic...]

tenant_characteristic:
    COMMENT 'string'
    | {CHARACTER SET | CHARSET} [=] value
    | REPLICA_NUM [=] num
    | ZONE_LIST [=] (zone [, zone])
    | PRIMARY_ZONE [=] zone
    | RESOURCE_POOL_LIST [=] (poolname)
    | {READ ONLY | READ WRITE}

tenant_variables_list:
    SET sys_variables_list
    | SET VARIABLES sys_variables_list
    | VARIABLES sys_variables_list

sys_variables_list:
    sys_variables [, sys_variables...]

sys_variables:
    sys_variable_name = expr

Note:

• If the tenant name already exists and IF NOT EXISTS is not specified, an error will be reported.

• Same as a variable, a tenant name can contain up to 30 characters, which consist of only uppercase letters, lowercase letters, digits, and underscores (). The tenant name must start with a letter or an underscore () and cannot be a keyword of OceanBase Database.

• You can specify a resource pool for the tenant.

• RESOURCE_POOL_LIST is must be specified when you create the tenant.

• RESOURCE_POOL_LIST in the CREATE TENANT command supports only one resource pool at present.

Example 1 :

The following statement creates a MySQL tenant named test_tenant with three replicas. By default, a new tenant is in MySQL mode.

obclient> CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4', replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1')

Example 2 :

The following statement creates an Oracle tenant named test_tenant with three replicas. To create an Oracle tenant, you must use the ob_compatibility_mode parameter and use the OBClient or OceanBase Developer Center (ODC) to connect to the Oracle tenant.

obclient> CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4', replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1') SET ob_compatibility_mode='oracle' 

Example 3 :

The following statement sets the value of the ob_tcp_innvited_nodes variable to %, to allow the tenant to connect to the database from any client IP address after the tenant is created. If you do not modify the value of this variable, the tenant can connect to the database only by using the IP address of the local machine.

obclient> CREATE TENANT IF NOT EXISTS test_tenant charset='utf8mb4', replica_num=3, zone_list=('zone1','zone2','zone3'), primary_zone='zone1;zone2,zone3', resource_pool_list=('pool1') SET ob_tcp_invited_nodes='%' 

Note:

• primary_zone specifies the zone where the partition leader of the table of the tenant is located. For example, primary_ zone =' Zone1; Zone2, Zone3' indicates that the partition leader of the table is in Zone1 for the tenant.

• Zone2 and Zone3 are separated with a comma (,) indicating that Zone2 and Zone3 share the same priority, which is lower than that of Zone1.

• primary_zone can be set to RANDOM(which must be in uppercase) to indicate a random primary zone.

The minimum memory size of a common tenant must be at least 5 GB. Otherwise, the tenant cannot be created. To create a tenant for simple feature tests, you can change the value of alter system __min_full_resource_pool_memory to 1073741824 to allow a minimum memory size of 1 GB for new tenants.