Create a private connection

Overview

After you log on to the homepage of OceanBase Developer Center (ODC), you can click Create Connection in the upper-right corner of the Private Connection tab to create a private connection.

After you create the connection, you can view the saved database connection in the connection list on the ODC homepage.

Notice

In ODC V2.3.0 and later versions, the root@sys user permissions are required to query views when you use the import and export features. Therefore, to use the import and export features, you need to set the root@sys user in the Advanced Configuration section during connection creation.

The root@sys user permissions are also required to view the partition information. Therefore, to display the table partition information, you must also set the root@sys user in the Advanced Configuration section during connection creation.

ODC V3.3.0 and later versions support independent sessions and shared sessions. In independent session mode, each SQL window corresponds to one database session, and all modifications affect only the current window. In shared session mode, all SQL windows share one database session, and modifications in any SQL window affect other SQL windows.

In manual-commit mode, make sure to set the `ob_trx_idle_timeout` parameter to a value greater than or equal to the timeout value of SQL queries. Otherwise, if two SQL statements in one transaction are executed at an interval longer than the value of the `ob_trx_idle_timeout` parameter, the connection is terminated. However, if the `ob_trx_idle_timeout` parameter is set to an excessively large value, the session cannot be released in time, resulting in unnecessary memory consumption. Therefore, you must set this parameter to a proper value.

Procedure

Create a single private connection

Perform the following steps to create a connection:

1. Log on to ODC and click Create Connection in the upper-right corner of the Private Connection tab.

2. In the Create Connection panel, specify the fields described in the following table. If you have an OceanBase connection string, which follows the mysql -h host name -P port number -u database username@tenant name # cluster name -D default database -p ' database password ' format, you can obtain the information from the string. You can also paste the string in the field of Intelligent Parsing on the panel to automatically populate the corresponding fields.

   Field	Description
   Intelligent Parsing	You can paste a connection string to the field. ODC automatically identifies the string and fills in the corresponding fields on the page.
   Connection Type	Select MySQL or Oracle . Logical databases support only the MySQL mode.
   Endpoint	Host IP : the IP address of the server where the target database is located. Port : the port number of the server where the target database is located. Cluster name : the name of the cluster where the target database is located. This parameter is not required for logical databases. Rent name : the name of the tenant in which the target database is located. This parameter is not required for logical databases. Note You can obtain the host name and port number from the details page of the tenant in which the target database is located, such as a tenant created on Alibaba Cloud or in OceanBase Cloud Platform (OCP).</blockquote
   Database Account	Database Username: an account under the tenant. In MySQL mode, this account must have access to the default database. Database Password: the password of the account under the tenant. You can click Test Connection next to the password field to check the connection to the target database. Save Database Password : If you disable this option, the database password for the connection is not saved. Each time you use or test the connection, you must enter the database password in the password dialog box. If you enable this option, the database password for the connection is saved. You do not need to enter the database password each time you use the connection. Note An error will be returned if the configurations such as the username, password, and network settings are invalid. You can only connect to a database in the current tenant. Otherwise, a test failure error will be returned.
   Default Database/schema	When you connect to a database in a MySQL tenant, you must specify the default database for the connection. You do not need to specify this field when you connect to a database in an Oracle tenant. Note If you leave this parameter unspecified, the information_schema database is connected by default. However, if the connection to the information_schema database fails, you need to specify a database that the current user has access permissions.
   Query Tenant Views with sys Tenant Account	When Database Type is set to Physical Database, you can specify an account that has the privilege to query the SYS tenant view, and the password. This field is automatically populated when SYS is specified as the tenant in Endpoint . Account : an account having the privilege to check the views under the SYS tenant. Password : the password of the account. After you specify the account and password, click Test Connection next to the password field to verify whether the account information is valid. Note In ODC V2.3.0 and later versions, to use the import and export features, you need to configure the root@sys account in the Advanced Configuration section during connection creation. In ODC V2.4.1 and later versions, the dependency of the import and export features on the root@sys user is removed when you query views. If the root@sys account is not configured, some ODC objects such as stored procedures and functions do not support the export feature. If the root@sys account is configured, you can access the views under the SYS tenant to obtain the optimal data routing strategy, which can increase the export speed.
   Enable SSL	Select whether to enable the SSL encrypted SSL link to ensure the safe transmission of data between the client and the server. Note Users created in non-encrypted mode (require none) (require none by default) can choose whether to enable SSL according to their needs. If Enable SSL is checked, an SSL connection will be created; if not checked, a normal connection will be created. For users created through the SSL connection method (require ssl), you can create an SSL connection after checking Enable SSL; if you don’t check it, the connection will fail. Users created through require ssl do not need to upload a certificate but must check Enable SSL, otherwise the connection will fail. It is recommended to upload a certificate to verify the identity of both communication parties (not required): Upload the CA certificate, and the client will verify the identity of the server; Upload the client key and client certificate, and the server will verify the identity of the client. Users created by x509 encryption (require x509) must enable SSL and upload the CA certificate, client key and client certificate, and the client and server will verify each other's identity; uncheck **Enable SSL ** Neither uploading the certificate nor uploading the certificate will successfully create the connection. Before enabling SSL through ODC, you must confirm that SSL is enabled on the OBServer side; if you connect through OBProxy, you also need to ensure that OBProxy has enabled SSL. How to enable SSL on the server side, please refer to OBServer Transmission Encryption and ApsaraDB for OceanBase SSL Link Encryption.

3. Click Save to save the current configuration in the ODC connection list, and enter the connection name. Next time you log on to ODC, you can directly select the connection from the list. The connection configuration does not necessarily need to pass the connection test when you save the configuration.

   Note

   Before you save the configuration, you can click Test Connection to test the connection to the database. If the username and password are correct, a Connected message will appear.

4. Click Copy Connection String in the lower part of the panel to copy the connection information being edited on the current panel as a connection string if necessary. This allows you to obtain the connection information with ease.

5. Click Cancel if you do not need to save the current connection configuration. Then, a confirmation dialog box appears.

Import multiple private connections at a time

ODC V4.1.2 and later allows you to import multiple private connections at a time by uploading a configuration file. Perform the following steps:

1. Log on to ODC and click Batch Import on the Private Connection tab.

   

2. In the Batch Import panel, click Download Template, and specify the parameters of the private connections to be imported.

   

   Parameter	Description
   Connection Name	Required. The name of the connection. The name cannot contain spaces and must be unique.
   Connection Type	Required. Valid values: OB Oracle, OB MySQL, Cloud OB Oracle, Cloud OB MySQL, and ODP(Sharding) OB MySQL.
   Host IP	Required. The IP address of the server where the target database is located. You can obtain the host IP address from the details page of the tenant in which the target database is located, such as a tenant created on Alibaba Cloud or in OCP.
   Port	Required. The port number of the server where the target database is located. You can obtain the port number from the details page of the tenant in which the target database is located, such as a tenant created on Alibaba Cloud or in OCP.
   Cluster	Optional. The name of the cluster where the target database is located. This parameter is not required for logical databases.
   Tenant	Required. The name of the tenant in which the target database is located. This parameter is not required for logical databases. The tenant name cannot contain spaces.
   Username	Required. An account created in the tenant. In MySQL mode, this account must have the privilege to access the default database.
   Save Password	Required. Valid values: true and false.
   Password	The password of the account under the tenant. If the Password parameter is left unspecified, it indicates that the password is an empty string.
   Default Database	Optional. When you connect to a database in a MySQL tenant, you must specify the default database for the connection. You do not need to specify this parameter when you connect to a database in an Oracle tenant. Note If you leave this parameter unspecified in MySQL mode, the information_schema database is connected by default. However, if the connection to the information_schema database fails, you need to specify a database that can be accessed by the current user.
   Sys Tenant Account	Optional. The account that has the privilege to view the sys tenant views.
   Sys Tenant Password	Optional. The password of the account.

   Note

   When you import multiple private connections, you do not need to specify the resource group name, connection status, read-only account, and password of Read-only account. They are required when you import public connections.

   

3. Click the file pool in the Batch Import panel to open the file explorer and select the file to be imported. You can also directly drag the file to the file pool to upload it.

4. Click Import. After the connections are imported, you can view them in the list of private connections.