MySQL table defragmentation (python script)

I’ve written the below Python script to defragment MySQL database tables. This should work with both InnoDB and MyISAM table formats, and is just calling “OPTIMIZE TABLE blah.foo”. It rummages through the information_schema for tables which may be defragmented.

You should be able to run it on any Debian based system without making any alterations.

#!/usr/bin/env python
import ConfigParser
import os
import time

# On Debian, /etc/mysql/debian.cnf contains 'root' a like login and password.
config = ConfigParser.ConfigParser()
config.read("/etc/mysql/debian.cnf")
username = config.get('client', 'user')
password = config.get('client', 'password')
hostname = config.get('client', 'host')

defragmented_tables_cmd="mysql -u %s -p%s -h %s --skip-column-names --batch  -e 'SELECT TABLE_SCHEMA, TABLE_NAME, Data_free FROM information_schema.TABLES WHERE Data_free > 1000'" % (username, password, hostname)

# This will explode if any databases or tables have spaces in their names.
for dbtable in os.popen(defragmented_tables_cmd).readlines():
    (database,table,space_free) = dbtable.split(None)
    print " Defragmenting database : %s, table: %s, space free: %s " % (database, table, space_free)
    os.popen("mysql --batch -u %s -p%s -h %s -e 'OPTIMIZE TABLE %s' " % (username, password, hostname, database + '.' + table))

Having written this, I don’t think it does any more than “mysqloptimize –all-databases”, althoug you could make the above run selectively (i.e. only on certain databases / table formats / disk free amounts ).