[mysql] How to test which port MySQL is running on and whether it can be connected to?

I have installed MySQL and even logged in there as a user.

But when I try to connect like this:

http://localhost:3306
mysql://localhost:3306

Neither works. Not sure if both are supposed to work, but at least one of them should :)

How can I make sure that the port is indeed 3306? Is there a linux command to see it somehow? Also, is there a more correct way to try it via a url?

3306 is default port for mysql. Check it with:

netstat -nl|grep 3306

it should give this result:

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN

Using Mysql client:

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';

For me, @joseluisq's answer yielded:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

But it worked this way:

$ mysql -u root@localhost  -e "SHOW GLOBAL VARIABLES LIKE 'PORT';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

If you are on a system where netstat is not available (e.g. RHEL 7 and more recent Debian releases) you can use ss, as below:

sudo ss -tlpn | grep mysql

And you'll get something like the following for output:

LISTEN     0      50        *:3306        *:*        users:(("mysqld",pid=5307,fd=14))

The fourth column is Local Address:Port. So in this case Mysql is listening on port 3306, the default.

Both URLs are incorrect - should be

jdbc:mysql://host:port/database

I thought it went without saying, but connecting to a database with Java requires a JDBC driver. You'll need the MySQL JDBC driver.

Maybe you can connect using a socket over TCP/IP. Check out the MySQL docs.

See http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

UPDATE:

I tried to telnet into MySQL (telnet ip 3306), but it doesn't work:

http://lists.mysql.com/win32/253

I think this is what you had in mind.

On a mac os X, there are two options. netstat or lsof

Using netstat will not show the process on Mac OS X. so using netstat you can only search by port.
Using lsof will show the process name.

I did the following as I was encountering port conflicts (docker containers):

netstat -aln | grep 3306

Outputs: tcp46 0 0 *.3306 *.* LISTEN

sudo lsof -i -P | grep -i "LISTEN" | grep -i 3306

Outputs: mysqld 60608 _mysql 31u IPv6 0x2ebc4b8d88d9ec6b 0t0 TCP *:3306 (LISTEN)

I agree with @bortunac's solution. my.conf is mysql specific while netstat will provide you with all the listening ports.

Perhaps use both, one to confirm which is port set for mysql and the other to check that the system is listening through that port.

My client uses CentOS 6.6 and I have found the my.conf file under /etc/, so I used:

grep port /etc/my.conf (CentOS 6.6)

netstat -tlpn

It will show the list something like below:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1393/sshd
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      1859/master
tcp        0      0 123.189.192.64:7654     0.0.0.0:*               LISTEN      2463/monit
tcp        0      0 127.0.0.1:24135         0.0.0.0:*               LISTEN      21450/memcached
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      16781/mysqld

Use as root for all details. The -t option limits the output to TCP connections, -l for listening ports, -p lists the program name and -n shows the numeric version of the port instead of a named version.

In this way you can see the process name and the port.

A simpler approach for some : If you just want to check if MySQL is on a certain port, you can use the following command in terminal. Tested on mac. 3306 is the default port.

mysql --host=127.0.0.1 --port=3306

If you successfully log in to the MySQL shell terminal, you're good! This is the output that I get on a successful login.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9559
Server version: 5.6.21 Homebrew

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Try only using -e (--execute) option:

$ mysql -u root -proot -e "SHOW GLOBAL VARIABLES LIKE 'PORT';"                                                                                                       (8s 26ms)
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

Replace root by your "username" and "password"

grep port /etc/mysql/my.cnf ( at least in debian/ubuntu works )

or

netstat -tlpn | grep mysql

verify

bind-address 127.0.0.1

in /etc/mysql/my.cnf to see possible restrictions

you can use

ps -ef | grep mysql