0

Is there a simple ping- like command to test if MySQL's 3306 is responsive?

The ultimate goal is to diagnose MySQL Workbench: Diagnosing connection error

NMAP

Assume that MYSQL is on the LAN at a known IP address: 192.168.8.112

nmap -p 3306 192.168.8.112

returns:

Starting Nmap 7.94 ( https://nmap.org ) at 2023-12-27 17:11 EST
Nmap scan report for mysql.local (192.168.8.112)
Host is up (0.0016s latency).

PORT     STATE  SERVICE
3306/tcp closed mysql

Nmap done: 1 IP address (1 host up) scanned in 0.03 seconds

mysql command line

The mysql command line:

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';

returns:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.02 sec)

The ability to 'ping' port 3306 may enable trouble shooting a connection from MacOS to Ubuntu MySQL with MySQL workbench.

TELNET

BEFORE binding to 0.0.0.0 was performed:

user@iMac ~ % telnet 192.168.8.112 3306 returns:

Trying 192.168.8.112...
telnet: connect to address 192.168.8.112: Connection refused
telnet: Unable to connect to remote host

After binding was performed:

enter image description here

NETSTAT (RECOMMENDED COMMAND)

performed from command mysql host's Ubuntu command line:

sudo netstat -tulpn  | grep -e "192.168.8.112" -e "mysql"

after changing the bind-address to 0.0.0.0:

enter image description here

The output indicates MySQL is now listening on 0 0.0.0.0:3306 local address. It means the server is now listening on all network interfaces on port 3306.

reference: https://tecadmin.net/mysql-allow-remote-connections/#

4
  • SELECT 1; is 'shorter'. What is the purpose of your pinging?
    – Rick James
    Dec 27, 2023 at 23:33
  • @RickJames ping is a good "smoke test" that confirms a target is 1) listening and 2) active. The goal is to determine if mySQL is responsive on 3306
    – gatorback
    Dec 28, 2023 at 2:00
  • @RickJames. I posted telnet results at the tail of the posting. Not sure that it confirms that port 3306 is open and responds via mySQL
    – gatorback
    Dec 28, 2023 at 5:39
  • So, none of these commands suits you and you're looking for another one? What exactly should it do that these don't?
    – mustaccio
    Dec 28, 2023 at 21:08

3 Answers 3

3

You can also use mysqladmin with the ping option

From the MySQL 5.7 Documentation

ping

  • Check whether the server is available. The return status from mysqladmin is 0 if the server is running, 1 if it is not. This is 0 even in case of an error such as Access denied, because this means that the server is running but refused the connection, which is different from the server not running.

EXAMPLE #1

Using standing MySQL Connection

MYSQL_HOST=...
MYSQL_USER=...
MYSQL_PASS=...
MYSQL_AUTH="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
mysqladmin ${MYSQL_AUTH} ping

EXAMPLE #2

Using TCP/IP you could also do

MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=...
MYSQL_PASS=...
MYSQL_AUTH="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_AUTH="${MYSQL_AUTH} -P${MYSQL_PORT} --protocol=tcp"
mysqladmin ${MYSQL_AUTH} ping

The output will say mysqld is alive if you connected successfully. Otherwise, it will give an error message.

You could search for the string mysqld is alive as follows

MYSQL_HOST=...
MYSQL_USER=...
MYSQL_PASS=...
MYSQL_AUTH="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_RUNNING=$(mysqladmin ${MYSQL_AUTH} ping 2>&1 | grep -c "mysqld is alive")
MYSQLD_STATE=Running
[ ${MYSQL_RUNNING} -eq 0 ] && MYSQLD_STATE="Not Running"
echo "mysqld is ${MYSQLD_STATE}"
2
telnet hostname 3306

is another way. But it returns an indication that mysqld is responding, plus gibberish (such as asking for a password) that you should ignore.

3
  • Thanks for the suggestion. I posted the results at the tail of the OP. Not sure if Connection refuse confirms an active listening?
    – gatorback
    Dec 28, 2023 at 3:24
  • "Connection refused" indicates that nothing is listening. And that corresponds with nmap telling you that port 3306 is closed.
    – Doug Deden
    Dec 28, 2023 at 3:52
  • @DougDeden. Indeed. I have changed the MySQL binding from localhost to 0.0.0.0 and posted the new telnet output to confirm your diagnosis
    – gatorback
    Dec 28, 2023 at 14:17
1

Telnet (like in Rick's answer) is what I typically use. But I often find myself on a machine that has no telnet client. So the next tool on my list is PowerShell's test-netconnection command. It goes something like this:

test-netconnection < hostname > -port < portnum >

So in your case:

test-netconnection 192.168.8.112 -port 3306

2
  • Is said command supposed to be in this powershell? brew install --cask powershell
    – gatorback
    Dec 28, 2023 at 5:35
  • I'm not familiar with that flavor of PowerShell. I was thinking about the flavor that is built in to Windows. But from some of your updates I see that you aren't running Windows, so PowerShell is not a very helpful answer for you. Sorry.
    – Doug Deden
    Dec 28, 2023 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.