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

If your application already uses the MySQL ecosystem, you can connect to LindormTable over the MySQL protocol without installing additional drivers. This lets you use standard MySQL clients and Lindorm SQL to create databases, manage tables, and read and write data.

Prerequisites

Before you begin, ensure that you have:

A Lindorm instance running LindormTable 2.6.0 or later. To check or upgrade the version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.
The client IP address added to the Lindorm instance whitelist. See Configure whitelists.
The MySQL compatibility feature enabled for the instance. See Enable the MySQL compatibility feature.
Familiarity with MySQL protocol development requirements. See Usage notes for application development using MySQL.

Install a MySQL client

Linux

Run one of the following commands based on your package manager:

APT (Advanced Package Tool):
```
sudo apt-get install mysql-client
```
Yum package management tool:
```
sudo yum install mysql
```

macOS

Install with Homebrew:

brew install mysql-client

Windows

Download and run the installer from MySQL Downloads.

Connect to LindormTable

Step 1: Get the connection endpoint

The MySQL URL is the Lindorm Wide Table SQL Address shown in the Lindorm console, with the trailing colon and port number removed.

Connection type	When to use	How to get the endpoint
VPC (recommended)	Application running on an Elastic Compute Service (ECS) instance	Use the VPC endpoint directly. Virtual private cloud (VPC) connections provide lower latency and higher security.
Public network	Application running outside Alibaba Cloud	In the console, go to Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint, then use the public endpoint.

Step 2: Run the connection command

mysql --get-server-public-key -h<mysql-url> -P33060 -u<username> -p<password> -D<database>

Note

The --get-server-public-key parameter prevents the error Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

Replace the placeholders with your actual values:

Parameter	Description	Example
`<mysql-url>`	The Lindorm Wide Table SQL Address from the console, without the trailing colon and port number	`ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com`
`<username>`	The username for LindormTable	`test`
`<password>`	The password for the username	`test`
`<database>`	The database to connect to. Defaults to `default` if not specified	`default`

Compatibility notes for MySQL 8.0 and earlier

MySQL command-line tools of version 8.0 and earlier have two compatibility issues with Lindorm. Apply the relevant fix before connecting.

TLS version mismatch

MySQL 8.0 and earlier use TLSv1.1 by default for encrypted connections. Lindorm supports TLSv1.2, which can cause a TLS handshake failure. Add --ssl-mode=DISABLED to force a plaintext connection:

mysql --ssl-mode=DISABLED --get-server-public-key -h<mysql-url> -P33060 -u<username> -p<password> -D<database>

Important

Use plaintext connections over the Internet with caution.

Authentication protocol mismatch

MySQL 8.0 and earlier use mysql_native_password by default and do not support caching_sha2_password. Authentication may fail because of LindormTable version requirements for the MySQL protocol. Add --enable_cleartext_plugin to pass the password in plaintext:

mysql --enable_cleartext_plugin --get-server-public-key -h<mysql-url> -P33060 -u<username> -p<password> -D<database>

Important

Use plaintext password transmission over the Internet with caution.

Run basic operations

The following examples use Lindorm SQL to create a database, create a table, insert data, and query data.

Create a database named test1:
```
CREATE DATABASE test1;
```
Switch to the test1 database:
```
USE test1;
```

Create a table and insert a row:

CREATE TABLE tb (id varchar, name varchar, address varchar, PRIMARY KEY (id, name));
UPSERT INTO tb (id, name, address) VALUES ('001', 'jack', 'hz');

Query the data:

SELECT * FROM tb;

Expected output:

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

What's next

Usage notes for application development using MySQL — learn about supported SQL syntax and known limitations before writing application code.
Release notes of LindormTable — check the latest features and version requirements.