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:

 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?

14 Answers

To find a listener on a port, do this:

netstat -tln 

You should see a line that looks like this if mysql is indeed listening on that port.

tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 

Port 3306 is MySql's default port.

To connect, you just have to use whatever client you require, such as the basic mysql client.

mysql -h localhost -u user database

Or a url that is interpreted by your library code.

Using Mysql client:

mysql> SHOW GLOBAL VARIABLES LIKE 'PORT'; 

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

or

netstat -tlpn | grep mysql 

or

mysql -u user_name -puser_pass -e "SHOW variables LIKE 'port';" 

verify

bind-address 127.0.0.1

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

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.

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"

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

UPDATE:

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

I think this is what you had in mind.

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> 

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

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 | +---------------+-------+ 

you can use

ps -ef | grep mysql 

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)

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.

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)

I think the most appropriate way of finding the port associated with mysql server using lsof command line tool.

lsof -i -n -P | grep mysql mysqld 1556 prince 10u IPv4 0x6ad3fd78a9051969 0t0 TCP 127.0.0.1:3306 (LISTEN)