Remote MySQL Administration for 1and1

10 Replies

If you use 1and1.com for web hosting, then you are familiar with how much of a pain their MySQL management can be. In order to access your databases on 1and1, you must first login through the 1and1 customer portal, then navigate to the MySQL databases section, find the database you want to work with, and click on the PHPMyAdmin link. It’s only a few steps, but between pages loads and a slow internet connection this can take up to a few minutes, and even then PHPMyAdmin can be clunky especially if you are used to using a desktop MySQL management tool such as MySQL Workbench or Sequel Pro (Mac). Although some hosting providers allow you to connect to your MySQL instances remotely, 1and1 does not support this (at least not by default).

What I am going to show you in this article is how to proxy a connection to your MySQL instance on 1and1′s servers so that you can manage your databases using a full-featured application like MySQL Workbench or Sequel Pro.

Prerequisites

So far I have tested this out on a 1and1 Managed Linux server and a 1and1 Shared Linux server. For what it’s worth, the 1and1 servers that I have access to are running Debian 4.0 (Etch).

As for client support, this solution is rather flexible, and you can get things to work cross-platform (Linux/Mac/Windows). Of course, since the method utilizes standard Linux utilities, getting the client working on Windows is a bit clumsy.

Setup Public Key Authentication

A crucial element in getting this working is to setup SSH public keys for authentication. This is really simple and takes only a minute. Simply run the following commands on your local machine (not on the 1and1 server):

  1. Run the ssh-keygen command
    1. Save the key files to .ssh/1and1_rsa
    2. Enter a passphrase for the private key (this is important for security)
  2. Run ssh-copy-id -i .ssh/1and1_rsa user@your-1and1-hostname
  3. Run ssh-add .ssh/1and1_rsa. This will add the private key to the SSH agent so that you will not be prompted for the passphrase over-and-over.

The goal here, is that our SSH client won’t ask us for our password every time we want to login to the server. For more information on setting up public key authentication for SSH, see Setting up public key authentication over SSH.

Install Socat

You will need to install socat on both your local machine and the 1and1 server. If you are using Linux, you can easily install socat via your distributions software repository (apt, yum, etc.), for a Mac you can install socat via Macports.

Since 1and1 uses the Debian Linux distribution, all we need to do is download and extract the socat binaries on the server. To keep my webroot clean, I place everything inside a subfolder which is protected by .htaccess.

user@localhost:~$ ssh user@your-1and1-hostname
(uiserver):user:~ > mkdir -p ~/opt/src
(uiserver):user:~ > echo "Deny from all" > ~/opt/.htaccess
(uiserver):user:~ > cd ~/opt/src && wget http://archive.debian.org/debian/pool/main/s/socat/socat_1.4.3.1-1_i386.deb
(uiserver):user:~/opt/src > dpkg -x socat_1.4.3.1-1_i386.deb ~/opt
(uiserver):user:~/opt/src > cd && echo 'export PATH=$HOME/opt/bin:$HOME/opt/usr/bin:$PATH' >> .bash_profile
(uiserver):user:~ > . .bash_profile
(uiserver):user:~ > socat -V

If all went well, then you should see the socat version information:

socat by Gerhard Rieger - see www.dest-unreach.org
socat version 1.4.3.1 on Apr 13 2006 15:56:26
   running on Linux version #1 SMP Tue Nov 30 18:27:29 CET 2010, release 2.6.28.8-20101130b-iscsi-ntacker-fasync-mremap-amd-sec6-grsec, machine i686
features:
  #define WITH_STDIO 1
  #define WITH_FDNUM 1
  #define WITH_FILE 1
...

Tunneling MySQL Traffic

At this point, we have everything in place for allowing MySQL traffic to be tunneled through an SSH connection. All we need to do is glue everything together using socat. The following commands will establish a listening socket on your local computer that will act as a proxy to your 1and1 database (don’t forget to replace the parameters in bold with your specific information). To test the proxy, try connecting via the MySQL command line: mysql -u 1and1-db-user -S /tmp/1and1-mysql.sock -p

1and1 Shared Hosting (MySQL on a separate server)

socat unix-l:/tmp/1and1-mysql.sock,fork exec:'ssh user@your-1and1-hostname "~/opt/usr/bin/socat stdio tcp:1and1-mysql-server:3306"'

1and1 Managed Hosting (MySQL on the same server)

MySQL 4.x:

socat unix-l:/tmp/1and1-mysql.sock,fork exec:'ssh user@your-1and1-hostname "~/opt/usr/bin/socat stdio unix:/tmp/mysql.sock"'

MySQL 5.x:

socat unix-l:/tmp/1and1-mysql.sock,fork exec:'ssh user@your-1and1-hostname "~/opt/usr/bin/socat stdio unix:/tmp/mysql5.sock"'

References

Tunneling the qemu or kvm vnc unix socket via ssh - http://www.nico.schottelius.org/blog/tunneling-qemu-kvm-unix-socket-via-ssh/