ApsaraDB for SelectDB:Multiple compute clusters

How it works

ApsaraDB for SelectDB uses a cloud-native compute-storage separation architecture. In this architecture, upper-layer computing resources share the same set of data. ApsaraDB for SelectDB supports multiple compute clusters based on this architecture. An instance can contain multiple compute clusters that share the underlying data storage.

A compute cluster is similar to a computing resource group or a compute queue in a distributed system. A compute cluster coordinates one or more compute units to complete a computing task. In an ApsaraDB for SelectDB instance, the computing resources of multiple compute clusters are physically isolated. You can use these compute clusters to process different workloads. For example, isolate data reads from data writes or isolate online data from offline data. This way, you can isolate workloads and prevent mutual interference among different business systems.

Example

In this example, an instance contains multiple compute clusters and two accounts test_01 and test_02 are created. The test_01 account is used to process the compute requests of Business System A, and is granted the permissions to access the cluster_01 cluster. The test_02 account is used to process the compute requests of Business System B, and is granted the permissions to access the cluster_02 cluster. Both accounts can access all the data in the instance.

1. Connect to the instance by using the admin account and initialize the test_01 and test_02 accounts. Sample code:

   mysql> CREATE USER test_01 IDENTIFIED BY 'testPassword';
   Query OK, 0 rows affected (0.06 sec)
   
   mysql> CREATE USER test_02 IDENTIFIED BY 'testPassword';
   Query OK, 0 rows affected (0.04 sec)
   
   mysql> GRANT ALL ON *.* TO test_01;
   Query OK, 0 rows affected (0.07 sec)
   
   mysql> GRANT ALL ON *.* TO test_02;
   Query OK, 0 rows affected (0.05 sec)
   
   mysql> GRANT USAGE_PRIV ON CLUSTER cluster_01 TO test_01;
   Query OK, 0 rows affected (0.05 sec)
   
   mysql> GRANT USAGE_PRIV ON CLUSTER cluster_02 TO test_02;
   Query OK, 0 rows affected (0.04 sec)

2. Query the available clusters within the admin account. Sample code:

   mysql> SHOW clusters;
   +------------+------------+-------+
   | cluster    | is_current | users |
   +------------+------------+-------+
   | cluster_01 | FALSE      |       |
   | cluster_02 | TRUE       |       |
   +------------+------------+-------+
   2 rows in set (0.04 sec)

   The results show that the admin account has the permissions to access the two clusters and accesses the cluster_02 cluster by default.

3. Connect to the instance by using the test_01 account and query the available clusters. Sample code:

   mysql> SHOW clusters;
   +------------+------------+-------+
   | cluster    | is_current | users |
   +------------+------------+-------+
   | cluster_01 | TRUE       |       |
   +------------+------------+-------+
   1 row in set (0.03 sec)

   The results show that the test_01 account is allowed to access only the cluster_01 cluster.

4. Insert test data by using the test_01 account. Sample code:

   mysql> CREATE TABLE golds_log
   (
       user_id  bigint,
       accounts string,
       change_type string,
       golds    bigint,
       log_time int
   ) DISTRIBUTED BY HASH(`user_id`) BUCKETS 3;
   Query OK, 0 rows affected (0.06 sec)
   
   mysql> INSERT INTO golds_log VALUES
       -> (3645356,'wds7654321(4171752)','swim',1700,152607152),
       -> (2016869,'dqyx123456789(2376699)','noise',1140,152607152),
       -> (3630468,'dke3776611(4156064)','white',1200,152602752);
   Query OK, 3 rows affected (0.20 sec)
   {'label':'insert_9707a9905aab4805_b8b9d28aadcd96e6', 'status':'VISIBLE', 'txnId':'15313504260617216'}

5. Connect to the instance by using the test_02 account and query the available clusters. Sample code:

   mysql> SHOW clusters;
   +------------+------------+-------+
   | cluster    | is_current | users |
   +------------+------------+-------+
   | cluster_02 | TRUE       |       |
   +------------+------------+-------+
   1 row in set (0.04 sec)

   The results show that the test_02 account is allowed to access only the cluster_02 cluster.

6. Query test data by using the test_02 account. Sample code:

   mysql> SELECT * FROM golds_log;
   +---------+------------------------+------------------+-------+-----------+
   | user_id | accounts               | change_type      | golds | log_time  |
   +---------+------------------------+------------------+-------+-----------+
   | 3630468 | dke3776611(4156064)    | noise            |  1200 | 152602752 |
   | 2016869 | dqyx123456789(2376699) | whitewo          |  1140 | 152607152 |
   | 3645356 | wds7654321(4171752)    | swim             |  1700 | 152607152 |
   +---------+------------------------+------------------+-------+-----------+
   3 rows in set (0.13 sec)

   The results show that different clusters share the data in the same instance.

7. Connect to the instance by using the admin account and create the test_03 account. Do not grant cluster access permissions to the test_03 account. Sample code:

   mysql> CREATE USER test_03 IDENTIFIED BY 'testPassword';
   Query OK, 0 rows affected (0.04 sec)
   
   mysql> GRANT ALL ON *.* TO test_03;
   Query OK, 0 rows affected (0.04 sec)

8. Connect to the instance by using the test_03 account and query test data. In this case, an error is reported because the test_03 account has no permissions to access clusters in the instance. Sample code:

   mysql> SELECT * FROM golds_log;
   ERROR 1105 (HY000): errCode = 2, detailMessage = 90363 have no queryable replicas. err: 90364's backend -1 does not exist or not alive, or you may not have permission to access the current cluster, clusterName=null