Troubleshooting MySQL Remote Connection Failure Caused by skip-grants-tables

Introduction

The author, a DBA team member responsible for DMP product operations, encountered a basic yet puzzling MySQL fault: the server could not be accessed remotely despite the firewall being normal.

Fault Scene

Attempting a client login produced the error:

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

Typical troubleshooting steps were applied:

Check if the MySQL process exists.

[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

Check port binding – none was found.

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

Inspect the startup log and notice that the listening port is reported as 0.

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.

Verify the port variable directly.

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

Searching Official Documentation

Google searches pointed to the skip-networking parameter. Although the local my.cnf did not contain skip-networking, it did contain skip-grants-tables.

Official Explanation

The documentation states that enabling skip-grants-tables disables the privilege tables, and MySQL automatically turns on skip-networking to block remote connections for security.

Solution

Comment out the skip-grants-tables line in the configuration file and restart MySQL. After the restart, remote connections work normally.

Keyword: #故障排查#