Differences

This shows you the differences between two versions of the page.

--- mysql:scripts [2008/10/31 23:31]
a created
+++ mysql:scripts [2009/05/25 00:35] (current)
@@ Line 1: / Line 1: @@
+===== 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%)
+<code bash|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)%)"
+</code>
 ===== Kill every MySQL SELECT older than X seconds =====
 <code bash|f kill.sh>
@@ Line 16: / Line 87: @@
 done
 </code>
+===== Sniff the MySQL traffic on the fly =====
+<code cli>
+# 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.=" $_";
+  }
+}'
+</code>
+===== RESET SLAVE =====
+<code bash|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
+        "
+</code>