How to Connect MySQL Client to a MySQL Server on a Custom Port

Learn how to easily connect your MySQL client to a MySQL server using a `custom port`, especially when the server is running on Docker.
---

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: Connect mysql client to a mysql server by using custom port

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Connecting MySQL Client to MySQL Server Using a Custom Port

If you've ever tried to connect your MySQL client to a MySQL server running on a different port, you may have encountered some frustrating issues. Particularly, if you are running a MySQL server in a Docker container and want to access it on a custom port (like 3307), you might find that your MySQL client connects to the host's MySQL server instead. In this guide, we will explore why this occurs and how to successfully connect to your MySQL server using a custom port.

The Problem

When attempting to connect to a MySQL server through the MySQL client using the command:

[[See Video to Reveal this Text or Code Snippet]]

You might find that it still connects to the default MySQL server running on port 3306 of your host machine. Even when specifying the custom port (3307 in this case), the connection does not reflect your intended settings.

The Odd Behavior

One might think that using a non-existing port would fail the connection, yet running a command like this would still work:

[[See Video to Reveal this Text or Code Snippet]]

This situation raises a critical question: Why does the -P option seem to be ignored? Let's dig into the core of the issue.

Understanding the Connection Mechanism

The root cause of this problem lies in the way MySQL treats the hostname localhost. When you specify localhost as your hostname, MySQL communicates via a UNIX socket instead of TCP/IP. This means that the port number provided is ignored because socket connections do not involve ports like traditional TCP/IP connections do.

Verifying the Connection Type

You can verify the type of connection you’re using by entering the MySQL shell and running:

[[See Video to Reveal this Text or Code Snippet]]

In this case, you'll see an output similar to:

[[See Video to Reveal this Text or Code Snippet]]

This confirms that you're not using TCP/IP but instead a UNIX socket to establish the connection.

The Solution: Force the Use of TCP/IP

To connect to your MySQL server using a specific port, you must use the IP address 127.0.0.1 instead of localhost. This tells MySQL to establish a TCP/IP connection instead of a UNIX socket connection.

Steps to Connect Using TCP/IP

Change the Host: Modify your connection command to use 127.0.0.1 as follows:

[[See Video to Reveal this Text or Code Snippet]]

Check the Connection: Run the status command again in the MySQL shell to confirm that the connection is now via TCP/IP:

[[See Video to Reveal this Text or Code Snippet]]

You should see an output indicating the use of TCP/IP:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

In summary, when connecting to a MySQL server, specifying localhost leads to a connection via a UNIX socket, ignoring any custom port settings. By switching to 127.0.0.1, you can ensure that your connection uses the desired port, allowing you to connect seamlessly to your MySQL server running in a Docker environment or any other setup.

Now you can confidently connect to your MySQL server using custom ports without any confusion or issues! Happy querying!