Using the MySQL Client to Connect to a Doris Normal Cluster
You can use MySQL to access a cluster on an ECS. For details about how to install the client, see Installing the Client.
Constraints
- 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 ECS security group.
Installing the Client
- Prepare a Linux ECS. For details, see Preparing an ECS.
- Install the client and connect to the cluster.
- Use the SSH login tool to remotely log in to the Linux ECS through the EIP.
For details, see Logging In to a Linux ECS Using an SSH Password in the Elastic Cloud Server User Guide.
- Decompress the installation package.
cd <Path of the client installation package> tar -xzvf Name of the client package
- Go to the bin directory.
cd mysql-5.7.22-linux-glibc2.12-x86_64/bin/
- Connect to the Doris cluster.
./mysql -uadmin -pPassword -hInternal IP address of the cluster -P9030
- Internal IP address of the cluster: Enter the cluster access address on the cluster details page. Replace it with the access address of the cluster you purchased. (All access addresses of the FE node can be used to access the cluster.)
- Password is the password set when you purchase the cluster. If there are special characters, use backslashes (\) to escape them. If the password is enclosed in single quotation marks ('), the special characters do not need to be escaped.
- Port: MySQL server port on the FE node. For details, see Table 1.
Table 1 Custom security rules Direction
Action
Port/Range
Type
Destination/Source Address
Usage
Outbound
Allow
All
IPv4/IPv6
0.0.0.0/0
Permit in the outbound direction
Inbound
Allow
9030
Security group of the CloudTable Doris cluster
MySQL server port on the FE node
Allow
8030
HTTP server port on the FE node
Allow
8040
HTTP server port on the BE node
Allow
8050
HTTPS server port on the FE node
- Use the SSH login tool to remotely log in to the Linux ECS through the EIP.
Getting Started with Doris
- Create a database.
CREATE DATABASE demo;
- 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" );
- Use the database.
- 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');
- 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)
- The following is an example of using Doris to perform quick data query and analysis.
- Delete data.
- 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'}
- Delete the table.
mysql> DROP TABLE demo.example_tbl; Query OK, 0 rows affected (0.01 sec)
- Delete a specified row of data.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot