Manage OceanBase Data Sources with NineData

NineData's database DevOps is a set of cloud-native SQL development tools that provide comprehensive and efficient enterprise-level features as well as high-security level support, empowering enterprises with better core database management and maintenance capabilities. This article introduces how to use NineData's database DevOps features to manage OceanBase data sources.

Feature Introduction

Database DevOps has powerful features such as data asset management, data querying, SQL execution, data editing, data import and export, SQL approval process, SQL standard pre-inspection, approval process, and sensitive data protection, helping users quickly complete various environmental data management tasks and supporting the digital transformation of enterprises.

• SQL Editor: Supports AI collaboration, intelligent SQL, complete syntax highlighting, auto-completion, object type recognition, error prompts, and more.
• Access Control: Supports managing permissions at all levels of the enterprise through roles (Role), and also supports configuring custom permissions for individual users, making permission management operations unrestricted and flexible.
• Companion Tools: SQL tasks, schema design and changes, data import and export, SQL review, batch database query changes, sensitive data protection, and other capabilities provide a one-stop management for the entire lifecycle of the database, making database operations and maintenance more efficient and secure.
• SQL Development Standards: Used to define enterprise SQL writing standards, providing more than 200 rules, in addition to improving SQL quality, preventing slow SQL, maintaining consistent coding styles, reducing potential errors and performance issues. For example:
  • For potential syntax conflicts that may exist when making changes to OceanBase for MySQL, conflict check rules are provided, forcing users to execute separately.
  • When querying or updating partitioned tables in OceanBase for MySQL, it is required to include the partition key in the WHERE condition to quickly locate the shard location and submit execution efficiency.
• Approval Process: Simplifies complex requests, decision-making, and approval steps, providing a unified and clear approval process, standardizing the approval process, reducing errors and risks, and improving the overall efficiency of the approval process. Configurable processes also meet the customized needs of enterprises and can adapt to various differentiated scenarios.
• SSO Single Sign-On: Unifies the identity management of enterprise members, simplifies user access methods, and enhances data security, reducing the risk of password leaks.
• Audit Logs: NineData provides centralized user behavior recording and monitoring methods, allowing enterprises to easily audit user activities, track security events, and take necessary measures.

Prerequisites

• Have registered a NineData platform account. For more information, please refer to Register NineData Account.

• Under (organization mode), you must have read-only, DML, or DDL permissions for the target data source.

  tip

  Read-only permissions only support viewing the database and querying data operations.

• In the commercial versions (, ), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOpsservice. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

  

Step One: Add OceanBase for MySQL Data Source

1. Log in to the NineData Console.

2. In the left navigation bar, click .

3. Click the tab and click on the page. In the pop-up data source type window, select > OceanBase for MySQL, and configure the parameters on the page according to the table below.

   tip

   If you make a mistake, you can click the icon at the top of the page to reselect.

   Click to expand table details

   Parameter	Description
   	Enter the name of the data source. To facilitate subsequent search and management, please try to use meaningful names.
   	Select the access method of the data source. Supports access through , , SSH Tunnel three ways. : Access the data source through a public network address. : A secure and fast intranet access method provided by NineData, which requires the data source host to be connected first. For connection methods, please refer to Add Gateway. SSH Tunnel: Access the data source through an SSH tunnel.
   Select configuration items	: The public network connection address and port of the data source.
   Select configuration items	: Select the NineData gateway installed on the host where the data source is located. : Can be written as localhost (the data source is on the local machine) or the intranet IP of the host where the data source is located.
   Select SSH Tunnel configuration items	: Enter the public IP or domain name of the server where the target data source is located, as well as the corresponding port number (the default port number for the SSH service is 22). : Select the SSH authentication method. : Connect through (i.e., the server's login name) and (i.e., the server's login password). : Enter the login username of the server where the target data source is located. : Enter the login password of the server where the target data source is located. (recommended): Connect through and . : Enter the login username of the server where the target data source is located. : Click to upload the private key file, which is the key file without a suffix. If you have not created one yet, please refer to Generate SSH Tunnel Key File. : Enter the password set when generating the key file. If you did not set a password during the key generation process, leave this field blank. Note: After the SSH configuration is completed, you need to click the on the right, there may be the following two results: Prompt : Indicates that the SSH Tunnel has been opened. Prompt error message: Indicates that the connection failed, you need to troubleshoot the cause of the error according to the prompt and retry. : Can be written as localhost (the data source is on the local machine) or the intranet IP of the host where the data source is located.
   	The username of the data source.
   	The password of the data source.
   	Select the region closest to your data source location to effectively reduce network latency.
   	Choose according to the actual business purpose of the data source, as the environmental identifier of the data source. The default provides and environments, and also supports you to create a custom environment. Note: Under the organization mode, the database environment can also be applied to permission policy management, for example, the default role only supports access to data sources in the environment and cannot access other environments. More information, please refer to Manage Roles.
   	Whether to use SSL encryption to access the data source (default on). If the data source enforces SSL encrypted connection, this switch must be turned on, otherwise the connection will fail. Click the switch on the right to turn on or off encrypted transmission. Click the > on the left side of to expand detailed configuration. : Supports the following two methods. : Automatically detect the server's SSL status, if the server has enabled SSL, it will connect through SSL first, if the server has not enabled SSL, it will connect in a non-SSL manner. : Always use SSL to connect to the data source, if the server does not support this method or cannot establish an SSL connection for other reasons, the connection will fail. : Specify the SSL-Cipher encryption algorithm. : If the OceanBase server uses a certificate issued by a self-signed CA, you need to upload the root certificate of this CA here. : Check the box to verify the server's name and IP address, etc., to ensure that the connection is to the real OceanBase server and to prevent man-in-the-middle attacks. : If the OceanBase server requires the client to provide a certificate, you need to upload the client's certificate and key here, and the OceanBase server will verify the information you uploaded to ensure the security of the connection. Note: Under normal circumstances, if the OceanBase server supports using SSL encrypted connection, you only need to select , and you do not need to configure other options, the system will automatically generate a key for you to connect.

4. After all configurations are completed, click on the right side of to test whether the data source can be accessed normally. If prompted with , you can click to complete the addition of the data source. Otherwise, please recheck the connection settings until the connection test is successful.

Step Two: Configure SQL Development Standards

1. Log in to the NineData Console.

2. In the left navigation bar, click .

3. On the page, click the tab and click in the upper right corner.

4. In the pop-up dialog box, configure the following parameters and click .

   • : Select which standard to create a new standard based on.
   • : Enter the name of the new standard, support 1 ~ 32 characters. To facilitate subsequent search and management, please try to use meaningful names.
   • (optional): Enter a description for the new standard, support 1 ~ 64 characters.

5. The console automatically jumps to the page, where you can edit and switch on and off the target rules.

   tip

   SQL development rules are classified according to major modules, and you can switch by clicking the category tabs.

6. Return to the page, click the tab, find the target data source name, and click in the column on the right.

7. In the pop-up window, uncheck the box on the left side of under , then select the newly configured standard and click .

The development standard has been configured, and all subsequent operations by users on this data source in NineData will be controlled based on this SQL development standard.

Follow-up Steps

• Perform CRUD operations on the OceanBase database through the SQL Console
• Submit change applications to the OceanBase database through SQL tasks
• Set sensitive columns for OceanBase
• Export data from the OceanBase database to a file
• Import data files into OceanBase
• Multi-environment change management for OceanBase
• Pre-release review of SQL code for OceanBase
• Batch query grouping of OceanBase database tables
• Batch change grouping of OceanBase database tables