sqlsizer.sh

#!/bin/bash

function usage {
    echo "sqlsizer.sh database ..."
    echo ""
    echo "sqlsizer is a script for finding the size of various databases and "
    echo "tables in a database. By default sqlsizer prints the size of every database. "
    echo "You can find the size of a whole database by specifying just the database name or "
    echo "you can find the size of a single table by specifying database.table instead. "
    echo "If you wish to find the sizes of all tables in a database, specify 'database.' with a trailing period."
    echo "This script always checks for databases running on localhost."
    echo ""
    exit
}

# converts input numbers to fewer digits using si-prefixes
function humanReadable {
    local bytes="${1:-0}"
    local padding="${2:-0}"
    local base="${3:-1024}"
    awk -v bytes="${bytes}" -v pad="${padding}" -v base="${base}" '
        function human(x, pad, base) {
            if(base!=1024)base=1000
            basesuf=(base==1024)?"iB":"B"

            s="BKMGTEPYZ"
            while (x>=base && length(s)>1)
                {x/=base; s=substr(s,2)}
            s=substr(s,1,1)

            xf=(pad=="yes") ? ((s=="B")?"%5d   ":"%8.2f") : ((s=="B")?"%d":"%.2f")
            s=(s!="B") ? (s basesuf) : ((pad=="0") ? s : ((basesuf=="iB")?(s "  "):(s " ")))

            return sprintf( (xf "%s"), x, s)
        }
        BEGIN{printf human(bytes, pad, base)}'
    return
}

# Checks to see if the sqlLoginFile contains working mysql login information.
# sqlLoginFile must be global.
sqlLoginFile="${HOME}/.my.cnf"
function myLoginCheck {
    if [[ -f ${sqlLoginFile} ]]; then
        if mysql --defaults-file="${sqlLoginFile}" -e ";" ; then
            return 0
        fi
    fi
    return 1
}

# Prompt for SQL login information, do not return until we have a working login
function userPrompt {
    local enteredPass=""
    read -s -p "Enter SQL root password: " enteredPass
    sqlLoginFile=$(mktemp -q --tmpdir "sqlsizer.XXXXXX")
    trap 'rm -f "${sqlLoginFile}"' EXIT
    chmod 0600 "${sqlLoginFile}"
    cat > "${sqlLoginFile}" << EOF
[client]
user=root
password='${enteredPass}'
EOF
    until mysql --defaults-file="${sqlLoginFile}" -e ";" ; do
        read -s -p "Can't connect, please retry: " enteredPass
        cat > "${sqlLoginFile}" << EOF
[client]
user=root
password='${enteredPass}'
EOF
    done
    echo ""
}

# takes a database name as it's only argument. returns true if a database exists with that name
function databaseExists {
    mysql --defaults-file="${sqlLoginFile}" --silent --skip-column-names -e "use $1" &> /dev/null
    return
}

# takes databasename.tablename as it's only argument. returns true if that table exists in that database
function tableExists {
    local dbname=$(cut -d '.' -f 1 <<< "$1")
    local tablename=$(cut -d '.' -f 2 <<< "$1")
    local retval=$(mysql --defaults-file="${sqlLoginFile}" --silent --skip-column-names -e \
    "SELECT COUNT(*) FROM information_schema.tables
    WHERE table_schema = \"${dbname}\" AND table_name = \"${tablename}\";")
    if [[ ${retval} -gt 0 ]]; then
        return 0
    fi
    return 1
}

# takes an existing database as it's only argument. Returns it's size in bytes
function databaseSize {
    mysql --defaults-file="${sqlLoginFile}" --silent --skip-column-names -e \
    "SELECT SUM(data_length + index_length) FROM information_schema.tables
    WHERE table_schema = \"$1\";"
}

# takes an existing database.table as it's only argument. Returns it's size in bytes
function tableSize {
    local dbname=$(cut -d '.' -f 1 <<< "$1")
    local tablename=$(cut -d '.' -f 2 <<< "$1")
    mysql --defaults-file="${sqlLoginFile}" --silent --skip-column-names -e \
    "SELECT SUM(data_length + index_length) FROM information_schema.tables
    WHERE table_schema = \"${dbname}\" AND table_name = \"${tablename}\";"
}

# returns a list of all tables in a database in database.table format
function getAllTables {
    local database=$(sed 's/.$//g' <<< ${1})
    mysql --defaults-file="${sqlLoginFile}" --silent --skip-column-names "${database}" -e "show tables" | sort | sed "s/^/${1}/g"
}

# simply returns a list of all databases
function getAllDBs {
    mysql --defaults-file="${sqlLoginFile}" --silent --skip-column-names -e "show databases" | sort
}

# --------------------------------------------------------------------------- #

if ! myLoginCheck; then
    userPrompt
fi

# If no  arguments, print all databases by default
if [[ $# -eq 0 ]]; then
    for d in $(getAllDBs); do
        humanReadable $(databaseSize $d)
        echo " $d"
    done
fi

while [[ $# -gt 0 ]]; do
    # option processing
    if [[ $1 == "-"* ]]; then
        if [[ $1 == "-h" || $1 == "--help" ]]; then
            usage
        fi
    fi

    # operand processing
    if [[ $1 == *"." ]]; then
        # input is database. print all the tables in a database
        for t in $(getAllTables "$1"); do
            humanReadable $(tableSize $t)
            echo " $t"
        done
    elif [[ $1 == *"."* ]]; then
        # input is database.table. print just that table
        if tableExists $1; then
            humanReadable $(tableSize $1)
            echo " $1"
        fi
    else
        # input is just a database. print just that database
        if databaseExists $1; then
            humanReadable $(databaseSize $1)
            echo " $1"
        fi
    fi
    shift
done

exit 0