Connect to an OceanBase tenant by using the mysql client

You can use the mysql client to connect to a MySQL tenant of OceanBase Database. This topic will guide you through the necessary steps and provide an example to help you get started.

Prerequisites

Before you connect to OceanBase Database by using the mysql client, make sure that the following conditions are met:

• The mysql client is installed on your local server. OceanBase Database supports the mysql client of the following versions: 5.5, 5.6, 5.7, and 8.0.2.

  Notice

  OceanBase Database does not support mysql client 8.0.2 when you use OceanBase Database Proxy (ODP) for connection.

• The environment variable PATH contains the directory where the mysql client is located.

• The client is in the allowlist of the tenant. For more information about how to query and configure a tenant allowlist, see View and set a tenant allowlist.

Procedure

Connect to OceanBase Database by using ODP

Run the following MySQL command to connect to OceanBase Database by using ODP:

mysql -h$host -P$port -u$user_name -p****** [-c] [-A] [$database_name]

The parameters are described as follows:

• -h specifies the IP address for connecting to OceanBase Database. Replace $host with the actual IP address of ODP used for connection.

• -P specifies the port for connecting to OceanBase Database. Replace $port with the port customized when ODP is deployed. The default value is 2883.

• -u specifies the tenant account for connecting to OceanBase Database. The following account formats are supported: username@tenant name#cluster name, cluster name:tenant name:username, cluster name-tenant name-username, and cluster name.tenant name.username. You can use the mysql client only to connect to a MySQL tenant. The default username of the administrator of a MySQL tenant is root.

  Note

  • When you use ODP to connect to an OceanBase cluster, you can obtain the cluster name in the following way:
    Directly connect to OceanBase Database and execute the SHOW PARAMETERS LIKE 'cluster'; statement to query the cluster name. In the query result, VALUE indicates the name of the OceanBase cluster.
  • If ODP works for multiple clusters, you must specify the cluster name when connecting to it. If ODP works for only one cluster, you do not need to specify the cluster name when connecting to it.

• -p specifies the account password for connecting to OceanBase Database. Replace ****** with the actual password.

  Note

  For security purposes, we recommend that you do not directly enter the password on the CLI, especially in an environment where the script or historical records are visible. You can skip this option and enter the password when being prompted to.

• -c specifies not to ignore comments in the runtime environment of MySQL. This parameter is optional.

  Note

  Hints are special comments that are not affected by the -c option.

• -A specifies not to automatically retrieve the statistical information when you connect to a MySQL tenant. This parameter is optional.

• $database_name specifies the default database to be used after the connection to OceanBase Database is established. This parameter is optional. By default, no database is accessed after the connection is established if you do not specify this option. You need to manually select a database.

Here is an example:

mysql -h10.10.10.1 -uuser001@obmysql#obdemo -P2883 -p****** -c -A oceanbase

Directly connect to OceanBase Database

Run the following MySQL command to directly connect to OceanBase Database:

mysql -h$host -P$port -u$user_name -p****** [-c] [-A] [$database_name]

where:

• -h specifies the IP address for connecting to OceanBase Database. Replace $host with the actual IP address of the OBServer node for direct connection.

  Notice

  When you use the direct connection method, make sure that the tenant resources are distributed on the OBServer node you specified. Otherwise, you cannot connect to the tenant by using this OBServer node.

• -P specifies the port for connecting to OceanBase Database. Replace $port with the port customized when OceanBase Database is deployed. The default value is 2881.

• -u specifies the tenant account for connecting to OceanBase Database, which is in the username@tenant name format. The default username of the administrator of a MySQL tenant is root.

  Notice

  When you directly connect to OceanBase Database, enter the username and tenant name in the format of username@tenant name for the connection parameter -u and do not specify the cluster name. If you specify the cluster name in -u, a connection error is returned.

• -p specifies the account password for connecting to OceanBase Database. Replace ****** with the actual password.

  Note

  For security purposes, we recommend that you do not directly enter the password on the CLI, especially in an environment where the script or historical records are visible. You can skip this option and enter the password when being prompted to.

• -c specifies not to ignore comments in the runtime environment of MySQL. This parameter is optional.

  Note

  Hints are special comments that are not affected by the -c option.

• -A specifies not to automatically retrieve the statistical information when you connect to a MySQL tenant. This parameter is optional.

• $database_name specifies the default database to be used after the connection to OceanBase Database is established. This parameter is optional. By default, no database is accessed after the connection is established if you do not specify this option. You need to manually select a database.

Here is an example:

mysql -h10.10.10.1 -uuser001@obmysql -P2881 -p****** -c -A oceanbase

Connection example

1. Open a command-line terminal.

2. Run the following command to connect to a MySQL tenant of OceanBase Database from the mysql client by using ODP:

   mysql -hxxx.xx.xxx.xxx -P2883 -uroot@mysql001#test424 -p****** -A oceanbase
   

   The return result is as follows:

   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MySQL connection id is 15
   Server version: 5.6.25 OceanBase 4.2.4.0 (r200000342024061210-c4c0c18741e45a1d40889b6147c3132574d6e7ea) (Built Jun 12 2024 10:58:58)
   
   <...>
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   MySQL [oceanbase]>
   

3. To exit the mysql client, enter exit and press Enter, or press the keyboard shortcut Ctrl + D.

More information

If errors occur when you connect to OceanBase Database by using the mysql client, we recommend that you refer to the official MySQL documentation for troubleshooting.