Troubleshooting | MySQL No Listening Port Troubleshooting

Author: Wang Xiang

A member of the DBA team of Akson, responsible for the operation and maintenance of the company's DMP products and the handling of customer MySQL problems. Good at database troubleshooting. Strong interest in database technology and python.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

foreword

Recently, a relatively basic problem was solved. Since the troubleshooting process is very interesting, I wrote this article based on this material.

fault scene

The firewall is normal but cannot be accessed remotely. Simply use the client to log in.

 ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

According to past experience, several common troubleshooting methods for such failures have emerged in the brain

1. Check the existence of the process

 [root@wx ~]# ps -ef|grep [m]ysql 
mysql 25973 1 1 8月30 ? 02:43:20 
/mysqldata/mysql/base/8.0.24/bin/mysqld --defaults-
file=/mysqldata/mysql/etc/3308/my.cnf --daemonize --pid-
file=/mysqldata/mysql/data/3308/mysqld.pid --user=mysql --
socket=/mysqldata/mysql/data/3308/mysqld.sock --port=3308

2. Check the port binding situation, but there is no binding port

 [root@wx ~]# lsof -i:3308
[root@wx ~]# ss -nltp|grep 3308

3. Check the startup log and find that the listening port is equal to 0

 2022-09-06T07:30:41.090649-00:00 0 [Note] [MY-010304] [Server] Skipping generation of SSL certificates as certificate files are present in data directory. 
2022-09-06T07:30:41.094320-00:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 
2022-09-06T07:30:41.094806-00:00 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 
2022-09-06T07:30:41.095177-00:00 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory. 
2022-09-06T07:30:41.095500-00:00 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through -- caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory. 
2022-09-06T07:30:41.124942-00:00 0 [System] [MY-010931] [Server] /mysqldata/mysql/base/8.0.24/bin/mysqld: ready for connections. Version: '8.0.24' socket: '/mysqldata/mysql/data/3306/mysqld.sock' port: 0 MySQL Community Server - GPL.

4. Use the show variables command to see that it is indeed 0

 mysql> show variables like 'port'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| port              | 0 | 
+---------------+-------+

Adhering to the idea of going before you can't start, I searched for many articles on Google and pointed to the skip-networking parameter; viewing our configuration file did not configure the skip_networking parameter. But found skip-grants-tables.

Official documentation for answers

It probably means that the permission table is disabled using the skip-grants-tables parameter. In this case, mysql will enable skip-networking by default to disable remote connections. The purpose is for safety.

solution

Because the configuration of skip-grants-tables causes the failure to connect to the mysql server remotely, the solution is also very simple to comment on restart.