Purpose
This statement is used to create a dblink that accesses a specified remote database. The statement requires specifying the dblink name and providing the remote database's username, tenant name, database name, password, IP address, port number, and cluster name.
In MySQL mode of OceanBase Database, currently, only local MySQL tenants of OceanBase clusters can read data from MySQL tenants of remote OceanBase clusters. dblink can also be created between different MySQL tenants within the same cluster. OceanBase clusters' MySQL tenants cannot read data from native MySQL databases, and native MySQL databases cannot read data from OceanBase clusters' MySQL tenants.
In MySQL mode of OceanBase Database, dblink supports the following data types:
Numeric types:
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT,FLOAT,DOUBLE, andDECIMAL.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]
Considerations
| Field | Description |
|---|---|
| dblink_name | The name of the database link to be accessed. |
| IF NOT EXISTS | If IF NOT EXISTS is specified, an error will not be returned even if the specified dblink already exists. If IF NOT EXISTS is not specified and the specified dblink already exists, an error will be returned. |
| user_name | The username of the remote database. |
| tenant_name | The tenant name of the remote database. |
| password | The login password of the remote database username. If the password contains special characters other than numbers and letters (~!@#%^&*_-+=|(){}[]:;,.?/), you must enclose the password in single or double quotation marks to avoid syntax errors. |
| database_name | The database name of the remote MySQL tenant. When reading data through the dblink, the default database is accessed. If you need to access other databases, you can specify the database in the SQL statement for the table. |
| ip | The IP address of the remote database. The IP address can be the IP address of the cluster's Proxy or the IP address of an OBServer node in the cluster. If the IP address of an OBServer node is specified, the network between the local database and the specified OBServer node must be accessible.
NoteOceanBase Database also supports domain names. For example, |
| port | The port number of the remote database. The port number can be the port number of the cluster's Proxy or the port number of an OBServer node in the cluster. If the port number of an OBServer node is specified, the network between the local database and the specified OBServer node must be accessible. |
| cluster_name | The name of the remote OceanBase cluster. The cluster name is case-sensitive and must be enclosed in double quotation marks. The cluster name is required only when the specified IP and port number belong to the Proxy, and the Proxy is deployed using the ConfigUrl parameter. |
Examples
Create a
dblinknamedob_dblinkthat connects to an OBServer node of a remote OceanBase cluster. The remote database's tenant ismysql(any MySQL tenant can be specified), the username isuser1, and the database istest. You can specify the password, IP address, and port number based on your 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';Create a
dblinknamedob_dblink_proxythat connects to a remote OceanBase cluster. The remote database's tenant is the defaultmysql(any MySQL tenant can be specified), the username isuser1, and the database istest. You can specify the password, IP address, and port number based on your 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";
After creating the dblink, you can perform query and delete operations on it. For more information, see SELECT.
You can query the oceanbase.DBA_DB_LINKS view to obtain information about the created dblink. For more information, see DBA_DB_LINKS.