Converting a Database from latin1 to UTF-8 Print

  • 9

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;
  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.

This information was collected from the following sources:

Was this answer helpful?

« Back