Solution to the problem that Navicat cannot remotely connect to MySql server

The solution to the problem that Navicat cannot remotely connect to the MySql server is written in the front:

Operating system: Winning bid for Kylin 4.0;
Database: MySQL 5.7.26;
JDK: Use the openjdk that comes with the Kylin system;
Client tool: Navicat;

Prerequisites:
The MySQL service is installed;

scene:

After installing MySQL, local access is normal, but machines in the same LAN cannot access the MySQL database on the server;

reason:

By default, MySQL cannot be accessed from a remote machine, only local access is allowed. Remote access can be enabled through the following configuration;

Troubleshooting ideas:

The remote access setting of MySQL is mainly tested from the following three aspects: first, check the network. The two computers must be able to ping each other, which is the basis; second, check port 3306. For the port, only the local machine is allowed to access it. There are two places to enable it. One is to configure MySQL to bind the local machine address, and the other is to enable 3306 on the firewall; third, the issue of MySQL user access rights.

Check one by one according to the ideas

1. Network detection:

Ping the IP address of the host where MySQL is located to see if it can be pinged. If it can be pinged, it means that the problem is not with the network.
I can ping it, which means it is not a network problem.

2. Port detection:

Detect port 3306:
netstat -ntpl | grep 3306, as shown in the figure:

Check port 22:
netstat -ntpl | grep 22, as shown in the figure:

From the above, we can see that port 22 listens to all addresses, while port 3306 only listens to the local address, so it cannot be accessed remotely. For ports that only allow local access, there are two places to enable it, one is to enable 3306 in the firewall, and the other is to configure MySQL to bind to the local address. So we have found the problem that Navicat cannot connect to remote MySQL. We just need to allow remote access to port 3306.

Solution:
In general, we only need to modify the /etc/mysql/my.cnf configuration file, change bind-address=127.0.0.1 in the my.cnf configuration file to bind-address=0.0.0.0, and comment out skip-networking;

However, my my.cnf configuration file did not have the lines of code bind-address=127.0.0.1 and skip-networking, so I added the line of code bind-address=0.0.0.0 myself, thinking that it would work. After restarting the system, it turned out to be of no use! ! ! I checked port 3306 again and found that it still only allowed local access. Just when I was puzzled, a comment in the configuration file caught my attention. There was a surprise here, and the problem could be solved immediately. > . <

Then we look at the /etc/mysql/my.cnf configuration file and find a sentence, as shown in the figure:

“Import all files ending with .cnf from the following configuration directory”, wow, do you feel like you have suddenly realized something? That is to say, the line address=127.0.0.1 is not configured in the my.cnf configuration file, but may be configured in one of the configuration files in those two directories, so let's go and look for it. We have to find the line of code "address=127.0.0.1" anyway.

First go to the first directory: /etc/mysql/conf.d, and look at the cnf.d file, as shown in the figure:

There is no "address=127.0.0.1";

Go to the second directory: /etc/mysql/mariadb.conf.d/, and find that there are 5 files ending with .cnf, namely: 50-client.cnf, 50-mysql-clients.cnf, 50-mysqld_safe.cnf, 50-server.cnf, my.cnf, as shown in the figure:

Use your brain to guess, you also know which file to look at. Yes, I locked the "50-server.cnf" file, and when I looked inside, I found the line of code "address=127.0.0.1". I changed it to "address=0.0.0.0", as shown in the figure:

Then restart the computer (note that you must restart the computer here, restarting mysql is useless, restarting mysql is useless for me), and then check the port, as follows:

Are you happy? I finally fixed it. I have to complain about MySQL. We should not only look at the configuration in its configuration file, but also cannot ignore the comments in the configuration file. Wow, I am convinced~~ I have learned my lesson!

However, the port detection step is not over yet. We have checked the MySQL configuration file, and there is still one more thing to check: whether the firewall port 3306 is open.

Firewall detection:
View: iptable --list
I checked and found that my firewall is not turned on. If there is a firewall, you need to turn on port 3306 of the firewall, or just turn off the firewall;

So far, there is no problem with port 3306 detection.

3.MySQL user access rights:

Enter the mysql database: mysql -uroot -p
use mysql;
Query user access rights:
select host,user from user; as shown in the figure:

When creating a user in MySQL, a host is specified. The default is 127.0.0.1 (localhost), which can only be accessed by the local machine. When other machines use this user account to access, they will be prompted that they do not have permission. Change the host to "%" to allow all machines to access. (I have changed it to allow all machines to access)
The modified statement is: grant all on . to 'root'@'%' identified by 'root用戶的密碼' with grant option;
flush privileges;

Another thing worth noting is that you need to delete "redundant users". For example, I used to have two root users, as follows:
user host
root %
root 127.0.0.1
In order to avoid mistakes at this time, you can delete the root user of 127.0.0.1 (I fell into the pit here. I fell into this pit when I changed the password. I changed the password of %root. As a result, I kept logging in with the root of 127.0.0.1 and couldn't log in,,,);

Summarize

To sum up, the problem of setting up remote access to MySQL has been solved. To summarize, setting up remote access to MySQL is mainly detected from the following three aspects: first, check the network. The two computers must be able to ping each other, which is the basis; second: detect port 3306. For the port, only local access is allowed. There are two places to enable it. One is to configure MySQL to bind to the local address (modify the MySQL configuration file (friendly reminder, it is not necessarily my.cnf ~ to read the contents of the configuration file carefully~) bind-address=0.0.0.0), and the other is to enable 3306 on the firewall (detect the firewall); third: the problem of MySQL user access rights.

This is a pit I have fallen into before, and I think it is worth noting. The so-called experience is nothing more than stepping into more pits, encountering more problems, and then solving them and jumping out of the pits. Then you will have experience. Accumulate experience, start from every pit you have stepped into, and then constantly improve yourself. Come on!

This is the end of this article about how to solve the problem that Navicat cannot connect to MySql server remotely. For more information about the problem that Navicat cannot connect to MySql server remotely, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!