Read RDS MySQL binary logs and provide a statement summary (supports ROW)

rds-mysql-binlog-analysis.sh

#!/usr/bin/env bash
#set -o errexit
set -o pipefail

[ -z "${TMP_DIR}" ] && TMP_DIR=/tmp
TMP_FILE=${TMP_DIR}/binlog.txt.$$

BINLOG_ADDITIONAL_ARGS=${BINLOG_ADDITIONAL_ARGS:- -vvv --base64-output=DECODE-ROWS}
BINLOG_FILE=$1
[ -z "${BINLOG_FILE}" ] && echo "ERROR: You must specify a MySQL binary Log file" && exit 1

[ -z "${AUTHENTICATION}" ] && echo "ERROR: You must a MySQL Authentication string, e.g. contains -u -p -h with values " && exit 1

# For RDS we check file
FILE_SIZE=$(mysql ${AUTHENTICATION} -ABU --show-warnings -e "SHOW BINARY LOGS" 2>&1 | grep -v "Using a password on the command line interface can be insecure" | grep ${BINLOG_FILE} | awk '{print $2}')
# For RDS we have to first read the file
echo "Retrieving binary log '${BINLOG_FILE}' size '${FILE_SIZE}' from remote host, storing in '${TMP_DIR}'"
mysqlbinlog --read-from-remote-server --raw --result-file=${TMP_DIR}/ ${AUTHENTICATION} ${BINLOG_FILE} 2>/dev/null # Kill warning message and not mess with $?
[ $? -ne 0 ] && echo "ERROR: Unable to retrieve '${BINLOG_FILE}'" && exit 1


mysqlbinlog ${BINLOG_ADDITIONAL_ARGS} ${TMP_DIR}/${BINLOG_FILE}  | \
  sed -e "s/^### //;" | \
  sed -e "/^#/d;s/\/\*.*\*\/[;]//;/^$/d" | \
  cut -c1-100 | \
  tr '[A-Z]' '[a-z]' | \
  egrep "^(insert|update|delete|replace|commit|alter|drop|create)" |  \
  sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//;s/ join .*$//;s/ values .*$//;" | sed -e "s/ where .*$//;s/ignore //g;s/ inner//g;s/ left//g;s/ right//g;s/ from//g;s/ into//g" |  \
  sed -e "s/or replace//;s/sql security definer//;s/algorithm=.* //;s/definer=.* //g" |  \
  sed -e "s/ \w.*\.\*//" | \
  awk '{ print $1,$2 }' | \
  sort | uniq -c | sort -nr   >  ${TMP_FILE}


TOTAL=`awk 'BEGIN {total=0}{total=total+ $1}END{print total}' ${TMP_FILE}`
echo "Analysis of '${BINLOG_FILE}' at "`date`
echo ""
(
echo "%    ${TOTAL} ALL QUERIES"
head -50 ${TMP_FILE} | awk -v TOTAL=${TOTAL} '{printf("%.2f %s\n",$1*100.0/TOTAL, $0)}'
) | column -t
echo ""
echo "Full Details available in ${TMP_FILE}"
exit 0