How do i free up port 3306 on windows 10?

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:

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.

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:

1. Create a firewall rule to open port number 3242.
2. Stop MySQL Service and add the port number into the my.ini file.
3. 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!

Tags: mysql, windows, workbech Last modified: September 23, 2022

How do I fix MySQL port 3306 already in use?

How do I check if port 3306 is open Windows 10?

How do I fix port 3306 in use by unable to open process?

How do I change MySQL port from 3306 to 3307?