Remote Access to MySQL database

MySQL can tie accounts to specific hostnames and/or IP addresses.  This does not work so well when traveling or when the access locations are somewhat ad hoc.  While certificates are an option, it is easier to use SSH to tunnel the connection.  The normal localhost accounts then work remotely.

ssh -f -L 33306:localhost:3306 mysql.example.com sleep 300

MySQL listens on port 3306. We need to map a local port to the remote 3306. Since I already have a local MySQL server running I need a different port number. 33306 was picked as easy to remember and available. The sleep 300 simply holds the connection open for 5 minutes (300 seconds) giving me ample time to connect.

mysql -P 33306

should connect, but there is a pitfall. On Linux, a localhost connection is done through a socket file and _not_ through the network stack. If you are also running a local MySQL server, the -P (port) argument will be ignored and you will not connect to your remote MySQL server. What to do?

mysql -h 127.0.0.1 -P 33306

Now you are connecting to your localhost, but using the network stack. The port argument (-P 33306) is no longer ignored.

You really want better authentication than a password provides when opening a port to the Internet. SSH allows you to use Public Key Encryption to control access. The public key is stored on the server. Anyone trying to access the server _must_ have the corresponding private key to establish a connection.

RAID 1 Drive Failure

One of my laptop's mirrored drives failed.  This should have been a non-event.  I expected the laptop to just keep working off the remaining drive.  This was the case until I tried to reboot.  The laptop reported no bootable drives!

My bootable USB sticks were not terribly helpful.  Both the Ubuntu and PuppyOS sticks were missing mdadm (multi drive administration) and lvm2 (logical volume manager).  They were unable to read and mount the good drive.  The Ubuntu Desktop CD was similarly unhelpful.  The Ubuntu 12.04.2 Server CD did the trick.  It includes the necessary mdadm and lvm2 packages and provides a rescue mode that works very smoothly.  (I shall be building an improved Ubuntu boot stick.)

The first step in getting back in operation was to write the MBR (master boot record) to the drive.  I must have failed to do that when I first installed the drive.  RAID1 with mdadm mirrors the file system partitions.  However the MBR is written to space outside the partitions.  Ubuntu 12.04 is using grub2 and this was my first opportunity to deal with grub2 booting problems.  The command for me was:

grub-setup -d /mnt/laptop/boot/grub /dev/sda

-d is used to specify the grub directory which must be accessible.  I had mounted my boot partition in /mnt/laptop/boot
/dev/sda is the name of the boot drive that is getting the new MBR.  When booting, the MBR needs to point grub to the grub directory.  And this time I remembered to repeat the command for /dev/sdb!

When I first tried to reboot with a single drive (degraded RAID), the laptop still failed to boot.  This appears to be a bug in the Ubuntu setup. mdadm was configured to allow a degraded RAID to boot. Once I installed /dev/sdb and gave it a few minutes to rebuild the boot partition, the laptop booted smoothly.

Things are much simpler when you have a working system.  Then the command to write an MBR is simply:

grub-install /dev/sda

grub will write an MBR for your current setup. This should be repeated for each of your potential boot drives. The command:

update-grub

will update the grub boot menu.

Don't go blindly using this advice on a dual boot system. There are other factors you need to worry about. In general, I think you are better off running any alternative OS virtualized. This lets you manage a simpler boot environment. If you need to run Windows, having Linux host it provides another level of security and management.