Log in 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 in 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 in through the mysql client

The database connection string for logging in through the mysql client is in the following format:

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 in to OceanBase Database. OceanBase Database allows you to directly log in to an OBServer node or connect to an OBServer node by using OceanBase Database Proxy (ODP). We recommend ODP because it helps you make full use of the features of OceanBase Database as a distributed system, such as routing and disaster recovery.

• -P specifies the port number for logging in to OceanBase Database. OceanBase Database allows you to change the port number in the configuration file during deployment. You can view the mysql_port parameter of OBServer nodes and the listen_port parameter of ODP in the configuration file. If no value is explicitly specified for this parameter, the default value takes effect.

  • If you log in by using ODP, the default port number is 2883.

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

• -u specifies the account for logging in to OceanBase Database. An OceanBase Database account has more fields than conventional database accounts, and it consists of a username, a tenant name, and a cluster name.

  • If you log in 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, or cluster name.tenant name.username. For example, if you want to log in as the root user to the sys tenant of the test cluster, your account name is root@sys#test.

  • If 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 in as the root user to the sys tenant of the test cluster, your account name is root@sys.

• -p specifies the password for logging in 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 to change the password of the root user immediately after you create a 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 in through the OBClient

You can also log in to OceanBase Database by using the OBClient. OBClient is a recommended CLI client tool for logging in to OceanBase Database. You can use your OBClient account in the mysql client. The database connection string for logging in through OBClient is in the following format:

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 login

• The root user has many privileges and the abuse of the root user account may cause serious data security threats. We recommend that you use the root user only to manage other users and create dedicated O&M and read-only users with the least privileges.

• It is recommended that you query views as a dedicated read-only user.

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

  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 displays 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. Views whose names begin with CDB_ are used for the sys tenant. You can query these views for the status of all tenants in the cluster from the sys tenant. For example, the CDB_OB_LS view displays information about all log streams of all tenants in the cluster. In general, each view whose name begins with DBA_ has a corresponding view whose name begins with CDB_ 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 in, and a view whose name begins with GV$ displays information about all servers. OceanBase Database V4.0 provides many dynamic performance views in user tenants. For example, you can query the GV$OB_UNITS view for the distribution of resource units in the current tenant. A dynamic performance view of the sys tenant displays information about all tenants in the cluster.

  For example, to view information about nodes in the OceanBase cluster, execute 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 you run cluster O&M commands as a dedicated O&M user.

  You can run cluster O&M commands in the sys tenant. For example, you can execute the following statement to isolate an abnormal node:

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

  After the statement is executed, you can verify whether this node is isolated based on the STOP_TIME field of this node in the DBA_OB_SERVERS view.

Log in to a user tenant

A user tenant is a tenant created by a user. It provides complete database features and supports two compatibility modes: MySQL and Oracle.

You can log in to the sys tenant and query the DBA_OB_TENANTS view for information about all tenants.

• TENANT_TYPE indicates the tenant type. The value SYS indicates the sys tenant, META indicates a meta tenant, and User indicates a user tenant. The ID of the sys tenant is 1. Among tenants whose IDs are greater than 1000, an even ID number indicates a user tenant, and an odd number indicates a meta tenant. The ID of a user tenant is equal to that of the corresponding meta tenant plus 1.

• COMPATIBILITY_MODE indicates the tenant mode. The value MYSQL indicates the MySQL mode, and ORACLE indicates the Oracle mode.

You can use the mysql client or OBClient to log in to a user tenant in MySQL mode. You can use only OBClient to log in to a user tenant in Oracle mode. The connection strings are the same as those for logging in to the sys tenant. You only need to change the tenant name in the login 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 use experience of views are the same in the two modes.

Log in to a meta tenant

Meta tenants are used for internal management in OceanBase Database. When you create a user tenant, a corresponding meta tenant is automatically created. The lifecycle of a meta tenant is the same as that of its user tenant. You cannot directly log in to a meta tenant. Instead, you can access a meta tenant from the corresponding user tenant or sys tenant.

References

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

• Connect to OceanBase Database in MySQL mode

• Connect to OceanBase Database in Oracle mode