Bash script that automates routine operations on sharded data using Percona Toolkit

lock_free_bulk_alter_table.sh

#!/bin/bash
set -e
declare -r DB_HOST=db_host
declare -r DB_USER=db_user
declare -r DB_PASSWORD=db_pass

db_table_info() {
    local db_name=$1; shift
    local table_name=$1; shift

    echo db_table_info for $db_name.$table_name
    mysql --host=$DB_HOST --user=$DB_USER --password=$DB_PASSWORD -e " \
        select TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE \
            from information_schema.TABLES \
            where TABLE_SCHEMA = '$db_name' and TABLE_NAME = '$table_name'"; 
}

db_alter_table() {
    local db_name=$1; shift                                                                               
    local table_name=$1; shift                                                                         
    local alter_text=$1; shift   
                                                                                                          
    echo running db_alter_table for $db_name.$table_name...
    db_table_info $db_name $table_name;

    time pt-online-schema-change --host=$DB_HOST --user=$DB_USER \
        --password=$DB_PASSWORD --alter "$alter_text" \
        --execute D=$db_name,t=$table_name;
    
    db_table_info $db_name $table_name;
}

main() {
    local db_names=(db1 db2)                                                          
    local table_names=(table1 table2)
 
    for db_name in ${db_names[*]}; do
        for table_name in ${table_names[*]}; do
            db_alter_table $db_name $table_name \
                "add column last_name varchar(200) null after first_name"
        done
    done
}

main "$0"