Converting a Database from latin1 to UTF-8 Print

These instructions will work for any MySQL database installed on a Linux server.  These instructions are specifically targeted at WHMCS users that are interested in upgrading to the new UTF8 standard, which is required for multi-lingual support.  These instructions are written assuming that you are logged into the Linux box command prompt with the required access.

1. Backup the MySQL Database
   

   mysqldump --user=USERNAME -p --default-character-set=latin1 
   -c --insert-ignore --skip-set-charset -r whmcs_database.sql DATABASE_NAME

2. Convert the Latin1 Encoding to UTF8
   

   iconv -f ISO8859-1 -t UTF-8 whmcs_database.sql > whmcs_database.utf8.sql

3. Drop and Re-Create your database
   

   mysql --user=USERNAME -p --execute="DROP DATABASE DATABASE_NAME;
   CREATE DATABASE DATABASE_NAME CHARACTER SET utf8 COLLATE utf8_general_ci;"

4. Edit the SQL file using a UTF8 capable editor, like Notepad+. 
   ** You can skip this step and run the Step 6 PHP script instead
   

   Find and replace "CHARSET=latin1" with "CHARSET=utf8"
   Find and remove "set latin1 collate latin1_general_ci" from table field lines
   Save the file

5. Import the updated UTF8 database
   

   mysql --user=USERNAME --max_allowed_packet=16M -p 
   --default-character-set=utf8 DATABASE_NAME < whmcs_database.utf8.sql

6. Run the following PHP script that check and provide update SQL for any table fields that may need character set conversion.
   http://billing.holodyn.com/dl.php?type=d&id=20

This information was collected from the following sources:

http://en.gentoo-wiki.com/wiki/Convert_latin1_to_UTF-8_in_MySQL
http://forum.whmcs.com/showthread.php?t=21865