You can create a tenant by using an SQL statement.
Applicability
OceanBase Database Community Edition provides only MySQL mode.
Limitations
You can create user tenants only from the sys tenant.
Create a tenant
OceanBase Database supports MySQL tenants and Oracle tenants.
Tenant creation statement
SQL syntax for creating a tenant:
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} [=] charsetname
| COLLATE [=] collationname
| ZONE_LIST [=] (zone [, zone...])
| PRIMARY_ZONE [=] zone
| DEFAULT TABLEGROUP [=] {NULL | tablegroup}
| RESOURCE_POOL_LIST [=](poolname [, poolname...])
| LOCALITY [=] 'locality description'
| ENABLE_ARBITRATION_SERVICE = {true | false}
opt_set_sys_var:
{SET | SET VARIABLES | VARIABLES} system_var_name = expr [,system_var_name = expr] ...
The following table describes the parameters in the syntax.
| Parameter | Description |
|---|---|
| IF NOT EXISTS | This parameter is optional. If the tenant name already exists and IF NOT EXISTS is not specified, an error will be reported. |
| tenant_name | Like a variable, a tenant name can contain up to 128 characters, which can be letters, digits, and underscores (_). The tenant name must start with a letter or an underscore (_) and cannot be a keyword of OceanBase Database. For information about the keywords supported by OceanBase Database, see Reserved keywords and Reserved keywords. |
| RESOURCE_POOL_LIST | This parameter is required. You can allocate only one resource pool to a tenant when you create the tenant. If you want to add multiple resource pools for a tenant, you can modify the resource pool of the tenant after the tenant is created.
Note |
| DEFAULT TABLEGROUP | Specifies 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 for the tenant. |
| CHARACTER SET | CHARSET | The character set of the tenant. For more information about the character sets, see Character sets. |
| COLLATE | The collation of the tenant. For more information about collation, see Collation. |
| ZONE_LIST | The zones of the tenant. |
| PRIMARY_ZONE | The primary zone of the tenant. The primary zone indicates a preferred location for scheduling the leader. If a primary zone is specified, the leader tends to be scheduled to the primary zone. The primary zone is essentially a list of zones. When the primary zone list contains multiple zones, zones separated with semicolons (;) are arranged in descending order of priorities, and zones separated with commas (,) have the same priority. For example, primary_zone = 'zone1; zone2, zone3' indicates that the partition leaders of the tables of the tenant are located in zone1, zone1 has a higher priority than zone2 and zone3, and zone2 and zone3 have the same priority.
Notice |
| LOCALITY | The distribution of replicas across the zones. For example, F@z1,F@z2,F@z3,F@z4 indicates that z1, z2, z3, and z4 are full-featured replicas. |
| ENABLE_ARBITRATION_SERVICE | Specifies whether to enable the arbitration service for the tenant. If this parameter is not specified, the arbitration service is disabled by default. You can enable the arbitration service after the tenant is created. For more information, see Enable the arbitration service. |
| system_var_name | Optional. Specifies the system variables of the tenant.
|
Examples
Create a MySQL tenant named
test_tenantwith two replicas and with the arbitration service enabled. By default, a new tenant is created in MySQL mode.obclient> CREATE TENANT IF NOT EXISTS test_tenant CHARSET='utf8mb4', ZONE_LIST=('zone1','zone2'), PRIMARY_ZONE='zone1;zone2', RESOURCE_POOL_LIST=('pool1');Create an Oracle tenant named
test_tenantwith three replicas.When you create an Oracle tenant, you must set the
ob_compatibility_modeparameter to specify the compatibility mode of the tenant.obclient> CREATE TENANT IF NOT EXISTS test_tenant CHARSET='utf8mb4', ZONE_LIST=('zone1','zone2','zone3'), PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1') SET ob_compatibility_mode='oracle';Create a tenant accessible to any client IP addresses.
obclient> CREATE TENANT IF NOT EXISTS test_tenant CHARSET='utf8mb4',ZONE_LIST=('zone1','zone2','zone3'), PRIMARY_ZONE='zone1;zone2,zone3', RESOURCE_POOL_LIST=('pool1') SET ob_tcp_invited_nodes='%' ;
Verify whether the tenant is created
After you create a tenant, you can query the oceanbase.DBA_OB_TENANTS view to verify whether the tenant exists in the cluster.
Log on to the
systenant as therootuser.Execute the following statement to check whether the tenant is created in the current cluster.
Sample code:
obclient> SELECT * FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'test_tenant'\G *************************** 1. row *************************** TENANT_ID: 1004 TENANT_NAME: test_tenant TENANT_TYPE: USER CREATE_TIME: 2023-02-06 13:38:27.626314 MODIFY_TIME: 2023-02-06 13:38:45.005930 PRIMARY_ZONE: zone1;zone2,zone3 LOCALITY: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 PREVIOUS_LOCALITY: NULL COMPATIBILITY_MODE: ORACLE STATUS: NORMAL IN_RECYCLEBIN: NO LOCKED: NO TENANT_ROLE: PRIMARY SWITCHOVER_STATUS: NORMAL SWITCHOVER_EPOCH: 0 SYNC_SCN: 1678329060764126878 REPLAYABLE_SCN: 1678329060764126878 READABLE_SCN: 1678329060621559803 RECOVERY_UNTIL_SCN: 4611686018427387903 LOG_MODE: ARCHIVELOG ARBITRATION_SERVICE_STATUS: DISABLED 1 row in set
What to do next
After a tenant is created, the password of the administrator user is empty by default. The administrator user of a MySQL tenant is root and that of an Oracle tenant is SYS. You can use the obclient -h10.10.10.1 -P2883 -uusername@tenantname#clustername -p -A command to log on to the database and then change the password of the administrator user. For more information about how to change the user password, see Change a user password (Oracle Mode) and Change a user password (MySQL Mode).
For more information about how to connect to a database, see Connect to a database.
If you want to log on to the tenant as a regular user, you must create a new regular user for the tenant and grant privileges to the user. For more information about how to create a user and grant privileges to the user, see Manage users and privileges.