This topic describes how to create a PolarDB O Edition cluster, specify basic configurations, and connect to the cluster. Then, you can familiarize yourself with the entire process about how to purchase and use a PolarDB O Edition cluster.

Procedure

To purchase and use a PolarDB O Edition cluster, you must perform the following steps:

  1. Step 1: Create a PolarDB O Edition cluster
  2. Step 2: Configure the cluster whitelist
  3. Step 3: Create a database account
  4. Step 4: Connect to the database cluster
  5. Step 5: Create a database
  6. Step 6: Query data

Step 1: Create a PolarDB O Edition cluster

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the page, click Create Cluster.
  3. Select Subscription or Pay-As-You-Go.
    Note
    • Subscription: An upfront payment is required for two compute nodes when you create the cluster. The two compute nodes are one primary node and one read-only node. The storage fee is charged based on the used storage and is deducted from your account on an hourly basis. The Subscription billing method is more cost-effective than the pay-as-you-go billing method if you want to use the new cluster for a long period of time. You are offered larger discounts for longer subscription periods.
    • Pay-As-You-Go: An upfront payment is not required. You are charged by hour for the compute nodes and the storage space that is consumed by the actual amount of data. The charges are deducted from your account balance on an hourly basis. We recommend that you select the pay-as-you-go billing method for short term use. You can reduce costs by releasing the cluster based on your business requirements.
  4. Specify the following parameters.
    Parameter Description
    Region The region where the cluster is deployed. You cannot change the region after you purchase the cluster.
    Note Ensure that the PolarDB cluster and the Elastic Compute Service (ECS) instance to be connected are deployed in the same region. Otherwise, the cluster and the ECS instance cannot communicate through the internal network and can communicate through only the Internet. This compromises the performance.
    Create Type The method to create a PolarDB cluster.
    • Create Primary Cluster: creates a PolarDB cluster.
    • Restore from Recycle: creates a cluster by restoring a backup of a deleted cluster from the recycle bin.
      • Source Version: the version of the cluster that has been deleted.
      • Deleted Clusters: the name of the cluster that has been deleted.
      • Backup History: select the backup that you want to restore.
    Note You can select other options to create databases of other engines.
    Primary Availability Zone The primary zone where the cluster is deployed.
    • Each zone is an independent geographical location in a region. The zones that are deployed in the same region are similar.
    • You can choose to create your PolarDB cluster in the same zone as the ECS instance or in the zone that is different from the zone of this instance.
    • You need to select only the primary zone. The system automatically selects a secondary zone.
    Network Type The value of this parameter can be only VPC. You do not need to select the value of this parameter.
    VPC

    VSwitch

    Ensure that the PolarDB cluster to be created and the ECS instance to be connected are deployed in the same VPC. Otherwise, the cluster and the ECS instance cannot communicate over the internal network to achieve the optimal performance.
    • If you have created a VPC that meets your network plan, select the VPC. For example, if you have created an ECS instance and the VPC where the ECS instance is deployed meets your network plan, select this VPC.
    • If you do not create a VPC that meets your network plan, use the default VPC and the default vSwitch.
      • Default VPC:
        • The default VPC is a unique VPC in the selected region.
        • The default VPC uses a 16-bit subnet mask. For example, the CIDR block 172.31.0.0/16 provides up to 65,536 private IP addresses.
        • The default VPC does not consume the quota of VPCs that you can create.
      • Default vSwitch:
        • The default vSwitch is a unique vSwitch in your selected zone.
        • The default vSwitch uses a 20-bit subnet mask. For example, the CIDR block 172.16.0.0/20 provides up to 4,096 private IP addresses.
        • The default vSwitch does not consume the quota of vSwitches that you can create in a VPC.
    • If the default VPC and vSwitch cannot meet your requirements, you can create your own VPC and vSwitch. For more information, see Create a VPC.
    Compatibility
    • MySQL 8.0: fully compatible with MySQL 8.0. MySQL 8.0 supports parallel queries. In specific scenarios such as TPC-H tests, the performance increases by 10 times. For more information, see Parallel query.
    • MySQL 5.7: fully compatible with MySQL 5.7.
    • MySQL 5.6: fully compatible with MySQL 5.6.
    • PostgreSQL 11: fully compatible with PostgreSQL 11.
    • Compatible with Oracle Syntax: highly compatible with the Oracle syntax. For more information, see Oracle compatibility.
    Edition By default, this parameter is set to .
    Node Specification Select the specifications as needed. All nodes in the PolarDB cluster are dedicated nodes with stable and reliable performance.

    For more information about compute node specifications, see Specifications and pricing.

    Nodes If the source cluster edition is , the system creates a primary node and a read-only node that have the same specification by default. You do not need to specify this parameter.
    Note If the primary node fails, the system uses the read-only node as the primary node and creates another read-only node. For more information about read-only nodes, see Architecture.
    Storage Cost You do not need to specify this parameter. The system charges you on an hourly basis based on the actual data usage. For more information, see Specifications and pricing.
    Note You do not need to select a storage capacity when you create a cluster. The system automatically scales the storage capacity based on the amount of data to be stored.
    Enable TDE Specify whether to enable Transparent Data Encryption (TDE). After TDE is enabled, PolarDB encrypts cluster data files. You do not need to modify the code to allow access to the services. The read and write performance is reduced by 5% to 10%.
    Note You cannot disable TDE after TDE is enabled.
    Cluster Name Enter the name of the cluster. The name must meet the following requirements:
    • It cannot start with http:// or https://.
    • It must be 2 to 256 characters in length.

    If this parameter is left empty, the system automatically generates a cluster name. You can change the cluster name after the cluster is created.

    Resource Group Select a required resource group from available resource groups. For more information about how to create a resource group, see Create a resource group.
    Note A resource group is a container that contains a group of resources in an Alibaba Cloud account. You can manage these resources in a centralized manner. A resource belongs to only one resource group. For more information, see Use RAM to create and authorize resource groups.
  5. Specify the Number parameter and click Buy Now.
    Note You can create a maximum of 50 clusters at a time. This allows you to create multiple clusters in specific scenarios. For example, you can enable multiple game servers at a time.
  6. On the Confirm Order page, confirm your order information. Read and accept the terms of service, and then click Activate Now.

    After you complete the activation, it takes 10 to 15 minutes to create the cluster. Then, the newly created cluster is displayed on the Clusters page.

    Note
    • If nodes in the cluster are in the Creating state, the cluster is being created and unavailable. The cluster is available only if it is in the Running state.
    • Make sure that you have selected the region where the cluster is deployed. Otherwise, you cannot view the cluster.
    • We recommend that you purchase subscription PolarDB storage plans to store a large amount of data. Storage plans are more cost-effective than pay-as-you-go storage. You are offered larger discounts if you purchase storage plans that provide larger storage capacities. For more information, see Purchase a storage plan.

Step 2: Configure the cluster whitelist

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region where the cluster is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane, choose Settings and Management > Whitelists.
  5. On the Whitelists page, you can click Add IP Whitelist to add an IP whitelist or click Modify to modify an existing IP whitelist.
    Modify an IP whitelist
    • Add an IP whitelist
      1. Click Add IP Whitelist.
      2. In the Add IP Whitelist panel, specify the name of the IP whitelist and enter the IP addresses that are allowed to access the cluster. Add an IP whitelist
        Note The name of the IP whitelist must meet the following requirements:
        • The name can contain lowercase letters, digits, and underscores (_).
        • The name must start with a letter and end with a letter or digit.
        • The name must be 2 to 120 characters in length.
    • Modify an IP whitelist
      1. On the right side of an IP whitelist name, click Modify.
      2. In the Modify Whitelist panel, enter the IP addresses that are allowed to access the cluster. Modify
        Note
        • A default IP whitelist that contains only the IP address 127.0.0.1 is automatically created for each cluster. This IP whitelist blocks all IP addresses.
        • If you set an IP whitelist to a percent sign (%) or 0.0.0.0/0, all IP addresses are allowed to access the cluster. We recommend that you do not use this configuration unless necessary because it compromises database security.
  6. Click OK.
    Note You can create at most 50 IP whitelists and add at most 1,000 IP addresses or CIDR blocks to the 50 IP whitelists.

Step 3: Create a database account

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region where the cluster is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the left-side navigation pane, choose Settings and Management > Accounts.
  5. On the page that appears, click Create Account.
  6. In the Create Account pane, configure the following parameters.
    Parameter Description
    Account Name

    Enter an account name. The account name must meet the following requirements:

    • It must start with a lowercase letter and end with a letter or a digit.
    • It can contain lowercase letters, digits, and underscores (_).
    • It must be 2 to 16 characters in length.
    • It cannot be a system reserved username, such as root or admin.
    Account Type
    • To create a privileged account, select Privileged Account.
    • To create a standard account, select Standard Account.
    Password Enter an account password. The password must meet the following requirements:
    • It must contain at least three of the following character types: uppercase letters, lowercase letters, digits, and special characters.
    • It must be 8 to 32 characters in length.
    • It can contain the following special characters:

      ! @#$%^&*()_+

    Confirm Password Enter the password again.
    Description Enter the information about the account to facilitate subsequent account management. The description must meet the following requirements:
    • It cannot start with http:// or https://.
    • It must start with a letter.
    • It can contain letters, digits, underscores (_), and hyphens (-).
    • It must be 2 to 256 characters in length.
  7. Click OK.

Step 4: Connect to the database cluster

In this step, Data Management (DMS) is used to show how to connect to a database cluster. To connect to a PolarDB O Edition cluster through a client, perform the following steps:

  1. Log on to the PolarDB console.
  2. In the upper-left corner of the console, select the region where the cluster is deployed.
  3. Find the cluster and click the cluster ID.
  4. In the upper-right corner of the Overview page, click Log On to Database.
  5. In the dialog box that appears, enter the database account and password that you create in the PolarDB cluster.
    Use DMS to connect to a PolarDB cluster
  6. Click Login.
    Note If you are using DMS to connect to the PolarDB cluster for the first time, you are prompted to set the whitelist. Click Configure Whitelist to complete the authorization.
  7. After you log on to DMS, refresh the page. In the left-side navigation pane, click Logged in instance.
  8. Find and double-click the name of the database that you want to manage. Then, you can manage the database.

For more information about how to connect to a PolarDB O Edition cluster, see Connect to a PolarDB cluster.

Step 5: Create a database

  • The following examples show how to create the dept, emp, and jobhist tables:
    CREATE TABLE dept (
        deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
        dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
        loc             VARCHAR2(13)
    );
    CREATE TABLE emp (
        empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
        ename           VARCHAR2(10),
        job             VARCHAR2(9),
        mgr             NUMBER(4),
        hiredate        DATE,
        sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
        comm            NUMBER(7,2),
        deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                            REFERENCES dept(deptno)
    );
    CREATE TABLE jobhist (
        empno           NUMBER(4) NOT NULL,
        startdate       DATE NOT NULL,
        enddate         DATE,
        job             VARCHAR2(9),
        sal             NUMBER(7,2),
        comm            NUMBER(7,2),
        deptno          NUMBER(2),
        chgdesc         VARCHAR2(80),
        CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
        CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
            REFERENCES emp(empno) ON DELETE CASCADE,
        CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
            REFERENCES dept (deptno) ON DELETE SET NULL,
        CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
    );
  • The following examples show how to insert data into the tables:
    INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
    INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO dept VALUES (30,'SALES','CHICAGO');
    INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
    INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
    INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
    INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
    INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
    INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
    INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
    INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
    INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
    INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
    INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
    INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
    INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
    INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
    INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
    INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30,'New Hire');
    INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30,'New Hire');
    INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30,'New Hire');
    INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30,'New Hire');
    INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20,'Raise');
    INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20,'Promoted to Analyst');
    INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30,'New Hire');
    INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10,'New Hire');
    INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,NULL,30,'Changed to Dept 30');
    INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20,'New Hire');
    INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10,'New Hire');

Step 6: Query data

Execute SELECT statements to query data from the tables. The following example shows how to query data of all columns in the emp table:

SELECT * FROM emp;

 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

Execute the following SQL statement to query data of specified columns:

SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ename  |   sal   | yearly_salary | deptno
--------+---------+---------------+--------
 SMITH  |  800.00 |      19200.00 |     20
 ALLEN  | 1600.00 |      38400.00 |     30
 WARD   | 1250.00 |      30000.00 |     30
 JONES  | 2975.00 |      71400.00 |     20
 MARTIN | 1250.00 |      30000.00 |     30
 BLAKE  | 2850.00 |      68400.00 |     30
 CLARK  | 2450.00 |      58800.00 |     10
 SCOTT  | 3000.00 |      72000.00 |     20
 KING   | 5000.00 |     120000.00 |     10
 TURNER | 1500.00 |      36000.00 |     30
 ADAMS  | 1100.00 |      26400.00 |     20
 JAMES  |  950.00 |      22800.00 |     30
 FORD   | 3000.00 |      72000.00 |     20
 MILLER | 1300.00 |      31200.00 |     10
(14 rows)

Execute the following SQL statement to query employees whose salary exceeds 1,000 in department 20:

SELECT ename, sal, deptno FROM emp WHERE deptno = 20 AND sal > 1000;

 ename |   sal   | deptno
-------+---------+--------
 JONES | 2975.00 |     20
 SCOTT | 3000.00 |     20
 ADAMS | 1100.00 |     20
 FORD  | 3000.00 |     20
(4 rows)

Execute SQL statements to return data in a specific order by using ORDER BY. The following example shows how to return data in alphabetical order of names:

SELECT ename, sal, deptno FROM emp ORDER BY ename;

 ename  |   sal   | deptno
--------+---------+--------
 ADAMS  | 1100.00 |     20
 ALLEN  | 1600.00 |     30
 BLAKE  | 2850.00 |     30
 CLARK  | 2450.00 |     10
 FORD   | 3000.00 |     20
 JAMES  |  950.00 |     30
 JONES  | 2975.00 |     20
 KING   | 5000.00 |     10
 MARTIN | 1250.00 |     30
 MILLER | 1300.00 |     10
 SCOTT  | 3000.00 |     20
 SMITH  |  800.00 |     20
 TURNER | 1500.00 |     30
 WARD   | 1250.00 |     30
(14 rows)

Execute SQL statements to query distinct values in a column. The following example shows how to query distinct job titles in the job column:

SELECT DISTINCT job FROM emp;

    job
-----------
 ANALYST
 CLERK
 MANAGER
 PRESIDENT
 SALESMAN
(5 rows)