Tarotoast’s Stuff

最近剛搬到 Slicehost，有個大問題就是 SSH 沒放多久就自動斷線
明明隔壁視窗的 Bluehost 都放了兩天 idle 都沒斷，為啥 Slicehost 才半個小時就葛掉了
稍微找了一下 (其實我丟了一張 ticket :$)，發現其實只要再 sshd 裡面修改一下就好了:
編輯 /etc/ssh/sshd_config
KeepAlive yes
ClientAliveInterval 60

還有因為我是從 Ubuntu 下面 GTerm 連過去的，所以其實還可以這樣改:
編輯 ~/.ssh/config
ServerAliveInterval 60

然後很奇妙的是，不再斷線了 XD
Bye bye SSH disconnecting every so fu*ing often :)

Linking libraries from its default location

ln -s /usr/include /opt/include
ln -s /usr/lib64 /opt/lib

Now configuring with new location:

./configure --prefix=/usr/local --enable-force-cgi-redirect --enable-fastcgi --with-libxml --with-zlib --with-bz2 --with-curl  --with-gd --enable-mbstring --with-mysql --with-jpeg-dir=/opt --with-png-dir=/opt --with-mysql=/opt

On my 1&1 VPS II server, it comes with only 256MB of dedicated memory, which is pathetic. If I run a separate mysqld, it’s going to take 1xxMB away from this super limit memory pool. It makes sense to me to make use of MySQL databases on another shared hosting account. BlueHost and HostMonster both comes with firewall enabled by default for their MySQL servers. Going about white listing server’s IP can be done via:

1. Launching remote firefox session, which takes your patient and the hassle of all those x window configs.
2. Setup VPN on the server, and use server’s IP to to whitelist itself.

But the connection between the VPS server and Shared Host’s MySQL database is insecure. It’s natural to come to this conclusion as the best option: ssh tunnel.

Assuming you have shell access to both VPS and Shared Host, and both are running OpsnSSH.

Setup Public Key Authentication

1. Login to VPS with account you want to create the tunnel

2. cd ~/.ssh

   (create it if not exist)

3. ssh-keygen -t dsa

4. Securely transfer id_dsa.pub to the Shared Hosting’s home directly, something like this:

   scp id_dsa.pub sharedhost:/home/username/.ssh/server_id_psa.pub

5. Login to Shared Host

6. cd ~/.ssh
   cat server_id_psa.pub >> authorized_keys

7. Switch back to VPS, try logging into Shared Host using public key authentication:

   ssh username@sharedhost

   If everything is setup properly, no password is prompted.

If in doubt, [this] is a good place to consult about Public Key Authentication.

Setting up a host name

When MySQL is connecting to localhost, it will try to use socket, regardless of the efforts trying to tell it the service is actually not running on localhost, but rather a tunnel to a remote host. To overcome this issue, create an entry in /etc/hosts:

127.0.0.1 localhost mysqlhost

This points the host name ‘mysqlhost’ to 127.0.0.1 which essentially is also localhost, but mysql is too dumb to understand.

Create the tunnel

1. First, stop the local mysqld. As root:

   service stop mysqld

2. Login to VPS with username previously configured with public key authentication to Shared Host
3. create tunnel:

   ssh -f username@sharedhost -N -L 3306:localhost:3306

   This creates a ssh tunnel in background and forward connection to port 3306 on localhost to port 3306 on sharedhost.

4. Make change in php to reflect the database names, users, password on Shared Host, and most importantly, instead of localhost, use mysqlhost in configuration files.

Automate the process

What if the ssh tunnel died? What if the VPS restarts? How to make sure the connection stays live? The answer is to use autossh.

1. As root:

   yum install autossh

2. Edit /etc/rc.d/rc.local, add this line at the bottom:

   su VPSusername -c 'autossh -M 5307 -f SHAREDHOSTusername@sharedhost -N -L 3306:*:3306' &

   This tells the server to execute the command in ‘ ‘ as VPSusername and put the process into background at startup. autossh will use port 5307 to monitor the ssh tunnel to sharedhost, and reconnect if it drops. Also make sure to disable the local MySQL daemon from starting automatically, or the tunnel cannot be created.

Server

1. yum install openvpn
2. cp -R /usr/share/openvpn/easy-rsa /etc/openvpn
3. cd /etc/openvpn/easy-rsa
4. vim vars:

   # easy-rsa parameter settings
   # NOTE: If you installed from an RPM,
   # don't edit this file in place in
   # /usr/share/openvpn/easy-rsa --
   # instead, you should copy the whole
   # easy-rsa directory to another location
   # (such as /etc/openvpn) so that your
   # edits will not be wiped out by a future
   # OpenVPN package upgrade.
   # This variable should point to
   # the top level of the easy-rsa
   # tree.
   
   export D=`pwd`
   
   # This variable should point to
   # the openssl.cnf file included
   # with easy-rsa.
   
   export KEY_CONFIG=$D/openssl.cnf
   
   # Edit this variable to point to
   # your soon-to-be-created key
   # directory.
   #
   # WARNING: clean-all will do
   # a rm -rf on this directory
   # so make sure you define
   # it correctly!
   
   export KEY_DIR="/etc/openvpn/keys"
   
   # Issue rm -rf warning
   
   echo NOTE: when you run ./clean-all, I will be doing a rm -rf on $KEY_DIR
   
   # Increase this to 2048 if you
   # are paranoid.  This will slow
   # down TLS negotiation performance
   # as well as the one-time DH parms
   # generation process.
   
   export KEY_SIZE=1024
   
   # These are the default values for fields
   # which will be placed in the certificate.
   # Don't leave any of these fields blank.
   
   export KEY_COUNTRY=US
   export KEY_PROVINCE=CA
   export KEY_CITY=SANDIEGO
   export KEY_ORG="TAROTOAST.COM"
   export KEY_EMAIL="chang.peter@gmail.com"

5. chmod 700 *
6. mkdir /etc/openvpn/keys
7. make sure in Bash shell (still at /etc/openvpn/easy-rsa directory)
8. ./vars
9. ./clean-all
10. ./build-ca
    Enter ROOTCA for common name
11. ./build-key-server server
    Enter ROOT for common name
12. ./build-key [computername] <– repeat for # of computers want to allow connect
    Enter [computername] as common name
    EX: ./build-key AMD64
    EX: ./build-key X40
13. ./build-dh
14. vim /etc/openvpn/server.conf:

    port 1194
    proto tcp
    dev tun
    ca keys/ca.crt
    cert keys/server.crt
    key keys/server.key
    dh keys/dh1024.pem
    server 10.11.12.0 255.255.255.0
    client-config-dir ccd
    push "dhcp-option DNS SERVERIP"
    push "dhcp-option DNS SERVERDNS"
    ifconfig-pool-persist ipp.txt
    push "redirect-gateway"
    keepalive 10 120
    comp-lzo
    persist-key
    persist-tun
    status server-tcp.log
    verb 3

15. Create log files: touch /etc/openvpn/server-tcp.log /etc/openvpn/ipp.txt
16. vim /etc/init.d/openvpn:
    Uncomment line 114

    /sbin/modprobe tun >/dev/null 2>&1

    Insert after line 115

    iptables -t nat -A POSTROUTING -s 10.11.12.3 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.4 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.5 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.6 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.7 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.8 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.9 -j SNAT --to SERVERIP
    iptables -t nat -A POSTROUTING -s 10.11.12.10 -j SNAT --to SERVERIP

17. service openvpn start

Client (Windows Vista with UAC on)

1. Download and install OpenVPN Gui 1.0.3 with OpenVPN 2.0.9 [Here]
2. Scary warning will pop up telling you the driver will not work, install it anyway. OpenVPN’s developers fixed it already.
3. Make a shortcut on desktop to GUI executable “C:\Program Files\OpenVPN\bin\openvpn-gui-1.0.3.exe”
4. Use WinSCP to download these files onto the client computer:
   /etc/openvpn/keys/ca.crt
   /etc/openvpn/keys/AMD64.key
   /etc/openvpn/keys/AMD64.crt
5. Put above files along with this VPNServer.ovpn in C:\Program Files\OpenVPN\config
   VPNServer.ovpn

   client
   dev tun
   proto tcp
   remote SERVERIP 1194
   resolv-retry infinite
   nobind
   persist-key
   persist-tun
   ca ca.crt
   cert AMD64.crt
   key AMD64.key
   ns-cert-type server
   push "dhcp-option DNS SERVERIP"
   push "dhcp-option DNS SERVERDNSIP"
   comp-lzo
   verb 3
   route-method exe
   route-delay 2
   

6. Right click on the shortcut to openvpn-gui-1.0.3.exe, select Run As Administrator
7. On the taskbar, right click the openvpn gui icon, select connect

DONE [aha

The above mostly follows [this thread] except some minor changes in client side config.

• byebye SK2, hello Akismet
• byebye Counterize, hello Counterize II
• byebye localhost mysqld, hello BH’s mysqld (via ssh tunnel)
• byebye K2, hello other wp themes