Create a DBLink

The DBLink feature of OceanBase Database enables cross-database access, allowing you to access remote databases from the local database.

In OceanBase Database in MySQL mode, you can create a DBLink between two MySQL tenants in the same cluster or in different clusters.

Limitations

• You cannot create a DBLink between a MySQL tenant of OceanBase Database and a MySQL database.
• At present, the DBLink write and reverse link features are not supported in MySQL mode.

Prerequisites

You must have the CREATE DATABASE LINK privilege. For information about how to grant user privileges, see Grant direct privileges.

Create a DBLink from one OceanBase database to another

To access a remote OceanBase database, you can create a DBLink from a MySQL tenant in your local OceanBase database to a MySQL tenant in the remote OceanBase database.

When you create a DBLink to access a remote database, you must specify the name of the DBLink and the username, tenant name, database name, password, IP address, port number, and cluster name of the remote database.

The SQL syntax for creating a DBLink between two MySQL tenants of OceanBase Database is as follows:

CREATE DATABASE LINK [IF NOT EXISTS] dblink_name CONNECT TO user@tenant DATABASE database_name IDENTIFIED BY 'remote_password' HOST 'ip:port' [CLUSTER "cluster_name"];

The parameters are described as follows:

• dblink_name: the name of the DBLink to be created.

• IF NOT EXISTS: used for compatibility with MySQL Database. After this parameter is specified, the system does not return an error if the DBLink to be created already exists. If this parameter is not specified and the DBLink to be created already exists, the system returns an error.

• user: the username for logging on to the remote OceanBase database.

• tenant: the tenant name of the remote OceanBase database.

• remote_password: the password for logging on to the remote OceanBase database. If the password contains special characters such as @#!, you must enclose the password with single quotation marks (' ') or double quotation marks (" ") to avoid syntax errors.

• database_name: the database name of the remote MySQL tenant. When you use the DBLink to read data, this database is accessed by default. To use the DBLink to access another database, specify the database in the SQL statement.

• ip: the IP address of the remote OceanBase database. It can be the IP address of the OceanBase Database Proxy (ODP) for connecting to the cluster or the IP address of an OBServer node in the cluster.

  If the IP address of an OBServer node is specified, network connectivity must be ensured between the local database and the specified OBServer node.

  Note

  You can also specify the domain name for accessing the remote OceanBase database, for example, example.com:port. You can use the corresponding domain name to connect to a remote database based on the actual environment.

• port: the port of the remote OceanBase database. It can be the port of the ODP for the cluster, which is 2883 by default, or the SQL port of an OBServer node in the cluster, which is 2881 by default.

  If the SQL port of an OBServer node is specified, network connectivity must be ensured between the local database and the specified OBServer node.

• cluster_name: the cluster name of the remote OceanBase database. You need to specify the cluster name only when the specified IP address and port number are those of the ODP for the cluster and the ODP is deployed by using ConfigUrl. The cluster name is case-sensitive and must be enclosed with double quotation marks (").

  Note

  You can deploy a proxy such as ODP for a cluster by using the ConfigUrl or RootService list.

  • ConfigUrl: When ODP is started, the obproxy_config_server_url parameter specified in the command is used to query the RootServer information of the OceanBase cluster.
  • RootService list: When ODP is started, the -r parameter specified in the command is used to query the RootServer information of the OceanBase cluster.

Here are some examples:

• Create a DBLink named ob_dblink that connects to a remote OceanBase database, and set the IP address and port number of the remote database to those of an OBServer node.

  CREATE DATABASE LINK IF NOT EXISTS ob_dblink CONNECT TO username@mysql DATABASE test IDENTIFIED BY '********' HOST 'xx.xx.xx.xx:2881';
  

• Create a DBLink named ob_dblink_proxy that connects to a remote OceanBase database, and set the IP address and port number to those of the cluster proxy that is deployed by using the ConfigUrl.

  CREATE DATABASE LINK IF NOT EXISTS ob_dblink_proxy CONNECT TO username@mysql DATABASE test IDENTIFIED BY '********' HOST 'xx.xx.xx.xx:2883' CLUSTER "obcluster";
  

  Notice

  Enclose the cluster name with double quotation marks (" ") to prevent the cluster name from being capitalized.

References

For more information about operations on DBLinks, see the following topics:

• Query existing DBLinks

• Use a DBLink to access data in a remote database

• Drop a DBLink