?

Log in

No account? Create an account

Previous Web | Next Web


OK, so, on my laptop, I've got a virtual Fedora machine running MySQL. I've just started a new project that uses MySQL as the database backend, so I've been 'playing' with it a bit...

Turns out that the tools I used to use in Windows for MySQL stuff (MySQL Administrator, MySQL Query Browser(?), etc) have all been superseded by an all-in-one gizmo, MySQL Workbench.

This is actually quite nice to use *^^* and I've got it installed on both the Fedora VM and on the Windows host. Bizarrely, it seems to be a lot slower to load on the Windows host OS than it is in the virtual Fedora guest OS... quite the opposite to most things I do virtualised!

Anyway, while I was setting up the Windows version, I ran into some problems getting Workbench to 'talk' to the MySQL daemon... originally I figured I'd just open the firewall ports for MySQL in Fedora, but the Workbench was a bit funny about letting my actually administer the box... kept telling me it couldn't find the local mysqld and insisting that, even though it was configured to connect to a remote machine, that it could not do anything...! so I gave up on it... and went looking for other ways to do it on Google...

Lo and behold, good old Putty came riding out of the Googlesphere to the rescue!

Following the instructions here (I'd forgotten you could do tunnels and stuff like that), I got the Windows mysql client to talk to the Fedora-hosted MySQL server... but still got some weirdness from the administrative part of Workbench... then I noticed that Workbench also supports its own internal SSH Tunnel connection... and ta-daa! using that worked... Windows MySQL Workbench happily talking to Fedora MySQL Server *^^*

But I do like to be able to just use the simple MySQL command-line client to do stuff... there's still a command-line-kid hiding out in me!

And this is where the "clever" stuff comes into play...

So, I have three different networks that I tend to use my machine on... my own home network (lets call it "Home"), and two different networks at the homes of a couple of friends who I stay with when I'm down south... (lets call them "FriendNet1" and "FriendNet2").

The networks that I've set up don't use the out-of-the-tin 192.168.1.n address range... Home is a combination of static ips and dhcp-allocated address because its an amalgam of my own network and my parents network... although it always did have a dhcp-pool, its been 'tweaked' since the merge happened. FriendNet1 is also one I set up, and originally it also had a couple of static machines on it... so I changed the base IP of the network so that anyone who came along with a 192.168.1.x static-configured machine wouldn't cause an IP conflict and might think to use DHCP instead...! As it happens, the static addresses on FriendNet1 are no-more, but I've left the configuration alone, cos its kinda useful for me!

So, to summarise this:
 HomeFriendNet1FriendNet2
Gateway192.168.3.254192.168.2.254192.168.1.254
DHCP Pool192.168.3.129 - 192.168.13.192192.168.2.1 - 192.168.2.100192.168.1.1 - 192.168.1.254
My Static Range192.168.3.1 - 192.168.13.128192.168.2.101 - 192.168.2.228192.168.1.101 - 192.168.1.228 1
My Windows
Laptop (static)
192.168.3.7192.168.2.107192.168.1.107
My Fedora
VMachine (static)
192.168.3.22192.168.2.122192.168.1.122


1 Not technically a static pool; these are addresses that are assumed to be unused because the number of devices on the network is unlikely to ever get close to the start of the "pool".

So I sat down and tried to work out a way to automate all the stuff that needs doing in order to connect the Windows command-line MySQL client to the Fedora MySQL server, even if its at one of three different IP Addresses... without needing to install any extra "gubbins" to actually do this... Google results for checking whether a specific port on a specific IP host is available for connections mention lots of different add-on programs that you can download and use... but I've got more than enough crap installed on my working machine without adding even more just to do the "little" jobs...!

This brings us to netsh... the built-in, but not particularly well-known Windows Network Shell... very useful little gizmo it is too... makes it possible to script all sorts of network-related changes... like changing IP Addresses (I'm thinking of writing a batch file that'll accept a network name and change the IP settings using netsh to match that network, rather than having to do it manually... cos I don't always manage to catch IP typos!)... IPv6 is supported too (not that I've got that running on any network at the mo; not got an IPv6 capable router!)... and apparently the Vista version lets you do stuff with Wireless too... not sure what, haven't gone Vista/7 still... but SSIDs are mentioned...!

Anyway, as well as all the "setting" stuff, netsh also has "diag" stuff... and thats where I ended up in my quest for a host-and-port pinger!!!

The big problem with netsh is that it isn't a Well Behaved DOS program... it doesn't use ErrorLevels to return any sense of what has happened... basically, everything it ever does is successful.... which, especially if you're pinging something to determine if it is there or not, is bloody useless!!!!

This is where that old Unixy friend grep would be very useful... but DOS (even the most recent NT-DOS derivatives) doesn't have a grep command... well, actually, thats not strictly true... DOS has has a find command for, well, forever... or atleast since MS DOS 3.30 anyway... (I didn't do DOS before that!). Admittedly, its not a patch on Real grep TM, but it does let you find out of a string of text is present in a bunch of text... and thats what we need here to work around the lack of exit statuses... we "read" the output text instead :)

The result is a rather neat little "Pure NT" (well, "Pure XP" I guess... as I don't know when netsh/"diag" mode was introduced) batch file:



myfedora.bat


@echo off

setlocal

rem Syntax: myfedora -n network [ mysql_user ]
rem
rem ToDo: It would be nice if this could have some auto-detection of the
rem network... it should be possible to get the local IP Address from
rem somewhere (another netsh thing? or ipconfig?) so it might be possible
rem
rem Of course, it may actually be easier to just generalise the script
rem to allow it to connect to arbitrary hosts with a few additional
rem command-line options

rem Defaults
rem In order for the user command-line value to be optional, there need to be a
rem legitimate default value for it... we'll use the logged-in user's username,
rem which is what Unix mysql client seems to default to
set MYUSER=%USERNAME%

set SSHPORT=22
set STEP=0

rem Process option $1
if "%1p"=="p" goto endCmdOpts
if "%1"=="-n" if not "%2p"=="p" set NETWORK=%2
if not "%1"=="-n" set MYUSER=%1

rem Process option $2
if "%2p"=="p" goto endCmdOpts
if "%2"=="-n" if not "%3p"=="p" set NETWORK=%3

rem Process option $3
if "%3p"=="p" goto endCmdOpts
if not "%NETWORK%p"=="p" set MYUSER=%3

:EndCmdOpts

rem Networks
rem ToDo: Make this attempt some auto-detection of networks...
if "%NETWORK%p"=="p" echo ! No network specified!
if "%NETWORK%p"=="p" goto end

if "%NETWORK%"=="Home" set IPHOST=192.168.3.22
if "%NETWORK%"=="FriendNet1" set IPHOST=192.168.2.122
if "%NETWORK%"=="FriendNet2" set IPHOST=192.168.1.122
if "%IPHOST%p"=="p" echo ! Invalid network specified!
if "%IPHOST%p"=="p" goto end

set MYTUNNELHOST=localhost
set MYTUNNELPORT=3306

echo Connecting %MYUSER%@Fedora...
rem echo * %IPHOST%@%NETWORK% (via %MYTUNNELHOST%:%MYTUNNELPORT%)

echo * Testing target host exists and SSH is available...
rem Use find as grep
rem Note: DOS's "blackhole" null-device only has one "l" character!
rem Don't be tempted to use "null" as per the Unixy /dev/null
netsh diag connect iphost %IPHOST% %SSHPORT% | find "Server" | find "[NONE]" > nul
if errorlevel 1 set STEP=1
if not "%STEP%"=="1" echo ! Unable to locate port %SSHPORT% on %IPHOST%.
if not "%STEP%"=="1" echo ! Is the target machine down?
if not "%STEP%"=="1" goto end

:STEP1
echo * Testing if SSH tunnel end point exists...
rem Use find as grep
rem Note: DOS's "blackhole" null-device only has one "l" character!
rem Don't be tempted to use "null" as per the Unixy /dev/null
netsh diag connect iphost %MYTUNNELHOST% %MYTUNNELPORT% | find "Server" | find "[NONE]" > nul

if errorlevel 1 set STEP=2
if not "%STEP%"=="2" echo ! Cannot locate SSH tunnel end point at %MYTUNNELHOST%:%MYTUNNELPORT%
if "%STEP%"=="2" goto STEP2

echo ? Do you wish to attempt to launch Putty and establish an SSH Tunnel?
echo If you do, Putty will be started for you. Please enter your credentials then
echo return to this window to continue.
set /p PUTTY=">> Establish Putty SSH Tunnel? [yn] "
if not "%PUTTY%p"=="yp" if not "%PUTTY%p"=="Yp" echo ! Giving up.
if not "%PUTTY%p"=="yp" if not "%PUTTY%p"=="Yp" goto end

rem NOTE: For this to work, there needs Putty to have a saved configuration
rem (with a 127.0.0.1:3306 SSH Tunnel) with a name of the form
rem Fedora@NetworkName, where the NetworkName is a valid value for the
rem -n network option of this script
start /I putty.exe -load Fedora@%NETWORK%
echo i Waiting for user to establish SSH Tunnel and return...
pause
goto STEP1

:STEP2
rem Fire up mysql client.
rem -h is the host to connect to
rem -P is the port on that host
rem -u specifies the username to use
rem -p specifies that the username requires a password

mysql -h %MYTUNNELHOST% -P %MYTUNNELPORT% -u %MYUSER% -p

:end
endlocal


Anyway, hopefully it might be useful to somebody somewhere!