repair and optimize all tables

Sometimes the database daemon crashes (because the server ran out of swap space for example). This can cause the databases to be damaged. Finding and repairing the database table in question can be an arduous task, especially if you have a lot of databases. This script runs both repair and optimize on all tables.

You’ll first need a .my.cnf file in your own directory (/root/.my.cnf if you’re root for example), with the following contents:

[client]
user=YOURUSERNAMEHERE
password=YOURPASSWORDHERE

For instance, if you’re running directadmin, they username is da_admin. The password can be found in /usr/local/directadmin/conf/mysql.conf

This script does work for all situations of MySQL and MariaDB though, so do not get hung up on the username. Just use the username you need to access ALL the databases. Make sure the permissions on the file are 600.

Here’s the (simple) script:

# Finding and repairing the database table in question can be an
# arduous task, especially if you have a lot of databases. This
# script runs both repair and optimize on all tables.
###
# (c)opyleft Take13
#!/bin/bash

MYSQLBIN=/usr/bin/mysql

for DATABASE in `echo "show databases" | $MYSQLBIN | grep -v Database | grep -v information_schema | grep -v performance_schema` ; do
        for TABLE in `echo "show tables" | $MYSQLBIN $DATABASE | grep -v Tables_in_` ; do
                echo $DATABASE"."$TABLE
                echo "repair table "$DATABASE"."$TABLE | $MYSQLBIN
                echo "optimize table "$DATABASE"."$TABLE | $MYSQLBIN
        done
done