Skip to main content

A simple MySQL database maintenance shell script.

#!/usr/bin/env bash

##############################################################################
# Performs Mysql Database Maintenance Operations:
# 	- Check
# 	- Optimize
# 	- Analyze
#
# WARNING:
# 	These operations will cause LOCKS, so be cautious on a production
# 	server!
#
# Based on:
# 	- MySQL Maintenance Tasks for InnoDB with MySQL 5.1
# 	  http://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/
##############################################################################

set -e

mysql_username="root"
mysql_password="password"
mysql_hostname="localhost"
mysql_database_name="testdb"

echo
echo "Checking db \"$mysql_database_name\" for integrity errors..."
echo "-----------------------------------------------------------------------"
mysqlcheck -h $mysql_hostname -u $mysql_username -p$mysql_password --check --databases $mysql_database_name

echo
echo "Optimizing db \"$mysql_database_name\" to reclaim unused space..."
echo "-----------------------------------------------------------------------"
mysqlcheck -h $mysql_hostname -u $mysql_username -p$mysql_password --optimize --databases $mysql_database_name

echo
echo "Analyzing db \"$mysql_database_name\" to rebuild and optimize indexes..."
echo "-----------------------------------------------------------------------"
mysqlcheck -h $mysql_hostname -u $mysql_username -p$mysql_password --analyze --databases $mysql_database_name

echo
echo "Finished."
echo