Hacker HowTos: Direct Connection to a VM on my laptop from a cloud server

sundialsvcs · 09-29-2016, 03:07 PM

I have a 16-gigabyte database on a cloud machine, accessible through an OpenVPN tunnel, and a duplicate of that database on my laptop. The problem has always been: loading it. Until now, that required making a mysqldump file, FTP'ing it to the laptop, and loading the file.

Today I started wondering ... might it somehow be possible to connect to my laptop's database (on its VM), through the tunnel, from the cloud machine, without requiring any privileged commands? I decided to find out.

First, I looked at the particulars of the tunnel. ifconfig utun0 included a line that looked like 11.8.0.14 ---> 11.8.0.11, which, is it turns out, means that "my laptop" when connected through OpenVPN has an address of 11.8.0.14 on the virtual private subnet.

I tried to ping the address, and found that it worked. So, next, I tried to use the Swiss-Army-Knife tool, nc == netcat.

On my laptop, in the host (not the VM's) operating system, I ran: nc -l 1234.
Then, on the cloud, I tried to connect: nc 11.8.0.14 1234 ...
And it worked. What I typed on one console appeared on the other and vice versa.

But how to make a two-way connection? Somehow, I had to get these packets to port 3306 of my VM at 192.168.56.101 ... and back again.

Of course, one way to do it is with (probably, privileged) commands, on whatever-is your operating system, to set up "port forwarding." But, I wanted to do it "on the cheap," without issuing special commands.

This XiNote provided the answer. From a terminal window on my laptop host, I typed:

Code:

mkfifo backpipe
while true
do
  nc -G 9999 -l 1234 0<backpipe | nc -G 9999 192.168.56.101 3306 1>backpipe
done

With that command running, I could now on the cloud machine connect to IP-address 11.8.0.14 port 1234, and issue my MySQL commands directly. To actually load the data, I used the MySQL source filename; command on a previously-prepared mysqldump. The critical difference is: that gigantic dump-file never leaves the cloud server. (The commands will end when the connection is closed.)

An endless loop is used to immediately restart the command when it ends, since each run of the mysqldump command, e.g. to load separate table files that have been "split out" from the gigantic MySQL dump, requires a new connection. (A one-second "sleep" is inserted in the script on the remote side, specifically to give this loop plenty of time to restart.)

OpenVPN delivers the traffic securely to port #1234 which is being listened-to by the command given above. It is forwarded to port #3306 on my virtual machine ... which, of course, is the standard MySQL port. Returning traffic goes the opposite way.

Although obviously it still takes a long time to move 16 gigabytes of data from here to there, the process got the job done.

The -G 9999 option for nc specifies a 9999-second timeout (not sure if that's mandatory) so that the connection never drops even if a particular query takes a long time. (There are also "keep-alive" options, but they pick up OS defaults and I never had to use them.)

The --compress option on MySQL makes a big difference, because OpenVPN is relatively slow while MySQL query text is quite compressible. It reduces the operation time by about two-thirds.

Nevertheless: the overall result was an enormous time-saver over the previous process that I had to do.

Quote:

"Man! I feel like a hack-er!"

sundialsvcs · 10-04-2016, 10:40 AM

Here's another "freebie" ... a Perl script used to split the mysqldump output-files by table so that they can be loaded individually:

Code:

#!/usr/bin/perl

# Using Perl because it's installed on Ubuntu by default and don't really want PHP loaded here.

use strict;
use warnings;

my $dumpfile_name = "mydatabase.sql";
my $fh_dumpfile;

open($fh_dumpfile, "<", $dumpfile_name)
  or die("Can't open '$dumpfile_name'\n");

my $outfile_name;
my $fh_outfile = undef;
my $line;

while ($line = <$fh_dumpfile>) {
  if ($line =~ /Table structure for table \`(.*)\`/) {
    if (defined($fh_outfile)) {
      close($fh_outfile);
      $fh_outfile = undef;
    }
    print STDERR "Writing:  $1 ...\n";
    $outfile_name = "splits/$1";
    open($fh_outfile, ">", "$outfile_name")
      or die("Can't open output file  '$outfile_name'\n");

    # Cause zero-dates to be accepted when the dump is read back in.
    print $fh_outfile "SET SESSION sql_mode = '';\n";
  }
  else {
    if (defined($fh_outfile)) {
      print $fh_outfile $line;
    }
  }
}
close($fh_outfile);
print STDERR "Operation complete.\n"

The per-table files are put in the splits subdirectory.

Also interesting is the SET SESSION sql_mode command which is inserted at the front of each file in order to allow "zero dates" to be accepted.

The script which loads these files, one file at a time (shortest file to longest) is given in the next post.

sundialsvcs · 10-04-2016, 10:47 AM

And, finally, here is the script that does individual loads of the files in the splits subdirectory, moving each file to the moved subdirectory after it has been loaded. The files are loaded "shortest file first."

Code:

#!/bin/bash

read -s -p "Your laptop's root password:" PASSWORD


# The first 'mysql' comand is likely to fail so throw it away
mysql -uroot --compress -A -p$PASSWORD -h10.8.0.14 -P 1234 mydatabase </dev/null
sleep 2

for FILE in `ls -1rS splits/*`
do
  echo "Dumping $FILE ..."
  time mysql -uroot --compress -A -p$PASSWORD -h10.8.0.14 -P 1234 mydatabase <"$FILE"
  if [ $? -eq 0 ]
  then
    mv  $FILE moved
    if [ $? -eq 0 ]
    then
      echo "... Dumped $FILE"
    else
      echo "Could not move $FILE after dumping"
    fi
  else
    echo "Could not dump $FILE"
  fi
  sleep 2
done

So, where did the IP-address 10.8.0.14 come from? From here: (command run on my [OS/X ...] laptop)

Quote:

$ ifconfig
utun0: flags=8051<UP,POINTOPOINT,RUNNING,MULTICAST> mtu 1500
inet 10.8.0.14 --> 10.8.0.13 netmask 0xffffffff
.....^^^^^^^^^