examples/mysql_over_ssh_tunnel.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# mysql_over_ssh_tunnel\n",
    "\n",
    "This example shows how to connect a MySQL server over a ssh tunnel.\n",
    "\n",
    "Put a private key to `~/.ssh` and add setting to `~/.ssh/config` like\n",
    "\n",
    "```\n",
    "Host mybastion\n",
    "  Hostname     50.0.10.10\n",
    "  User         your_username\n",
    "  IdentityFile ~/.ssh/private_key\n",
    "\n",
    "Host mysql.example.local\n",
    "  User         your_username\n",
    "  IdentityFile ~/.ssh/private_key\n",
    "  ProxyCommand ssh mybastion -W %h:%p\n",
    "```\n",
    "\n",
    "and mount `~/.ssh` on the host machine to `/home/opam/.ssh` on the guest machine:\n",
    "\n",
    "```sh\n",
    "docker run -it -p 8888:8888 -v $PWD:/notebooks -v ~/.ssh:/home/opam/.ssh akabe/iocaml-datascience\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#require \"core\" ;;\n",
    "#require \"mysql\" ;;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "open Core ;;\n",
    "open Core.Caml.Format ;;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"color:slategray;max-height:100px;overflow:hidden\" \n",
       "onclick=\"\n",
       "if (this.style.maxHeight === 'none') \n",
       "    this.style.maxHeight = '100px';\n",
       "else\n",
       "    this.style.maxHeight = 'none'; \n",
       "\">val with_ssh_tunnel :\n",
       "  remote_port:int -&gt;\n",
       "  local_port:int -&gt; bastion:string -&gt; host:string -&gt; f:(unit -&gt; 'a) -&gt; 'a =\n",
       "  &lt;fun&gt;\n",
       "</pre>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "let with_ssh_tunnel ~remote_port ~local_port ~bastion ~host ~f =\n",
    "  let ssh_ctrl = sprintf \"/tmp/ctrl_ssh.%d\" local_port in\n",
    "  let close_ssh () = assert(Unix.system (sprintf \"ssh -S %s -O exit %s 2>/dev/null\" ssh_ctrl bastion) = Ok ()) in\n",
    "  assert(Unix.system (sprintf \"ssh -o ExitOnForwardFailure=yes -MS %s -fNL %d:%s:%d %s\" ssh_ctrl local_port host remote_port bastion) = Ok ()) ; (* open ssh tunnel *)\n",
    "  match f () with\n",
    "  | y -> close_ssh () ; y\n",
    "  | exception exn -> close_ssh () ; raise exn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"color:slategray;max-height:100px;overflow:hidden\" \n",
       "onclick=\"\n",
       "if (this.style.maxHeight === 'none') \n",
       "    this.style.maxHeight = '100px';\n",
       "else\n",
       "    this.style.maxHeight = 'none'; \n",
       "\">val exec_sql :\n",
       "  key:string array -&gt; f:(string option array -&gt; 'a) -&gt; string -&gt; 'a list =\n",
       "  &lt;fun&gt;\n",
       "</pre>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "let exec_sql ~key ~f query =\n",
    "  with_ssh_tunnel\n",
    "    ~remote_port:3306\n",
    "    ~local_port:3316\n",
    "    ~bastion:\"mybastion\"\n",
    "    ~host:\"mysql.example.local\"\n",
    "    ~f:(fun () ->\n",
    "        let dbd = Mysql.quick_connect ~host:\"127.0.0.1\" ~port:3316 ~user:\"myuser\" ~password:\"mypass\" () in\n",
    "        let rows = Mysql.exec dbd query |> Mysql.map_cols ~key ~f in\n",
    "        Mysql.disconnect dbd ;\n",
    "        rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre style=\"color:slategray;max-height:100px;overflow:hidden\" \n",
       "onclick=\"\n",
       "if (this.style.maxHeight === 'none') \n",
       "    this.style.maxHeight = '100px';\n",
       "else\n",
       "    this.style.maxHeight = 'none'; \n",
       "\">- : string list = [&quot;1&quot;]\n",
       "</pre>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exec_sql\n",
    "  \"SELECT 1 AS x\"\n",
    "  ~key:[|\"x\"|]\n",
    "  ~f:(function [|Some x|] -> x | _ -> assert false)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "OCaml 4.04.1",
   "language": "ocaml",
   "name": "iocaml-kernel"
  },
  "language_info": {
   "name": "ocaml",
   "version": "4.1.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}