Counting non InnoDB with CloudWatch

With WordPress we run the risk our tables are being 'altered' to MyISAM. We suspect plugins, with plugin developers dis-respecting our choices. It appears to be innocent, harmless. But we loose the ability to 'Point in Time Restore' of RDS. So, potentially devastating.

We created a small bash script to count the nr of non InnoDB tables.

#!/bin/bash

E_BADARGS=65

if [ ! -n "$1" ]
then
  echo "Usage: `basename $0` hostname username password"
  exit $E_BADARGS
fi

hostname=$1
identifier=${hostname/%.*}
username=$2
password=$3

echo "getting the databases from ${hostname}"
databases=`mysql -N -u ${username} -p${password} -h ${hostname} -e 'show databases where \`Database\` not in ("mysql", "innodb", "information_schema", "performance_schema")'`

total=0

for database in ${databases}
do
    echo "getting the nr of tainted tables from ${database}"
    tainted_tables=`mysql -N -u ${username} -p${password} -h ${hostname} -e "show table status from ${database} where Engine <> \"InnoDB\"" | wc -l`
    total=$((total + tainted_tables))
done

echo "we have ${total} tainted tables"
php put-status.php ${identifier} ${total}

We report the identifier and nr to CloudWatch with a PHP script.

<?php
require_once 'AWSSDKforPHP/sdk.class.php';

define('AWS_KEY', '');
define('AWS_SECRET_KEY', '');
define('AWS_ACCOUNT_ID', '');

$cw = new AmazonCloudWatch();

$dimensions = array(
    array( 'Name' => 'DBInstanceIdentifier',
        'Value' => $argv[1])
);
$timestamp = date( DATE_RFC822);

$response = $cw->put_metric_data('9Apps/RDS', array(
    array(
        'MetricName' => 'NonInnoDBTables',
        'Dimensions' => $dimensions,
        'Value' => $argv[2],
        'Timestamp' => $timestamp,
        'Unit' => 'Count'
    ),
));
?>

Which we put to use with simple cron job by adding something like this

# check and report non InnoDB tables
* * * * * /root/innodb.sh [hostname] [username] [password] > /dev/null 2>&1