Create a DBLink

OceanBase Database provides DBLinks to support cross-data source access. You can use a DBLink to access a remote database from your local database.

The MySQL mode of OceanBase Database allows you to create a DBLink between two MySQL tenants of the same or different OceanBase 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 the MySQL mode of OceanBase Database.

Prerequisites

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

Create a DBLink from an OceanBase database to another OceanBase database

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

When you create a DBLink to access a remote database, you must specify the name of 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"];

where

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

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

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

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

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

• database_name specifies 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 specifies the IP address of the remote OceanBase database. It can be the IP address of the OceanBase Database Proxy (ODP) for 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.

• port specifies 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 specifies the name of the cluster on 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 of 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.
  • RoortService 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. When you create the DBLink, 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. When you create the DBLink, set the IP address and port number to those of the cluster proxy that is deployed by using the ConfigUrl list.

  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:

• View a DBLink

• Use a DBLink to access data in a remote database

• Drop a DBLink