Solution to Workbench not connecting to MySQL on Alibaba Cloud Server Ubuntu (tested)

In the past two days, I have been very troubled to solve the problem that workbench cannot connect to Alibaba Cloud Server. The tutorials I found on the Internet are all similar, but this one doesn't work for me. Fortunately, it was finally solved in the end. Let me record this annoying process.

Used environment:

system:

Ubuntu 18.04.2 LTS
MySQL: mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper

MySQL Workbench 8.0CE

1. Unable to connect to the server

When you first connect, the following status appears.

One of the pitfalls encountered

Most of the methods found on the Internet are to modify the value of bind_address

First check the following mysql port status

netstat -anp | grep mysql

ps: If it shows 127.0.0.1:3306, it means it needs to be modified. If it is: : :3306, it is not necessary.

Because the status I displayed is 127.0.0.1:3306, which is this value, I have to modify it.

Most of the configuration file modification paths found on the Internet say that they are in **/etc/mysql/my.cnf**, but this is only for the old version. Since I installed the latest version, I opened this file and found that there was no bind_address value in it. Later I checked and found that the new version is in **/etc/mysql/mysql.conf.d/mysqld.cnf** . Now that you have discovered the problem, it is easy to correct it. Find the following part and comment it out.

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

2. Modify the information of mysql.user table

In order to allow the root user to remotely access the database, some information must be changed.

mysql -u root -p password
mysql>use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,host from user;
+------------------+----------------+
| user | host |
+------------------+----------------+
| root | localhost |
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+----------------+

The host above indicates the addresses from which users can access the database. Since it is localhost, it can only be accessed locally. A total of two modification methods were found. One is to directly change the host corresponding to the original root to **%**, or to add a new user who can access remotely.

Method 1:

Execute the following command in the state of entering the mysql database

update user set host='%' where user='root'

Method 2: Add a new user:

grant all privileges on *.* to new_user@'%' identified by "password";

No matter which of the above methods you use, you must refresh the settings just now.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

I tried both methods, but it still didn't work. It said that our current IP address does not allow the connection.

After trying many times, I found that the username for remote connection should not be root. After several reinstall attempts, I found that setting root to allow remote connection always failed.

3. Turn off the firewall

# Restart the database first
sudo service mysql restart
# Check if the MySQL port status is: : :3306
netstat -anp | grep mysql
# Disable the firewall
sudo ufw disable
# Check the firewall status
sudo ufw status

4. Open ports

Users of Alibaba Cloud or other cloud services must be careful to open and protect the corresponding ports. The default MySQL port is 3306.

Open the Alibaba Cloud console and add security group rules

There is a security group restriction setting. Set the inbound direction to allow port 3306 and try the following. It will work in an instant.

Alibaba Cloud ECS MySQL cannot be connected remotely. If you have tried all the tutorials online and still cannot connect, please see below

First, check the firewall. This is the most basic. Many tutorials will mention that port 3306 should be enabled. Check the Alibaba Cloud server security group to see if the inbound and outbound rules for 3306 are enabled. This is really a pitfall. I don't know when Alibaba Cloud Linux servers have this thing. I remember that it is only available on Windows servers. Sometimes it still cannot be connected through tools, and it will prompt that it is related to SSL. I use IDEA and MySQL workbench to connect. IDEA is to add on the connection URL

useSSl=false

As for MySQL workbench, I didn't find how to connect, so another option is to disable SSL on the server
Find the my.cnf file, usually in /etc/my.conf
Add the skip_ssl command to the content to turn off SSL

service mysqld restart

Restart mysql

I won't go into details about setting all IP addresses to be accessible, changing localhost to 127.0.0.1, etc. There are many articles online for reference. Those who are not familiar with security groups and SSL can refer to the above methods.

The role of useSSL=false when connecting to the database

useSSL=false
In higher versions of MySQL, you need to specify whether to use SSL connection

1.true requires connection
2.false No connection required

5. Get it done

This is the end of the article, so basically everything is solved.