Debugging and other tools

MySQL   |  PostgreSQL   |  PostgreSQL to AlloyDB

Overview

This page includes scripts that help with debugging and using MySQL.

Find write activities

#!/bin/bash

# Tail the binlog and look for insert / update / delete
# The goal is to help the user understand which writes are happening


MYSQL=$(which mysql)
MYSQLBINLOG=$(which mysqlbinlog)
DATE=$(which date)

if [[ -z "${MYSQL}" ]]
then
  echo "ERROR: Could not find mysql shell"
  exit 1
fi
if [[ -z "${MYSQLBINLOG}" ]]
then
  echo "ERROR: Could not find mysqlbinlog utility"
  exit 1
fi

if [[ -z "$1" ]]
then
  echo "Usage: $0 [mysql connection parameters]"
  exit 1
fi

last_log=$("${MYSQL}" "$@" -N -B -e "SHOW BINARY LOGS;" | tail -n 1 | cut -f1)
time=$("${DATE}" '+%Y-%m-%d %H:%M:%S')

echo "Continuously reading from ${last_log} starting from ${time}"

"${MYSQLBINLOG}" --base64-output=DECODE-ROWS --verbose --start-datetime="${time}" --read-from-remote-server "$@" "${last_log}" --stop-never | grep "INSERT\|UPDATE\|DELETE"

Find ALTER TABLE commands

#!/bin/bash

# Search for DDL Commands in the last 24 hours. Should help the user understand
# which DDL commands they are performing.

MYSQL=$(which mysql)
MYSQLBINLOG=$(which mysqlbinlog)
DATE=$(which date)

if [[ -z "${MYSQL}" ]]
then
  echo "ERROR: Could not find mysql shell"
  exit 1
fi

if [[ -z "${MYSQLBINLOG}" ]]
then
  echo "ERROR: Could not find mysqlbinlog utility"
  exit 1
fi

if [[ -z "$1" ]]
then
  echo "Usage: $0 [mysql connection parameters]"
  exit 1
fi

log_files=$("${MYSQL}" "$@" -N -B -e "SHOW BINARY LOGS;" | cut -f1)

yesterday=$("${DATE}" --date="-1 day" '+%Y-%m-%d %H:%M:%S')

echo "Searching for DDL commands, starting at ${yesterday}"
for file in ${log_files}
do
  echo "Log file: ${file}"
  "${MYSQLBINLOG}" --start-datetime "${yesterday}" --read-from-remote-server "$@" "${file}" | grep -B 2 "ALTER TABLE\|CREATE TABLE\|TRUNCATE TABLE\|RENAME TABLE\|DROP TABLE"
done

Lock all tables

#!/bin/bash

# This script locks all non-system tables on a MySQL database.
# Helps for the case where we cannot acquire read lock with flush.

MYSQL="$(which mysql)"

if [[ -z "${MYSQL}" ]]
then
  echo "ERROR: Could not find mysql shell"
  exit 1
fi

if [[ -z "$1" ]]
then
  echo "Usage: $0 [mysql connection parameters]"
  exit 1
fi

LOCK_TABLES_STMT="select concat('LOCK TABLES ', group_concat(concat('\`',table_schema,'\`.\`',table_name,'\` READ')),';') as stmt from information_schema.tables where table_schema not in ('mysql', 'sys', 'performance_schema', 'information_schema');"
QUERY="$("${MYSQL}" "$@" -N -B -e "${LOCK_TABLES_STMT}")"

(
 echo "${QUERY}"
 read -n 1 -r -s -p $'Tables locked, press any key to stop the session and UNLOCK TABLES\n'
 echo "UNLOCK TABLES;"
) | "${MYSQL}" "$@"

Send feedback