Posts tagged conversion
Converting Databases To UTF8 Format From Latin1
1I 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!
Recent Comments