MySQL Default Port

Updated June 7, 2023

Introduction to MySQL Default Port

MySQL Ports are one kind of mechanism in network protocols that allows the server to handle and manage multiple client connections and sessions in a particular network. The default port of MySQL is 3306. The port is represented and identified by a numerical value of 2 bytes, 16 bits in size. Out of them, 65536 port numbers are named ports that are further classified based on the protocol used for transport as TCP or UDP ports.

There are four types of protocol values, namely, TCP, used in all operating systems; PIPE and MEMORY, used in the Windows operating system and SOCKET, used in Unix/Linux operating systems. All these protocol values serve the purpose of establishing and maintaining connections to remote or local servers. MySQL utilizes TCP/IP sockets to establish connections with remotely located clients.

This article will demonstrate retrieving the currently used ports, checking your MySQL port, and connecting with the MySQL database server using the default MySQL port. Additionally, it will explain how to modify the default port used for connecting to MySQL by making changes in the configuration file.

Checking the MySQL port

When you are connected to the MySQL server and want to check the port your client is using to connect with the MySQL server, you can retrieve the value of the variable named port that stores the port number used for the connection. This can be done in the following way –

SHOW VARIABLES LIKE 'port';

You can use the above command in Unix/Linux platforms and learn the port number to connect to the MySQL server. In our case, the output is as follows –

We can see that the value of the port variable is 3306. This is the default value of the MySQL port number.

In Windows, you can use the following command to retrieve the value of the MySQL port:

Mysql -u your_username -e "SHOW GLOBAL VARIABLES LIKE 'PORT'"

Multiple clients and users access the Mysql database, leading to various connections and sessions. MySQL supports all these, which use TCP/IP sockets, Named pipes, or Unix sockets to connect with the network. Remote connections to MySQL always utilize TCP/IP sockets, with the default port number being 3306.

To check the port of MySQL on our network in Unix/Linux platforms, we can make use of the following command –

netstat -a |grep mysql;

that gives the following output in my case –

Executing the mentioned command reveals two active connections for MySQL, with one of them in a listening state. The command employs the “netstat” utility to exhibit the network status, while the “-a” option instructs it to display all connections. Furthermore, the “|” operator includes filters and supplementary information to define the output. In our case, we “se gr”p, which states to search with the keyword “mysql” specified further.

Alternatively, you can use the following command –

netstat -na |grep 3306;

that gives the following output –

In Windows, you can make use of the following query –

netstat -na | find “3306”

that will display all connections with the 3306 port. The output indicates that the port with port number 3306 is listening at localhost 127.0.0.1 address and uses TCP socket and protocol for connection.

Connecting to Mysql database server

We can connect to our MySQL database server using the command-line terminal with the help of a TCP/IP protocol connection by connecting to a specific port that is listening to the network connections. As we saw in the output of the above command, the 3306 port is listening. The command used to connect to the database server using its default port and root access is as follows –

mysql -u root

that gives the following output –

By specifying the particular port using which you want to establish the TCP/IP connection to the MySQL database server, you can use the following command –

mysql -u root –port=3306

that gives the following output –

We had to first log in as a superuser using the su command for both these commands.

This approach allows us to connect to the MySQL database server using the command line on any platform.

Changing the default port of MySQL

Now, let’s explore the configuration of the default port for MySQL and learn how to modify it according to our preferences. The my.ini or my. cnf file contains the MySQL configuration details, including settings and parameters. Locate the my.ini or my.cnf file in the directory where your database is installed. It is very easy to change the port. All we have to do is to change the settings of the file.

The file extension may change depending on which MySQL server package is installed on your machine but will always be ini or cnf with the name my or mysql.cnf or mysqld.cnf. In my case, this file is located here –

/etc/mysql/mysql.conf.d/mysqld.cnf

and it contains the following things –

Observing online number 4 in my case, it can be noted that the file contains a variable named “port” with the value 3306 specified.

port = 3306

To change the default port, we have to change the value of the 3306 to the port number that you wish your MySQL database server to listen to and then save the file. Built before this, it is necessary to the execution of the MySQL service that is currently running using the following query –

service MySQL stop

in unix/linux platform and

stop mysql.exe

for the Windows platform.

Then open the configuration file. Suppose we change the port to 4000 instead of 3306 as the default port. Then, we will change the port value to 4000 in the configuration file, as shown below, and save this file.

If the file is in the read-only mode, change the privileges by either using the chmod command or changing the properties and then save the file.

Then restart the MySQL service by using

service mysql start

in Unix/Linux platforms and

start mysql.exe

for windows platform.

Conclusion

Mysql has the default port number 3306 assigned to it, where the server listens for client connection establishment. You can further configure and change the port variable value in the configuration file to modify it.

Recommended Articles

We hope “hatEDUCBA’sUCBA information on “MySQLEDUCBA’s Port” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL SHOW
2. MySQL Offset
3. Grant Privileges MySQL
4. MySQL Binlog