CREATE DATABASE LINK

Purpose

You can use this statement to create a DBLink for accessing a specified remote database. In this statement, you must specify the DBLink name and the username, tenant name, database name, password, IP address, port number, and cluster name of the remote database.

In MySQL mode of OceanBase Database, you can create a DBLink to access a MySQL tenant in a remote OceanBase cluster from the local MySQL tenant. You can also create a DBLink to access a MySQL tenant in the same OceanBase cluster as the local MySQL tenant. You cannot use a DBLink to access a native MySQL database from a MySQL tenant in an OceanBase cluster, and vice versa.

A DBLink in OceanBase Database in MySQL mode can be used to read the following types of data:

• Numeric data types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, and DECIMAL

• Datetime data types: DATE, TIME, YEAR, DATETIME, and TIMESTAMP

• Character types: CHAR, VARCHAR, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMTEXT, LONGBLOB, and LONGTEXT

• Other data types: ENUM and SET

Syntax

CREATE DATABASE LINK [IF NOT EXISTS] dblink_name CONNECT TO user_name@tenant_name DATABASE database_name IDENTIFIED BY password HOST 'ip:port' [CLUSTER cluster_name];

Parameters

Examples

• Create a DBLink named ob_dblink that can be used to connect to an OBServer node in a remote OceanBase cluster. The tenant of the remote database is mysql. (You can specify any MySQL tenant.) The username is user1, and the database name is test. You can specify the password, IP address, and port number based on the actual environment.

  obclient> CREATE DATABASE LINK IF NOT EXISTS ob_dblink  CONNECT TO user1@mysql DATABASE test IDENTIFIED BY '******' HOST 'xx.xx.xx.xx:xx';
  Query OK, 1 row affected
  

• Create a DBLink named ob_dblink_proxy that can be used to connect to a remote OceanBase cluster. The tenant of the remote database is mysql, which is the default one. (You can specify any MySQL tenant.) The username is user1 and the database name is test. You can specify the password, IP address, and port number based on the actual environment.

  obclient> CREATE DATABASE LINK IF NOT EXISTS  ob_dblink_proxy CONNECT TO user1@mysql DATABASE test IDENTIFIED BY '******' HOST 'xx.xx.xx.xx:xx' CLUSTER "obcluster";
  Query OK, 1 row affected
  

After a DBLink is created, you can use it in queries or drop it when it is no longer needed. For more information about how to use a DBLink in queries, see SELECT.

You can query the oceanbase.DBA_DB_LINKS view for information about the created DBLink. For more information, see oceanbase.DBA_DB_LINKS.

References

SELECT

oceanbase.DBA_DB_LINKS