Mysqlcheck is a stand-alone MySQL utility for checking, optimizing and repairing tables. It’s a useful tool that allows automating the usually time-consuming database maintenance tasks. One of the advantages of mysqlcheck it is ability to run in batch mode and operate on all tables in a database or on multiple databases.

The power of mysqlcheck is not without limitations. One of the tool’s more annoying failings is its inability to recover from connection loss errors. If mysqlcheck is in the process of scanning thousands of tables across multiple databases and the server blinks, mysqlcheck may lose database connection and just quit.

Another limitation of mysqlcheck is its single-threaded nature. On modern multi-core servers with high-performance storage, scanning one table at a time seems an epic waste of time. And in combination with the aforementioned connection loss issue, your entire database maintenance run may cut out early in the run.

Here’s a fairly simple script that will run mysqlcheck on all of your tables in all databases and perform optimization and auto-repair operations.  The script will start multiple instances of mysqlcheck, depending on the number of CPU cores on your servers.

#!/bin/bash
# Optimize/repair all tables in all MySQL databases avoiding failure on problem tables

configure() {
	# Misc configurable options
	db_user=username
	db_pass=password
	MYSQL="/usr/bin/mysql -A --batch --skip-column-names --max_allowed_packet=100M -h 127.0.0.1 -u${db_user} -p${db_pass}"
	cpu_cores=$(fgrep -c processor /proc/cpuinfo)
	max_threads=$(echo "scale=0;$${cpu_cores}*4/1"|bc -l)
}

sanity_check() {
	# Make sure no other mysqlcheck threads are running
	if [ `ps -ef | grep -c "[m]ysqlcheck "` -ne 0 ]
	then
		echo "An instance of mysqlcheck is already running. Exiting..."
		exit 1
	fi
}

mysqlcheck_do() {
	# Let 'er rip
	cd /tmp
	$MYSQL -e "show databases" | egrep -v "information_schema|mysql" | while read db_name
	do
		$MYSQL ${db_name} -e "show tables" | while read table
		do
			while [ `ps -ef | grep -c "[m]ysqlcheck "` -gt ${max_threads} ]
			do
				sleep 1
			done
			
			echo "Checking ${db_name}.${table}"
			nohup mysqlcheck -u${db_user} -p${db_pass} --auto-repair -o ${db_name} ${table} &
		done
	done
}

# RUNTIME

configure
sanity_check
mysqlcheck_do