Connect to and use LindormTable by using the MySQL command line - Lindorm

LindormTable supports access over MySQL. You can use a MySQL client to connect to LindormTable and use Lindorm SQL to manage data in LindormTable. This topic guides you through how to get started with LindormTable by providing detailed steps from tool download to simple data reading and writing.

Prerequisites

The LindormTable version of the instance is 2.6.0 or later. For information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.
The IP address of your client is added to the whitelist of your Lindorm instance. For more information, see Configure whitelists.
The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.
You understand the instructions for the development based on MySQL. For more information, see Usage notes for application development using MySQL.

Usage notes

MySQL command-line tools of version 8.0 and earlier use TLSv1.1 by default for encrypted connections. Lindorm supports TLSv1.2. This version mismatch can cause connection failures. To resolve this, add the --ssl-mode=DISABLED parameter to the startup command to force a plaintext connection.
Important
Exercise caution when you establish an unencrypted connection over the Internet.
MySQL command-line tools of version 8.0 or earlier use the mysql_native_password authentication protocol by default. Authentication may fail because of LindormTable version requirements for the MySQL protocol. These tool versions also do not support the caching_sha2_password authentication protocol. Therefore, you must add the --enable_cleartext_plugin parameter to the startup command to pass the password in plaintext.
Important
Use this method with caution in a public network environment.

Procedure

Tool installation

Linux
Run one of the following commands based on the management tool that you use in Linux:
- APT (Advanced Package Tool)
```
sudo apt-get install mysql-client
```
- Yum package management tool
```
sudo yum install mysql
```
macOS
To install using the Homebrew package management tool, execute the following command.
```
brew install mysql-client
```
Windows operating system
For more information, see Install the MySQL client.

Connect and use LindormTable

Run the following command to connect to LindormTable:

mysql --get-server-public-key -h<mysql url> -P33060 -u<Username> -p<Password> -D<Database>

Note

You can add the --get-server-public-key parameter to avoid the following error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection..

The following describes the parameters:

Parameter	Example	Acquisition Method
mysql url	ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com	In the console, obtain the Lindorm Wide Table SQL Address and remove the trailing colon and port number. Important If your application is deployed on an Elastic Compute Service (ECS) instance, we recommend that you use a virtual private cloud (VPC) connection to access the Lindorm instance to ensure higher security and lower network latency. If your application is deployed locally, enable a public endpoint in the console before you connect to the Lindorm instance over the public network. To enable the endpoint, choose Database Connections > Wide Table Engine in the navigation pane. On the Wide Table Engine tab, click Enable Public Endpoint.
Username	test	The username that you use to connect to LindormTable.
Password	test	The password that corresponds to the username.
Database	default	The LindormTable database to connect to. If you do not specify a database, the client connects to the `default` database by default.

Perform operations in LindormTable.

Create a database named test1.
```
CREATE DATABASE test1; 
```
Use the test1 database.
```
USE test1; 
```

Create a table named tb in the test database and insert a row of data into the tb table.

CREATE TABLE tb (id varchar, name varchar, address varchar,  primary key(id, name)) ; 
UPSERT INTO tb (id, name, address) values ('001', 'jack',  'hz');

Query data in the tb table.

SELECT * FROM tb;

The following result is returned:

+------+------+---------+
| id   | name | address |
+------+------+---------+
| 001  | jack | hz      |
+------+------+---------+