Monday, October 13, 2014

Convert to InnoDB from MyISAM - MySQL

Bash IT!

this will convert and schema prefixed with foo_ AND is MyISAM to InnoDB.

#!/bin/bash
#

# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"

# Get hostname
HOST="$(hostname)"
x=1
# Get all database list first
echo "select table_schema,table_name from information_schema.tables where (table_schema Like 'foo_%') and engine = 'MyISAM'" | $MYSQL --login-path=local -Bs | while read -r schema name
do
    x=$(ps ax|grep -c "mysql --login")
    while [ $x -gt 40 ]
    do
       echo "Overload!!! Running = $x"
       sleep 10
        x=$(ps ax|grep -c "mysql --login")
    done
    echo "Running = $x"

    changequery="ALTER TABLE \`$schema\`.\`$name\` ENGINE=InnoDB;";
    echo "echo $changequery | $MYSQL --login-path=local -Bs -D$schema";
    nohup echo $changequery | $MYSQL --login-path=local -Bs -D$schema &
done
~