Alter all tables in a given schema from InnoDB to TokuDB

alter_to_tokudb.sh

#!/bin/sh

SCRIPT_NAME=`basename $0 | sed -e "s/\.sh$//"`
[ -z "${TMP_DIR}" ] && TMP_DIR="/tmp"
TMP_FILE="${TMP_DIR}/${SCRIPT_NAME}.tmp.$$"

[ -z "${LOG_DIR}" ] && LOG_DIR="/opt/pythian/log"
[ ! -d "${LOG_DIR}" ] && echo "ERROR: Log directory '${LOG_DIR}' is not accessible." && exit 1

[ -z "${SCHEMA}" ] && SCHEMA="test"
[ -z "${SOURCE_ENGINE}" ] && SOURCE_ENGINE="InnoDB"
[ -z "${ROW_FORMAT}" ] && ROW_FORMAT="TOKUDB_LZMA"

generate_foreign_keys() {
  local FILE=$1

  [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1

  echo "Generating foreign keys in '${SCHEMA}'"
  mysql -N -u${MYSQL_USER} -p${MYSQL_PWD} -e "SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_SCHEMA=DATABASE();" ${SCHEMA} > ${FILE}
  RC=$?
  [ ${RC} -ne 0 ] && echo "ERROR: select of foreign keys failed" && exit ${RC}

  return 0
}

drop_foreign_keys() {
  local FILE=$1

  [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1

  echo "Dropping foreign keys in '${SCHEMA}' with '${FILE}'"
  mysql -vvv -u${MYSQL_USER} -p${MYSQL_PWD} ${SCHEMA} < ${FILE}
  RC=$?
  [ ${RC} -ne 0 ] && echo "ERROR: drop of foreign keys failed" && exit ${RC}

  return 0

}

alter_tables() {
  local FILE=$1

  [ -z "${FILE}" ] && echo "INTERNAL ERROR: No file passed" && exit 1

  mysql -N -u${MYSQL_USER} -p${MYSQL_PWD} -e "SELECT CONCAT('SELECT NOW();ALTER TABLE ',TABLE_NAME,' ENGINE=TokuDB, ROW_FORMAT=${ROW_FORMAT};') FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND engine = '${SOURCE_ENGINE}'"  ${SCHEMA} > ${FILE}
  RC=$?
  [ ${RC} -ne 0 ] && echo "ERROR: Unable to generate alter file for '${SOURCE_ENGINE}' tables" && exit ${RC}

  time mysql -vvv -u$MYSQL_USER -p$MYSQL_PWD ${SCHEMA} < ${FILE}
  RC=$?
  [ ${RC} -ne 0 ] && echo "ERROR: Alter of tables failed" && exit ${RC}

  return 0
}

# Ensure environment variables to access MySQL environment via script are defined
[ -z "${MYSQL_USER}" -o -z "${MYSQL_PWD}" ] && echo "ERROR: MySQL credentials are not defined" && exit 2

# Generate the foreign keys that are to be dropped
FK_FILE="${LOG_DIR}/drop_foreign_keys.pre.sql"
generate_foreign_keys ${FK_FILE}
[ -s "${FK_FILE}" ]  && drop_foreign_keys ${FK_FILE}

# Verify there are no foreign keys left
FK_FILE="${LOG_DIR}/drop_foreign_keys.post.sql"
generate_foreign_keys ${FK_FILE}
[ -s "${FK_FILE}" ] && echo "ERROR: foreign keys remain in '${FK_FILE}'" && exit 1
echo "All foreign keys for Schema ${SCHEMA} removed"

ALTER_FILE="${LOG_DIR}/alter.sql"
alter_tables ${ALTER_FILE}

exit 0