convert a mysql-database from latin1 to utf8

I recently had a joomla-installation that ran on latin1 but we wanted to become it utf8 to be able to add turkish text.

I did not inline-convert but converted the existiing database into a new one and changed joomla to listen on the new one. So I was sure to have a working backup in case something goes wrong.

• dump the old database : mysqldump -uroot -pXXXXX --opt --default-character-set=latin1 --skip-set-charset DATABASE > /tmp/database
• create a new database : CREATE DATABASE newdatabase CHARACTER SET utf8 COLLATE utf8_bin;
• grant rights on the new database to the old user:
  insert into db (Host,Db,User,Select_priv) values (’%’,’NEWDATABASE’,’USERNAME’,’Y’);
update db set select_priv=’Y’, Insert_priv=’Y’, Update_priv=’Y’, Delete_priv=’Y’, Create_priv=’Y’, Drop_priv=’Y’, Grant_priv=’Y’, References_priv=’Y’, Index_priv=’Y’, Alter_priv=’Y’, Create_tmp_table_priv=’Y’, Lock_tables_priv=’Y’ where User=’USERNAME’;
• convert database-dump to utf8: sed -e ‘s/latin1/utf8/g’ -i /tmp/database
• import dump to new database : mysql -uroot -pXXXXX newdatabase < /tmp/database
• and now there is one important step left : convert all tables to utf8. you can do it by hand for each table or use the Tee-command and a perl-command to automate the process:
  • \T /tmp/wtf\\show tables;
  • use emacs or another editor to open the file and get rid of header and footer
  • use the following line to create the proper commands :

    cat /tmp/wtf  | perl -pe 's/\|//g;s/\s//g; $_="ALTER TABLE $_ CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;\n"' 

  • you can then pipe or copy/paste this into mysql