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 mysql clients 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

1. Open a command-line terminal.

2. Run the MySQL command to connect to a MySQL tenant.

   • To connect to the MySQL tenant by using an OceanBase Database Proxy (ODP), run the following command:

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

     or

     $mysql -h10.10.10.1 -uobdemo:obmysql:username -P2883 -p****** -c -A oceanbase
     

     where

     • -h specifies the IP address for connecting to OceanBase Database, which is usually the IP address of an ODP.

     • -u specifies the tenant account. 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 connect a MySQL client only to a MySQL tenant. The default username of the administrator of a MySQL tenant is root.

       Note

       • When connecting to an OceanBase cluster 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 will return 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 port for connecting to OceanBase Database, which is also the listening port of the ODP. The default value is 2883, which can be customized when ODP is deployed.

     • -c specifies that comments must not be ignored in the runtime environment of MySQL.

       Note

       Hint is a special comment that is not affected by the -c option.

     • -A specifies not to automatically retrieve the statistical information when connecting to a MySQL tenant.

     • oceanbase specifies the name of the database to be accessed. You can change it to a business database name.

     Here are the examples:

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

     or

     $mysql -h10.10.10.1 -uobdemo:obmysql:****** -P2883 -p****** -c -A oceanbase
     

   • To directly connect to the MySQL tenant, run the following command:

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

     where

     • -h specifies the IP address for connecting to OceanBase Database, which is usually the IP address of an OBServer node.

     • -u specifies the tenant account in the username@tenant name format. You can connect a MySQL client only to a MySQL tenant. 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. Including the cluster name in -u will cause a connection error.

     • -P specifies the port for connecting to OceanBase Database. The default value is 2881, which can be customized when OceanBase Database is deployed.

     • -p specifies the account password. For security reasons, you do not need to specify this option. In that case, you will be prompted to enter a password later. The password is invisible.

     • -c specifies that comments must not be ignored in the runtime environment of MySQL.

       Note

       Hint is a special comment that is not affected by the -c option.

     • -A specifies not to automatically retrieve the statistical information when connecting to a MySQL tenant.

     • oceanbase specifies the name of the database to be accessed. You can change it to a business database name.

     • 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.

     Here is an example:

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

3. After the connection is established, the following command-line prompt is displayed by default:

   MySQL [oceanbase]>
   

4. To exit the MySQL command line, enter exit and press Enter, or press the shortcut key Ctrl + D.

Connection example

The following example shows how to connect to a MySQL tenant of OceanBase Database from the mysql client:

$mysql -h10.10.10.1 -u******@obmysql#obdemo -P2883 -p****** -c -A oceanbase
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5751
Server version: 5.6.25 OceanBase 4.0.0 (r10100032022041510-a09d3134c10665f03fd56d7f8bdd413b2b771977) (Built Apr 15 2022 02:16:22)
<...>
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [oceanbase]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set

MySQL [oceanbase]> exit
Bye

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.