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 types:
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,FLOAT,DOUBLE, andDECIMAL.Date and time types:
DATE,TIME,YEAR,DATETIME, andTIMESTAMP.Character types:
CHAR,VARCHAR,TINYBLOB,TINYTEXT,BLOB,TEXT,MEDIUMTEXT,LONGBLOB, andLONGTEXT.Other types:
ENUMandSET.
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
| Parameter | Description |
|---|---|
| dblink_name | The name of the DBLink. |
| IF NOT EXISTS | Specifies not to report an error even if the DBLink to be created already exists. If IF NOT EXISTS is not specified, the system reports an error in this case. |
| user_name | The username of the remote database. |
| tenant_name | The tenant name of the remote database. |
| password | The password corresponding to the username of the remote database. If the password contains special characters ~!@#%^&*_-+=|(){}[]:;,.?/, enclose it in 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. If you need to access another database, specify the database for the target table in the SQL statement. |
| ip | The IP address of the remote database. It can be the IP address of the OceanBase Database Proxy (ODP) of the OceanBase cluster or the IP address of an OBServer node in the cluster. If you use the IP address of an OBServer node, the local database and the OBServer node must be able to communicate with each other. |
| port | The port number of the remote database. It can be the port number of the ODP of the cluster or the port number of an OBServer node in the cluster. If you use the port number of an OBServer node, the local database and the OBServer node must be able to communicate with each other. |
| cluster_name | The name of the remote OceanBase cluster. The cluster name must be specified only when the IP address and port number are those of an ODP and the startup method of the ODP is ConfigUrl. The cluster name is case-sensitive and must be enclosed in double quotation marks (" "). |
Examples
Create a DBLink named
ob_dblinkthat can be used to connect to an OBServer node in a remote OceanBase cluster. The tenant of the remote database ismysql. (You can specify any MySQL tenant.) The username isuser1, and the database name istest. 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 affectedCreate a DBLink named
ob_dblink_proxythat can be used to connect to a remote OceanBase cluster. The tenant of the remote database ismysql, which is the default one. (You can specify any MySQL tenant.) The username isuser1and the database name istest. 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.