Tarotoast’s Stuff

mysql -u root --default_character_set utf8 -p database < import.sql

沒有亂碼了 [aha

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.