mysql_over_ssh_tunnel

This example shows how to connect a MySQL server over a ssh tunnel.

Put a private key to ~/.ssh and add setting to ~/.ssh/config like

Host mybastion
  Hostname     50.0.10.10
  User         your_username
  IdentityFile ~/.ssh/private_key

Host mysql.example.local
  User         your_username
  IdentityFile ~/.ssh/private_key
  ProxyCommand ssh mybastion -W %h:%p

and mount ~/.ssh on the host machine to /home/opam/.ssh on the guest machine:

docker run -it -p 8888:8888 -v $PWD:/notebooks -v ~/.ssh:/home/opam/.ssh akabe/iocaml-datascience



In [1]:

    
#require "core" ;;
#require "mysql" ;;



In [2]:

    
open Core ;;
open Core.Caml.Format ;;



In [3]:

    
let with_ssh_tunnel ~remote_port ~local_port ~bastion ~host ~f =
  let ssh_ctrl = sprintf "/tmp/ctrl_ssh.%d" local_port in
  let close_ssh () = assert(Unix.system (sprintf "ssh -S %s -O exit %s 2>/dev/null" ssh_ctrl bastion) = Ok ()) in
  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 *)
  match f () with
  | y -> close_ssh () ; y
  | exception exn -> close_ssh () ; raise exn









    Out[3]:




val with_ssh_tunnel :
  remote_port:int ->
  local_port:int -> bastion:string -> host:string -> f:(unit -> 'a) -> 'a =
  <fun>



In [4]:

    
let exec_sql ~key ~f query =
  with_ssh_tunnel
    ~remote_port:3306
    ~local_port:3316
    ~bastion:"mybastion"
    ~host:"mysql.example.local"
    ~f:(fun () ->
        let dbd = Mysql.quick_connect ~host:"127.0.0.1" ~port:3316 ~user:"myuser" ~password:"mypass" () in
        let rows = Mysql.exec dbd query |> Mysql.map_cols ~key ~f in
        Mysql.disconnect dbd ;
        rows)









    Out[4]:




val exec_sql :
  key:string array -> f:(string option array -> 'a) -> string -> 'a list =
  <fun>



In [5]:

    
exec_sql
  "SELECT 1 AS x"
  ~key:[|"x"|]
  ~f:(function [|Some x|] -> x | _ -> assert false)









    Out[5]:




- : string list = ["1"]



In [ ]: