Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.


Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.


Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.


Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » Commands & Shells, Featured, MySQL

A Better Mysqlcheck Script

Submitted by on April 2, 2015 – 10:44 am

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.

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

configure() {
	# Misc configurable options
	MYSQL="/usr/bin/mysql -A --batch --skip-column-names --max_allowed_packet=100M -h -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 ]
		echo "An instance of mysqlcheck is already running. Exiting..."
		exit 1

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




Print Friendly, PDF & Email

Leave a Reply