Checking a Running MySQL Server’s Port

1. Overview

Indeed, every application employs ports to interact with other devices across the network. As system administrators, we’ll invariably need an application port number to monitor the services and connect with other services. Today, MySQL is one of the most popular relational databases and is widely used by system administrators to store and retrieve data.

This tutorial will elucidate the different methods to check the port on which the MySQL server is running by way of an example.

Now, let’s get into the nitty-gritty of it.

2. Identifying the MySQL Runtime Info

In this section, we’ll see the three different ways of identifying the port that is used by the MySQL server.

2.1. Using MySQL Client Services

First, we can quickly get the port information from the global variables of the MySQL services. It’s mandatory to install the client on the host machine in order to use this method:

server# sudo apt install mysql-client -y
Reading package lists... Done
Building dependency tree       
Reading state information... Done
mysql-client is already the newest version (5.7.41-0ubuntu0.18.04.1).
...
... output truncated ...
...

Now, let’s log in to the MySQL server using the client and extract the port information from the global variables definition:

server# mysql -u root -p
Enter Password: 

mysql>
mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

Alternatively, we can also access the global variables of the remote MySQL server using an inline command:

server# mysql -u root -h 172.17.0.4 -pmy -e "SHOW variables LIKE 'port';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

Here, we’re using the default port 3306 for the MySQL services.

2.2. Using MySQL Configuration File

We can also get the port details from the MySQL configuration file, “mysqld.cnf”. To extract the port information from the configuration file, let’s use the grep command:

server# sudo grep ^port /etc/mysql/mysql.conf.d/mysqld.cnf 
port		= 3306

Further, we can update the configuration file using the editor if we need to set a new port for the MySQL services. By default, the configuration file will be available in /etc/mysql/mysql.conf.d/, but the location varies depending on the operating system.

Now, let’s open the configuration file and update the port number under the [mysqld] section. Finally, we’ll save the file and restart the MySQL services for immediate effect:

[mysqld]
# * Basic Settings
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql

2.3. Using the netstat Command

netstat is a command-line utility of the Linux system that showcases the outbound network statistics for several network protocols and interfaces. Further, it will also help to investigate the network sockets and test the Linux network ports:

server# sudo netstat -tulpn 
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.1:3306            0.0.0.0:*               LISTEN      mysql  

Also, we can use the ps command to track the service PID and then the netstat command to get port-level insights:

server# ps aux | grep mysql
mysql     1894  0.8  9.8 1865804 1192032 ?     Sl   Jul01 300:53 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/DBNAME.err --pid-file=/var/lib/mysql/DBNAME.pid

Alternatively, the ports can also be mapped with the valid IP address depending on the bind-address configuration defined in the MySQL configuration file:

[mysqld]
# * Basic Settings
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
...
... output truncated ...
...
bind-address		= 190.87.34.89

server# sudo netstat -tulpn 
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 190.87.34.89:3306            0.0.0.0:*               LISTEN      mysql

3. Accessing the MySQL Server

Now, let’s access the MySQL server using the port information from the previous section.

Here, the argument -P represents the MySQL service port number. The -h option represents the service host’s IP address, -u represents the username, -p represents the password, and –protocol represents the underlying TCP protocol:

server# sudo mysql -h 190.87.34.89 -P 3306 --protocol=tcp -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
...
... output truncated ...
...
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

...
... output truncated ...
...

mysql> 
mysql> exit
Bye
server#

We’ve successfully logged into the MySQL server using the identified port number.

4. Conclusion

In summary, when working with Linux, we must know the port numbers on which the application services run. Using any of the techniques above, we can quickly check the port on which MySQL services are running.

Also, we’d covered steps for accessing the MySQL server using extracted port details.