Table of Contents

Pretty InnoDB Buffer Pool Stats

Now I can quickly see buffer pool stats in the following format:

 buffer pool size: 27999076352 bytes (27G)
 used: 16563568640 bytes (16G) (59.00%)
 modified db pages: 4747952128 bytes (4G) (16.00%)

f innodb-status.sh

#!/bin/sh

# Pretty print InnoDB buffer stats.
#
# SHOW INNODB STATUS looks like:
#
# Buffer pool size   1708928
# Free buffers       1142066
# Database pages     565676
# Modified db pages  123467

pp() {

    value=$1

    if [ $value -gt 1000000000 ]; then
        value=$(expr $value / 1000000000)G
    elif [ $value -gt 1000000 ]; then
        value=$(expr $value / 1000000)M
    elif [ $value -gt 1000 ]; then
        value=$(expr $value / 1000)K
    fi

    echo $value

}

perc() {

    nr=$1
    total=$2

    echo "scale = 2; ($nr / $total ) * 100" | bc

}

# donnot let bash screw up multiline parsing (I hate this bug)
IFS=

stats=$(echo "SHOW ENGINE INNODB STATUS\G" | mysql -N)

#echo $stats
set -o noglob

buffer_pool_size=$(echo $stats | grep -E '^Buffer pool size' | grep -Eo '[0-9]+$')
buffer_pool_size=$(expr $buffer_pool_size * 16384)

free_pool_size=$(echo $stats | grep -E '^Free buffers' | grep -Eo '[0-9]+$')
free_pool_size=$(expr $free_pool_size * 16384)

modified_db_pages=$(echo $stats | grep -E '^Modified db pages' | grep -Eo '[0-9]+$')
modified_db_pages=$(expr $modified_db_pages * 16384)

used=$(expr $buffer_pool_size - $free_pool_size)

echo "buffer pool size: $buffer_pool_size bytes ($(pp $buffer_pool_size))"
echo "used: $used bytes ($(pp $used)) ($(perc $used $buffer_pool_size)%)"  

echo -n "modified db pages: $modified_db_pages bytes "
echo -n "($(pp $modified_db_pages)) "
echo    " ($(perc $modified_db_pages $buffer_pool_size)%)" 

Kill every MySQL SELECT older than X seconds

f kill.sh

#!/bin/bash
SEC=$1
IFS=’|’
if [[ $SEC -lt 1 ]]; then
   echo “Usage: $0 SECONDS”
   exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi “delete|update|insert|alter table” |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
   if [ $qsec -gt $SEC ]; then
      echo “Killing query $qid…”
      mysqladmin kill $qid
   fi
done

Sniff the MySQL traffic on the fly

# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

RESET SLAVE

f reset-mysql-slave.sh

#!/bin/bash

HOST=$1
RPASS=$(<passwordfile)

IFS="
"

mysql -h $HOST -e "STOP SLAVE"

for line in $(mysql -e "SHOW SLAVE STATUS" -E -h $HOST)
 do
	key=${line%:*}  # Split off what goes before :
	key=${key// }   # And trim spaces
	data=${line#*:} # Split off what goes after :
	data=${data// } # And trim spaces yet again!!! 

	case $key in
		Exec_master_log_pos) LOGPOS=$data;;
		Relay_Master_Log_File) LOGFILE=$data;;
		Master_Host) RHOST=$data;;
		Master_User) RUSER=$data;;
	esac

 done

if [ -z $LOGPOS -o -z $LOGFILE ]; then
        echo "OMG FAIL NO POSITIONS KNOWN"; exit;
fi 

mysql -f -h $HOST -e "
        RESET SLAVE;
        CHANGE MASTER TO
                MASTER_HOST='$RHOST',
                MASTER_USER='$RUSER',
                MASTER_PASSWORD='$RPASS',
                MASTER_LOG_FILE='$LOGFILE',
                MASTER_LOG_POS=$LOGPOS ;
        START SLAVE
        "