0

I am desperately trying to set up an SSH tunnel so that I can connect to a mySQL database. Here is the situation: I have an instance of mySQL 5.6 sever running on a host PC in a corporate network with port 3306 blocked by firewall. My network admin wont reply to any of my emails about the issue or unblocking the port so I can't get that to happen.

I want to connect to this host PC's instance of mySQL from a remote PC on another network. I've been trying hard to follow guides on using putty to create an ssh tunnel but I am failing miserably, is there anyone that can explain the process please? (in detail)

EDIT: (additional info added)

Okay, good point. So here is whats happened so far: I transferred the db onto the server with all tables ext.. I went to connect to the db from a client PC and entered the string 

mysql -uroot -h SRVR1 -P DBNAME"

and got 

error 1045(28000): Acess denied for user 'root'@192.168.myip#' (using password: YES)

... this led me to investigate further and discover that port 3306 (I think) is blocked. I ran netstat -a and saw that something is listening on port 3306 but it is obviously not working so I tried the ssh by using putty (following this guide :

ssh -fNg -L 3306:127.0.0.1:22 jaugust@SRVR1.com
mysql -h 127.0.0.1 -P 22 -u jaugust -p mydbname

Which also did not work and gave the access denied error

EDIT #2:

I went and checked out what the privileges are for root(from host PC): 

mysql> select user,host from mysql.user;
+----------+--------------+
| user     | host         |
+----------+--------------+
| admin    | %            |
| jaugust  | 127.0.0.1    |
| root     | 127.0.0.1    |
| root     | ::1          |
| root     | localhost    |
| someuser | somehostname |
+----------+--------------+
6 rows in set (0.00 sec)

mysql> show grants for 'admin'@'%';
+-------------------------------------------------------------------------------
--------------------------------------------------+
| Grants for admin@%
                                                  |
+-------------------------------------------------------------------------------
--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '****' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
--------------------------------------------------+

mysql> show grants for 'root'@'localhost';
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
                                                         |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '****' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `rti`.* TO 'root'@'localhost'
                                                         |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
                                                     |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
3 rows in set (0.00 sec)
Hooplator15
  • 1,540
  • 7
  • 31
  • 58
  • Why don't you explain specifically what you've tried, and what happened or didn't happen? – Kenster Sep 08 '14 at 18:26
  • The Access denied message doesn't imply 3306 is blocked - it means you established the connection but the MySQL server actively refused access for the user. MySQL grants access in 3 parts - user, host, and password. Does `root` actually have access granted from remote addresses? Check the grant hosts with something like `SELECT user, host FROM mysql.user WHERE user='root'` then if remote addresses are defined, check the grants: `SHOW GRANTS FOR 'root'@''` – Michael Berkowski Sep 08 '14 at 19:32
  • You may just need to grant remote access. http://stackoverflow.com/questions/6239131/how-to-grant-remote-access-permissions-to-mysql-server-for-user/6239145#6239145 – Michael Berkowski Sep 08 '14 at 19:35
  • 1
    So based on your update, you should already be able to connect to the remote host without SSH as the `admin` user (since it has `admin@%` granted) – Michael Berkowski Sep 08 '14 at 19:47
  • Yep, just tried that, it WORKED!!! Can't believe it. Thank you! Can I change root privileges so I can connect as root? – Hooplator15 Sep 08 '14 at 19:50
  • But if you want to go with a tunnel (which is more secure) the target port on the remote machine should be 3306, not 22: `-L3306:ip.of.mysql.server:3306` Then connect to MySQL on 127.0.0.1: `mysql -h 127.0.0.1 -uroot -p` which should talk to your local port an tunnel in, requesting root's password local to the server. – Michael Berkowski Sep 08 '14 at 19:50
  • @JohnAugust Yes, the answer I linked a few minutes ago addresses the remote grants for root. – Michael Berkowski Sep 08 '14 at 19:51

0 Answers0