Robert de Bock

Optimize only fragmented tables in MySQL

When you are using MySQL, you will (likely) have tables that can be fragmented. In MySQL terms this is called "OPTIMIZE".

You could simply OPTIMIZE every table in every database, but during an OPTIMIZE, the tables are locked, so writing is not possible.

To minimize the time that MySQL will be locked (and results cannot be written), here is a script that checks fragmentation of every table of every database. Only if a table is fragmented, the table is OPTIMIZED.


echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
  if [ "$datafree" -gt 0 ] ; then
   fragmentation=$(($datafree * 100 / $datalength))
   echo "$database.$name is $fragmentation% fragmented."
   mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"

Result will look something like this:

MySQL username: root
MySQL password:
database.cache_filter is 19% fragmented.
meinit.cache_filter optimize status OK
database.cache_page is 35% fragmented.
meinit.cache_page optimize status OK

You may comment out that line with OPTIMIZE TABLE in it, if you are just interested in seeing the fragmentation.

Failover on OpenBSD is so easy to setup using carp!

I am not the first (and last) to write about carp, the failover/vip/floating-IP solution OpenBSD is using. Many articles describe this topic including a very complete answer to a frequently asked question about carp.

If you are not familiar with IP failover situations; in case of carp/pulse/HSRP/VIP, an IP "floats" between different machines. One machine actually answers request to received packets, so this is an solution that knows of a MASTER of ACTIVE node .

A CARP interface (which is not physical) is bound to a physical interface. The physical interface advertises statuses so other CARP interfaces know about each other.

You can bind almost any service to a CARP interface, some examples are:

  • DNS
  • HTTP
  • NTP
  • Proxy/Squid

Services that store data/stadia locally are not very suitable for a CARP solution. Examples are: DHCP (because leases are stored localy), MySQL/PostgreSQL (because data is stored on a physical local storage) and SSH (because you can never be sure what machine you are connecting to.

Here is how to set it up. On both boxes add a file /etc/hostname.carp0 with this content:

inet 255.255.0 vhid 1 pass SeCrEt carpdev em0

Remember to activate the interface like this: (All your network cards will be (re-) configured!)

# sh /etc/netstart

In this case, is the floating IP address and em0 is the physical device that carp0 is running on. Be aware that the other server's carpdev should be connected to the same LAN.

Now that this is done, you may access services on the newly created CARP device's IP address. You may also specifically bind applications to only the CARP device.

You may check the status using ifconfig: (Please not the "carp: MASTER" part, it tells you this machine is the master, all others are "BACKUP".)

# ifconfig carp0
        lladdr 00:00:5e:00:01:02
        priority: 0
        carp: MASTER carpdev em0 vhid 1 advbase 1 advskew 0
        groups: carp
        inet6 fe80::200:5eff:fe00:102%carp0 prefixlen 64 scopeid 0x5
        inet netmask 0xffffff00 broadcast

One limitation I found; you can not run dhclient on a carp interface, you will need to assign an IP address to the carp device. Please be aware that this would be a very odd setup; DHCP in a failover interface...

Your Soekris OpenBSD as a OpenVPN appliance

I have an existing network at home, but would like to be able to connect to it using a VPN every now and then. This enables me to access the fileserver, printer and so on.

My network contains an Apple Time Capsule as a nat router, an ethernet modem provided by my cable company Ziggo and devices such as laptops, that use the network.

A Soekris box I had lying around meets all requirements perfectly for a VPN-server. Here is how to set it up.

1. Forward UDP port 1194 from your router to your soekris box.

This one is easy enough, on Apple Mac OS X and a Time Capsule (or Airport Express) open AirPort Utility on your Mac, select the Time Capsule, click Manual Setup.
Go to Internet - NAT
Select the box "Enable NAT Port Mapping Protocol" and click on "Configure Port Mappings..."

Click on the "+" to add a portmapping. OpenVPN uses UDP port 1194, so map it from the "Public UDP Port(s)" to the "Private UDP Port(s)" on the "Private IP Address" of your soekris box. Fill in "OpenVPN" in the next "Description" field.

Finish your router configuration by pressing "Update". N.B. The network connection will be gone for a minute or two.

2. Install OpenVPN

I assume OpenBSD is already running on your Soekris box, otherwise check out how to install your soekris box with OpenBSD.
Add the package "openvpn". A dependency "lzo" will be added automatically.

3. Configure OpenVPN

Create a directory /etc/openvpn/keys:

soekris # mkdir -p /etc/openvpn/keys

And create the file /etc/openvpn.conf with this content:
port 1194
proto udp
dev tun0
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/server.crt
key /etc/openvpn/keys/server.key                                 
dh /etc/openvpn/keys/dh1024.pem
# This is the network that lives on the tun0 device.
# My regular network uses, so using
# seems pretty logical.
ifconfig-pool-persist ipp.txt
# When clients connect, tell them that can
# be reached through this tunnel. (You may also set this on the,
# client instead of "broadcasting" this...
push "route"
keepalive 10 120
user _openvpn
group _openvpn
status openvpn-status.log
verb 3

4. Create OpenSSL certificates

This is quite an abstract step. It boils down to this: on the server you will create a certificate authority (ca) key and certificate, also you will create a key and certificate for each client connecting and sign them using your newly create certificate authority. The certificate from the certificate authority (ca.crt) and client (client1.crt) and the key for the client (client1.key) will be distributed to all clients. That's a mouth full, but here is how to do it in steps:

soekris # cp -Rip /usr/local/share/example/openvpn/easy-rsa /etc/openvpn
soekris # cd /etc/openvpn/easy-rsa/2.0
soekris # cat vars
export EASY_RSA="`pwd`"
export OPENSSL="openssl"
export PKCS11TOOL="pkcs11-tool"
export GREP="grep"
export KEY_CONFIG="/etc/openvpn/easy-rsa/2.0/openssl.cnf"
export KEY_DIR="/etc/openvpn/keys"
echo NOTE: If you run ./clean-all, I will be doing a rm -rf on $KEY_DIR
export PKCS11_MODULE_PATH="dummy"
export PKCS11_PIN="dummy"
export KEY_SIZE=1024
export CA_EXPIRE=3650
export KEY_EXPIRE=3650
export KEY_CITY="Utrecht"
export KEY_ORG="Me in It Consultancy"
export KEY_EMAIL="[email protected]"

N.b. Please change the KEY_ values to match your personal settings.

Now execute these steps, as stolen from The OpenVPN homepage.

soekris # . vars
soekris # ./clean-all
soekris # ./build-ca
soekris # ./build-key-server server
soekris # ./build-key client1
soekris # ./build-key client2
soekris # ./build-key client3
soekris # ./build-dh

Once again; send the newly created file /etc/openvpn/keys/ca.crt, /etc/openvpn/keys/client1.crt and /etc/openvpn/keys/client1.key to the machine using the vpn connection.

5. Configure the OpenBSD Packet Filter

This step enables client to reach your local network using network address translation. At the bare minimum, add this rule to your pf configuration in /etc/pf.conf

nat pass on sis0 from !(sis0) to any -> (sis0)

sis0 is a physical interface that connects the Soekris box to my local area network.

Also, make sure the packet filter is enabled and is using your pf.cofn

soekris # pfctl -e
soekris # pfclt -f /etc/pf.conf

And finally make sure it works after a reboot:

soekris # echo "ps=yes" >> /etc/rc.conf.local

6. Start OpenVPN on the server

Wow, almost there, let's start the software:

soekris # /usr/local/sbin/openvpn --config /etc/openvpn/server.conf --key /etc/openvpn/keys/server.key

Some debugging information will scroll down your screen.

7. Make sure OpenVPN starts at boot time

Add these lines to your /etc/rc.local.

# Add your local startup actions here.

echo " openvpn"
/usr/local/sbin/openvpn --config /etc/openvpn/server.conf --key /etc/openvpn/keys/server.key >> /var/log/openvpn.output &

echo '.'

8. Configure the client(s)

I use Mac OS X to connect to OpenVPN. You will have to install some extra software, your choices are:

  • Tunnelblick - (Free) Tunnelblick is a ready-to-use graphical OpenVPN client for Mac OS X
  • Viscosity - is an OpenVPN client for Mac, providing a rich Cocoa graphical user interface for creating, editing, and controlling VPN connections.

For now I am using the trail version of Viscosity because it looks great. Check out the screenshots below.

Managing a Linux Virtual Server

When you have setup an LVS you will need to administer it. Here are the tools you can use.

Discover what machine is the master.

Log in to both boxes and issue the command:

# ipvsadm

A list of active services and it's IP-addresses will be printed on the active master.

Or, check /var/log/messages for a line like this:


This clearly shows you the machine is a backup.

Failover from one machine to another.

You could simple reboot the active machine. Otherwise, stop the service pulse for a moment on the active server. The backup will discover this and configure the floating IP.
On the active machine, issue:

# /etc/init.d/pulse stop
# sleep 60
# /etc/init.d/pulse start

Add/delete a Virtual Service or Real Server.

Use the piranha web interface, located on port 3636 of either one of the load balancers. Remember to copy /etc/sysconfig/ha/ to the backup machine as well.
After you have altered the configuration, restart pulse on the active machine. (Be aware; this makes services unavailable for a couple of seconds.

# ipvsadm
[services are printed]
# /etc/init.d/pulse restart
# ipvsadm
[services should be printed in a couple of seconds.]

Really minimal HOWTO for loadbalancing on CentOS 5.3 using ipvsadm

There are quite a few howto's for LVS, but all of them are quite extensive. To be honest; you'll need to read them at some point, but for now let's try to make a very minimal howto for setting up LVS.

Step 1: Install and configure a few settings.

Configure the director/loadbalancer to have two NIC's. One side on a routable network, the other side connected to the machine running the services, called realservers.

# chkconfig ipvsadm on
# sed -i 's/net.ipv4.ip_forward = 0/net.ipv4.ip_forward = 1/' /etc/sysctl.conf
# sysctl -p

If you want your realservers to be able to use the internet, execute these lines on the director. Replace YOURREALSERVERLAN for the network address of the network where the real servers are located, for example.

# service iptables save

Step 2: Tell IPVS how to configure itself for HTTP.

Fill in the blanks for PUBLICIP and REALSERVERIP. If you would like to add more servers to this virtual server, just repeast the last line a few times, changing the REALSERVERIP every time.

# echo "-A -t PUBLICIP:80" > /etc/sysconfig/ipvsadm
# echo "-a -t PUBLICIP:80 -r REALSERVERIP -m" >> /etc/sysconfig/ipvsadm
# service ipvsadm start

Step 3: Test it.

From a machine other then the redirector and/or the realserver, visit the ipaddress of your virtual ip.

N.B. I have spent quite some time trying to access the loadbalancer from the loadbalancer; this does not work.

Zabbix on OpenBSD

Installing and using the monitoring tool Zabbix on OpenBSD is quite simple. Take just these steps to get started.

Step 1: Install a few packages.

Use pkg_add to add these packages: (Versions could change over time.)


Follow all hints the package manager tells you.

Step 2: Configure some items.

Make sure the apache daemons is started at boot time. (/etc/rc.conf.local)

Modify PHP to allow longer execution times and set the timezone:

$ grep max_execution_time /var/www/conf/php.ini
max_execution_time = 300
$ grep date.timezone /var/www/conf/php.ini
date.timezone = Europe/Amsterdam
$ sudo pkill httpd
$ sudo /usr/sbin/httpd

Step 3: Compile and install Zabbix.

Get the latest release of Zabbix, untar it and use these options to configure it:

./configure --enable-server --with-pgsql --with-net-snmp --with-libcurl --enable-agent

Use "make install" to install all items. The binaries will be placed in /usr/local.

Import database schemes as described in the Zabbix documentation, chapter 2.4.3: "Zabbix Server"

Create /etc/zabbix/zabbix_agentd.conf and /etc/zabbix/zabbix_server.conf by copying them from the untarred zabbix release:

# mkdir /etc/zabbix
# cp zabbix-1.6.5/misc/conf/zabbix_agentd.conf /etc/zabbix
# cp zabbix-1.6.5/misc/conf/zabbix_server.conf /etc/zabbix

Set DBName DBUser and DBPassword in /etc/zabbix/zabbix_server.conf.

Step 4: Automatically start Zabbix items.

$ cat /etc/rc.local
#       $OpenBSD: rc.local,v 1.39 2006/07/28 20:19:46 sturm Exp $

# Site-specific startup actions, daemons, and other things which
# can be done AFTER your system goes into securemode.  For actions
# which should be done BEFORE your system has gone into securemode
# please see /etc/rc.securelevel.

echo -n 'starting local daemons:'

# Add your local startup actions here.

if [ -x /usr/local/sbin/zabbix_agentd ] ; then
echo -n ' zabbix_agentd'

if [ -x /usr/local/sbin/zabbix_server ] ; then
echo -n ' zabbix_server'

echo '.'

Step 5: Install the webfrontend.

You are practically done, now copy the php files and visit your zabbix installation:

# cp -Rip zabbix-1.6.5/frontends/php/* /var/www/htdocs/zabbix/

That's it, not extremely difficult!

Bash completion for Mac OS X

If your are using the Terminal application of your Apple computer running Mac OS X, try bash programmable completion. It allow you to use the TAB key more often, for example in scp: (If you are using ssh-keys.)

$ scp shell01:/etc/pa
/etc/pam.d/         /etc/pam_smb.conf   /etc/passwd
/etc/pam_pkcs11/    /etc/pango/         /etc/passwd-
$ scp shell01:/etc/pa

The steps to start using this great utility are these:

  1. Download the bash programmable completion .tar.gz file.
  2. In a Terminal on your Apple, untar it. (tar -xvf bash-completion-20060301.tar
  3. Copy the bash_completion shell-script to /etc (sudo cp bash_completion/bash_completion /etc)
  4. Add the bash complation to your login script. (echo ". /etc/bash_completion" >> ~/.profile)
  5. Start a new terminal and see the result!

Using DynDNS for iPhone OS 3.0 without crontab but with launchd

Apple iPhone OS 3.0 does not have crontab anymore. You are supposed to use launchd's facilities to execute something at a scheduled interval. Here is an example of a simple script to update the IP-address at

The script /var/mobile/ contains:



oldip=$(cat /tmp/ip)
wget -o /dev/null -O - > /tmp/ip
ip=$(cat /tmp/ip)

if [ "$oldip" != "$ip" ] ; then
echo -n "$(date) "
echo $(/usr/local/bin/wget -O - "http://$user:[email protected]/nic/update?hostname=$host&wildcard=NOCHG&bacakmx=NOCHG"  2> /dev/null)

The file /var/LaunchDaemons/org.dyndns.update.plist contains:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "">
<plist version="1.0">

And execute:

# launchctl load org.dyndns.update.plist

Now your IP will be update every 3-rd minute. Have fun!

iPhone OS 3.0 Bluetooth tethering with Mac Book Pro

Here is a step by step guide to help you get online with your iPhone using OS 3.0.

  1. Go to this page: It will send you an email. Download that email on you iPhone. It contains a profile to enable Tethering.
  2. Now go to your iPhone preferences, under Network you can enable tethering, activate it
  3. On your Mac Book Pro, go to the bluetooth preferences. Add your iPhone. I had to press "continue" on my Mac Book pro after accepting the pairing request on the iPhone. Waiting here resulted in an error.
  4. On you Mac Book Pro, connect to your iPhone using bluetooth
  5. Now on your Mac Book Pro, under Network Preferences, add a connection: "Bluetooth PAN".

You can now tether using your iPhone!

For tethering, an extra network interface is added, in my case "en5". To see what the IP-address of your connection is, open a Terminal and type:

ifconfig en5

In my case I see that I am using a private class ( IP address. That means that T-Mobile in the Netherlands is NAT-ing my connection. Not a problem, but connecting back to my laptop is not possible from the internet.

Convert a human readable date to epoch with a shell script on OpenBSD and Mac OS X

Dates can be quite challenging. Especially if you systematically want to use dates, for example to compare what date is older.

If you would like to convert this date into epoch, take these steps.

$ # The first step is to print the date.
$ echo "2009/05/25 18:34:30;"
2009/05/25 18:34:30;
$ # This step is to strip the /-es.
$ echo "2009/05/25 18:34:30;" | sed 's%/%%g'
20090525 18:34:30;
$ # This step removes the space
$ echo "2009/05/25 18:34:30;" |  sed 's%/%%g;s% %%g'
$ # This step removes the trailing :30;.
$ echo "2009/05/25 18:34:30;" | sed 's%/%%g;s% %%g;s%:..;%%'
$ # This step removes the :.
$ echo "2009/05/25 18:34:30;" | sed 's%/%%g;s% %%g;s%:..;%%;s%:%%g'
$ # Finally feed that output to the "date" command.
$ date -j "+%s" $(echo "2009/05/25 18:34:30;" | sed 's%/%%g;s% %%g;s%:..;%%;s%:%%g')

On Mac OS X you'd have to use this command:

$ date -j -f date -j -f "%Y/%m/%d %T" "2009/10/15 04:58:06" +"%s"

Syndicate content