Understanding and Configuring MySQL Port

Summary

In this tutorial, you will learn about the default MySQL port, how to find the port that MySQL is using, and how to change the default port.

Default MySQL Port

By default, MySQL uses port 3306 for communication between the client and the MySQL server. When you connect to a MySQL server, if you don’t specify a port, the client assumes port 3306.

Finding the MySQL Port

You can find the port that the MySQL server is using from the port variable or the MySQL configuration files.

MySQL Configuration Files

To find the port of the MySQL server, you can check the MySQL configuration files. The location of these files depends on your operating system.

Common MySQL Configuration Locations:

• Linux: /etc/my.cnf or /etc/mysql/my.cnf
• Windows: C:\ProgramData\MySQL\MySQL Server X.X\my.ini
• macOS: /etc/my.cnf or /usr/local/mysql/my.cnf

Find the port variable in the [mysqld] section in the configuration file:

[mysqld]
port=3306

MySQL Variables

Alternatively, you can find the port number that MySQL is currently using by showing the port variable if you are already connected to the MySQL server:

SHOW GLOBAL VARIABLES LIKE 'port';

Output:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

Changing the MySQL Port

To change the default MySQL port, follow these steps:

1. Open the MySQL Configuration File:Open the MySQL configuration file in a text editor. The location of the configuration file varies based on your operating system, as mentioned above.
2. Change the Port:Change the port under the [mysqld] section to a new port and save the configuration file.

[mysqld]
port=3301 # new port

1. Restart the MySQL Server:Restart the MySQL server for the changes to take effect. Ensure that the new port is not in use by other applications.

Connecting to MySQL Using a Non-Default Port

When you connect to a MySQL server using a non-default port, you need to use the -P or --port option followed by the port number:

mysql -h <hostname> -P <port_number> -u <username> -p

In this command:

• <hostname> is the host of the MySQL server.
• <port_number> is the port number on which MySQL is listening.
• <username> is the user account you want to use for the connection.

The command will prompt you to enter the password for the specified username.

Example:

To connect to the local MySQL server at port 3301 with the root user:

mysql -h localhost -P 3301 -u root -p

Port Security

When you open port 3306 or any other port for MySQL, you should restrict which IP addresses can access it to ensure that the MySQL server is not accessible from untrusted hosts.

Summary

• The default MySQL port is 3306.
• The port variable stores the port that the MySQL server is currently using.
• The port option under the [mysqld] section of the MySQL configuration file specifies the port to which the MySQL server is currently listening.
• You can change the default port by modifying the MySQL configuration file and restarting the MySQL server.
• When connecting to a MySQL server using a non-default port, use the -P or --port option followed by the port number.
• Ensure port security by restricting access to trusted IP addresses.

By understanding and configuring the MySQL port, you can optimize your database’s accessibility and security according to your specific requirements. Happy querying!