MySQL has a kill command which lets you kill a specific query or connection. This is useful it situations where you’ve accidentally issued a really complex query, or an application created a persistent connection and forgot to close it.

Killing lots of connections can be tedious though. Say an application has created lots of persistent connections and not closed any of them, you’d have to go through the following steps:


mysql> SHOW FULL PROCESSLIST; /* shows all connections */
mysql> KILL <process_id>; /* for EVERY process you want to kill */


To make this process much simpler I wrote a little bash script that will kill all connections for a given MySQL user.

#!/bin/bash
# Kill all MySQL connections for a given user
# Ben Dowling - Apr 2009
# www.coderholic.com

user="root" # MySQL super user
pass="" # Above user's password
kill_user="myapp" # User who you want to kill all the connections for

# Get the connection IDs for all connections from the user $kill_user
processList=$(mysql -u$user -p$pass -s -e "SHOW FULL PROCESSLIST" | grep $kill_user | awk '{print $1}')
# Loop through all the connection IDs and kill the connection
for id in $processList
do
       result=$(mysql -u$user -p$pass -s -e "KILL $id")
       echo $result
done

The MySQL superuser account name and password, along with the user you want to kill all the connection for, are embedded within the script. It would be quite simple to pass these in as command line arguments if you wanted to though.

After years of using Linux I’ve built up a collection of customised config scripts for things like bash, vim, and screen. Having everything setup just how I want it is great, until I need to start working on a new computer. A simple solution is to copy all my config files over to the new computer, but it means I need to re-copy all the config files if I make any changes to them. This isn’t too much of a problem if I’m only working on two computers, but it becomes a real pain with more.

The solution I’ve come up with (and I’m sure others have too) is to put all my config files into into a Git repository on GitHub. Any changes to the config files can be committed to the repository, and than a quick update operation other machines keeps all the config files in sync. In this post I’ll describe how to set it all up.

Adding your config files to GitHub

You’ll obviously need to install Git. On any debian based system this is as simple as:

   sudo aptitude install git-core

You’ll also nee to Signup for a free GitHub account, and create a new repository. GitHub uses public-key cryptography and SSH for authentication, so you’ll need to create a SSH public key on each machine you’ll be accessing GitHub from. Details of how to do this are given in their guide. After adding your public key details to your GitHub account page you’re ready to add your config files:

  git init
  git add .bashrc
  git add .vimrc
  git add <any other config files you want to add>
  git commit -m 'Initial commit'
  git remote add origin git@github.com:coderholic/config.git # Your URL will differ
  git push origin master

Now all your config files are on GitHub!

Setting up a new machine

You’ll need to create a SSH public key on the new machine, just as you did on the previous one. Once you’ve done that:

Once you’ve done that:

  git init
  git pull git@github.com:coderholic/config.git
  git remote add origin git@github.com:coderholic/config.git

And your setup will match your other machine!

Making changes

The real benefit of storing your config files on GitHub is that it makes it really easy to synchronise changes between all your computers. After making a change on one computer:

  git commit -a -m "Useful commit message goes here"
  git push origin master

Then all you need to do on your other machines is issue the following command:

  git pull origin master

And you’re synced!

If you’re interested in seeing my config files you can see them on the GitHub page: http://github.com/coderholic/config/tree/master

Inspired by a movable tripe blog post I came up with the following shell script to convert an AVI file to an ISO image that can be burned to disc and played back on a standard DVD player.

The script requires mencoder, ffmpeg, dvdauthor and mkisofs, and will let you know you if any of these are missing. Use it like follows:

./dvd.sh input.avi

Once the script has finished a “dvd.iso” file will be created, which can then be burned to DVD using your favourite disc burning tool.

#!/bin/bash
# AVI to DVD Script
# Ben Dowling - www.coderholic.com

# Change to "ntsc" if you'd like to create NTSC disks
format="pal"

# Check we have enough command line arguments
if [ $# != 1 ]
then
	echo "Usage: $0 <input file>"
	exit
fi

# Check for dependencies
missing=0
dependencies=( "mencoder" "ffmpeg" "dvdauthor" "mkisofs" )
for command in ${dependencies[@]}
do
	if ! command -v $command &>/dev/null
	then
		echo "$command not found"
		missing=1
	fi
done

if [ $missing = 1 ]
then
	echo "Please install the missing applications and try again"
	exit
fi

function emphasise() {
	echo ""
	echo "********** $1 **********"
	echo ""
}

# Check the file exists
input_file=$1
if [ ! -e $input_file ]
then
	echo "Input file not found"
	exit
fi

emphasise "Converting AVI to MPG"

ffmpeg -i finalmovie.avi -y -target ${format}-dvd -sameq -aspect 16:9 finalmovie.mpg

if [ $? != 0 ]
then
	emphasise "Conversion failed"
	exit
fi

emphasise "Creating DVD contents"

dvdauthor --title -o dvd -f finalmovie.mpg
first=$?
dvdauthor -o dvd -T
second=$?

if [ $first != 0 || $second != 0 ]
then
	emphasise "DVD Creation failed"
	exit
fi

emphasise "Creating ISO image"

mkisofs -dvd-video -o dvd.iso dvd/

if [ $? != 0 ]
then
	emphasise "ISO Creation failed"
	exit
fi

# Everything passed. Cleanup
rm -f finalmovie.mpg
rm -rf dvd/

emphasise "Success: dvd.iso image created"

It can be absolute nightmare if you find out on Monday morning that your web server was down all weekend, and all your potential visitors would have been greeted with an error message! This has happened to me a couple of times recently (fortunately not on this site), so I came up with an automated checker. The shell script is below:

#!/bin/bash

# Query a supplied URL and output an error message if
# the status code was not 200. Can be used as a cron
# task to check if a server is up or not.

# Ben Dowling - http://www.coderholic.com

# The URL to query
if [ $# -ne 1 ]
then
	echo "Usage: $0 <URL>";
	exit;
fi

url=$1;
response=$(curl -s -I -L $url | grep HTTP); 

status=${response#* }; # Strip off characters up to the first space
status=${status:0:3}; # Just use the 3 digit status code

if [ "$status" != "200" ]
then
	echo "Error fetching $url. Status code '$status'";
fi

I then set this up as a cron task to run every 15 minutes, and to email me if the site doesn’t respond with a HTTP 200 OK status:

*/15 * * * * /path/to/checkserver.sh "http://www.coderholic.com" | mail ben@coderholic.com

I’ve been using Linux for over 10 years now, but I ran into a seemingly simple problem today that had me stumped. I wanted to change directory whose name started with a “-” character (a hypen, or dash). Here’s what happened:

bmd /dir: ls
-folder
bmd /dir: cd -folder
-bash: cd: -f: invalid option
cd: usage: cd [-L|-P] [dir]
bmd /dir: cd \-folder
-bash: cd: -f: invalid option
cd: usage: cd [-L|-P] [dir]
bmd /dir: cd “-folder”
-bash: cd: -f: invalid option
cd: usage: cd [-L|-P] [dir]

I wasn’t having much luck! It turns out solution is to pass “--” (two hypens) as the first argument to cd:

bmd /dir: cd — -folder
/home/bmd/dir/-folder
bmd /-folder:

I think I remember having to use this trick in the past, but it’s such an infrequent problem that I obviously forgot about it. Hopefully by posting about it I’ll have a better change of remembering it for next time!