Troubleshooting MySQL – The Impact of skip-grant-tables on TCP Connections

I recently encountered a interesting issue while working with MySQL on a CentOS server. The problem revolved around the skip-grant-tables option, which I enabled to reset the root password. Unexpectedly, enabling this option caused MySQL to stop listening on the default TCP port (3306), which led to an inability to connect to the server remotely. Here’s a detailed breakdown of the problem, the investigation process, and the solution.

Problem Description

After enabling skip-grant-tables in the MySQL configuration to bypass the password authentication for user management tasks, I noticed that MySQL was no longer accepting connections on the default TCP port (3306). This behavior effectively limited access to the MySQL server to only those using the Unix socket on the local machine.

Investigation

Initially, I attempted to verify whether MySQL was listening on port 3306 using the following command:

sudo netstat -tulnp | grep mysql

However, this command returned no output, indicating that MySQL was not listening on any TCP ports. To further investigate, I checked the Unix socket that MySQL might be using instead:

mysqladmin -u root -p variables | grep socket

This command confirmed that MySQL was indeed using a Unix socket instead of TCP. The use of skip-grant-tables had caused MySQL to stop listening on the TCP port and rely solely on the Unix socket for connections.

Root Cause

The root cause is that enabling skip-grant-tables stops MySQL from listening on TCP ports by default. This behavior is likely a security measure, as skipping grant tables disables user authentication, which could expose the server to unauthorized access if it continues to listen on the network.