Specify the storage location of a database or a table

Prerequisites

The instance version is PolarDB-X 5.4.10 or later. For more information about how to view an instance version, see View the version of an instance.

Attention

After the storage location of a database or a single table is specified by using the LOCALITY syntax, the storage location cannot be modified.

Specify the storage location of a database

When you create a database, specify the storage location of the database so that the data in the database can be isolated from that in another database.

1. If an instance is available, PolarDB-X you can execute the following statement to view the storage nodes in the instance:

   SHOW STORAGE;

   The following result is returned:

   +---------------------------+--------------------+------------+-----------+----------+-------------+
   | STORAGE_INST_ID           | LEADER_NODE        | IS_HEALTHY | INST_KIND | DB_COUNT | GROUP_COUNT |
   +---------------------------+--------------------+------------+-----------+----------+-------------+
   | polardbx-storage-1-master | 1.1.1.1:3308       | true       | MASTER    | 2        | 6           |
   | polardbx-storage-meta     | 1.1.1.1:3306       | true       | META_DB   | 2        | 2           |
   | polardbx-storage-0-master | 1.1.1.1:3306       | true       | MASTER    | 2        | 8           |
   +---------------------------+--------------------+------------+-----------+----------+-------------+

   Note

   • polardbx-storage-1-master and polardbx-storage-0-master are the storage nodes that store user data.
   • polardbx-storage-meta is the storage node of metadata. This storage node does not store user data.
2. Execute the following statement to create a database in the instance and specify the storage location of the database as the polardbx-storage-0-master node:

   CREATE DATABASE db1 LOCALITY='dn=polardbx-storage-0-master';

   Note

   • If the storage location of the database is not specified when you create the database, the system evenly distributes the database among all the storage nodes by default.
   • The storage locations of table shards in the database are consistent with those of the database so that data in a table shard can be isolated from the data in another table shard.
   • The storage location of a single table in a database is not affected by the storage location of the database. If the storage location is not specified when you create a single table, the table is randomly stored on a storage node. PolarDB-X In addition, if you continue to create single tables on the instance and do not specify the storage location for these tables, the tables are stored on the storage node.
3. After the tables are created, you can view the storage location of the database by executing the following statement:

   SHOW CREATE DATABASE db1;

   The following result is returned:

   +----------+------------------------------------------------------------------------+
   | DATABASE | CREATE DATABASE                                                        |
   +----------+------------------------------------------------------------------------+
   | db1      | CREATE DATABASE `db1` /* LOCALITY = "dn=polardbx-storage-0-master" */  |
   +----------+------------------------------------------------------------------------+
   1 row in set

4. You can also view the logical and physical database shards created in the database by executing the following statement:

   SHOW DS;

   The following result is returned:

   +----+---------------------------+-----+------------------+------------+---------+
   | ID | STORAGE_INST_ID           | DB  | GROUP            | PHY_DB     | MOVABLE |
   +----+---------------------------+-----+------------------+------------+---------+
   | 0  | polardbx-storage-0-master | db1 | DB1_000000_GROUP | db1_000000 | 1       |
   | 1  | polardbx-storage-0-master | db1 | DB1_000001_GROUP | db1_000001 | 1       |
   | 2  | polardbx-storage-0-master | db1 | DB1_P00000_GROUP | db1_p00000 | 1       |
   | 3  | polardbx-storage-0-master | db1 | DB1_SINGLE_GROUP | db1_single | 0       |
   +----+---------------------------+-----+------------------+------------+---------+
   4 rows in set

   Note In the result, the row whose ID is 0 indicates that the logical database shard DB1_000000_GROUP and the physical database shard db1_000000 are created in the db1 database. The db1 database is stored on the storage node polardbx-storage-0-master.

Specify the storage location of a table

When you create a single table, specify the storage location of the table so that data can be evenly distributed to storage nodes.

CREATE TABLE tb1 (id int) LOCALITY='dn=polardbx-storage-0-master';

SHOW TOPOLOGY FROM tb1;

The following result is returned:

+----+------------------+------------+
| ID | GROUP_NAME       | TABLE_NAME | 
+----+------------------+------------+
| 0  | DB1_000000_GROUP | tb1        |
+----+------------------+------------+
1 row in set