Analyze MySQL/MariaDB binlog

analyze_binlog.sh

#!/bin/sh

CMDS="insert update delete"
for CMD in ${CMDS}; do
  mysqlbinlog ${1} | grep "^${CMD}" > $1.${CMD}
  if [ "${CMD}" = "insert" ]; then
    cat ${1}.${CMD} | gawk 'split($3, a, /\(/){print a[1]}' | sort -u > ${1}.${CMD}.tables
  fi
  if [ "${CMD}" = "delete" ]; then
    cat ${1}.${CMD} | gawk 'split($3, a, /\(/){print a[1]}' | sort -u > ${1}.${CMD}.tables
  fi
  if [ "${CMD}" = "update" ]; then
    cat ${1}.${CMD} | gawk '{print $2}' | sort -u > ${1}.${CMD}.tables
  fi

  SEARCH=""
  while read -r line; do
    if [ "${CMD}" = "insert" ]; then
      SEARCH="${SEARCH}${line}\(|"
    else
      SEARCH="${SEARCH}${line}\$|"
    fi
  done < ${1}.${CMD}.tables

  echo "${CMD}:"
  if [ "${CMD}" = "update" ]; then
    cat ${1}.${CMD} | gawk "match(\$2, /${SEARCH}/, b) { a[b[0]]++ } END { for (i in a ) printf(\"%-30s\t%s\n\", i, a[i]) }" | sort
  else
    cat ${1}.${CMD} | gawk "match(\$3, /${SEARCH}/, b) { a[b[0]]++ } END { for (i in a ) printf(\"%-30s\t%s\n\", i, a[i]) }" | sort
  fi
  echo
done