1. Overview
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. MySQL is one of the most popular relational databases 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. However, it’s mandatory to install the client on the host machine 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”. Further, 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
Moreover, 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.
Therefore, 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
Additionally, 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. Finding URL of the MySQL Server
We know the port number used by the MySQL server. Now, we’ll discuss how to find the URL or address of the MySQL server.
Let’s take a look at the format of the URL of the MySQL server:
jdbc:mysql://host:port/database
Here, jdbc is the name of the protocol we use to establish a connection with the database. To find the server’s address, in addition to the port number, we also need to know the host and name of the database.
First, let’s connect to the MySQL server and extract connection details:
$ sudo mysql -u root -p
Enter Password:
mysql>
mysql> \s
--------------
mysql Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))
Connection id: 10
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.37-0ubuntu0.22.04.3 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: --------------
Here, the output provides useful information, including the current user, server version, and connection details.
Furthermore, we can use the mysql.user table to find the host and usernames:
mysql> SELECT User, Host FROM mysql.user;
+------------------+-----------+
| User | Host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
The output displays five users on the system, along with their hostnames. Since the database and the MySQL server are stored on the same machine, we can utilize localhost as a hostname.
Finally, let’s assume that the name of the database we want to connect to using the MySQL server is TestDB. Therefore, putting all the data together, the URL would be:
jdbc:mysql://localhost:3306/TestDB
4. 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#
Therefore, we’ve successfully logged into the MySQL server using the identified port number.
5. Conclusion
In this article, we discussed how to find the URL, host, port, and username of the MySQL server in Linux.
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.
Additionally, we covered steps for finding the URL and accessing the MySQL server using extracted port details.