All Products
Search
Document Center

SQL queries across instances

Last Updated: Sep 24, 2019

Background

With the increase of business complexity and data volumes, more and more enterprises choose to partition their online business databases vertically or horizontally. Some may even choose different types of database to suit business requirements. In the meantime, business data is "scattered" in multiple database instances. How to collect and query these data is a critical issue for users.

For example, an e-commerce startup stores its information about members, products, and orders in a SQL Server instance. But the quantity of members and transactions are still increasing and a single instance can no longer cope with the growing business needs. In order to control costs, products and order data are removed from SQL Server and stored separately in two MySQL instances. Previously, you could log on to one instance and execute one SQL statement to query data from three tables. Now you cannot perform this operation due to the partitioned database.

In this circumstance, DMS provides you with a solution based on database links. You can query data across multiple database instances.

In Oracle, you can log on to an Oracle database and create a database link to access remote tables in another database.

Database links of DMS are available for all database instances . You can use it to refer to any database instance.

  • Each database link is associated with a specific database instance. Take MySQL for example, a database link is associated with the IP address: port of the MySQL database.
  • You can use database links to refer to databases such as MySQL, SQL Server, PostgreSQL, Oracle, and Redis.
  • In SQL statements, prefix the database and the table name with "DBLink" such as DBLink.database.table to query instances in other databases.
  • The name of a database link contains letters, numbers, and underscores (_). Example

Procedure

  1. Open the Cross-instance Query page. Open DMS for MySQL, choose SQL Operations > Cross-instance Query on the top menu bar. 1

On the Cross-instance Query page, click Create on the Cross-instance DBLink page to create a database link that refers to another instance. Enter the name of the database, the IP address of the target instance, and the username and password. Click Save. 4

After a database link is created, you can manage the existing database links on the right-side menu bar, including adding, deleting, and altering a database link. You can also unfold a database link to view which databases and tables it refers to, or the information of the table structure. 5

On the Cross-instance Query page, click Execute. 6

SQL statements

  • Query tables of a logged-in instance. Tips: If the database link of the logged-on instance is link1, and the target database is the user database, you cannot enter DBLink or the database name in the SQL statement.

     Method 1: select * from link1.user.user_info;
    Method 2: select * from user_info;
    
  • Query database data and table data in another instance.

     select * from link2.orders.order_info;
    
  • Correlation queries of multiple instances. ``` select u.name, i.item_name, i.money, o.status from user_info as u, link2.orders.order_info as o, link3.item.item_info as i

where u.id= o.user_id and o.item_id= i.id and i.id= 1;

- Use the create table as select statement to copy the table structure and data of link2 to link3.

create table link3.item.order_copy as select * from link2.orders.order_info;


- Use the insert into select statement to copy data of link2 to the source instance.

insert into order_bak select * from link2.orders.order_info; ```

Features supported

No. Feature Status and Schedule
1 Join multiple table across multiple MySQL instances Supported
2 create table as select Supported
3 insert into select Supported
4 insert Supported
5 update/delete November 2018
6 SQL Server, PostgreSQL, and Redis November 2018