Skip to main content

Simple and secure MySQL database management using SSH

I manage a number of MySQL databases on a daily basis.  While I feel more at home on the command-line and am used to manipulating MySQL databases using the standard mysql CLI tool- others may be more comfortable with using their GUI based tools.

I get many requests to open up the database for remote access.    For a test or development environment, this is OK - but I usually deny this request for production servers unless the connection being made will be secured in some way.  MySQL databases are not typically setup securely (it can be, with SSL) and the connection is sent over the wire in the clear; this means an attacker can assemble the packets and playback what was being transferred.  Most of the time, the connection to the MySQL database is made locally by applications running on the same server so this is of little concern.

But there is a simple and convenient solution to allowing access in insecure environments.  On Linux and Mac systems, you can do the following:

ssh -L localport:localhost:remoteport user@remotehost.com

The -L flag sets up local port forwarding from the local port to the remote port of the remote server.

Then configure your MySQL client to connect to the database locally on port 3306.  It works because port 3306 on the localhost is set up to forward to 3306 on the remote host. 

For example,  I will do "ssh -L 3306:localhost:3306 user@server.com" to setup a local port forward to the default MySQL port on my computer to the remote server's port 3306. Once the connection is established, it looks like any other SSH connection.

Of course, a requirement for this to work is that port 3306 on my local machine must not be occupied by any other running service.  If you are already running MySQL on your local machine on port 3306, it will fail because port 3306 is likely occupied.  If this is the case, you simply have to change the "localport" to something else.

Once my port forwarding session is established, I will use my "favorite" MySQL tool and connect to localhost on port 3306 using the credentials of the database I am connecting to.

Comments

Popular posts from this blog

Starting vmtoolsd as a service on Red Hat / CentOS

If you're like me;  you may manage virtual servers within vSphere.. Linux ones.  Red Hat ones, in particular, but this applies to CentOS as well.

A long, long time ago, in a galaxy far away, the vmware-tools setup procedure installed the necessary init script for you.  Lately though, for new images that I've been building - those init scripts aren't getting installed by the vmware tools installation package.  So they don't start up on reboot.  VMware based backups failed; clock were going askew, you name it.   I need that daemon started on reboot.

Without a SysV init script handy, I had to roll my own.. and this is the result;  despite having worked with Linux for well over 15 years, setting up SysV init scripts remain somewhat of a black art.  The ones on our older system were more complicated than we needed.  I was aiming for something simpler and portable.

With RHEL 7, the rumor mills are abuzz with systemd so that may change. But, I'm a practical system admini…

Attempting to use dd on Mac OSX? Resource Busy?

If you're trying to use the dd command to image a usb disk or another device and you're running into an error that looks like:

# dd: /dev/disk#: Resource busy

There is a simple solution.

Use OSX's Disk Utility and unmount any of the partitions you have mounted on that particular disk without unmounting or ejecting the disk itself.

Afterwards, attempt the dd command again.

Update:  

Attempting to use the umount utility in Mac OSX will result in a "Resource busy -- try 'diskutil unmount'".  The command-line equivalent would be:

# sudo diskutil unmount /Volumes/<disk in question>

E.g.  

# sudo diskutil unmount /Volumes/FLASHUSB

NetApp: Disabling snapshot for a volume on Data OnTAP

This is one of those things that isn't always very obvious. Sometimes, you need to disable snapshots for a volume.

Why in the world would someone want to disable a perfectly good feature of NetApp NAS Storage? Server/data migration for one. Disabling it temporarily will prevent the volume from filling up the snapshot directory. Maybe your volume doesn't need snapshots (data always changing, and can not be recoverable even with snapshots- such as oracle data dirs, in which case snapshots are useless).
You have to perform simple but important tasks. If your volume has a schedule, turn it off.

somefiler> snap sched rootvol Volume rootvol: 2 4 8@2,4,6,12,164
somefiler> snap sched rootvol 0 0 0 
somefiler> snap sched rootvol Volume rootvol: 0 0 0

That takes care of that. Next step is to disable the automatic snapshot option.

somefiler> vol options rootvol nosnap on

Now if you issue vol options rootvol You should see an option that says nosnap=on.

Lastly, you'l…