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 OceanBase Database's MySQL mode, currently, only local OceanBase clusters' MySQL tenants can read data from remote OceanBase clusters' MySQL tenants. dblink can also be created between different MySQL tenants within the same cluster. However, it is not supported for a MySQL tenant in an OceanBase cluster to read data from a native MySQL database, or for a native MySQL database to read data from a MySQL tenant in an OceanBase cluster.
In OceanBase Database's MySQL mode, the dblink supports reading 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 | Specifies the name of the database link to access. |
| IF NOT EXISTS | If you specify IF NOT EXISTS, an error will not be returned even if the specified dblink already exists. If you do not specify this clause and the specified dblink already exists, an error will be returned. |
| user_name | Specifies the username of the remote database. |
| tenant_name | Specifies the tenant name of the remote database. |
| password | Specifies the login password for the username of the remote database. If the password contains special characters other than numbers and letters (such as ~!@#%^&*_-+=|(){}[]:;,.?/), you need to 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, it defaults to accessing this database. If you need to access other databases, you can specify the specific database in the SQL statement for the table. |
| ip | Specifies 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 as addresses. For example, |
| port | Specifies 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 | Specifies 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 IP and port number belong to the Proxy, and the Proxy is deployed using the ConfigUrl. |
Examples
Create a
dblinknamedob_dblinkthat connects to an OBServer node in 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'; Query OK, 1 row affectedCreate 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"; Query OK, 1 row affected
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.