Posted on by admin yep

MySQL Inside: Using the PS error_log table for a quick peak!

Just thought I’d share a script I use daily and helps me redirect my attention if needed.

This is but a mere pointer, guideline and starting point in any task. I just thought I’d share and hope someone else’s day becomes slightly easier thanks to some brief investigation and command tweaking.

Now the really handy thing here is that I only hard code the router01 node name, as I’m using that as a potential endpoint (thinking cloud, XaaS, etc…) where it could also be a VIP, LBR or similar. It’s the entry point so I can query the P_S table error_log so I can get different views and act accordingly.

For example:

  • First, give me the InnoDB Cluster ordered server list so I can take a step back from my usual pains and worries, and see the architecture view. And make me type “Y” or similar to move on. Here if there were any server missing, I’d see the summary right away so I don’t really need to worry about the error_log and a good ol’ CTRL-C is struck.
    • Now, give me a list of errors that appear in the log for the last:

30 minutes

4 hours

24 hours

    And then a summary & count, so I can prioritize my investigation time.

    Some explanations as to what’s being used:

    “–login-path” is how I have configured my env for added simplicity. Feel free to use user@host:port or whatever tickles the proverbial.

    “-A” when call mysqlsh means I won’t search any cached data.

    “–sqlc” means I’ll execute SQL.

    And at the end I’m curious as to since when the instance has been recording the error_log, so I add it in, for the hell of it.

    Sharing is good, hence the script:

    $ cat error_log.sh



#!/bin/bash

MYSQLUSER=icadmin
MYROUTER1=router01

MYSQLSH="mysqlsh --login-path=$MYSQLUSER -h$MYROUTER1 --table -A --sqlc -e "

echo
echo "Current time:" date
echo
echo "Remember: login-path needs to be defined."
MYSQLUSER=icadmin
MYSQLSH="mysqlsh --login-path=$MYSQLUSER -h$HOSTNAME --tabbed -A --sqlc -e "
MYSQLLOGIN="mysqlsh --login-path=$MYSQLUSER -h"

MYROUTER1=$MYSQLSH "select min(address) from mysql_innodb_cluster_metadata.routers" | grep -v address
MYROUTER2=$MYSQLSH "select max(address) from mysql_innodb_cluster_metadata.routers" | grep -v address
HOST1=$MYSQLSH "select MEMBER_HOST from performance_schema.replication_group_members where MEMBER_ROLE = 'PRIMARY';" | grep -v MEMBER
HOST2=$MYSQLSH "select min(MEMBER_HOST) from performance_schema.replication_group_members where MEMBER_ROLE != 'PRIMARY';" | grep -v MEMBER
HOST3=$MYSQLSH "select max(MEMBER_HOST) from performance_schema.replication_group_members where MEMBER_ROLE != 'PRIMARY';" | grep -v MEMBER
echo
echo "We have the following information: "
echo " "$MYROUTER1" & "$MYROUTER2
echo " "$HOST1" & "$HOST2" & "$HOST3
echo " "$HOSTNAME" is the current host."
echo
read -p "Continue? (Y/N): " confirm && [[ $confirm == [yY] || $confirm == [yY][eE][sS] ]] || exit 1


echo ----------------------------------------------------------------------------------------------------------------------------------------------------------------
echo
echo $HOST1
MYSQLSH="mysqlsh --login-path=$MYSQLUSER -h$HOST1 --table -A --sqlc -e "

echo "Errors in the last 30 mins:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 30 MINUTE)"
echo
echo "Errors in the last 4 hours:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 4 HOUR)"
echo
echo "Errors for the last 24 hours:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY)"
echo
echo "Summary of the latest errors for the last 24h in the error_log:"
$MYSQLSH "select count(*) from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY)" $MYSQLSH "select ERROR_CODE, SUBSYSTEM, DATA, count() from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY) group by ERROR_CODE, SUBSYSTEM, DATA"
echo
echo "Summary of error log:"
$MYSQLSH "select min(LOGGED) as Earliest, max(LOGGED) as Last, count(*) as Count from performance_schema.error_log "
echo
echo ----------------------------------------------------------------------------------------------------------------------------------------------------------------
echo
echo $HOST2
MYSQLSH="mysqlsh --login-path=$MYSQLUSER -h$HOST2 --table -A --sqlc -e "
echo
echo "Current time:"date
echo
echo "Errors in the last 30 mins:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 30 MINUTE)"
echo
echo "Errors in the last 4 hours:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 4 HOUR)"
echo
echo "Errors for the last 24 hours:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY)"
echo
echo "Summary of the latest errors for the last 24h in the error_log:"
$MYSQLSH "select count(*) from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY)" $MYSQLSH "select ERROR_CODE, SUBSYSTEM, DATA, count() from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY) group by ERROR_CODE, SUBSYSTEM, DATA"
echo
echo "Summary of error log:"
$MYSQLSH "select min(LOGGED) as Earliest, max(LOGGED) as Last, count(*) as Count from performance_schema.error_log "
echo

echo ----------------------------------------------------------------------------------------------------------------------------------------------------------------
echo
echo $HOST3
MYSQLSH="mysqlsh --login-path=$MYSQLUSER -h$HOST3 --table -A --sqlc -e "
echo
echo "Current time:"date
echo
echo "Errors in the last 30 mins:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 30 MINUTE)"
echo
echo "Errors in the last 4 hours:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 4 HOUR)"
echo
echo "Errors for the last 24 hours:"
$MYSQLSH "select * from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY)"
echo
echo "Summary of the latest errors for the last 24h in the error_log:"
$MYSQLSH "select count(*) from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY)" $MYSQLSH "select ERROR_CODE, SUBSYSTEM, DATA, count() from performance_schema.error_log where logged > (NOW() - INTERVAL 1 DAY) group by ERROR_CODE, SUBSYSTEM, DATA"
echo
echo "Summary of error log:"
$MYSQLSH "select min(LOGGED) as Earliest, max(LOGGED) as Last, count(*) as Count from performance_schema.error_log "
echo
echo "End!"

    Planet MySQL