Fixes broken mysql meta tables

fix-mysql-meta-tables

#!/bin/bash
#
# Fix broken mysql meta tables.
#
# For some reason, `innodb_*_stats' tables seem to be corrupted (not existing
# when being queried from mysql shell, but physical files do exist).
# `slave_worker', `slave_relay_log_info' and `slave_master_info' tables
# seem to have the same problem, which `mysql_install_db' will happily fix.
#
# This script performs the necessary steps to have that done.

echo "You do not want to run this accidentally, do you?"
exit 1

do_sql () {
    local query="$1"
    echo "${query}" | mysql -u root mysql
}

if [ $(whoami) != root ]; then
    echo "Can't run $0 without root permissions!"
    exit 1
fi

tables="innodb_index_stats innodb_table_stats slave_master_info \
        slave_relay_log_info slave_worker_info"

for table in ${tables}; do
    do_sql "DROP TABLE ${table};"
    do_sql "ALTER TABLE ${table} DISCARD TABLESPACE;"
done

mysql_pid=$(pidof mysqld)

if [ ! -z "${mysql_pid}" ] ; then
    echo "stopping mysql (${mysql_pid})"
    service mysql stop
    wait ${mysql_pid}
else
    echo "mysql not running"
fi

mysql_db_dir=/var/lib/mysql/mysql
backup_dir=/root/backup-mysql

echo "moving bad table files in ${backup_dir}"
mkdir "${backup_dir}" >/dev/null

for table in ${tables} ; do
    mv -f ${mysql_db_dir}/${table}.* ${backup_dir} >/dev/null
done

cd /var/lib/mysql && mysql_install_db

service mysql start && echo "mysql restarted"