Create a tenant|V3.2.4|OceanBase Database| docs|Distributed Database

Create a tenant

Last Updated：2023-10-27 09:57:43 Updated

This topic describes how to create a business tenant of OceanBase Database by using SQL statements. OceanBase Database allows you to create resource units, resource pools, and business tenants under the sys tenant.

When you create a tenant, if the current resource pool is occupied by another tenant, you need to create a new resource pool. Before you create a resource pool, you must determine the resource unit configurations and range of resources available to the tenant.

Procedure

1. (Optional) View the resource unit and resource pool

obclient> SELECT * FROM oceanbase.__all_unit_config;
obclient> SELECT * FROM oceanbase.gv$unit;

2. Create a resource unit

A resource unit only defines the specifications but does not allocate resources.

Syntax

CREATE RESOURCE UNIT unit_name
MAX_CPU [=] cpu_num,
MAX_MEMORY [=] mem_size,
MAX_IOPS [=] iops_num,
MAX_DISK_SIZE [=] disk_size,
MAX_SESSION_NUM [=] session_num,
[MIN_CPU [=] cpu_num,]
[MIN_MEMORY [=] mem_size,]
[MIN_IOPS [=] iops_num];

Parameters

Parameter	Description
unit_name	The name of the resource unit.
MAX_CPU	The maximum number of CPU cores for the resource unit.
MAX_MEMORY	The maximum memory size, in bytes. Value range: [1073741824, +∞). Minimum value: `1 GB`.
MAX_IOPS	The maximum IOPS. Value range: [128, +∞).
MAX_DISK_SIZE	The maximum disk size, in bytes. Value range: [536870912, +∞). Minimum value: `512 MB`.
MAX_SESSION_NUM	The maximum number of sessions. Value range: [64, +∞).
MIN_CPU	The minimum number of CPU cores.
MIN_MEMORY	The minimum memory size.
MIN_IOPS	The minimum IOPS for the resource unit.

Example

Create a resource unit named unit001 with the following specifications: 2 CPU cores, 6 GB of memory, IOPS of 128, 100 GB of disk, and up to 64 sessions.

obclient> CREATE RESOURCE UNIT unit001
     MAX_CPU 2,MAX_MEMORY '6G',MAX_IOPS 128,MAX_DISK_SIZE '100G',MAX_SESSION_NUM 64,
     MIN_CPU 2,MIN_MEMORY '6G',MIN_IOPS 128;
Query OK, 0 rows affected

obclient> CREATE RESOURCE UNIT unit001
     MAX_CPU 2,MAX_MEMORY 6442450944,MAX_IOPS 128,MAX_DISK_SIZE 107374182400,MAX_SESSION_NUM 64,
     MIN_CPU 2,MIN_MEMORY 6442450944,MIN_IOPS 128;
Query OK, 0 rows affected

3. Create a resource pool

The resource pool allocates resources based on the resource unit specifications.

Syntax

CREATE RESOURCE POOL pool_name
UNIT [=] unit_name,
UNIT_NUM [=] unit_num,
ZONE_LIST [=] ('zone' [, 'zone' ...]);

Parameters

Parameter	Description
pool_name	The name of the resource pool to be created.
UNIT [=] unit_name	The name of the resource unit.
UNIT_NUM [=] unit_num	The number of resource units to be created in a zone. A server is automatically selected from each zone for each unit based on the current cluster load. However, multiple units of a resource pool cannot be allocated to the same server. That is, the number of units in a resource pool cannot exceed the number of servers in a zone.
ZONE_LIST [=] ('zone' [, 'zone' ...])	The zone to which the resource pool to be created belongs. `zone_list` lists all zones in a cluster.

Example

Create a resource pool named pool001.

obclient> CREATE RESOURCE POOL pool001
     UNIT='unit001',UNIT_NUM=1,ZONE_LIST=('zone1','zone2','zone3');
Query OK, 0 rows affected

4. Create a tenant

Allocate the resource pool to the corresponding tenant.

Syntax

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
    | PRIMARY_ZONE [=] zone  
    | DEFAULT TABLEGROUP [=] {NULL | tablegroup}
    | 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] ...

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 resource pool list, which is required when you create a tenant. Only one resource pool is supported.
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 of the tenant.
COLLATE	The collation.
PRIMARY_ZONE	The primary zone. Regarding the priorities of zones where leader replicas are allocated, the zones on two sides of a comma (,) have the same priority, and those on the left side of a semicolon (:) have a higher priority than those on the right side. Example: `zone1,zone2;zone3`.
LOCALITY	The distribution of replicas across zones. For example, `F@z1,F@z2,F@z3,R@z4` indicates that `z1`, `z2`, and `z3` are full-featured replicas, and `z4` is a read-only replica.
system_var_name	Optional. The system variables of the tenant. `ob_compatibility_mode` specifies the compatibility mode of the tenant. Valid value: MySQL or Oracle. You can specify the compatibility mode only when you create the tenant. If you do not specify the `ob_compatibility_mode` variable, the compatibility mode is MySQL by default. `ob_tcp_invited_nodes` specifies the whitelist of client IP addresses allowed to connect to the tenant. If you do not modify the default value of the `ob_tcp_invited_nodes` variable, only the IP address of the local server is allowed to connect to the tenant. You can also modify the whitelist after the tenant is created. For more information about how to modify the whitelist, see Set and view the whitelist of a tenant. For more information about the system variables available for tenants in OceanBase Database, see System variables.

Examples

Create a tenant in MySQL mode.

obclient> CREATE TENANT IF NOT EXISTS mysql001
     CHARSET='utf8mb4',
     PRIMARY_ZONE='zone1;zone2,zone3',
     RESOURCE_POOL_LIST=('pool001')
     SET ob_tcp_invited_nodes='%';
Query OK, 0 rows affected

Create a tenant in Oracle mode.

obclient> CREATE TENANT IF NOT EXISTS oracle001
     CHARSET='utf8mb4',
     PRIMARY_ZONE='zone1;zone2,zone3',
     RESOURCE_POOL_LIST=('pool002')
     SET ob_tcp_invited_nodes='%',ob_compatibility_mode='oracle';
Query OK, 0 rows affected

5. Set the password of the administrator user

Notice

For a tenant created by using an SQL statement, the password of the tenant administrator is empty. Set a password before you use the tenant administrator.

Examples:

Set the password of the administrator user root of the mysql001 tenant to ******.
```
$ obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p -A
Enter password:

obclient> SET PASSWORD = PASSWORD('******');
Query OK, 0 rows affected
```
For more information about how to change the password of a user in MySQL mode, see SET PASSWORD.
Set the password of the administrator user SYS of the oracle001 tenant to ******.
```
$ obclient -h10.10.10.1 -P2881 -usys@oracle001 -p -A
Enter password:

obclient> SET PASSWORD = PASSWORD(******);
Query OK, 0 rows affected
```
For more information about how to change the password of a user in Oracle mode, see SET PASSWORD.

More information

For more information about how to create an OceanBase Database user, see CREATE USER (MySQL mode) and CREATE USER (Oracle mode).