How to test which port MySQL is running on and whether it can be connected to?
MysqlLinuxUbuntuMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
Using Mysql client:
mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';
Solution 3 - Mysql
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
Solution 4 - Mysql
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.
Solution 5 - 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"
Solution 6 - Mysql
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.
Solution 7 - Mysql
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>
Solution 8 - 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*
Solution 9 - Mysql
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 |
+---------------+-------+
Solution 10 - Mysql
you can use
ps -ef | grep mysql
Solution 11 - 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)
Solution 12 - Mysql
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.
Solution 13 - Mysql
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)
Solution 14 - Mysql
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)