create ssh tunnelling for phpMyAdmin



  • I have 2 web servers with MySql server onboard, let's call them b.myserver.com and c.myserver.com .

    I'd like to create a server (db.myserver.com) with phpMyAdmin onboard to be able to access all the databases on b.myserver and c.myserver without the need to create new remote mysql users. I plan to add some new servers in the future so I'd like to reduce the setup to the bare minimum.

    Every server is configured to accept connections on port 80,443 and SSH. MySql listen to localhost only on 3306. SSH access is restricted to allowed keys only

    I made some research and I think I can use ssh tunnelling but it's unclear to me how to do exactly.

    phpMyAdmin allow me to define multiple configurations (one per server) so I thought I can assign one local port and one ssh tunnel per server.

    i.e:

    localhost:10001 (on db.myserver.com) forward over ssh to b.myserver.com:3306 localhost:10002 (on db.myserver.com) forward over ssh to c.myserver.com:3306

    and so on.

    I can use autossh to re-establish connection if needed.

    Can I consider this a good and safe approach or are there other alternatives available?

    Thanks



  • I found the solution by doing the following:

    Inside db.myserver.com server create a new ssh tunnel:

    ssh -L UNIQUE_LOCAL_PORT:REMOTE_HOST:REMOTE_PORT authorized_ssh_user@remotehost -N 
    

    Authentication is passwordless because I already added ssh_user key to ~/.ssh/authorized_keys on remotehost

    Suppose I assigned port 10001 to b.myserver.com, I can connect to it trought: i.e.

    ssh -L 10001:127.0.0.1:3306 authorized_ssh@b.myserver.com -N
    

    The tricky part to understand is that I have to use 127.0.0.1 as REMOTE_HOST instead of its public ip. That's because destination is reached over an ssh tunnel, so the connection is like it starts locally. So 127.0.0.1 is the ip of the database as seen from inside b.myserver.com

    to connect from db.myserver.com to b.myserver.com I just specify port 10001. If I want to connect to c.myserver.com I just have to set up another tunnel and to specify another port.

    mysql -u user_b -p -h 127.0.0.1 -p 10001
    

    Hope it helps 🙂


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2