Bash script for converting the charset of all tables in a database

convert_charset

#!/bin/bash

# Convert all tables in a specified database to a different charset
#
# NOTE: this will NOT preserve data if it was stored improperly to begin with,
# e.g. UTF8 encoded strings saved as latin1 charset will get munged by this
# script when the table is converted. To preserve data you must export it, change
# the charset, then re-import.
#
# Command taken from:
# http://stackoverflow.com/questions/105572/a-script-to-change-all-tables-and-fields-to-the-utf-8-bin-collation-in-mysql

COMMAND=mysql
CHARSET=utf8
COLLATION=
DRYRUN=false

usage() {
  echo "Usage: convert_charset [-u <db_username>] [-p <db_password>] [-d] <db_name> [<charset>] [<collation>]"
  exit 1
}

while getopts ":u:p:d" opt; do
  case $opt in
    u)
      COMMAND="$COMMAND -u $OPTARG"
      ;;
    p)
      COMMAND="$COMMAND -p$OPTARG"
      ;;
    d)
      DRYRUN=true
      ;;
    \?)
      usage
      ;;
  esac
done

shift $((OPTIND-1))

# Fail if wrong number of args are specified
if [ $# -lt 1 -o $# -gt 3 ]; then
  usage
fi

COMMAND="$COMMAND --database $1"
EXCOMMAND=$COMMAND

# Set charset if sepcified
if [ $# -gt 1 ]; then
  CHARSET=$2
fi

MESSAGE="Converting all tables in database '$1' to charset '$CHARSET'"

# Add collation if specified
if [ $# -gt 2 ]; then
  CHARSET="$CHARSET COLLATE $3"
  MESSAGE="$MESSAGE using collation '$3'"
fi

echo $MESSAGE

# If this is a dry run, just echo the SQL to stdout
if $DRYRUN; then
  echo "Dry run..."
  EXCOMMAND=cat
fi

$COMMAND -B -N -e "SHOW TABLES" \
| awk "{print \"SET foreign_key_checks = 0; ALTER TABLE\", \$1, \"CONVERT TO CHARACTER SET $CHARSET; SET foreign_key_checks = 1; \"}" \
| $EXCOMMAND