Connect to OceanBase Database from a MySQL client

You can use a MySQL client to connect to a MySQL tenant of OceanBase Database.

A MySQL client is a MySQL CLI tool that must be separately installed. OceanBase Database tenants are divided into MySQL tenants and Oracle tenants. You can use a MySQL client to access a MySQL tenant.

After you connect to OceanBase Database, you can run MySQL O&M commands and execute SQL statements in the CLI of a MySQL client for the following purposes:

• Calculating, storing, and generating query results
• Creating database objects and checking and modifying object definitions
• Managing databases and modifying parameters

Prerequisites

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

• A MySQL client is installed on your 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.

• Before you connect to a tenant from a client, make sure that the client is in the allowlist of the tenant. For more information about how to query and configure a tenant allowlist, see Set and view the tenant allowlist.

Procedure

1. Open a command line.

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

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

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

     or

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

     The parameters are described as follows:

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

     • -u: specifies the tenant account. Two account formats are supported: Username@Tenant name#Cluster name 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.

     • -P: specifies the port for connecting to OceanBase Database, which is also the listening port of the ODP. Default value: 2883, which can be customized.

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

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

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

     Here is an example:

     $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 -ppassword -c -A oceanbase
     

     The parameters are described as follows:

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

     • -P: specifies the port for connecting to OceanBase Database. Default value: 2881, which can be customized.

     • -p: specifies the account password. For security reasons, you do not need to specify this parameter. 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.

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

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

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

Examples

The following sample code shows how to connect to a MySQL tenant of OceanBase Database from a 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 127765
Server version: 5.6.25 OceanBase 3.2.0 (...) (Built Jun 19 2021 11:36:47)
<Omitted>
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 from a MySQL client, we recommend that you refer to the official MySQL documentation.