biotext.org.uk

This took a bit of head-scratching, so for future reference, or anyone else looking:

Say I am working outside the office firewall, on a machine called home, and I need to get into a MySQL server inside it. (Doesn’t have to be MySQL, but just for argument’s sake.)

There’s a machine called gateway I can ssh to and tunnel through, but for security reasons, the database server mysql doesn’t accept connections from gateway directly. But my desktop machine at the office (err… desktop) can connect to mysql.

One way round it is to ssh from home to gateway and forward a port on gateway to the ssh server on desktop:

home $ ssh -L 2222:desktop.example.org:22 -tAY gateway.example.org

And then in another terminal, ssh from home to desktop via this tunnel, forwarding another port on home to the incoming connections port on mysql (3306 in MySQL’s case usually):

home $ ssh -p 2222 -L 23306:mysql.example.org:3306 127.0.0.1

This time, you’re connecting to home port 2222, but because of the first command, this forwards you straight to desktop port 22.

Now both tunnels are in place, you can just connect to port 23306 on home and arrive by magic at mysql. In another terminal (or from your MySQL GUI):

home $ mysql -uUSER -pPASS -h127.0.0.1 -P23306

This example shows a tunnel-within-a-tunnel. There should be a way to make this work using end-to-end tunnelling instead, I tried but didn’t get anywhere. But that might be due to ssh server restrictions on our equivalent of gateway.

If none of this means anything, there’s an intro to ssh port forwarding here.

Note to self — so I don’t have to think about it again next time.

#!/bin/bash
export MYSQL_PWD=myrootpassword
BACKUP_DIR=/mnt/backups/mysql_backups
DATE=`date -I`
for dbname in `mysql -uroot --batch --skip-column-names -e "show databases;" | grep -v information_schema`; do
	/usr/local/bin/mysqldump -u root $dbname | gzip -9 > $BACKUP_DIR/$dbname-$DATE.sql.gz
done
 
# Clear backups older than 7 days
/usr/local/bin/find $BACKUP_DIR/*.sql.gz -mtime +7 -delete

Edited to include compression. Remember to include full paths if you’re running it from a crontab, just in case.

This is really a note-to-self but it might help other people too.

I have two network interfaces in my Ubuntu guest (on OS X 10.4 host), one NAT, one host-only.

Sometimes one of them doesn’t get an IP address — in ifconfig it has an inet6 address but not an inet address.

Today I tried

sudo dhclient eth1

and it worked — all it needed to do was re-query VirtualBox’s built-in DHCP server.

I would have thought this would do that automatically:

sudo /etc/init.d/networking restart

However that didn’t help… But dhclient did.

Another recurring problem is that when I move between different networks, DNS resolution gets screwed up. There’s a description of this with a suggested work around (using VBox’s DNS Proxy) here:

http://forums.virtualbox.org/viewtopic.php?f=1&t=28332

However, while this seemed to help with two of the networks I use frequently, it didn’t with a third, even after rebooting.

So, as suggested on one or two threads, I’ve removed the DNSProxy setting, and set the Ubuntu guest to use Google’s public DNS servers instead of those provided by the DHCP server for the NATted virtual NIC. (Blah blah blah.) Follow example here (more or less):

http://grumpymole.posterous.com/how-to-use-google-public-dns-or-opendns-in-ub

Seems to be working so far… But only tested on one of the offending networks, and without rebooting yet. Updates to come later.

… Nope, after upgrading to Snow Leopard and the latest VirtualBox (it’s now from Oracle — scary), random problems persist.

Sometimes I’ll lose guest-host connectivity in the middle of a session, without any sleep or other interruptions, but will still be able to see the internet from the NAT adaptor.

One possible solution here involving using OS X itself as the NAT router:

http://akutz.wordpress.com/2009/08/20/building-a-better-os-x-firewall-or-how-i-solved-the-nat-problem-for-virtualbox/

… but it’ll take work.

Or maybe a simpler version of the same approach: have a single host-only adaptor, use OS X’s internet sharing to share the AirPort card, and manually tell the vbox to use 192.168.56.1 as the default gateway:

http://forum.virtualbox.org/viewtopic.php?f=1&t=26544

If all else fails, try using a different ethernet hardware emulator.