Converting Databases To UTF8 Format From Latin1
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!