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 = 220.127.116.11 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 18.104.22.168: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 22.214.171.124 -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.
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.