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 another MySQL tenant in the same OceanBase cluster from the current MySQL tenant. You cannot use a DBLink to access a native MySQL database from a MySQL tenant in an OceanBase cluster, or 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, andDECIMALDatetime data types:
DATE,TIME,YEAR,DATETIME, andTIMESTAMPString data types:
CHAR,VARCHAR,TINYBLOB,TINYTEXT,BLOB,TEXT,MEDIUMTEXT,LONGBLOB, andLONGTEXTOther data 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 | If you specify IF NOT EXISTS and the DBLink to be created already exists, the system does not report an error and does not create the DBLink again. If you do not specify this parameter and the DBLink to be created already exists, the system reports an error. |
| 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 any special character other than digits and letters, it must be enclosed in single or double quotation marks to avoid syntax errors. Special characters are ~!@#%^&*_-+=\|(){}[]:;,.?/. |
| 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.
NoteYou can also specify the domain name for accessing the remote OceanBase database, for example, |
| 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, see SELECT.
You can query the oceanbase.DBA_DB_LINKS view for information about the created DBLink. For more information, see DBA_DB_LINKS.