===== 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%)


#!/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 =====


#!/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 =====


#!/bin/bash

HOST=$1
RPASS=$(