I have been putting off the inevitable the last couple of weeks, which was the task of converting the database for CVGM.net to UTF8 from the default collation of latin1-swedish-ci. I am not the best when it comes to administering MySQL via command script, and phpMyAdmin didnt do the job properly of conversion for me. After a bit of googling, I found a bash script at islandlinux that was able to help me out considerably:

#!/bin/bash

DATABASE=$1

if [ ! "$DATABASE" ]; then
echo "Please specify a database"
exit
fi

BACKUPDIR="/root/tmp/mysql_backups/"

if [ ! -d "$BACKUPDIR" ]; then
mkdir -p "$BACKUPDIR"
fi

BACKUP="$BACKUPDIR""$DATABASE.sql"

mysqldump --add-drop-table --extended-insert "$DATABASE" > "$BACKUP"

TABLES=`mysql --batch --execute 'SHOW TABLES' "$DATABASE" | grep -v "^Tables_in"`

for TABLE in $TABLES; do
echo 'ALTER TABLE `'"$TABLE"'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;';
# uncomment the following line to process the commands
#mysql --execute 'ALTER TABLE `'"$TABLE"'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' "$DATABASE"
done

This script converted the database tables and everything under it to the correct format. It also backs up the databases first in case something decides to take a crap on you. The problem I had with phpMyAdmin was even though I changed the collation to utf8_general_ci it didn’t do it recursively into the tables and the fields underneath it.

Thanks for the script guys 🙂 You saved me even more work!