Log on to an OceanBase tenant

OceanBase Database adopts a multi-tenant architecture. This architecture is suitable for resource consolidation, cloud services and other scenarios, and also reduces operational complexity.

As of OceanBase Database V4.0, there are three types of tenants: sys tenant, user tenant, and meta tenant corresponding to each user tenant.

Log on to the system tenant

The system tenant, also referred to as sys tenant, is a database instance used internally by OceanBase Database to handle cluster-wide public tasks. The RootService is based on the sys tenant to undertake the management work of OceanBase Database, including cluster management, tenant management, resource management, load balancing, scheduling of daily compaction tasks, and data migration and replication. The sys tenant is the default tenant created for the cluster and is responsible for managing the lifecycle of the cluster and all tenants.

The sys tenant is compatible with the MySQL mode and can be accessed through both the MySQL client and OceanBase Client (OBClient).

Log on through the MySQL client

The database connection string for logging on through the MySQL client is shown as follows:

mysql -h host IP address -P port number -u account name -p 'password' -D default database -A -c --prompt "OceanBase(\u@\d)> "

where

• -h specifies the host IP address for logging on to OceanBase Database. You can log on either through the OceanBase Database Proxy (ODP) or through a direct connection to an OBServer node. It is recommended to log on through the ODP as it makes optimal use of OceanBase's distributed features, including routing and disaster recovery.

• -P specifies the port number for logging on to OceanBase Database. You can change the port number in the configuration file during deployment. You can view the mysql_port parameter of each OBServer node and the listen_port parameter of the ODP in the configuration file. If no values are explicitly specified for these parameters, the default values are used.

  • If you log on through the ODP, the default port number is 2883.

  • If you log on through a direct connection to an OBServer node, the default port number is 2881.

• -u specifies the account for logging on to OceanBase Database. Unlike conventional databases, the account for OceanBase Database contains more fields, including username, tenant name, and cluster name.

  • If you log on to OceanBase Database by using ODP, you must specify the username, tenant name, and cluster name in one of the following formats: username@tenant name#cluster name, cluster name:tenant name:username, cluster name-tenant name-username and cluster name.tenant name.username. For example, if you want to log on as the root user to the sys tenant of the test cluster, your account name is root@sys#test.

  • When you directly connect to an OBServer node, you must specify the username and tenant name in the username@tenant name format. For example, if you want to log on as the root user to the sys tenant of the test cluster, your account name is root@sys.

• -p specifies the password for logging on to OceanBase Database. For a new cluster, the initial password for the root user of the sys tenant is empty. We recommend that you execute the alter user root identified by '******' statement as the first step after creating a new cluster.

For more information about connecting to OceanBase Database through the MySQL client, see Connect to an OceanBase tenant by using the mysql client.

Log on through the OBClient

OBClient is a recommended CLI client tool for logging on to OceanBase Database. You can use your OBClient account in a MySQL client. The database connection string for logging on through the OBClient is shown as follows:

obclient -h host IP address -P port number -u account name -p 'password' -D default database -A -c --prompt "OceanBase(\u@\d)> "

For more information about connecting to OceanBase Database through OBClient, see Connect to an OceanBase tenant by using OBClient.

Best practices for logon

• The root user has a high level of permissions and the abuse of the root user will pose a great security risk to the database. It is recommended that the root user only be used to manage other users and create dedicated O&M users and read-only users with the least permissions.

• It is recommended that dedicated read-only users query views.

  As of OceanBase Database V4.0, internal information is queried only in the form of views. The sys tenant can query various views.

  obclient> use oceanbase
  
  obclient> show tables;
  +-------------------------------------------+
  | Tables_in_oceanbase                       |
  +-------------------------------------------+
  | CDB_INDEXES                               |
  | CDB_IND_COLUMNS                           |
  | CDB_IND_PARTITIONS                        |
  | CDB_IND_SUBPARTITIONS                     |
  | CDB_OBJECTS                               |
  | CDB_OB_ARCHIVELOG                         |
  | CDB_OB_ARCHIVELOG_PIECE_FILES             |
  ...
  

  These views can be classified into two categories: data dictionary views and dynamic performance views.

  • Data dictionary views: The name of a data dictionary view begins with DBA_ or CDB_. A view whose name begins with DBA_ displays the internal information of a tenant. For example, the DBA_OB_LS view provides information about log streams in the tenant; the DBA_OB_LS view in the sys tenant displays only information about log streams in the sys tenant itself. A view whose name begins with CDB_ is used for the sys tenant. From the sys tenant, you can query these views for the status of all tenants in the cluster. For example, the CDB_OB_LS view provides information about all log streams of all tenants in the cluster. Generally, for each DBA_ view, there is a corresponding CDB_ view in the sys tenant.

  • Dynamic performance views: The name of a dynamic performance view begins with GV$ or V$. A view whose name begins with V$ displays only information about the server to which you have logged on, and a view whose name begins with GV$ displays information about all servers in the tenant. In OceanBase Database V4.0, many dynamic performance views have been added to a user tenant. For example, the GV$OB_UNITS view can be used to query information about the distribution of units within the tenant. The dynamic performance view of the sys tenant displays information about all tenants in the cluster.

  To view information about nodes in the OceanBase cluster, run the following statement:

  obclient> use oceanbase
  Database changed
  
  obclient> select * from dba_ob_servers;
  +-------------+----------+----+--------------+----------+-----------------+--------+----------------------------+-----------+-----------------------+----------------------------+----------------------------+-------------------------------------------------------------------------------------------+
  | SVR_IP      | SVR_PORT | ID | ZONE         | SQL_PORT | WITH_ROOTSERVER | STATUS | START_SERVICE_TIME         | STOP_TIME | BLOCK_MIGRATE_IN_TIME | CREATE_TIME                | MODIFY_TIME                | BUILD_VERSION                                                                             |
  +-------------+----------+----+--------------+----------+-----------------+--------+----------------------------+-----------+-----------------------+----------------------------+----------------------------+-------------------------------------------------------------------------------------------+
  | 6.x.xxx.xx  |     2882 |  6 | sa128_obv4_2 |     2881 | NO              | ACTIVE | 2022-12-30 16:17:03.173519 | NULL      | NULL                  | 2022-12-30 16:08:04.749100 | 2023-01-04 11:48:36.589270 | 4.0.0.0_101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322(Dec  7 2022 16:22:15) |
  | 6.x.xxx.xx  |     2882 |  4 | sa128_obv4_3 |     2881 | NO              | ACTIVE | 2022-12-30 16:36:35.567437 | NULL      | NULL                  | 2022-12-30 16:08:02.755200 | 2023-01-04 14:13:36.976548 | 4.0.0.0_101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322(Dec  7 2022 16:22:15) |
  | 6.x.xxx.xxx |     2882 |  3 | sa128_obv4_3 |     2881 | NO              | ACTIVE | 2022-12-12 12:42:00.054759 | NULL      | NULL                  | 2022-11-03 15:37:09.530894 | 2022-12-22 14:43:26.717736 | 4.0.0.0_101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322(Dec  7 2022 16:22:15) |
  | 6.x.xx.xx   |     2882 |  1 | sa128_obv4_1 |     2881 | NO              | ACTIVE | 2022-12-12 12:25:17.555651 | NULL      | NULL                  | 2022-11-03 15:37:08.990683 | 2022-12-12 12:25:18.553763 | 4.0.0.0_101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322(Dec  7 2022 16:22:15) |
  | 6.x.xx.xxx  |     2882 |  2 | sa128_obv4_2 |     2881 | YES             | ACTIVE | 2022-12-12 11:46:37.222980 | NULL      | NULL                  | 2022-11-03 15:37:09.490511 | 2022-12-12 11:47:31.075335 | 4.0.0.0_101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322(Dec  7 2022 16:22:15) |
  | 6.x.xx.xx   |     2882 |  5 | sa128_obv4_1 |     2881 | NO              | ACTIVE | 2022-12-30 16:25:45.420996 | NULL      | NULL                  | 2022-12-30 16:08:03.928478 | 2023-01-04 11:48:36.578231 | 4.0.0.0_101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322(Dec  7 2022 16:22:15) |
  +-------------+----------+----+--------------+----------+-----------------+--------+----------------------------+-----------+-----------------------+----------------------------+----------------------------+-------------------------------------------------------------------------------------------+
  6 rows in set (0.03 sec)
  

• It is recommended that dedicated O&M users run cluster O&M commands.

  Cluster O&M commands can be executed through the system tenant. For example, run the following command to isolate an abnormal node:

  obclient> ALTER SYSTEM STOP SERVER 'xx.xx.xx.xx:2882';
  

  After the command is executed, you can confirm whether the isolation is successful by checking the STOP_TIME field of the node in the DBA_OB_SERVERS view.

Log on to a user tenant

A user tenant is created by a user. A user tenant can provide complete database services and supports MySQL and Oracle modes.

To view information on all tenants, log on to the sys tenant and query the DBA_OB_TENANTS view.

• TENANT_TYPE specifies the tenant type. SYS is the sys tenant, META is a meta tenant, and USER is a user tenant. The ID of the sys tenant is 1. Among tenants whose ID is greater than 1000, even-numbered IDs indicate user tenants, and odd-numbered IDs indicate meta tenants. The ID of a user tenant is greater than that of its corresponding meta tenant by 1.

• COMPATIBILITY_MODE specifies the tenant mode. MYSQL indicates MySQL mode and ORACLE indicates Oracle mode.

In MySQL mode, both the MySQL client and OBClient can be used to log on to a user tenant. However, in Oracle mode, only the OBClient can be used to log on to a user tenant.

The database connection strings for logging on to a user tenant are almost identical to those for logging on to the sys tenant. The only difference is that you need to change the tenant name in the logon account.

For user tenants in MySQL mode, the built-in data dictionary views and dynamic performance views of OceanBase Database are all located in the oceanbase namespace. For user tenants in Oracle mode, the built-in views of OceanBase Database are located under the SYS user. The names, fields, and usage experience of views are the same in both modes.

Log on to a meta tenant

Meta tenants are used for internal management in OceanBase Database. For each user tenant created, a corresponding meta tenant is automatically created whose lifecycle is the same as that of the user tenant. A meta tenant cannot be logged on directly, but its information can be accessed through the user tenant and system tenant.

References

For more information about the methods and procedures for connecting to OceanBase Database, see:

• Connect to the MySQL mode of OceanBase Database

• Connect to the Oracle mode of OceanBase Database