How to Connect to a Different Port Using MySQL Command Line Client

When working with MySQL, the default port for connecting to the server is 3306. However, there are scenarios where the MySQL server might be configured to listen on a different port. In this blog post, we will explore how to connect to a MySQL server using a custom port via the MySQL Command Line Client.

Why Would You Use a Different Port?

There are several reasons why you might encounter a MySQL server running on a non-standard port:

1. Multiple MySQL Instances: Running multiple MySQL servers on the same machine requires each to have a unique port.
2. Custom Configuration: Some setups use non-default ports for security purposes.
3. Firewall or Proxy Settings: Your network may require MySQL traffic to be routed through a specific port.

Prerequisites

Before proceeding, ensure you have the following:

• MySQL Command Line Client installed.
• Credentials to access the MySQL server (username, password).
• The specific port number the server is using.
• Network access to the server’s host and port.

Connecting to a Custom Port

The mysql command-line tool provides an option to specify a custom port using the -P flag. Below is the general syntax:

mysql -u [username] -p -P [port] -h [host]

Explanation of the Flags

• -u [username]: Specifies the username for the MySQL connection.
• -p: Prompts for the password. If you omit this flag, the connection attempt will fail unless your MySQL configuration allows password-less login.
• -P [port]: Defines the port number to connect to.
• -h [host]: Specifies the hostname or IP address of the MySQL server. If omitted, it defaults to localhost.

Example Command

Suppose you want to connect to a MySQL server running on 192.168.1.100 with the following details:

• Username: admin
• Password: securepassword
• Port: 3307

The command would look like this:

mysql -u admin -p -P 3307 -h 192.168.1.100

When executed, the command will prompt you for the password. Enter the password, and you should be connected to the MySQL server.

Troubleshooting Connection Issues

If you encounter problems connecting to the server, consider the following troubleshooting tips:

1. Verify the Port Number: Double-check that the port number is correct and matches the server configuration.
2. Check Firewall Settings: Ensure that the server’s port is open and accessible through any firewalls.
3. Confirm MySQL Server is Running: Use a command like netstat or ss to verify the server is listening on the specified port.
4. Inspect MySQL Configuration: Check the my.cnf or my.ini file on the server for the port parameter to confirm the active port.
5. Test with Telnet or Netcat: Use tools like telnet or nc to confirm that the port is reachable.

Connecting to a MySQL server on a custom port using the Command Line Client is straightforward with the -P option. Understanding how to specify a port, troubleshoot connection issues, and verify server settings ensures a smooth experience when working with non-default configurations.

By mastering this skill, you can adapt to various MySQL environments, whether for development, testing, or production purposes.

Feel free to share your experiences or questions about connecting to MySQL on custom ports in the comments below!