MATLAB to MySQL through SSH tunnel

A “QuickNote” about how to connect MATLAB to a remote MySQL server through an SSH tunnel.
(updated 12/21/2011 to avoid port issues with Cisco VPN) 

Why:

The server only accepts encrypted connections and I couldn’t figure out an easy way to use SSL directly within the JDBC driver. Additional note: the server’s main role is the head node of a high-performance computing cluster and my goal with the laptop is to perform simple testing before spawning lots of parallel processes on the cluster. MySQL is used to store and retrieve data across these parallel processes without clobbering each other.

Prerequisites:

  • MATLAB with Database Toolbox (tested using R2011b pre-release)
  • An account on a server running MySQL (tested server running MySQL 5.1.58)
  • A command-line SSH tool. I am running Mac OSX (10.6), so this is included and accessed through Terminal

Setup:

  1. Download and extract the latest (platform independent) Connector/J driver from MySQL (tested using  5.1.17)
  2. Move the file mysql-connector-java-VERSION-bin.jar to a suitable permanent home. I put mine at /Library/Java/Extensions/
  3. Tell MATLAB where to look for this file by editing the MATLAB specific Java classpath.txt file. Easiest way to find this file is to open MATLAB and type

    edit classpath.txt

    This will bring up the file in the MATLAB editor. Ignore any comments about the file being autogenerator (so far so good). Somewhere in this file add the following line (modify with your path and version number)

    /Library/Java/Extensions/mysql-connector-java-5.1.17-bin.jar

Establishing the Connection

  1. Go to a command prompt (Terminal on OSX) and establish an SSH tunnel using something like the following:

    ssh -f user@server.edu -L 7800:server.edu:3306 -N

    Notes: -L is the key. It sets up an ssh tunnel that connects port 7800 on the localhost (laptop) to port 3306 (default MySQL port) on the server; -f runs ssh in the background; and -N tells ssh to not enter any commands once the tunnel is open. Replace user with your username on the server (probably different than your database user name), and server.edu (in both places) with the appropriate MySQL server name. There is nothing special about port 7800 so feel free to pick your favorite number;  but do use >1024 to avoid most critical ports, and check the tcp/udp port number list on wikipedia to make sure some other application doesn’t use it.

  2. Enter your password (server password not MySQL password). Now your SSH tunnel is setup and you can connect anything to localhost:7800 and have it look like you are connected the server’s MySQL service at server.edu:3306
  3. Now get MATLAB to use this connection using:

    conn = database('test', 'username', 'passwd', 'com.mysql.jdbc.Driver', 'jdbc:mysql://localhost:7800/test')

    in this case you use your MySQL username and password. ‘test’ specifies the database (schema) to access within the server, replace it in both places with the appropriate database to access.

  4.  Finally to get data from this connection first establish a cursor by issuing an SQL command:

    curs = exec(conn,'SELECT * FROM test_table');

    and then fetch the data from it (here we only get 10 results at a time)

    curs = fetch(curs, 10);

    and finally look at the data (as a cell array)

    curs.Data

    See the MATLAB database toolbox for more ways to use the connection such as writing data, altering the table structure, etc.

References (Thanks!):

http://www.revsys.com/writings/quicktips/ssh-tunnel.html

[old] http://www.mathworks.com/help/toolbox/database/gs/braiey2-1.html#braiey2-24

2 comments

  1. Thank you for these instructions, they were very helpful.

    fyi, I did have to change one aspect of the ssh call in terminal before it worked.

    rather than ‘7800:server.edu:3306’ I needed to use ‘7800:localhost:3306’ where the ‘localhost:3306’ refers to mysqld as would be referenced on the remote server.

Leave a Reply to ben Cancel reply

Your email address will not be published. Required fields are marked *