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-compatible 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 on through the MySQL client is as follows:

mysql -h<host_ip> -P<port> -u<account> -p<'password'> -D<default_database> -A -c --prompt "OceanBase(\u@\d)> "

Parameter description:

• -h: The host IP address for logging on to OceanBase Database. OceanBase supports login through ODP or direct connection to an OBServer node. It is recommended to log in through ODP, as ODP can better handle the distributed features of OceanBase Database, including routing and disaster recovery.

• -P: 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 ODP in the configuration file. If no value is explicitly specified, the default value is used.

  • If you log in through the 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: 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 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 and 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.

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

• -p: The password for logging on to OceanBase Database. For a newly deployed cluster, the initial password for the root user of the sys tenant is empty. It is recommended that the first thing you do after deploying a new cluster is to change the root user password: alter user root identified by '******'.

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

Besides using the MySQL client, you can also use OceanBase Client (OBClient) to log in. OBClient is the recommended CLI client tool for OceanBase Database. It is compatible with the MySQL client. The database connection string is as follows:

obclient -h<host_ip> -P<port> -u<account> -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 high privileges. If root credentials are freely shared, it will pose a great security risk to the database. It is recommended that the root user only be used to manage other users and to create dedicated O&M users and read-only users that follow the principle of least privilege.

• It is recommended to log in as a dedicated read-only user when querying views.

  As of OceanBase Database V4.0, the biggest change compared with previous versions is that internal information is queried entirely through views. Through the sys tenant of OceanBase Database, you can query rich view information.

  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: Views are named with the DBA_ or CDB_ prefix. Views with the DBA_ prefix display information within each tenant. For example, the DBA_OB_LS view displays log stream information in the tenant; in the sys tenant, the DBA_OB_LS view displays only the log stream information of the sys tenant itself. Views with the CDB_ prefix are dedicated to the sys tenant and are used to view the database status of all tenants in the cluster from the sys tenant. For example, the CDB_OB_LS view displays log stream information of all tenants in the cluster. Generally, each DBA_ view has a corresponding CDB_ view in the sys tenant.

  • Dynamic performance views: Views are named with the GV$ or V$ prefix. Views with the V$ prefix display only information on the server to which you have logged on. Views with the GV$ prefix display a summary of information from all servers in the tenant. OceanBase Database V4.0 adds many dynamic performance views to user tenants. For example, the GV$OB_UNITS view can be used to query the Unit distribution information of the tenant. The dynamic performance views of the sys tenant are specially processed to display a summary of information about all tenants in the cluster.

  For example, to query node information of the OceanBase cluster:

  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
  

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

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

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

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

Log in to a user tenant

A user tenant is the counterpart of the sys tenant. User tenants are created by users and provide complete database services. They support MySQL and Oracle compatibility modes.

To view all tenant information, log in to the sys tenant and query the DBA_OB_TENANTS view:

• TENANT_TYPE indicates the tenant type: SYS for the sys tenant, META for the meta tenant, and User for user tenants. The tenant with ID 1 is the sys tenant. Among tenants with ID greater than 1000, even-numbered IDs are user tenants and odd-numbered IDs are meta tenants. The ID of a user tenant is 1 greater than that of its corresponding meta tenant.

• COMPATIBILITY_MODE indicates the tenant compatibility mode: MYSQL for MySQL-compatible mode and ORACLE for Oracle-compatible mode.

To log in to a MySQL-compatible user tenant, you can use either the MySQL client or OBClient. To log in to an Oracle-compatible user tenant, you can only use OBClient. The login method is the same as that for the sys tenant. The only difference is that the tenant name in the login account must be changed to the corresponding tenant name.

In MySQL-compatible user tenants, the built-in data dictionary views and dynamic performance views of OceanBase Database are in the oceanbase namespace. In Oracle-compatible user tenants, the built-in views of OceanBase Database are under the SYS user. In both modes, the view names, fields, and usage experience are the same.

Log in to a meta tenant

The meta tenant is a self-managed tenant internal to OceanBase Database. One meta tenant is created for each user tenant, and their lifecycles are the same. A meta tenant cannot be logged in to directly. Its information can be accessed through the user tenant and sys tenant.

References

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

• Connect to OceanBase Database (MySQL-compatible mode)

• Connect to OceanBase Database (Oracle-compatible mode)