Create a user tenant

OceanBase Database supports multitenancy. In a cluster, you can create many tenants for different departments in your organization. The resources of each tenant are isolated from each other to ensure that tenants can access their own data only. In OceanBase Database, a tenant is similar to a database instance.

Background

MySQL adopts the single-tenancy mode, and all users share the same resource pool, which may lead to failures. When the load is very high, applications will use up all database resources, preventing the database administrator from accessing the database or executing high-priority control commands. For example, the administrator may be unable to kill queries that have timed out, or even unable to use management platforms to connect to the databases.

By default, OceanBase Database automatically creates a sys tenant. The sys tenant partly manages the OceanBase database and has access to system metadata tables. OceanBase automatically reserves a specific amount of resources for the sys tenant.

Create a tenant

1. Use the root user (root@sys) of the sys tenant to connect to OceanBase Database. You can use the OBClient or mysql client to connect to OceanBase Database. Sample statement:

   obclient -hxxxx -uroot@sys -P${port_num} -p -Doceanbase -A
   # or
   mysql -hxxxx -uroot@sys -P${port_num} -p -Doceanbase -A
   

   For more information about the OBClient, see OBClient Documentation.

   Notice

   Only the root@sys user has the privilege to create a tenant.

2. Query the resource allocation of all OBServer nodes in the cluster. Sample statement:

   obclient> SELECT * FROM oceanbase.GV$OB_SERVERS;
   +-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
   | SVR_IP          | SVR_PORT | ZONE  | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY | MEM_ASSIGNED | LOG_DISK_CAPACITY | LOG_DISK_ASSIGNED | LOG_DISK_IN_USE | DATA_DISK_CAPACITY | DATA_DISK_IN_USE | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT | DATA_DISK_ABNORMAL_TIME | SSL_CERT_EXPIRED_TIME |
   +-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
   | 172.xxx.xxx.228 |     2882 | zone1 |     2881 |            8 |                8 |            1 |                1 |  10737418240 |   2684354560 |        5368709120 |        2684354560 |       134217728 |        10737418240 |        83886080  | NORMAL                  |  17179869184 | NULL                    | NULL                  |
   +-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
   | 172.xxx.xxx.229 |     2882 | zone2 |     2881 |            8 |                8 |            1 |                1 |  10737418240 |   2684354560 |        5368709120 |        2684354560 |       134217728 |        10737418240 |        83886080  | NORMAL                  |  17179869184 | NULL                    | NULL                  |
   +-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
   | 172.xxx.xxx.230 |     2882 | zone3 |     2881 |            8 |                8 |            1 |                1 |  10737418240 |   2684354560 |        5368709120 |        2684354560 |       134217728 |        10737418240 |        83886080  | NORMAL                  |  17179869184 | NULL                    | NULL                  |
   +-----------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+------------------+-------------------------+--------------+-------------------------+-----------------------+
   3 row in set
   

3. Query the resource allocation of all tenants in a cluster. Sample statement:

   obclient> SELECT * FROM oceanbase.GV$OB_UNITS;
   +-----------------+----------+---------+-----------+-------+---------+---------+-------------+----------+----------+-------------+---------------+-----------------+------------------+--------+----------------------------+
   | SVR_IP          | SVR_PORT | UNIT_ID | TENANT_ID | ZONE  | MAX_CPU | MIN_CPU | MEMORY_SIZE | MAX_IOPS | MIN_IOPS | IOPS_WEIGHT | LOG_DISK_SIZE | LOG_DISK_IN_USE | DATA_DISK_IN_USE | STATUS | CREATE_TIME                |
   +-----------------+----------+---------+-----------+-------+---------+---------+-------------+----------+----------+-------------+---------------+-----------------+------------------+--------+----------------------------+
   | 172.xxx.xxx.228 |     2882 |       1 |         1 | zone1 |       1 |       1 |  2684354560 |    10000 |    10000 |           1 |    2684354560 |        85075438 |        83886080  | NORMAL | 2022-11-08 14:24:01.960562 |
   | 172.xxx.xxx.229 |     2882 |       1 |         1 | zone2 |       1 |       1 |  2684354560 |    10000 |    10000 |           1 |    2684354560 |        85075438 |        83886080  | NORMAL | 2022-11-08 14:24:01.960562 |
   | 172.xxx.xxx.230 |     2882 |       1 |         1 | zone3 |       1 |       1 |  2684354560 |    10000 |    10000 |           1 |    2684354560 |        85075438 |        83886080  | NORMAL | 2022-11-08 14:24:01.960562 |
   +-----------------+----------+---------+-----------+-------+---------+---------+-------------+----------+----------+-------------+---------------+-----------------+------------------+--------+----------------------------+
   3 row in set
   

4. Create a resource unit configuration

   obclient> CREATE RESOURCE UNIT unit1
      max_cpu = 1,
      min_cpu = 1,
      memory_size = 2684354560,
      max_iops = 10000,
      min_iops = 10000,
      iops_weight = 1,
      log_disk_size = 2684354560;
   

   For more information about the SQL statement for creating a resource unit configuration, see Create a resource unit configuration.

5. Create a resource pool

   obclient> CREATE RESOURCE POOL pool1
      UNIT = 'unit1',
      UNIT_NUM = 1,
      ZONE_LIST = ('zone1','zone2','zone3');
   

   Parameters:

   • The UNIT_NUM parameter specifies the number of resource units contained in a zone in the cluster. The value must be smaller than or equal to the number of OBServer nodes in the zone.

   • The ZONE_LIST parameter specifies the zones that are allowed to use resources in the resource pool.

   • If none of the servers in a zone have enough resources available for a resource unit, the resource pool fails to be created.

   For more information about the SQL statement, see Create a resource pool.

6. Create a tenant

   obclient> CREATE TENANT IF NOT EXISTS tenant1
      charset='utf8mb4',
      comment 'mysql tenant/instance',
      primary_zone='RANDOM',
      resource_pool_list = ('pool1') set ob_tcp_invited_nodes = '%';
   

   For more information about the SQL statement, see Create a tenant.

7. Change the password

   Use the root user root@tenant1 of the new tenant to connect to OceanBase Database. Then, run the following command to create a password for the root user.

   #Use the root@tenant1 user to connect to OceanBase Database.
   obclient -hxxxx -uroot@tenant1 -P${port_num} -p -Doceanbase -A
   
   # Change the password
   obclient> ALTER USER username IDENTIFIED BY password;
   # Sample statement:
   obclient> ALTER USER root IDENTIFIED BY 'Xc****Db';
   

   For more information, see Change the password of a user.

Create a user

When you use a new tenant to connect to OceanBase Database for the first time, you must use the root user. After you connect to OceanBase Database, you can run the following command to create a new user:

obclient> CREATE USER IF NOT EXISTS test IDENTIFIED BY '******';

For more information, see Create a user.