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, and 5.7.

• 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 OceanBase Database Proxy (ODP):

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 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. $user_name can be in any 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. 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 connecting to an OceanBase cluster by using ODP, you can obtain the cluster name in the following way:
    First, connect directly to OceanBase Database. Then, use the SHOW PARAMETERS LIKE 'cluster'; command to retrieve the name of the cluster. This command returns the name of the OceanBase cluster corresponding to the value of VALUE.
  • 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 is optional, which specifies that comments are not ignored in the runtime environment of MySQL.

  Note

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

• -A is optional, which specifies not to automatically retrieve the statistical information when the client connects to OceanBase Database.

• $database_name is optional, which specifies the default database to be used after the connection to OceanBase Database is established. 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. The format of $user_name is username@tenant name. 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 -u option and do not specify the cluster name. If you including 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 is optional, which specifies that comments are not ignored in the runtime environment of MySQL.

  Note

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

• -A is optional, which specifies not to automatically retrieve the statistical information when the client connects to OceanBase Database.

• $database_name is optional, which specifies the default database to be used after the connection to OceanBase Database is established. 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 by using the mysql client:

   mysql -hxxx.xx.xxx.xxx -P2883 -uroot@mysql001#test432 -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.3.2.0 (r200000272024061717-93c0ed73ebbcac6edbf7b585860b75d825935358) (Built Jun 17 2024 18:06:04)
   
   <...>
   
   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.