Using the MySQL Client to Connect to a Doris Security Cluster

Constraints

• Enabling security channel may cause cluster performance deterioration.
• The Doris cluster and the ECS must be in the same region, AZ, and VPC.
• The Doris cluster and the ECS must be in the same security group.
• The IP address of the local host has been added to the security group the ECS belongs to.

Procedure

1. Log in to the CloudTable console.
2. Select a region in the upper left corner.
3. Click Create Cluster in the upper right corner.
4. Check whether Enable Https (which is toggled on by default) is toggled on after completing other configurations.
5. Set the parameters and click Next.
6. Confirm the cluster information and click Submit. After the cluster is created, go to its details page to view its security channel status.

Connecting to a Doris Cluster

1. Click the name of the target security cluster to download the certificates on its details page.
2. Specify the path for storing the certificates.
3. Connect to the cluster.

   ./mysql -uadmin -hCluster private IP address -PPort --ssl-ca={path}/certificate.crt --ssl-mode=VERIFY_CA -p Password

   • Cluster private IP address: private IP address of the cluster to be connected
   • path: path for storing certificates.
   • Port: MySQL server port 9030 on the FE node
   • Password: password set during cluster creation
   • {path}/certificate.crt: path for storing the downloaded certificate
     

     Use a MySQL 8.0 or later client after HTTPS is enabled.

Getting Started with Doris

1. Create a database.

   CREATE DATABASE demo;

2. Create a data table.
   • Use the database.

     USE demo;

   • Create a table.

     CREATE TABLE IF NOT EXISTS demo.example_tbl
     (
         `user_id` LARGEINT NOT NULL COMMENT "User ID",
         `date` DATE NOT NULL COMMENT "Data import date and time",
         `city` VARCHAR(20) COMMENT "City where the user locates",
         `age` SMALLINT COMMENT "User age",
         `sex` TINYINT COMMENT "User gender",
         `last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "Last visit date of the user",
         `cost` BIGINT SUM DEFAULT "0" COMMENT "Total consumption",
         `max_dwell_time` INT MAX DEFAULT "0" COMMENT "Maximum residence time",
         `min_dwell_time` INT MIN DEFAULT "99999" COMMENT "Minimum residence time",
     )
     AGGREGATE KEY(`user_id`, `date`, `city`, `age`, `sex`)
     DISTRIBUTED BY HASH(`user_id`) BUCKETS 1
     PROPERTIES (
         "replication_allocation" = "tag.location.default: 3"
     );

3. Insert data.

   INSERT INTO demo.example_tbl (user_id,date,city,age,sex,last_visit_date,cost,max_dwell_time,min_dwell_time) VALUES('10000','2017-10-01','A','20','0','2017-10-01 07:00:00','35','10','2'),('10001','2017-10-01','A','30','1','2017-10-01 17:05:45','2','22','22'),('10002','2017-10-02','B','20','1','2017-10-02 12:59:12','200','5','5'),('10003','2017-10-02','C','32','0','2017-10-02 11:20:12','30','11','11'),('10004','2017-10-01','D','35','0','2017-10-01 10:00:15','100','3','3'),('10004','2017-10-03','D','35','0','2017-10-03 10:20:22','11','6','6');

4. Query the data.
   • The following is an example of using Doris to perform quick data query and analysis.

     mysql> SELECT * FROM demo.example_tbl;                                                                                                                                                                      +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | user_id | date       | city | age  | sex  | last_visit_date     | cost | max_dwell_time | min_dwell_time |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | 10000   | 2017-10-01 | A    |   20 |    0 | 2017-10-01 07:00:00 |   35 |             10 |              2 |
     | 10001   | 2017-10-01 | A    |   30 |    1 | 2017-10-01 17:05:45 |    2 |             22 |             22 |
     | 10002   | 2017-10-02 | B    |   20 |    1 | 2017-10-02 12:59:12 |  200 |              5 |              5 |
     | 10003   | 2017-10-02 | C    |   32 |    0 | 2017-10-02 11:20:12 |   30 |             11 |             11 |
     | 10004   | 2017-10-01 | D    |   35 |    0 | 2017-10-01 10:00:15 |  100 |              3 |              3 |
     | 10004   | 2017-10-03 | D    |   35 |    0 | 2017-10-03 10:20:22 |   11 |              6 |              6 |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     6 rows in set (0.02 sec)

   • View information about a specified city.

     mysql> SELECT * FROM demo.example_tbl where city='B';
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | user_id | date       | city | age  | sex  | last_visit_date     | cost | max_dwell_time | min_dwell_time |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     | 10002   | 2017-10-02 | B    |   20 |    1 | 2017-10-02 12:59:12 |  200 |              5 |              5 |
     +---------+------------+------+------+------+---------------------+------+----------------+----------------+
     1 row in set (0.10 sec)

5. Delete data.
   1. Delete a specified row of data.

      mysql> DELETE FROM demo.example_tbl WHERE user_id = 10003;
      Query OK, 0 rows affected (0.04 sec)
      {'label':'delete_77ed273a-a052-4d64-bac0-23916b698003', 'status':'VISIBLE', 'txnId':'39'}

   2. Delete the table.

      mysql> DROP TABLE demo.example_tbl;
      Query OK, 0 rows affected (0.01 sec)