Port Forwarding Tricks

Xeoncross

Don't run development tools on your VPS - just don't do it.

I would never run something like phpMyAdmin on a VPS unless I was hosting for other users that had no idea what they were doing.

Did you know you can run phpMyAdmin (or MySQL Workbench) on your own computer and use an SSH tunnel to have it access the MySQL on your VPS?

ssh -L 33066:127.0.0.1:3306 [email protected]

This will allow you to configure your phpMyAdmin to listen over port 33066 and have it forwarded to your VPS. You can even add it as an alias in your .bashrc ($ vim .bashrc) so you can call it like $ vps_db?

alias vps_db = 'ssh -L 33066:127.0.0.1:3306 [email protected]'

This also works for browsing the web if you want to have your own private HTTP(s) proxy so your IP shows your VPS IP.

So what port forwarding techniques do you use most often?

carlosg

Excellent tip!

Dudeist

Or one can use Adminer instead, which is a lightweight, single file (200 kb) alternative to phpmyadmin. The only place I use phpmyadmin is on my local XAMPP installation. Either way it's good to know one can do that when needed. Thanks!

Xeoncross

Well, you still don't want a public facing MySQL administration utility on your VPS - even if it is SQLBuddy, Adminer, or phpMiniAdmin.

Personally, I say they should all banned so people will learn a real administration system like MySQL Workbench.

AuroraZ

Xeoncross said: Well, you still don't want a public facing MySQL administration utility on your VPS - even if it is SQLBuddy, Adminer, or phpMiniAdmin.

Personally, I say they should all banned so people will learn a real administration system like MySQL Workbench.

So should all GUIs unless you have a minimum 5 years command line exp.

drmike

@AuroraZ mentioned that "So should all GUIs unless you have a minimum 5 years command line exp."

Oh that will go over well with my soccer moms.

Xeoncross

@Aurora, it's about choosing the right tools for the job - not competing for the fun of it. Sure, I wish more people used the prompt - but you can only do so much there. Charting complex database relations using graphs isn't possible via $ mysql but is thanks to the MySQL Workbench.

AuroraZ

I was just saying that the GUI has ruined a lot of things. If people HAD to use a command line and learn it not only would it make it better for them in the long run but also it might be better on the providers. A lot of problems can be solved if the user would only take the time and think through it.

Here is an easy and stupid example. I walked into a place a couple years ago. This place was supposed to help others get jobs. I noticed a whole line of computers not working. So I asked what was wrong with those. The lady running it said the monitors would not turn on. She went on to explain that the "techs" (I have to use this term very loosely) could not even figure them out. I walked over and took a look behind them to see if they were plugged in. Turns out they had plugged them into the computer but not the wall outlet. Two minutes of thought could have solved this problem and anyone could have done it.

I realize this has nothing to do with the command line but if more people knew how to use them then maybe they could do their own tests which could then help the techs help them. I understand the need for GUI for some people and I use them myself but at least if things go wrong I know enough to use other methods to get the job done.

drmike said: Oh that will go over well with my soccer moms.

No it probably would not go over now but if they had to learn it to begin with they would not know a difference.

I know this entire post is way off topic so I won't go down it here. PM me if you would like to continue this discussion. It is not fair for any of us to hijack a thread. I am sorry I did this and apologize.

kristal

id like to see udp port forwarding trick... ;/

Go59954

Nice! What is suggested seems like a good foundation for controlling, not for only Mysql As you said that's better security wise, and probably for speed and lessen the load on VPS end, overall it's better imo.

But at the same time I thought having everything on the VPS might be useful at one day, like if something went wrong while you are abroad and have public PC to connect from, and also it's not running Linux! does for example phpMyAdmin, have a Windows version? So that in a public PC one can setup (quickly!) SSH tunnel and anything like phpMyAdmin

gsrdgrdghd

Sure you can put XAMPP+PMA and Putty on an USB drive and then walk around with everything you need to administrate the server. Keep in mind that in some public places certain ports etc might be blocked tho.

NickM

Or just put a Linux distro on the USB drive and boot the machine from that. Assuming, of course, that you won't get kicked out of whatever place for doing so.

drmike

@AuroraZ

No it probably would not go over now but if they had to learn it to begin with they would not know a difference.

I have a couple who have learned command line but 99.9%, I wouldn't even suggest it.

Of course though that's what I cater to.

@Go59954

So that in a public PC one can setup (quickly!) SSH tunnel and anything like phpMyAdmin

Could always put Apache on a USB stick and localhost it. I would think that would work.

(edit: I missed that suggestion made up there. Sorry.)

@gsrdgrdghd

Keep in mind that in some public places certain ports etc might be blocked tho.

sigh Story of my life.

Xeoncross

gsrdgrdghd said: put XAMPP+PMA and Putty on an USB drive

Actually, I can do better than that. Install my WNMP server which allows you to use whatever version of NGINX, PHP, MySQL, Memcached you need to match the exact version your server is using.

AuroraZ said: It is not fair for any of us to hijack a thread. I am sorry I did this and apologize.

I am grateful for your contribution, you spoke in a well written and thoughtful way. Comments like this are never unwanted even if they are off topic.

Go59954 said: does for example phpMyAdmin, have a Windows version?

phpMyAdmin runs on PHP, the OS doesn't matter as long as PHP works.

yomero

kristal said: id like to see udp port forwarding trick... ;/

Yeah, is my dream too

speedy007

udp port forwarding

yomero

@speedy007 said: udp port forwarding

Damn, seems complex to read now :P, bookmarking n_n

Thanks

Aldryic

@AuroraZ said: I was just saying that the GUI has ruined a lot of things.

GUI is also capable of introducing more advanced concepts to novice users. phpMyAdmin, for example, gives you a raw dump of damn near everything you do. Don't know how to select/edit multiple fields? Do it in phpMyAdmin, review the raw output. Command learned.

dmmcintyre3

I use http://www.sequelpro.com/ for MySQL administration which has built in SSH tunneling.

Aldryic

I know that's the common pronunciation for SQL, but it still irks me every time I hear it. <_<

justinb

@Aldryic said: I know that's the common pronunciation for SQL, but it still irks me every time I hear it. <_<

It pisses me off when people don't just say S-Q-L, so when I'm around those kinds, I use "squeal" specifically. Like "my squeal"

LowEndAdmin

@Xeoncross said: alias vps_db = 'ssh -L 33066:127.0.0.1:3306 [email protected]'

Rather than a shell alias, usually I just add the entry to SSH config file. For example

$ cat >> ~/.ssh/config <<END
> Host vps_db
>    HostName site.com
>    User user
>    LocalForward localhost:33066 localhost:3306
> END

One advantage is that the configuration is also used by other applications that use SSH (rsync, git, etc).

amigoserv

WoW nice tut. thank you

Abhishek

I use

ssh -D 4000 remote-user@remote-host

4000 is local port. After doing this there is a socks5 proxy on my local 4000 port.

Now in any application if you set your socks proxy to be 127.0.0.1:4000, all the remote sites will see your remote-host IP.

Note - In firefox, leave all but socks proxy options empty. Else it will not work.

Note 2 - On linux system use tsocks to force applications like apt-get to use proxy. The command then looks like sudo tsocks apt-get install wmd

Cheers!

danielfeng

Too much trouble if you need to forward multiple ports. I just use OpenVPN and then I can access all ports. Be sure to setup iptables (I use ufw frontend) of course.

cleonard

For the more than mildly paranoid the ssh with port forwarding can be a bit better than the VPN option. The reason is access. The ssh solution is a one way tunnel. The far side has no access. This is not the case with the VPN. Well unless you take the effort of setting up a very restrictive firewall on the VPN.

When working remotely with my "day job" I used to do ssh tunneling only. They removed that and now my only option is the wonderful Microsoft PPTP VPN. I don't like that so much. Tons of crap comes in over that interface from the work network.

yomero

You can't ssh tunnel?

Joel

@AuroraZ said: If people HAD to use a command line and learn it not only would it make it better for them in the long run but also it might be better on the providers.

I have a database that's over 210 tables, 2760 columns. Now I have had to document each and every single one of these and how it functions, what the values represent and how they affect the web app along with huge algorithms. Tell me, of what benefit am I going to get putting a command line up on a big screen in front of 3 developers? I'm damn curious.

For those of us that actually do need to make a living and be somewhat time efficient there's great apps out there such as Navicat. For the rest that want to relive their hey-day as a neanderthal I guess there's the command line for that!

Aldryic

Related - http://i52.tinypic.com/10i5apg.png (yes, that's 2826 tables)

INSERT INTO GBAFLS.OM10 (OM10_RECSTA, OM10_TECPRCSTA1, OM10_CREDAT, OM10_CRETIM, OM10_CREUSR, OM10_UPDDAT, OM10_UPDTIM, OM10_UPDUSR, OM10_OUTNUM, OM10_ADJSEQ, OM10_ADJLST, OM10_ADJCLCBAS, OM10_ADJSKP, OM10_FRQCOD, OM10_NXTRGEBIL, OM10_NXTRGEDSC, OM10_NXTRGECRD, OM10_EFRDAT, OM10_EFTDAT, OM10_ADJINAIND, OM10_USRARE004, OM10_VOLCTCIDR, OM10_CRYCODEST, OM10_ADJIND01, OM10_ESTRAT01, OM10_ADJIND02, OM10_ESTRAT02, OM10_ADJIND03, OM10_ESTRAT03, OM10_ADJIND04, OM10_ESTRAT04, OM10_ADJIND05, OM10_ESTRAT05, OM10_ADJIND06, OM10_ESTRAT06, OM10_ADJIND07, OM10_ESTRAT07, OM10_ADJIND08, OM10_ESTRAT08, OM10_ADJIND09, OM10_ESTRAT09, OM10_ADJIND10, OM10_ESTRAT10, OM10_ADJIND11, OM10_ESTRAT11, OM10_ADJIND12, OM10_ESTRAT12, OM10_ADJIND13, OM10_ESTRAT13, OM10_ADJIND14, OM10_ESTRAT14, OM10_ADJIND15, OM10_ESTRAT15, OM10_ADJIND16, OM10_ESTRAT16, OM10_ADJIND17, OM10_ESTRAT17, OM10_ADJIND18, OM10_ESTRAT18, OM10_ADJIND19, OM10_ESTRAT19, OM10_ADJIND20, OM10_ESTRAT20, OM10_APVIND1, OM10_APVIND2, OM10_APVIND3, OM10_APVIND4, OM10_APVIND5, OM10_USRARE030A, OM10_USRARE030B, OM10_USRARE030C, OM10_USRAMT01, OM10_USRAMT02, OM10_USRAMT03, OM10_USRINT01, OM10_USRDEC01, OM10_USRCNT01, OM10_USRDAT01)

SELECT OM10_RECSTA, OM10_TECPRCSTA1, OM10_CREDAT, OM10_CRETIM, OM10_CREUSR, OM10_UPDDAT, OM10_UPDTIM, OM10_UPDUSR, OM10_OUTNUM, 120, 1440, OM10_ADJCLCBAS, 129, OM10_FRQCOD, OM10_NXTRGEBIL, OM10_NXTRGEDSC, OM10_NXTRGECRD, 1120101, 1999999, OM10_ADJINAIND, OM10_USRARE004, OM10_VOLCTCIDR, OM10_CRYCODEST, OM10_ADJIND01, OM10_ESTRAT01, OM10_ADJIND02, OM10_ESTRAT02, OM10_ADJIND03, OM10_ESTRAT03, OM10_ADJIND04, OM10_ESTRAT04, OM10_ADJIND05, OM10_ESTRAT05, OM10_ADJIND06, OM10_ESTRAT06, OM10_ADJIND07, OM10_ESTRAT07, OM10_ADJIND08, OM10_ESTRAT08, OM10_ADJIND09, OM10_ESTRAT09, OM10_ADJIND10, OM10_ESTRAT10, OM10_ADJIND11, OM10_ESTRAT11, OM10_ADJIND12, OM10_ESTRAT12, OM10_ADJIND13, OM10_ESTRAT13, OM10_ADJIND14, OM10_ESTRAT14, OM10_ADJIND15, OM10_ESTRAT15, OM10_ADJIND16, OM10_ESTRAT16, OM10_ADJIND17, OM10_ESTRAT17, OM10_ADJIND18, OM10_ESTRAT18, OM10_ADJIND19, OM10_ESTRAT19, OM10_ADJIND20, OM10_ESTRAT20, OM10_APVIND1, OM10_APVIND2, OM10_APVIND3, OM10_APVIND4, OM10_APVIND5, OM10_USRARE030A, OM10_USRARE030B, OM10_USRARE030C, OM10_USRAMT01, OM10_USRAMT02, OM10_USRAMT03, OM10_USRINT01, OM10_USRDEC01, OM10_USRCNT01, OM10_USRDAT01

FROM GBAFLS.OM10

WHERE OM10_ADJLST = 4260

AND (OM10_OUTNUM NOT IN (SELECT OM10_OUTNUM FROM GBAFLS.OM10 WHERE OM10_ADJLST = '1440'));

Sadly, there's quite a bit I'm forced to do through raw SQL (such as the query above). Despite my affinity for CLI, and having primarily used console for close to 20 years, I do greatly appreciate the times I can use an application's interface to get a lot of work done in a timely manner.

clarity_64

@Aldryic is that a JDE table? I have that program on my machine, but I never use it for anything.

AuroraZ

@Joel said: I have a database that's over 210 tables, 2760 columns. Now I have had to document each and every single one of these and how it functions, what the values represent and how they affect the web app along with huge algorithms. Tell me, of what benefit am I going to get putting a command line up on a big screen in front of 3 developers? I'm damn curious.

For those of us that actually do need to make a living and be somewhat time efficient there's great apps out there such as Navicat. For the rest that want to relive their hey-day as a neanderthal I guess there's the command line for that!

WTH why did this suddenly become such a personal thing. I am sorry I did not say in my opinion the GUI has ruined a lot of things. It has for some of us. If I am neanderthal then I guess I am a neanderthal. Joel if you want an argument look elsewhere I have neither the time nor the motivation to argue with you or anyone else here.

I happen to like using it and happen to know from experience that most people coming to Linux/Unix platform need CLI experience. If you don't have the experience when your GUI falters where is your information? Do you load another GUI? Do you run to the developer and scream at him? When he tells you how to fix it or it does get fixed how is the fix applied usually? The CLI is an important tool and people need to learn about it. That is the last thing I shall say in this thread as it has now been derailed big time.