MySQL Default Port

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’s 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 that is 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 that are used in Unix/Linux operating systems. All these protocol values are used for establishing and maintaining the connection to a remote or local server. In MySQL TCP/IP sockets are used to connect with the clients that are situated remotely.

In this article, we will see how the ports currently being used are retrieved and how to check your MySQL port and connect with the MySQL database server using your default MySQL port and how we can change the default port used to connect to MySQL using the configuration file.

Checking the MySQL port you are using

When you are connected to the MySQL server and you want to check the port that is being used by your client to connect with 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 get to know the port number being used 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, the following command is used to get to know the value of MySQL port.

Mysql -u your_username -e “SHOW GLOBAL VARIABLES LIKE ‘PORT’”

Mysql database is accessed by multiple clients and users and this leads to the creation of multiple connections and sessions. All these are supported by MySQL that uses TCP/IP socket, Named pipes, or Unix sockets to connect with the network. The remote connections are always made using TCP/IP sockets and 3306 as the default port number of Mysql.

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

netstat -a |grep mysql;

that gives the following output in my case –

it tells us that there are 2 connected connections of MySQL and one connection is listening. In the above command, netstat is used to display the status of the net and -a stands for all the connections while | operator is used to specifying the filters and additional information. In our case, we are using grep that states to search with keyword “mysql” specified further.

Alternatively, you can use the following command –

netstat -na |grep 3306;

that gives following output –

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

netstat -na | find “3306”

that will display all connections with 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 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 that 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 following output –

For both these commands, we had to first log in as a superuser using su command.

In this way, we can connect to the MySQL database server by using the command-line on any platform.

Changing the default port of MySQL

Now, we will see where the default port of MySQL is configured and specified and how we can change it to any other port we want. The information about the MySQL configurations is stored inside the my.ini of my.cnf file. This file is present in the folder 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.

Depending on which MySQL server package is installed on your machine the extension of the file may change but it will always be either 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 –

It can be seen that online number 34 in my case the file contains a variable named port that has value 3306 specified as follows –

port = 3306

To change the default port, all we have to do is 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 windows platform.

Then open the configuration file. Suppose, we want to change the port to 4000 instead of 3306 as 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, firstly 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 platform 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. It can be further configured and changed by changing the port variable value in the configuration file.

Recommended Articles

This is a guide to MySQL Default Port. Here we discuss how the ports currently being used are retrieved and how to check your MySQL port. You may also have a look at the following articles to learn more –

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