BOFH Hunter

Abusing MySQL

One of the larger complaints about mysql for me has always been the hoops required to find out basic information. I want to check my GRANTS periodically to check permissions. I want backups to not take forever, and I want to be able to use find. Turns out, I can have everything I want and all I have to do is use maatkit.

I discovered this a few nights ago while reading through Jeremy Cole’s blog and noticed some of the functionality he was talking about. While I don’t fully trust their repositories (I prefer to stick with the distro mysql releases), I was very interested in the maatkit functionality.

After talking with Karanbir and waiting patiently for a few minutes, I had a nice shiny build of the maatkit MySQL toolkit in the testing repository to play with. It works exactly as advertised, and I could not be happier with it.

The majority of the maatkit tools are geared toward mysql replicated environments, so there’s a load of master/slave tracking capability including some new ways to track latency, lag, and more. This is all neat, but it’s not something I’m overly interested in just yet, because I don’t have any replication problems.

What does interesting me is this: mk-find

mk-find is essentially a perl implementation of find for mysql. I can now search for and sort tables by size, row, create/modify time, and perform actions based on what returns with –exec.

Another tool within maatkit which caught my eye was mk-parallel-dump, which while exceptionally long to type in (yes, I’m lazy) is quite a good tool for speeding up backups. Instead of simply dumping the entire db all at once into a single file and waiting for it to spool through huge amounts of information, I can pass this utility a directory, run a thread per table, and cut my backup time by a significant percentage of time. Simply run: mk-parallel-dump –tab –basedir /path/to/backups/ and you’re off, but if you want to get even more hi-tech, they include some sample scripts for scheduled backups.

If you’re looking for a way to make your mysql management tasks easier, have a look at maatkit, or just go get it from the CentOS-Testing repository and see how it can simplify your life.

CentOS 5 and aide

In recent days, the subject of intrusion detection systems for centos has come up. To cover this and hopefully help some folks out, I’ve decided to do a brief writeup of Aide, the IDS which comes with CentOS. Please don’t confuse this with SELinux. SELinux is a Mandatory Access Control style permissioning system. SELinux stops people from getting into your system via protected applications. Aide lets you know if they actually get beyond SELinux and onto your system.

Installing Aide
yum install aide
What? You expected it to be harder? Now that we have aide installed, we need to configure it. The default config file should be okay for most folks who haven’t relocated things on the distro too much. Double check to make sure that all the directories you want to scan are listed. If you want to fine-tune the aide config, then you’ll need to edit /etc/aide.conf.

Initializing Aide’s Records

The next thing we need to do is create the initial aide database. For this, you need to run the following command:
# /usr/sbin/aide --init

This will take a little bit of time to run, and you’ll have some disk churn for minute or two while aide investigates your system and creates a baseline. Once this is done, we’re going to run an initial query of the system, just to make sure that everything’s working properly. To do this, run the command below:
# cp /var/lib/aide/aide.db.new.gz /var/lib/aide/aide.db.gz
# /usr/sbin/aide --check


This copies the initial database to the current database, then checks them against each other. In theory you should not have any differences. If you do, investigate them. As we’re still setting this up, they’re likely to be mundane .viminfo files or something similar. Keep in mind that when you update applications via ‘yum update’ that you may see aide go a bit nuts, just as tripwire or others would. You’re replacing files on your system when you update, and this is exactly what aide is designed to warn you about. In a perfect world, you should get some output like the text below:

# aide --check
AIDE, version 0.13.1
### All files match AIDE database. Looks okay!


Once we’re satisfied that aide is working as we expect, it’s time to set up a periodic check of the system. Only you can determine what’s often enough for your servers. I personally run aide as weekly cron, by creating a file in /etc/cron.weekly/ called aide.cron, with the following contents:


#!/bin/bash
/usr/sbin/aide --check | /bin/mail -s "Weekly Aide Data" email@host.com

This runs my check once a week. That’s pretty much it to setting up aide. If you want to see more options for aide, please check out the documentation in /usr/share/doc/aide-*/

Update:

So it seems that by default, aide requires selinux to be enabled, or at least permissive so that it can record the selinux contexts of the files it watches. If for some reason you really, truly want to have selinux disabled, but you still want aide to watch the system, use the config file below. It is identical to the default scan, but with the selinux bits removed.

selinux-free.aide.conf

ssh oneliners

Some handy commands to remember when you really need to abuse ssh.

ssh -X remotehost # yawn. X forwarding through ssh.

ssh -Y remotehost # trusted X forwarding through ssh. Still yawn, let’s do something fun.

ssh -D2222 remotehost # This is okay. This command sets up a SOCKS proxy on port 2222 which can be used with firefox (and Internet Explorer if you really hate yourself) to avoid office internet filters…. not that I condone such anti-social behavior.

ssh -L 3306:database.example.net:3306 # okay, now we’re getting interesting. This generates an ssh tunnel between your machine and the remote box on port 3306. This works for connecting to remote mysql instances when firewalls would ordinarily interfere. Simply point your mysql client to localhost:3306 and you’re off and running. This can also be applied to other applications as well. A slight modification, and you get the string below:

ssh -L3389:remote.win2k3.server:3389 user@remote.linux.box # This command is a variation on the command above, allowing us to connect to those unsightly windows machines via rdesktop for remote administration. Best of all, we do this without opening up the remote desktop ports to the outside world. Remember folks, that windows code is expensive, you have to keep your precious little snowflakes safe after all.

With the commands above, you can alternatively add -f , if you want ssh to go to the background after the authentication portion is handled. Otherwise it’ll just leave you sitting at a remote shell prompt. If you wanted to add a built-in self-destruct, you could add ’sleep 30′ after the ssh command strings above. This tells ssh to exit after 30 seconds if nothing has made a connection via the tunnel created.

ssh -nNT -R 2222:localbox:2222 remotebox # This command lets you create a reverse ssh tunnel, so that if you connect to remotebox:2222, you’ll be connected to the local machine on port 2222 also. This is useful when you really want to go home at night, but your boss demands you keep working. This way, you each get what you want, and you can avoid the firewall your office employs to keep folks from remotely connecting to…well, if you’re using this, you don’t really care.

Crap, what if I already have an ssh session open, but I forgot to create the tunnel? Not to worry, there are escape keys to rescue you. Operating a little like screen, ~C will open an ssh command prompt so that you can start or stop tunnels as needed. It’ll look a bit like the one below:

[jperrin@server ~]$
ssh> help
Commands:
-Lport:host:hostport Request local forward
-Rport:host:hostport Request remote forward
-KRhostport Cancel remote forward


As always, we’re just scratching the surface of what ssh can do, so if you want more information, fire up ‘man ssh’ in your favorite terminal and sit down for a good read.

Customize SSH

SSH is a staple of *nix environments, however most people don’t take the time to customize it in order to take advantage of the deeper features. If you have more than a few machines to log into or administer, it’s pretty likely that you’ve also got a few usernames to go along with them. By creating a personal ssh config file, you can shave a few seconds and a few keystrokes off your logins. You can even create profiles for several user accounts on the same machine if you have specific task based logins.

To get started with your own personal config file, create and open ~/.ssh/config in your favorite text editor. The easiest way to organize this file is to break it up into host based sections, similar to the example below.

Host SessionName # Short Name you can use to start a session
HostName server.example.com # FQDN or ip of the server
User myuser # Unprivileged username for this session

Host Session2
HostName server.example.com
User myadminuser #this is a 2nd user with sudo access to the same server
IdentityFile id_rsa

Host server2
HostName server2.example.com
User yetanother # yet another user on another host.
IdentifyFile second_rsa_key
Compression yes #server has limited bandwidth, so enable compression

Host backup
HostName backups.example.com
User backupuser # backup user
BindAddress 192.168.1.2 #Connect from this ip address.
Port 2222 # Use this port instead of 22.

As you can see from the example above, we change ssh keys so you don’t have to use the same one, ssh usernames to eliminate the @, and the address ssh connects from since we have multiple IP addresses on this system. This isn’t really even scratching the surface of what you can do with ssh, so if you have even more customizations in mind, have a read through the ssh_config manpage in your favorite terminal.

death to the tilde

Well, for the past 2 hours, I’ve been fighting with mod_rewrite to get rid of the ~ character that apache shoves into a url for the UserDir directive by default. Since having a url like http://example.com/username/foo.html is far neater than http://example.com/~username/foo.html, and mostly because hughesjr has been after me to fix it, I finally have a solution.

So, for those of you thinking “uh, there’s an example for doing this right in the httpd docs”… let me save you the trouble. It doesn’t work. the instructions at http://httpd.apache.org/docs/2.2/rewrite/rewrite_guide.html cause the url to be rewritten properly, but then promptly 404’s because it looks in /var/www/html/u/username/ for the files. Afer digging around in google, and working with some friendly folks in #apache (yes they do exist) we have a solution:

RewriteEngine On
#RewriteLog logs/rewrite.log # Uncomment for rewrite logging
#RewriteLogLevel 3 # uncomment for verbose logging
RewriteCond %{REQUEST_URI} ^/([^/]+)
RewriteCond /home/%1 -d
RewriteRule ^/([^/]+)(.*) /home/$1/public_html/$2


Take that bit of code, and drop it into /etc/httpd/conf.d/homedir.conf or wherever else you’d like in your httpd configs, and reload apache. From there you’ll be able to use shorter, sexier UserDir urls for your user’s webpages.