How to Change/Convert your MySQL Database Collation to utf8 (a verbose sample)

Scenario: You have a very big database(schema)(more than 500 mb), and you wish to convert it say from latin1 to utf8(for reasons you can keep :) probably because you want your DB to be able to accommodate an app that can be fully localized (like Mambo or Vbulletin for Example), then this article might help.

You might find many conversion scripts around, but ever wondered what happens in the background? then read on.

Tools:
1.) mysqldump and mysql -or- phpmyadmin
2.) a text editor (vi perhaps?)

Steps:

Step 1: Create a new Database
This will be the placeholder for the database you are about to convert:

[SQL Query]

CREATE DATABASE `new_database` DEFAULT CHARSET=utf8


Step 2: Generate a "Structure-only sql dump"
Export an SQL file that should contain only the table structure:

[SHELL COMMAND]

mysqldump -u {USER} -p{PASSWORD} --no-data --no-create-db --opt --quote-names old_db > old_db_structure_only.sql

where old_db is the name of the database you wish to convert. Replace {USER} and {PASSWORD} with appropriate values


Step 3. Modify the "Structure-only sql dump"
Open the "old_db_structure_only.sql" file, use a text editor-> "Replace All" feature to change all instance of "DEFAULT CHARSET={CURRENT_CHARSET}" to "DEFAULT CHARSET=utf8". If you are using vi, you can probably get away with it by using the following command

[vi's non-visual mode command (press caps lock, then escape, then 'q' to switch to this mode)]

:%s/CHARSET=latin1/CHARSET=utf8/

assuming you are converting a latin1 collated DB


Step 4. Generate a Data-only sql dump
Next is generate a data-only dump, if your DB is more than 50mb, i advise that you use mysqldump command to execute this. Generating a big data-only dump from a big DB might lead to problems if done over http (timeouts, corrupted output etc)

[SHELL COMMAND]

mysqldump -u {USER} -p{PASSWORD} --no-create-db --no-create-info --opt --quote-names old_db > old_db_data_only.sql


Step 5. Restore the Structure-only sql dump (.sql) into the new Database

[SHELL COMMAND]

mysql -u {USER} -p{PASSWORD} new_db < old_db_structure_only.sql


Step 6. Restore the Data-only sql dump (.sql) into the new Database

[SHELL COMMAND]

mysql -u {USER} -p{PASSWORD} new_db <>Step 7. Drop the old Database (Make sure you are keeping your backups)

[SQL Query]

DROP DATABASE `old_db`

Step 8. Point your app from `old_db` to `new_db`
This would depend with your php application. There is a file usually named config.php or configuration.php which you will have to change the db_name values.

your app nows use utf8.. done