Unable to connect to a self-managed MySQL server in an ECS instance -

Overview

If you create a user-created MySQL server on a Linux ECS instance, you may fail to remotely connect to the server. This topic describes common errors and solutions in this situation.

Description

Take note of the following items:

Before you perform high-risk operations such as modifying the specifications or data of an Alibaba Cloud instance, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.

Before you modify the specifications or data of an Alibaba Cloud instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backups for the instance. For example, you can enable log backups for an ApsaraDB RDS instance.

If you have granted specific users the permissions on sensitive information, such as usernames and passwords, or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity.

The following lists some common problems and solutions.

Unauthorized access to the MySQL service
Unable to connect to the MySQL service due to an incorrect listener address
Connection Times "Lost connection to MySQL server at 'reading initial communication packet" error
The MySQL service cannot be connected due to version issues.

Unable to connect to MySQL service due to unauthorized authorization

Problem description

If you connect to the MySQL service within the ECS instance, the following error occurs when you connect to the remote connection:

EHost 'XX.XX.XX.XX' is not allowed to connect to this MySQL serverConnection closed by foreign host.

If an output similar to the following one is returned, one of the solutions is applicable to your system kernel version:

Solution

Make sure that the MySQL service listens to the 0.0.0.0 address.
Check whether the firewall is enabled for the instance. We recommend that you disable the firewall first for testing.
Check whether remote logon is enabled for the MySQL service. You can use the following method to enable remote logon:
- Log on to the database and select the mysql database. Change the host item in the user table from localhost to '%'.
- Log on to the database and create a user for remote connection.

Unable to connect to MySQL service due to incorrect listening address

Problem description

The MySQL service listens to the 127.0.0.1 address. As a result, external connections cannot be made.

Solution

In the MySQL service configuration file, comment out the bind-address parameter and restart it.

Connection Times "Lost connection to MySQL server at 'reading initial communication packet" error

Problem description

The following error is reported when you remotely connect to the MySQL service.

Lost connection to MySQL server at 'reading initial communication packet'

Solution

Check whether the firewall is enabled to restrict connection requests.
Check whether the MySQL database has access permissions. You can run the following command to add the value:
```
use mysql;
Grant all privileges on *.* to 'root'@'%' identified by '[$Password]' with grant option;
flush privileges;
```
Note:[$Password] is the password.
Check whether the bind-address parameter is set in the my.ini configuration file and comment out this parameter.
In the my.ini configuration file, add skip-name-resolve parameters.
Modify the hosts.allow configuration file and add the following configuration:
```
mysqld-max : ALL :ALLOW
```

The MySQL service cannot be connected due to version issues.

Problem description

The version of the user-created MySQL server is 5.6. The following error message is returned when you connect to the instance.

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

Cause

Due to the old password management protocol on the server side, the old user password format is used for storage, but the new password format is adopted after the client upgrade. If the MySQL 5.6 version encounters this inconsistency, the connection will be rejected.

Solution

Add --secure-auth parameters when you connect. The following command is used to connect. For more information, see MySQL.

mysql -h [$Host] -u [$Username] --secure-auth -p

Note:[$Host] is the domain name or IP address of the ECS instance, and [$Username] is the database username.

Applicable scope

Elastic Compute Service (ECS)