A network port is a communication endpoint used by applications, incoming connections, and external devices to the computer/server or specific application. In this article, we’ll discuss MySQL ports and learn how we can change the default port on Windows 10.
Table of contents
- List of Ports Used by MySQL Features
- Environment Setup
- Create a Firewall Rule in Windows
- Stop MySQL Service and Add the Port Number in the my.ini file
- Start the MySQL Services
- Test Connectivity Using the Command Line
- Summary
Every RDBMS has its default port to accept incoming connections. The below table presents the default ports for popular RDBMS:
| Database Name | Ports |
| MySQL Server | 3306 |
| Oracle database | 1521, 1830 |
| PostgreSQL | 5432 |
| SQL Server | 1433, 1434 |
| MongoDB | 27017, 27018, 27019,28017 |
| Cassandra | 7000, 7001, 9042 |
For MySQL Server, the default port is 3306. It is a TCP port that is used by the MySQL client connectivity tools. Assume we want to change this default port of MySQL Server on Windows 10. For that, we have to explore some theory and practical tasks.
List of Ports Used by MySQL Features
Let us have a look at the list of important ports of the MySQL Server. Note that they are used by different features, and some of them are non-default. However, we should keep them all in mind.
| MySQL Feature | Default Port Number |
| MySQL Client to Server – MySQL Protocol | 3306 |
| MySQL Client to Server – New X Protocol | 33060 |
| For checks, from Shell to GR server during InnoDB cluster configuration. | 33061 |
| MySQL Workbench MySQL Client to Server | 3306 |
| MySQL Workbench MySQL Client to Server New X protocol | 33060 |
| MySQL Workbench Connects via SSH Tunnel | 22 |
| MySQL Client Any SQL to Router – MySQL Protocol | 6446 |
| MySQL Client ReadOnly SQL to Router – MySQL Protocol | 6447 |
| MySQL Router to Server – MySQL Protocol | 3306 |
| MySQL Client API calls to MySQL Router – New X Protocol | 6448 |
| MySQL Client Read-Only Calls to Router | 6449 |
| MySQL Router to Server | 33060 |
| MySQL Group Replication internal communications | 33061 |
| MySQL Replication | 3306 |
| MySQL Authentication – LDAP | 389 |
| MySQL Authentication – AD | 389 |
Ideally, to keep our database secure, we must disable default ports. So, let’s consider changing the default port of the MySQL Server.
Environment Setup
I have prepared a lab setup to understand the step-by-step process to change the Port. I have installed MySQL Server on my Workstation and enabled the Windows Defender firewall.
We are going to change the MySQL Server port number from 3306 to 3242. For that, we must perform the below steps:
- Create a firewall rule to open port number 3242.
- Stop MySQL Service and add the port number into the my.ini file.
- Start the MySQL Services and test the connectivity using the MySQL command-line tool.
Now, let us understand how to create a firewall rule that allows incoming connections from port number 3242.
Create a Firewall Rule in Windows
To create a firewall rule, open the Control Panel > Windows Defender firewall > Advanced settings:
A dialog box named Windows Defender Firewall with Advanced Security opens. Click on Inbound Rules from the left pane > New Rule:
The New Inbound Rule Wizard starts. On the Rule Type screen, select Port.
On the Protocols and Ports screen, select TCP and enter 3242 into the Specific local ports field.
Select Allow the connection on the Action screen.
We want to apply the rule to all network locations. Select Domain, Private, and Public checkboxes and click Next.
Specify the Name and Description of the new firewall’s rule in the Name screen.
Click Finish to create the rule and close the wizard.
Now, let us stop the MySQL service and edit the MySQL configuration file.
Stop MySQL Service and Add the Port Number in the my.ini file
Before we change the default port, we must stop the MySQL Services. We are using PowerShell commands for that:
PowerShell Command
PS C:\> net stop MySQL
Once the Services are stopped, we’ll edit the configuration file. We can change the default port by editing the my.ini configuration file.
The my.ini file is in the MySQL Home directory. In this demo, the location of that directory is C:\MySQL_Home. Once you find the file, open it with any text editor and add the following line:
Port = 3242
Save the file and close it.
Start the MySQL Services
Once the my.ini file is updated, we should start MySQL from the Services. Use the following command:
PS C:\> net start MySQL
Test Connectivity Using the Command Line
When the Services are started, connect to MySQL – open cmd.exe and run the following command:
C:\Users\Nisarg>mysql -u root -p
As you can see in the above screenshot, the command encounters an error.
To connect to MySQL using port number 3242, we must use the -P option in the MySQL command:
C:\Users\Nisarg>mysql -u root -P 3242 -p
Enter password: **************
Now, MySQL has been connected successfully.
You can use the SHOW VARIABLES command to verify that your MySQL server accepts incoming connections on port 3242.
mysql> Show variables like '%Port%'
The screenshot proves that MySQL Server is now listening on port number 3242.
Test Connectivity using MySQL Workbench
Let us understand how to connect to the MySQL server through port 3242 using MySQL Workbench.
Open MySQL Workbench and on the Welcome screen, click New(+)
The Setup New Connection window opens. There, specify 3242 in the textbox named Port:
Click Test Connection.
Output:
As you can see, the connection is established successfully. Now, run the following query in the query editor window.
show variables like '%Port%'
MySQL services are listening on port number 3242.
Alternatively, you can view the Port of the MySQL Server from the Server Status window:
Summary
Thus, we have learned about the ports of database servers and MySQL default ports. We also got the comprehensive lists of the default network ports of well-known database servers and the specific port used by different MySQL Server features.
The process of changing the default port of the MySQL Server is quite straightforward. You only need to perform the required steps, and this will ensure that your MySQL Server will accept connections from the previously non-default ports. This practice is widely used for increasing Server security.
We hope that these tips will be useful in your work!
