Remote Access to MySQL database

MySQL can tie accounts to specific hostnames and/or IP addresses.  This does not work so well when traveling or when the access locations are somewhat ad hoc.  While certificates are an option, it is easier to use SSH to tunnel the connection.  The normal localhost accounts then work remotely.

ssh -f -L 33306:localhost:3306 mysql.example.com sleep 300

MySQL listens on port 3306. We need to map a local port to the remote 3306. Since I already have a local MySQL server running I need a different port number. 33306 was picked as easy to remember and available. The sleep 300 simply holds the connection open for 5 minutes (300 seconds) giving me ample time to connect.

mysql -P 33306

should connect, but there is a pitfall. On Linux, a localhost connection is done through a socket file and _not_ through the network stack. If you are also running a local MySQL server, the -P (port) argument will be ignored and you will not connect to your remote MySQL server. What to do?

mysql -h 127.0.0.1 -P 33306

Now you are connecting to your localhost, but using the network stack. The port argument (-P 33306) is no longer ignored.

You really want better authentication than a password provides when opening a port to the Internet. SSH allows you to use Public Key Encryption to control access. The public key is stored on the server. Anyone trying to access the server _must_ have the corresponding private key to establish a connection.